Allen White

Best Thing I Learned at PASS

AllenMWhite — Mon, 29 Jun 2009 11:35:00 GMT

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...(read more)

Emergency Procedures

AllenMWhite — Mon, 22 Jun 2009 19:34:00 GMT

In a followup to Aaron Bertrand's recent blog post on Disaster Recovery, I'd like to mention something I always mention when I teach SQL Server classes. One of the best sources of training I received for being a DBA was, in fact, the training I received...(read more)

PASS Summit 2009 - PowerShell and Performance

AllenMWhite — Mon, 15 Jun 2009 11:46:00 GMT

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

PowerShell Whitepaper Published

AllenMWhite — Sat, 13 Jun 2009 16:50:00 GMT

My first whitepaper for Microsoft, Understanding and Using PowerShell Support in SQL Server 2008 has been published. It was a great honor to have been selected to write the paper, and I thank Kalen Delaney for giving me the opportunity. I hope you find it useful.

Allen

Handling Errors in PowerShell

AllenMWhite — Mon, 08 Jun 2009 12:41:00 GMT

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

Scripting Our Daily (Professional) Lives

AllenMWhite — Wed, 03 Jun 2009 16:00:00 GMT

I talk a lot about scripting tasks on our servers to automate our regular processes, but in this case I'm talking about a different kind of script.

When I first started college, I was a Theatre major. Of course, I learned pretty quickly that my success in that field would be unlikely, so I switched to "Data Processing" and have had a good career. (I continued to do community theatre until a couple of years ago, when work constraints limited my availability, but I look forward to getting back to it at some time in the foreseeable future.) Theatre, of course, is mostly scripted, and the script provides a comfortable framework within which to work.

In doing our everyday jobs, though, it seems that a script - a list of tasks necessary to accomplish a particular objective - would be helpful. I know that as a DBA I have to have consistent and reliable backups. What are the steps necessary to make that happen? Yes, I can "script" that. I also know I need to set up my servers in a somewhat consistent manner, so anyone walking into my shop can easily take over and understand where to find all the buttons and knobs. I can script that, too.

Performance monitoring is another area that I need to track. Not only can I script the steps to build the performance monitor database and data collection jobs, but I can "script" the steps needed to get that monitoring process up and functional.

A new IT project requires my services, well, there are always certain tasks they need me to do, and the project lifecycle process has that script.

Now, don't get me wrong. Not everything can be handled via "script". Some problems can't be anticipated, but most of the normal, everyday tasks of our professional lives have a specific set of steps we follow every time, and having that script helps us be consistent in our work.

I'd absolutely love to see some additional "scripts". For example, what do you do when starting an ETL process, or designing a data warehouse? These are all processes that can be scripted.

Maybe we should work towards a central collection of scripts like this to help people who "find themselves assigned" to be responsible for these tasks. Perhaps if we had someplace to go when faced with a set of new responsibilities, we could make good decisions based on industry expertise.

It's just a thought.

Allen

Changing Server Default Collation

AllenMWhite — Fri, 29 May 2009 07:23:00 GMT

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

Tracking Deprecated Features

AllenMWhite — Tue, 26 May 2009 10:03:00 GMT

This week I'm in Brno, Czech Republic leading an App Compat seminar for Microsoft clients here, and one of the things we need to look for in their applications is the use of deprecated features. I was poking around the sys.os_performance_counters DMV and discovered the [sqlinstance]:Deprecated Features object and found that if your application uses a deprecated feature, SQL Server increments the appropriate counter.

For example, on my SQL Server instance INST03, I looked at the value for the use of the sysdatabases backward compatibility view using the following query:

select * from sys.dm_os_performance_counters
where object_name = 'MSSQL$INST03:Deprecated Features'
and instance_name = 'sysdatabases'

When I ran it the first time it returned the value of 2 in the cntr_value column. I then selected from that view, then ran the query again, and it returned 3.

If you're trying to track the use of deprecated features to eliminate them from your application, this is a quick and easy way to find which features (a) are deprecated and (b) your application is using.

Allen

[Nashville] Presentation: Administering SQL Server 2008

AllenMWhite — Thu, 23 Apr 2009 14:26:00 GMT

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

User Group Presentation: Administering SQL Server 2008

AllenMWhite — Tue, 10 Mar 2009 11:32:00 GMT

We are pleased to invite you to the March 12th 2009 meeting of the Ohio North SQL Server Users Group (ONSSUG). The meetings are held on the 2nd Thursday of every month at 5:45 in the Cleveland Microsoft office. The address of the office is below:

Microsoft Corporation
6050 Oak Tree Blvd. Suite 300
Cleveland, OH 44131

Agenda:

Topic: Administering SQL Server 2008 by Allen White

At the March 12th, 2009, Ohio North SQL Server Users Group Meeting, Allen White will present on new features and administering SQL Server 2008. In this discussion, Allen 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.

Registration:

To register, go to www.bennettadelson.com/sql and click on the link "reserve your seat".

Allen

SQL Injection

AllenMWhite — Sat, 21 Feb 2009 19:09:00 GMT

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

SMO Changes from SQL 2005 to SQL 2008

AllenMWhite — Fri, 20 Feb 2009 14:17:00 GMT

A year ago in anticipation of publishing a book on SMO (which never came to be) I wrote a number of chapters demonstrating how to manage specific sets of objects, and I provided examples in VB.Net, C# and PowerShell. As should be expected, I thoroughly tested all of the code samples to make sure they worked.

This year I've been part a couple of book projects, one being the MVP Charity book which will come out this spring, and I just completed the chapter called "Scripting with PowerShell" for Paul Nielsen's SQL Server 2008 Bible. Without thinking much about it I included a couple of scripts that I'd written for the SMO book as examples. Unfortunately, I didn't test them again in my SQL Server 2008 environment.

I've already blogged here about the relocation of objects from the SMO.dll to the SMOExtended.dll.

What burned me most recently was a change in how you load a table object. Under SQL 2005 the following code worked fine:

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

$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") "MyServer\MyInstance"

#Reference the AdventureWorks database.
$db = $s.Databases["AdventureWorks"]

#Connect to the HumanResources.Employee table
$tbhremp = $db.Tables["Employee", "HumanResources"]

The script just wouldn't work using the SQL Server 2008 SMO DLLs. Here's what I ended up doing, and this worked.

#Connect to the HumanResources.Employee table
$tbhremp = $db.Tables | where-object {$_.Name -eq 'Employee' -and $_.Schema -eq 'HumanResources'}

I'm not sure why the first construct wouldn't work, but I couldn't even load the ErrorLog table using the first method. (I used that table for testing because it's in the dbo schema, which my login had as its default, so I didn't need to specify the schema name.)

Needless to say, any scripts you use today which rely on the behavior of the SQL 2005 DLLs need to be thoroughly tested with the SQL 2008 DLLs after they've been installed.

Allen

Finding Your Default File Locations in SMO

AllenMWhite — Thu, 19 Feb 2009 17:31:00 GMT

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

Use PowerShell to Retrieve your Server Inventory

AllenMWhite — Wed, 18 Feb 2009 17:49:00 GMT

I've recently completed a chapter for Paul Nielsen's SQL Server 2008 Bible on Scripting with PowerShell. While writing the chapter I created a short script to do a quick inventory of SQL Server information.

Right after I wrote the chapter I was on a consulting assignment where I needed a much more complete inventory of servers, so I expanded the script. I felt it would be useful to others, so I wrote an article and it's now available.

Let PowerShell do an Inventory of your Servers

Allen

Get a Quick Review of SQL Server Information

AllenMWhite — Sun, 01 Feb 2009 21:22:00 GMT

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