Search results matching tags 'SQL', 'SQL Server 2008', and 'SQL Server'

High Performance Dimensional Data Loads With SSIS Presentation

Mike C — Sat, 20 Nov 2010 18:25:00 GMT

Just finished giving the SSIS High-Performance Dimensional Data Load presentation at SQLSaturday #59 NYC. Here are the slides in PDF format. I'll upload the Try-N-Save code and sample data later for attendees to play with.

Thanks to everyone who attended my session and thanks to Melissa D. and NJSQL for putting this together. For those who are interested in Alejandro Mesa's composable DML solution to the problem of Type 2 dimension updates, here's the complete statement from the demo:

INSERT INTO Dim.Geography_Hash
(
CityName, CountyFIPS, CountyName, StateFIPS,
StateUSPS, StateName, ZIP, TimeOffset,
DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
LineageID, CubeInd, SortOrder, Hash
)
SELECT CityName, CountyFIPS, CountyName, StateFIPS,
StateUSPS, StateName, ZIP, TimeOffset,
DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
LineageID, CubeInd, SortOrder, Hash
FROM
(
MERGE INTO Dim.Geography_Hash AS Target
USING Staging.Geography_Hash AS Source
ON Target.ZIP = Source.ZIP
  AND Target.CurrentFlag = Source.CurrentFlag
WHEN MATCHED AND Target.Hash <> Source.Hash
  THEN UPDATE SET CurrentFlag = 'N'
WHEN NOT MATCHED
  THEN INSERT
  (
   CityName, CountyFIPS, CountyName, StateFIPS,
   StateUSPS, StateName, ZIP, TimeOffset,
   DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
   LineageID, CubeInd, SortOrder, Hash
  )
  VALUES
  (
   Source.CityName, Source.CountyFIPS, Source.CountyName, Source.StateFIPS,
   Source.StateUSPS, Source.StateName, Source.ZIP, Source.TimeOffset,
   Source.DaylightSavingTime, Source.StartDateID, Source.CurrentFlag, Source.BatchID,
   Source.LineageID, Source.CubeInd, Source.SortOrder, Source.Hash
  )
OUTPUT $action, inserted.CityName, inserted.CountyFIPS, inserted.CountyName, inserted.StateFIPS,
   inserted.StateUSPS, inserted.StateName, inserted.ZIP, inserted.TimeOffset,
   inserted.DaylightSavingTime, inserted.StartDateID, inserted.CurrentFlag, inserted.BatchID,
   inserted.LineageID, inserted.CubeInd, inserted.SortOrder, inserted.Hash
)
AS T
(
action, CityName, CountyFIPS, CountyName, StateFIPS,
StateUSPS, StateName, ZIP, TimeOffset,
DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
LineageID, CubeInd, SortOrder, Hash
)
WHERE action = 'UPDATE';

As mentioned, the OUTPUT clause on the inner MERGE statement feeds the outer INSERT clause. Next stop is SQLSaturday #61 in DC at the beginning of December.

Find a Hash Collision, Win $100

Mike C — Sat, 17 Apr 2010 21:38:00 GMT

Margarity Kerns recently published a very nice article at SQL Server Central on using hash functions to detect changes in rows during the data warehouse load ETL process. On the discussion page for the article I noticed a lot of the same old arguments against using hash functions to detect change. After having this same discussion several times over the past several months in public and private forums, I've decided to see if we can't put this argument to rest for a while. To that end I'm going to hold a little contest: Generate an SHA-1 hash collision and win $100 and a book (see bottom section for details). Before I get into the details of the contest I'm going to give a little background of how this came about.

Background Info

NOTE: If you aren't familiar with hash functions I highly recommend first reading the Wikipedia article at http://en.wikipedia.org/wiki/Cryptographic_hash_function.

The idea of using a hash function for change detection is not new. Essentially a hash function generates a "fingerprint" of your data that you can use to compare an inbound row and an existing row.

Some people are wary of hash functions because they map a theoretically infinite number of large inputs to a much smaller finite set of hash values. Most of the arguments people make against using hash functions for change detection boil down to variations of Murphy's Law:

"There's a chance of a hash collision [generating the same hash value for two different inputs], so a collision will happen!"

People have different ways of dealing with this issue, including taking one of the following positions:

The chance of collision is negligible so no additional precautions are required.
A collision will absolutely happen so I won't use hash functions for change detection at all!
A collision may happen so I want to use hash values only to initially narrow down the number of rows I need to compare fully.

Positions #1 and #2 above are at different ends of the spectrum. Position #3 sits in the middle as a compromise solution. While compromises may make for good politics, they often make for terrible technical solutions, as I'll discuss below.

Position #1: Odds of Collision are Low Enough to be Ignored

As far as position #1 is concerned, it depends on which hash function you're using. You need to choose a true one-way collision-free* cryptographic hash function with a wide bit length. I normally recommend an SHA-2 hash function (256, 384 or 512 bit hash value), or when that's not available the SHA-1 160 bit hash function. The odds of generating a collision with a 160 bit hash function are 2^80. That is to say you can expect a collision after you generate hashes for 1,208,925,819,614,629,174,706,176 rows of data.

Of course if you're identifying rows by their natural or business keys this alternatively means you need to generate 1,208,925,819,614,629,174,706,176 variations of that single row before you'll hit a collision with SHA-1.

To put that number in perspective, consider that Google processes 20,000,000,000,000,000 bytes (20 petabytes) of data per day. If you were to store a single row in a database table for every single byte Google processes each day, it would take you 60,446,290 days (approximately 156,600 years) to store 1,208,925,819,614,629,174,706,176 rows in that table.

I personally assume position #1 on this subject, with the assumption that you have chosen a good solid hash function for the job. More on this later.

*A collision-free cryptographic hash function is a one-way hash function with negligible probability of generating the same hash value for two different inputs. SHA-1 and SHA-256 are examples of collision-free cryptographic hash functions.

Position #2: I Don't Trust Hash Functions

This position can't really be argued with. As shown above the odds of a collision with SHA-1 or another collision-free hash function are extremely low. But if you don't trust it, you just don't trust it. So the alternative is to compare every inbound column with every existing column. It will cost you in efficiency on wide tables, but if you're not concerned about processing power, server resources and execution time this classic method of change detection is well-proven to be 100% effective.

Position #3: The Compromise - Use Hash Values to Initially Narrow Down Results

This position is the compromise position that combines the implementation of #1 and #2 above. It sounds wonderful in theory - use a hash function to narrow down your results, eliminating rows that don't need to be compared column by column; then compare all of the columns in the remaining rows that haven't been eliminated. So let's look at a scenario:

You are processing Row A through your ETL process into a target table. Row B is the equivalent row in the target table (it has the same natural key/business key as Row A). This assumes we are first locating the equivalent row in the target table by natural key/business key of the incoming row.

There are three possible scenarios:

Row B exists in the target table, and is equal to Row A (no change).
Row B exists in the target table, but it is not equal to Row A (update).
Row B does not exist in the target table (insert Row A).

Let's say you've generated two hash values, h(A) is the hash for Row A and h(B) is the hash for Row B. Now we need to use h(A) and h(B) to eliminate rows to get rid of the extra column by column comparisons. Here are the rules you need to implement to use h(A) and h(B) to eliminate extra comparisons in this compromise solution:

A. h(A) is equal to h(B): according to the compromise, if h(A) = h(B) we need to compare all columns of the inbound row against the existing row since the belief is that the hash function can/will generate collisions. The idea is that h(A) may have generated the same value as h(B) even if A <> B. So we need to:

(1) Compare all columns in A and B. If A = B then perform no action.

(2) Compare all columns in A and B. If A <> B then update.

B. h(A) is not equal to h(B): cryptographic hash functions guarantee that they will generate the same hash value for the exact same inputs. So we can eliminate full row comparisons if h(A) <> h(B). We know automatically that if h(A) <> h(B) then A <> B. Just perform the update.

C. h(B) is NULL: that is, if Row B does not exist in the target table than h(B) is NULL. This is a case where no further full-row comparisons are necessary. Just insert the row.

Now consider a slowly changing dimension (SCD) in a datamart application. Many SCDs change slowly over time (hence the name slowly changing dimension). This means that new rows (updates and inserts) are far less common than receiving duplicate rows during ETL. So the vast majority of your inbound data will fall under rule A(1) above. So you're still performing comparisons of all columns for the vast majority of rows in a given table just to figure out that you don't need to update them after all!

If you eliminate even 90% of the inbound rows under rule A(1) above you haven't saved much processing (you're still comparing all columns for changes for 90% of your inbound rows). You probably actually cost yourself a lot of time and efficiency since you haven't accounted for the overhead of generating hash values for 100% of the inbound rows.

The only way this compromise is more efficient is if a very large percentage of your inbound rows (much greater than 50+%) are inserts under Rule C or updates under Rule B above. If the majority of your inbound rows are duplicates of existing rows under Rule A, you gain nothing.

The Contest

One-way collision-free cryptographic hash functions are supposed to have negligible probability of a hash collision, or two different inputs generating the same output. Hash collisions are what cause change detection with hashes to fail.

For instance, consider the following example of an MD5 hash collision:

DECLARE @A varbinary(8000),
      @B varbinary(8000),
      @hA binary(16),
      @hB binary(16);

SELECT @A = 0xd131dd02c5e6eec4693d9a0698aff95c2fcab58712467eab4004583eb8fb7f8955ad340609f4b30283e488832571415a085125e8f7cdc99fd91dbdf280373c5bd8823e3156348f5bae6dacd436c919c6dd53e2b487da03fd02396306d248cda0e99f33420f577ee8ce54b67080a80d1ec69821bcb6a8839396f9652b6ff72a70,
      @B = 0xd131dd02c5e6eec4693d9a0698aff95c2fcab50712467eab4004583eb8fb7f8955ad340609f4b30283e4888325f1415a085125e8f7cdc99fd91dbd7280373c5bd8823e3156348f5bae6dacd436c919c6dd53e23487da03fd02396306d248cda0e99f33420f577ee8ce54b67080280d1ec69821bcb6a8839396f965ab6ff72a70;

SELECT @hA = HASHBYTES('MD5', @A),
      @hB = HASHBYTES('MD5', @B);

SELECT CASE WHEN @A = @B
                  THEN '@A Equals @B'
                  ELSE '@A Is Not Equal To @B'
                  END AS AB_Equal,
            CASE WHEN @hA = @hB
                  THEN '@hA Equals @hB'
                  ELSE '@hA Is Not Equal To @hB'
                  END AS Hash_Equal;

The results are shown below:

When you run this you'll notice that the query reports the two source varbinary strings @A and @B are not equal, yet the two MD5 hashes they generate are equal. This is an example of a simple hash collision with MD5.

Now the challenge is to populate the following script with two different binary values that generate the same hash value. The output should be the same as shown above in the MD5 example.

-- Begin script
DECLARE @A varbinary(8000),
      @B varbinary(8000),
      @hA binary(20),
      @hB binary(20);

-- Replace the ? below with binary strings

SELECT @A = ?,
      @B = ?;

SELECT @hA = HASHBYTES('SHA1', @A),
      @hB = HASHBYTES('SHA1', @B);

SELECT CASE WHEN @A = @B
                  THEN '@A Equals @B'
                  ELSE '@A Is Not Equal To @B'
                  END AS AB_Equal,
            CASE WHEN @hA = @hB
                  THEN '@hA Equals @hB'
                  ELSE '@hA Is Not Equal To @hB'
                  END AS Hash_Equal;
-- End script

The first person who sends me an example of two varbinary strings that generate the same SHA1 hash value will win $100 (US$) and a copy of my book Pro T-SQL 2008 Programmer's Guide.

And here are the inevitable conditions:

No NULLs. @A and @B in the script above cannot be set to NULL for purposes of this contest.
8,000 bytes or less. The T-SQL HASHBYTES function accepts varbinary(8000) values, so the values passed into it in this contest must be 8,000 bytes in length or less. The values assigned to @A and @B above must be 8,000 bytes or less in length.
No unnecessary changes to the script. The only change allowed to the script above are the replacement of the question marks (?) with binary strings. No other changes to the script are authorized.
Only one person will win. The first person who sends me a copy of the above script with two different binary values that generate an SHA-1 hash collision will win.
Void where prohibited. Obviously if contests like this aren't legal in your country, state, county, city, etc. then you can't take part. Petition your government to make it legal :)
Time limits. Entries must be received prior to midnight U.S. Eastern Standard Time on October 31, 2010.
Decisions of the judge are final. For purposes of this contest that would be me.
SQL Server 2005 or 2008. Entries must be runnable on SQL Server 2005 and SQL Server 2008 Developer Edition, and the results must be reproducible.

If a winning entry is received prior to the deadline, I'll post an update entry to the blog with the winning script and the name of the winner.

Calculating Holidays in SQL

Mike C — Thu, 25 Feb 2010 21:51:00 GMT

Ask about nearly any kind of SQL-based date calculation in the newsgroups, and you'll likely get responses that include use of an auxiliary calendar table. It's a really good idea, and something I highly recommend to anyone who has to do date-based calculations on the server. For those who do dimensional modeling, you'll probably notice the auxiliary calendar table closely resembles a Time dimension with one-day intervals. I won't get into too much detail on it, since ASPFAQ and the newsgroups cover it well.

What I do want to talk about is calculating holidays, which can be important when populating your auxiliary calendar table. When you create an auxiliary calendar table you may need to perform calculations based on business days, for instance; or you may need to schedule activities before, after, or even during certain holidays. There are two types of holidays that I'll address:

Fixed holidays that fall on the same day every year
Floating holidays for which the date can change from year to year

Fixed holidays include holidays that always fall on the same day each year. Some examples of fixed holidays include Christmas (always December 25 in the West), Canada Day (always July 1), U.S. Independence Day (always July 4). These are fairly easy to detect and set in your auxiliary calendar table. Just look for the static month + day combinations in your table and update it accordingly.

Floating holidays are far more interesting and difficult to calculate. Take Easter. Easter actually has two definitions. The popular definition is:

"Easter Day is the first Sunday after the full moon that occurs next after the vernal equinox"

This is not the exact ecclesiastical definition though. The full moon here is not the astronomical full moon but an "ecclesiastical moon" that's determined through precalculated lookup tables. The ecclesiastical rules are:

Easter falls on the first Sunday following the first ecclesiastical full moon that occurs on or after the day of the vernal equinox
This particular ecclesiastical full moon is the 14th day of a tabular lunation (new moon)
The vernal equinox is fixed as March 21

I'm not going to recount the long and painful history leading to the modern definition of Easter--but the details can be found on Wikipedia if you're interested. The main point is that Easter calculation is fairly complex. The udf_CalculateEaster function below accepts the year (integer) as a parameter and returns the date of Easter for that year (datetime). Notice the large number of calculations required to make this function work:

CREATE FUNCTION dbo.udf_CalculateEaster
(
@Year INT
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @Date DATETIME,
    @c INT,
    @n INT,
    @i INT,
    @k INT,
    @j INT,
    @l INT,
    @m INT,
    @d INT;

    SELECT @n = @Year - 19 * (@Year / 19),
    @c = @Year / 100,
    @k = (@c - 17) / 25,
    @i = @c - @c / 4 - (@c - @k) / 3 + 19 * @n + 15,
    @i = @i - 30 * (@i / 30),
    @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11)),
    @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4,
    @j = @j - 7 * (@j / 7),
    @l = @i - @j,
    @m = 3 + (@l + 40) / 44,
    @d = @l + 28 - 31 * (@m / 4),
    @Date = CAST
    (
      CAST(@Year AS CHAR(4)) +
      RIGHT
      (
        '0' + CAST
        (
          @m AS VARCHAR(2)
        ), 2
      ) +
      RIGHT
      (
        '0' +CAST
        (
          @d AS VARCHAR(2)
        ), 2
      ) AS DATETIME
    );
    RETURN @Date;
END;
GO

If you want the date for Easter 2010, just run a query like the following:

SELECT dbo.udf_CalculateEaster (2010);
GO

The result is 2010-04-04.

Another somewhat daunting type of floating holiday is the type that is based on the Nth weekday of the month. These holidays are defined using terms like "the 4th Thursday of November" (U.S. Thanksgiving), "the 3rd Monday in January" (U.S. Martin Luther King Day), or even "the *last* Monday in May" (U.S. Memorial Day). These floating holidays are not as hard to calculate as Easter, but still a little tricky (particularly Memorial Day).

To calculate these dates we need a function like udf_nthWeekDay below. This function accepts 4 parameters:

@n = integer that represents which instance of the weekday you're looking for. This would be the 4 in "4th Thursday in November".
@weekDay = 3-character day of week ("SUN" = Sunday, "MON" = Monday, etc.) I used character abbreviations instead of numbers just to make it a little easier.
@year = integer year for which to calculate
@month = integer month for which to calculate (1 = January, 2 = February, etc.)

As you can see below, the Nth weekday calculation is pretty simple.

CREATE FUNCTION dbo.udf_nthWeekDay
(
@n       INT,
@weekDay CHAR(3),
@year    INT,
@month   INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @date    DATETIME,
    @dow         INT,
    @offset      INT,
    @wd          INT;

SELECT @wd = CASE @weekDay
      WHEN 'SUN' THEN 1
      WHEN 'MON' THEN 2
      WHEN 'TUE' THEN 3
      WHEN 'WED' THEN 4
      WHEN 'THU' THEN 5
      WHEN 'FRI' THEN 6
      WHEN 'SAT' THEN 7
    END,
    @date = CAST
    (
      CAST(@year AS VARCHAR(4)) +
      RIGHT
      (
        '0' + CAST
        (
          @month AS VARCHAR(2)
        ), 2
      ) +
      '01' AS DATETIME
    ),
    @dow = DATEPART(dw, @date),
    @offset = @wd - @dow,
    @date = DATEADD(day, @offset + (@n - CASE WHEN @offset >= 0 THEN 1 ELSE 0 END) * 7, @date);
RETURN @date;
END;
GO

To get the date for Thanksgiving 2024 you could call the function like this:

SELECT dbo.udf_nthWeekDay
(
    4,
    'THU',
    2024,
    11
);

The result is 2024-11-28. Now for a holiday like U.S. Memorial Day ("the last Monday in May") the calculation is a little trickier. You have no idea up front how many Mondays there are in any given May. As an example, in 2010 and 2011 there are 5 Mondays in May; in 2012 and 2013 there are only 4. You could try to figure out the number of Mondays in May of the given year, but that's way too much work.

There's one thing we know about the last Monday in May with absolute certainty: after the last Monday in May, the next Monday we encounter will *always* be the first Monday in June. So the easier way to figure out the last Monday in May is to calculate the first Monday in June and then back into it (subtract 7 days), like this:

SELECT DATEADD
(
day,
-7,
dbo.udf_nthWeekDay
(
    1,
    'MON',
    2012,
    6
)
);

By calculating the date of the first Monday in June 2012, we can then subtract 7 days to get the date of the last Monday of May: 2012-05-28.

You can use the functions to calculate holidays for your auxiliary calendar table. I covered U.S. federal (and some other national) holidays in this one, but it can be extended to cover other national, state, and local holidays.

Service Packs... Coming to a Download Near You!

Mike C — Sun, 14 Feb 2010 23:07:00 GMT

Based on feedback from the community (https://connect.microsoft.com/SQLServer/feedback/details/522123/sql-server-2008-service-pack-2 and https://connect.microsoft.com/SQLServer/feedback/details/522122/service-pack-4-for-sql-server-2005), Microsoft has announced plans to release Service Pack 2 for SQL Server 2008 and Service Pack 4 for SQL Server 2005. The official announcement from the SQL Server Group Project Manager Matthias Berndt is here.

Here's the abbreviated version:

Service Pack 2 for SQL Server 2008 is targeted for a Q3 release.
Service Pack 4 for SQL Server 2005 is targeted for a Q4 release.

SP 4 is currently scheduled to be the final SP for 2005. The announcement also contains some info. about SQL 2005 going into extended support from April 2011 to April 2016.

NJ .NET User Group Presentation: Building a SQL Server Search Engine in .NET

Mike C — Sun, 07 Feb 2010 18:11:00 GMT

I'll be presenting at the Northern NJ .NET User Group meeting on Tuesday February 9. Topics we'll cover include:

Intro to SQL Server 2008 Integrated Full-Text Search (iFTS) features and functionality, including:

Full-text indexes
Thesaurus
Word breakers, filters and stemming
Multilanguage support

Intro to SQL Server 2008 FILESTREAM storage
Troubleshooting with iFTS dynamic management views and functions
Building a simple and powerful .NET-based search engine-style interface

Click here for more information: http://www.setfocus.com/n3ug/welcome.aspx

See you there!

It's Official - SQLSaturday is Coming to NYC!

Mike C — Sat, 06 Feb 2010 19:59:00 GMT

New Jersey SQL Server User Group (NJSQL) is bringing SQLSaturday #39 to NYC on April 24, 2010! The free all-day training event will be hosted by Microsoft at their Midtown Manhattan offices. The speaker line-up is growing fast—if you'd like to present, visit the event's open call for speakers. This is a free full-day training event, but registration is required to attend. Seating is limited.

Registration, speaker, and sponsorship details are posted at http://www.sqlsaturday.com/39/eventhome.aspx.

Parent-Child Build Scripts with SQLCMD

Mike C — Sun, 10 Jan 2010 18:33:00 GMT

On the SQL Server public programming newsgroup someone recently posted a question about an SSMS error ("Cannot parse script. 'System.OutOfMemoryException' thrown.") I hadn’t encountered this error myself, but the workaround is to break up very large scripts (50+ MB) into smaller scripts. Adam Machanic posted a T-SQL Tuesday challenge to post a solution to a puzzling situation, so this actually gives me a good opportunity to share how I structure my own build scripts -- which avoids this issue entirely.

When I create database build scripts, I use the SQLCMD utility to run them from the command line instead of using SSMS or another tool. SQLCMD has its own commands, which it parses separately from SQL/T-SQL statements. These commands are not understood by SQL Server or other scripting tools like SSMS (exception: you can run SSMS in SQLCMD mode, but that’s another story). These special SQLCMD commands all start with a ":" at the front of the line.

The SQLCMD command that makes parent-child structured build-scripts possible is the ":r" or "run" command, which tells SQLCMD to run another script file from within the current script file. In the figure below I’ve set up a local directory structure with database object creation scripts in subdirectories:

The \Scripts directory contains a Create.All.Sql script. This script uses the SQLCMD run command to execute the Database\Create.Database.Sql script, the Create.All.Schemas.Sql script, and so on. The Create.All.Schemas.Sql script calls the Person.Schema.Sql and Sales.Schema.Sql scripts in turn. The other Create.All.* scripts each call the object creation scripts in their subdirectories as well. Here’s what my Create.All.Sql script looks like:

/*
    Create All Items
*/


:r Database\Create.Database.sql
:r Schemas\Create.All.Schemas.sql
:r Types\Create.All.Types.sql
:r Tables\Create.All.Tables.sql

Each :r command kicks off the next level of child packages in turn.

SQLCMD has another great feature known as scripting variables that you can use to create dynamic scripts. Essentially you define a scripting variable on the command line with SQLCMD's -v command line option. Now the way scripting variables work, they are replaced wholesale in your scripts with their replacement value. So if you define a scripting variable named environment you can replace it with a value like "Dev", "QA" or "Prod" anywhere it occurs in your script. This is great for making dynamic scripts that need to be built across multiple environments.

In the example I've used a scripting variable named database. You can set the value of the database variable from the command line with the -v option. In the example below I set the database variable to the value "Test".

The nice thing about the SQLCMD scripting variables is that once you declare them you can access them from the parent script you run (in this case Create.All.Sql) or from any child scripts that are run (like Create.Database.Sql, Create.All.Schemas.Sql, Person.Schema.Sql and Sales.Schema.Sql). Here’s the Create.Database.Sql script from the example:

/*

 Create database

*/

USE master;
GO


CREATE DATABASE $(database);
GO

The scripting variable is accessed in the script with $(database). The scripting variable is replaced with its value by SQLCMD, so in the example SQL Server sees this:

/*

 Create database

*/

USE master;
GO


CREATE DATABASE Test;
GO

One thing to keep in mind when you use this scripting pattern is that every script should end with the batch terminator (default is "GO"). If not you could end up with one script running into another and get some strange, not-very-helpful error messages.

Another thing you need to know is that scripting variables are replaced wholesale with their replacement text. This makes them very flexible, since you can replace text anywhere in the script with anything you want. It can also be dangerous if your script is run by someone with malicious intent. A malicious user can replace a scripting variable with T-SQL statements that could damage your data or database structure; so keep your scripting variable-enabled scripts out of the hands of potentially malicious users.

I've attached a sample ZIP file with the directory structure shown in the example above. The scripts build out a few database objects from the AdventureWorks sample database.

NJSQL Saturday Event (10/24)

Mike C — Tue, 06 Oct 2009 19:37:00 GMT

The NJSQL user group is hosting a one-day Saturday SQL Server event at the Microsoft Iselin offices. On October 24th Jacob Sebastian, Robert Pearl and I will be presenting on the following SQL Server topics:

* T-SQL Defensive coding and exception handling best practices
* Database forensics in SQL Server ("Who did it and ran?")
* SQL Server 2008 spatial data ("Where in the World?")

This one-day event is free, but registration is required. More details, including registration link at: http://njsql.org/Default.aspx

No Backup For Asymmetric Keys

Mike C — Fri, 10 Jul 2009 15:32:00 GMT

The encryption features in SQL Server 2005 and 2008 provide the ability to create asymmetric encryption key pairs (RSA public/private key pairs) using the T-SQL CREATE ASYMMETRIC KEY statement. One feature that was left out, however, is the ability to backup and restore asymmetric key pairs generated on the server. Once an asymmetric key pair has been created there's no way to export it out of the server. Unlike symmetric keys, you can't create a duplicate asymmetric key with T-SQL statement options. This can be a serious issue for your disaster recovery (DR) program - if a server goes down you'll never regenerate that same asymmetric key again.

What you can do, however, is import asymmetric keys that were generated by an external source. You can import asymmetric key pairs from strong name key (SNK) files (and public keys only from executable files and assemblies). As long as you import your asymmetric key pairs from SNK files, you can always back up those files to a secure location to handle your DR requirements.

Alternatively you can use certificates (via the CREATE CERTIFICATE statement), which contain an asymmetric key pair and some additional metadata (certificate name, subject, expiration date, etc.) SQL Server does provide the necessary facilities to backup and restore certificates.

Basically if you need to use asymmetric encryption on SQL Server, my recommendation is to (1) generate your asymmetric keys elsewhere and import them into SQL Server, or (2) use certificates instead of asymmetric keys.

So the question is just sort of hanging there -- what purpose does it server to create an asymmetric key pair that you can never export or back up? The best answer I've come up with so far is that you can use these randomly-generated non-exportable asymmetric key pairs for testing, when you don't want to provide your developers with your real production asymmetric keys. This answer is a little unsatisfying, so if anyone out there has a better answer, hit me up!

"Cloning" Symmetric Keys

Mike C — Thu, 18 Jun 2009 01:40:00 GMT

It's well-known by now that SQL Server 2005 and 2008 include new encryption-related statements that allow you to create and administer encryption keys. You can use CREATE CERTIFICATE to create or import a certificate or DROP ASYMMETRIC KEY to remove an asymmetric key from the database, for instance. One of the interesting ommissions from the T-SQL encryption statements is the statements necessary to backup and restore a symmetric key. Why would you want to do this? I can think of a couple of reasons off the top of my head:

You need to backup symmetric keys (and all other encryption keys, in fact) as part of an overall disaster recovery (DR) program. If a server needs to be rebuilt you obviously need a way to restore all encryption keys.
You need to implement the same symmetric keys on multiple servers. There could be a couple of reasons for this -- you might encrypt data on one server and decrypt it on another, or you might be load-balancing across a server farm and need identical encryption keys on multiple servers simultaneously.

It seems like a bit of an oversight to not include BACKUP and RESTORE SYMMETRIC KEY options in T-SQL, but in practice you can effectively achieve the same end results with the standard CREATE SYMMETRIC KEY statement. Basically the CREATE SYMMETRIC KEY statement gives you an option to "clone" the exact same symmetric key on any SQL Server 2005 or 2008 instance, anywhere, at any time. To create a cloneable symmetric key you need to specify two special CREATE SYMMETRIC KEY options:

The IDENTITY_VALUE option, which SQL Server uses to generate a GUID (uniqueidentifier) for the key
The KEY_SOURCE option, which SQL Server uses as key material to generate the actual key

As long as you specify the same values for the IDENTITY_VALUE and KEY_SOURCE options (and the same ALGORITHM), your symmetric key will be exactly the same no matter where, when, or how many times you create it. To be honest, if I were creating a list, always use IDENTITY_VALUE and KEY_SOURCE would be listed in the top 10 list of SQL Server encryption best practices.

Here's a quick sample demonstrating the CREATE SYMMETRIC KEY statement with IDENTITY_VALUE and KEY_SOURCE specified:

CREATE SYMMETRIC KEY test_aes128_key
WITH KEY_SOURCE = 'I am the very model of a modern major general',
IDENTITY_VALUE = 'E pluribus unum',
ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = 'p@$$w0rd';

This CREATE SYMMETRIC KEY statement will create the same symmetric encryption key on any SQL Server 2005 or 2008 instance on which you run it. This brings up another point, about security. If the same KEY_SOURCE and IDENTITY_VALUE options can create the exact same encryption key on any of your servers, they will create the exact same encryption key on any of my servers, or any server owned by any hacker anywhere in the world. So once you've run your CREATE SYMMETRIC KEY statement, the IDENTITY_VALUE and KEY_SOURCE need to be handled like any other secure information. Don't leave them lying around where just anyone can access them. Store them with your certificates, key backups, and other confidential materials in a secure off-site location.

So what happens when you don't specify the IDENTITY_VALUE and KEY_SOURCE options? Well, basically SQL Server generates an unpredictable GUID to identify the symmetric key and the encryption key source material is randomly generated. Basically you'll never regenerate the exact same key again. Ever. There could be situations where this would be handy. The concept of "session keys" comes to mind. A session key is basically a "temporary" key that's only required to encrypt data for a user during a single session. Since it only exists for the life of the session, a totally randomly-generated key is just fine.

For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default. If you didn't want to specify both of these options there should be another option/indicator specifically to say that you want these options generated randomly. At any rate, it's a good idea to get into the habit of treating these options as if they are mandatory unless they have a very specific special-purpose requirement (e.g., "session keys").