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:
v 0.2.6
- Modified code to parse index when loading from file
- Modified NotSupportedByAzureFile.config to correct Regex expression for index options.
You must manually add a clustered index primary key constraint to SSMS’s Script Wizard’s output for the AdventureWorksLT2008’s BuildVersion table as shown emphasized (bold) in the example below:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BuildVersion]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[BuildVersion]( [SystemInformationID] [tinyint] IDENTITY(1,1) NOT NULL, [Database Version] [nvarchar](25) NOT NULL, [VersionDate] [datetime] NOT NULL, [ModifiedDate] [datetime] NOT NULL, /******* Added 9/21/2009 RJ ******/ CONSTRAINT [PK_BuildVersion_SystemInformationID] PRIMARY KEY CLUSTERED ( [SystemInformationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]END GO
If you don’t add the constraint, you will encounter many errors when attempting to generate the SQL Azure database.
The Attempting to Run a Raw SSMS-Created Script That Populates Table Data section has been renamed Running an SSMS-Created Script That Populates Table Data.
•• Update 9/19/2009: George’s v0.2.6 release solves the problems reported in the Attempting to Run a Raw SSMS-Created Script That Populates Table Data section. George’s Release Notes say:
v 0.2.6
- 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.
• Update 9/8/2009: Added promised Attempting to Run a Raw SSMS-Created Script That Populates Table Data section.
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.
The second section, Attempting to Run a Raw SSMS-Created Script That Populates Table Data, describes an unsuccessful attempt to process a script file generated by SSMS’s Script Wizard that includes INSERT statements for adding data.
Note: Reducing the size of screen captures doesn’t cause a significant reduction in page size or loading time.
Generating an SQL Azure Schema from an On-Premises SQL Server Database
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.
7. Expand the Script Wizard Summary page’s nodes to review your selections, options, and objects.
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.
After fixing the two preceding issues, your response turns all green.
Running an SSMS-Created Script That Populates Table Data
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:
Atom 1.0 site feed
