Scott Klein

  Home  |   Contact  |   Syndication    |   Login
  40 Posts | 0 Stories | 32 Comments | 0 Trackbacks

News

Twitter












Tag Cloud


Archives

Post Categories

Some Background

The AdventureWorks database has been around for over a decade; a staple amongst sample databases. The first version of the AdventureWorks database appeared in time for SQL Server 2000. Microsoft has been good at keeping the AdventureWorks sample database up to date as new versions of SQL Server are released. Case-in-point: SQL Server 2012 is at RC0 and yet you can already find a version of AdventureWorks for it (albeit, it really isn’t that different from the SQL Server 2008 R2 version). They even have multiple versions depending on your needs (Data Warehouse, LT, OLAP, etc).

As a Corporate Technical Evangelist for SQL Azure and somewhat new to Microsoft recently, I was even glad to see a version for SQL Azure. Added to CodePlex in late 2009, the current zip file, AdventureWorks2008R2AZ, contains an install for two databases based on the AdventureWorks database; a small data warehouse database and a small light version of the full AdventureWorks database. However, neither of these database are the full AdventureWorks database that we know and love, so I set out to solve that and make a version for SQL Azure that utilizes the full AdventureWorks database. And, while I was at it, with all of the hype and talk surrounding SQL Azure Federations, I thought it would also be nice to see a Federated version of the AdventureWorks database.

Exciting News

Thus, I am happy to let you know of two new additions to the SQL Azure samples page on CodePlex. Starting today, two new installs are available. The full AdventureWorks database for SQL Azure, and a SQL Azure Federation version of the full AdventureWorks database are now available and can be downloaded from here:

http://msftdbprodsamples.codeplex.com/releases/view/37304

I’ll spend a few minutes and discuss these two databases individually regarding why the efforts were taken to migrate them to SQL Azure and what we hope you will get from them.

Full AdventureWorks for SQL Azure

As far as sample databases go, the AdventureWorks database is the king. It exists simply, yet elegantly, to illustrate the features and functionality of its corresponding version of SQL Server. As such, migrating the full version of the AdventureWorks database to SQL Azure was a must, in part for the following reasons:

· SQL Server as a Service – The primary goal of taking the AdventureWorks database and migrating it to SQL Azure is to show that SQL Azure is SQL Server served up as a PaaS service. Obviously there are some differences in the logical vs. physical administration aspects but the bottom line is that SQL Azure is a cloud-based relational database service that is built on SQL Server technologies, and what better way to prove that by taking an existing on-premises database and showing how easy it is to migrate it to SQL Azure.

· Supported Functionality and Migration Strategies – AS SQL Azure gains adoption, the question continues to exists as to what does it take to migrate an existing on-premises database to SQL Azure, and what functionality is, and is currently not, supported and the steps necessary in the migration process. This example answers those questions.

Everything that needed to be modified, changed, or removed to ensure support for SQL Azure has been documented on the CodePlex page for this database. For example, all ON PRIMARY statements have been removed, and we explain this and the reasons why on the CodePlex page. We list these out so you’ll have an idea of what was needed in order get the AdventureWorks database into SQL Azure.

Given that this is the first fore of the full AdventureWorks database into SQL Azure means there is much more to come.

Full AdventureWorks with SQL Azure Federations

SQL Azure Federations was launched December of 2011. There wasn’t a whole lot of fanfare when it was released but those who have been keeping up with SQL Azure were and certainly are aware of its existence, simply because Microsoft has been talking about it for well over a year. Thus, creating a Federated version of the AdventureWorks database for SQL Azure was also a must with the following thoughts in mind:

· Traction – What a better way to keep momentum going for SQL Azure Federations than to take a well-known sample database and Federate it! Developers can now look at a long-existing sample database that has been federated and use that as a starting point to understanding and working with SQL Azure Federations.

· Example – With SQL Azure Federations so new it makes sense to provide a real-life example on how to Federate an existing database.

· Coolness – Honestly, seeing a Federated version of the AdventureWorks database is just cool. Really.

The current Federated version of the AdventureWorks database federates on Customer. We specifically selected to federate on Customer because it provides a great base to build from. There were several tenants we could have federated on, such as Products or People, but for a first cut, and to help the “transition” into understanding Federations, we decided to start somewhat simple.

The installs for both of the databases, the full non-federated and the federated, is quite easy. Once installed you will be able to see all the databases in SQL Server Management Studio Object Explorer, including the Federation Member as shown in the following figure.

clip_image001

Even cooler is that you can manager your Federations via the SQL Azure Management Portal as shown in the Figure below.

clip_image003

What’s Next

We are already in the process of creating additional, more advanced, versions of this database, which you will see in the coming weeks and months.

Conclusion

As features and functionality is added to SQL Azure, these databases will be updated correspondingly.

Long Live the AdventureWorks database! Love it, use it!

posted on Tuesday, February 14, 2012 5:09 PM

Feedback

# re: Full Version of AdventureWorks database for SQL Azure and and SQL Azure Federations 2/18/2012 2:38 PM David Walker
Happy Valentines Day indeed! :)

Great work Scott! I am looking forward to your updates as well!

Post A Comment
Title:
Name:
Email:
Comment:
Verification: