Migrating A SQL Server Database To SQL Azure

SQL Azure is essentially a cut down version of SQL Server and so we would expect that migrating from SQL Server to SQL Azure should be a straightforward task. However, in the first release of SQL Azure, the scripts generated by SQL Server Management Studio will require some extra cleanup since not all the SQL Server 2008 features are supported in SQL Azure.

For this demo we will use SQL Server Management Studio (SSMS) to generate the SQL scripts and
migrate an existing database from SQL Server 2008 to  SQL Azure. It should be noted that there are several tools such as the SQL Migration Wizard for assisting in the migration, but in this article we will look at performing a manual migration.

Since SQL Azure is built from SQL Server 2008, the database should first be migrated to SQL Serer 2008 if necessary (note – it is not necessary to update to 2008 R2).

1. Open SQL Server Management Studio (SSMS), right-click on the database node of the database to be migrated (AdventureWorks for this demo) and select Tasks > Generate Scripts , then select the database to be migrated and click Next:
   
2. In the Choose Script Options page, we need to configure the script to comply with the restrictions of SQL Azure, so make the following changes:
   • Convert UDDTs to Base Type: Set this  to true as SQL Azure does not support user-defined types. UDDT’s need to be converted into SQL Azure portable types.
   • Script extended properties: Set to false as SQL Azure does not support extended properties.
   • Script USE DATABASE: Set to false as SQL Azure does not support the USE statement.
   • Script Data: Set to false since we are not migrating the data right now.
3. In the Choose Object Types page – select all the objects except User Defined Data Types and click Next:
   
4. In a succession of screens you will be prompted to select the Stored Procedures, Views, Tables etc to be migrated (depending on which objects are in your database). Click Select All then Next for each.
5. Finally at the Output Option page select where the script should be generated (in this example I have elected to have the script generated in a new query window):
   
6. Click Next and SSMS will direct you to the Summary page before generating the scripts in a new SQL script window. The Generate Script Success screen shows the progress and results of the migration on the database objects (see below). Copy the script generated to the clipboard.
   
7. Log in to the Azure platform portal at https://sql.azure.com/ and create a target database on the online portal (simply click Create Database after navigating to the the SQL Azure tab and then select the database size).
8. Close and re-open SSMS to connect to SQL Azure and view your database (see Connect to SQL Azure from SSMS for details if neceesary) . Note – SSMS in SQL Server 2008 R2 can view the SQL Azure objects and should be used whenever connecting to SQL Azure.
9. Copy the script into a new script window in SSMS. Before we run the script against the target SQL Azure database we still need to make some modifications to the script:
   • SQL Azure’s first release does not support partitions. Therefore the KEY constraint statement for the table-creation scripts has to be removed and replaced by a separate script. For example, in the original script SSMS generated for creating a data table there are KEYconstraints such as

     PRIMARY KEY CLUSTERED
     (
     [TerritoryID] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
     ) ON [PRIMARY]

     This needs to be replaced with a separate ALTER TABLE Script to Assign a Key to a Table, such as:

     ALTER TABLE [Table_Name]
     ADD CONSTRAINT ID_PK PRIMARY KEY (Primary_Key)

   • If there are any CREATE NONCLUSTERED INDEX statements in the script, these will need to be modified. For example remove the bold type text from the below script section:

     CREATE NONCLUSTERED INDEX [IX_UserTable_FirstName] ON [dbo].[UserTable]
     (
     [FirstName] ASC
     )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,ONLINE = OFF) ON [PRIMARY]
     GO

That’s it, now run the script against the SQL Azure database (ie click New Query in SSMS, select the target SQL Azure database from the dropdown database listing  and click Execute).

It should be noted that this is as of SQL Azure Update 1 (February 2010) , SQL Azure is currently developing rapidly and not all the inconsistencies with SQL Server are well documented.

Related Articles:

• Migrating an ASP.NET site to an Azure Web Role
• SQL Azure Firewall – Tutorial
• SQL Azure Encryption and Compression [FAQ]
• SQL Azure Backup
• SQL Azure Migration – Moving the Data [FAQ]