Geo Replication meets Azure SQL Database


Active Geo-Replication enables continuous copy of your Premium database across geographic regions worldwide or within the same region. This is akin to setting up an availability group for SQL Server 2012 and above. For this you will need the following:

  • At least two Azure SQL Database Servers will available Premium database quota. The quota available is visible under the SERVERS tab in the Azure Management Portal for SQL Databases. You can alternatively retrieve the available quota by querying the server_quotas view (also in PREVIEW).
  • One or more Azure SQL Database configured on the Premium tier.

When downgrading from a Premium service tier, you must first terminate all Active-Geo Replication relationships. You can follow the steps described in the Terminate a Continuous Copy Relationship topic to stop the replication process between the primary and the active secondary databases.

The performance level of the secondary replica should be the same or higher than the primary replica.

Setting it up

In screenshot 1, you will see that the GEO-REPLICATION tab shown for the database optimus (Yes, another transformer character!) This will be available only if you have signed up for the PREVIEW feature for the new database tiers offered for Azure SQL Database. And if you have a Premium database configured, then you can setup a replica using the ADD REPLICA button which will be available at the bottom of the page.

image

On clicking the ADD REPLICA button, you will be provided with an ADD ACTIVE GEO-REPLICA screen. This allows you to pick another target server which will host the replica copy of your Azure SQL Database. See Screenshot 2.

image

Select the target server where you want your active geo-replica created. Only servers enabled for geo-replication in the same subscription as the source with available Premium database quota are shown. Additionally, only servers enabled for the current reservation size of the source database are shown. Replicas are created with the same database name, edition, maximum size, and reservation size as the source.

In the above screenshot, you can see that I have asked Azure to create a replica of optimus in the North Central US region and I have the primary copy hosted in the South Central US region. Once you proceed with your selection, you will be asked to accept terms and conditions as Premium database hosting attracts a cost!

Once the replication is configured, the Geo Replication tab will show you the status and the configured replicas as shown in Screenshot 3.

image

Reference:

SQL Database Pricing Details
http://azure.microsoft.com/en-us/pricing/details/sql-database/#service-premium

Changing Database Service Tiers and Performance Levels
http://msdn.microsoft.com/library/azure/dn369872.aspx

Active Geo-Replication for Azure SQL Database
http://msdn.microsoft.com/en-us/library/azure/dn741339.aspx

Advertisements

Restore a deleted Azure SQL Database


There could be a need where you have to restore a database that was deleted accidentally due to malicious intent or a plain old mistake (people end up writing resumes for such mistakes) or for some other inexplicable need! This is where backups come into the picture! For the Web and Business editions, this is not an option unless you have exported the database or you had made a copy of the existing database. Note that Business and Web service tiers will be retired in 12 months from April 24, 2014.

If you have just entered the world of Azure SQL Database, then this will seem even more difficult because you do not have the traditional scheduled backup job that you can trigger. So what is possible? Read on to find out…

Windows Azure SQL Database automatically creates backups of every active database using the following schedule: Full database backup once a week, differential database backups once a day, and transaction log backups every 5 minutes. The full and differential backups are replicated across regions to ensure availability of the backups in the event of a disaster. In addition, premium database offers the ability to create secondary databases in different regions to protect against datacenter disasters.

If you have played around with the new Azure SQL Database tiers (Basic, Standard and Premium), you will notice that there is a new tab available for Deleted Databases. This tab shows you the option of restoring a database (Basic, Standard and Premium tiers only) which was recently dropped. See screenshot 1.

image

I had dropped the bumblebee database which was a Basic tier database on 30th June. I can choose to restore this using the RESTORE button available at the bottom of the page. In the restore settings page popup page, you will have to specify the following:

1. Database name – This cannot be same as the original database

2. The time to which you want to restore available through a slider and text boxes. This is similar to the Timeline feature which was introduced in SQL Server Management Studio.

image

Note that the target server name cannot be changed. You will have to restore the deleted database backup to the same server. You cannot restore a database backup across Azure SQL Database Servers using this Wizard. Cross server restores are not currently supported.

While the database is restoring, you will see the database in the Databases tab but the status will show up as “RESTORING”. This is currently a PREVIEW feature. So you will need to sign up for the new database service tier preview to explore this feature.

Note that the deleted database backup is not retained indefinitely. As of July 1st, the following retention policy applies for the backups as shown in Screenshot 3:

image

More about Azure SQL Databases in a future post!

Reference:

Changing Database Service Tiers and Performance Levels
http://msdn.microsoft.com/library/azure/dn369872.aspx

Azure SQL Database Backup and Restore
http://msdn.microsoft.com/en-us/library/azure/jj650016.aspx

Management Portal for Azure SQL Database


The Azure SQL Database provides multiple options to connect to the database. The Management Portal being one of them. In my last post, I had talked about what options the Object Explorer offers you for an Azure SQL Database.

Connecting using the Management Portal

The simplest way to connect to an Azure SQL database is to use the management portal. The management portal link will be https://&lt;servername>.database.windows.net/?langid=en-us#$database=<database name> for any Azure SQL Database. When you log onto the management portal, you will need to provide the details shown below in Screenshot 1. If you have multiple Azure SQL databases hosted on the same server, then it is recommended that you provide the database name as the USE command is prohibited for switching database connections.

image

Continue reading