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

T-SQL Tuesday 104 – Capture expensive queries with query store

For this month’s T-SQL Tuesday Bert Wagner invites us to share SQL scripts we’ve written that we’d hate to live without.

Code You Would Hate To Live Without (T-SQL Tuesday #104 Invitation)

I don’t know that I’d go quite as far as “hate”, but I’ve been getting a lot of… Read more

0 comments, 126 reads

Posted in Matthew McGiffen DBA on 10 July 2018

Alter Multiple Databases at Once

This is a quick and dirty method I often use when I want to make a change to multiple databases on a SQL Server instance, usually based on a criteria.

It’s a fairly basic level thing to do, and while it is probably trivial to a SQL expert, I find… Read more

2 comments, 1,904 reads

Posted in Matthew McGiffen DBA on 26 June 2018

Cycle Through Your Clipboard History In SSMS

This is my favourite SSMS trick I’ve discovered recently, probably some time towards the end of last year.

Basically, when you paste in Management Studio, you have not just the option to paste the last thing you selected and copied, but can cycle back through previous things that were in… Read more

1 comments, 1,064 reads

Posted in Matthew McGiffen DBA on 6 June 2018

The Importance of ORDER BY

Everyone, at the beginning of their SQL career, get’s told that it is important to include an ORDER BY if they want the results ordered. Otherwise the order in which they are returned is not guaranteed.

But then you run queries a lot of times that don’t need a specific… Read more

1 comments, 193 reads

Posted in Matthew McGiffen DBA on 30 May 2018

Thoughts on Query Performance with TDE enabled

Microsoft state that enabling TDE (Transparent Data Encryption) usually has a performance  overhead of 2-4%. That doesn’t sound like very much, and personally I wouldn’t let it bother me if I want to make sure my data is encrypted at rest.

However, you may have heard other sources saying that… Read more

1 comments, 700 reads

Posted in Matthew McGiffen DBA on 23 May 2018

Hitting my first big blogging milestone – 100k Reads

After blogging about SQL Server for just over a year, at some in the last month I went over 100,000 reads in total for my blog posts.

That breaks down today as about 17,500 reads on my WordPress site, and 89,000 on SQL Server Central where my blog is syndicated,… Read more

7 comments, 340 reads

Posted in Matthew McGiffen DBA on 16 May 2018

Database Files Down The Wrong Path

I manage a few servers used to host SQL Instances for development and test purposes. Each of those instances hosts databases covering multiple environments. So I’ve got multiple servers, with multiple instances, with multiple environments.

It’s important that issues in those environments don’t block development tasks, or invalidate or block… Read more

1 comments, 2,164 reads

Posted in Matthew McGiffen DBA on 24 April 2018

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, 1,545 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

1 comments, 960 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

5 comments, 192 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, 355 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,901 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, 202 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, 858 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

6 comments, 2,144 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, 295 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, 757 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, 469 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,639 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,403 reads

Posted in Matthew McGiffen DBA on 3 January 2018

Older posts