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 15, 2012 at 12:30 AM by scottkl, version 2

Comments

TheDanishDynamo Jul 5 at 9:35 PM 
@BobAshworth562 looks like the creators of AdventureWorks2014 left some room for improvement or refactoring. Thank god we don't live in a perfect world!!

BobAshworth562 May 31 at 3:59 PM 
The Person.ContactType table does not allow for the Types "Vendor" and "Customer". This must be an oversite, since AdventureWorks sells both through vendors of the bikes as well as anyone who purchases by the internet. The use of Person... as a schema item implies that of a larger scope than merely the compnies employees and vendor/representatives. Perhaps that is why the appearance of person.password and person.EmailAddress seem so foreign to the rest of the DB design. More like an online customer logon than an intra-business communication. The generic "person" looks like a steal from a general purpose contacts listing. One author of an AdventureWorks oriented Database design went so far as to create a table called "Purchasing.VendorAddress". to prove a point about GROUP BY and HAVING Clauses. It seems the book was self-published and perhaps not thoroughly edited. The SELECT QUERY, of course, fails. I'm still trying to figure out how the query could work, but the overlapping meanings of person, purchasing and no financial representation keep getting in the way. Thanks anyway for the queries that do work and teach.

jmorisi555 Dec 11, 2015 at 5:24 PM 
Is there a single installer for this version (2012) like there was for 2008?
https://msftdbprodsamples.codeplex.com/downloads/get/96448