Voice of the DBA

Category: Blog

  • You need to run DBCC CheckDB regularly

    You never know when you’ll encounter corruption. It can happen at any time, usually due to some sort of hardware problem or driver issue. Corruptions don’t disappear and you can’t necessarily

    Corruptions can be caused by numerous factors, and it isn’t something you can predict. However sooner or later, you’ll get corruption on one of your instances. I don’t know which one, whether it’s the finance production instance or the vacation tracker development database, but it will happen somewhere.

    Since corruption can flow through to backups, and can exist in your system for some time, you could end up losing lots of data. Your best defense is to run DBCC on a regular basis and ensure that you catch corruption or problems as early as possible.

    My recommendations in order. If you can’t do #1, do #2, or #3, but try to get something in place.

    1. Run DBCC on every database, every day – If you can, run this on all your servers. I know it’s a large request for some of you, but if you can do it, do it.
    2. Run DBCC on every database on another instance, restoring from last night’s backup – If you can’t run DBCC on your main server, offload it onto another one.
    3. Run DBCC every day on some database, rotating so that all databases are covered – Do this so that at least every week or two you have a DBCC run on your production databases. You can do a random sample of some sort, but hit all of them as often as possible.
    4. Work on a your resume or CV – If management won’t let you run DBCC checks, sooner or later you’ll have an issue, take some blame, and need a new job. Be prepared for that, if for nothing else.

     

    sqlbackuproOne of the recommendations to minimize impact from CHECKDB operations is to run them on another server, using an automated process. SQL Backup Pro 7 from Red Gate makes this very easy to do with automated backup file copy operations, restores, and CHECKDB execution.

  • Cool Data Visualization

    I attended a course from Edward Tufte a few years ago on Data Visualization, and while I learned some neat things, I never was able to implement them well. I think part of it is a complete lack of an art gene in my person, but I do keep it in mind sometimes and would like to actually implement some of the ideas in his books.

    Today someone at Red Gate posted a link to this very cool data visualization on wind. It shows the wind speeds on days for the entire US, but in a very visual way. This is what it looks like, but if you go to the site, you’ll see it animated.

    wind

    There’s also a gallery of some days in the past to view.

    It’s neat because unlike a chart, or a static picture, you can easily see where wind is flowing, and the intensity for a large area. You can zoom in to see what the speeds are in a particular area.

  • The Mentoring Experiment – Cycle 2

    It’s been a long time, almost a year since Andy Warren and I kicked off The Mentoring Experiment. We had a successful cycle 1, and are not in the process of looking for mentors for cycle 2. Once we get an idea of how many people are willing to be mentors, we’ll start taking applications for mentees.

    If you are interested in helping out a fellow SQL Server professional, apply today.

    Cycle 1 worked out well, and I know a few people expressed interests in cycle 2 late last year and earlier this year. My apologies for the delays in getting started. Both Andy and I have been busy and this was one of the projects we were working on that suffered. We hope to get things moving a little more quickly and smoothly in the future, so subscribe and watch for more announcements on the Mentoring Experiment Blog.

  • Creating a Symmetric Key in SQL Server

    Symmetric keys in SQL Server are recommended for encrypting data in columns. They are a good balance of security and resource usage, much better than asymmetric keys. Creating a symmetric key is fairly simple, using DDL that’s easy to understand.

    One note before I show this is that symmetric keys are deterministic when created, meaning that the same parameters run in different databases will result in the same key. That means that the same key in a different database (or instance) can decrypt data that was encrypted in your production instance. Keep control of the parameters used to create symmetric keys and secure them. That means watch out for storage of these items in source control, in installation files, upgrade scripts, etc.

    Creating a Key

    The creation DDL used is the CREATE SYMMETRIC KEY statement. This command has a number of parameters that you can change. The important ones for most people are:

    • the algorithm
    • the encryption mechanism
    • the key source
    • the identity value

    You should try to use the most secure algorithm you can, which is AES_256 in SQL Server 2012. It’s the same back to SQL Server 2005. You should avoid the RC4 algorithms, since they are not terribly secure. Even the DES ones you might avoid, but do some research to understand if you have a need to use anything less than AES_256.

    The encryption mechanism provides protection for the key. You can use a password (secure it) or you can use another key. The common way to secure the symmetric key is with an asymmetric key (or a certificate). However if you have the option to use a hardware module with the extensible key management (EKM) system, use that. You can use multiple encryption mechanisms if needed, which might be useful for separating the access to this key for different users.

    The key source provides a way to seed the key. This is a parameter you need to regenerate the key.

    The identity value is optional, but provides more a passphrase to tag a key. Useful for temporary keys.

    To actually build a key, let’s create one here using a few parameters, and securing it with a password:

    -- create a symmetric key
create symmetric key MySalaryProtector
 WITH ALGORITHM=AES_256
    , IDENTITY_VALUE = 'Salary Protection Key'
    , Key_SOURCE = N'Keep this phrase a secr#t'
  ENCRYPTION BY PASSWORD = 'Us#aStrongP2ssword';
go

    That’s it, once you have executed this, you have created a key. You can see your symmetric keys by querying sys.symmetric_keys

    SELECT * FROM sys.symmetric_keys

    sym_key_a

     

    That’s all you need to do. There’s not backup or restore of a key; if you need to recreate it, supply the same parameters and you’ll get the same key. The keys are stored in the backup of a database, so if you restore from backup, you’ll have them back as well.

    In another post, I’ll look at actually encrypting data with a key.