Monday, September 21, 2009

George Huey’s SQL Azure Migration Wizard (MigWiz) 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. You can learn more about MigWiz and watch a screencast from Wade Wegner’s SQL Azure Migration Wizard post of 9/1/2009.

••Update 9/21/2009: There were still a few problems remaining with processing the raw AdventureWorksLT2008.sql script from SSMS that George’s v0.2.7 release solves:

Implemented suggestion from rogerj to give the user the option to parse a TSQL file for incompatibilities

Fixed parsing for Multi-part names with Server or Database specifier

Modified parsing for ROWGUIDCOL to leave uniqueidentifier

Modified the way a file is loaded (1000x performance increase)

Note, that when parsing for “text” to replace with “varchar(max)”, if you have a comment in your code (i.e., Take user’s text and save it), then text will be changed to varchar(max). If this causes you lots of problems, take the search for text out of the TSQL section of NotSupportedByAzureFile.config.

I wanted to add a few sections about the MigWiz to Chapter 13, “Exploiting SQL Azure Database's Relational Features,” of my forthcoming Cloud Computing with the Windows Azure Platform book for Wrox/Wiley, so I gave it a test drive with the AdventureWorksLT2008 sample database over the Labor Day weekend.

This post’s first section demonstrates the most common workflow for duplicating an on-premises SQL Server 2008 schema (but not data) for the AdventureWorksLT2008 in SQL Azure running in a Microsoft data center. In the process, I detected and worked around a few problems with MigWiz v.0.2.3 of 9/2/2009.

1. Run SQLAzureMW.exe to open the ScriptWizard page and click Connect to open the Connect to SQL Server dialog. With the default SQL Server tab active, select the server instance in the Server Name list, accept the default Use Windows NT Integrated Security option and 15-second Connection timeout, and click Connect to connect to the instance and open the Select Source page.

2. Select the on-premises server’s database that you want to script in the Select Source page and click Next to open the Choose Script Options page.

3. Accept the default options in the Choose Script Options page and click Next to display the Select Object Types page.

4. Click Select All and then clear the User Defined Data Types and XML Schema Collections check boxes, because the current SQL Azure CTP doesn’t support these object types:

Click Next to open the Choose Schemas page.

5. AdventureWorksLT2008 has only a single SalesLT schema, so select it.

Click Next to open the Choose Stored Procedures page, click Select All, and click Next to open the Tables page.

6. Click Select All to choose all tables for scripting.

Click Next to open the User Defined Functions page, click Select All, click Next to open the Views page, click Select All to choose all Views, click Next to open the Output Option page, accept the default Script to Window / SQL Azure option, and click Next to open the Script Wizard Summary page.

8. Click Script to generate the script, process it with the regular expressions from the NotSupportedByAzureFile.Config file, and display the corrected script with annotations in the Results Summary page:

Problems with SeverityLevel=”2”, if any, appear in red.

9. Click Next to open the Setup SQL Azure Connection page to set up for executing the script on SQL Azure running in the DataCenter of your choice by pasting the server protocol:name from the connection string, and entering the User Name and Password. Click Test Connection to display the combo list and select the previously created but empty AdventureWorksLT2008 database in the list:

If you didn’t create the database, type its name in the combo list. Click Script to execute the script against the SQL Azure cloud instance and display the SQL Azure Response page.

10. You’ll see an error in the SQL Azure Response page of v.0.2.3 of 9/2/2009 associated with an attempt to create an XML index on the ProductModel.CatalogDescription column, which SQL Azure doesn’t support:

There’s only a single instance of this error, so it’s easier to remove the offending instruction from the script than to write the regex.

11. You’ll also see another error near the end of the response caused by not detecting a PAD_INDEX = OFF directive in the CREATE UNIQUE CLUSTERED INDEX statement for the vProductAndDescription view:

This is another instance where simply removing the offending directives solves the problem: SORT_IN_TEMPDB = OFF must also be removed. The better choice is to remove the entire statement.

Manually editing T-SQL scripts generated by SSMS’s Script Wizard is tedious at best. MigWiz v0.2.6 and later support T-SQL scripts created by SSMS’s Script Wizard. Here are the SSMS Script Wizard options I set to create the 5-MB, 6,590-line Raw_AdventureWorks2008LT_Azure.sql file, which is also included in the AdventureWorks2008LT_Azure.zip file on SkyDrive:

Here’s a list of the objects generated by the script:

Executing the script on SQL Azure running in the Production Fabric involves these steps:

1. Add the missing clustered primary key index constraint to the BuildVersion table as described near the beginning of this post, and save Raw_AdventureWorks2008LT.sql.

2. Click the Text File tab of the opening MigWiz dialog, mark the Parse TSQL for SQL Azure Incompatibles check box, click the Browse button, navigate to the location where you saved Raw_AdventureWorks2008LT.sql, and open it:

Click Next and wait while MigWiz crunches the file.

3. The script displayed in the SQL Script pane includes comments indicating unsupported T-SQL constructs, which have been removed by the regex instruction in the NotSupportedByAzureFile.Config file. The Result Summary pane is empty.

Click Next to open the Setup SQL Azure Connection page.

4. Complete the SAL Azure Connection page:

Click Next and wait for processing to complete to open the SQL Azure Response page. (Processing stops after encountering a large number of errors).

5. Making the modification to the Raw_AdventureWorks2008LT_Azure.sql results in only a few non-fatal errors near the end of the script:

Using the SQL Azure Migration Wizard with the AdventureWorksLT2008 Sample Database

George Huey’s SQL Azure Migration Wizard (MigWiz) 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. You can learn more about MigWiz and watch a screencast from Wade Wegner’s SQL Azure Migration Wizard post of 9/1/2009.

••Update 9/21/2009: There were still a few problems remaining with processing the raw AdventureWorksLT2008.sql script from SSMS that George’s v0.2.7 release solves:

Implemented suggestion from rogerj to give the user the option to parse a TSQL file for incompatibilities

Fixed parsing for Multi-part names with Server or Database specifier

Modified parsing for ROWGUIDCOL to leave uniqueidentifier

Modified the way a file is loaded (1000x performance increase)

Note, that when parsing for “text” to replace with “varchar(max)”, if you have a comment in your code (i.e., Take user’s text and save it), then text will be changed to varchar(max). If this causes you lots of problems, take the search for text out of the TSQL section of NotSupportedByAzureFile.config.

I wanted to add a few sections about the MigWiz to Chapter 13, “Exploiting SQL Azure Database's Relational Features,” of my forthcoming Cloud Computing with the Windows Azure Platform book for Wrox/Wiley, so I gave it a test drive with the AdventureWorksLT2008 sample database over the Labor Day weekend.

This post’s first section demonstrates the most common workflow for duplicating an on-premises SQL Server 2008 schema (but not data) for the AdventureWorksLT2008 in SQL Azure running in a Microsoft data center. In the process, I detected and worked around a few problems with MigWiz v.0.2.3 of 9/2/2009.

1. Run SQLAzureMW.exe to open the ScriptWizard page and click Connect to open the Connect to SQL Server dialog. With the default SQL Server tab active, select the server instance in the Server Name list, accept the default Use Windows NT Integrated Security option and 15-second Connection timeout, and click Connect to connect to the instance and open the Select Source page.

2. Select the on-premises server’s database that you want to script in the Select Source page and click Next to open the Choose Script Options page.

3. Accept the default options in the Choose Script Options page and click Next to display the Select Object Types page.

4. Click Select All and then clear the User Defined Data Types and XML Schema Collections check boxes, because the current SQL Azure CTP doesn’t support these object types:

Click Next to open the Choose Schemas page.

5. AdventureWorksLT2008 has only a single SalesLT schema, so select it.

Click Next to open the Choose Stored Procedures page, click Select All, and click Next to open the Tables page.

6. Click Select All to choose all tables for scripting.

Click Next to open the User Defined Functions page, click Select All, click Next to open the Views page, click Select All to choose all Views, click Next to open the Output Option page, accept the default Script to Window / SQL Azure option, and click Next to open the Script Wizard Summary page.

8. Click Script to generate the script, process it with the regular expressions from the NotSupportedByAzureFile.Config file, and display the corrected script with annotations in the Results Summary page:

Problems with SeverityLevel=”2”, if any, appear in red.

9. Click Next to open the Setup SQL Azure Connection page to set up for executing the script on SQL Azure running in the DataCenter of your choice by pasting the server protocol:name from the connection string, and entering the User Name and Password. Click Test Connection to display the combo list and select the previously created but empty AdventureWorksLT2008 database in the list:

If you didn’t create the database, type its name in the combo list. Click Script to execute the script against the SQL Azure cloud instance and display the SQL Azure Response page.

10. You’ll see an error in the SQL Azure Response page of v.0.2.3 of 9/2/2009 associated with an attempt to create an XML index on the ProductModel.CatalogDescription column, which SQL Azure doesn’t support:

There’s only a single instance of this error, so it’s easier to remove the offending instruction from the script than to write the regex.

11. You’ll also see another error near the end of the response caused by not detecting a PAD_INDEX = OFF directive in the CREATE UNIQUE CLUSTERED INDEX statement for the vProductAndDescription view:

This is another instance where simply removing the offending directives solves the problem: SORT_IN_TEMPDB = OFF must also be removed. The better choice is to remove the entire statement.

Manually editing T-SQL scripts generated by SSMS’s Script Wizard is tedious at best. MigWiz v0.2.6 and later support T-SQL scripts created by SSMS’s Script Wizard. Here are the SSMS Script Wizard options I set to create the 5-MB, 6,590-line Raw_AdventureWorks2008LT_Azure.sql file, which is also included in the AdventureWorks2008LT_Azure.zip file on SkyDrive:

Here’s a list of the objects generated by the script:

Executing the script on SQL Azure running in the Production Fabric involves these steps:

1. Add the missing clustered primary key index constraint to the BuildVersion table as described near the beginning of this post, and save Raw_AdventureWorks2008LT.sql.

2. Click the Text File tab of the opening MigWiz dialog, mark the Parse TSQL for SQL Azure Incompatibles check box, click the Browse button, navigate to the location where you saved Raw_AdventureWorks2008LT.sql, and open it:

Click Next and wait while MigWiz crunches the file.

3. The script displayed in the SQL Script pane includes comments indicating unsupported T-SQL constructs, which have been removed by the regex instruction in the NotSupportedByAzureFile.Config file. The Result Summary pane is empty.

Click Next to open the Setup SQL Azure Connection page.

4. Complete the SAL Azure Connection page:

Click Next and wait for processing to complete to open the SQL Azure Response page. (Processing stops after encountering a large number of errors).

5. Making the modification to the Raw_AdventureWorks2008LT_Azure.sql results in only a few non-fatal errors near the end of the script:

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.