As Microsoft Azure gets richer with features, companies are starting to transition more and more infrastructure to the cloud. Why not? Azure now supports full website backups, SQL Azure auto backups, and great uptime. The toolset is so rich that the IT specialist role has become a developer/cloud architect role and leverages Azure's PAAS and IAAS features to support anything develoepers don't want to. "Managed Hosting" is something Cloud Construct does for many of it's clients now and we are happy to discuss assisting in migrating your business infrastructure to the cloud. We have successfully migrated multiple clients with complex and legacy setups to Windows Azure. This post describes a simple way to get around some of the errors you may experience when migrating legacy SQL databases to SQL Azure.

SQL Azure does not support a few features that you may or may not have taken advantage of in your local install. For example, I recieved the following errors :

• Extended Properties on view and table columns not being supported in SQL Azure
• SQLRegex functions not being supported
• FILLFACTOR values that are not zero on PRIMARY KEYS
• Tables not having clustered indexes.
• RowGuid not supported on uniqueidentity column types

These 4 items were the items that stopped me from easily right clicking on my databases in Enterprise Manager and choosing > "Deploy Database to SQL Azure".

When I ran this command I often received these errors on legacy databases and it stopped me in my tracks.  I began handling each one by hand because these for the most part didn't seem like show stoppers to me. There were workarounds I was able to perform on the DB by hand and then got it to deploy. But what if there are just too many? Well with a quick tip from my colleague/friend Jason Haley about scripting DB's for Azure and a little improvisation on my part, I was able to come up with a pretty good solution.

STEP 1 - SCRIPT DB SCHEMA ONLY

Jason recommend utilizing the Tasks > Generate Scripts option and then selecting the "SQL Azure Database" value for "Script for the database engine type". I decided this was OK to use only AFTER I looked at the errors the "Deploy Database to SQL Azure" command returned. It is important to understand what these errors are so when you are in the new environment you understand what you may need to workaround or patch with a different solution. Things like adding indexes, removing auto GUID's from being created, and removing unsupported functions need to be accounted for.

I decided scripting the schema only and having it modify the schema to work for SQL Azure was a way to get a DB that could be setup in Azure quickly. I scripted to a script file and then created the DB in Azure by hand. Lastly, I ran the create script against the new DB, and now I had a working empty DB.

STEP 2 - SYNCHRONIZE DATA TO NEW DATABASE

Now that we have the database in the cloud, scripting the data and running will work...but it could take forever to cut and paste sections of the script and run it in enterprise manager. I decided to take advantage of a tool I only recently begain using in Visual Studio called SQL Data Compare. This tool is great and takes some features you would normally need a third party like RedGate to take advantage of.

I ended up using this tool to compare the source and destination database data, and once it found everything missing...I asked it to push the data from the source to the target. This took a little bit, but it worked and was much easier than scripting all the data. Now all my data is in the cloud!

IN CONCLUSION

If you are having issues migrating a legacy DB to the cloud, try this approach and hopefully it saves you some time and debugging. But be sure to understand the features you are losing in SQL Azure so you can account for them later. Feel free to contact us and get some expert advice on some of these Azure mgiration scenarios.