Question:
What is the best method to use for copying an “On Premise SQL DB” to SQL Azure. I’ve used the generate script wizard method but it takes quite awhile to get everything setup. I was looking a DAC Pack but I’m a little nervous using a command utility that you could easily use to wipe out a database on accident. Looks like a great tool though. What do you recommend? Or, is the Generate Script method the best way to go? I noticed that SQL Azure requires Clustered indexes on all tables. I would imagine that this might be a hindrance to automated migration tools.
My Answer:
Generate script wizard is a great tool for smaller databases.
For bigger databases, You should consider using a combination of Generate script wizard for migrating schema and then BCP for data.
And http://blogs.msdn.com/b/sqlcat/archive/2010/07/30/loading-data-to-sql-azure-the-fast-way.aspx has some great info.
And as far as i know, the SQL Azure Migration wizard creates scripts that does clustered index creation (if it does not exist) – so yes, that is taken care of.
And for more information on various options for schema migration and data migration that you have – I would suggest an article from solidq Journal: http://www.solidq.com/sqj/Pages/2011-May-Issue/Migrating-Data-into-Microsofts-Data-Platform-SQL-Azure.aspx. It also covers best practices while migrating data to SQL Azure.
Link: