Azure SQL Databases – 2 Lesser Known Capabilities

By | In Cloud Computing, SQL Server | April 14th, 2016

AzureWorking 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.

Azure SQL Databases

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.

Contact Us
Eric Russo
Senior Vice President of Database Services
Eric Russo is SVP of Database Services overseeing all of Datavail’s database practices including project and managed services for MS SQL, Oracle, Oracle EBS, MySQL, MongoDB, SharePoint and DB2. He is also the Product Owner for Datavail Delta, a database monitoring tool. He has 21 years’ experience in technology including 16 years in database management. His management success and style has attracted top DBAs from around the world to create one of the most talented and largest SQL Server teams. He has been with Datavail since 2008: previous to that his work experiences include DBA Manager at StrataVia, Senior Web Developer at Manifest Information Systems and SQL Server DBA at Clark County, Nevada.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).