Voice of the DBA

Author: way0utwest

SSoL–Elevating Privileges

I don’t think most of us need to know Linux, but if you end up managing a system, it’s good to have a little idea of how to get around. This is a short series of posts as I remember the skills I used to have back in university.

One of the things that you might find the need to do on a Linux system is elevate your privileges. By default when we connect and work on Linux, we are working as normal users. We do this in Windows, but when we need more privileges, Windows will give us a UAC prompt. Linux doesn’t.

For example, when I want to check for updates of software, I use apt-get. That doesn’t work for my normal user:

Instead I need to use sudo to elevate privileges. You prefix a command with sudo, enter the root password, and you get elevated privileges, as shown here.

If you just enter sudo -i, then you get the shell to switch and all commands execute as root.

Don’t do this. DON’T. Work as a normal user until you need higher privileges. For the most part you don’t.

June 28, 2017
SQL Server is Lagging a Bit

I don’t expect this to last, or I hope it doesn’t, but SQL Server is lagging behind. I ran across a short piece on the SQL:2016 standards, and I know these standards aren’t necessarily an important consideration for most of us when choosing a database. The standards are a compromise from many industry professionals working together. However, they do influence the vendors a bit. The way the list is written makes me wonder if Oracle provides more input and resources, or exercises more influence than other platforms. I hope not.

In any case, I would expect that Microsoft to be able to make change and catch up quickly with their rapid engineering process. At least, they will if they want to, which means, do these things matter do you as a customer? Or maybe, do these things matter to the really large customers that use, or might switch to, SQL Server?

I’m not sure I understand all of the changes to the standards, but row pattern recognition looks like some sort of LIKE-across-a-row feature. I know there are people that might need this, but I worry about the performance implications of doing this. As we move to larger data sets, perhaps those that come from sensors and other large data producers, this might be an important feature. Hopefully the implementation perform well.

The one thing I expect is more JSON support. As much as I saw the value of XML and hated working with it, I see JSON as a better format, and one that developers use extensively. I do hope that SQL Server gets more extensive JSON support, though I expect plenty of people with a native JSON type to just start stuffing blobs in a column and spending lots of CPU cycles trying to de-serialize the values for queries. If this a native JSON type is an intermediate store before moving the data into a better format, I think this makes sense. However, we can’t prevent poor choices from database designers, and I would expect consultants to love this, as I’m sure more JSON creates more issues for them to fix.

I am glad that date/time work gets some attention in the standards, but since I can never remember the actual syntax and values, I don’t really care. I’ll depend on SQL Prompt to get me through changes here, and then try to get all data in yyy-mm-dd format so I am not confused. LISTAGG is interesting, but I expect JSON to be used more. There are other changes, and I’m not sure how valuable they are for most data professionals.

Adding small functions and features, even those that are used rarely, are ways that we can dramatically improve developer productivity. Looking back over a career, I’m glad that I don’t need to write sorting routines anymore. Using an ORDER BY or x.Sort() is a great time saver. There are certainly some poor implementations of some features, but we can often override and write our own implementations if needed. Or find workarounds that will meet our needs.

I do hope to see the T-SQL language grow and expand over time, with regular enhancements that might help us work with data in easier and more efficient ways in future versions.

Steve Jones

The Voice of the DBA Podcast

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

June 28, 2017
Classifying Sensitive Data

Our databases store all kinds of data in them, depending on the purpose of your database. Most of us create tables and store data in response to some requirement or necessity in our organizations. Our decisions should be driven by good design principles, and I’d hope they are, but we do need to find a place to put all the data that our system will receive.

Depending on the sensitivity or personal nature of data, we may have to alter the way we store data (perhaps encrypt it) or alter the security for parts of the system. This is almost an ad hoc, deal with it at that time task. The exceptions might be when I’ve had to conform to a regulatory statute, such as SOX, PCI, HIPAA, etc. In those cases, I’ve often had to ensure the entire database is protected in some way that ensures it meets the requirements of the statute.

The time when I have had to think about individual columns of data is usually when building a development database where potentially sensitive information can’t be transferred to development machines. In that case, because of the effort of changing data, I’ll try to build scripts that change out individual columns and ensure that sensitive data doesn’t get copied. However, the data that may be deemed sensitive for one company, isn’t always classified that way for another.

At least that’s been my experience. I shared some of this with the Redgate Foundry, who is running research into data classification. They’re looking for people to share opinions, but the project has me curious. I’ve always felt intuitively we could classify data in tables, but perhaps that’s too simplistic a way of looking at the problem. I know that legal groups struggle with some this problem with email and file server documents. Classifying the content in different ways is a challenge.

Is it the same in databases? I’m not sure. I don’t know if there complex rules needed or if this is a simple problem that we easily solve and rarely deal with. I’m curious from those of you that deal with highly regulated industries. Is data classification something that you work with often? How do you decide the data classes and does this impact your administration of the database? If you don’t classify the data, do you worry about the sensitivity of the bits in your database? Let us know today.

Steve Jones

The Voice of the DBA Podcast

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

June 27, 2017
Adding Performance Counters back for SQL Server
I had a strange situation the other day, where a number of things went wrong with my instance. First, I lost permissions to detached databases. The SID was listed in the file permissions, but apparently unlinked to an account.

Next, I went to add an alert, and I only had the XTP counters.

The counters are also missing in Performance Monitor. What is interesting is that I show the correct SQLAgent counters for each of my three instances.

A quick search found me this blog on MSDN, where it recommends the following:
```
unlodctr mssqlserver

lodctr perf-mssqlserversqlctr.ini
```
I had a named instance, so for me I entered:
```
unlodctr mssql$sql2016

lodctr perfMSSQL$SQL2016sqlctr.ini
```
from an elevated command prompt. Running the last command again shows the counters loaded.

I also checked my registry, which appeared to be fine:

I next found another blog that noted I might need to resynch WMI, so I ran winmgmt, using the PID from Task Explorer (details tab):

I didn’t see counters at first, but I restarted the instance. Once that was done …

A nice fix, and one I probably won’t forget after this blog.
June 26, 2017