SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Matthew McGiffen DBA

I started my career in IT as a developer with Visual Basic, C# and ASP working with SQL Server backends. For the last ten years though I’ve focused on the SQL Server side of things. Currently I’m working for a leading software vendor for the financial industry as a DBA, doing a mixture of development and data architecture. If I have a speciality it is in making things run faster.

Migrating or Recovering a TDE protected Database

When encrypting a database with Transparent Data Encryption(TDE), a vital consideration is to make sure we are prepared for the scenario when something goes wrong. For instance, if the server hosting our SQL instance goes belly-up, can we recover the data that we have encrypted with TDE?

In the ordinary… Read more

2 comments, 365 reads

Posted in Matthew McGiffen DBA on 14 February 2018

Encrypting an existing database with TDE

As mentioned previously, the process of setting up TDE (Setting up Transparent Data Encryption (TDE)) is the same whether you’ve just set up a new database, or whether you’re working with a live database. Once you turn encryption on SQL Server will begin the process of encrypting any… Read more

5 comments, 832 reads

Posted in Matthew McGiffen DBA on 26 January 2018

Setting up Transparent Data Encryption (TDE)

You can set up Transparent Data Encryption (TDE) when you first create a database, or you can apply it to an existing database. In the latter case, once TDE has been enabled it will set to work encrypting your existing data in the background.

In either case the steps are… Read more

6 comments, 654 reads

Posted in Matthew McGiffen DBA on 26 January 2018

What is Transparent Data Encryption?

Transparent Data Encryption (TDE) was introduced in SQL 2008 as a way of protecting “at rest” data. It continues to be available in all versions of SQL right up until the present, though only in the Enterprise editions of SQL Server (though as with all other Enterprise only features, you… Read more

4 comments, 1,776 reads

Posted in Matthew McGiffen DBA on 26 January 2018

How Secure is Transparent Data Encryption (TDE) – and How to Prevent Hacking

TDE is commonly described as “at-rest” encryption, i.e. it protects your data wherever it is stored on disk. This includes the database files, any backups taken (including Log and Differential), and any data that may get temporarily persisted to TempDB (when you use TDE to encrypt any database on an… Read more

8 comments, 2,622 reads

Posted in Matthew McGiffen DBA on 3 January 2018

SQL Puzzle 5: Prime Magic

Quite a few of you have read or attempted the previous puzzle SQL Puzzle 1: Magic Squares

As a quick reminder, when you have a 3 x 3 grid, a magic square is one where each row, column or diagonal add up to the same value. e.g.

All the rows,… Read more

3 comments, 1,352 reads

Posted in Matthew McGiffen DBA on 19 December 2017

Understanding Keys and Certificates with Transparent Data Encryption (TDE)

I’ve been taking a bit of a deep dive into understanding Transparent Data Encryption (TDE). As part of that I’ve been reading a lot of blog posts, stack overflow answers and technical documentation to try and deepen my knowledge.

Within that I’ve found a lot of contradiction I’ve needed to… Read more

10 comments, 2,066 reads

Posted in Matthew McGiffen DBA on 12 December 2017

Overview of Encryption Tools in SQL Server

More and more people are considering some level of encryption against their data stored in SQL Server. In many cases it might be considered that other measures such as firewalls, well defined access permissions and application code free of security flaws, already offer sufficient protection.

Anyone who follows the tech… Read more

7 comments, 2,958 reads

Posted in Matthew McGiffen DBA on 5 December 2017

How does Query Store capture cross database queries?

When I was writing my post  Capture the most expensive queries across your SQL Server using Query Store a question crossed my mind:

Query Store is a configuration that is enabled per database, and the plans and stats for queries executed in that database are stored in the database itself.… Read more

0 comments, 493 reads

Posted in Matthew McGiffen DBA on 16 November 2017

SQL Puzzle 4: The Beale Papers

I’ve not done a SQL puzzle for a while so thought it was getting overdue…

I set this one for my workmates a while ago and people found it quite fun.

The Beale Papers are a set of three ciphertexts which allegedly reveal the details of a buried treasure. The… Read more

1 comments, 1,299 reads

Posted in Matthew McGiffen DBA on 8 November 2017

Capture the most expensive queries across your SQL Server using Query Store

I’m a big fan of using queries based on the dynamic management view sys.dm_exec_query_stats to capture the most resource hungry queries across a SQL instance.

That approach has some drawbacks though. First, it is cleared out every time an instance restarts, and second it only keeps figures for currently cached… Read more

3 comments, 3,061 reads

Posted in Matthew McGiffen DBA on 1 November 2017

Avoiding confusion with SQLCMD variables

SQLCMD variables can be a useful way of having changeable parameters for your SQL scripts, allowing you to specify the value from a command line, but also to control things you couldn’t manage through a SQL variable.

They can also be a little confusing the first time you see them. Read more

0 comments, 848 reads

Posted in Matthew McGiffen DBA on 1 November 2017

Implementing Temporal Tables Where You Have Existing Data

In my post about auditing tools in SQL Server I mentioned a few tools we have at our disposal for capturing the change history of data.

One was Change Data Capture which I want into a bit more detail about in Setting up Change Data Capture (CDC)

I also referred… Read more

3 comments, 3,088 reads

Posted in Matthew McGiffen DBA on 24 October 2017


I love the STATISTICS IO and STATISTICS TIME commands. They are such a powerful and easy way to be able to measure improvements when performance tuning (Measuring SQL Query Performance).

You have to be a little bit wary though, there are a few places where they don’t report… Read more

0 comments, 424 reads

Posted in Matthew McGiffen DBA on 17 October 2017

Avoid “Constants” in T-SQL

Unlike some other languages, T-SQL doesn’t have the concept of a constant.

As good coders, we’ve all at some point tried to use a SQL variable to hold a constant value within a stored procedure, in order to make our code both more readable and maintainable.

I’ll give you an… Read more

10 comments, 3,519 reads

Posted in Matthew McGiffen DBA on 10 October 2017

Setting up Change Data Capture (CDC)

As mentioned in my post Auditing Data Access in SQL Server for GDPR Compliance CDC can be a useful tool for capturing a change history for specific data – which is something we might be thinking about a bit more now the GDPR is heading our way.

It’s pretty straightforward… Read more

1 comments, 657 reads

Posted in Matthew McGiffen DBA on 3 October 2017

Auditing Data Access in SQL Server for GDPR Compliance

In relation to the GDPR, I’ve recently been looking at the tools available within SQL Server to support auditing our databases. Many of you out there will already has full-scale auditing solutions in place. For others though, the prompting of GDPR could be the first time you’re really thinking about… Read more

6 comments, 4,424 reads

Posted in Matthew McGiffen DBA on 26 September 2017

When Deadlocks Become Art

We’ve all had to solve deadlock issues. Identify the two conflicting pieces of code, work out an appropriate change to one of them to avoid the issue. Job done.

Occasionally you see a deadlock that has three nodes. Okay, a little bit more analysis and head scratching before the solution… Read more

2 comments, 626 reads

Posted in Matthew McGiffen DBA on 19 September 2017

Setting the Sample Rate for Auto Stats updates

SQL Server functionality can move on pretty quickly sometimes, and it’s not always all about the big features but the many little enhancements the SQL team implements to make the product better and easier to use.

I’d barely published my post about Automatic Sample Sizes for Statistics Updates when I… Read more

0 comments, 504 reads

Posted in Matthew McGiffen DBA on 12 September 2017

Viewing the Statistics Objects Used to Create an Execution Plan

A while ago now I was running some Performance Tuning workshops and was asked how you can find out which Statistics objects SQL Server has used in the generation of an execution plan (for cardinality estimation). My answer was: “As far as I know – you can’t.”

Some time later… Read more

0 comments, 312 reads

Posted in Matthew McGiffen DBA on 1 September 2017

Newer posts

Older posts