SQL Azure: Using the Database Copy Feature
Contents |
SQL Azure Database Copy
Your data is in the cloud, but it really doesn't stop there. Much of the functionality that DBAs have at their fingertips when dealing with local data stores doesn't exist in the cloud yet. The operative word in that sentence is yet. At the moment SQL Azure supports the Database Copy functionality.
Database Copy enables you to copy your database to make a new database on the same SQL Azure server. Alternative, you can copy to a different SQL Azure server in the same subregion or data center. This functionality is much needed, but at the same time it has some shortcomings; some can be worked around, but others will require future service updates.
Copying a Database
The Database Copy feature allows you to create a single copy of a source database. You do so by adding a new argument to the CREATE DATABASE statement: AS COPY OF. As a refresher, the syntax for CREATE DATABASE is as follows:
CREATE DATABASE MyDatabase (MAXSIZE= 10 GB, EDITION= 'Business')
To create a copy of a source database, the syntax now becomes
CREATE DATABASE MyDatabase AS COPY OF [source_server_name].source_database_name]
Thus, if you want to create a copy of your TechBio database, the syntax is
CREATE DATABASE TechBio2 AS COPY OF servername.TechBio
Figure 1 shows the execution of the previous statement. The interesting thing to note is the message in the Messages window. When you execute the CREATE DATABASE statement with the AS COPY OF argument, you immediately get the "Command(s) completed successfully" message. Does this mean the copy finished that quickly? No. This message means the copy has started. You can also see in Figure 1 that the TechBio2 database is already listed in the list of databases; however, that doesn't mean the database copy has completed.
Figure 1 - Copying a database
Knowing When a Copy Is Complete
The question then becomes, how do you know the copy is finished? The answer is that Microsoft created a new data management view (DMV) to return the details of the database copy operation. This DMV is called sys.dm_database_copies, and it returns a great deal of information about the status of the database copy, such as when the database copy process started and completed, the percentage of bytes that have been copied, error codes, and more. In addition, Microsoft modified the state and state_desc columns in the sys.databases table to provide detailed information on the status of the new database.
Figure 2 was generated by deleting the TechBio2 database and creating it again. This time, a statement looks at the sys.dm_database_copies DMV and checks the status of the copy. You can see the statement highlighted in Figure 2. You can also see results in the figure from the query against the DMV. The TechBio database was tiny to begin with, so the copy takes only a few seconds, if that.
Figure 2 - Checking the database copy status
Maintaining a Backup History
The Database Copy functionality lets you create an instant backup of your database, but it doesn't provide a way to create a backup history. In other words, you can't append to the backup and create multiple days' worth of backups. You do have several options, however.
If all you care about is backing up the current day's data, you can delete the current backup copy and re-copy the database. This is a viable option and doesn't require a lot of maintenance.
If, on the other hand, you want a backup history, doing so is a bit more tricky. Many, if not most, companies like to keep a week's worth of backups. These companies back up their databases each night and keep seven days' worth of backups so they have the option to restore past the previous night's backup. To do this with the Database Copy functionality, you must create seven copies of the source database—you have seven backup copy databases. This strategy works, but keep in mind that you're billed for those additional seven databases. The key here is that if you're using SQL Azure, a look at your backup plan is critical.
Author
Scott Klein
Scott Klein is co-founder of Blue Syntax, a consulting a training company specialized on the Azure platform. Scott has been working with SQL Server for nearly 20 years, working with companies large and small in fields such as medical, finance, and retail. Scott is a veteran author, having written a number of books including the soon to be released Pro SQL Azure. Scott is a SQL Server MVP and President of the South Florida SQL Server Users Group and the newly formed Azure PASS Virtual Chapter. Scott also organizes and runs the South Florida SQL Saturday and speaks frequently at SQL Saturday and .NET Code Camp events as well as user groups. He was lucky enough to speak at the 2008 European PASS conference and recently returned from Germany providing a 3-day deep dive training at the PASS Camp.
His online presences include: