At the 2003 PASS Summit in Seattle the organization had a "SIG Challenge" that involved developing a database solution to a business problem. They assigned people to teams and allowed us to work on the problem only when sessions weren't occurring, but we could start as early as 6:30am and work as late as 11:30pm. My teammates and I took advantage of those long hours. We didn't win, but we became fast friends, and meet up every year (when possible) at the PASS Summit, and even met in Hamburg last December when I went to Germany to visit my daughter.

The important learning takeaway was the opportunity to meet and work with people in other organizations, who probably have similar problems, but may have solved them in different ways. Through PASS I've met many amazing people who have a wide array of experience, and they help me understand that there are many ways to solve a problem - some good, some better, but it's the people you meet that make it worthwhile.

Allen

Over the weekend I received the great news that I've been selected to present a new session, Gather SQL Server Performance Data with PowerShell, at the 2009 PASS Summit in Seattle.

Here's the abstract:

We all know how important it is to keep a baseline of performance metrics that allow us to know when something is wrong and help us to track it down and fix the problem. We don't always know how to do this easily and consistently. This session will walk you through a series of PowerShell scripts you can schedule which will capture the most important data and a set of reports to show you how to use that data to keep your server running smoothly.

I look forward to seeing you there.

Allen

Scripting tasks in PowerShell is a fairly easy way to automate tasks, but you have to know when you get errors and what the real error is. There have been quite a number of blog posts on how to handle errors in PowerShell, like this one by Joel 'Jaykul' Bennett. What many of them don't do is deal with the "nested" errors that .NET throws for SQL errors.

For example, the Database object in SMO provides the CheckTables() method, which runs the DBCC CHECKDB command. The problem is that if an error occurs PowerShell just returns a message like

Exception calling "CheckTables with "1" argument(s): "Check tables failed for Database 'AdventureWorks'."

The important thing to remember is that the Error object captured by the Trap has a property called InnerException, and as long as this has a value, there's more to be found. By iterating through the inner exceptions we can find the real message we need. Here's an example:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

Trap {
  $err = $_.Exception
  while ( $err.InnerException )
    {
    $err = $err.InnerException
    write-output $err.Message
    };
    continue
  }

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'SQLTBWS\INST01'
$db = $s.Databases['AdventureWorks']
$db.CheckTables('Fast')

This script runs DBCC CHECKDB(N'AdventureWorks', REPAIR_FAST), as you can see by watching it using Profiler. The error gets thrown if you don't have AdventureWorks in Single User Mode. The console output of this script then becomes

An exception occurred while executing a Transact-SQL statement or batch.
Repair statement not processed. Database needs to be in single user mode.

That's a whole lot more useful than the first message we received.

Allen

An interesting problem develops when the standard default collation for SQL Server isn't appropriate. Working with clients here in the Czech Republic, I have to reset our lab systems server default collation to Czech_CI_AS, and I found it difficult to find out how to do this via Books Online. Since we have systems running SQL 2000, SQL 2005 and SQL 2008, I have to be able to make these changes in each version, and each version has a different method for accomplishing this.

In SQL 2000, you must run rebuildm.exe in the SQL Server binaries directory, and set the collation in the setup dialog.

In SQL 2005, the following command will reset it:

setup.exe /qb INSTANCENAME=[instance] REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=[password] SQLCOLLATION=[new collation]

In SQL 2008, the command is slightly different:

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=[instance] /SQLSYSADMINACCOUNTS=[admin account] /SAPWD=[password] /SQLCOLLATION=[new collation]

Note that you should do this before loading ANY databases or logins. If you've already done so, detach the databases and script the logins using something like sp_help_revlogin (search the Microsoft KB articles for your version). Then, after rebuilding the master database, you can reattach the databases and reload the login accounts.

Collations can be very tricky, and when you're testing application compatibility it's important that issues like this are correctly handled so your testing is valid.

Allen

As many of you know I run marathons. My next one is on Saturday in Nashville, the Country Music Marathon. Timing sometimes is our best asset, and so it is here in that the Nashville SQL Server Users Group is meeting on Friday at noon.

They were kind enough to ask me to do my presentation on new features and administering SQL Server 2008. In this discussion, I will give an overview of SQL Server 2008 and the wealth of features aimed at the DBA. In this demo-fest of features you will see ways to make administering SQL Server easier and faster such as Centralized Data Management, Performance Data Warehouse, Resource Governor, Backup Compression and more.

I hope to see you there, and then again Saturday morning along the marathon course to cheer me to finish my 24th state, in pursuit of my goal of running a marathon in all 50 states.

Allen

I've been teaching a class called SQL Server 2008 for the Oracle DBA in a number of cities in the US and Canada. I helped build the class last summer and it's truly enjoyable to bring the technology of Microsoft SQL Server to people whose careers have kept them focused on Oracle.

What scares me is the number of DBAs with decades of experience who've never heard of SQL Injection attacks. In one class just two of twelve DBAs had ever heard of SQL Injection.

The important thing to remember is that SQL Injection attacks aren't just limited to Microsoft SQL Server - they're just as prevalent on other database platforms, including Oracle, DB2, MySQL, ProgreSQL, etc. It's also important to understand that it's not just externally facing applications that are of concern. Your company's systems are probably more likely to be hacked by someone inside your organization than from the outside. (Disgruntled employees are disgruntled, to use a current Farkism.)

Here are a couple of sites to learn more about SQL Injection:

SQL Injection - SQL Server Books Online
SQL Injection - Wikipedia

I'm sure a quick look using your favorite search engine will return a long list of sites explaining the issue and ways to prevent it. For your own sake, please understand this problem and help your organization build protections against it.

Allen

I love the fact that you can get information about your SQL Server installations from SMO, and then you can use that information in scripts. This allows you to create one script for some function, like to create a database, for example, and run it on any server and you know it'll just run.

There are two places where SMO stores database file default location. One is for the system database and log files, and it's in the Information collection of the Server object. The properties are MasterDBPath and MasterDBLogPath, respectively. The other is in the Settings collection of the Server object, and the properties are DefaultFile and DefaultLog, respectively.

Here's the problem: when the default location for the user database files is the same as the system database files, the DefaultFile and DefaultLog properties are never initialized. If you change them (using Management Studio or via the registry values) then the properties will be populated, but if you don't the values will be empty.

Using a PowerShell script to get this information, we can accommodate this issue by script like this:

	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null

	$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'SQLTBWS\INST01'
	$fileloc = $s.Settings.DefaultFile
	$logloc = $s.Settings.DefaultLog
	if ($fileloc.Length = 0) {
	    $fileloc = $s.Information.MasterDBPath
	    }
	if ($logloc.Length = 0) {
	    $logloc = $s.Information.MasterDBLogPath
	    }

At this point the $fileloc variable contains a string object with the name of the default file location, and the $logloc variable contains a string object with the name of the default log file location.

The SQL Server dev team is going to make a change in SMO so that DefaultFile and DefaultLog are properly populated, but until that's available this code will successfully return the locations you need in your scripts.

Allen

It can be tedious to keep a list of the version, edition or other information on each of the servers you manage. SMO provides an easy way to return this information, and PowerShell provides a quick way to get to SMO.

Sometimes you may not know if a server is online, or reachable. WMI has a class called Win32_PingStatus which sends a signal to the server to see if it's responding, and if so, returns a 0 in the StatusCode property of one of the result objects.

The first thing we need to do is to define in a file the list of SQL Servers we're interested in. There are most likely very many instances of SQL Server on your network that you don't care about. (Lots of applications using SQL Server Express edition, for example.) We'll put the servers we do care about in a file called servers.txt, and it looks like this.

SQLTBWS\INST01
SQLTBWS\INST02
SQLTBWS

PowerShell provides a cmdlet called Get-Content which will read this file and place its contents in a collection we'll iterate through to return our server information. Once we load the collection we'll use a ForEach-Object cmdlet to loop through the servers. For each server we'll extract the machine name by using the Split method on the server name and then returning the first part. We'll use that to ping the server to see if it's responding. If it is, then we can connect using SMO to return the server information we're interested in.

Here's the script:

#serverinfo.ps1
#Pings a list of servers contained in the text file servers.txt and if
#the server responds, returns SQL Server information from each server
#
# Change log:
# February 1, 2009: Allen White
#   Initial Version

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$servers = Get-Content 'servers.txt'

ForEach ($server in $servers) {
	$nm = $server.Split("\")
	$machine = $nm[0]
	# Ping the machine to see if it's on the network
	$results = Get-WMIObject -query "select StatusCode
		from Win32_PingStatus where Address = '$machine'"
	$responds = $false
	ForEach ($result in $results) {
		# If the machine responds break out of the result loop and indicate success
		if ($result.statuscode -eq 0) {
			$responds = $true
			break
		}
	}

	If ($responds) {
		# Gather info from the server because it responds
		$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server
		$s.Information | select $s.Name, Version, Edition | format-table -Autosize
	} else {
		# Let the user know we couldn't connect to the server
		Write-Output "$server does not respond"
	}
}

It should be easy for you to modify this to add information you find useful.

Allen