Wibke Carstensen

28 June 2016

SQL Server 2016 – what’s changed in the SQL Toolbelt?

Over recent months, Redgate’s development teams have been busy updating the tools in the SQL Toolbelt to support the valuable new functionality released with SQL Server 2016. To achieve this, most tools now support the syntax for SQL Server 2016’s key features, letting you do even more with them.

Stretch Tables

Our flagship tool and the engine for a lot of our products, SQL Compare, now supports Stretch Tables. This new SQL Server feature migrates historical data to Microsoft Azure, allowing users to stretch warm and cold transactional data dynamically from SQL Server on-premise to the cloud. Data is always online and available to query, and can be accessed seamlessly whether it’s stored locally or remotely. Because the location of the data is completely transparent to the application, Stretch Database doesn’t require any changes to existing queries or applications.

SQL Compare now recognizes the Stretch DB syntax, so you can sync schema changes as you usually would. You simply set the policy that determines where data is stored in SQL Server and use SQL Compare as normal to compare and deploy changes across databases and environments. Whether your data is stored on-premise or in Azure, the location is invisible to SQL Compare.

The principle is the same for SQL Data Compare, but it’s worth being aware of the bandwidth and storage implications of pulling large amounts of data from the cloud to on-premise and vice versa.

Operational Analytics

Our comparison tools also support Operational Analytics. In Microsoft’s own words:

‘SQL Server 2016 introduces the ability to run both analytics and OLTP workloads on the same database tables at the same time. [To achieve this] real-time analytics uses an updateable columnstore index on a rowstore table. The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data. This minimizes the performance impact of both workloads running at the same time. SQL Server automatically maintains index changes so OLTP changes are always up-to-date for analytics. With this design, it is possible and practical to run analytics in real-time on up-to-date data.’

When you take advantage of this new SQL Server feature, SQL Compare and SQL Data Compare now deploy these indexes alongside any other changes.

Dynamic Data Masking and Row Level Security

Both SQL Compare and SQL Data Compare support the syntax for Dynamic Data Masking and Row Level Security, but as a user you need to ensure you’re logged in with the appropriate level of permissions.

Deploying masked data is useful, for example, when you’re syncing changes to a dev or test environment. If you want developers to see the unmasked data or specific rows of the data, they need appropriate permissions.

SQL Source Control and SQL Change Automation are built on SQL Compare functionality, and the changes described for the comparison tools equally apply to these tools.

New SQL keywords

One of the favorite products in the SQL Toolbelt is SQL Prompt, Redgate’s code completion and formatting tool.

It now offers suggestions for all the new SQL Server 2016 syntax and everything pretty much works exactly as you’d expect. The screenshot on the right, for example, shows how it predicts dynamic data masking keywords. We’re still looking to introduce better support for changes to formatting, so drop me a line if you have any feedback for us while we look at implementing this.

Always-on Availability Groups

In the context of SQL Server performance monitoring and alerting, SQL Monitor has a key advantage in the support it offers for Always-on Availability Groups. This feature is now part of SQL Server’s standard edition and is much more widely available.

Availability Groups allow sets of databases to be replicated across multiple SQL Server instances for the purposes of reporting and disaster recovery. Availability Groups support automatic failovers, for example, making them an ideal high availability solution. Data can be replicated synchronously or asynchronously to any number of secondaries, and SQL Server instances can participate in multiple Availability Groups.

It can, however, become complicated and Anthony Nocentino, Enterprise Architect at Centino Systems, put it succinctly in his interview with Redgate’s Mark Champion: ‘Managing SQL Server Availability Groups is a bit like spinning plates.’

To address this challenge, SQL Monitor covers the concerns and complexities Availability Groups can introduce with an awareness of the operational and performance issues that arise. It provides monitoring coverage for all host machines and Availability Group replicas, and gives Availability Group-specific overviews, alerts, metrics, and wait types.

The Availability Group overview also shows at a glance the status and performance of an Availability Group and allows you to drill down into the replicas and databases. With 17 Availability Group-specific metrics and six new alert types, it gives you a complete picture of your databases allowing you to use Availability Groups with greater confidence.

Temporal Tables

The Temporal Tables feature in SQL Server 2016 brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. We’re now looking at supporting this and your feedback would be invaluable to our development teams.

Do you want to deploy Temporal Tables like any other table, for example, or would you prefer a more granular approach that specifies a point in time and limits the deployment to specific data? Let us know, and give us any other feedback, at dlm@red-gate.com.

Find out more about how SQL Toolbelt can double your productivity with industry-standard tools, protect your data with backups and monitoring, and include your database in agile processes.