As a strategic direction, many companies have decided to start moving their SQL Server databases into the cloud using SQL Azure. This change in direction allows companies to outsource their hardware while moving to a “Database as a Service” model. One lesser known feature of SQL Azure is Point-in-Time restore which is available by default in the new Basic, Standard and Premium Service Tiers.
Previously, SQL Azure was only available in two Service Tiers: Web and Business. The major difference between the Service Tiers was the size of the database that you could store in each Tier: 5GB for the Web Tier and 150GB for Business Tier. In order to backup databases on either of these Service Tiers, you had to schedule an Automated Export of the database to SQL Azure Blob Storage. While not overly difficult, you did have to set up a SQL Azure Storage Account and go through the steps to back up the database to the Storage Account.
In April 2014, Microsoft announced that it would be retiring the Web and Business Service Tiers in September 2015 and introduced three new Service Tiers: Basic, Standard and Premium. Within the Standard and Premium Tiers, there are also three different Performance Levels. Aside from the size of the database that is available in each Service Tier, changing Tiers and Performance Levels also affects the Max Worker Threads, Max Sessions, Benchmark Transaction Rate, Predictability (of performance) and something called the Database Throughput Units (DTU) which is a measure of the performance level of each Tier taking into account CPU, memory, reads and writes.
Another feature that comes as standard with the new Service Tiers is the ability to do a Point-in-Time Restore of the database. Databases are backed up daily, and the backup retention is based on the Service Tier chosen (see Figure 1 below).
Service Tier | Backup Retention |
|---|---|
Basic | 7 Days |
Standard | 14 Days |
Premium | 35 Days |
Figure 1 – Backup Retention for Service Tiers
In order to do a Point-in-Time restore, you must first log in to the SQL Azure Portal. At the bottom of the screen, you will see the new Restore icon (see Figure 2).
Figure 2 – New Restore Icon for Point-in-Time Restore
Once you click the Icon, you will get a dialog that allows you to specify how far back you want to go to restore the database (see Figure 3). Remember that the retention time depends on the Service Tier that was chosen.
Figure 3 – Specify the Restore Point
It should be noted that you cannot restore the database directly. You must first restore a copy of the database, then drop the original database and then rename the restored database. In order to see the progress of the restore, you can query the SQL Azure DMV sys.dm_operation_status as shown in Figure 4.
Figure 4 – DMV sys.dm_operation_status
Another thing to keep in mind is that the restore is not necessarily a quick operation as it took nearly 5 minutes to restore a test database with very little data. I need to do a bit more research as to how the Point-in-Time restore is affected by the Service-Tiers and database size, but this is a topic for a future article.
Once the database restore is complete, you will see the two databases in the SQL Azure Portal (see Figure 5).
Figure 5 – Original database and restored database
The next step is to drop the original SQL Server Database and then rename the new SQL Server database. In this instance, I chose to manage the SQL Server Database through the Management Portal for the SQL Database. If you are in the Management Portal for the SQL Database, it is important to remember to select the Master Database as shown in Figure 6.
Figure 6 – Select the Master Database
Once here, you can enter the following query into the Query Window (see Figure 7) to drop the old database and rename the new database.
Figure 7 – T-SQL to drop and alter the database
Once you’ve run the above script, you will see that you now only have one database (see Figure 8).
Figure 8 – Restored SQL Database renamed
Although not an substitute for a proper disaster recovery plan, Point-in-Time Restore is a great feature of the new SQL Azure Service Tiers.
