It has been quite a while since SQL Azure has come out. But there are still areas where I have been asked; what is the best way to migrate the existing database to a SQL Azure instance? And, almost as always, I start off answering with “it depends”. Because really, that’s what it is. There are a lot of factors that needs to be weighed before even considering migrating and then choosing. And with that, I won’t be able to provide a generic answer for all. As migration issues will be a case-to-case basis. Specially with all the “homegrown” techniques that we apply.
So here, I will just go ahead and timewarp myself to the stage where we have already decided to migrate the database to the app, and then show you the different means of getting there.
I start off with the Database Manager.
A SQL Azure subscription comes with a web based database manager. This is fine and dandy. It hosts a Silverlight powered UI where we will be given a connection to the Azure database and be able to view the database contents and run scripts. For most DBAs, they would say having the ability to run their scripts is all they need.
Here are my pros and cons with this:
- You can actually manage the DB from anywhere you have a Silverlight enabled browser and a good internet connection.
- You will need a browser with Silverlight installed on it. So it is not 100%. And you might want to turn off pop-up blockers; which has a good reason why they are turned on.
- Being browser based, it is prone to browser based problems like session time-outs, sessions persisting. I experienced once where I was not able to log on again using the manager after closing the window. As it turns out, I was still logged in.
- Another browser issue might be Zoom level. You might not be able to see some buttons if you are not at 100%
- Again with session issues, once you log in, the state of the database’s properties does not update from that state. So you might miss out on some changes while you are logged on.
- And lastly, being browser based and the UI doesn’t really catch up with the processes behind the scenes, sometimes pressing STOP on a process doesn’t really stop it.
The second option; using SQL Server 2008 R2 Management Studio
This might be the option that most will take. With SSMS already installed, the next step is to connect it to the SQL Azure instance. Connection can be done the old fashioned right-click way and providing the proper credentials. Another good thing is, SSMS08R2 has the options in the UI to actually steer you in the right direction of using SQL Azure. Commands that won’t work with SQL Azure, are disabled so we won’t get to mess up.
- SSMS is already installed and with the right settings (like enabling TCP and going through 1433), should be able to connect to the SQL Azure instance readily.
- SSMS is already a familiar tool. And its ability to connect to multiple databases beats the Browser based manager. Using SSMS, you can view both your SQL Azure database, and your SQL Server databases at the same time.
- The express edition works too.
- SSMS08, well at least the R2 edition, actually includes SQL Azure to the types of dabatases that it targets.
- Although it is unlikely that you have a SQL Azure database but no SQL Server database, I will have to place this as a con; even if the situation is not for those who want to migrate. Installing JUST SSMS is not possible and you will need to install SQL Server for that.
There are also some migration tools that you can find, download and use, like the SQLAzureMigrationWizard that you should be able to find on codeplex.
There are also other tools for other databases
SQL Server Integration Services (SSIS) is also another option, and this should be able to “export” your data to a SQL Azure database.
Transfer data to SQL Azure by using SQL Server 2008 Integration Services (SSIS). SQL Server 2008 R2 or later supports the Import and Export Data Wizard and bulk copy for the transfer of data between an instance of Microsoft SQL Server and SQL Azure. You can use this tool to migrate on-premise databases to SQL Azure.
Another way that you may want to look into is to use the BCP Utility that can be ran using SSMS to perform a bulk copy.