Getting Started With SQL Azure series:

1. What is SQL Azure? 2. Creating First SQL Azure Database 3. Provisioning and Billing Model 4. SQL Azure Architecture 5. SQL Azure's security model 6. Migrating Databases to SQL Azure 7. Administration Tasks - I 8. Administration Tasks - II 9. Developing SQL Azure applications 10A. Getting started with SQL Azure reporting and SQL Azure Data SYNC 10B Conclusion

Now in this module, we are going to see how we can migrate SQL Server 2008 R2 database to SQL Azure. We will also discuss about options available and their pros and cons. So, by the end of this article, you would be equipped with knowledge of tools that could help you migrate your SQL Server Database to SQL Azure.

Now, there are two types of migration:

1. Schema Migration: Here our aim is to get the DDL (Data definition language) scripts of the source SQL Server Database. In this article, I will demonstrate a schema migration process using “Generate script wizard” which is a feature available through SQL Server Management Studio.

2. Data Migration: Once we have generated the schema in the target SQL Azure Database, the next step is to populate the tables with data. In this article, I will demonstrate a data migration process using BCP (command line utility).

Now, Generate Script Wizard and BCP are not the only option for migration process. There are other options too. At the end of the article, I would summarize other options. Therefore, based on your need – you could pick the best tool for the process.

So, let us do a schema migration through Generate Script Wizard.

Once you have provisioned a SQL Azure server, created a SQL Azure database and ready to migrate the SQL Server Database Schema (or probably a small chunk of data) to SQL Azure, you will find Generate Script Wizard that is a feature available in SQL Server Management Studio 2008 R2 to be very handy. In this article, we are going to discuss how to use Generate Script Wizard feature to migrate SQL Server database schema to SQL Azure database. So let’s get started.

Open SQL Server Management Studio 2008 R2 (I suggest you use this version or above of the SQL Server Management Studio with SQL Azure)

Enter in the correct SQL Server credentials. Now in the Object Explorer, right click on the Database >> Go to ‘Tasks’>> Select ‘generate scripts’

3. Now the home page of the ‘Generate script wizard’ looks like:

As mentioned in the wizard – there are four steps to complete the Wizard. Let’s do them step by step

4. Click on next. To see a ‘choose objects’ page.

You could choose to script the entire database and database objects or alternatively just script specific database objects. For the purpose of the blog post, I have selected three tables that I wish to script. Once you are done, click next

5. Now you will see a ‘scripting options’ page

Now, here you are given the option of choosing the destination where the generated scripts could be saved. I choose to save it in a SQL file that could be later opened in a SSMS and we could easily run it against our target database. In this case, it is a SQL Azure database.

Now, let’s set some scripting options. Click on Advanced button that you see.

6. The first thing that you want to do select the target database type. In our case, it is a SQL Azure database. you can do that by selecting ‘SQL Azure Database’ in the ‘Script for the database engine type’:

Notice some of the options being greyed out. This is so because when you select the destination database engine type as ‘SQL Azure database’ – features that are currently not supported by SQL Azure are greyed out and will not be scripted. Also set “Convert UDDTs to Base Types” option as “True”.

Please note that even after selecting this option, you may run into features that are currently not supported by SQL Azure. Ideally, clear understanding of the contemporary difference between a SQL server and a SQL Azure database is required before you start the migration process. Having said that, if you ran into error in the scripting process, you will shown a report and you could easily rectify it. I will show a sample error report, later in the post.

7. Also, Advanced let’s you select the “Types of data to script”. In our case, we intend to carry out a schema migration, so we select “schema only”.

Now, ideally the Generate script wizard is not used to migrate data – tools like SSIS and BCP do that better. If you are looking for Data Migration, I suggest you try out SSIS or BCP.

8. Now click on ‘ok’ and then in the ‘set scripting page’, select ‘next’. you will see a page that summarizes your selections:

review it and then click on ‘next’ button

9. A successful scripting process will look like this:

In case, you run into any error – please click on ‘save report’ and open it. A sample report looks like:

review the message column and after rectifying  the error, try scripting it again.

10. We have been successful at scripting, right? 

So now I double click on the .SQL file and also from the SSMS, I connect to the destination SQL Azure database and then run the script. Also create the schema if it does not exist. This is result after the script ran successfully!

11. Voila! we have successfully completed the schema migration process!

Browsing the database in the object explorer to double check the success of the process:

Now let’s populate the ‘SalesLT.Address’ table with data using BCP!

BCP is an ideal straightforward command prompt driven tool to push data from one table to another similar table. This makes it an excellent tool to carry out data migration from SQL server to SQL Azure.

we have already migrated the schema of ‘SalesLT.Address’ table to SQL Azure. Now let’s populate it with data using BCP!

Table in local SQL server 2008R2 database:

Table in SQL Azure, Before Data Migration:

Now to carry out the data migration, the first step is to export data out from SQL server’s Table.

The format to Export table using BCP is: bcp table out filename -servername -username –password

Now here is the command in my case:  bcp adventureworkslt2008r2.saleslt.address out c:\temp\datafromsqlserver.txt -c -S (local) –T

here ‘adventureworkslt2008r2.saleslt.address’ is the table. ‘out’ states that we are exporting data out of the table. ‘c:\temp\datafromsqlserver.txt’ is the location of the text file where data will be pushed. “–S (local) –T” is to state that I want to connect to SQL server instance named (local) and ‘–T’ is used for windows Authentication. And if you are using SQL server Authentication, you can specify it in the U username-S servername-P password format.

Now, after we have exported the data from a local table, it’s time to push the data to cloud! So we will import the data to SQL Azure from the text file (we created the text file in above step, remember?!)

The format to Import table using BCP is bcp table in filename -servername -username –password

Now here is the command in my case: bcp adventureworksltaz2008r2.saleslt.address in c:\temp\datafromsqlserver.txt -c -U parassqlazure@bpqjia83ct -S tcp:bpqjia83ct.database.windows.
net –P *************

Now,  Let’s see the status of the table in SQL Azure database. Were we able to push those rows (Data) to cloud?!

Table in SQL Azure, After Data Migration:

Voila! as you might have noticed that we were able to migrate 450 rows (data) from ‘local’ table to ‘cloud’ table using BCP! it’s very easy and straight forward, isn’t it? And what’s more, it is an excellent tool to push lots of data to SQL Azure!

So, I hope you got a taste of how to migrate your SQL server database to SQL Azure – Now, let’s see what are our other options:

(This article would become ultra long if I discuss each process in step by step process, so I would point you to the article that I have written and then, summarize all options in a table. I hope you are OK with it)

Here we go:

First, there is a popular desktop application named SQL Azure migration wizard for migrating SQL Server databases to SQL Azure. However, Microsoft does not officially support it. However, it encapsulates the entire process of schema migration and data migration and the GUI is intuitive for the end user.

You can download the tool from here: http://sqlazuremw.codeplex.com/

For a step-by-step process: www.beyondrelational.com/blogs/parasdoshi/archive/2011/04/04/migrating-tsql-file-and-sql-server-2005-2008-database-to-sql-azure-using-sql-azure-migration-wizard.aspx

Second, SSIS (SQL Server Integration Services)*:* This is not a feature of SQL Azure. However, if you run SSIS on premise, you can configure the target database to be SQL Azure. And SSIS is a better control than BCP to migrate data from SQL server to SQL Azure.

[Tip: Prefer ADO.Net over OLEDB destination, as OLEDB is not supported by SQL Azure]

For a step-by-step process: http://beyondrelational.com/blogs/parasdoshi/archive/2011/07/18/how-to-use-sql-server-integration-services-ssis-to-migrate-data-from-sql-server-to-sql-azure-lt-lt-paras-doshi.aspx

Third, using SQL Azure import Export tool (Not to be Confused with SQL Server Import Export Wizard) to generate a BACPAC of a SQL Server database. And then load that BACPAC into SQL Azure. Now, BACPAC is essentially a zipped file of your ENTIRE database.

Note: When you try creating a BACPAC of your SQL Server database and if your source SQL Server database contains features that are not supported by SQL Azure. Then, the process would throw and error and you would be able to see a report of what went wrong. Once you know that, you can fix it and retry the BACPAC creation process.

Now, to generate a BACPAC of your SQL server database, you will need to install some binaries on your machine. Download details here: http://sqldacexamples.codeplex.com/

And once you generate your BACPAC, you upload it to Azure Blob storage. And then using the Import/Export tab in the SQL Azure portal (Windows Azure management portal) – you import this BACPAC and that’s how you migrate a SQL server database to SQL Azure using SQL Azure import export.

And now it’s summary time!

Note: Dac V 2.0 = SQL Azure import Export.

Source: http://beyondrelational.com/blogs/parasdoshi/archive/2011/08/15/summary-sql-server-to-sql-azure-migration-lt-lt-paras-doshi.aspx 

Now, up to this point, we have seen migration for SQL Server database to SQL Azure. What if we have non-SQL Server databases? No worries, the SQL Server Migration assistant tools could help you migrate MS ACCESS, MySQL and Oracle database to SQL Azure! (Just make sure, you the versions that support SQL Azure)

Conclusion:

Thus in this article, we saw how to migrate your databases to SQL Azure! Happy Migrating!

Update:

Part 7 is live: http://beyondrelational.com/blogs/parasdoshi/archive/2011/11/27/getting-started-with-sql-azure-part-7-sql-azure-administration-i-lt-lt-paras-doshi.aspx