SQL 2016 Features News

1] Stretch Database

Stretch Database allows you to keep your most recent production data local to your location but migrate the historical data to the cloud(Microsoft Azure). 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.

2] 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.

3] 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.

4] 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.

5] 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.

6] 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.

7] 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: