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.
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.
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:
- Are there any migration blockers? Also provides some solutions.
- Does your current SQL Server 2012 have any unsupported features that no longer exist within the newer versions?
- 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
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:
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.
If 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.