Voice of the DBA

Tag: administration

Controlling Alerts

None of us likes to be on call for our organizations. Late night phone calls, especially for something silly like adding a new user to a system or rebooting a machine, can make for irritable employees in the morning. Too many nights in a row of problem calls, legitimate or not, and I’d argue that an employee’s effectiveness during the day is substantially diminished.

Since many of us are paid to be creative and improve systems over time, is regular firefighting a good use of our knowledge? This might be even more true if we find ourselves receiving escalation calls on a regular basis and our daily workload doesn’t change.

I ran across a company that had set up a rule for alerts and pages. If any alert was set up in an automated system, it had to have corresponding documentation, with a resolution, in an operations manual. That’s an interesting idea, and certainly promotes the idea of performing root cause analysis and understanding your systems in advance. However does this really reduce any escalation?

Perhaps if there is a team available 24/7 that triages initial alerts this helps, but if there’s a small team of people that must support systems, is it worth the effort to document every issue? I suspect that a better policy is that repeating alerts, those that occur regularly, need to be analyzed and a more permanent solution implemented. I know that’s easy to say and hard to do, but that really is the best policy. At least if you want to ensure your employees are working efficiently over time.

For those calls that are ad hoc, I recommend a log of actions taken be kept in an easily accessible location, in a text format for searching. That way if a different person receives a similar alert, at least they’ll have some idea of what actions the last person performed.

Steve Jones

The Voice of the DBA Podcast

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

May 20, 2015
Getting Database Properties – DatabasePropertyEx()
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had someone ask me a question about security recently, and while working through the answer, I ran across something I didn’t know well: databasepropertyex(). Here’s a few notes.

Each database in SQL Server has all sorts of options and settings. Most people (including me) get in the habit of checking here for the information. First we right click the database (after starting SSMS if it isn’t running)

Then you get this dialog, with lots of stuff.

And more on the other tabs.

Time consuming, and error prone. As I get older, I find that trying to decide which selection is set for which option becomes harder. I find my finger tracing across the screen. It’s entirely possible I’d make a mistake when glancing at this to check the ArithAbort setting.

Use T-SQL

Scripting and querying is usually better. Not the sp_configure scripting where you get a whole list of options, but looking for a particular item. That’s where DatabasePropertyEx() comes in. This lets you query for a database property.

The problem comes in when you run it. If you run the function, you get little information.

Certainly you can go look at BOL to get more data, but that’s annoying. If I run sp_configure, I get data. However here, nothing. Even if I do what I think would be helpful, with a NULL parameter, I don’t get a list of stuff. SQL Prompt alerted me to the fact that the first parameter is the database, and the second is the property, but that doesn’t work.

Fortunately, I have SQL Prompt, so I get this when I put in quotes for the second parameter.

As you can see, the parameters don’t map to the properties, though you can figure out what they mean if you see them. They tend to follow the conventions that most application programmers use (IsAutoClose).

That’s fine, and it just means you need to have a reference handy for properties to query. I wish MS would give all properties with a NULL parameter, or a link to BOL.

Writing

This one took a bit longer to write. Once I realized I didn’t know databasepropertyex() very well, I had to read about it (5 minutes) and experiment a bit. I took some screen shots, which is always cumbersome. As I wrote this, I had to change the wording and ordering a few times to try and convey a simple message. I was originally going to look at more details, but decided to keep this simple and talk about just querying properties.

This was about 30-40 minutes for me.

You can do this. Join the #SQLNewBlogger group and start documenting your career. You can see all my posts that fall into this area by looking through the SQLNewBlogger tag here.

References
- https://msdn.microsoft.com/en-us/library/ms186823.aspx
May 14, 2015
Dig Out the Root Cause

Early in my career in technology, I worked as a system administrator on a Novell network. Periodically we’d have crashes of servers on our network, or application failures, and while we understood that sometimes these were caused by transient errors, we often invoked a root cause analysis process when an issue repeated itself. I’m sure that the environment in which we worked, a nuclear power plant, contributed to the idea that we should always understand why some system failed.

I was reminded of this while reading Grant Fritchey’s “Understand the True Source of Problems” in which he relates a few “best practices” that are likely folklore stemming from ignorance rather than actual good ideas. I’ve seen a few of these rules at various places in the past, often implemented because they appeared to work. However, we need to remember this:

Correlation != causation

Just because you perform some action and observe an effect doesn’t mean that one caused the other. If that were the case, I’d never install new hardware or applications on my computer systems as I’ve had crashes during various installations. However I often back out a change, or retry it and realize that I had a coincidental result, rather than a related one. The same thing has occurred in more complex systems where an action appears to cause an issue, but in reality, the two items are unrelated, or loosely related.

We often don’t take the time to determine the root cause of many issues, which is disappointing. While it often doesn’t seem to be a worthwhile use of resources, I bet that often we’d learn there are actions we are taking (or code we’ve written) that actually is the cause. If we learned from our mistakes and could avoid making the same ones again, we’d greatly improve the quality of our technology systems, with many fewer issues over years.

Unfortunately, “good enough” is often good enough, even if it does result in a bit of downtime.

Steve Jones

The Voice of the DBA Podcast

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

April 28, 2015
The Demo Setup–Attaching Databases with Powershell
I found another use for Powershell, one actually suggested by someone else: attaching specific SQL Server databases.

TL;DR I have a script that detaches all user databases from a SQL Server instance and then reattches certain ones. Full script at the end.

The Issue

We have a lot of demo databases on our demo VMs for Red Gate. Some specific databases are used to show things with different products, but it ends up with us having a few dozen databases on an instance of SQL Server.

That’s not the best way to show things to users, as they can get confused with so many databases. Specifically for us, we have a set of databases for one of our classes, a different set for a second class, and a third set for a third class. We do this because things need to be set in different stages for each class.

One of our sales engineers said it would be great if we could hide some databases when we didn’t need them. I immediately saw a use for Powershell here.

Approach

My approach to this problem would be this.
- detach all user databases
- attach specific databases by specifying the name of the database, and the mdf/ldf/ndf file names.
- use a batch file the user can double click on the desktop to run the Powershell script.
This seemed to make sense, and I started to tackle this on one of my machines in this manner. However because I detached all my databases first, all of a sudden working on things was a pain. As a result, I setup a new VM and created dummy databases there. I first worked on the attach piece, and then the detach part.

Detaching User Databases

This was fairly simple, and I’ve written about it before. In this case, I merely cut and pasted this code into my script.

$srv = New-Object ‘Microsoft.SqlServer.Management.SMO.Server’ $instance

#detach all user databases
$dbnames = $srv.Databases.name

foreach ($dbn in $dbnames) {
    Write-Host $dbn
    if ($dbn -ne "master" -and $dbn -ne "model" -and $dbn -ne "msdb" -and $dbn -ne "tempdb") {
      $srv.DetachDatabase($dbn, $false)

      }
    }

The first line is actually needed for both parts of the script, and we re-use that object later.

The script gets a handle to the databases object and then a collection of all the names. We loop through the collection and if we aren’t looking at one of the four system databases, we call the detachDatabase method.

Note that this means I’m in control of the instance and I know I don’t have a distribution database or anything else that might break. For me, I can safely drop everything other than master/model/msdb/tempdb.

Attaching Databases

I had to search around for some example code. I guess I didn’t have to, but the docs from MS can be tricky to put together, so I searched and found a few examples. Specifically, I ran across this post that described how to attach a single database.

I decided to begin by building up the db name and paths to the files. I started by setting a variable to the path and database name.

$sqldatapath = "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"

$dbn = "sandbox"

One of my databases is “Sandbox” and the path for all my database files is given as the default.

Next I build up the mdf/ldf files. In my case, I don’t have anything other than single mdf file databases.

$mdffiles = $sqldatapath + $dbn + ".mdf"
write-host $mdffiles
$ldffiles = $sqldatapath + $dbn + "_Log.ldf"
write-host $ldffiles

With these, I now can tell what I’m doing. I write the data out to the host, mostly so that if something breaks, the user can determine where. We’re all technical, but it’s nice to know what’s broken.

These are the important bits, but now I need a place to store them. At only one time in the script, I create a new StringCollection object.

$dbfiles = New-Object System.Collections.Specialized.StringCollection

I’ll reuse this object for each database. In this object, I store the database file names. I use the .Add method to get them in here.

$dbfiles.Add($mdffiles)
$dbfiles.Add($ldffiles)

Now I have all my parameters. I can call the AttachDatabase method.

$srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

The documentation says I need an owner, and for simplicity, I use “sa”. I also can specify options, but I don’t care in this case.

This attaches my first database. However, I need to repeat this. I could build some loop and use some array, which is probably better, but for the sake of simplicity here, and preventing issues, I copy and paste this code multiple times. In my case, I have no more than 4 databases, for any environment, so I merely copy/paste this code and change the database name.

However, I don’t want to keep adding to my StringCollection each time. In between each set of databases I need to call, I add this:

$dbfiles.Clear()

Now I have a few simple scripts I can modify easily, and others can understand them.

The Batch File

The other thing I learned with the batch file is that it doesn’t have the same context as my editing session. I had to add a line to load the SQLPS stuff at the beginning for it to work.

Import-Module "sqlps" -DisableNameChecking

I also had to ensure the execution policy is set on each machine, but we tend to do that when we set up the machines.

Simplicity

This is the simple way. It’s really not the best way, and if these scripts change much, this is a problematic way of doing things. I really should have a loop with a list of databases in one place in the script. That way if I add or remove a database, I can easily do it.

That’s an improvement I’ll make.

Let me also say that I have a pattern of database names, and files. If I needed to handle different file locations and varying numbers of files, I think this approach actually works better. Each section of the script can be edited easily, and separately, without worrying about complex logic.

I like simple.

Scripts

The batch script is this.

powershell c:\Utilities\attach_demodbs.ps1

I call the Powershell host and give a fully qualified path to the script.

Here is one of my demo scripts, for two databases: sandbox and EncryptionPrimer:

<#

Attach Demo Databases

This script detaches all user databases and then attaches the following databases

Attaches
– Sandbox
– EncryptionPrimer

#>

Import-Module "sqlps" -DisableNameChecking

$srv = New-Object ‘Microsoft.SqlServer.Management.SMO.Server’ $instance

#detach all user databases
$dbnames = $srv.Databases.name

foreach ($dbn in $dbnames) {
    Write-Host $dbn
    if ($dbn -ne "master" -and $dbn -ne "model" -and $dbn -ne "msdb" -and $dbn -ne "tempdb") {
      $srv.DetachDatabase($dbn, $false)

      }
    }

$dbfiles = New-Object System.Collections.Specialized.StringCollection

$sqldatapath = "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"

$dbn = "sandbox"

write-host "Instance: " $srv.Name
write-host "Attach " $dbn

$mdffiles = $sqldatapath + $dbn + ".mdf"
write-host $mdffiles
$ldffiles = $sqldatapath + $dbn + "_Log.ldf"
write-host $ldffiles

$dbfiles.Add($mdffiles)
$dbfiles.Add($ldffiles)

$srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

$dbfiles.Clear()

#attach staging
$dbn = "EncryptionPrimer"

write-host "Instance: " $srv.Name
write-host "Attach " $dbn

$mdffiles = $sqldatapath + $dbn + ".mdf"
write-host "MDF: " $mdffiles
$ldffiles = $sqldatapath + $dbn + "_Log.ldf"
write-host "LDF: " $ldffiles

$dbfiles.Add($mdffiles)
$dbfiles.Add($ldffiles)

$srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

$dbfiles.Clear()
March 16, 2015