[ SQL Azure Labs ] Exploring SQL Azure Import/Export Feature (CTP)

[Update: The location from where you would download the client libraries is now: http://sqldacexamples.codeplex.com/releases/view/72388]

In this blog post, we are going to explore a tool called SQL Azure Import/Export currently available in SQL Azure Labs.

So what does it enable us to do? One, it helps us migrate SQL server ‘Data + Schema’ to SQL Azure and two, it helps us Export SQL Azure data and keep an archived copy on local disks. When you export a database, it creates a Zipped like version of the entire database in other words both data and schema is pushed in a file having .bacpac extension. Now this file can also be imported using the import feature. Thus as you can imagine, migrating database between SQL server versions just got simpler!

Note that the import export facility is not equivalent to backup restore. if you perform export on a live database then the .bacpac may or may not contain the transitionally consistent data. And point in time recovery is not supported.

SQL Azure Import/Export (CTP) is also called Data Tier Application v2.0 and adds full support for SQL Azure.

Now, First let’s setup the environment (Links added as on 8th July 2011)

You will need a .Net 4.0 runtime installed. Download it from here.

Now,

Install https://www.sqlazurelabs.com/SQLSysClrTypes.msi first. And Now install https://www.sqlazurelabs.com/SharedManagementObjects.msi .

Now install, https://www.sqlazurelabs.com/DACFramework.msi , https://www.sqlazurelabs.com/SqlDom.msi , https://www.sqlazurelabs.com/TSqlLanguageService.msi in any order.

(Not recommended to work in parallel with SQL server Denali CTP 1)

Now, download https://www.sqlazurelabs.com/DacImportExportCli.zip and extract to a folder.

Now, Open command prompt and reach the location where the DacImportExportCli.exe resides.

Now First, we are going to export a SQL Azure Database and archive it locally in a .bacpac file

Run the following command:

DacImportExportCli.exe –s servername.database.windows.net –d databasename –f filepath –x –u username –p passwordSee a sample command in action:

Now, we can import this bacpac to a SQL server server. I tried Importing it to a SQL server 2008 R2 instance using the following command

DacImportExportCli.exe –s servername –d databasename –f filepath –i -eNote: –e indicates that the login method is Windows Authentication

To get help on the Command DacImportExportCli.exe please run

DacImportExportCli.exe helpyou will see:

Now, we can also use DAC v2.0 (SQL Azure Import Export) to migrate database from SQL server to SQL Azure. For the purpose of this blog post, I will migrate a SQL server 2008 R2 database to SQL Azure.

Before we do so, just note that as of now, the DAC v2.0 CTP does not support all SQL server schema objects and so when trying to export a database other than SQL Azure, you may run into an error. In such cases, you will need to remove object that is marked unsupported by the DAC.

now, run the command to create a BACPAC of a SQL server database:

DacImportExportCli.exe -s serverName -d databaseName -f filepath -x -e  After, successful exporting you will see:

Now, one can import this BACPAC to a SQL Azure server using the command:

DacImportExportCli.exe -s serverName.database.windows.net -d databaseName -f filepath -i -u userName -p password

Also to specify the Max Size and the edition, use the following command:

DacImportExportCli.exe -s serverName.database.windows.net -d databaseName -f C:filePathexportFileName.bacpac -i –edition business –size 20 -u userName -p passwordit creates business edition SQL Azure database of MaxSize 20 GB.

Thus in this article, we saw how to archive a SQL Azure database and also migrate SQL server database to SQL Azure using DAC v2.0 (SQL Azure Import Export CTP)