What’s Your Test Plan?

I ran across a post on upgrading a SQL Server instance, where the original poster (OP) was asking about a document to upgrade from SQL Server 2008 to 2014. That’s a big ask, especially as not many documents tend to be written to go across three versions. The official ones, or the people that document well, tend to upgrade every version, and so they have a document to go from 2008 to R2, and from R2 to 2012, etc. However, given the pace of Microsoft releasing things, perhaps we ought to build a document and checklist across every 2-3 versions since many people may be upgrading from 2005/2008 to 2014 (or soon, 2016).

Apart from having the document, one of the questions was a list of what to test. That struck me, as I’m big on testing, and have tried to keep comprehensive plans when I had important systems. However, many of you might be like me and don’t consider most systems to be “important”. For those systems, a patch, a Service Pack, an application upgrade was really tested by applying the change to a test server and asking users to see if they could use the application. I’m not confident that there was any sort of comprehensive look at the system in these cases, but this system worked most of the time.

There were some instances that we deemed important, usually measured as such because a failure would mean some high level manager would call my manager’s boss and smelly things would slide in my direction. In those cases, we had a list of the functions and processes that needed to work. These could be application functions, queries, ETL packages, reports, or anything that would cause a user to complain. This list became our test plan, and it was kept up to date. Usually back dated, since we weren’t sure what new things were important until they failed for some reason, but once we received a ticket on an item, we added it to our list. We went through the entire list for upgrades, ensuring each item worked.

I’m wondering, do many of you have a test plan for your systems? Any system? It doesn’t matter if it’s automated or manual, but if you had to patch/upgrade instance X, are there a list of things you’d verify? Or is the system not that important, so you’d just make sure the database service was running? Let us know what your test plans look like.

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.

The Complexity of Branches

Branching code is hard.  Well, branching isn’t hard. Just right click in some GUI or or type “git branch” and off you go. The actual branching of code is pretty simple and painless. In some sense, branching might be too simple in today’s tools as developers are almost encouraged to branch in a VCS and “test something” quickly without a lot of thought.

Even merging has become easier, with tools to visual code changes, and automatic merging in many cases. Despite the hassles of merging, it often isn’t too difficult, which can mask the complexity of branching.

However branching can easily devolve into a nightmare of multiple versions of code, with various developers unsure of the impact of their changes on a merged code base. This is especially true in a large code base where many developers each have their own branch where they work. With pressure from multiple individuals to patch bugs, add features, and streamline performance, I’ve met developers that end up building changes in three or four branches at the same time.

Merging all the work together, into the correct main branch, becomes tricky.  We’re all human, and I’ve seen developers merge a change into the wrong branch. Tools try to limit this, but it still happens. The more branches that are in use, the more time that ends up getting spent on the administrative action of reconciling code differences rather than building new code (or fixing old code).

Understanding our code is hard enough, without adding the additional complexity of versioning multiple files and objects. My philosophy has always been to limit branches as much as possible, usually only allowing one, or at most, two, per team. We have also endeavored to merge back changes every few days, just to limit the amount of time spent performing merge reconciliations. I’d rather have developers have the pain of merging daily on a shared branch than dealing with branch merges less frequently (usually more complex merges).

We’ll always have mistakes made, but using automated testing and continuous integration builds on all branches can help limit the number of obvious mistakes that break code. This won’t prevent bugs, but our tests can help reduce the number of bugs, especially regression bugs, over time. At least, they can if we use testing on all branches, all the time.

Steve Jones

The Voice of the DBA Podcast

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

Half Baked Features

I gave a talk recently on some of the data protection features being added in SQL Server 2016, along with a few that have been available for some time. I think the talk went well, but I point out lots of potential issues with the features themselves. I had a question from someone that noted Always Encrypted, Dynamic Data Masking and more aren’t really full developed, are they?

The question threw me, not because I agree, but because I think that there is a complex answer. Encryption and data security features are easy to use. The features really are easy to implement, especially encryption. Most encryption is just another function call for the developer, which is something almost all of us can write. The mechanics of using these features are fairly easy.

However the architecture, the decisions on how to manage keys and where to deploy features, those are hard. We deal with those relatively infrequently, but when we must make those decisions, we should carefully consider the ways in which our systems might be attacked or mis-used.

Some of the restrictions that I see in various features are unavoidable. At least, I can’t see a way to avoid them. In some sense, things like a binary collation are almost required because of the nature of how encryption must operate on data. Perhaps there are ways to mitigate issues, but I’m not sure. No security mechanism is perfect and all encryption can be broken. However I think the way these features work is good enough in many situations.

There are some things, such as allowing Dynamic Data Masking on Always Encrypted columns, which can be implemented. However, the changes are more complex, and involve not just SQL Server changes, but probably also ADO.NET changes. Making the decision on how to actually implement these changes shouldn’t be taken lightly, and I’m happy to have a working feature that might be enhanced in later versions over not getting any options at all in this version.

Steve Jones

The Voice of the DBA Podcast

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

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.

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.


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.

Solving Tempdb Issues

While reading Remus Rusanu’s piece on SQL Server 2016 development, there was an item that struck me. There’s a part of the piece where he notes that an engineer at Microsoft realized that there was a tempdb issue with Azure SQL Database. To solve it, a failover was needed, which could be problematic. The basic issue was that tempdb files were sized unequally, discovered after “months of investigation.”

Now, on one hand this seems silly. After all, we’ve known since SQL Server 2005 that the guidance was for all files to be sized the same. Shouldn’t engineers at Microsoft be following all the practices  known for optimizing SQL Server performance? I think all Microsoft people should follow this, especially those working on other products (*cough* Sharepoint *cough*), but at the very least SQL Server engineers should have a huge list, perhaps with PBM or unit tests, that warn about non-compliance.

On the other hand, since we’ve known this is an issue since SQL Server 2005, why does SQL Server allow this? I’d think some simple tooling and a few checks in code could eliminate this as an issue in any new install. Catch the issue during upgrades and grow (or shrink) files to be the same size, or at least warn the administrator. I know there are situations where you need to add a new file in an emergency, but shouldn’t this be an item we push administrators to correct quickly after the emergency is past? Or at least force them to consciously make an exception.

There are plenty of limitations and restrictions in SQL Server systems that Microsoft forces on us. We have limits on where new features can be used, various historical limits on function parameters, and more. Why not also just enforce limits that prevent issues like this? I’m sure people will complain, but wouldn’t those complaints be outweighed by less issues overall from all customers?

Steve Jones

The Voice of the DBA Podcast

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

Use Tools

This editorial was originally published on May 23, 2012. It is being re-run as Steve is out of town.

As someone that works with Information Technology, I usually work on applications designed to make work easier. For example, we have accounting systems that ease the handling of debits and credits, they detect mistakes in data entry, and in general require less people to handle the accounting needs of many firms. Do we have less accountants?

I suppose we might have less, but it seems that the finance departments, handling AP and AR in many companies is as large as I remember from my younger days. Perhaps the department is smaller than it might otherwise be, but it’s usually not small.

In IT, we have all kinds of tools available to us that can help with our jobs. What’s more, we can create new tools as needed to do our jobs. Powershell or Perl scripting, Codeplex projects, and more are available to most of us to help us better manage systems. In SQL Server, we even have a built in framework, Policy Based Management, that helps us prevent changes or problematic configurations. If you find a third party tool that can help, you can make a case for its purchase, using the time savings translated to actual costs. One of those tools might really make your job more enjoyable..

In theory, we ought to be able to manage many more systems per person than we used to. Does that many that we need less people? Perhaps, but it seems we grow systems fast enough that we still need to hire more staff in many environments.

I so often see people working in IT fail to take advantage of all the tools we have to automate much of their jobs. They often tell me if they automate too much of their jobs, they won’t be needed and may get let go. Personally I think that’s an excuse not to exercise your skills, challenge your mind, and get rid of tedious work There’s no shortage of work to be done in most companies. If that’s true, why not use your tools to get rid of the tedious work and spend your time on something more interesting?

Steve Jones

A Lifetime of Software

This editorial was originally published on April 27, 2012. It is being re-run as Steve is out of town.

I’ve been working with computers and software for most of my life, but it’s been a career for a couple of decades now. I don’t do as much technical work as I used to, mostly testing and experimenting, but my job is related to SQL Server and software, and I anticipate that’s what I’ll be doing for the next two decades.

However that’s not necessarily the plan for everyone that works in the technology business. I know plenty of people that would like to move into management, or even move into some other career field if they can afford to do so. In the responses to many editorials in the past, I read that quite a few people think the technology business isn’t a great choice and wouldn’t encourage their children to enter this field.

I disagree, and think that this business has been very good to me, overall I’ve enjoyed it, and I think it would make a good career  for any of my kids. However this Friday I wanted to ask the rest of you what you think.

Do you expect to work in the technology field until you retire?

I know many of you will change your focus in technology, perhaps moving to develop from DBA, vice versa, or moving into another field. I know most of you will change companies before you retire, but I’m curious about whether you think you will remain a technology worker for the rest of the time you work.

Steve Jones

Work To Live

This editorial was originally published on May 2, 2012. It is being re-run as Steve is at SQL Bits.

I’m halfway through my career, having spent about 20 years since college working in technology with another twenty to go. The rest of the community is probably spread out around me, some older, some younger. It doesn’t matter what part of your life you are in, there are a few things about work that I think always apply. The first is that we work to live, and don’t live to work. The second is I like to hope for the best, but plan for the worst.

It’s all too common for people in this business to overwork themselves, work very long hours, and neglect the rest of their life. I have rarely seen someone that is retired or late in life complain that they didn’t work enough over their lifetime, and I’d caution you to keep that in mind as you go through life. There will always be more work, more things to do, and more requests from clients and customers. Keep a balance in your life, and remember to enjoy it along the way.

The second part of my advice can apply in many parts of our career, but it ought to apply to our finances and our retirement. Unfortunately, I’ve seen a few articles like this one that notes many people have not funded their retirements well. We all have expenses, and it’s easy to delay the investments in our life until later, but that is a bad idea. Whether it’s your IRA/401K or your health, you’d be better off sacrificing a little today for a little more security later.

In the US, many employers match contributions and you ought to take advantage of that. There are all sorts of options for retirement, like ROTH IRAs, that it seems many people aren’t aware of. I also meet many people that don’t realize they can borrow against their 401K as well, taking a loan whose interest goes back to your own account. There are few excuses to not plan for the future. Enjoy today, but plan to live a long time and to enjoy your retirement.

How much money you might need depends on your lifestyle, and that’s something you should spend time thinking about and talking with your family about. Many of us may not be able to retire at 65, or count on social security, but I’d hate to have to work longer than I want to because I cannot afford to retire.

Steve Jones


Promoting Engineers

This editorial was originally published on May 1, 2012. It is being re-run as Steve is at SQL Bits.

Never, ever promote your best salesperson.

That’s the advice that Scott Horowitz got from his father. He talks a little about how this relates to technical people who find themselves looking at management to further their careers, or are sometimes even get promoted just because they’re the strongest technical employee in a group. Mr. Horowitz sees the transition as one that rarely works well, though he has some good advice on how you can increase the likelihood of success.

In my career I’ve moved to management a few times and back again to a technical role, though never at the same company. I am sure it would be a strange move to be in charge and then move back to a contributing employee, but I do know some other people that have done it successfully in their careers. I have enjoyed both roles, and at different times in my career, the different role has fit me better.

Many companies struggle with their technical people as they advance in their careers. Few companies think ahead and build dual career tracks in both the technical and managerial areas for their staff. As a result, many technical people find their career stalled in a company and may look to leave for another position. Setting aside a technical track that allows an employee to advance as a strong technical worker, and continue to grow their challenges and salary seems like a no-brainer, but it’s something rarely implemented outside of technology-oriented companies.

Perhaps senior technical people can’t continue to add the same value as a manager or director in an organization. There are definitely lots of technical people that have a lot of seniority in a company, but do not necessarily bring a lot of additional value for all their experience. However some do, and those are often the people that a company should not let go. If they continue to grow their knowledge, and add more value, there ought to be a career advancement track for them in the technical area.

Steve Jones