XTIVIA is highlighting new features in SQL Server 2016! We can set you up with all the exciting new features available in SQL Server 2016. Here’s how implementing new features will benefit your SQL Server environment.
Stretch Database
Stretch Database allows you to keep your most recent production data local to your location but migrate the historical data to the Microsoft Azure cloud. This new feature gives you the benefit of presenting the data as a single data store while behind the scenes allowing you the flexibility to segregate active and historical data helping to reduce cost of storage, backups, and help speed up access to “Live” data.
Basic Availability Groups
Basic Availability Groups replaced Database Mirroring technology. This feature is available in Standard Edition giving you the ability to have a single primary and single replica database.
Distributed Availability Groups
Another new feature in SQL 2016 is the ability to have availability groups defined on two different Windows Clustered. This new feature allows available groups to exist in both the production environment as well as your disaster recovery (DR) environment. If connectivity to your DR site is interrupted, it will not affect your internal availability groups.
Query Store
You can now compare differences in your query plans over time without the need to create your own custom solution. SQL Server now stores query plans with executed queries and runtime statistics to make performance troubleshooting faster and easier.
Live Query Statistics
Gone are the days of viewing only the estimated execution plan or waiting for that long-running query to finish to see the actual execution plan. SQL Server now gives you the ability to view the execution plan as the query is active on the system.
In-Memory OLTP
First introduced in SQL 2014, In-Memory OLTP received several significant updates in SQL 2016. The ALTER TABLE command received a performance update to minimize the number of log writes to help reduce overall IO usage. Stats are now updated automatically. Additionally, several DDL and DML commands were added to your arsenal including Index on null values, foreign keys and DML triggers.
Changes in SQL Server Tools
Two main changes were made to the SQL Server Tool set. SSSM is no longer a part of the SQL Server install media, but a separate install you can download from Microsoft. Install media is no longer needed to run the Upgrade Advisor. It is now a standalone tool which can be downloaded from Microsoft.
Changes to TempDB
New with 2016, when installing SQL Server, the installation wizard will detect the number of CPUs on the system and automatically populated the number of TempDB datafiles recommended to support the number of CPUs. The new wizard gives you more flexibility on configuring drive locations of the TempDB files. On high performance systems, Trace flags -T1117 and -T1118 no longer need to be set.
Wrap It Up
SQL Server 2016: New features and updates address customer’s request for added and enhanced functionality of the software. We at XTIVIA are excited to sit down with you and discuss how these enhancements can help address issues you are facing in your environment.