Azure SQL Databases – 2 Lesser Known Capabilities

Working with cloud enabled databases is the trend for future. In the recent past, many of my clients who have engaged me for database related consulting – some have gone the cloud route. Though this seems like a new trend that is hitting our market, the basics of working with SQL Server – be it on-premise or on the cloud remains the same. SQL Azure DB in the backend is still a SQL Server that has been exposed as a service. The biggest advantage that my customers talk to me about when working with this database is the ability to automatically get the High-Availability configured that makes it a viable option for small databases without much of maintenance to be thought through. In this blog post, let me take a step back to let you know some of the lesser known capabilities that are available with Azure SQL Databases.

Transactional Replication to Azure SQL DB

The first capability that is worth making a note is the ability to do transactional replication with Azure SQL databases from your on-premise environments. A detailed step by step approach for this can be found in the documentation of Azure. The more I work with customers, the lesser they know this can be used as a potent mechanism for movement of data from on-premise to Cloud when they are planning their migration strategy. So what are the tenants for this capability:

• Easy migration of SQL Server workloads to AzureDB with minimum downtime
• Existing features such as Sync framework and Export/Import don’t achieve real-time data movement.

Moreover, from my experience and various documentation Sync Framework is out of support and no feature upgrades are planned. So it makes all the more reasons to look for alternatives that can help customers. Export/Import does not copy the data real time and there will always be gaps unless there is downtime for migration.

If you get into the nuances of this feature, we can see that there are a number of restrictions that one needs to keep in mind. I am outlining some of them based on my reading.

PS: Whenever we write about Azure, some information can get state as product teams might go ahead and remove some of the restrictions. These are based on what I have seen as on date.

• Only Transactional replication is supported to AzureDB
• Snapshot replication is not supported
• Pull subscription is not supported
• Peer-to-Peer, bi-directional or updateable subscriptions are not supported
• Distribution agent will use SQL auth and cannot use impersonate Dist agent process account
• No-sync subscribers are not supported (backup/restore for initializing subscription)

If you want to learn more about this, then Channel 9 Video is a great start too.

Auto Upgrade of SQL

If you goto the new portal and check on any of the databases that you created recently, you will find a new tab under Settings called as “Latest SQL Database update”. I never took a note of this till recently. Here you can see that the databases are automatically upgraded when a new version update comes to the core-engine deployed on Azure SQL DBs.

This is a great addition because we will be always on the latest and greatest version when working with Azure SQL databases and will be able to get the goodness of the enhancements automatically. I just love the ability to explore and look at options that are available working with Cloud based databases.

Have you been got a chance to explore and try the Azure SQL Databases? What has been your experience of working with the same? Do let me know via comments so that I can learn from you equally.