Book on Azure and SQL Server

Posted on February 6, 2017 by Amit Banerjee

My last contribution to a book was in 2012. With the advent of the cloud and my continuing work with SQL Server, I jumped at the opportunity when my friends and colleagues, Pranab Mazumdar [t] and Sourabh Agarwal [t], talked to me about contributing to a book on running SQL Server on Azure.

The book “Pro SQL Server on Microsoft Azure” attempts to teach the basics of Microsoft Azure and see how SQL Server on Azure VMs (Infrastructure-as-a-Service) and Azure SQL Databases (Platform-as-a-Service) work. This book will show you how to deploy, operate, and maintain your data using any one or more combinations of these offerings along with your on-premise environments. You will also find some architecture details which are very important for an end user to know in order to run operations using Azure.

The book is available on Apress and Amazon.

We would love to hear any feedback about the book. It could be good, bad or ugly. You will find the resources available for download on the site.

Azure SQL Database Export

Posted on July 23, 2014 by Amit Banerjee

In one of my previous posts I had talked about creating or rather restoring a deleted database backup. There is another option in preview called the automated export which allows you to create an automated export copy of your Azure SQL Database. The Azure SQL Database Import/Export Service is a REST-based web service that runs in every Microsoft Azure data center. The service provides a free request queuing service and a free Compute service to perform imports and exports from a Microsoft Azure SQL database to Microsoft Azure binary large object (BLOB) storage. The import and export operations are not a traditional physical database backup but a logical backup of the database that uses a special BACPAC format. This logical BACPAC format lets you avoid having to use a physical format that might vary between versions of SQL Server and SQL Database. Therefore, you can use it to safely restore the database to an SQL database and also to a SQL Server database.

As you can see from the screenshot, the Configure tab in the Azure Management portal for my Azure SQL Database, megatron, has an Export Status set to None.

As usual, this is a PREVIEW feature and the above is true as of today when I am writing this post. This functionality could change in the future.

Read on to find out what happens when I change the setting to Automatic… Is is as easy as 1-2-3?

Continue reading →

Restore a deleted Azure SQL Database

Posted on July 3, 2014 by Amit Banerjee

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.

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.

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: