Voice of the DBA

Author: way0utwest

Software Maturity with Release Management

For most of my career, releasing new versions of software that I or others had written consisted of manually running scripts or copying files, maybe using an installer once in awhile, but that was rare. In many cases, the process of actually deploying the software involved a developer sitting with an operational person, especially if the operational person had been burned by previous deployments that were poorly executed. Once burned, you usually require the person building the scripts to be available when they are run.

That’s a poor way of managing the release of software. We write code to allow computers to repeat the instructions we’ve given them over and over. Computers are good at that, and they don’t make mistakes the third or fifth or hundredth time they execute the instructions. We might not give them the correct instructions, but computers don’t make mistakes and do execute what we code. If we find mistakes, we fix them and let the computer then execute them again.

However it seems that for many individuals that release software, they want to do it manually. Especially when the release is to just one machine (or database) inside their organization. And time and time again, I find that these people spend time building scripts, correcting them, and still finding mistakes it production deployments that they need to fix. Often these fixes are made on the fly, with no testing.

There’s a good mantra in software that if something is painful, do it more often. Make it easier with automation and execute it multiple times. Learn to code it better. There’s a whole industry that has grown up to help you release software more often, multiple times, and in a controlled manner. These are release management (RM) tools like Octopus Deploy and TFS Release Management. I’ve been using both of these internally, and I couldn’t imagine not using an RM tool at any point in the future. It should be easy to deploy software from one machine to another, and the process should be repeatable. That’s a sign of mature software development.

If you’re not using a RM tool, I’d urge you to check one out. Most let you get started for free, and a small Proof of Concept (POC) project is the way to get started. Once you learn the ins and outs of how you really deploy software, you will never want to do it manually again.

Steve Jones

The Voice of the DBA Podcast

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

May 16, 2016
The Proliferation of Roles

The best practice guidance for SQL Server security is to use roles for permissions, instead of granting rights to users. I’ve always followed this guidance in my career. I’ve learned that if one person needs access, sooner or later someone else will. Even if it’s an automated process, I’ll use a role so that I can build a test login to ensure I’ve configured things correctly.

In small companies, or in relatively static applications, this makes perfect sense and few people argue with the rule. That is, until they get some requirement that only one account will ever be used. Then they want to just grant rights to a user. After all, why add the role for one person. See my thought above. Sooner or later, someone else will want access.

However I also have had people complain that if each new required position or process needs their own role, sooner or later we’ll have this proliferation of dozens of roles.

True.

I worked in a large Fortune 100 company and we had thousands of groups in our AD forest, and easily dozens of roles in many databases. While that might seem complex and confusing, it wasn’t bad. We named roles to match AD groups or job functions, and adding in new users was simple as we usually mapped them to the same roles as a previous user. If a new system or person needs access, usually their access is the same as some other account.

However, I know they can be complex, so I’m wondering if you have any tips, tricks, hints, or even gotchas for using roles. I’d also be curious if you think there are cases where roles don’t make sense.

Steve Jones

The Voice of the DBA Podcast

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

May 13, 2016
The New Global Dashboard

Redgate recently released SQL Monitor 5.2, which is the latest upgrade to our monitoring/alerting/troubleshooting product for DBAs. This was the big change that the team has spent a lot of time developing and refining. It’s been available for a few weeks as a hidden URL, but with 5.2, this becomes the default main screen for SQL Monitor.

I think it’s a good move forward. In general, I don’t like things moving around physically in applications, as I get used to them being in a certain spot, or I expect them. However in this case, it makes sense.

In a dashboard for monitoring and alerting, you want to know what’s broken. Having a list of 20 servers at the top, and 1 broken one potentially “beneath the fold” (in newspaper parlance) and requiring scrolling would be bad. As a DBA, I’d want to see those items that are problematic. When I look at the monitor.red-gate.com site, I see:

The cluster has a long running query, which is an active, high priority alert. If I were to clear this, along with the other active alerts, this “card” would move to the end, and the sm-cluster2 item would take the top left spot.

Note there are options to configure what is a high or low priority, and even pin specific servers at the top, but the general behavior is to let you know what’s broken now.

There’s one other cool feature in this. If I have a high level alert, like a machine unreachable, and the alert clears itself (the machine reboots), I may see the machine as “green” on the dashboard when I login. This is because current alerts are shown, not historical ones. I can still get the historical data, but the intention is to make this a responsive tool for right now, not last night.

I think this is a great change, and I’m excited to see how well it works in practice as customers roll this out.

May 13, 2016
SQL Nexus
I was lucky enough to be accepted to speak at SQL Nexus in Copenhagen and I attended the event a few weeks ago. This is the new Nordic SQL conference that seems to be replacing the SQL Rally. I’d never been to Denmark, but the trip was easy, and I had no problem getting from the airport to the area downtown where the event was held. Surprisingly (for me) everyone I met spoke English, which was nice, considering I didn’t even know how to pronounce many words, including the train station I was trying to get to.

Being on the water, the speaker dinner was appropriate and quite enjoyable. I’ve had some good ones, but I think this was the best of them all.

The event was held in a movie theater, which I think is very interesting. The spaces were large, theater style room with big screens. Our computers were hooked to the projectors, which worked well. You can see the keynote in the IMAX room below.

The keynote was interesting, from Joseph Sirosh (Microsoft) and Troels Peterson (physicist at the Neils Bohr Institute). The same keynote from Joseph was at SQL Bits, but I don’t think Dr. Peterson went over to the UK. I want to write on Joseph’s a little later, so I’ll just take a few moments and show some highlights of how data is managed at the Cern Hadron collider.

Dr. Peterson works with the ATLAS detector. He had a few nice stats on the hardware.

If you do some math here, you’ll see that when they run the detector and conduct an experiment they produce a lot of data. In fact, this was the next slide:

That’s serious data. It was interesting that he said that’s an unmanageable amount of data. In fact, they need sensors to make decisions on the raw data because they can’t even use computers to analyze this level of information. However, they do have computers. In fact, they have:
- 1127 racks
- 10,070 servers
- 17, 259 processors
- 90, 948 cores
- 75,718 disks
- 113, 852 TiB raw disk
- 312 TiB of memory capacity.
- 120 tape drives
- 52000 tape catridges
- 75 PiB data on tape.
In all their analysis, searching for the secrets of the universe, they’ve learned a lot and gotten better at finding anomalies and problems with data. They know all their data is flawed, so they must use algorithms to try and find the data they can rely on in the entire lake of bits that is captured and stored. They use a lot of machine learning to comb through data.

In fact, he said their research actually showed that there was a reason certain data was altered in line with the phase of the moon. In fact, Dr. Peterson said that they determined that the length of the collider tunnel actually lengthened by 1mm because of the moon.

There were lots of other interesting SQL Server 2016 talks, including the ones you’d expect on machine learning, R, one on IoT (a bit of a wreck of a talk) and a great one showing MitM and other attacks against a SQL Server from a Linux machine.

The event was two days long. I spoke on encryption and security changes in SQL Server 2016 that went well. I’ll do some writing on my demos, showing more Always Encrypted, RLS, Dynamic Data Masking, and more.

The theater was right next to the river, with a nice walkway. A few of us were able to run alongside in the morning. Plenty of people walked or biked along the river each day, and the weather was amazing.

I think this was a really nice conference, at a good cost for those of you in Europe. If you can get to Copenhagen, it might be worth the two (or three days with a pre-con) to try SQL Nexus next year. I’m hoping they do the event again next year and I’d certainly like to go if I can.
May 12, 2016