Can my old MSSQL environment still work?

The title of this publication is the question many system administrators ask themselves. After all, the SQL 2008 environment runs fine, the write-off time has passed, almost all bugs have now been solved and we have the most bang for the least buck. True, it seems! But this is not the case. The main argument is security. New exploits that are not fixed in old versions make an environment vulnerable. Microsoft develops a lot on SQL Server and focuses on improving performance, High Availability, security and new features. We discuss these points in this publication. In this article we also explain why it is important to use new versions.

Performance

In SQL Server 2014, the query estimator has been completely rewritten for the first time in 20 years. The query estimator is an essential part of creating a query plan. This plan is usually created for every query that comes along and ensures that the data is returned/processed as quickly as possible. A significant performance improvement has been achieved with this new query estimator. With the arrival of SQL Server2005, included columns are also possible in the index. This greatly relieves the CPU and memory. If there is then an information request for several columns of a table, the index no longer needs to be created on all columns, but only on the columns that are included in the join/where clause. This results in speed gain.

As of version 2014, in-memory optimization has been added for databases. With these features it is possible to store data in the memory so that this data is available faster.
Many transactions (inserts) benefit from this. This reduces blocking and achieves a performance improvement of 5 to 20 times. This effect is even more apparent when it concerns stored procedures with many calculations. In version 2016, Query Store offers the possibility to easily visualize and follow heavy query plans. Version 2017 allows more than 8 indexes on an in-memory optimized table.

High Availability

From version 2016 it is also possible to distribute Availability Groups over 2 clustered environments. With Availability Groups (a mirrored database) one can unburden the production database by referring the BI environment to the database that is on standby. Stretched databases ensure that large tables can be placed in the cloud.

Security

From version 2014 it is possible to encrypt the backups. This guarantees that if the backup files are stolen, the data cannot be viewed. Transparent Data Encryption and Column Level Encryption provide protection when this data is in-rest and/or in-flight. Or take Dynamic Data Masking, which is an excellent feature to be AVG/GDPR compliant in your development/test/acceptance environment. The options for auditing who accesses the environment have also been expanded. SQL Server is therefore the most secure database platform of the last 8 years.

New features

From version 2012 there is Master Data Management. This tooling helps, especially medium-sized and large companies that consider data quality to be of paramount importance, in deduplicating data. As of 2016, SQL JSON provides support for import/export/parsing and Vulnerability Assessment.
From version 2017 SQL Server can run on Linux and an installation on GUIless Windows (Server Core) is also possible. Support for the R programming language has been added. You can now execute a Python script directly against the SQL Server for the benefit of, for example, the mature BI organization (artificial intelligence). SQL Server 2019 offers possibilities to run SQL Server on almost any platform: native or through Docker and Kubernetes. And offers Polybase to combine cross database platform data. The performance has also been further improved and here SQL Server is the fastest OLTP database platform according to www.tpc.org .

BI

With the included Reporting Services you can create simple and advanced reports. These can be scheduled to be automatically sent and/or saved. Do you want to continue with BI? Power BI allows you to create a spiffy dashboard for management, complete with mobile device support and drill-through functionalities. With the included Analysis Services, you can create OLAP cubes so that managers/specialists can examine data in understandable language.

The Migration

When you then upgrade, a system administrator/project manager has to deal with a lot. What are the risks? How should the hardware be set up? Do applications need to be modified? SQL Server is far from optimally configured in a standard installation. This often results in a large performance loss. Often it is not known from which servers & clients a connection is made to the database. Here too, DBA.nl provides insight to make the migration a success. It is good to have a reliable all-round database partner behind you. DBA.nl has a lot of experience with migration processes and, if desired, offers the possibility to limit the downtime required to switch to the new environment to a few seconds. Microsoft is constantly improving its products to stay on top. With significantly lower costs compared to Oracle and widely available support, it is an extremely suitable relational database management system for all companies in the middle and enterprise segment.

DBA.nl is the all-round database expert specialized in setting up, maintaining and monitoring database environments. In addition, we provide advice and remove performance problems.