Voice of the DBA

Author: way0utwest

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:

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.
December 30, 2016
What Will 2017 Bring?

What do you think will happen in the database world in 2017?

That’s a question I want to ask you today, the last work day of 2016. When most of us come back to work next week, a new year starts, though it won’t really mean much to most of us. We’ll continue on with the projects we’ve been working on, managing the same systems and dealing with similar issues to those we face today. Budgets may reset, which could be a good thing if you can find a way to divert some of that money for your own training or pet project use. In general, next week will just be a continuation of the work many of us have been doing.

If I look forward and try to imagine where 2017 will take us, I envision focus in a few areas, and perhaps a few things that won’t change. As much as I find the progress our industry has made in the last ten years amazing, I also think that year to year we tend to make small changes. It’s rare that a huge advance in computing drives us forward. Usually we can see the technology emerge, gain momentum, and then grow very quickly. That happened with SSDs. The first models were exciting, and expensive, but also prone to failure and burnout. Across a few years, quality improved, prices dropped, and all of a sudden most new machines now use SSDs. In fact, it seems most people working with databases wouldn’t consider purchasing hardware without at least some SSD storage.

There’s a lot of media attention being paid to Artificial Intelligence and Machine Learning these days, and I think these will grow more rapidly in 2017. As we get more tools that make it easier to build applications that incorporate AI and ML, I envision pressure on many developers to begin incorporating these features into applications. Those that are able to do this effectively will likely help their organizations gain a significant advantage over competitors. The maintenance and evolution of these systems is harder than expected. While our tooling will lower the bar to get started, my suspicion is that most of the applications that get built will not provide the expected results and will get abandoned.

That brings me to the second area that I think will grow in 2017. Data Science, and the idea of somehow analyzing all the Big Data (from 2015) to gain amazing new insights will be an even bigger focus in 2017. The media attention and hype have so many managers thinking they need more data science. The interest means higher paychecks, which have everyone that passed Introduction to Statistics in university claiming some data science skills to get a larger paycheck. This will snowball in 2017, as more and more people on both sides press the issue.

The reality is that data science and more complex analysis is hard. It’s much harder than most people realize, requiring lots of knowledge and patience to experiment with data. I’m not sure that most people are willing to make the investment in time and resources that it takes to become a good data scientist. Of course, if it’s like many of the other career paths in technology, even average skills can end up with a successful career.

Security will continue to be a problem, especially as more and more hackers continue to probe organizations and systems for vulnerabilities and weaknesses. Some do this for profit, some (maybe most) for basic vandalism, but we’ll continue to find SQL Injection, phishing, and other attack vectors to be problems. As we connect more and more systems together, and as there is pressure to build more distributed systems, whether with cloud services or business partners, we will have more and more weak points. I wish I could say that 2017 would be the year that our aging systems will see pressure to improve their security and patch more rapidly, I suspect that far, far too many large organizations will continue to tolerate poorly written systems (from a security standpoint) and allow their developers to deploy code that doesn’t remotely adhere to best coding practices. Perhaps one day…

In our SQL Server world, I think we’re going to have an interesting year. All signs point to another SQL Server version, one that runs on both Windows and Linux, so we’ll start to have the challenges and excitement of moving databases across platforms. Add in the ability to run inside containers, and I think we’ll see some crazy growth in SQL Server deployments that will stretch the ability of administrators to keep track of how many instances are actually running. I suspect that we’ll see lots of data loss from small instances that are deployed by developers quickly and easily, without a backup plan. I wouldn’t be surprised to see a whole new set of issues when transaction logs grow to fill disks from instances running in containers that never back them up. Maybe we’ll get the simple recovery model as the default for SQL Server 2017? I can hope.

All in all, I think 2017 is just another new year, but it is the time for you to take stock of your life, decide what you like, what you don’t, and where you want to go. Perhaps you should take another step on your epic life quest, maybe you want to improve your skills, maybe you want to find a new job, or perhaps you have something outside of work that will be important this year. No matter where you are in life, the end of one year and beginning of a new one is a good time to reflect, reminisce, and dream about the future.

Steve Jones

The Voice of the DBA Podcast

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

December 30, 2016
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.

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.

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

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.

#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.
December 29, 2016
Looking Back at 2016

We’re coming to the end of a crazy year. 2016 has seemed to be one of the craziest of my life with world events like Brexit and the US election as well as an astounding number of data breaches. More people who impacted my life passed in 2016 than in any other year I can remember, and I traveled far, far too much this year. Quite a change from the beginning of the year when the Denver Broncos won SuperBowl 50. 2016 has also been a very interesting year in the data world.

Certainly the release of SQL Server 2016 was exciting for many of us. For the first time since 2012, or really since 2008, I thought this was a true, major release of the platform. I was surprised and pleased by the amount of features added and improvements made to this version. I very much liked to see the inclusion of a number of security features. While some of these need some maturity and work, they do bring us some additional capabilities that I think start to help us implement better data protection for our database systems.

We’ve also seen a few things I’ve written about for years coming true in the SQL Server world. We have a Linux version in CTP status, due to be released next year. Whether adding a Linux edition is a good idea or not remains to be seen, but I am glad that Microsoft is making an attempt to port SQL Server to other host platforms. With SQL Server 2016 SP1, we also have a common programming surface, allowing almost all of the T-SQL features that were previously only in Enterprise edition to be used in other editions. This means we’re closer to paying for SQL Server based on the scale of data we process. I think this is a good move that makes sense for Microsoft and customers. While some might lament the hardware limits on Standard Edition, I think they are fine. I just wish it wasn’t sure a big jump to move from Standard to Enterprise, or there were an option in between the two.

The cloud has grown tremendously in 2016, in many areas, but certainly for data. While AWS and Azure grew in size, they also lowered prices for users. It’s not clear how much of this usage is just for database work, but I certainly think that more and more organizations are looking at moving a portion of their data to the cloud. When you can store data cheaply and scale your query computing up and down, this starts to look like a viable option for some workloads. I don’t know that I think most RDBMSes used for on-premise applications make sense in the cloud, but some do, especially when your customer base is distributed and your workload has predictable spikes.

I think the idea of cloud databases for analytics and warehousing makes more sense. Those are the workloads that require larger hardware for peak workload levels and become expensive for local systems. Getting your data to the cloud is a challenge, but I suspect that data movement, gateways, and other innovative ETL (or ELT) solutions are coming. The Azure SQL Data Warehouse is a very interesting product to me, as is the Azure Data Lake, and I look forward to seeing how people start to use these solutions in the future. Certainly the cloud is going to continue to play an interesting role for data professionals in the future.

This was an interesting year of hardware for me. The DevOps movement has said that we should treat servers like cattle, not pets. I’ve started to try and do this with hardware as well. I got a new laptop (VAIO Z Canvas) in 2016, and after setting up my old one with Chocolatey, I did the same with the new laptop, becoming productive with my new machine in a few hours. It helps to have various distributed data services like Evernote, Dropbox, and remote Git Repos, but I suspect many of you have similar services inside of your organization. When I rebuilt my desktop this year, I was using it about an hour after I rebooted the new hardware thanks to Chocolatey. This really make me rethink of my individual machines as cattle. Provided I have some good way to remotely keep various data accessible. That brings me to another interesting issue.

Data breaches were an issue in 2016, as in years past. They seem to be occurring on a regular basis and increasing in size, though it’s hard to determine whether or not the impact to individuals is greater. The Yahoo breaches were incredible in size, with over 1 billion accounts affected. However, other security issues are just as worrisome. The DDOS attack on DYN shut down a number of sites, but what if a more subtle attack managed to change DNS entries. I’d worry that as more of our data is accessible through public networks, the compromise of credentials could lead to more data loss issues.

However, one of the most common issues with computer security has been shown to be out of date software with vulnerabilities where patches have been available. To me, this means we could thwart a significant number of breaches by keeping software up to date with patches. This brings to mind plenty of other issues, but ensuring our platforms are patched seems to be important. Perhaps ensuring we can patch our application software quickly if there are issues from patching platforms is one way to improve security.

Perhaps one of the items that I think dramatically changed in 2016 was the growth of data analysis. Whether we look at the call for data scientists, R analysis of data, visualizations with tools like Power BI, big data or something else, it seems like these were important topics in 2016. This might be the first year where I think that business intelligence truly took a leap forward with tools designed to make analysis easier, and lower the bar. This is interesting, and perhaps profitable, for the average data professional to use in their jobs, but all these tools and options for analyzing data don’t necessarily mean that we will better analyze data. I suspect that many of the initiatives started by individuals and organizations will be abandoned in the short term because the experiments aren’t well designed, and the effort to cleanse and prepare the data for some predictive analytics is greater than what most companies want to invest in this project. However, there are more tools and ways for people to begin their journey to better understanding statistical methods and use them for analysis. The data professional of the future (say 10 years from now) will have a better understanding of data analysis techniques as they begin their careers. Just as we expect most data professionals to have some understanding of relational databases and SQL today.

It’s been a long year, and I, for one, am glad to see it ending. Some great memories and trips, but too much travel for me. I only took 16 work trips (and 5 personal ones), but three of those were over two weeks and I spent about 80 nights in hotels. Hopefully I can substantially lower those numbers in 2017.

Steve Jones

The Voice of the DBA Podcast

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

December 29, 2016