previous article
It's been ten years since SQL Server 2012 was brought out. Ten years, and soon it will be over again – at least as far as extended support is concerned. Before we talk about what this means and what our options are, I would like to take this opportunity to look back on these ten years: On aspects whose foundations were laid back then, and which are now standard in the database landscape, especially in the BI context.
My personal top 5 from 2012:
- Columstore
The first version of the Columnstore index was released. This was a milestone as regards performance of BI workloads in SQL Server databases. - Always-on availability groups
Brand new at the time, now standard. Always-on availability groups ensure high availability of databases and allow them to be read simultaneously. - Tabular model
A new BI semantic model came onto the market: The tabular model was introduced in SQL server. Though regarded mainly with scepticism ten years ago, it is now widely recognized as the technical basis for Power BI. Also on board: Data-quality services and master-data services. - PowerView
Speaking of Power BI, does anyone still remember PowerView? Also available for the first time in 2012, this provided more "colour" in visualizations. Thanks to Silverlight implementation, however, this aspect is now obsolete. - T-SQL functions
Last but not least: SQL Server customarily provides a number of new T-SQL functions with each release. With this release, it was sequence objects, error handling, TRY_CONVERT, CONCAT, etc.
Due to SQL Server's modified license policy, however, this version was only hesitantly accepted by the market. The Enterprise Edition was now only core-based, and no longer based on Server+CAL.
" For cloud platforms, AI solutions and visualization tools, Microsoft has been a reliable partner who has worked with us over many years. We have delivered many successful customer projects together. These successes are, of course, due not only to the expertise of the Microsoft team but also to our excellent relationship with them."
End of Support: What is the problem now in 2022?
But now the time has come: According to the lifecycle policy für SQL Server 2012, extended support will end on 12.07.2022. Almost all IT policies stipulate and require support from the manufacturer for operations at enterprises. Wednesday, 13.07.2022 (probably with sunny weather) will therefore result in countless companies violating compliance guidelines.
End of support: What's next?
Which options, including those for modernization, are available for SQL Server 2012?
I would first like to briefly mention two possibilities before moving on to modernization which is the focus of this article.
- Option 1: Upgrade to SQL Server 2019, and then SQL Server 2022
- Option 2: Run SQL Server locally in an Azure environment. The keyword here would be Azure Stack HCI.
The following diagram shows another four ways offered by the Azure cloud for migrating SQL Server:
1️⃣ The most obvious idea: Lift and shift
We choose one of the prefabricated SQL VMs on Azure, backup and restore the databases, change the compatibility level – and we're done. In reality, however, this is not quite as simple. Network aspects, performance requirements and security issues often stand in the way of a fast migration. This necessitates clarification and establishment of high availability during VM operation. The advantage of this variant: SQL Server 2012 can be operated for another three years with extended security updates.
However, there are other possibilities of dealing with the topic of PaaS without concentrating on infrastructure:
2️⃣ SQL Server managed instance
From my own point of view: This is often a very good choice for modelling workloads in Azure while reaping the benefits of the cloud. SQL MI gives you a complete SQL Server instance in the cloud, allowing you to create up to 100 databases. Cross-database queries seen commonly on-premises are thus one of the achievable scenarios. The latest service levels offer a choice of up to 64 vCores and almost 900 GB of RAM – enough for most requirements. Plenty of storage capacity is also offered with SQL MI: Up to 16 TB are available here - that's sufficient space for a small database, also enabling business-intelligence applications.
3️⃣ Azure SQL DBs
If you want to migrate a single database or many small, mutually independent databases, then it's worth taking a look at the Azure SQL DBs. These are available as individual databases or as elastic pools. An advantage of such elastic pools is easy handling of peaks in the respective workloads, due to sufficient capacity provided for all databases by the various pools. It is therefore not necessary to ensure excess reserve capacity in advance for reliability during performance peaks. An interesting variant at the server level in the case of individual Azure SQL DBs: Serverless. If an application only needs a database backend at certain times, then the serverless variant is worth considering. With the help of the "auto-pause" functionality, only storage costs are incurred, without any costs of vCore. This proves ideal in scenarios which are not 24/7.
4️⃣ Azure Synapse analytics
If SQL Server is to operate according to a BI workload, it might be worth considering the fourth variant as an option: Azure Synapse analytics, a service which combines data integration, data warehousing and big data. Synapse is also equipped for future issues such as AI and machine learning. Synapse provides a SPARK engine as well as 2 SQL engines. The serverless engine makes it possible to execute ad-hoc queries to the data lake. Costs are incurred here only per individual query, not per CPU or server, or the like. This is particularly interesting for initial explorations of data. Powerful, dedicated SQL pools technically representing an MPP (massively parallel processing) database are available for the segment involving the actual data warehouse. The greater the quantity of data and the ability to distribute them, the greater the benefit provided by such architectures during queries.
All PaaS options have one thing in common: Administration is greatly simplified, and solutions can be scaled dynamically as required! Costs can be saved additionally by means of Azure hybrid benefit. Here it is possible to "credit" existent SQL Server licenses in Azure.
Which technique is the right one depends significantly on the current use of SQL Server and its features. Is a high-performance database or BI solution involved? We ascertain the optimal setup for the new environment and pave the way there jointly with you.
The starting point here is a solution assessment also supported by Microsoft. This indicates fundamental aspects and identifies potential roadblocks.
Would you like to learn more about the various possibilities, or do you need help in identifying the one ideal for you? We will be happy to assist you.
