Voice of the DBA

Author: way0utwest

The Ease of Containers

I first heard about containers in 2012. I went to a conference on software delivery and talked with a gentleman that was using them in his Java application, mostly because the Java app had version issues and would leak memory. He could run hundreds of instances of the app on each server and handle a load, allowing each to be built, run, and then get destroyed in minutes.

Since then, I’ve come to appreciate containers as a way to isolate workloads of specific applications. They became very popular a few years ago, and many developers and companies were looking at them. Since then, the hype has cooled, but I find that the used of containers is still growing, and certainly my use is growing as well.

In the last year, I’ve experimented with a few different pieces of software in containers. Rather than try to install a number of dependencies on my system, I’ve downloaded a container, mapped a volume, and been up and running much quicker than I would have been otherwise. My experiments with Jekyll were one of the recent examples.

I also got a demo from someone at Redgate recently that included a container file, allowing me to use VS Code, but develop and run in a container, avoiding dependencies on my machine. I was up and running with this software in minutes. I was truly impressed with the ease of getting started, and the simplicity for me to actually run and debug code.

I still don’t see a lot of database container work in production, or even in development. We get requests from customers, but often it’s a wish, and they aren’t even sure how they’d get started. While I think database development with containers is fantastic, you do need to have a good dataset available that you can use inside the container to keep resource usage low and make this a viable environment.

I still expect the future of database development to be in containers, especially as we start to have more and more applications connecting to multiple data sources. That’s going to take some time, but I still think learning about, and experimenting with, containers is a great skill for you to have. It’s also an impressive topic on which to have a few stories ready for your next interview.

Steve Jones

March 10, 2021
Quick NoLock with SQL Prompt

First, please, please, please, avoid NoLock. You can lose data, or get strange results, as Jason Strate demonstrates (blog | video). Before you read further or try this, read his post and look at Kendra’s video.

I had a customer request an easy way to add NOLOCK to tables in SQL Prompt. This person wanted to be able to highlight a table and make this happen. Fortunately, this is easy in Prompt.

A snippet will allow you do this on demand. I’ll explain how.

First, open the Snippet manager from the SQL Prompt menu in SSMS.

Click “New” to create a new snippet.

When the form appears, fill it out as shown. Feel free to change the snippet code if you want. The $SELECTEDTEXT$ is the key. This allows me to have this snippet available when you highlight a table name.

Save this, and then when you highlight a table, you can have Prompt add nolock by pressing CTRL and then typing your snippet name. It will be in the popup list.

I also have an animated gif to show this:

March 10, 2021
Daily Coping 10 Mar 2021

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to notice how you speak to yourself. Try to use kind words.

I can be hard on myself. If I don’t get something done that I planned, or I skip out on some activity, I get a little annoyed with myself. My long running streak was the result of this attitude, but it can be unhealthy if I don’t have flexibility with myself.

As I get older, and hopefully a little wiser, I to be more understanding of the choices I make and the benefits or costs of each. Skipping the gym isn’t necessarily a bad choice, depending on why. I’m also learning to better listen to the state of my physical and mental health on a regular basis.

As a result, I try not to berate myself, or not to denigrate myself. I’m trying to continue to push myself to be better, but understand that a step backwards at times is OK. Remind myself of the accomplishments and then encourage myself to move forward again tomorrow.

March 10, 2021
Distributed SQL Databases

One of the challenges that SQL Server, and many RDBMSes, face is scaling out. While SQL Server can grow to handle a large workload on one piece of hardware, eventually there reaches a point when a single machine cannot handle the workload.

Microsoft is trying to handle this with some new tricks. We have the Hyperscale editions of Azure SQL Database and Big Data Clusters, on premise. I don’t know that either of these will end up providing us with scale out for our OLTP databases, but they are options.

There are other options. Google has Spanner, which is a distributed SQL database and CockroachDB has attracted some attention. I caught an article that talks about some of the reasons you might look at CockroachDB, which is a cloud system, but one that gives you scale, and perhaps more importantly, lack of lock-in with a cloud vendor.

I don’t know all the ins and outs of a distributed, scalable SQL database, but I do know that scaling one table, with lots of concurrent access, can be tricky. There are improvements in bandwidth and technology that might allow things to scale well, but I can’t help but think that conflict resolution will be a challenge if many people need to write to the same rows.

I also know that often we aren’t writing to the same rows, but to the same table. Distributing that is easier, but when we start to look at lots of tables with referential integrity, I wonder how scalable this is. After all, the data needs to get committed and then written to multiple places for this to work well.

The one advantage in the cloud is throwing hardware at problems like this, often in a more cost effective way than an organization can do in their own data center. Whether this works well remains to be seen, but certainly there are customers and investors that see this as a part of the future.

Steve Jones

March 9, 2021