While I have production databases in Azure, I can’t exactly experiment with them at will. Further, while they mostly have pretty innocuous data, it’s not all public. So, I need a mechanism for creating a database that I can play with in Azure. I use AdventureWorks. I get it. It’s not that big (actually a good thing for Azure) and it’s not a great database design which doesn’t reflect reality (I would argue makes this reflects reality as I’ve seen it). However, it’s a handy resource because you can go to Codeplex and download it. That makes it a great way to teach others because everyone has access to it.
But…
The other day I download the database and install it on my Azure system. I wanted cleaned up copies of everything for a series of tests and to help get ready for my pre-con at the PASS Summit. I ran a Powershell command to create a copy of the database and was presented with an error. Seems one of the tables, DatabaseLog, didn’t have a clustered primary key, a requirement in Azure. You can create tables without a clustered primary key in Azure, but if you attempt to access that table, you’ll get an error. That’s what was happening to me. Now I had just created two copies of AdventureWorks on two different servers and I had used SQL Compare & SQL Data Compare to create a copy of the database (testing stuff for work too). I had three copies available to validate that it wasn’t something I had screwed up. Nope. It was them, not me.
You can go to Connect to vote up the fix. I also posted the very simple work-around for the issue.
I would be remiss if I didn’t give you one more opportunity to win an Aston Martin. Connect your MSDN account to an Azure account for your chance to win. Oh, and to get a great place to go to learn and test and grow your skill set. All this at no cost to you and no credit card required.