THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

AdventureWorks2012 now available for all on SQL Azure

Three days ago I tweeted this:

Evidently I wasn't the only one that thought this was a good idea because as you can see from the screenshot that tweet has, so far, been retweeted more than fifty times. Clearly there is a desire to see the AdventureWorks databases made available for the community to noodle around on so I am pleased to announce that as of today you can do just that - [AdventureWorks2012] now resides on SQL Azure and is available for anyone, absolutely anyone, to connect to and use* for their own means.

*By use I mean "issue some SELECT statements". You don't have permission to issue INSERTs, UPDATEs, DELETEs or EXECUTEs I'm afraid - if you want to do that then you can get the bits and host it yourself.

This database is free for you to use but SQL Azure is of course not free so before I give you the credentials please lend me your ears eyes for a short while longer. AdventureWorks on Azure is being provided for the SQL Server community to use and so I am hoping that that same community will rally around to support this effort by making a voluntary donation to support the upkeep which, going on current pricing, is going to be $119.88 per year. If you would like to contribute to keep AdventureWorks on Azure up and running for that full year please donate via PayPal to adventureworksazure@hotmail.co.uk:

Any amount, no matter how small, will help. If those 50+ people that retweeted me beforehand all contributed $2 then that would just about be enough to keep this up for a year. If the community contributes more than we need then there are a number of additional things that could be done:

  • Host additional databases (Northwind anyone??)
  • Host in more datacentres (this first one is in Western Europe)
  • Make a charitable donation

That last one, a charitable donation, is something I would really like to do. The SQL Community have proved before that they can make a significant contribution to charitable orgnisations through purchasing the SQL Server MVP Deep Dives book and I harbour hopes that AdventureWorks on Azure can continue in that vein. So please, if you think AdventureWorks on Azure is something that is worth supporting please make a contribution.


OK, with the prickly subject of begging for cash out of the way let me share the details that you need to connect to [AdventureWorks2012] on SQL Azure:

  • Server mhknbn2kdz.database.windows.net 
  • Database AdventureWorks2012
  • User sqlfamily
  • Password sqlf@m1ly

That user sqlfamily has all the permissions required to enable you to query away to your heart's content. Here is the code that I used to set it up:

CREATE USER sqlfamily FOR LOGIN sqlfamily;
CREATE ROLE sqlfamilyrole;
EXEC sp_addrolemember 'sqlfamilyrole','sqlfamily';
GRANT VIEW DEFINITION ON Database::AdventureWorks2012 TO sqlfamilyrole;
GRANT VIEW DATABASE STATE ON Database::AdventureWorks2012 TO sqlfamilyrole;
GRANT SHOWPLAN TO sqlfamilyrole;
EXEC sp_addrolemember 'db_datareader','sqlfamilyrole';

You can connect to the database using SQL Server Management Studio (instructions to do that are provided at Walkthrough: Connecting to SQL Azure via the SSMS) or you can use the web interface at https://mhknbn2kdz.database.windows.net:

Lastly, just for a bit of fun I created a table up there called [dbo].[SqlFamily] into which you can leave a small calling card. Simply execute the following SQL statement (changing the values of course):

INSERT [dbo].[SqlFamily]([Name],[Message],[TwitterHandle],[BlogURI])
VALUES ('Your name here','Some Message','your twitter handle (optional)','Blog URI (optional)');

[Id] is an IDENTITY field and there is a default constraint on [DT] hence there is no need to supply a value for those.

Note that you only have INSERT permissions, not UPDATE or DELETE so make sure you get it right first time! Any offensive or distasteful remarks will of course be deleted :)

Thank you for reading this far and have fun using AdventureWorks on Azure. I hope it proves to be useful for some of you.

@jamiet

AdventureWorks on Azure - Provided by the SQL Server community, for the SQL Server community!

Published Tuesday, March 27, 2012 10:52 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

John Pertell said:

What a great idea! Thanks for setting it up!

March 27, 2012 11:39 AM
 

Chris Webb said:

Wouldn't it be good to get it hosted on Azure DataMarket too? Boyan Penev got his DateStream date dimension table on there:

http://www.bp-msbi.com/tag/datamarket/

https://datamarket.azure.com/dataset/1542c52d-d466-4094-a801-9ef8fd7c6485

March 27, 2012 4:27 PM
 

Tuan Tran said:

Brilliant idea Jamie.  Thx.

March 27, 2012 6:20 PM
 

Jim said:

I'll probably never use it, but I think it's a great idea, so I sent you some money. Cheers!

April 1, 2012 10:08 AM
 

jamiet said:

Jim,

That's great, thank you very much - a generous amount too. It is very much appreciated.

Regards

Jamie

April 1, 2012 10:14 AM
 

SSIS Junkie : Big AdventureWorks2012 said:

April 2, 2012 6:19 PM
 

SSIS Junkie : Northwind now available on SQL Azure said:

April 10, 2012 5:08 PM
 

Andy Hayes said:

Great idea Jamie.

I have not used SQL Azure until now. Donation is on its way and I've just created a post on my blog about it. Hope this generates some more support for you.

http://www.dbadiaries.com/if-you-want-to-try-sql-azure-then-this-is-how-you-can-do-it/

All the best.

Andy

April 17, 2012 10:28 AM
 

Thomas said:

Jamie,

Thank you for doing this. I'm heading to PayPal right now. Great idea indeed!

April 17, 2012 11:39 AM
 

jamiet said:

Thomas/Andy,

Thanks very much guys, both for the comments and the donations.

JT

April 17, 2012 11:41 AM
 

SSIS Junkie said:

I recently overheard a remark by Greg Low in which he said something akin to "the most interesting parts

May 10, 2012 5:14 AM
 

Bill Anton said:

Jamie,

I've been messing around with SQL Azure Reporting recently and built a few reports based on your dataset.  My understanding is that your account will get charged for outbound data since my report server is (I'm assuming) in a different region than your SQL Azure database.  

Can you share which region you're database is in?

May 15, 2012 3:31 PM
 

jamiet said:

Hi Bill,

As far as I am aware there is no charge for outbound data from SQL Azure:

Pricing and Metering for SQL Azure Database

(https://www.windowsazure.com/en-us/home/features/sql-azure/)

Regards

Jamie

May 15, 2012 5:48 PM
 

Bill Anton said:

Gotcha...so it looks like the fee for the database is only based on size.  I assumed that SQL Azure was a part of the Windows Azure subscription...and SQL Azure Reporting was a Windows Azure service and therefore was subject to the transfer rates associated with Windows Azure...

below is a link and the statement that led me down this path...

https://www.windowsazure.com/en-us/pricing/details/

"Data transfers are charged based on the total amount of data moving in and out of the Windows Azure platform datacenters via the internet in a given billing period. Data transfers between Windows Azure services located within the same sub-region are not subject to charge. Data transfers between sub-regions are charged at normal rates on both sides of the transfer."

May 25, 2012 8:11 AM
 

jamiet said:

Yeah, I'm led to believe that that only applies to Azure storage, not SQL Azure. Not exactly unambiguous though is it?

May 25, 2012 8:40 AM
 

Jeff Olmstead said:

I also deployed this to Azure (though I think I will just utilize yours as you have NorthWind - nice).  One problem with the Azure 2012 database I see is that the SalesOrderHeader.SalesOrderID primary key does not line up with the SalesOrderDetail.SalesOrderID (it does in the 2008 version, but not the Windows Azure version). Looks like there might be some other primary key issues according to the comments on codeplex. Just wanted to give any user here an FYI and looking forward to when it is fixed - will be great.

August 30, 2012 11:43 AM
 

jamiet said:

"One problem with the Azure 2012 database I see is that the SalesOrderHeader.SalesOrderID primary key does not line up with the SalesOrderDetail.SalesOrderID"

Really? <sigh> That Microsoft themselves can't get simple things like FKs in place actually make me rather sad.

August 30, 2012 11:57 AM
 

SSIS Junkie said:

I have used the same email providers for my own domains for a few years now however I am considering

October 18, 2012 8:02 AM
 

SSIS Junkie said:

Are you looking around for some decent test data for your BI demos? Well, if so, Microsoft have provided

November 29, 2012 6:19 PM
 

SSIS Junkie said:

Just over 12 months ago I published a blog post entitled AdventureWorks2012 now available for all on

April 7, 2013 5:02 PM
 

SSIS Junkie said:

In March 2013 I launched an initiative called AdventureWorks on Azure in which I hosted the AdventureWorks2012

May 20, 2013 5:48 PM
 

Kalyan Arangam said:

Thanks Jamie.

for anyone connecting through SSMS and getting "Cannot open database "master" requested by the login".

1. Click Options

2. Click "Additional Connection Parameters" tab

3. enter "Initial catalog=AdventureWorks2012"

4. Click Connect

Worked for me!

Cheers,

Kalyan

September 27, 2013 6:27 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement