Using the SQL Azure Migration Wizard v3.3.3 with the AdventureWorksLT2008R2 Sample 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:
<add key="BCPFileDir" value="c:\SQLAzureMW\BCPData" /> <!-- Input / Output file location for BCP table data. If blank, then will use temp directory -->
<add key="DelOldBCPFiles" value="true" /> <!-- Deletes old BCP files if exists, otherwise, it will add a unique numeric ext -->
<add key="BCPFileExt" value="dat"/> <!-- BCP output file extension –>v3.2.1
- Added support for User Defined Table Types
v3.2
- Added validation of SQL Azure user name ext @SERVER to make sure that it matches server name SERVER.
v3.1.9
- Fixed code to not throw an exception when trying to generate script on an encrypted stored procedure. A red error message will displayed in the status page.
- Modified code to move non clustered index creation until after data upload
- 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.
Update 1/22/2010 6:00 PM PST: 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. To download and run these files, see the Temporary Workaround for v3.1.3 with a Modified AdventureWorksLT.sql File section at the end of this post.
Update 1/21/2010: Added workarounds for unsupported features (XML indexes and three-part column names) not handled by the Wizard’s NotSupportedByAzure.Config file and a bug related to importing TSQL scripts for execution, which will be fixed in future v3.1.4. See the Dealing with Errors Resulting from Tables Containing xml Columns/Indexes and Three-Part Names section near the end of this lengthy post.
This post is an update of my Using the SQL Azure Migration Wizard with the AdventureWorksLT2008 Sample Database post last updated on 9/21/2009 for George’s v0.2.7 release and covers new requirements and features in v3.1.3 from the SQLAzureMW docx v2.0 documentation file:
Requirements: SQLAzureMW requires SQL Server 2008 R2 bits to run.
Project Details: The SQL Azure Migration Wizard (SQLAzureMW) gives you the options to analyzes, generates scripts, and migrate data (via BCP) from:
It will also analyze SQL Profiler trace files and TSQL script for compatibility issues with SQL Azure
- SQL Server to SQL Azure
- SQL Azure to SQL Server
- SQL Azure to 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.
Installing the AdventureWorksLT2008R2 November 2009 CTP Database
1. Download the source database as AdventureWorks2008R2_NovemberCTP.exe from the AdventureWorks 2008R2 November CTP release of 11/9/2009 on CodePlex.
2. Run AdventureWorks2008R2_NovemberCTP.exe and accept the default script destination folder: C:\Program Files\Microsoft SQL Server\100\Tools\Samples\.
3. Optionally, skip the Database Selection dialog’s Install operation by clicking Cancel and performing the following steps; otherwise click Install and you’re done.
4. Open C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks2008R2LT\instawltdb.sql in SQL Server Management Studio 2008 R2 [Express] and connect to your SQL Server 2008 RS [Express] instance’s master database.
5. Choose Query | SQLCMD Mode to execute the query in SQLCMD mode.
6. Uncomment the two :setvar statements highlighted in the following screen capture by removing the -- prefix to set the script and database file paths:
7. Execute the query and verify that all tables are present and contain data.
Generating an SQL Azure Schema from an On-Premises SQL Server Database
1. Go to CodePlex’s Release page for SQL Azure Migration Wizard v3.1.3 (Beta) of 1/15/2010 and download SQLAzureMW v3.1.3 Release Binary (SQLAzure v3.1.3 Release Binary.zip).
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):
8. Click Next to open the Choose Objects page, mark the Select Specific Database Objects, and mark all object type check boxes except XML Schema Collections, which SQL Azure doesn’t suport:
Optionally, click Advanced to display the Advanced Settings properties list.
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:
<add key="TargetServerName" value="SERVER.database.windows.net"/>
<add key="TargetUserName" value="UserName@SERVER"/>
<add key="TargetPassword" value="YourPassword (optional)"/>
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.
Note: You can download from SkyDrive SQLAzureMW RTF Files.zip, which contains the three .rtf fields shown above.
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.
Dealing with Errors Resulting from Tables Containing xml Columns/Indexes and Three-Part Names
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.
AdventureWorksLT2008R2FixScriptNoBCP.sql omits the
-- BCPArgs:1:.dbo.BuildVersion" in "C:\Users\Administrator\AppData\Local\Temp\1\tmpEB01.tmp" -E -q -n -b 5000
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 File Analyze and Migrate – TSQL File option, browse to and select the AdventureWorksLT2008R2FixScript[NoBCP].sql file for this example:
2. Click Next twice, connect to your SQL Azure server instance. If the existing AdventureWorksLT2008AZ database contains objects, delete and recreate it.
3. Click next to process the script.
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.
2. Click Next twice, connect to your SQL Azure server instance. If the existing AdventureWorksLT2008AZ database contains objects, delete and recreate it.
6. Click next to process the script.
Temporary Workaround for v3.1.3 with a Modified AdventureWorksLT.sql File
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:
1. Create a C:\Temp folder if you don’t already have one.
2. Download the AdventureWorksLT.zip archive from SkyDrive, saving it in the C:\Temp folder.
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.
Atom 1.0 site feed
