Voice of the DBA

Author: way0utwest

Moving Through Five Years

I wrote the Five Year Plan in mid 2013. In it, I noted there was a prediction that IT departments wouldn’t exist in 5 years, meaning in mid 2018. That’s a year and a half away. Is that a possibility?

I don’t think so. The more I work in the technology world, the more I see a need for humans to help manage the systems and data. The systems are complex, the small details of getting a platform up and running are varied and not standardized across any two companies, and I can’t envision a complete self-service world. As easy as the Azure or AWS consoles can be, the mindset of those platforms still expects a technical person to choose options and provision systems. After all, how many of your non-technical friends understand what geo-redundancy is?

It doesn’t seem that IT departments are really shrinking. As I look through various surveys, employment statistics and predictions, it seems that most all positions in IT are still growing and hiring. The outlook for the next few years is still good and the pay is still rising overall. What does that mean for all the DevOps, self service, and BYOD vendor support that hint at less jobs for many administrators?

I suspect that there are trends at some companies, where mundane, less skilled, easy-to-automate jobs are being replaced by automation. Some companies may even eliminate certain jobs, like the Database Administrator, but they don’t really eliminate people. Those individuals that can learn to handle other work, and become more efficient still keep their jobs, albeit with different titles. Some work may get handled by systems, but much of the work just gets distributed to other staff as a part of their jobs.

I’ve seen this in software development at companies that eliminated testers. Developers and operations staff start to become responsible for different aspects of testing. Each person spends a little time testing, in addition to their other work. Everyone ends up doing a little less of what they used to to, but a little more of something new. This also usually results in a larger development staff to cover the work the testers used to do. Often this means the department remains the same size, some testers become junior developers, and we’ve moved work around. The shared responsibility might actually improve overall quality since the impact of poor code gets noticed by more people.

I think this is what will happen with many operational IT staffs. Perhaps some companies will try to eliminate the IT department, but really just move the staff to different departments, changing the reporting structure, perhaps expand some of the responsibilities of people, but they’ll likely still have the same number of “IT staff”, even if they don’t call it that.

This doesn’t mean that each of us should count on gainful employment at our organization until we retire. Most of us should constantly get better at our jobs, and learn more about technology. I would recommend you learn new skills, but constantly and regularly practice and polish your old ones. Become better at your craft, even as you might choose to grow your career in new ways.

Steve Jones

The Voice of the DBA Podcast

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

February 10, 2017
Opening the PowerShell ISE from Explorer

This is a cool productivity trip, and one that I ran into by accident. I had heard that I could launch a command prompt by typing cmd in the address. That works, and it’s cool. It even works with ConEmu, which is my default command window.

However, while I was looking for other hints, I found that Powershell_ISE also works. That will launch the Powershell_ISE editor with the current folder as the default one in the lower pane. The “open” dialog, is still a the previous location, but I’m not sure how easy that is to change.

In any case, here’s how this works. Browse to a folder in Exporer, such as a GitHub repo I have:

Type “powershell_ise”

Hit Enter, and the ISE appears. Note the path in the lower pane.

That’s it.

Quick, easy, and if you want to get to the ISE quickly to do something, or write some code, this is one way to do from a folder.

February 9, 2017
Why Devops? For Better Security

The ideas of DevOps are a mixture of principles, ideas, recommendations, tools, processes, attitudes, and more. There isn’t any one way to implement a DevOps process, and plenty of people have been working in what many would consider a DevOps environment without calling it that. I really like Donovan Brown’s definition: “DevOps is the union of people, process, and products to enable continuous delivery of value to our end users.”

That sums it up nicely, but what are some of the “value” items that we can deliver to our customers? Today I want to discuss one of these: security.

The historical view of a secure system is one that gets secured, rarely changes, and every change allowed is reviewed to ensure no mistakes are made. That view fits fine in a DevOps software pipeline, well, except the rare part. Does that make a DevOps built application less secure? Let’s turn that around. Is a traditional (waterfall, agile, etc.) application more secure because of the limitations?

I’d argue it’s not. One of the issues with security is that the issues, holes, and vulnerabilities constantly change. What was secure last week might not be secure this week. In traditional applications we find one of two things. Since deployments are relatively rare, security problems often remain un-patched for long periods of time, or they are patched quickly by changes to production systems that are not well tested or evaluated. There are countless tales of changes made to production applications that end up breaking the system and must be removed. The result, a less secure system. This can be especially problematic when dependent software, for example the OpenSSL issues, is not patched because there are so many dependencies that no one is willing to change the system for fear of causing downtime.

In a mature DevOps environment, the system is better understood because the software is regularly built, testing is automated, and there are regular deployments to various downstream environments. Security patches can be incorporated and deployed quickly, enabling the ability of our automated testing process and intermediate environments to look for potential issues. With a regular branching strategy, we can even quickly suspend current development and focus on producing a patch or changing other code to ensure a successful deployment. Because we practice regular deployments, the need for un-tested, cowboy code changes in production is eliminated.

Certainly a DevOps process doesn’t preclude making mistakes. It doesn’t ensure developers or administrators won’t create vulnerabilities (intentional or accidental). DevOps doesn’t prevent mistakes. DevOps does ask us to continually learn and get feedback from our efforts. And it asks that we incorporate that feedback into our process. If we find a problem in how we write code, a test missed, or a problem in deployment, we correct that in our automated process to prevent it happening again. And since every task, every build, every deployment is logged, we can audit everyone’s actions. DevOps certainly encourages more security, though not perfect security. The goal is that a DevOps process gets us a little better security every time we learn something.

Steve Jones

The Voice of the DBA Podcast

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

February 9, 2017
Let NonAdmins Get Logins

I saw a question recently about how to allow some users to see the logins on a server without being a sysadmin or securityadmin. This was in support of a migration effort, so users needed read rights without being able to change anything.

This appears to be a good place to use the WITH EXECUTE AS option for a stored procedure. I decided to try. I have a normal, non privileged user, JoeDBA, with rights to connect to my Sandbox database. I decided to create a proc as a sysadmin user.

Here is my code. Notice the WITH EXECUTE AS option. Since I created this with my sjones, sysadmin, account. This should be able to call into master and get the data.

CREATE PROCEDURE GetLogins_NonSA
WITH EXECUTE AS OWNER
/*
Description:

Changes:
Date       Who         Notes
———- —         —————————————————
1/24/2017 PLATO\Steve Initial proc to get server logins
*/
AS
BEGIN
SELECT name
     , principal_id
     , sid
     , type
     , type_desc
     , is_disabled
     , default_database_name
FROM master.sys.server_principals

RETURN
END
GO

GRANT EXECUTE ON GetLogins_NonSA TO MigrationRole
go
ALTER ROLE MigrationRole ADD MEMBER JoeDBA

Now, I can log in with a low privileged user. By default, a query against sys.server_principals should only return my login. If I query the DMV, I get this:

Now, I can execute the procedure. I’ve scrolled the results a bit, but you can see I view other users.

Certainly I could limit the columns returned, or transform them to provide more data, but this is a good way to give read-only access to a login about other logins.

February 8, 2017