Voice of the DBA

Author: way0utwest

Avoiding Development Taxes

When I first worked in an environment where multiple developers needed to release software, I found too many rules and constraints for smooth development. The process was developed by one person, or for one application, and somehow expanded to encompass all work being done in the organization. It was if we weren’t trying to think about our creative work as developers and DBAs, and instead viewed our systems composed of widgets built in a factory, each one a copy of the others, perhaps just a bit bigger or smaller. We used the same set of rules for the mission critical finance program as we did for the department vacation scheduling application.

While working inside an overreaching command-and-control mentality, the idea of moving faster or releasing software more often was seen as really dangerous. Two decades of Agile methodologies, and the rise of DevOps have started to change this for many companies. Each year the State of DevOps report seems to show more and more organizations finding ways to build better software, often by having their operations people embrace the concepts and processes used by software developers as a way of building and managing their environments.

Databases still lag behind, and as Donovan Brown says in this piece, if you’re not automating the back-end work of code deployments to the database, you’re still “faking” some of your DevOps process. Databases must maintain the state of data, even as transactions take place around code changes. Ensuring that we properly handle those data changes is a challenge. However, by treating the database changes as similarly as possible to application changes, we can minimize risk and learn database development techniques that help us push forward without being reckless.

Tools have gotten better for database development. Microsoft has some tools for SQL Server, and various other vendors such as Redgate have others. Good tools are essential, but each new one adds some complexity to the environment. That’s one reason why having a minimal number of tools and platforms reduces the friction of getting things done. I see this as a big reason SQL Server on Linux might take off. Plenty of companies don’t want Windows in their infrastructure when most of their systems are on Linux. Not that Windows is hard, but consistency makes everything easier for a staff.

This is the same reason why I’d say that it’s worth sticking with a database platform or two and not experimenting with each new type of system that comes out. If your staff knows SQL Server, then adding in MongoDB or Cassandra means there’s a learning curve, or a “tax” as the piece notes, to getting things done. This same tax gets paid with each new vendor, platform, language, or technology you take on.

Database work is hard, and once your staff gets good at building and deploying changes, you want to take advantage of their knowledge. For companies that have solid development and deployment practices, stick with the things that work well. However, if you have staff that turns over regularly and don’t have a mature process, you can reduce the “tax” you pay for database development. There are companies that have paid some of that integration tax, that learning effort, and they’ve got tooling to help you build code better and faster. Just as I wouldn’t want to code my own build server, I don’t want to code my own database deployment tools. I could, but I’d rather spend my time solving problems and use the tooling that someone else has built to make my job easier, and provide a consistent coding experience for my developers.

Steve Jones

The Voice of the DBA Podcast

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

November 21, 2017
Data Bots

One of the challenges for many databases is getting the information out to our users. Early on many of us had to write queries in a customized fashion for our users. Eventually, reporting systems, such as Reporting Services and Crystal Reports, have been used extensively in many organizations to ease the burden or getting information to users, sometimes even allowing self-service where users write reports. Those are inefficient systems, where a lot of man-hours are spent to create something for users.

Certainly some efforts are made to allow users to query data in an easier fashion. English Query in SQL Server didn’t work well, but the version in Power BI seems to do some things well. Perhaps it’s because a user can see some of the data and the domain of data being queried is inherently limited. I expect this to improve as services such as Alexa, Siri, Cortana, and more become embedded into more applications.

The bots are coming. I used to wonder, but the more I see the advances made in machine learning and related technologies, the more I think that we will continue to see bots used as a way to access our data. There is plenty of hype, but certainly the technologies are being used more and more, and increasingly sophisticated ways. Plenty of businesses are experimenting with them, though it seems in many cases the technology is in an early stage.

I haven’t been sure where bots are useful, but I see them more and more in places like Slack where a workflow can be kicked off with a chat operation. This is somewhere between running a script with parameters and using a semi-intelligent agent, but certainly the ease of a bot in places like Slack is very interesting. Operations can be easily conducted, with logging and auditing, and feedback, in a way that a CLI doesn’t quite work as smoothly.

Perhaps it’s because the domain of actions is more limited, I think that ChatOps works better than general purpose bots. I expect that that will change as we find ways to both limit the scope of interactions, perhaps by passing more information along to the bot. I’m certain the context technology for bots will improve, as well the interaction design. In some ways it’s exciting to think about bots being more focused and responsive than today’s customer service representatives. The one thing that I do worry about is the ability to get an empathetic response from a human. A person is more likely to go the extra mile or do a favor than a bot.

Steve Jones

November 20, 2017
One More Trip and Down for the Count

The last month has been a rough one for me, with lots of travel. I was on trips for 4 out of the last 5 weeks, and the week I was home I had a remote presentation to do. Travel can be hard enough, but add in the need to continue to manage the day job as well as prepare and practice for presentations, and it’s a long stretch.

Fortunately, I have a month off. Or nearly so. I have one last trip in December, and then I’m done for the foreseeable future. I have nothing scheduled in Q1 for travel, and I’d like to keep it that way. Apart from trip to visit the Redgate offices and a few customers, I’ll likely take quite a bit of 208 off from speaking at conferences. I’ve done a lot the last couple years and I need a break.

Plus there are other projects with Redgate and SQLServerCentral I’d like to tackle, not the least of which is upgrading our infrastructure from SQL 2008/WS2003.

I’ll still aim to get to a few new events next year, and I do appreciate the invitations. You’re always welcome to ask, but understand I’ll do less. I’d like to visit some new places, and Pittsburgh is tops on my list for now.

It’s been a pleasure seeing and meeting so many of you the last few years. I hope you’ve enjoyed my talks, and I’ll certainly continue to do more in the future, just at a slower pace.

November 17, 2017
Keeping the Single User Connection

A reader was having issues with a script. They ran their code from PoSh (Invoke-Sqlcmd), setting single user and then trying to rename a database and its files. However, occasionally, they’d lose the single user connection to another user. Frustrating, but it wasn’t a PoSh issue.

The ALTER DATABASE commands don’t really specify how SINGLE_USER works. What happens is that if you set single user, the first user gets the connection, whether that’s sysadmin, dbo, or a regular user. This means that when you set a database to single user mode, you need to be one of the users in the database.

The change to single used is blocked, until all other connections disconnect. One way to ensure this happens quickly is to add the WITH ROLLBACK IMMEDIATE clause, which disconnects users.

The original poster’s issue was that while they used the clause, their connection was in the master database. This meant that another user could potentially connect to the database, grab a shared lock, and prevent renaming files.

If your connection is in the user database, then when you issue the ALTER DATABASE WITH ROLLBACK IMMEDIATE and disconnect users, then you have the only connection. This should prevent any issues.

Quick Demo

Let’s see this in a simple way. I’ve got a demo database that I use, and I’ll open three connections. The first will be as a sysadmin here:

and then the other windows I’ll switch the connection to a non-privileged user.

At this point, I have a few connections to my database.

Let me now reset the database option.

Everything works here, let’s check the next session. If I switch to another tab, I’ll run a query. After a second or two, I get this:

SQL Server has disconnected my session. If I try to reconnect (this login has EncryptionDemo as the default), I get this:

If you need to perform maintenance, and want to ensure you have the connection, change context to the database before setting single user mode.

November 16, 2017