skip to Main Content

Migrating off SQL Server 2012

Contents

Introduction

     Migration Options

     Planning

SQL Server 2019

     Intelligent Query Processing

     High Availability

     Recovery Improvements

     Linux Improvements

Azure Virtual Machines

     Compute Type

     Storage

     Compression

     TempDB

     Post Testing

     SQL IaaS Extension

Azure SQL Managed Instance

     Migration Components

     The Architecture of Migration to Managed Instance

Conclusion

References

Introduction

As you know, on July 12th 2022 Microsoft will end support for SQL Server 2012. Hopefully, you have already started to think about your migration paths and options, especially if running within an on-premises architecture.

Why should you upgrade from SQL Server 2012? This is because with end of support it means that Microsoft will no longer provide critical patches to address vulnerabilities. That is a big issue if SQL Server is running your mission-critical applications, coupled with the fact that the newer versions of SQL Server are just better and more performant.

Migration Options

You have many options available; you could use this time to start the move to Azure and potentially use Azure Virtual Machines or Azure SQL Managed Instance. If you’d rather stay on-premises, then it will be a case of upgrading SQL Server 2012 to the latest version, (at the time of writing SQL Server 2022 is in preview) that being said, SQL Server 2019 is the most attractive option.

Planning

It is well known that the DMA – Database Migration Assistant tool from Microsoft should be the starting point for analysis of current SQL Server 2012 installations to see if anything needs refactoring before moving to a newer release of SQL Server. The assessment is very thorough and it will show and provide guidance within the following areas:

  1. Are there any migration blockers? Also provides some solutions.
  2. Does your current SQL Server 2012 have any unsupported features that no longer exist within the newer versions?
  3. Are there any breaking, behavioural or deprecated features in use?

To get started please read and download the tool from this link – https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15

SQL Server 2019

The SQL Server 2012 engine is 10 years old now and it’s natural to expect big changes from this version. Significant changes actually happened with SQL Server 2014 with the change to the cardinality estimator. Ever since 2014 things have improved even further, and when you migrate successfully you will enjoy the benefits and powerful features of SQL Server 2019. Some key features to take note of which should really make the move to 2019 an attractive option are listed below:

Intelligent Query Processing

This family feature made its debut with SQL Server 2017 but 2019 enhanced it further. If you study the below diagram, you will see the new features available to you, these are not available with SQL Server 2012.

SQL Server 2019 Intelligent Query ProcessingIf you want to read in-depth technical details on each of these features, please read https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver15#row-mode-memory-grant-feedback

I helped test Interleaved execution with Microsoft, comparing performance across different versions of SQL Server and I can say you will be impressed with the difference.

High Availability

With SQL Server 2019 we now have the option to build a maximum of 5 synchronous replicas for your Availability Group (rather than 3) and also the introduction of secondary to primary replica read/write connection redirection.

Recovery Improvements

ADR – Accelerated Database Recovery. Classically the 3 areas of recovery within SQL Server were done via analysis, redo and undo. With this approach, if a crash happens then the recovery process can take a long time in the presence of long-running transactions. Which is roughly proportional to the size of the longest active transaction in the system at the time of the crash. Microsoft has a feature called ADR which changes the process with the introduction of new concepts.

SQL Server 2019 Recovery Improvements

At the core is the persistent version store. This is where row versions are stored in the database itself. Logical revert is asynchronous row level version-based undo which makes things feel like “instant” rollback. S-log is the in-memory log stream for non-versioned operations.

Linux Improvements

SQL Server 2019 is a great database engine if you are using a Linux environment because now you have support for AD integration (or Open LDAP). Replication is now supported which includes transactional, snapshot and merge, and with Linux we can now do distributed transactions via a Linux version of MSDTC.

Azure Virtual Machines

So, you now want to move to SQL Server 2019 and maybe couple it with a move to Azure? The quickest and easiest way to move to the cloud would be to follow an IaaS approach. This should not be the end goal but can be seen as an intermediary step to going fully native.

Microsoft has made this a very compelling option because if you “Migrate applications and SQL Server databases to Azure Virtual Machines, you get free Extended Security Updates for three years after end of support, only in Azure. Customers can combine this with Azure Hybrid Benefit to save big on Azure by using existing on-premises SQL Server and Windows Server licenses on the cloud with no additional cost”. (https://cloudblogs.microsoft.com/sqlserver/2021/07/14/know-your-options-for-sql-server-2012-and-windows-server-2012-end-of-support/) This is quite significant news and if you decide this is what you want to do then you need to also consider these following areas during your migration plan.

Compute Type

SQL Server requires memory, hence for production workloads use VM sizes with 4 or more vCPUs such as E4S_v3 or higher, or DS12_v2 or higher. M-series machines offer more RAM per CPU core hence it’s beneficial to SQL performance. You should seriously consider M series machines when building in Azure VMs.

Storage

The SQL Server files MDFs and LDFs must be separated with different underlying storage attached to those separated disks. Striping may be required if greater IOPS are needed. Cache policy should be off for SQL Server but read cache could be enabled on MDF drives. It requires testing to confirm ratio of read/write patterns for the MDF.

In conjunction with this, when formatting disks it is recommended that you use a 64-KB allocation unit size for data and log files as well as TempDB. If you have a SQL Server background this will remind you of an on-premises configuration for optimal performance. Running SQL on VMs in Azure is no different when it comes to configuration setup.

Compression

Further performance improvements can also be made by enabling database page compression over row compression. CPU analysis is needed as there is a 5% overhead, but the benefits outweigh the costs. Page compression always includes row level and is applied once the page is full and applied once SQL Server deems that there will be a meaningful amount of space saved. Another form of compression called backup compression could also be enabled.

TempDB

As per Microsoft best practice, TempDB should be built on its own dedicated premium SSD disk. Again if further IOPS are needed for the specific solution in question, then disk striping should be considered. In terms of file layout, 1 file per core, up to 8 files maximum – again very natural to read for those with SQL Server experience.

Post Testing

Based on experience I like to validate that the storage layer is designed to an optimal standard, the following metrics should be tracked:

  • \LogicalDisk\Disk Reads/Sec (read and write IOPS)
  • \LogicalDisk\Disk Writes/Sec (read and write IOPS)
  • \LogicalDisk\Disk Bytes/Sec (throughput requirements for the data, log, and TempDB files).

This is to deduce whether or not we have IO latency/bandwidth issues.

SQL IaaS Extension

The next topic you need to think when moving to a VM design is about the IaaS extension. This extension helps you manage your SQL VM. There are two modes, lightweight (which unlocks few features) and full mode. As you know when going down the IaaS route, you still need to do a lot of management and administration. With full mode extension, you get the following features to help ease the burden:

  • Automated backups.
  • Automated patching.
  • Azure Key Vault Integration.
  • Flexible licensing and versioning – with this you can easily change versions and editions without any net new VM deployments.
  • Microsoft Defender for SQL integration.
  • Built-in health assessments.

If your organisation doesn’t really have DBA knowledge to help configure things like important backups, this is a great choice to enable.

To read how to enable this please see the following link which shows some PowerShell commands.

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-agent-extension-manually-register-vms-bulk?view=azuresql

Azure SQL Managed Instance

If you know you can move your database to Managed Instances (If the DMA analysis tells you) then you will enjoy many benefits of a cloud-native approach, these include:

  • Built-in High Availability architecture within the product itself.
  • 99% SLA.
  • Azure AD Integration.
  • Scale up and down with ease.
  • Built-in backups.
  • Built-in Maintenance and patching.
  • Native TDE – Transparent Data Encryption.
  • Choice of General Purpose or Business Critical editions:
    • General Purpose – Supports a vCore range of 4-80 cores which also dictates your memory, storage, I/O capabilities and SQL Log write performance.
    • Business Critical – This is the edition you use for elite performance requirements (significant increase in costs). This is based on classic Always on AGs with 1 read replica included, extremely fast SSDs meaning higher IOPs and I/O throughput rates than General Purpose.

Migration Components

Technically the move is quite easy, assuming you have the relevant components ready (like the SQL Credential and SAS key).

  • Create a credential on the source server (SQL VM).
    • The secret is needed which is a SAS token that needs to be created on the Azure Blob Container.
  • Backup the database to URL location – Azure Blob Container.
  • Login to Azure SQL MI via Azure AD Auth (sysadmin group aligned).
  • Create the same credential on Azure Managed Instance like the first step.
  • Issue a restore file list only to confirm you can communicate with the BAK file(s).
  • Issue a restore from URL command stating the URL location.
  • Post checks on Database.

The Architecture of Migration to Managed Instance

Architecture of migration to Managed Instance

Conclusion

Now is the time to move off your SQL Server 2012 platform. From reading this article hopefully, you can see that analysis needs to be conducted before you begin anything. Then you need to decide which option best suits your modernisation project, will it be an on-premises upgrade to SQL Server 2019 or will you take the opportunity to move to Azure and use one of its many options that are available to you.

You can get in touch with Ballard Chalmers for your modernisation needs. As Microsoft experts in both SQL Server and Azure, consultants will be able to help advise or undertake the full modernisation project. Contact now.

References

Post Terms: Azure SQL Managed Instance | Azure Virtual Machines | SQL | SQL Server | SQL Server 2012 | SQL Server 2019

About the Author

Arun Sirpal, writing here as a freelance blogger, is currently a two-time Data Platform Microsoft MVP, specialising in Microsoft Azure and Database technology. A frequent writer, his articles have been published on SQL Server Central and Microsoft TechNet alongside his own personal website. During 2017/2018 he worked with the Microsoft SQL Server product team on testing the vNext adaptive query processing feature and other Azure product groups. Arun is a member of Microsoft’s Azure Advisors and SQL Advisors groups and frequently talks about Azure SQL Database.

Education, Membership & Awards

Arun graduated from Aston University in 2007 with a BSc (Hon) in Computer Science and Business. He went on to work as an SQL Analyst, SQL DBA and later as a Senior SQL DBA, DBA Team Lead and now Cloud Solution Architect. Alongside his professional progress, Arun became a member of the Professional Association for SQL Server. He became a Microsoft Most Valued Professional (MVP) in November 2017 and has since won it for the fourth time.

You can find Arun online at:

Previous Post
Next Post
Back To Top