Voice of the DBA

Author: way0utwest

  • Merry Christmas 2011

    Merry Christmas and Happy Holidays.

    The Voice of the DBA Podcasts

    Enjoy the blooper reel

  • Backing up the Service Master Key

    Making a backup of your Service Master Key (SMK) should be one of the first things that you do if you are enabling encryption. This key encrypts and secures your other keys, and it will be needed when you have to rebuild the server.

    Backing up the key is simple. You use the BACKUP SERVICE MASTER KEY command and it only has two arguments

    • File – The path and name of the file for the backup.
    • Password – provides security for the backup file. This is needed when restoring the file.

    That’s it. Performing a backup is as simple as running a command like this: 

    -- Backup the SMK
BACKUP SERVICE MASTER KEY
 TO FILE = 'c:\sqlbackup\MainServiceMaster.key'
 ENCRYPTION BY PASSWORD = 'S3cureP@ssword!sneeded'

    Securing this file, however, is a little harder, and managing it is really hard. Ideally you do not want this key stored with the backup files for the server, because you don’t want anyone to have this and the data. However if you must do that, and that might be the practical thing to do so that it’s available when you use this tape/disk to recover the server, I recommend you do not keep the password with this file. Store it in an admin system somewhere else, preferably a protected system with something like KeePass or Password Safe.

    A couple things to note. Just like with a backup, the service account needs write permissions on the path (local or UNC) to create the file. This command requires the CONTROL SERVER permission, so not anyone can use it.

  • All I want for Christmas

    It’s the Christmas holiday, and many of you might be already out celebrating and away from work this week. Today is usually a day off for many companies, with last minute preparations for the celebrations this weekend.

    Those of us working in IT are very lucky for the most part. We have good jobs, jobs that don’t require heavy physical exertions, we work in offices not in inclement weather, and we are paid well relative to many other professions.

    I’m not Santa Claus, but I’ll offer up a virtual knee for this holiday poll:

    What would you like to get for Christmas?

    I’m looking for anything technology related, for work, for fun, or for the family. It doesn’t even have to require electricity, but what fun gift would you like to get this year? Have some fun and dream a little, but not too much. Someone close to you might read your answer and actually pick out the gift for you if it’s affordable and reasonable.

    For me, there’s not much I want or need in the technology space, and I’m actually trying to reduce the amount of technology stuff I have and work “lighter” with less stuff. However the one thing that I don’t need, but I’d like, is an Airport Extreme router that I can mount in the kitchen, add a set of speakers, and plug in my iPhone for music while I cook.

    Steve Jones

    The Voice of the DBA Podcasts

  • How many calls – A T-SQL Question

    I got a call from a friend recently that was looking for some query help. He was actually using Access, which I haven’t used in years. He knew a little T-SQL, so he could convert anything I gave him to work with his database.

    Here was his issue. He had a list of calls made for a marketing campaign, and with each call, a call back date. His task was to get counts of the calls made for a particular date for which there were call backs within two time periods: 5 days and 10 days.

    I want to walk through what I tried and what worked. I actually came up with two methods, though I’m not sure either is that efficient. However they worked, and since this is something he’ll run in Access monthly, it’s not a big deal.

    I set up a table and get some samples from him:

     CREATE TABLE Calls
( date_sent DATETIME , acc_call_date DATETIME ) GO -- rules -- #1 acc call < 6 days -- #2 acc call < 11 days INSERT calls SELECT '12/1/2011', '12/2/2011' -- meets #1 INSERT calls SELECT '12/1/2011', '12/3/2011' -- meets #1 INSERT calls SELECT '12/1/2011', '12/8/2011' -- meets #2 INSERT calls SELECT '12/2/2011', '12/8/2011' -- meets #2 INSERT calls SELECT '12/2/2011', '12/9/2011' -- meets #2 INSERT calls SELECT '12/3/2011', '12/4/2011' -- meets #1 INSERT calls SELECT '12/3/2011', '12/4/2011' -- meets #1 INSERT calls SELECT '12/3/2011', '12/11/2011' -- meets #2 INSERT calls SELECT '12/4/2011', '12/11/2011' -- meets #2 INSERT calls SELECT '12/5/2011', '12/6/2011' -- meets #2 go

    I have two rules that track the calls. As a quick note, if a call is returned in 5 days, it’s also returned in 10 days, so we should never have more calls returned in 5 days than are returned in 10 days.

    Essentially to meet rule #1, we want this:

    SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent

    If I run this, I get back three rows:

    date_sent               five_day_call

    ———————– ————-

    2011-12-01 00:00:00.000 2

    2011-12-03 00:00:00.000 2

    2011-12-05 00:00:00.000 1

    These are the counts of calls returned in five days. If I change the scalar from 6 to 11, I get back the calls back in ten days.

     SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
go

    The results, as expected, include the 5 calls above, but also have the additional calls returned in ten days.

    date_sent               ten_day_call

    ———————– ————

    2011-12-01 00:00:00.000 3

    2011-12-02 00:00:00.000 2

    2011-12-03 00:00:00.000 3

    2011-12-04 00:00:00.000 1

    2011-12-05 00:00:00.000 1

    Now I need to combine these sets. The first thought is often a UNION, but in this case, that doesn’t work. Here’s what happens:

     

    SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
UNION SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent

    I get duplicate rows for each date if there are rows from each separate query:

    date_sent               five_day_call

    ———————– ————-

    2011-12-01 00:00:00.000 2

    2011-12-01 00:00:00.000 3

    2011-12-02 00:00:00.000 2

    2011-12-03 00:00:00.000 2

    2011-12-03 00:00:00.000 3

    2011-12-04 00:00:00.000 1

    2011-12-05 00:00:00.000 1

    I can’t do a DISTINCT here, nor can I sum up the rows, because the ten day calls include the five day calls.

    Plus my friend really wanted this report:

    date_sent               five_day_call ten_day_call

    ———————– ————- —————-

    2011-12-01 00:00:00.000 2             3

    2011-12-02 00:00:00.000 0             2

    2011-12-03 00:00:00.000 2             3

    2011-12-04 00:00:00.000 0             1

    2011-12-05 00:00:00.000 1             1

    This report is designed to measure the effectiveness of calls, and business analysts need an easy report. If I join the two queries on the call date (date_sent), the problem is that I don’t necessarily have matching call dates for all rows.

    What about an outer join?

    ; WITH fiveCTE (call_date, five_day) AS ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) , tenCTE (call_date, ten_Day) AS ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
) SELECT a.call_Date
 , a.five_day
 , b.ten_day
 FROM fiveCTE a
   FULL OUTER JOIN tenCTE b
     ON a.call_date = b.call_date

    I’ve moved the two queries into CTEs for readability. I then join them on the date the call was made and return the results. I get this:

    call_Date               five_day    ten_day

    ———————– ———– ———–

    2011-12-01 00:00:00.000 2           3

    NULL                    NULL        2

    2011-12-03 00:00:00.000 2           3

    NULL                    NULL        1

    2011-12-05 00:00:00.000 1           1

    Hmmm, not quite what I need, but it’s closer. I need to get the date for ten day calls, and I also need the NULLs removed from the five day calls.

    My first take is to remove the NULL counts.

    ; WITH fiveCTE (call_date, five_day) AS ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) , tenCTE (call_date, ten_Day) AS ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
) SELECT a.call_Date
 , ISNULL( a.five_day, 0) 'five_day' , b.ten_day
 FROM fiveCTE a
   FULL OUTER JOIN tenCTE b
     ON a.call_date = b.call_date

    This was better, and cleaned up the results slightly.

    call_Date               five_day    ten_day

    ———————– ———– ———–

    2011-12-01 00:00:00.000 2           3

    NULL                    0           2

    2011-12-03 00:00:00.000 2           3

    NULL                    0           1

    2011-12-05 00:00:00.000 1           1

    Next, I’ll clean up the dates.

    ; WITH fiveCTE (call_date, five_day) AS ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) , tenCTE (call_date, ten_Day) AS ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
) SELECT ISNULL(a.call_date, b.call_date) 'date_sent' , ISNULL( a.five_day, 0) 'five_day' , b.ten_day
 FROM fiveCTE a
   FULL OUTER JOIN tenCTE b
     ON a.call_date = b.call_date

    This is much better:

    date_sent               five_day    ten_day

    ———————– ———– ———–

    2011-12-01 00:00:00.000 2           3

    2011-12-02 00:00:00.000 0           2

    2011-12-03 00:00:00.000 2           3

    2011-12-04 00:00:00.000 0           1

    2011-12-05 00:00:00.000 1           1

    That’s what I want, or, what my friend wants. However that wasn’t what I sent. I wasn’t sure that Access would support the full outer join and CTEs, so I actually came up with another way that I’ll write about next time.

    If you know of a more efficient way of doing this, I’ve love to know what it is.