Be Careful Installing Windows Features

Did you know that installing certain Windows features could impact the way that SQL gets installed on your server? Me either.

When performing some installs recently I came across a problem whereby I was not able to change the shared tools location. The option was greyed out. This didn’t make any sense to me. The install was happening on a new server, there had been no previous SQL installs on the machine and so no problems with installed components preventing those kinds of problems.

Digging through the filesystem I found a C:\Program Files\Microsoft SQL Server folder. That didn’t make any sense to me at all, I’d not installed anything.

I could not even find anything that might be using this folder when looking through the installed software and yet I couldn’t delete the folder as there were files in use.

After much head scratching I finally found the problem.

I had installed the Windows System Resource Manager feature to help manage resources, which is very useful when running more than one instance of SQL on a machine, or if you have multiple software installs and want to keep control of your CPU and memory. This feature uses (and so automatically installs) another feature called the Windows Internal Database which it says is a relational data store.

By the looks of things the Windows Internal Database is based around the SQL 2005 engine, this gets installed into C:\Program Files\Microsoft SQL Server.

When performing the new SQL install it went out and identified that SQL components were already installed and forced installation of the shared tools into that same location.

After removing the Windows System Resource Manager and Windows Internal Database features I was able to move the shared tools install to another location.

Be very aware of what features and roles might be install on your machines, it might bite you when you least expect it.

Central Auditing Of SQL Permissions Scripts

Yesterday I gave a presentation for the PASS PowerShell Virtual Chapter on central auditing of SQL permissions with PowerShell and TSQL. For those that attended feel free to download the scripts I used in my presentation. For those that didn’t…well you can still download them but this picture may not apply to you…

Image from whosawesome.com

Don’t Shrink TempDB!

As a general practice don’t shrink any databases. Every once in a while there is a need to do so. I had one of those needs today.

My disk is full

Thanks to a cumulative effect of a misconfigured SQL install, incorrect location of TempDB and the server pagefile combined with a user running a huge query joining 20+ tables with no where clause we had a drive run out of disk space. Unfortunately this caused another process to break which needed to grow the transaction log on another database.

I contacted the business owner immediately explaining how we needed to move TempDB to another drive and restart SQL (in fact I’d already performed the alter statement to move TempDB off to it’s own LUN where it should have been all along). The business owner stated that we could not restart SQL until after the evening so we were left in a situation where certain things were failing.

Why not shrink TempDB?

That was my feeling. TempDB was 18GB larger than it had ever needed to be so I figured I could just shrink the data file (there was just one) and reclaim 50% of the difference to keep things chugging along until the restart. I attempted to perform the shrink on the file and it did nothing. I checked TempDB and nothing was using it, but still the shrink failed.

Interesting problem. Argenis Fernandez (blog|twitter) just joined my team and he came over to try and help. He suggested running DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS given that the user impact would be minimal given the servers usage patterns. 

After running the DBCC commands I was able to perform the shrink, clean up the space and keep everything running.

Corruption – that’s why

Curious as to why running those DBCC commands allowed me to shrink the file I posted to #sqlhelp on Twitter asking why.

Pretty much immediately I got a reply from Paul Randal (blog|twitter) that made my stomach churn:

Paul and Aaron Bertrand (blog|twitter) sent me links to a Microsoft KB article that clearly explains this.

From Paul I bring you the KB in a nutshell: unless you quiesce SQL Server, a TempDB data file shrink can cause corruption.

This post brought to you by yet another reason not to perform a shrink.

Presenting For The PASS PowerShell Virtual Chapter

A quick note, on Wednesday at 1PM EST I'll be giving an online presentation on one of the two topics I submitted for the PASS Summit this year.

 The presentation will be heavy on the demo side of things and I'll cover the basics of querying SQL Server using PowerShell, running scripts against multiple machines quickly and easily as well as bulk loading data into SQL.

Please check out http://www.powershell.sqlpass.org/ for a link to the livemeeting.

Central auditing of SQL permissions with PowerShell & TSQL

Description: As a DBA it can be a challenge to know who has permissions to what SQL instances and what objects. The more instances you have the more complex that task. In this presentation I'll share a method using PowerShell and TSQL that can be used to capture permissions from all of your SQL instances and load them into a centralized location. We'll even take it a step further by auditing those permissions so that we can quickly and easily identify any that might have changed.

SQL Clustering–Network Binding Order Warnings

In setting up my Windows 2008 R2/SQL 2008 R2 cluster this week I came across a warning in the Setup Support Rules stating that “The domain network is not the first bound network.”

This didn’t make any sense to me as I had been very careful in setting the binding order in the network connections properties:

Deciding to look for further information on this I opened up the Detail.txt file from the Setup Bootstrap/Log directory and searched for network binding order:

Strangely the first bound network is Local Area Connect* 9. I had no idea what this network was as it was not listed in the network adapter list.

I remembered a discussion on Twitter a couple of weeks ago between Allan Hirt (blog|twitter) and Denny Cherry (blog|twitter) where they talked about this exact problem and the fact that it was a hidden cluster adapter which needed to be moved in the binding order, and that had to be done in the registry.

I opened up regedit and drilled down to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Linkage and opened up the Bind value:

I’ve highlighted the first item in the bind order. The trouble is that this GUID value really means nothing to us…time to crack open PowerShell

gwmi Win32_NetworkAdapterConfiguration | where-object {$_.IPEnabled -eq "True"} | ft Description, SettingID -auto

As you can see the Microsoft Failover Cluster Virtual Adapter GUID matches the first bound value. Back to the registry to move that adapter down the list with a quick cut and paste.

*Warning, playing in the registry can cause system instability, BSOD problems, singularities and quantum fluctuations*

Starting up the cluster install again and the Network binding order warning is gone

Hopefully the next releases of Windows and SQL won’t have this problem. In the meantime this is good to know. Thanks Allan and Denny for engaging on Twitter and leading me to the answer to the problem.