Voice of the DBA

Category: Blog

  • My 2016 by the Numbers

    This is it, the last work day of the year. I wrote about my look back from the data side of the world, but I also wanted to take a look back at my personal 2016 from the work perspective. This is a chance to examine the way my career’s gone this year and make plans for next year.

    Travel

    I traveled a lot this year. I had sixteen trips in 2016 for work. That’s less than some years, but it was a harder year for me. With five personal trips out of town, that means I had over 80 nights in hotels or other accommodations, which is quite a bit for me. Some people like that, but the time away from home wears me down.

    I also had some long trips. Three of these trips were over two weeks, which is a long time for me. I did get some holiday time on some of these trips, and a couple with me wife, which makes things a little easier.

    I have already made plans to reduce this in 2017. I hope.

    • 16 business trips
    • 80+ hotel nights
    • 5 personal trips
    • 42 flights
    • 4 trains between cities
    • 1 car trip to an event
    • 2 car rentals driving on the left side of the road

     

    New Countries

    On the plus side, I went to two new countries this year for work: Norway and Denmark. Those were great experiences, and I have to really thank the organizers of those events (SQL Nexus and SQL Saturday – Oslo). They made the trips enjoyable and memorable. I’m not sure if I’ll get back, but I would certainly like to.

    The hard part of adding in the other countries is that I need to plan these trips with business trips, so they get extended. I may get to one new country in 2017, and my fingers are crossed that things will work out.

    • 4 countries in which I delivered talks during 2016
    • 9 US States in which I presented
    • 17 cities where I stood in front of an audience.

    Conferences

    In the past I’ve typically spoken at a few user groups, SQL Saturdays, the PASS Summit, IT/Dev Connections, SQL in the City, and SQL Bits. This year I added a few new ones. I was lucky enough to get selected for a couple VS Live events, SQL Nexus, the London Database Professionals Meetup, and MeasureUp, a developer event in Austin.

    The highlight for me was //build//, where I was on stage with Donovan Brown from Microsoft talking database devops to hundreds of people. I haven’t spoken at a large Microsoft event before, so this was quite a honor for me.

    Caveat, I was selected to speak at Ignite a few years ago, but had to cancel with a knee surgery. Still, I’m quite honored to have been chosen twice.

    • 7 expensive conferences
    • 7 SQL Saturdays
    • 4 user group presentations

    Learning

    This was an interesting year for me. Each year I try to make an effort to learn some new technologies and new skills. At the beginning of 2016, I was continuing on with learning Python. In 2015 I started attending (remotely) the San Diego TIG meetings with the Python track and got interested. I found this was an interesting way to force some learning on myself. I even worked through part of the Advent of Code with Python (as well as T-SQL and PoSh) to practice some programming skills.

    However, I fell off throughout the year. I started brushing up C# skills since I needed some small applications for Always Encrypted, but again fell off. I started a mobile project and let that go. It seems as though life seems to get in the way of any long term learning, and I made the mistake of not setting aside specific times each week for learning. I relied on my curiosity and interest in technology to drive learning, but this wasn’t as concentrated or focused as I would have liked.

    I did make time for some pre-cons at a few events and made a point of attending a number of sessions at every event, taking notes and even working through some sample applications. A list of the major talks I remember attending and learning in.

    • Encryption – I’ve always dabbled, but I spent time watching a number of sessions from other presenters and building projects with SQL Server’s encrpytion capabilities.
    • Azure Machine Learning – worked through a few sample and experimental projects on my own.
    • Microsoft Bot Framework – build a sample bot
    • Extended Events – drove me to complete a Pluralsight course
    • SSIS Frameworks – need to use this
    • VSTS DevOps – I’ve started using VSTS almost every week
    • PowerShell – I’ve begun using PoSh more and more to handle some tasks and practice my skills. I especially like dbatools.
    • C# – On and off practice in addition to Pluralsight courses and a few sessions.
    • Python – I really like Python. I worked my way through part of an ML book, but I’d like to do more with this language. I started with v

    In 2017 I need a better learning plan.

    SQL Server

    This was a fascinating year of SQL Server work for me. I began working with a large number of parts of the platform. I spent time working with all of these items:

    • Extended Events
    • Row Level Security
    • Dynamic Data Masking
    • Always Encrypted
    • TDE
    • SQL Audit
    • T-SQL (new language constructs as well as practicing old skills)
    • XML
    • JSON
    • Stretch DB
    • Always On
    • In-Memory

    There are probably other areas that I tackled, but it’s hard to remember across the year. In each of these areas, I spent a number of concentrated hours experimenting, learning, and getting some piece of technology working. I’d hope to continue this in 2017, especially as I expect another new version to appear with some enhancements that I’m interested in using.

    Focus

    This was a year where my focus changed from DBA to more of a developer look at the world. While I certainly worked on non-SQL technologies, my focus with Redgate has been more of a developer, DevOps, CI/CD look at the world and trying to help customers and community solve those issues.

    I find this to be more interesting, but also most challenging. One of the things I learned as a developer is that there are always cool new things to experiment with and it can be hard to actually focus on the technology you primarily use and continue to improve the way in which you use the tools. There is a distraction from all of the possibilities and options available to you.

    As an example, I started to work with one of the dbatools cmdlets and saw a note that Visual Studio Code supports PoSh. I’ve usually worked with the ISE, but launched VS Code and looked for a PoSh extension. I found a few and spent time looking at reviews and comments.

    Then I picked one, installed it, restarted, and then tried to code. Struggled to run the PoSh inside of VS Code, so Googled for this. Spent more time trying to get this working. I ended up learning a couple things, but really I wasted close to an hour playing with VS Code, trying to make it work for me, when I had a perfectly good tool (ISE) that works. As much as I like lightweight editors, I can’t spend a bunch of time trying new ones when I have some that work.

    I want to try and focus more on specific items next year, make improvements and grow deeper in a few areas rather than trying to grow too widely.

    Overall

    I found 2016 to be a hard year. My travels were tiring and difficult with family life. I had a few personal issues that made the year hard, among them my second son graduating from high school. I stressed about trying to spend lots of time with him (and my 3rd child) before he leaves home. On the plus side, despite the stress, I was able to watch him complete his Eagle Scout rank for Boy Scouts, which made me quite proud.

    Work was a mix, both with more demands in some ways, less in others. Higher engagement with some parts of Redgate, lower with others. Certainly a year of change. However, I have a great company, and I really enjoy working there. They even got me to dress up:

    awards

    The crazy election(s) of 2016 in the US and UK, the number of talented celebrities I will mourn, those where downsides. The Broncos winning the Super Bowl, lots of skiing, and some long vacations with family where highlights. I also managed a 199 day run streak and over 400 miles for the year as part of my cousin’s Zuckerburg challenge to run a mile a day.

    I’m glad 2016 is ending, and I look forward to 2017 and enjoying a better year.

  • Checking Permissions for Keys–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I got a call from someone wanted to check how permissions were stored for encryption objects. I ran a quick double check for them and decided to write this short post.

    Let’s say that you create a few encryption keys. In my case, I’ll use this code to create a symmetric key, an asymmetric key, and a certificate.

    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

CREATE ASYMMETRIC KEY HRProtection
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'Use4SomeStr0ngP@ssword%^';

GO

CREATE CERTIFICATE MySalaryCert
ENCRYPTION BY PASSWORD = N'UCan!tBreakThis1'
WITH SUBJECT = 'Sammamish Shipping Records',
    EXPIRY_DATE = '20161231';
GO

    I do this, I have these objects.

    2016-11-29 14_21_19-SQLQuery11.sql - 192.168.1.204_SQL2016.EncryptionDemo (sa (57))_ - Microsoft SQL

    Let’s now grant rights to these objects. I’ll use this code to grant CONTROL to a user.

    GRANT CONTROL ON SYMMETRIC KEY::MySalaryProtector TO JoeDBA

GRANT CONTROL ON ASYMMETRIC KEY::hrprotection TO JoeDBA

GRANT CONTROL ON CERTIFICATE::MySalaryCert TO JoeDBA

    Once I do this, I should see permissions, right? Let’s check.

    2016-11-29 14_25_30-Database User - JoeDBA

    I don’t see any permissions in the dialog above. That’s not exactly what I’d want to see. After all, if I’m trying to determine why a user can’t access a certificate, I’d want to know if they had rights here.

    Instead of this, I need to use T-SQL, and check for specific classes in sys.database_permissions. Here’s the query looking for class 24 (symmetric keys), 25 (certificates) and 26 (asymmetric keys).

    2016-11-29 14_27_57-SQLQuery11.sql - 192.168.1.204_SQL2016.EncryptionDemo (sa (57))_ - Microsoft SQL

    You can see that I have permissions in here, and if I check the principal_id, I’ll find these are for my user. I could also join to database_principals and get specific information for my user.

    2016-11-29 14_30_41-SQLQuery11.sql - 192.168.1.204_SQL2016.EncryptionDemo (sa (57))_ - Microsoft SQL

    #SQLNewBlogger

    This took a bit longer as someone asked me a question and I didn’t know the answer. I had to dig and read some documentation, but I found some answers and documented things myself.

    Learned something, showed it, and hopefully will remember it from now on.

  • Post TDE–Getting Unencrypted Backups

    I saw a question posted recently about someone that had disabled TDE and was still having issues restoring a backup. This doesn’t seem like that should be an issue, but it can be. A little testing shows how.

    Let’s assume I have an encrypted TDE database. If I run a query, I can see the status as 3, which is encrypted.

    2016-11-23 11_16_07-11_TDE_Demo.sql - localhost_SQL2016.TDE_Primer (PLATO_Steve (64))_ - Microsoft S

    If I take a backup at this point, the backup will be encrypted, and to restore this on another instance, I’d need to first restore the certificate. I don’t want to do that, so let’s remove encryption. This is a simple command

    ALTER DATABASE TDE_Primer

      SET ENCRYPTION OFF;

    This runs quickly.

    2016-11-23 11_18_08-11_TDE_Demo.sql - localhost_SQL2016.TDE_Primer (PLATO_Steve (64))_ - Microsoft S

    If I now query for encryption, I see this.

    2016-11-23 11_19_00-11_TDE_Demo.sql - localhost_SQL2016.TDE_Primer (PLATO_Steve (64))_ - Microsoft S

    A one means that this is an unencrypted database, but a DEK (Database Encryption Key) exists. If I were to detach and examine this database file with a hex editor, the pages would be decrypted.

    I’ll now take a backup and move that to another instance. Once I’ve copied that over, I’ll try to restore the backup. In T-SQL, I’ll see this:

    2016-11-23 11_21_43-SQLQuery7.sql - (local)_SQL2016_qa.master (PLATO_Steve (60))_ - Microsoft SQL Se

    Why is this? The database was decrypted, as was the backup. In fact, if I open my backup file in a hex editor, I can see row data.

    2016-11-23 11_23_12-XVI32 - tde_primer_decrypted.bak

    The Problem

    When SQL Server goes to restore the file, it reads part of the header. In here, the process must detect the DEK and try to decrypt that key. However, since this new instance does not have the certificate, this doesn’t work and an error is thrown, despite not needing the key since the data isn’t encrypted.

    The issue here is the DEK still exists in the source database.

    The Solution

    Let’s fix this. I’ll return to my first instance and the original database that was TDE encrypted and now is not. I can issue this:

    DROP DATABASE ENCRYPTION KEY

    Once I do this, it completes quickly. This is a standard DDL command, but one that’s not often used.

    Once I do this, I’ll take another backup and return to the second instance. Now when I try the restore, I see this:

    2016-11-23 11_27_28-SQLQuery8.sql - (local)_SQL2016_qa.master (PLATO_Steve (58))_ - Microsoft SQL Se

    If you’re having issues restoring a database that used to be TDE encrypted, try removing the DEK and then backing it up.

  • Back After the Break

    Refreshed and back to work today. After a week at Redgate Software for SQL in the City, I took a week to travel in Ireland with my family. In that time, I really ignored almost all email and didn’t manage SQLServerCentral at all.

    Well, I did fix one article bug and replied to a couple comments, but mostly I ignored things. Even over Christmas and yesterday, back in the US, I tried to really ignore most work. I spent about an hour doing some SPAM cleanup, but that was early morning because my body clock was still on GMT time.

    However, I’m back at it today. Up at almost a normal time (no kids to get to school, so slightly late), I wandered to the office, made coffee, and got back to editor duties.

    I feel nice and refreshed. Being disconnected in Ireland for most of a week, I really had a nice chance to relax and not think or worry about computers and technology. I even avoided most Twitter/news on my phone, just getting away.

    Now it’s time to get back on track, and I’m a little overwhelmed as I’ve been out of touch for almost two weeks. Feels like I have a lot of editing to do and plenty of scheduling and certainly need to get back to learning more and improving myself.

    Hope everyone else had a great holiday break.