Voice of the DBA

Author: way0utwest

  • 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

     

  • Finding Sysadmins with dba tools–GetDbaRoleMember

    I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

    I’ve been wandering through the dbatools set of cmdlets, trying to see where various cmdlets are useful, and also practicing some PoSh skills.

    Recently I noticed there was a Get-DbaRoleMember cmdlet to use. Of course, when I tried to check it, I found an issue. I didn’t have the cmdlet in my system. Autocomplete didn’t find it and running the cmdlet returned an error. I assumed that there had been a dbatools update, and I hadn’t gotten it.

    I run into this before, and a quick query to @sqlvariant helped me realize I needed to run update-module, not re-run import-module. I did that and it worked smoothly (I did need to be an administrator to update this).

    2017-01-20 09_09_12-cmd - powershell (Admin)

    One I did this, I could run the cmdlet and get data.

    2017-01-20 09_09_55-cmd - powershell (Admin)

    Interesting, but this doesn’t seem incredibly useful. After all, I can easily query this in T-SQL, and my monitoring software will check to see if roles change.

    However, perhaps I am actually going to write something that checks to see if we have consistent sysadmins on all instances. Or perhaps we’re looking to add a sysadmin to an instance where he/she doesn’t exist. In any case, PoSh is a way to easily connect to multiple instances on many machines in a way that’s more cumbersome in T-SQL or SQLCMD.

    I can get the server level roles with –IncludeServerLevel parameter. When I do that, I see my roles and members.

    2017-01-20 09_51_34-Atlas Home Lab .201 - VMware Workstation

    I can also connect to remote servers and get this:

    2017-01-20 09_52_25-Atlas Home Lab .201 - VMware Workstation

    How can I do this in bulk? Well, I can certainly run this multiple times with a list of instances. Let’s make a quick list. I’ll make a quick array:

    $instances=”.\SQL2016″,”Atlas”,”Atlas\SQL2016″

    Now I’ll use that in a foreach loop, sending the server name to the cmdlet and looking for sysadmins.

    2017-01-20 09_56_36-Atlas Home Lab .201 - VMware Workstation

    And I have the sysadmins on all of my instances. Certainly this isn’t terribly useful by itself, but I can easily add more programming that looks for a user, maybe checks for which machines have a sysadmin, maybe add (or remove) a sysadmin from instances that are out of sync with what I need.

    This isn’t one of the cmdlets I’d use the most often, but I can see it being handy when trying to automate some of the permissions checks across multiple machines that I need to keep in sync. At the very least, this could be helpful in comparing permissions between primary and secondary (or DR) nodes.

    Note: I haven’t tried it, but I suspect that the migration cmdlets at dbatools would be better for keeping primary and secondaries in sync.

  • How Much Code Can You Review?

    When I was in college, I mostly wrote my own code for assignments (and fun), turning it in when I thought it was done. At some point I had a class that started to adopt more software engineering practices, and I was forced to get my code reviewed by a peer before handing it in. This was an interruption to my workflow (and more work since I had to review other code), but it did allow find mistakes I had made and seemed to improve my grade.

    In one of my first development positions, I had to get two people to review my code before I could submit it for deployment. A humbling and painful process at first, but over time I became used to the idea. I’m not sure the quality of code improved, but it was more readable, more in-line with everyone else’s code, and as we learned techniques that worked well, all developers adopted them quickly.

    These days as I talk with various customers and clients, I find that code reviews are handled very inconsistently. Some companies require them, some have automated processes, some have ad hoc reviews, and everyone has ways to circumvent the system. Certainly emergency patches need to be made at times, often with little review, but I will say that it seems the more common a set process and practice is, the less issues a customer has.

    If you review code, or have your own code reviewed, on a regular basis, I have question for you.

    How much code can you review for a deployment?

    The question I’m asking is about your process and habits. Is it worth reviewing every bit of code? Perhaps there are objects that you don’t worry about as much as others, or certain developers that receive more (or less) review of their code. Perhaps seniority makes a difference as to the level of scrutiny. If there are problems found in QA and changes made, is there a way to re-review code? I’m wondering today about your process. Share what you can, and if you want to do it anonymously, please feel free to send me a note at sjones (at this domain).

    Steve Jones

    The Voice of the DBA Podcast

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

  • 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.