Wednesday 10 September 2014

Auditing for Microsoft Azure SQL Database

I was recently asked to contribute a module on Azure SQL Database to a new course on Azure for IT Professionals. This gave me an excuse to experiment with the new Auditing feature, which is currently in preview. Auditing enables you to record events that occur in your Azure database, such as logins, data access, data updates, and schema changes; and is an important addition to Azure SQL Database that supports security and compliance requirements in many organizations.

Setting up Auditing is pretty straightforward, as you can see in this video.

To learn more about auditing in Azure SQL database, see http://azure.microsoft.com/en-us/documentation/articles/sql-database-auditing-get-started/.

Saturday 10 May 2014

Microsoft Azure SQL Database Self-Service Restore

You may have missed the recent enhancements to Azure SQL Database service tiers, which Microsoft announced last month. There are three new service tiers (or “editions” if you prefer) of SQL Database that are currently available in preview – basic, standard, and premium. That’s currently in addition to the Web and Business service tiers that were available previously, though these are being retired over the next year. The performance and scalability options that go with these new tiers are pretty compelling, and may make a good subject for a future post. However, the other key new feature that comes with these features is a self-service restore feature that provides a basic disaster recovery capability.

Restoring an Azure SQL Database

When you create a database with one of the new service tiers, Azure automatically maintains backups that you can use to back-out unintentional changes or recover an accidentally deleted database. The specific options for restoring a database depends on the service tier:

  • Basic tier databases can be restored to the most recent daily backup. Backups are retained for 24 hours.
  • Standard tier databases can be restored to a specific point in time, and backups are retained for 7 days.
  • Premium databases can be restored to a specific point in time in the last 35 days.

You can restore databases using the Azure management portal, or with PowerShell. Restoring an existing database creates a new database of the same service tier with a name that reflects the date and time to which the database has been recovered. For example, suppose you executed a Transact-SQL command that accidentally deleted the contents of a table in your database. Depending on the service tier, you can restore the database to the most recently available recovery point before the data was deleted. After you’ve verified that the recovered database contains the required data, you can delete the original database and the use ALTER DATABASE statement to rename the restored database to match the original name.

If you delete an entire database, it remains listed in the portal until its retention period has expired. If you accidentally delete a database, you can immediately restore it to the most recently available recovery point (again, depending on the service tier).

Of course, this self-service recovery feature isn’t a replacement for a properly planned disaster recovery solution. However, it’s a nice addition to SQL Database that could potentially save you a lot of pain and stress one day!