Modernizing your SQL Server Data Platform

Are you taking advantage of the new, modern features in SQL Server 2019? A lot of administrators don’t know these exist. Some were enterprise-only features and are now part of the Standard Edition.

Here are some examples of the valuable features that SQL Server administrators often miss:

Availability groups Are you still using log shipping or mirroring? If so, it might be time to rethink your high availability.

Always-on availability groups are a feature that was Enterprise only in the past. As of SQL Server 2016 SP1, it is now available in Standard Edition. There’s a limitation: you can only have one database per availability group. Sometimes if you have many databases, you must have many availability groups.

Transparent data encryption

Another feature that used to be enterprise only that they have since switched is transparent data encryption. This feature involves protecting your data at rest.

One of the drawbacks of transparent data encryption in the past is that the backups couldn’t take advantage of compression because you encrypted the data, so the compression algorithms didn’t work on it.

In the new versions of SQL Server, encryption is not only for data at rest but also what is known as always encrypted, protecting the data in motion.

You will need a certificate to decrypt that data when working with applications. With this encryption active, if a DBA were to run select star from one of these always encrypted tables, it would pull the results, but the columns in question would be encrypted.

Columnstore indexes

Columnstore indexes are a promising technology that they’ve added for large datasets, especially data warehouse tables. You may find it improves query performance when using these indexes against a datamart or operational data store. Columnstore indexes are an Enterprise-only feature at one point that can speed things up if you have data warehouse-type data in your environment.

Microsoft says column-based data storage and query processing can “achieve gains up to 10 times the query performance in your data warehouse over traditional row-oriented storage.” You can use it to improve performance over a traditional non-clustered index.

Table and index partitioning

Something that has been around for quite a while but people don’t often use is database partitioning. Now in newer versions, SQL Server offers table and index partitioning.

If you’re working with gigantic amounts of data and using the correct partitioning function and schema based on your data type, it can speed up row retrieval.

This isn’t a new feature and people are hesitant to take advantage of it. But if implemented correctly, partitioning can provide excellent performance improvements.

Automatic database tuning

One of the Enterprise-only features that Microsoft recently released was automatic database tuning. You can turn it on at the database level.

For example, if you have parameter sniffing or a problem with a query that, after a statistics update, runs and picks a bad plan. With automatic database tuning, it’ll notice that the plan is regressed and poorly skewed from the previous plan. It will try to force it back to the last good plan. Before this, a DBA would need to go in and do a recompile. SQL Server will now look at the problem and do it automatically with automatic database tuning.

Query Store

Microsoft introduced Query Store in SQL Server 2016. All you have to do is enable it for a given database. It captures all the queries that run against that database and lets you look at a point in time. Utilizing Query Store lets you analyze performance in the environment.

For example, if you had a problem between five and seven yesterday evening, you put that in, and it shows you what ran between five and seven. It makes it effortless to find things.

One note: You can’t wait until you have a problem to activate this and have worthwhile information. You must turn it on first.

Set Max DOP per database

In the past, the maximum Degree of Parallelism (max DOP) was a server-level configuration with SQL Server. But in the newer versions of SQL servers, you can also set the max DOP at the database level.

For example, let’s say you have a multi-purpose instance with SharePoint running alongside a few other application databases. Microsoft’s recommendation for SharePoint is to set max DOP to one (1), but all other databases require a different setting. Since most companies are running mixed-use mixed application servers, you might have a vendor recommendation from one database to another. Now you can set max DOP at the database level to accommodate this scenario.

Conclusion

You may be a lone DBA working on databases without a Managed Services partner to lean on. You probably don’t have the opportunity, or time to stay current on the most modern SQL server features. What if you had access to a company like MiCORE, for database administration and SQL Server Consulting Services where you can get access to a team that collaborates with our partners and learns from one another? MiCORE can help you stay up-to-date with the latest industry standards and best practices.

MiCORE provides its clients with the assurance their critical database systems are fully covered, and they can focus on their business. As a specialized database managed services and consulting organization, MiCORE works with many different companies across various industries. The vast experience we have gained working on a multitude of client engagements allows us to quickly engage with our clients and provide best practices to maximize database performance and stability.

MiCORE provides database support services for Oracle, Microsoft SQL Server, major open source databases (MySQL, PostgresSQL, MariaDB), and AWS databases. MiCORE provides database cloud architecture, migration, and management across AWS, Azure, and Oracle Cloud.

We can augment or provide full-time database support services across all your key platforms.