Sunday, July 18, 2010

George Huey’s SQL Azure Migration Wizard (SQLAzureMW) offers a streamlined alternative to the SQL Server Management Studio (SSMS) Script Wizard for generating SQL Azure schemas that conform to the service’s current Data Definition Language (DDL) limitations. You can download the binaries, source code, or both from CodePlex; a Microsoft Public License (Ms-PL) governs use of the software. Detailed documentation in *.docx format is available. The recent versions use SQL Server’s Bulk Copy Protocol (BCP) utility to populate SQL Azure tables with data from an on-premises database.

• Update 7/18/2010 9:00AM: George Huey updated SQLAzureMW from v.3.3.2 to v.3.3.3 on 7/17/2010 with 5,227 downloads of the new version on its first day. The last documentation release on 7/6/2010 covers v.3.3. Following are the changes to versions v3.1.4 and later (the previous version of this post covered v.3.1.3):

v3.3

Added DateTime stamps on upload processing

Added Support [for] 50 GB SQL Azure databases

Removed checks for HierarchyID and geography data types

Refined check for textprt function, not just name

Created SQLAzureMWUtils which is used by SQLAzureMW and SQLAzureMWBatch

Added retry on 10054 SQL Azure connection errors

v3.2.2

Added functionality to allow the user to specify where the BCP output files should be stored. The following items have been added to SQLAzureMW.exe.config file:

Put in check to make sure that @Server (where @Server is the SQL Azure Server) has been entered during login process.

v3.1.8

On the DROP objects, put in a tempor[ar]y fix to reverse sort order the object. What this means is that as long as you have all of the dependent tables selected [for] the main table you want to drop you will be ok. If you want to drop one table that has dependent tables on it, then you will get errors. A fix is in process.

v3.1.7

Fixed sorting routine to ignore case when comparing object names

v3.1.6

Added functionality to create DROP scripts. You can set this option in SQLAzureMW.exe.config or dynamically during runtime.

v3.1.5

Removed search for dbo.sysobjects from tables in NotSupportedByAzureFile.config

Fixed script engine to handle a single TSQL file with no "GO" statements in it

v3.1.4

Modified SQLAzureMW to put "GO" between Tables / Views and their indexes. This is needed when parsing SQLAzureMW TSQL output from a file. Without the "GO", then the indexes were being parsed with Table / View rules thus causeing syntax errors on generated TSQL.

Most, if not all, workarounds in the step-by-step instructions below are unnecessary with v.3.3.3.

• Update 1/23/2010 4:00 PM PST: George Huey released v3.1.4 of his SQL Azure Migration Wizard (SQLAzureMW.exe) to CodePlex on 1/23/2010 with a fix for problems analyzing (parsing) T-SQL scripts from a file. If you don’t want to go through the entire process of analyzing and creating a T-SQL script from the on-premises AdventureWorksLT2008R2 database, I recommend using files from AdventureWorksLT.zip.

It will also analyze SQL Profiler trace files and TSQL script for compatibility issues with SQL Azure

I updated in mid-January 2010 the sections about SQLAzureMW (originally MigWiz) of Chapter 13, “Exploiting SQL Azure Database's Relational Features,” of my Cloud Computing with the Windows Azure Platform book for Wrox/Wiley to point to this post. SQLAzureMW is a moving target, as you can see from the workarounds at the end of this long article, so this post will be updated as George upgrades his Migration Wizard.

This post’s first section demonstrates the most common workflow for duplicating an on-premises SQL Server 2008 R2 database (AdventureWorksLT2008R2) in SQL Azure running in a Microsoft data center.

Note: This post is a preliminary attempt to find workarounds to problems observed in testing SQLAzureMW.exe v3.1.3 with the AdventureWorksLT2008R2 sample database when running on a VM with Windows Server 2008 R2 (no Hyper-V) as the guest OS. The host OS is Windows Server 2008 R2 Hyper-V.

2. Extract the zip files to a local folder, C:\Program Files\SQLAzureMW for this example.

3. Run SQLAzureMW.exe to open the ScriptWizard’s Select Process page.

4. Mark the Analyze and Migrate – SQL Database option, which enables migrating schemas and data to or from SQL Azure databases:

5. Click Next to open the Select Source page with the Connect To Server dialog active.

6. Select the on-premises SQL Server 2008 R2 server instance that contains the AdventureWorksLT2008R2 database from the Server Name list (the WINSRV2008SP2VM virtual machine for this example), accept the use Windows NT Integrated Security and 15 second Connection (timeout) defaults:

7. Click Connect to connect to the instance and open a list of available databases on the Select Source page, and select the source database (AdventureWorksLT2008R2 for this example):

9. Click Next to open the Script Wizard Summary page and expand the nodes to display individual database objects:

10. Click Next and click Yes in the Generate Script message box to open the end of the Result Summary Page:

Note: You can open BCP output files stored as tmp####.tmp in your \Users\UserName\AppData\Local\Temp\1 folder in NotePad or a binary file editor, but native BCP files consist of length followed by data. Data in native BCP files is encoded as Unicode (UTF-16).

Click Save to save the Result Summary as the default .rtf file type in the \Users\UserName\Documents folder.

11. Click the SQL Script tab to display the end of the generated T-SQL script:

Click Save to save the Script as the default .rtf file type (AdventureWorksLT2008R2Script.rtf for this example) in the \Users\UserName\Documents folder.

12. Click Next to open the Setup Target Server Connection with the Connect To Server dialog active with default SQL Azure Server Name, SQL Server security, User Name and Connection (timeout) defaults.

13. Replace SERVER with a copy of your SQL Azure server name in two places and User Name with your administrative user ID, and type your password:

Note: You can avoid the need to retype the Server Name and your administrator credentials by replacing values of the following elements of the SQLAzureMW.exe.config file’s <appSettings> group:

14. Click Connect and Create Database to open the Create Database dialog. Type the name of the SQL Azure database (AdventurWorksLT2008AZ for this example):

15. Click Create Database to add the new database to the list of the server’s databases, click Next to open the Execute Script message box and click Yes to execute the script against the SQL Azure database and display the Target Server Response page:

Click Save to save the Target Server Response as the default .rtf file type in the \Users\UserName\Documents folder.

Errors Reported in the Target Server Response File

The Target Server Response file shows numerous red error messages, which require correction to enable migration of object types that SQL Azure supports. The most serious of these errors occur with the SalesLT.ProductModel table, which contains a ProductDescription column of the xml datatype. SQL Azure supports the xml datatype, but not xml indexes, so creation of the table fails. The missing table cascades several other errors.

Error #: 40512 -- Deprecated feature 'More than two-part column name' is not supported in this version of SQL Server errors appear twice in the Target Server Response .rtf file. These three-part column name errors prevents creation of the SalesLT.uSalesOrderHeader and SalesLT.iduSalesOrderDetail triggers.

When using the SQL Azure Migration Wizard v3.1.3 with the SQL Azure Database December 2009 Release to Web version, it appears the simplest approach is to delete the PXML_ProductModel_CatalogDescription primary XML index from the source table (see this post’s first screen capture). In a production environment, however, deleting a Primary XML index can have serious affect on the performance of xml operations.

You must edit the T-SQL in the to solve the three-part column name errors, so also removing the primary XML index generation statements is a practical alternative:

To edit the T-SQL Script file you created and saved in step 11 of the preceding process, do the following:

1. If you have an AdventureWorksLT2008AZ database, open the SQL Azure portal and delete it. (Although you can delete an existing database of the selected server in a Wizard dialog, you might encounter an unhandled exception.)

2. Launch SQLAzureMW.exe and complete steps 1 through 10 of the preceding section.

3. In the Results Summary dialog with the SQL Script tab selected, scroll to the

--~ Table [SalesLT].[ProductModel] -- CREATE PRIMARY XML INDEX is not supported in current version of SQL Azure

and delete the instructions shown selected here:

4. Scroll to the first instance of the

--~ Table [SalesLT].[SalesOrderHeader] -- Multi-part names ( [SalesLT].[SalesOrderHeader].[RevisionNumber) with Server or Database specifier is not supported in this version of SQL Azure.

comment and remove the three instances of the [SalesLT].[SalesOrderHeader]. prefix, to change the code for the uSalesOrderHead trigger to that shown here:

Also remove the red error line.

5. Scroll to the second instance of the Multi-part names error message and change the code for the iduSalesOrderDetail trigger to that shown here

Also remove the red error line.

6. Click Save and save the script as an .rtf file (AdventureWorksLT2008R2FixScript.rtf for this example) to your Documents folder.

7. Continue with steps 12 through 15 of the preceding section. The dialog now appears as shown here:

8. Open the AdventureWorksLT2008AZ database in SQL Server 2008 R2 SSMS [Express] and verify that the SalesOrderHeader and SalesOrderDetail tables have a trigger.

Thanks to George Huey for providing details of the required workarounds.

To Create a T-SQL ScriptName.sql File from a Saved ScriptName.rtf File:

1. Open \Users\UserName\Documents\AdventureWorksLT2008R2FixScript.rtf (for this example) in WordPad or Microsoft Word (the version isn’t important), and save a backup copy.

2. Save a text file (\Users\UserName\Documents\AdventureWorksLT2008R2FixScript.txt for this example) encoded as a Unicode Text File.

3. Change the file extension from .txt to .sql.

To Create a T-SQL ScriptName.sql File by Copying Text from the Text Box:

1. Make the corrections to the Results Summary text box shown in steps 3, 4 and 5 of the preceding section.

2. With the text box selected, press Ctrl+A and Ctrl+C to copy the contents to the Clipboard.

3. Open a new query in SQL Server 2008 R2 SSMS [Express].

4. Press Ctrl+C to copy the text to the query.

5. Press Ctrl+F5, choose Query | Parse, or click the check mark in the tool bar to parse the query.

6. Save the script (as \Users\UserName\Documents\AdventureWorksLT2008R2FixScript.sql for this example.)

Download Demo Script Files for Testing:

The AdventureWorksLT2008R2FixScript.sql and AdventureWorksLT2008R2FixScriptNoBCP.sql files are available for download and inspection in AdventureWorksLT2008R2FixScript.zip from my SkyDrive folder.

instructions for adding data from temporary BCP native data files that are machine and instance specific. Errors reported as the result of temporary BCP files are non-fatal but time-consuming.

To Use the Saved Script in v3.1.3 with a Temporary Workaround:

1. Launch SQLAzureMW.exe, select the Run TSQL without Analyzing – TSQL FileAnalyze and Migrate – TSQL File option, browse to and select the AdventureWorksLT2008R2FixScript[NoBCP].sql file for this example:

Note: I investigated a problem importing a script file with the Analyze and Migrate – TSQL option, which causes many errors when analyzed, with George Huey. He reports that a bug in v3.1.3 removes some TSQL commands from the file that should be present. Until George releases v3.1.4 with the problem corrected, use the Run TSQL Without Analyzing option.

To Use Saved Scripts After the Bug Fix in v3.1.4:

1. Launch SQLAzureMW.exe, select the Analyze and Migrate – TSQL File option, browse to and select the AdventureWorksLT2008R2FixScript.sql file for this example:

Note: The script file with temporary BCP options included is shown above, but using the “NoBCP” version is recommended.

George Huey has provided an AdventureWorksLT.zip archive with an updated T-SQL script and a set of BCP data files that run without errors in v3.1.3 and v3.1.4. To use these files with v3.1.3 or v3.1.4, do the following:

3. Extract all files to the default subfolder: C:\Temp\AdventureWorksLT. The fixed path is required to process the BCP native data (*.DAT) files in the archive.

4. Follow the instructions in the preceding section, except substitute C:\Temp\AdventureWorks\AdventureWorks.sql for the path and file shown in the preceding screen capture.

5. Verify with SQL Server 2008 R2 SSMS that AdventureWorksLT2008AZ SQL Azure database contains the same objects, including the two database triggers, and data as the original AdventureWorksLT2008R2 on-premises database.

Using the SQL Azure Migration Wizard v3.3.3 with the AdventureWorksLT2008R2 Sample Database

George Huey’s SQL Azure Migration Wizard (SQLAzureMW) offers a streamlined alternative to the SQL Server Management Studio (SSMS) Script Wizard for generating SQL Azure schemas that conform to the service’s current Data Definition Language (DDL) limitations. You can download the binaries, source code, or both from CodePlex; a Microsoft Public License (Ms-PL) governs use of the software. Detailed documentation in *.docx format is available. The recent versions use SQL Server’s Bulk Copy Protocol (BCP) utility to populate SQL Azure tables with data from an on-premises database.

• Update 7/18/2010 9:00AM: George Huey updated SQLAzureMW from v.3.3.2 to v.3.3.3 on 7/17/2010 with 5,227 downloads of the new version on its first day. The last documentation release on 7/6/2010 covers v.3.3. Following are the changes to versions v3.1.4 and later (the previous version of this post covered v.3.1.3):

v3.3

Added DateTime stamps on upload processing

Added Support [for] 50 GB SQL Azure databases

Removed checks for HierarchyID and geography data types

Refined check for textprt function, not just name

Created SQLAzureMWUtils which is used by SQLAzureMW and SQLAzureMWBatch

Added retry on 10054 SQL Azure connection errors

v3.2.2

Added functionality to allow the user to specify where the BCP output files should be stored. The following items have been added to SQLAzureMW.exe.config file:

Put in check to make sure that @Server (where @Server is the SQL Azure Server) has been entered during login process.

v3.1.8

On the DROP objects, put in a tempor[ar]y fix to reverse sort order the object. What this means is that as long as you have all of the dependent tables selected [for] the main table you want to drop you will be ok. If you want to drop one table that has dependent tables on it, then you will get errors. A fix is in process.

v3.1.7

Fixed sorting routine to ignore case when comparing object names

v3.1.6

Added functionality to create DROP scripts. You can set this option in SQLAzureMW.exe.config or dynamically during runtime.

v3.1.5

Removed search for dbo.sysobjects from tables in NotSupportedByAzureFile.config

Fixed script engine to handle a single TSQL file with no "GO" statements in it

v3.1.4

Modified SQLAzureMW to put "GO" between Tables / Views and their indexes. This is needed when parsing SQLAzureMW TSQL output from a file. Without the "GO", then the indexes were being parsed with Table / View rules thus causeing syntax errors on generated TSQL.

Most, if not all, workarounds in the step-by-step instructions below are unnecessary with v.3.3.3.

• Update 1/23/2010 4:00 PM PST: George Huey released v3.1.4 of his SQL Azure Migration Wizard (SQLAzureMW.exe) to CodePlex on 1/23/2010 with a fix for problems analyzing (parsing) T-SQL scripts from a file. If you don’t want to go through the entire process of analyzing and creating a T-SQL script from the on-premises AdventureWorksLT2008R2 database, I recommend using files from AdventureWorksLT.zip.

It will also analyze SQL Profiler trace files and TSQL script for compatibility issues with SQL Azure

I updated in mid-January 2010 the sections about SQLAzureMW (originally MigWiz) of Chapter 13, “Exploiting SQL Azure Database's Relational Features,” of my Cloud Computing with the Windows Azure Platform book for Wrox/Wiley to point to this post. SQLAzureMW is a moving target, as you can see from the workarounds at the end of this long article, so this post will be updated as George upgrades his Migration Wizard.

This post’s first section demonstrates the most common workflow for duplicating an on-premises SQL Server 2008 R2 database (AdventureWorksLT2008R2) in SQL Azure running in a Microsoft data center.

Note: This post is a preliminary attempt to find workarounds to problems observed in testing SQLAzureMW.exe v3.1.3 with the AdventureWorksLT2008R2 sample database when running on a VM with Windows Server 2008 R2 (no Hyper-V) as the guest OS. The host OS is Windows Server 2008 R2 Hyper-V.

2. Extract the zip files to a local folder, C:\Program Files\SQLAzureMW for this example.

3. Run SQLAzureMW.exe to open the ScriptWizard’s Select Process page.

4. Mark the Analyze and Migrate – SQL Database option, which enables migrating schemas and data to or from SQL Azure databases:

5. Click Next to open the Select Source page with the Connect To Server dialog active.

6. Select the on-premises SQL Server 2008 R2 server instance that contains the AdventureWorksLT2008R2 database from the Server Name list (the WINSRV2008SP2VM virtual machine for this example), accept the use Windows NT Integrated Security and 15 second Connection (timeout) defaults:

7. Click Connect to connect to the instance and open a list of available databases on the Select Source page, and select the source database (AdventureWorksLT2008R2 for this example):

9. Click Next to open the Script Wizard Summary page and expand the nodes to display individual database objects:

10. Click Next and click Yes in the Generate Script message box to open the end of the Result Summary Page:

Note: You can open BCP output files stored as tmp####.tmp in your \Users\UserName\AppData\Local\Temp\1 folder in NotePad or a binary file editor, but native BCP files consist of length followed by data. Data in native BCP files is encoded as Unicode (UTF-16).

Click Save to save the Result Summary as the default .rtf file type in the \Users\UserName\Documents folder.

11. Click the SQL Script tab to display the end of the generated T-SQL script:

Click Save to save the Script as the default .rtf file type (AdventureWorksLT2008R2Script.rtf for this example) in the \Users\UserName\Documents folder.

12. Click Next to open the Setup Target Server Connection with the Connect To Server dialog active with default SQL Azure Server Name, SQL Server security, User Name and Connection (timeout) defaults.

13. Replace SERVER with a copy of your SQL Azure server name in two places and User Name with your administrative user ID, and type your password:

Note: You can avoid the need to retype the Server Name and your administrator credentials by replacing values of the following elements of the SQLAzureMW.exe.config file’s <appSettings> group:

14. Click Connect and Create Database to open the Create Database dialog. Type the name of the SQL Azure database (AdventurWorksLT2008AZ for this example):

15. Click Create Database to add the new database to the list of the server’s databases, click Next to open the Execute Script message box and click Yes to execute the script against the SQL Azure database and display the Target Server Response page:

Click Save to save the Target Server Response as the default .rtf file type in the \Users\UserName\Documents folder.

Errors Reported in the Target Server Response File

The Target Server Response file shows numerous red error messages, which require correction to enable migration of object types that SQL Azure supports. The most serious of these errors occur with the SalesLT.ProductModel table, which contains a ProductDescription column of the xml datatype. SQL Azure supports the xml datatype, but not xml indexes, so creation of the table fails. The missing table cascades several other errors.

Error #: 40512 -- Deprecated feature 'More than two-part column name' is not supported in this version of SQL Server errors appear twice in the Target Server Response .rtf file. These three-part column name errors prevents creation of the SalesLT.uSalesOrderHeader and SalesLT.iduSalesOrderDetail triggers.

When using the SQL Azure Migration Wizard v3.1.3 with the SQL Azure Database December 2009 Release to Web version, it appears the simplest approach is to delete the PXML_ProductModel_CatalogDescription primary XML index from the source table (see this post’s first screen capture). In a production environment, however, deleting a Primary XML index can have serious affect on the performance of xml operations.

You must edit the T-SQL in the to solve the three-part column name errors, so also removing the primary XML index generation statements is a practical alternative:

To edit the T-SQL Script file you created and saved in step 11 of the preceding process, do the following:

1. If you have an AdventureWorksLT2008AZ database, open the SQL Azure portal and delete it. (Although you can delete an existing database of the selected server in a Wizard dialog, you might encounter an unhandled exception.)

2. Launch SQLAzureMW.exe and complete steps 1 through 10 of the preceding section.

3. In the Results Summary dialog with the SQL Script tab selected, scroll to the

--~ Table [SalesLT].[ProductModel] -- CREATE PRIMARY XML INDEX is not supported in current version of SQL Azure

and delete the instructions shown selected here:

4. Scroll to the first instance of the

--~ Table [SalesLT].[SalesOrderHeader] -- Multi-part names ( [SalesLT].[SalesOrderHeader].[RevisionNumber) with Server or Database specifier is not supported in this version of SQL Azure.

comment and remove the three instances of the [SalesLT].[SalesOrderHeader]. prefix, to change the code for the uSalesOrderHead trigger to that shown here:

Also remove the red error line.

5. Scroll to the second instance of the Multi-part names error message and change the code for the iduSalesOrderDetail trigger to that shown here

Also remove the red error line.

6. Click Save and save the script as an .rtf file (AdventureWorksLT2008R2FixScript.rtf for this example) to your Documents folder.

7. Continue with steps 12 through 15 of the preceding section. The dialog now appears as shown here:

8. Open the AdventureWorksLT2008AZ database in SQL Server 2008 R2 SSMS [Express] and verify that the SalesOrderHeader and SalesOrderDetail tables have a trigger.

Thanks to George Huey for providing details of the required workarounds.

To Create a T-SQL ScriptName.sql File from a Saved ScriptName.rtf File:

1. Open \Users\UserName\Documents\AdventureWorksLT2008R2FixScript.rtf (for this example) in WordPad or Microsoft Word (the version isn’t important), and save a backup copy.

2. Save a text file (\Users\UserName\Documents\AdventureWorksLT2008R2FixScript.txt for this example) encoded as a Unicode Text File.

3. Change the file extension from .txt to .sql.

To Create a T-SQL ScriptName.sql File by Copying Text from the Text Box:

1. Make the corrections to the Results Summary text box shown in steps 3, 4 and 5 of the preceding section.

2. With the text box selected, press Ctrl+A and Ctrl+C to copy the contents to the Clipboard.

3. Open a new query in SQL Server 2008 R2 SSMS [Express].

4. Press Ctrl+C to copy the text to the query.

5. Press Ctrl+F5, choose Query | Parse, or click the check mark in the tool bar to parse the query.

6. Save the script (as \Users\UserName\Documents\AdventureWorksLT2008R2FixScript.sql for this example.)

Download Demo Script Files for Testing:

The AdventureWorksLT2008R2FixScript.sql and AdventureWorksLT2008R2FixScriptNoBCP.sql files are available for download and inspection in AdventureWorksLT2008R2FixScript.zip from my SkyDrive folder.

instructions for adding data from temporary BCP native data files that are machine and instance specific. Errors reported as the result of temporary BCP files are non-fatal but time-consuming.

To Use the Saved Script in v3.1.3 with a Temporary Workaround:

1. Launch SQLAzureMW.exe, select the Run TSQL without Analyzing – TSQL FileAnalyze and Migrate – TSQL File option, browse to and select the AdventureWorksLT2008R2FixScript[NoBCP].sql file for this example:

Note: I investigated a problem importing a script file with the Analyze and Migrate – TSQL option, which causes many errors when analyzed, with George Huey. He reports that a bug in v3.1.3 removes some TSQL commands from the file that should be present. Until George releases v3.1.4 with the problem corrected, use the Run TSQL Without Analyzing option.

To Use Saved Scripts After the Bug Fix in v3.1.4:

1. Launch SQLAzureMW.exe, select the Analyze and Migrate – TSQL File option, browse to and select the AdventureWorksLT2008R2FixScript.sql file for this example:

Note: The script file with temporary BCP options included is shown above, but using the “NoBCP” version is recommended.

George Huey has provided an AdventureWorksLT.zip archive with an updated T-SQL script and a set of BCP data files that run without errors in v3.1.3 and v3.1.4. To use these files with v3.1.3 or v3.1.4, do the following:

3. Extract all files to the default subfolder: C:\Temp\AdventureWorksLT. The fixed path is required to process the BCP native data (*.DAT) files in the archive.

4. Follow the instructions in the preceding section, except substitute C:\Temp\AdventureWorks\AdventureWorks.sql for the path and file shown in the preceding screen capture.

5. Verify with SQL Server 2008 R2 SSMS that AdventureWorksLT2008AZ SQL Azure database contains the same objects, including the two database triggers, and data as the original AdventureWorksLT2008R2 on-premises database.

The dual Web role application has been running in Microsoft's South Central US (San Antonio) data center since September 2009. I believe it is the oldest continuously running Windows Azure application.

About Me

I'm a Windows Azure Insider, a retired Windows Azure MVP, the principal developer for OakLeaf Systems and the author of 30+ books on Microsoft software. The books have more than 1.25 million English copies in print and have been translated into 20+ languages.

Full disclosure: I make part of my livelihood by writing about Microsoft products in books and for magazines. I regularly receive free evaluation software from Microsoft and press credentials for Microsoft Tech•Ed and PDC. I'm also a member of the Microsoft Partner Network.