Azure SQL Database has come a long way since I first started using it, back at the start of 2017. This is a feature rich PaaS implementation which comes in three different flavours:

SQL Database - three flavours
Courtesy of Microsoft.com
  • Single database – similar to a contained database, which is isolated from other databases and from the instance of the SQL Server. This rules-out the use of Cross-database queries and transactions, Linked servers, etc. Includes dynamic scaling of a single database up and down
  • Managed instance – fully managed instance of SQL Server DB Engine. Contains set of databases which can be used together. Though built on PaaS infrastructure, it’s a fully fledges SQL instance with almost 100% compatibility with on-prem. A Managed Instance is isolated from other instances and provides guaranteed resources. Each can be scaled both horizontally and vertically
  • Elastic Pool – a collection (pool) of single databases where assigned resources are shared by all databases within the pool. Useful for avoiding the over-provisioning of resources. Includes flexibility of moving single databases in and out of the pool. Scaling applies to the pool

In this edition of the blog, I’m going to cover what Azure SQL Database is; some of it’s main features; and, share with you some materials to get you started in a flash.

Azure SQL Database… Why I love it

As i’ve said, Azure SQL Database is feature rich. In continuity and scalability terms, the features included are :

  • Geo-Replication – single and pooled databases allow you to have up to four readable secondary databases in the same or globally distributed Azure Region. This is useful as part of a Business Continuity strategy and for reporting on data without affecting Primary-Production workloads
  • Auto-Failover Groups – Enable high availability and load balancing on a global scale, with Geo-Replication

Combining these two features, you can provision a global-scale database resource without the complexity of typical IaaS or on-prem configuration and orchestration.

The features continue:

  • Automatic Backups – Azure SQL Database automatically performs full, differential and log backups – this also enables Point-in-Time Restores. Copy-only backups are available for Managed Instances
  • Point-in-Time Restores – recover data to a point in time for any SQL Database, with a time grain as little as 5mins (at last time of checking)
  • Zone-Redundant Database – host multiple SQL Databases across multiple availability zones – recover data without data loss

How Secure is it?

As with all data stored in Azure, your data is secured in Azure SQL Database by various levels of encryption:

  • Data being transferred to and from the SQL Database is secured using TLS
  • Data at rest, is secured using TDE (Transparent Data Encryption)
  • Data being accessed, can be secured with the Always Encrypted feature – this makes data Transparent to your application. It allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine

Taking security a few steps further, a suite of Advanced Threat Protection includes the following features:

  • Data Discovery and Classification – discover, classify, label data and track access to it
  • Vulnerability Assessment – discover, track and remediate potential database vulnerabilities – enhance the security of your Database resource
  • Threat Detection – detects anomalous activity that indicate unusual and potentially harmful attempts to access or exploit your Database resource.

There are some additional features that help you to Audit for Compliance and Security.

Why is a Highly-Available Endpoint Important?

Well, in the scenario where you have a failure on your Live Azure Region, your Primary Azure SQL endpoint will begin servicing database requests from your Secondary (DR) site. This means you don’t have to modify your connection strings, whether it be on your API or other depending resource, allowing for seamless failover.

However…

…watch-out for the User SID mismatch.

Whilst the User object might be replicated across to the database in your DR site, the Login will have a separate SID on the secondary server. So you’ll need to bake that in to your solution during your initial deployment. Otherwise, you’ll be unable to have your API, etc, access your DR site.

Deploying Azure SQL Database to Azure

Below is a scenario I have pulled together in order to show how the features above can be implemented, and introduced to a wider solution architecture.

There are two Elastic Pools, in two different Azure Regions. Geo-Replication has been enabled and API requests are served via a highly-available endpoint through a Failover Group. A secondary (read-only) endpoint is available to enable Report Services.

I have put together an example ARM Template for you to try deploying your own implementation with these cool features.

AzureSqlDatabase – ARM Template with READMeDownload

Alternatively, you can Clone my Repo on GitHub:

In these material, provided for you to use, the READMe will take you through the prerequisites; how to complete the ARM template for your environment; and, the features included in the deployment.

The features includes are:

  1. SQL Database deployed within Elastic Pools
  2. Active Geo-Replication
  3. Auto Failover Groups
  4. Advanced Threat Protection
    • SQL Injection
    • SQL Injection Vulnerability
    • Data Exfiltration
    • Unsafe Action
    • Brute Force
    • Anomalous Client Login
  5. Vulnerability Assessments
  6. Auditing to Blob Storage
  7. Azure AD Secure Logins
  8. Backups – Long Term Retention and Point in Time Restore

Feel free to have a go and get in touch with any feedback, comments or queries.