Voice of the DBA

Tag: sql server

  • Your Favorite Feature that Needs Work

    The SQL Server platform has grown tremendously since I started working with SQL Server v4.2. We’ve had various subsystems added, lots of enhancements, and even new languages. There have also been a few parts of the platform that have been discontinued, such as Notification Services and English Query. There are items that are deprecated, though far fewer that have actually been discontinued from the product. I’m not sure if I think that’s better or worse for all of us.

    Apart from the deprecated, discontinued, and removed features, there are also lots of features in the platform that are stale. They receive very little development, and perhaps are very lightly featured on the What’s New pages. Or they never get any development from version to version.

    This week, I’m curious what features you use or like that need help. SQL Server v.Next is in development, and while I don’t think major changes will be made between now and RTM, perhaps there are improvements that could be made in some areas. I’m sure there are a few SQL Server developers at Microsoft that have time to ask for a few more pull requests from their particular area of development. With that in mind…

    What feature in SQL Server do you really like and wish would be improved in the future?

    Your idea would be large or small. Perhaps you have some language enhancement. Maybe you run setup regularly and there is a change that might streamline that process. It may be that replication conflict resolution is a constant problem for you and you’d like a few development hours spent on this. It could be anything that you use, or would like to use. Perhaps you have an idea for an improvement, or maybe you just want something done. Let us know today.

    The place to log bugs or feature requests is at the SQL Server Connect site. Post your idea there, then put a link in the discussion and make a case for why people should vote for it. Who knows? Perhaps you’ll get some improvement for the feature you like in the next version of SQL Server.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Using a DMK in TDE

    When you setup TDE, you need to create a DMK (Database Master Key) in the master database if one doesn’t exist. This key forms the basis of a chain of protection for your data inside of the SQL Server instance. The hierarchy is:

    • The DMK exists in the master database. This protects a certificate or asymmetric key by encrypting it.
    • The certificate in turn encrypts the DEK in a database using TDE. This certificate is required to open the DEK in the database, or open the DEK during a restore operation.
    • The DEK encrypts the data.

    There is only one DMK in each database, so the DMK in master for TDE could be used to protect multiple certificates, asymmetric keys, or symmetric keys that exist in master for use by TDE or any other encryption mechanism.

    A particular DMK is not needed to restore a TDE database on another instance. Rather the new instance of SQL Server should have a DMK created in the master database. Then the backup of the certificate is restored on the other instance, protected by the DMK there. Once this is complete, the TDE database can be restored.

    If you’d like to know more about what a DMK is, I’ve written about it in a previous post.

  • The vNext Bleeding Edge

    Microsoft has had early access programs for years. I worked at a few companies that were a part of the TAP (Technology Adoption Program) program, and we would get access to code early in exchange for providing feedback and working closely with Microsoft engineers. This was interesting, as well as good and bad. We could move early to implement new features, but we also had the struggles of dealing with code that wasn’t always working as expected.

    Microsoft has stared a new program, the SQL Server Early Adoption Program (SQL EAP) to allow companies interested in moving to SQL Server v.Next as soon as possible. I suspect lots of ISVs that might want to build products for SQL Server on Linux might get involved, but there will also be some large corporations that want to influence the platform and get certain features changed or prioritized. The benefits note that participants can come to the SQL CAT team lab to test things, work with product managers, and get separate support if they go into production. For companies that might feel the next version provides some new benefits,

    Microsoft gets all sorts of feedback, but working with a small group of product managers or engineers is a good way to get your voice heard above others. I know that MVPs get the chance to exert similar influence, and at times this can be valuable. However, one has to keep in mind that this is the chance to voice opinions and thoughts, not votes. Microsoft isn’t necessarily going to implement something you want just because you convinced some engineers it’s a good idea.

    Getting into the program does mean that your are bound by an NDA, and that you may have a separate EULA for production workloads. However, if your company likes being on the bleeding edge, and wants the chance to upgrade early, perhaps you should consider applying. I would say that participation in the TAP program was a beneficial experience for my employers. Perhaps the SQL EAP will be for you as well.

    Steve Jones

     

  • What’s a Database Encryption Key (DEK) in TDE

    The encryption mechanisms in SQL Server are interesting, and they work well, but they are somewhat poorly named. I ran across a few people struggling to understand, so I decided to cover the concepts in a series of posts. This one looks at the Database Encryption Key (DEK).

    The Purpose of the DEK

    The DEK is designed to actually encrypt and decrypt the data in your mdf/ldf/ndf/backup files when you use TDE. This key is passed to the AES algorithm and allows SQL Server read a block of encrypted text from disk and decrypt it before placing it in memory. This same process is reversed, with data from memory being encrypted before being written to disk.

    The DEK is created in a database, specifically for use with TDE and isn’t used in any other encryption process. The DEK also cannot be backed up, or restored, except with a database restore.

    Protecting the DEK

    The DEK is protected with a certificate. This can be a purchased or self-signed certificate, but in either case, the certificate must reside in the master database of the instance hosting the TDE encrypted database.

    When a DEK is created, the DDL requires that either a certificate or asymmetric key is specified as protecting the DEK. SQL Server will use this certificate (or asymmetric key) to decrypt the DEK when the database is first opened and then uses the DEK to encrypt and decrypt all data.

    Without the certificate that has encrypted the DEK, the SQL Server instance cannot access any data encrypted by the DEK.

    That’s about it. The DEK simply provides encryption for TDE, and exists only inside of a particular database that will use TDE.