As promised we’re going to dive into the details on how we would actually migrate the database from your on premise server to Windows Azure SQL Database. We’re going to go over how to do this with the SQL Database Migration Wizard. I’ll be honest, going forward we only use SQL Server Data Tools (SSDT), because you get the full database project experience. And once you tie your project into source control (you all do that, right?) You’re set up for the actual project life-cycle.
Features that won’t work with WASD (today)
Before we dive in, I want to link you to two pages that list some of the features you can’t currently use with Windows Azure SQL Database. This is the first, and this is the second. Notice I say today, that’s because since I started with Azure SQL Databases, features have been “turned on.”
At the beginning you had no backup strategy at all. Then, RedGate and a few other companies rolled out services that would snapshot your backup. It wasn’t necessarily a transactionally consistent backup, but it gave you the ability to create your daily backup, and you could restore this data to a second database (on prem or in the cloud), and restore just the data you lost.
A few months ago, this feature moved to first party. Microsoft also added a method to ensure transactional consistency on their snapshots. Depending on the size of your database, this new offering might have a lower price than the third party solutions. But given the fact it added transactional consistency, that was totally worth the cost change.
Now, if only we could get point in time backups, without having to put in a service request to Microsoft CSS…now that would be even better!
I want to make sure you get two points here;
- Windows Azure SQL Database is an evolving product. I’ve seen changes happen in less than a month. I’m really excited when I get my email from the Azure Marketing team telling me about all the new features that just went General Availability (GA), or those items just entering preview.
- Windows Azure SQL Database isn’t a dedicated private server. You’re sharing it with others, sometimes MANY others. When you notice there are certain features not supported, ask yourself, “what’s the cost of turning this on for a box with possibly a hundred different SQL workloads”. If you think the costs are too high, that’s a good indication of why that feature is not yet available in WASD. This means sometimes you’ll have to alter how you do things. Sometimes it means you’ll have to be more performance and cost conscience. And that’s something I’ve been preaching for years before moving to the cloud!
Analyzing your database with SQL Database Migration Wizard
During the next phase of the migration, you’ll need to find out if you’re using any features on premise that you can’t use in Azure. After you download and extract the Migration Wizard, fire it up!
When the program opens, you can do a couple things, you could analyze a trace. This is a great tool if you have a program that’s not very well documented in what it does with the database. You can start a replay trace from your on premise server, let it capture your “standard” work load. This could mean running that trace for many hours, possibly for a few days. Please run that trace from the server and not your local machine.
Once you’ve collected the trace, bring it down to your workstation and choose the first option and go. It will churn through your workload.
The steps we’re going to take are just going to look at the database itself. Choose to analyze / migrate your database and click next.
You have to authenticate to your local database server, then click next. on the next screen you’ll see a list of your databases. Choose the database you want to migrate, then click next again.
Next, you can choose to check specific objects or the full database. In this case, we’re checking the whole thing, Adventureworks2008R2.
Next you see a summary of what’s going to be scripted and checked. Followed by the obligatory are you sure Yes/No.
Once the wizard has crawled through your database you get a two tab result. The first tab shows you a summary of the results. Scan through looking for red text. These are the items you’ll have to address before moving on to Azure SQL Database. Notice that AdventureWorks is using XML schemas, XML indexes, and a few other no nos.
The second tab gives you a script you could execute against an azure database and create your objects there. It also includes comments on things you’ll want to fix before fully moving to Azure.
If you start in the Migration Wizard, you then would have to create a Database Project (in SSDT or not) and then start working through these problems. Some might require program changes, SSIS changes, or other changes outside the database. This becomes the hard part. Deciding how to change the database to make it work. There’s even a chance that this step might reveal problems making the project too expensive to migrate.
Next time, I’ll cover how to accomplish these same steps with SQL Server Data Tools (SSDT). In the mean time, if you have any questions, send them in. I’m here to help!