Voice of the DBA

Author: way0utwest

Azure Labs–Minor Cluster Issues for SQL Server Setup
During the process of building an Azure lab, I ran into a place where my cluster was not completely validated. I think this happened when a node failed and was re-provisioned, but in any case, when I ran SQL Server setup, the program failed cluster validation and would not install.

If you find issues, try the command line to start setup. This is what I did. For the first node, I ran setup from the command line like this:
```
Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster
```
The same GUI popped up, and I was able to set various settings and save my config file.

For the other nodes, I ran this:
```
Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode
```
Pretty simple, the platform installed and worked fine. Whatever cluster issue I had must have been transient, at least for my lab purposes.
July 27, 2017
DevOps–Fixing Poorly Named Constraints
I was building some code the other day and kept getting problems in my deployment for a change. The deployment was having issues, and this came down to this statement.
```
ALTER TABLE EventLogger DROP CONSTRAINT [PK__EventLog__5E548648B043C0BC]
```
The problem was that this was the constraint on one developer’s workstation, but on another laptop, and in QA/Staging/Production, this constraint didn’t exist.

When we deploy to other environments, such as QA and Production, we will always see the wrong constraint, as most deployment mechanisms look at the name of the object, not the function. Every upgrade script will typically try to run the above statement and then run an ALTER TABLE ADD CONSTRAINT later to add the PK back.

If we have the correct name of the constraint in QA, the script will work. However, the name is likely different in each environment, so we need to fix this.

We can find the name of the PK with this script:
```
SELECT 
    A.TABLE_NAME, 
    A.CONSTRAINT_NAME, 
    B.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
       CONSTRAINT_TYPE = 'PRIMARY KEY' 
    AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.TABLE_NAME = ‘EventLogger’
```
If we take the results of this, we can use this to produce a drop script. Here’s one way to do this. We’ll store the name of the constraint in a variable and use the EXEC() statement to execute some dynamic SQL. We then can execute the ADD CONSTRAINT with a new name later in the script.
```
DECLARE @s VARCHAR(200)
SELECT @s = A.CONSTRAINT_NAME
FROM 
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
        CONSTRAINT_TYPE = 'PRIMARY KEY' 
     AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND A.TABLE_NAME = 'EventLogger'

EXEC('alter table EventLogger drop constraint ' + @s)
GO
/*
Other work
*/
ALTER TABLE dbo.EventLogger ADD CONSTRAINT EventLoggerPK PRIMARY KEY (LogId)
```
This is the type of DevOps change that I would release a table at a time, slowly cleaning up the constraint names. This will smooth your process and increase the reliability of your deployments.
July 26, 2017
SSoL: Where are my data and log files?

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.

This is easily documented, and once you start working, you’ll learn this, and it is documented, but after a few weeks when I actually go into a Linux VM, sometimes I forget. Most of the time I connect with SSMS, and it’s just another server.

Data and log files are stored in /var/opt/data. You can see this in SSMS by selecting the properties of the server instance.

If you check the Files tab in a database properties, you’ll also see this:

You can also get this in Linux by starting the file manager as root. In a terminal type:

sudo nautilus

and enter your password. Then browse to the folder (computer/var/opt/mssql/data) as shown here:

July 25, 2017
Advancing Security

One of the topics that is very important to many technology professionals is security. Security also might be one of the least understood aspects of our jobs. It’s an area that requires regular learning, monitoring, reactions, and vigilance. A healthy dose of paranoia makes a good security mindset, but this an easily devolve into greater stress and worry than might be appropriate, or the feeling that one should throw their hands in the air because of all the potential issues, attacks, and vulnerabilities.

I think as individuals our part is to learn to write better code and scripts with security in mind, perhaps implementing best practices, but really the ways we will get better security is when vendors and platforms develop better ways to implement their security for us. They need people that study the issues and build fixes applicable to protect systems. This means we need good interfaces and basic contracts that ensure we can build software on top of platforms, but we will need to outsource this part of our security.

Microsoft is one company that has been working to help us implement better security through defense in depth, through partnerships with other firms, and is making a difference. They’re not perfect, and there are still flaws in their software, but they are addressing and fixing them quicker and quicker. The world is changing, and Microsoft is a very different company than the one that build SQL Server 2008 R2, or even SQL Server 2012. They are maturing and becoming more responsive, and to me, more responsible about their place in the technology industry. They are striving to produce higher quality products, and when there are issues, they look to fix their mistakes quickly.

Many of us that have struggled to believe in this new Microsoft and apply patches in a more timely manner. I’ll admit that I still rarely apply CUs unless I have a need for a fix, but mostly that’s because I don’t want any unexpected issues to crop up when I’m presenting and I don’t have time to test that regularly. Like you, I have multiple versions of SQL Server. I do catch up periodically, and I certainly try to apply Service Packs within a few weeks of release, if not sooner. As much as Windows updates can be annoying, this is more a matter of timing than concern over quality, and I do try to keep up with these.

Will Microsoft and other vendors make mistakes with updates? Sure, sometimes there will be a patch that causes a problem with some, maybe many systems. However, we do need to grow and advance the security of our systems, which will always have vulnerabilities. Therefore, we need good quality updates from vendors like Microsoft, which I do think has happened. However, we also need customers to apply those patches. You can be slow and conservative, but don’t be negligent and try to avoid them completely. That makes the security situation worse for all of us.

Steve Jones

The Voice of the DBA Podcast

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

July 25, 2017