You can upgrade from SQL Server 2012 to SQL 2019.
Before
upgrading SQL Server, enable Windows Authentication for SQL Server Agent
and verify the default configuration: that the SQL Server Agent service
account is a member of the SQL Server sysadmin group.
Amazon RDS (Relational Database Service) is a cloud computing solution from Amazon Web Services that aims to facilitate the process of setting up, deploying, and scaling a relational database in the cloud.
Azure SQL Database is a cloud-computing database service (Database as a
Service), that is offered by Microsoft Azure Platform which helps to host and
use a relational SQL database in the cloud without requiring any hardware or
software installation. Also, it provides various advanced features to its users
and some of them can be listed as the following;
i) Enabling high availability configurations for SQL Server running in containers,now kubernetes can be used as an orchestration layer while configuring highly available systems with Always On Availability Groups.
In SQL Server 2019, High availability options have been enhanced by adding support to different platforms which includes
It will be the right time to move to 2019 if your analytics delivery time is more than desired, and developers complain about the lack of DAX functionality. It is the way to go if the information security is concerned with the uncontrollable degree of sensitive details exposed in the reports. And it might be a remedy if your server environment suffers from performance issues.
Benefits of moving from SQL Server platform 2016 to 2019 :-
Many companies are still using the 2016 version of SQL Server and Analysis services since the official support lifespan is up to 2025. This article will help the Data Analytics and Business Intelligence department managers to decide if it is the right time to upgrade to the 2019 platform now.
We will take a look at some of the features relevant for a data and analytics platform when moving from 2016 to 2019 (including those from 2017). And specifically focusing on the benefits for Data Science, Governance, Analytics, and Business Intelligence. We would not focus on the benefits of the IT department and database administrators except for features that improve query and data update performance.
Data Analytics: Calculation groups in Tabular models. According to some experts, it's the most important feature ever implemented in DAX. It would spare development time spent on time intelligence calculations. Now Tabular models would not require redundant measures created for every combination of base measure and calculation type needed e. g. events by month-to-date (MTD), quarter-to-date (QTD), year-to-date (YTD), year-to-date for the previous year (PY).
Object-level security in Tabular models Version 2016 implemented row-level security. In version 2019, access to specific users or groups also could be set for columns and tables. In our days of GDPR and other data restraining policies, the ability to provide users the data which has passed through granular control is highly demanded. The introduction of many-to-many relationships for two non-unique tables helps to avoid unnecessary “snowflake” models.
Data Science & Exploration: Support for Machine Learning, R, and Python has for SQL Server provides more flexibility for data scientists. PolyBase feature was introduced in SQL Server 2016 and allowed reading data from external data sources like Hadoop and Azure using SQL queries. SQL Server 2019 added connections to Oracle, Teradata, MongoDB, SQL Server, and ODBC.
Master Data Services: HTML controls replace all former Silverlight components. Silverlight will be supported until 2021. HTML works faster, and users do not need extra plugins installation. It is now possible to move MDS to Azure managed instance in Cloud.
Performance: Query interleaving with short query bias allows concurrent queries to share CPU resources, so fast queries are not blocked by the slow ones. Intelligent Query Processing is a behind-the-scene set of enhancements that affect the behavior of the Query Optimizer that generates execution plans for queries. According to some observations, this could give up to a 10x performance boost for certain queries.
Data Governance: A new feature of SQL Data Discovery and Classification is natively built-in SQL Server 2019 and allows marking of columns in a database that contains sensitive information. This feature is designed to help with GDPR compliance and traceability for sensitive information. The classification metadata is stored on SQL object level and is not necessarily designed for a data platform or data warehouse where data flows through ETL processes and are moved to SSAS and or Power BI.
Administration: Accelerated Database Recovery (ADR) - speeds up the time SQL server needs to perform database recoveries, transaction rollbacks, and failovers.
N.B: One thing to remember when you've upgraded SQL and SSAS server to 2019, to enable the new features you need to update the compatibility level for your SQL databases and analysis models.
