SQL Server Administration

Fun with Filegroups

Filegroups are an interesting and useful (if not actually all that fun, despite the title) concept within SQL Server. They provide not just a way to group objects like tables together, but also provide a mechanism to help us to speed up the backup, restore and recovery of the databases. And sometimes they can even give us a little help with the performance or to migrate data to disks that have more space available.

In this post, focus will be in archiving a table that we no longer need by making a read-only copy of it. In a real world scenario we’d also consider putting that to a slower disk, if it’s not frequently used, but my demo environment only has a single data disk.

Hiding (mostly) in plain sight: Dynamic Data Masking

One of the more recent additions to SQL Server security features is the Dynamic Data Masking (DDM), included with the 2016 version. Like the Transparent Data Encryption I blogged about recently, DDM is a feature that is relatively easy to implement, and doesn’t require a lot of changes to the application. And just like pretty much everything is easy in a real life, it too has some limitations.

Protecting Data at Rest: Transparent Data Encryption

I recently read an article which stating that since the GDPR came in force, there has been 59,000 data breaches reported in the EU. I must admit, that while I did anticipate that we’d see a surge in these numbers, due to reporting requirements in the legislation. I really did not expect the numbers to look that terrifying.

From the point of view of a SQL Server DBA, there is a number of different ways to protect your data. Some of them are even quite easy to setup, such as Transparent Data Encryption (TDE). So let’s have a look at how to set that up!

My favorite PerfMon counters

About two months back I ended up moving to another job, which has unfortunately kept me to be bit too occupied to find the time to blog, until now. Due to this previously mentioned career change, I have been working quite a bit with monitoring, and it gave me a spark to write this post about my favorite PerfMon counters.

Like most DBAs I rely quite a lot on monitoring, and like most DBAs I too have my own set of PerfMon counters that I rely on to provide me an accurate view of what’s happening in the environment I am administering. In this post, I’ll describe what are my favorite PerfMon counters.

Connecting to SQL Server instance through Dedicated Admin Connection

Dedicated Admin Connection is one of the easy-to-forget features in SQL Server that can really save your day. DAC (no relation to Data-Tier Applications, just shares the acronym), as it’s often called, is the way you can try to access a SQL Server instance that is in such a bad shape, that no normal connection is available. This can be due to resource exhaustion or if you happened to create a slightly wonky logon trigger. In this post we’ll look at how you enable Dedicated Admin Connection (for remote users) and how you connect to SQL Server using the DAC.

Continue reading “Connecting to SQL Server instance through Dedicated Admin Connection”

Offloading DBCC CHECKDB

I recently had a discussion about the ability to offload DBCC CHECKDBs to a secondary database using Active Secondaries in SQL Server Availability Groups. While it is fully possible to run database consistency checks against secondary database (and there’s plenty of recommendations floating around for doing this), it needs to be pointed out that using Availability Groups for this does not equal of running checks on the primary database.

Let’s consider this for a bit…

Continue reading “Offloading DBCC CHECKDB”

SQL Server and Joins

I was recently looking at some Execution Plans with a co-worker and we ended up discussing the different types of joins in a SQL Server and what implications they might have when it comes to query performance. While many of us are familiar with writing joins, as we usually don’t query just a single table, there are quite few things about the physical joins that may not quite obvious. In this post our focus will be in the physical joins, but we will also very briefly look at the different types of logical joins also.

Continue reading “SQL Server and Joins”