Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

SQL Azure Migration Wizard

There’s a project over on code plex to come up with a mechanism for validating databases and generating the necessary scripts to allow you to move those databases, and data, over to SQL Azure. It’s called the SQL Azure Migration Wizard. There’s no real install yet since it’s just a beta. You can download the executable and run it.

It’s pretty straight forward stuff. It’s primarily focused on validating that there is a database, script or series of calls (from a trace event file) that will be properly compatible with SQL Azure, but it can also generate a deployment script from the database. Here’s the opening screen:

MigrationWizard_MainScreen

Not much to it. I’ll walk you through the Analyze and Migrate path which covers most of the functionality of the app. Clearly, you click Next from this screen. This will open a connection window that recommends you connect to Master and let the app bring back a list of databases. OK. Fine. Here’s the list that comes back from my server after a standard connection:

MigrationWizard_SelectSource

Select a particular database and move on. I chose a copy of my MovieManagement database that I had created for my DAC pac testing. Clicking on Next opens up the Choose Objects window. You can either take the default and move everything or change the radio button and select individual objects. I selected everything here just to show off the screen:

MigrationWizard_ChooseObjects

If you click on the Advanced button near the bottom of the screen, you get a whole bunch of scripting options. I won’t go into them all, but you get a surprising level of control as to what gets created and how. The most interesting part there are all the compatibility checks. You can almost get a guess as to what Microsoft might be working on for upcoming releases if you assume that things that are included here can be switched on at a later date without compiling a new set of software:

MigrationWizard_Options

Clicking Next on the Choose Objects screen brings up a summary showing all the choices made so far.

MigrationWizard_Summary

Clicking next just generates the script. No really secrets. You can choose to save the script and the output from the process:

MigrationWizard_Script

Clicking next, things start to get interesting. You have to connect up to a SQL Azure database. You’ll forgive me if I don’t share my connection information with you. I’m trying to keep the costs on this silly thing near zero as much as possible. You again get a list of databases with a few interesting buttons on the bottom of the screen.

MigrationWizard_Target

I can change the connection to a new server. I can delete a database or I can create a database. Either way, the script I run is going to run, as is. There is no comparison, so except for your abilities to choose which objects get deployed, there is no incremental build, say, adding a column to a table. It’s all or nothing, drop & create or just create. Once you pick a database you get the choice to run your script and it finishes.

MigrationWizard_Finished

That’s it. Overall, I like this better than the DAC pac, at least for basic setup and operation. But there’s no way to script it, so you’re forced to walk through this wizard over & over again. However, that said, you have a mechanism that clearly offers more control and abilities than the DAC pac currently supports in SQL Azure.

But… I’m still underwhelmed. I’m still not getting my database into source control. I’m still not able to perform minor changes or in-place upgrades. This will move data as well as objects, which is good, but again, it’s all or nothing. No incremental deployments. We’re still not there with Microsoft tools, not yet.

Comments

Posted by jrbirdman on 10 May 2011

Nice post.  Thank you!

To be honest, I haven't really looked at Azure yet...still too busy with stuff "on earth" rather than "in the cloud".

But your comment about no source control, the inability to make minor changes sounds a lot like the old days (v1.1/4.21).  I guess you have to learn to walk before you can run.  :)

Leave a Comment

Please register or log in to leave a comment.