Azure Stack and SQL Server: Best of Friends

On July 9th , 2019, support for SQL Server 2008 and 2008 R2 will end - at the time of writing this blog, this is tomorrow. By the time you read this, it'll likely have passed.

This means the end of regular security updates, leaving your heritage SQL server workloads unprotected from the latest security vulnerabilities. Note that this is end of support (EOS), not end of life (EOL), as there are still options available for you to protect these workloads, and further extend their lifespan.

Today there are effectively four options available to customers in this situation:

Upgrade and modernize your SQL server workloads

This is the best solution - while it's tempting to simply extend the life of existing workloads, SQL 2008 is over 10 years old now, and the features and capabilities available in modern versions put it to shame. If it's possible to upgrade and modernize, this represents your best path forward.

We recognize though that there are many workloads which simply cannot be upgraded - sometimes this is due to technical dependencies, sometimes it's due to the sheer scale of the task. Regardless of the reason, there are three additional options available for those who cannot upgrade their 2008 workloads.

Pay for extended support

As with any Microsoft product which has reached EOS, the option exists to purchase extended support, to buy some more time to either upgrade or retire the product, and ensure security protection for the duration of those activities. This is not a cheap option, and adds a significant overhead to already hefty SQL Server licenses - typically adding 75% to the cost of licensing.

Recognizing this, Microsoft have made additional options available to customers, to help them avoid the additional (and often unplanned) overhead of paying for extended support.

Migrate workloads to Azure

SQL Server 2008 and 2008 R2 workloads migrated or 'lifted and shifted' into Azure are eligible for three years of free security updates. In addition, any SQL Server 2008 Enterprise licenses with Software Assurance can make use of Azure Hybrid Use Benefits (AHUB), and can be migrated directly into Azure with no licensing impact.

While this is a superb offer, it falls short for many enterprise customers in a few key areas.

Firstly, SQL server workloads never operate in isolation - they're part of a wider application stack, and often other parts of an application cannot be migrated to Azure. Moving a database backend into an Azure datacentre and leaving other components on-premises typically introduces unacceptable latency, particularly when discussing older versions of SQL Server like 2008 and R2.

Secondly, while AHUB is great for those customers who operate Enterprise Core licenses and have maintained Software Assurance (SA), all of those companies with investments in other licensing mechanisms and who don't have SA will need to re-purchase their licenses, often not at insignificant cost, in order to move their workloads to Azure.

Thirdly, even for customers who can technically move their workloads to Azure, and who have the appropriate licenses in place to avoid having to buy them again, there are often a myriad of regulatory compliance reasons preventing migration into Public Azure datacentres. For these customers, migration to Azure just isn't an option, and yet paying for extended support is unpalatable from a cost perspective.

For customers who fall into any of these three categories, a fourth option exists.

Migrate workloads to Azure Stack for three years of free extended support

Azure Stack is an extension of Azure on-premises, and as such delivers the same extended support for Windows and SQL 2008/R2 as Public Azure, with three years of support included for all such workloads migrated into Azure Stack.

Azure Stack doesn't live in isolation or at arm's length from the rest of your infrastructure, it operates within the same four walls as your existing systems, and as such can integrate with them easily without the latency penalties which can come from Public Cloud solutions.

In addition to bringing some Azure licensing benefits on-premises - for example pay as you use licensing for Windows Server - Azure Stack also obeys many of the same licensing laws as other on-premises solutions. So while Azure Hybrid Use Benefits only apply to Enterprise Core licenses with Software Assurance in Public Azure, typically existing licenses can simply be migrated into Azure Stack with no impact, regardless or version or presence of SA. As ever, make sure to validate your licensing position with your licensing reseller/specialist to ensure you're in compliance with all requirements.

Because you own and operate Azure Stack, the regulatory compliance issues which can prevent migration to Public Azure simply don't exist within Azure Stack, and companies can migrate their workloads while remaining in full compliance with country, region, or industry regulations. Additionally, Azure Stack can operate in a fully disconnected mode, making it a great choice for both highly regulated dark sites, and sites with limited/no connectivity.

As if this wasn't compelling enough, customers are finding that when comparing the cost of implementing Azure Stack vs the cost of paying for extended support, not only do they get a modern, evergreen, Azure-consistent IaaS and PaaS ready platform, but it's actually saving them substantial amounts of money while providing significant benefits!

Going beyond End of Support

Over and above extending the life and value of SQL 2008 workloads though, Azure Stack is a fantastic platform for operating SQL Server workloads in general. Azure Stack offers some unique capabilities not offered by other on-premises environments, as well as unlocking some of the greatest value adds in modern SQL Server variants.

Infrastructure as Code

One of the most valuable capabilities in any cloud-native platform is the ability to define your infrastructure in Infrastructure as Code (IaC) templates. In the same way that the same source code should always generate the same binary when compiled, an IaC template will always generate the same environment every time it's applied. Infrastructure as code removes the problem of configuration drift, and enables the DevOps concept of cattle vs pets.

Because Azure Stack is an extension of Azure, we benefit from much of the extensive Azure ecosystem already in place, including pre-defined IaC templates. In the Azure Stack Quickstart Templates gallery there are templates for deploying a SQL 2014 Always on Availability Group in non-HA mode, SQL 2014 Standalone server, and SQL 2016 Always on Availability Group with HA enabled. These templates can be very easily edited and moulded to your individual purposes.

In order to deploy this SQL 2014 template, only four parameters need to be filled in - a domain name, a SQL Service account name, an admin username, and a password. After entering these parameters, the template will deploy a full end to end SQL environment.

Not only does the template provision and configure a SQL server, but also an Active Directory environment, public IP Address, load balancer, and a network security group to manage north/south/east/west traffic external to the VMs. In short - a fully functional end to end SQL and AD environment, with just a few clicks.

Of course there are existing templates for deploying full always on availability groups, different versions of SQL, and extensions for joining to an existing domain rather than deploying a new one.

For SQL 2008R2 workloads, there is a marketplace image available to deploy SQL 2008 R2 SP3, so instead of migrating in a full VM, it's often worth looking at deploying a fresh VM and just migrating in the database or databases. Further down in this blog you'll see some of the benefits to this approach come to light.

Azure Stack benefits massively from being a part of the wider Azure ecosystem, where there's a plethora of official and community content to build on top of. With Azure Stack you don't start from scratch, you stand on the shoulders of giants and reap the efforts of a decade-old community.

Backup and Recovery

As ever, Backup and Recovery remains a critical consideration with any SQL Server workload, and in this regard Azure Stack has you covered as well.

Built into the SQL IaaS Extension in Azure Stack (and of course in Public Azure) is the ability to configure automated backup of your SQL server workloads. You can backup user databases and system databases, configure a retention period, encrypt your backups, configure frequency of full backups, tweak the backup window… all of this and more is available out of the box and manageable via the Azure Stack portal, via ARM templates, or any automation tooling of your choice.

This means that when you deploy a SQL workload from Infrastructure as Code template, you can include the backup configuration in that template, and know that your workload is protected immediately from time of deployment. Backup isn't an additional step, it's just an inbuilt part of  your deployment process.

By default, these backups will be stored in a storage account within your Azure Stack scale unit - you may want to keep some recent backups locally on the Azure Stack itself to enable rapid restore activities, while you probably want to store older backups outside the Azure Stack itself, both to take advantage of lower cost storage, and to protect from failure of the scale unit itself.

The means to achieve this automatedly exists within Azure Stack as well - an Azure Functions Blob Trigger set to run whenever a new item is added to a storage container is a very lightweight way to manage automatically copying or moving your backups to an external storage device. In the example below I'm using a simple Batch script, but this could be any one of a number of languages, be it C#, Javascript, Typescript, or more!

When combined with Dell EMC support for Isilon storage natively managed within Azure Stack, you can have a fully automated end to end off-stack backup solution for SQL server workloads, all managed natively from within Azure Stack and deployable from ARM template.

Restore operations are just as important as backup operations, and here we can use native features to rapidly recover from our backups as well. Within SQL Server Management Studio (SSMS), or using your choice of PowerShell or other automation tools, databases can be restored directly from Azure blob storage accounts, and this includes Azure Stack blob storage.

Because backups will always be relatively local to the SQL server - either running in the same Azure Stack on the same physical storage, or off-stack in an Isilon or similar storage device in the same premises - restore time can be significantly enhanced vs restoring from a Public Azure blob storage account, where bandwidth and hence restore time is limited by your connectivity outside the datacentre.

As you can see, just running SQL Server workloads in Azure Stack confers upon them a host of protection options and benefits, every one of which can be automated.

Patch and Update

Patch and update of any IT environment is vital, and so it is with SQL workloads too, with maintenance of security updates and minor patches being both important, and traditionally a drain on IT resources. The SQL IaaS Extension within Azure Stack provides the opportunity to automate the deployment of these Windows and SQL updates - those marked important - while still allowing IT admins to choose if and when to deploy major releases which could have potential workload impact.

Within the SQL Configuration Blade in the VM Configuration, a patch schedule can be enabled and configured, with a defined maintenance window to ensure updates are completed or rolled back in an acceptable and known timeframe.

If you have a need to delve deeper and automate additional patch and update capabilities, including deployment of new major releases or cumulative updates, Azure can be used to enable full Update Management of Azure Stack workloads. For SQL Server alone though, the built in functionality described here can provide an auditable and automated mechanism for keeping on top of important updates, with no dependencies outside your datacentre.

SQL Server 2019 - Polybase

Within SQL Server, from 2016 onwards, there exists a wonderful feature called Polybase. While we won't do a deep dive into it here - instead saving that for a future blog - it's something that's very much worth considering in the context of Azure Stack, given some of the new scenarios it can unlock in the on-premises datacentre.

We know that one of key inhibitors to customers moving wholesale to public cloud providers is the problem of data gravity - where large datasets on premises are just too large to migrate to the public cloud, and where an application ecosystem has built around and is now sticky to and 'orbiting' these datasets, precluding their migration as well.

As we know, Azure Stack provides the ability to run Azure services within the existing four walls of your datacentre, delivering significant management, automation, and development benefit both to IT Pros and to Developers. Deploying Azure Stack adjacent to a location locked data-gravity bound environment can in and of itself unlock new opportunities for application modernisation, however it's in conjunction with Polybase in SQL Server 2019 that some of the coolest opportunities come to light.

We've seen already that running SQL Server within Azure Stack confers upon it certain benefits by default, around patch and update, backup, monitoring, automated and repeatable deployment and scaling, and more. Data gravity often precludes existing workloads moving into Azure Stack though, and often times the workloads aren't SQL workloads anyway. This is where Polybase comes to the rescue.

In a nutshell, Polybase enables the querying of and access to data in external data sources - some of which are detailed in the below diagram - without moving that data into SQL Server itself. Developers can use features like the Azure App Service, Azure Functions, or Azure marketplace images in Azure Stack IaaS, query 'external tables' in SQL 2019, and communicate with these external data sources entirely through T-SQL.

All operations are offloaded from the SQL environment to the data source which holds the data, so load addition to the Azure Stack environment is minimal, and developers now no longer need to maintain different libraries for different data sources. The requirement for ETL is removed or at least minimsed, and querying against multiple disparate data sources becomes a significantly simpler proposition.

In short - by delivering an Azure Stack solution in conjunction with SQL Server 2019 Polybase, IT Pros can deliver their developers a platform which allows them to develop in an Azure consistent way, using Infrastructure as Code and Azure Marketplace images, as well as some hugely powerful Azure PaaS features, while significantly simplifying the way they access existing data sources.

As Azure Stack continues to grow and mature, we'll see less of Azure Stack being discussed as a silo, and more of these integration scenarios, where Azure Stack is not just the enabler for otherwise untenable scenarios, but also the core driver to data centre modernisation, standardisation, and developer agility in the context of the entire datacentre estate.

Bringing Value to any SQL Variant

From the most modern variants right back to those at the end of extended support, Azure Stack is able to provide significant value add to any SQL Server workload.

Migrating your existing SQL 2008/R2 workloads into an Azure Stack today reduces your management burden, gives you three years of free extended support and security updates, opens up new and valuable scenarios and opportunities, and even potentially saves you money over paying for extended support.

Migrating your workloads to Azure Stack and extending the life of those SQL 2008 environments isn't the end of the road though. With an Azure Stack in your datacentre you're now running a modern, evergreen cloud platform, consistent with public Azure, with the time and space to explore workload modernisation in a safe and secure environment.

These are a few of the ways Azure Stack brings new value to your on-premises database platforms, regardless of version. Whether it's extending the life of your heritage workloads, enhancing all SQL workloads with new management and support capabilities, or bringing the power of Azure IaaS and PaaS to your datacentre, SQL Server and Azure Stack are the best of friends, and very much designed to operate better together.