Yesterday we looked at how much your indexes were costing you in SQL Azure. Today we shift focus a bit and I am going to show you something quick and easy for the weekend.
We all know that you cannot take a backup of a SQL Azure database. For many DBAs I know this is a huge sticking point because they have a primary focus of disaster recovery. And without backups, you can’t do a restore. And the thought of not having a safety net just scares them to the core. I know, because I was one of them, until I took that red pill from Morpheus.
So what options do you have? Well, you could BCP your data back and forth from the cloud to an on-premise server. Of course you pay for the bandwidth with that method. Or you could store your data using another Azure storage option (and pay for that as well). But there is a third option, and one that I don’t believe most people talk about: you can create a copy of your database (yeah, you pay for that, too. Face it, Azure is the new Electric Company, they are a utility, just take the red pill already).
This command will create a copy of your database in SQL Azure:
CREATE DATABASE destination_database_name
AS COPY OF [source_server_name.]source_database_name
Notice the inclusion of [source_server_name.]? That’s right, you can quickly and easily create a copy of your database onto a different server. So, we can’t issue a USE command in SQL Azure, but we can create copies of databases between servers. Warrants mentioning.
Once you have started the copy process you are probably going to want to have an idea as to when it will finish. And that’s where the sys.dm_database_copies system view comes to your rescue. While connected to master you can run the following:
SELECT *
FROM sys.dm_database_copies
This will show you the current state of the copy process. When the copy is complete the row is removed from this table.
Tomorrow we will look at how you can find out if a new release of SQL Azure will affect your current databases.
Great info, Tom. Just a question to clarify, are you saying we should copy the SQLAzure database to another SQLAzure instance? Or are you saying we should do if from SQLAzure to an on-premises SQL Server?
Do you have any idea what the trade-off in costs are between the different techniques you posed?
Many thanks,
-Kev
Hi Kevin!
The command will only create a copy to another SQL Azure instance. And it is the option that I believe would work for 80% of all scenarios.
I can’t comment in the tradeoff on costs, as that is tied to the quantity of data being moved and stored. Also, I can’t really say if you should pipe your data to an on-premise instance, because that is the type of HA/DR discussion that need to happen for each shop.
Personally, my OCD kicks in and I want to keep everything as close to me as possible, just in case.
HTH