With its public launch two weeks ago, Microsoft has made SQL Server 2016 widely available. But like with any product release, the first question is ‘What’s it offer for my data?’
It’s a fair question, especially when you’re forking out anywhere from US $3,717 for a Standard Edition or $14,256 for Enterprise. That’s per-core, in two-core packs. Anyone would want to get their money’s worth. (Thankfully, Microsoft is offering a free 180-day trial, so you’re not forking out upfront capital for the promise of features.)
For those who want a rundown of the features of Microsoft SQL , look no further. We’ve summarized the eight best perks of SQL Server 2016 so you know exactly what it can do for your business and your data.
As of BackupAssist’s v9.4 update, our software fully supports Microsoft SQL Server 2016.
1. Always Encrypted, Better Security
When you’re storing huge amounts of data, the last thing you want is for it to be compromised or stolen. In SQL Server 2016, you can protect your data by turning on the ‘Always Encrypted’ feature. This means access to your encrypted data can only be made by applications with the encryption key that access your SQL Server 2016 database.
SQL itself doesn’t have the encryption key, since it’s a master key stored in your system. It’s worth noting that this process of encyption and decryption of your data is done at the database driver level. That means the database owners or admins don’t have access to your unencypted data at all. Honestly, this feature is fairly awesome, since data theft and security breaches happen most often by someone inside an organization, not outside of it.
So, how’s this affect performance? Not all that much, really. To increase performance, only the sensitive data is encrypted, not the whole lot. So the non-sensitive columns such as the primary key are not encrypted.
SQL Server 2016 offers two modes of encryption; deterministic and random. The first means you get the same value when you encrypt your data multiple times, the second means you get different values each time you encrypt your sensitive data. So all of this is pretty cool, especially if you don’t want a third-party software safeguarding your data.
2. Enhanced Always On Availability Groups (AGs)
When you’re running an SQL Server, you usually want it running all the time, with consistent data, and backed up near-constantly. Rather than adding new functionality to their AGs, Microsoft have gone for high-availability and disaster recovery enhancements instead. (Which, you know, as backup and recovery specialists, we totally approve of).
So in SQL Server 2014, your failover was determined by the overall health of your server as a whole. That meant if a database in an AG went offline, the failover wouldn’t be triggered because overall your server was okay. But with SQL Server 2016, you can make sure all databases within the affected AG failover if it becomes unhealthy, even if the SQL server as a whole is fine. Noted, you’ve got to activate this feature—it’s not on from the start.
That’s not all. It has the ability to have up to three synchronous replicas, Distributed Transaction Coordinator (DTC) Support, and round-robin load balancing.
3. Speedier Processing and Query Store
Compared to SQL Server 2014, the 2016 release is significantly speedier. That’s a boon for any business. Without even using its in-memory computing features, queries execute 25% faster on the same hardware.
When you activate the features I mentioned, like SQL Server’s 2016 in-memory updatable columstores? For some queries, it processes up to 100 times faster. You know, so just a bit faster. In-memory tables can now hold up to 2TB of data, up from 256GB.
According to Microsoft, who never trash-talk their competition, these features “aren’t something you’ll likely get from other relational databases like MySQL any time soon.” Ouch. Burn cream on isle five?
Another thing SQL Server 2016 offers is changes to the query optimizer, which used to negatively impact performance. Query Store offers a history of query executions and their performance, and identifies those that have gotten slower recently. That means admins and developers can force the use of an older, better plan to fix the issue, instead of the old slog of identifying and resolving the performance problem.
4. Real-Time Operational Analytics
So with the better performance power offered by the features above, SQL Server 2016 is able to offer real-time operational analytics. That lets you fine-tune your system for optimal transactional performance and increase workload concurrency.
5. Revamped SQL Server Data Tools
So in SQL Server 2016, Microsoft has combined the wildly popular Business Development Studio (BIDS) with SQL Server Data Tools (SSDT). That means just one set of comprehensive tools for everyone instead of having two tool kits to deal with.
6. Native JSON Support
For the longest time, the standardized data exchange format JSON (JavaScript Object Notation) hasn’t been supported by SQL Server, so you had to convert it. SQL Server 2016 thankfully does away with all this and natively parses and stores JSON as relational data, and supports exporting it to JSON.
7. Stretch Database
SQL Server 2016 helps you reduce your storage and processing costs with its ‘Stretch Database’ feature. Basically, it lets you move parts of your tabled onto an Azure SQL Database in the cloud securely. When you perform a query, the query optimizer knows which ones are on the server and which ones are in Azure, and performs accordingly.
So the idea is that you keep your frequently accessed or hot data on premise, and use your cold data onto the cloud. That means you can use low cost Azure storage and still have high performance. You’re only charged when you’re using it for queries.
8. Full BackupAssist Support for SQL Server 2016
When Windows brings out a new product, you want to make sure it’s supported by other software developers. Well you can rest easy, since it’s fully supported by BackupAssist. All backup, restore and recovery features that worked for previous versions of Microsoft SQL Server also work with the 2016 version.
If you’ve got SQL Continuous, you can protect your SQL Servers with near-continuous backups, minimizing Recovery Point Objectives (RPOs) to five minutes. Read more about our SQL Server 2016 support here.
Or even better, give BackupAssist a free 30-day trial along with Microsoft’s 180-day trial, and see if both products meet your business needs.
