Updated 2nd December 2022 to reflect the change from SQL Server 2019 to SQL Server 2022
Introduction
As you know, on July 12th 2022 Microsoft ended 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, that being SQL Server 2022.
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:
- 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
SQL Server 2022
The SQL Server 2012 engine is now 10 years old and fell out of Extended Support on Jul 12, 2022. Significant changes 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 2022. Some key features to take note of, which should really make the move to 2022 an attractive option, are listed below.
Intelligent Query Processing (IQP)
This family feature made its debut with SQL Server 2017 but 2022 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.
With SQL Server 2022 the new features within the IQP family include:
- Parameter Sensitive Plan Optimisation
- Optimised Plan Forcing
- Percentile Grant Feedback
- CE Feedback
- DOP Feedback
- Feedback Persistence
- Approximate Percentile
QUERY STORE
This is now on by default as opposed to before SQL 2022 when a DBA would need to manually enable this. Microsoft has put much effort into improving this feature and it is great, it is the “black box” recorder for your SQL Server and a very important tool within the performance tuning kit. The key area of improvement comes in the shape of Query Store hints. Query Store hints provide a direct method for developers and DBAs to shape query plans without changing application code. They are very similar to plan guide hints, but Query Store hints are much easier to use than plan guides and they are persisted so they will survive server restarts too.
High Availability
With SQL 2022 we can now create a contained availability group. With this, you can create users, logins and the relevant permissions at the availability group level and they will automatically be consistent across replicas in the availability group thus reducing the admin overhead of syncing these manually with previous versions.
Another great feature is the Managed Instance link which enables near real-time data replication from SQL Server to Azure SQL Managed Instance. With this, you can use it for off-loading reporting or even as a possible Disaster Recovery strategy by manually issuing failovers between SQL Server 2022 instance and the Azure SQL Managed Instance. Microsoft uses a distributed availability group under the covers as shown below.
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.
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 2022 has improved on its 2019 equivalent by optimising certain elements of ADR, such as:
- User transaction clean-up
- Clear pages that could not be cleaned by the regular process due to lock failure.
- Reducing memory footprint for PVS page tracker
- This improvement tracks persisted version store (PVS) pages at the extent level.
- Accelerated Data Recovery (ADR) Cleaner improvements
- Accelerated Data Recovery (ADR) cleaner has improved version clean-up efficiencies to improve how SQL Server tracks and records.
- Transaction-level persisted version store (PVS)
- This improvement allows ADR to clean up versions belonging to committed transactions independent of whether there are aborted transactions in the system.
- Multi-threaded version clean-up
- SQL 2019 the clean-up process was single-threaded within a SQL Server instance. Beginning with SQL Server 2022 this process uses multi-threaded version clean-up.
Azure Virtual Machines
So, you now want to move to SQL Server 2022 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 the 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’s 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.
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
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 2022 or will you take the opportunity to move to Azure and use one of the 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 us now.
References
- https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver15#row-mode-memory-grant-feedback
- https://blobeater.blog/2017/11/09/sql-server-2017-interleaved-execution/
