Migration Notes

Introduction

Scripting the AdventureWorks database community sample database with the default scripting options generates a script that when executed against SQL Azure without modification will not work. The option to script objects for the SQL Azure database engine type eleviates this issue, but it helps to know what changes were made to the script to make it "Azure Compatible". Thus, the Full AdventureWorks2012 download on this page was scripted for the SQL Server 2008R2 database engine and the following changes were applied to the script to allow the script to run successfully on SQL Azure:

Script Options

  • Objects Included in Script Generation
    • Tables
    • Views
    • Stored Procedures
    • User-Defined Fuctions
    • User-Defined Data Types
    • Schemas
  • Objects Excluded in Script Generation
    • DDL Triggers
    • Full Text Catalog
    • XML Schema Collections

Syntax Adjustments

The following options, statements, and T-SQL references were removed from the script to enable it to run successfully in SQL Azure:
  • Removed all ON PRIMARY options
  • Removed all PAD_INDEX options
  • Removed all ALLOWROWLOCKS options
  • Removed all ALLOWPAGELOCKS options
  • Removed all SORTINTEMPDB options
  • Removed all CREATE PRIMARY XML INDEX statements
  • Removed all references to XML schema collections on XML data type columns
  • Removed all TEXTIMAGE_ON options
  • Removed all ROWGUIDCOL identifier on UniqueIdentifier column data types
  • Removed all NOT FOR REPLICATION option from tables and stored procedures
  • On the Production.ProductProductPhoto table, changed: PRIMARY KEY NONCLUSTERED to PRIMARY KEY CLUSTERED (every table needs a clustered index).
  • Excluded the stored procedure uspSearchCandidateResumes as it references the unsupported functions FREETEXTTABLE and CONTAINSTABLE.
  • Excluded the view vAdditionalContactInfo. The XQuery method value() was generating an compile error. This is under investigation.

Last edited Feb 14, 2012 at 11:30 PM by scottkl, version 2

Comments

No comments yet.