Voice of the DBA

Author: way0utwest

Pro SQL Server Linux–Moving to Linux

As part of my learning goals for 2018, I wanted to work through various books. This is part of my series on Pro SQL Server on Linux from Bob Ward.

It’s not until Chapter 10 that we get back to Linux specifics. Most of the chapters in the middle are general SQL Server items that are good to know, but not are useful for experienced SQL Serve professionals. They’re worth skimming as you might learn some things you didn’t about the features.

In Chapter 10, the focus is migrating to Linux. Bob presents a number of options, including one I hadn’t heard of: the DEA. This is the Database Experimentation Assistant, and I hadn’t heard of it. I don’t do a lot of migrations as I start and stop on SQL Server, but this allows you to do some A/B testing of upgrades. I’m sure it’s not perfect, but that’s something that is definitely needed.

Most of the chapter is about versions, from Oracle, or from PostgreSQL. The Oracle parts walks through the migration, while the PostgreSQL part tries to show where features in SQL Server exist or don’t in PostgreSQL. In a number of places, Bob found projects that provide some functionality in PostgreSQL that is in SQL Server, but didn’t test things.

The last chapter of the book deals with containers, and that was one of the more interesting things for me to read. I’ve been on a bit of a container kick, digging in more and more, so I was curious to see how Bob described and talked about the technology. I think he did a good job, but I still think a couple courses I’ve watched on Pluralsight from Nigel Poulton really covered this well, so perhaps I glossed over this a bit.

I liked that Bob includes lots of code and links to help you get started, so if you read this book, spend time on containers. They’re the future, and for now, Linux containers rule.

Overall Review

I learned a few things about SQL Server from the book, but far too much of it was basics and beginning SQL Server stuff, not Linux stuff. It was a good refresher, and I’m not disappointed in the time I spent, but I was hoping for more stories and more Linux.

The lesson really is that SQL Server is SQL Server, with few differences on the platform. As Bob mentions, you shouldn’t care about the platform and just pick what works.

The basics of SQL Server did make this hard to read, and as a result, I started strong at the end of December and hit a lull for almost two weeks where I wasn’t motivated to skim through basic T-SQL, security, etc. I realized around the 20th that I was running out of time and crammed a bit in.

If you don’t have a lot of experience with SQL Server, this is good. If you know SQL Server, but want to know Linux differences, I might recommend digging into some of the more specific Linux blogs, or the SQLServerCentral Stairway series.

February 8, 2019
Forgiveness

We are frail, fragile creatures. I think this is one of the main reasons I push for and try to work in an agile, DevOps style of development. I know that I make mistakes, as do others. My goal is often to ensure our system, our process, our way of working can transcend those mistakes and we can still be effective. We can use automation and regular examination of what works and what doesn’t to ensure our system works well and isn’t overly dependent on one person.

Part of dealing with mistakes and failures is learning from them. Not only do we need to learn ourselves, and avoid repeating mistakes, but we also need to learn to forgive others. I thought about this as I was reading Derik Hammer’s post on Active Forgiveness. In his post, he covers the situations where we acknowledge mistakes without a punishment. Instead, we forgive the act and go to work on finding ways to prevent the same situation from happening in the future.

I think this is one of those concepts and practices that are very difficult for most leaders, and maybe more followers. We don’t want to excuse the behavior, and we certainly don’t want to tolerate this in the future. What we want is for this person, and others, to learn from the mistake and work to prevent it in the future. What often happens is that we blame the person, we’re glad we’re not the person, and we may not learn a lesson because we think that we wouldn’t make the same mistake.

Throughout my career I’ve seen the same mistakes occur over and over. Sometimes the same person makes a mistake again, but often different people make the same mistake because there wasn’t a product retrospective where everyone learned for the future. Having a blameless meeting where discussions take place, mistakes aren’t minimized, and everyone learns is hard, but it is important if we are going to improve our teams performance over time.

I’d encourage you to try to find ways to learn from failures and grow. Forgive others actively, but don’t minimize the mistake. Hopefully you can also help your management learn to do this.

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.

February 7, 2019
Versions of Stored Procedures
I never knew this, but stored procedures have versions.

I was browsing the CREATE PROCEDURE doc page, and stumbled upon this item. In the beginning part of a stored procedure definition, after the name, there is a section that starts with

; number

Hmmm, I read the entry and it says the number is an integer that is used to group procedures of the same name. There is a note this is a deprecated feature that may be dropped, should not be used for new work, and old work ought to be modified, but it was interesting, so I tried it.

First, create a proc:
```
CREATE PROCEDURE dbo.GetOne

AS 

SELECT 1

GO
```
Once this is done, we can execute it and it works. Now, let’s modify this. I’ll use this code:
```
CREATE PROCEDURE GetOne;2

AS 

SELECT 'One'

GO
```
You can see these will have different results. If I execute them, I do so with the name, including the optional integer.

In SSMS, there is only one object listed.

If I drop the procedure, both are gone.

I’m not completely sure where I’d use this feature, and I can see not investing in it, but I found this fascinating. All these years of writing stored procedures and I learned something new this week.
February 6, 2019
Complex Constrained Security

I was reading about Kerberos and authentication with SPNs recently. It’s a topic that seems to make sense and appears orderly, but when I’ve had issues with SPNs, it feels like voodoo and black magic sometimes might be needed to get things working. As I read through the document, trying to ensure I would learn a bit more about how impersonation and delegation work, I noticed this sentence:

“As a security best practice, Microsoft recommends constrained delegation over unconstrained delegation.”

That seems reasonable to me. We ought to limit where users can connect to specific systems to ensure good security. This makes perfect sense where we have systems like web servers or application servers and we should limit delegation to specific databases servers. This wouldn’t prevent all security breaches, but it would limit the scope of many.

The complexity comes when we start to have multiple servers that might connect to multiple back ends, especially as we grow our architectures to include additional HA nodes with Availabilty Groups. Tightly linking security complicates the configuration and requires that our sysadmins setup new machines and properly add new delegation targets as machines change. DevOps and configuration as code can help here with ensuring that we always add the required security changes to the right machines.

That still doesn’t make it easy to manage a tight security environment without lots of resources. As we rotate or retire machines, we need cleanup of the security settings that refer to these objects. If we rotate host machines, which is usually rare, we need to remember to update out configuration scripts to work with new machines and accounts. If we add nodes, we need additional lines in scripts. If we move to containers for database servers, this might require even more changes.

None of these items is complex, but when you must repeat them for many systems, many accounts, and on a semi-rare basis, they add some overhead that is both tedious and difficult to keep up with for a staff. This is especially true as staff turns over. Do you want to let the new people know that they need to make all these updates while handling their “normal work”? I could see all these details becoming a chore because we’re human, we’re flawed, and we make mistakes.

I like the idea of tighter security, but at a scale, at random times, in between all the other tasks we must complete, the tools and techniques we have don’t make this something that seems manageable. I don’t have solutions, but I think that we do need some better tools that ensure security can be both flexible and convenient, while enforcing the principle of least privilege. The management of systems at scale is helping (forcing?) companies rethink some security tools and features, but there is still work to be done to ensure our employees will correctly and consistently configure security.

Steve Jones

The Voice of the DBA Podcast

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

February 6, 2019