-->
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.

RDS encryption vs TDE

If you’re starting to use cloud services, the number of options available can be confusing. Particularly when they may seem to do the same thing.

If you’re on AWS, and you want to create a SQL Server instance on RDS (Relational Database Service), then you potentially have a couple of… Read more

0 comments, 116 reads

Posted in Matthew McGiffen DBA on 17 April 2018

T-SQL Tuesday #101 – Some Great SQL Server Tools

This month for T-SQL Tuesday #101 Jens Vestergaard asks us to blog about the essential tools in our SQL Toolbelt.

http://t-sql.dk/?p=1947

I’d just completed by post on CMS when I realised I’ve blogged about a few of my favourite tools in the past and that this would be a good… Read more

0 comments, 753 reads

Posted in Matthew McGiffen DBA on 10 April 2018

T-SQL Tuesday #101. CMS – Effortlessly run queries against multiple SQL Servers at once

This month for T-SQL Tuesday #101 Jens Vestergaard asks us to blog about the essential tools in our SQL Toolbelt.

http://t-sql.dk/?p=1947

The concept of a Central Management Server (CMS) is one I’ve been meaning to blog about for a while – just because I get the impression not a lot… Read more

4 comments, 121 reads

Posted in Matthew McGiffen DBA on 10 April 2018

TDE – Regenerating the Database Encryption Key

As discussed in the last post, with TDE (Transparent Data Encryption), you generally do not need to change the Database Encryption Key (DEK) as it is separately protected by a certificate in the master database and you can rotate the certificate periodically.

Rotating TDE Certificates without re-encrypting data

As also… Read more

0 comments, 156 reads

Posted in Matthew McGiffen DBA on 3 April 2018

Rotating TDE Certificates without re-encrypting data

I talked previously about why we have each of the layers in the encryption hierarchy used to support TDE (Transparent Data Encryption).

Understanding Keys and Certificates with Transparent Data Encryption (TDE)

At the time I mused about why we need the encryption key in the database itself, rather than just… Read more

3 comments, 1,686 reads

Posted in Matthew McGiffen DBA on 28 March 2018

Crystal Balls

In the centenary edition of the monthly blogging party that is T-SQL Tuesday, Adam Machanic asks us to look forward 100 months and speculate what the world of SQL Server will look like:

http://dataeducation.com/invitation-t-sql-tuesday-100-looking-forward-100-months/

For Adam’s challenge, we’re looking forward just over 8 years. If we look back that amount… Read more

1 comments, 149 reads

Posted in Matthew McGiffen DBA on 13 March 2018

Recovering a TDE protected database without the Certificate

If you’ve been careful and done everything right when you’re setting up TDE then you shouldn’t run into this problem.

We all make mistakes though, and we’ve all been asked to deal with environments that haven’t been so carefully managed.

But what if you do? You have access to the… Read more

1 comments, 750 reads

Posted in Matthew McGiffen DBA on 21 February 2018

TDE and backup compression – still not working?

Until SQL 2016 if you used TDE (Transparent Data Encryption) you couldn’t use backup compression.

In 2016 Microsoft changed this, but it has been a rocky road. Backups work okay but in some circumstances people have found that they are corrupt when they come to restore them.

We thought that… Read more

5 comments, 1,996 reads

Posted in Matthew McGiffen DBA on 16 February 2018

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, 249 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

4 comments, 585 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, 362 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,434 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

7 comments, 2,118 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,209 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, 1,845 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,515 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, 433 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,168 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

2 comments, 2,735 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, 311 reads

Posted in Matthew McGiffen DBA on 1 November 2017

Older posts