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”

Logical Joins: OUTER JOIN

In my previous post about logical joins I wrote about the most common type of SQL Server join, the INNER JOIN. Naturally the logical follow-up is to look at the OUTER JOIN. Syntax for OUTER JOIN is bit different from the INNER JOIN, as you need to define either LEFT, RIGHT or FULL keyword (more about FULL in another post). Unlike the INNER JOIN which only returns rows from the tables if there are equal values in join column, OUTER JOIN will return all the rows from either LEFT or RIGHT even if there is no matching value in the table you are joining.

Continue reading “Logical Joins: OUTER JOIN”

Logical Joins: INNER JOIN

While back I wrote this blog post about SQL Server joins, the focus back then being in the physical ones. So I figured that now would be a good time to re-visit the topic and look at the logical joins also. Where the physical joins are decided by the SQL Server engine the logical joins are the ones we write. There are quite a few different kind of logical joins, so I will be writing multiple posts about this topic.

Continue reading “Logical Joins: INNER JOIN”

Perils of synthetic test data

binary-code-475664_640
Data, data, data

I was recently involved in a query tuning work where we used synthetic, rather than production data, to validate the results of our query and index tuning work. We faced some issues with the generated data that had quite a severe impact on our testing, and that prompted me into writing this blog post. Lets start by first defining what is synthetic data. In my view synthetic data is data that resembles actual production data, but is artificial/generated. I have seen similar (and also more detailed) definitions elsewhere and I think it is a good one.

I also like to point out that there are plenty of good reasons for using synthetic data in testing, as production data is often strictly regulated and not easily available for testing purposes.  However, you need to be certain that the synthetic data you are using is similar to what you have in production.

Continue reading “Perils of synthetic test data”

Reflections on PASS Summit 2017

PASSSummit2017

PASS Summit 2017 is now well behind us and there has been good time to reflect on this years conference. First of all, I have to say that Seattle as a venue is a good choice, even though for some of us that is a long way to travel. To me, it is about 20 hours from SEA-TAC to my home with the flights and driving, doable but not necessarily pleasant. As for the conference itself, I feel that it keeps getting better every year. It has definitely changed a quite a bit from my first Summit back in 2013. But so is the world where data platform professionals live, and the products we work with.

Here are some highlights from the PASS Summit 2017!

Continue reading “Reflections on PASS Summit 2017”

Undeprecated SQL Server features

The Feature Reaper!
The Feature Reaper!

As we all know there are many features in SQL Server that have been deprecated over the time by Microsoft for one reason or another. In fact, there is a long list of features that are deprecated in the latest SQL Server 2017 release.

It is far less often that any of these features make a comeback, however that can apparently happen, as I just witnessed last week.

Continue reading “Undeprecated SQL Server features”