Search results matching tag 'AdventureWorks on Azure'

AdventureWorks on Azure now hosted by Red Gate software

jamiet — Mon, 20 May 2013 21:48:39 GMT

In March 2013 I launched an initiative called AdventureWorks on Azure in which I hosted the AdventureWorks2012 sample database on Azure so that the SQL Server community at large could use it to get familiar with what ~~SQL Azure~~ Windows Azure SQL Database had to offer. I asked the SQL community to support the initiative by donating money to keep it afloat and that community responded in spades to the point where last month I was able to make a donation to the War Child charity on behalf of that community. In the interim period I added more sample data to the pot, made Northwind available, and also data related to Olympics medal tables down the years.

Today AdventureWorks on Azure takes on a new guise, Steve Jones of SQL Server Central fame announced via a press release that hosting of the database is now to be taken on by his employer Red Gate Software. Here’s what Steve had to say in today’s announcement:

One of the goals of Red Gate Software is that we not only sell products to the SQL Server community, but that we also participate in the community. In line with our goals of participating in the community, we have agreed to host the AdventureWorks 2012 sample database on the Azure platform.
Red Gate has worked to transition this set of databases to our own subscription and will assume the financial responsibility for making this available for the community to use.
http://www.sqlservercentral.com/articles/Adventureworks/99290/

This is great news. I no longer have to worry about finding the funds to keep the initiative afloat and the community can feel comfortable that this will be around for the foreseeable future, so thank you to Steve and red gate for this undertaking. Special thanks must also go to David Atkinson at Red Gate who first suggested that they take this on.

I have high hopes that Steve & Red Gate will build upon this with more offerings for the community and equally I also hope I can stay involved somehow. Watch this space!

@Jamiet

AdventureWorks on Azure donates GBP351.49 to War Child. THANK YOU!

jamiet — Sun, 07 Apr 2013 21:02:35 GMT

Just over 12 months ago I published a blog post entitled AdventureWorks2012 now available for all on SQL Azure. In it I explained that I had set up a Windows Azure SQL Database (then known as SQL Azure database) for the SQL Server community to use and hence familiarise themselves with the SQL Azure offering – I called this initiative “AdventureWorks on Azure”. Judging by the comments that were left in the table that I set up for that very purpose it seems that a number of people were in favour and made use of it:

The credentials to enable you to connect up and leave your own comment are in that same blog post!

Running a SQL Azure database is of course not free hence I asked for the community to support the initiative by contributing via PayPal to its upkeep. I hoped that there might be enough left over to make a charitable donation and as you have probably gathered from the title of this blog post I’m delighted to be able to say that that did indeed happen. A few minutes ago I made a contribution to War Child (the same charity that the SQL Server MVP Deep Dives book donated to) of £351.49

What do War Child do? In their own words:

We look forward to a world in which children's lives aren't torn apart by war.

There's 27 of us in an old false-teeth factory in north London.
We're trying to change the world.

You could say we're a small charity with big ambitions.

We're directly transforming the lives of tens of thousands of children. And we're campaigning to improve the lives of millions more.

Thank you to everyone that made a donation especially to the extremely generous person (who shall remain nameless – you know who you are) that contributed $105.

I would like to keep AdventureWorks on Azure going for another year at least but to do that I need more donations. If you would like to support this initiative for another year take a read of the instructions below that I have copied from last year’s blog post:

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.

Thank you once again to everyone that donated!

@Jamiet

Olympics data available for all on Windows Azure SQL Database and Power View

jamiet — Thu, 29 Nov 2012 23:19:37 GMT

Are you looking around for some decent test data for your BI demos? Well, if so, Microsoft have provided some data about all medals won at the Olympics Games (1900 to 2008) at OlympicsData workbook - Excel, SSIS, Azure sample; it provides analysis over athletes, countries, medal type, sport, discipline and various other dimensions. The data has been provided in an Excel workbook along with instructions on how to load the data into a Windows Azure SQL Database using SQL Server Integration Services (SSIS).

Frankly though, the rigmarole of standing up your own ~~Windows Azure SQL Database~~ ok, SQL Azure database, is both costly (SQL Azure isn’t free) and time consuming (the provided instructions aren’t exactly an idiot’s guide and getting SSIS to work properly with Excel isn’t a barrel of laughs either). To ease the pain for all you BI folks out there that simply want to party on the data I have loaded it all into the SQL Azure database that I use for hosting AdventureWorks on Azure.

You can read more about AdventureWorks on Azure below however I’ll summarise here by saying it is a SQL Azure database provided for the use of the SQL Server community and which is supported by voluntary donations.

To view the data the credentials you need are:

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

Type those into SSMS and away you go, the data is provided in four tables [olympics].[Sport], [olympics].[Discipline], [olympics].[Event] & [olympics].[Medalist]:

I figured this would be a good candidate for a Power View report so I fired up Excel 2013 and built such a report to slice’n’dice through the data – here are some screenshots that should give you a flavour of what is available:

A view of all the available data

Where do all the gymastics medals go?

Which countries do top ten all-time medal winners come from?

You get the idea. There is masses of information here and if you have Excel 2013 handy Power View provides a quick and easy way of surfing through it. To save you the bother of setting up the Power View report yourself you can have the one that I took these screenshots from, it is available on my SkyDrive at OlympicsAnalysis.xlsx so just hit the link and download to play to your heart’s content. Party on, people!

As I said above the data is hosted on a SQL Azure database that I use for hosting “AdventureWorks on Azure” which I first announced in March 2013 at AdventureWorks2012 now available for all on SQL Azure. I’ll repeat the pertinent parts of that blog post here:

I am pleased to announce that as of today … [AdventureWorks2012] now resides on SQL Azure and is available for anyone, absolutely anyone, to connect to and use for their own means.

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.

I’d like to emphasize that last point. If my hosting this Olympics data is useful to you please support this initiative by donating. Thanks in advance.

@Jamiet

Northwind now available on SQL Azure

jamiet — Tue, 10 Apr 2012 21:08:14 GMT

Two weeks ago I made available a copy of [AdventureWorks2012] on SQL Azure and published credentials so that anyone from the SQL community could connect up and experience SQL Azure, probably for the first time. One of the (somewhat) popular requests thereafter was to make the venerable Northwind database available too so I am pleased to say that as of right now, Northwind is up there too.

You will notice immediately that all of the Northwind tables (and the stored procedures and views too) have been moved into a schema called [Northwind] – this was so that they could be easily differentiated from the existing [AdventureWorks2012] objects.

I used an SQL Server Data Tools (SSDT) project to publish the schema and data up to this SQL Azure database; if you are at all interested in poking around that SSDT project then I have made it available on Codeplex for your convenience under the MS-PL license – go and get it from https://northwindssdt.codeplex.com/.

Using SSDT proved particularly useful as it alerted me to some aspects of Northwind that were not compatible with SQL Azure, namely that five of the tables did not have clustered indexes:

The beauty of using SSDT is that I am alerted to these issues before I even attempt a connection to SQL Azure. Pretty cool, no? Fixing this situation was of course very easy, I simply changed the following primary keys from being nonclustered to clustered:

[PK_Region]
[PK_CustomerDemographics]
[PK_EmployeeTerritories]
[PK_Territories]
[PK_CustomerCustomerDemo]

If you want to connect up then here are the credentials that you will need:

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

You will need SQL Server Management Studio (SSMS) 2008R2 installed in order to connect or alternatively simply use this handy website: https://mhknbn2kdz.database.windows.net which provides a web interface to a SQL Azure server.

Do remember that hosting this database is not free so if you find that you are making use of it please help to keep it available by visiting Paypal and donating any amount at all to adventureworksazure@hotmail.co.uk. To make this easy you can simply hit this link and the details will be completed for you – all you have to do is login and hit the “Send” button. If you are already a PayPal member then it should take you all of about 20 seconds!

I hope this is useful to some of you folks out there. Don’t forget that we also have more data up there than in the conventional [AdventureWorks2012], read more at Big AdventureWorks2012.

@Jamiet

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

Big AdventureWorks2012

jamiet — Mon, 02 Apr 2012 22:19:00 GMT

Last week I launched AdventureWorks on Azure, an initiative to make SQL Azure accessible to anyone, in my blog post AdventureWorks2012 now available for all on SQL Azure. Since then I think its fair to say that the reaction has been lukewarm with 31 insertions into the [dbo].[SqlFamily] table and only 8 donations via PayPal to support it; on the other hand those 8 donators have been incredibly generous and we nearly have enough in the bank to cover a full year’s worth of availability.

It was always my intention to try and make this offering more appealing and to that end I have used an adapted version of Adam Machanic’s make_big_adventure.sql script to massively increase the amount of data in the database and give the community more scope to really push SQL Azure and see what it is capable of. There are now two new tables in the database:

[dbo].[bigProduct] with 25200 rows
[dbo].[bigTransactionHistory] with 7827579 rows

The credentials to login and use AdventureWorks on Azure are as they were before:

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

Remember, if you want to support AdventureWorks on Azure simply click here to launch a pre-populated PayPal Send Money form - all you have to do is login, fill in an amount, and click Send. We need more donations to keep this up and running so if you think this is useful and worth supporting, please please donate.

I mentioned that I had to adapt Adam’s script, the main reasons being:

Cross-database queries are not yet supported in SQL Azure so I had to create a local copy of [dbo].[spt_values] rather than reference that in [master]
SELECT…INTO is not supported in SQL Azure
The 1GB limit of SQLAzure web edition meant that there would not be enough space to store all the data generated by Adam’s script so I had to decrease the total number of rows.

The amended script is available on my SkyDrive at https://skydrive.live.com/redir.aspx?cid=550f681dad532637&resid=550F681DAD532637!16756&parid=550F681DAD532637!16755

@Jamiet

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

AdventureWorks2012 now available for all on SQL Azure

jamiet — Tue, 27 Mar 2012 08:52:00 GMT

Three days ago I tweeted this:

Idea. MSFT could host read-only copies of all the [AdventureWorks] DBs up on #sqlazure for the SQL community to use. RT if agree #sqlfamily
— Jamie Thomson (@jamiet) March 24, 2012

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