Data Encryption and Protection - Part 1

A client has recently revisited their need to protect sensitive data in their application and the database that it connects to. As a DBA it is easy to focus solely on the database but as I was considering their infrastructure and all the parts of their system it became clear that that was a small piece of the puzzle. In terms of protecting data for an entire IT solution there are quite a few other areas to consider. In this and a few subsequent posts I will discuss what options there are in securing data. I will attempt to touch on the major advantages and disadvantages of those options. Obviously, as I'm not a network or systems admin I will only touch on the concepts to consider that are beyond the scope of securing the database.

First I will cover what I know best: SQL Server.  There are 2 main encryption methods serving different purposes:

1.  Transparent Database Encryption (TDE) - available for SQL Server 2005+
2. Cell-level encryption

Implementing Change Data Capture Part 3 – Creating an SSIS Package

In the last post I covered creating a wrapper function for use within an SSIS package and staging tables in the data warehouse. This post will deal with creating the SSIS package(s) itself.

As I mentioned in my first post about Change Data Capture (CDC) , SQL Server Books Online has a very good step-by-step how-to on setting up CDC and the related SSIS package to perform incremental loads to a data warehouse. My series of posts is meant to make that set up and implementation a little easier by somewhat automating the script and object generation process.

Implementing Change Data Capture Part 2 – Preparing for SSIS

In the last post I discussed setting up Change Data Capture (CDC) on a database. This post deals with the first steps in setting up an incremental extract process. This would be, loosely speaking, the ‘E’ of a larger ETL (Extract, Transform, Load) process.

I feel that this is where CDC really shines. With CDC itself a transaction log based process like log shipping and mirroring and, set up as in the last post, with the CDC tables on a separate filegroup (even a separate disk array) it is a very efficient method of extracting data to a data warehouse.

Microsoft BOL’s step-by-step guide calls this an ‘incremental load’. But I see this really as the extract part of the process. This and the next post deal with getting the data out of an OLTP database and preparing it for the transform and load parts of an ETL. As you will see there is no transform occurring and it is just incidental that the data is being loaded into the data warehouse.