Tuesday, May 26, 2009

Changing database options on multiple SQL Servers and databases

I have 150 odd SQL databases across the country. A lot are SQL 2000 or SQL 2005 that have been upgraded. And a lot of the databases on these have auto_close ON, Auto_Shrink ON and PageVerify set to NONE. The config exception report was 10 pages long! Enter Powershell.

I wrote a powershell function to alter each setting, so 3 powershell functions. I could have written one, but for 150 servers with many databases on each server, I only want to target databases on servers that are not correctly configured. So I do three passes of my server list. On each pass I find one incorrect setting and only fix this setting on databases that have this setting, not a blanket update to all.

The code for these functions is:

Function Set-AutoCloseOff {
Param ([string]$svr, [string]$dbname)
$dbn = get-sqldatabase $svr $dbname
$dbn.DatabaseOptions.AutoClose = $False

Function Set-AutoShrinkOff {
Param ([string]$svr, [string]$dbname)
$dbn = get-sqldatabase $svr $dbname
$dbn.DatabaseOptions.AutoShrink = $False

Function Set-PageVerify {
Param ([string]$svr, [string]$dbname)
$dbn = get-sqldatabase $svr $dbname
$dbn.DatabaseOptions.PageVerify = "TornPageDetection"

These functions changes one setting for one database on one server. Now the trick is to find the incorrectly configured databases on each server and then call the above functions. Code:

. ./SQLPSX/LibrarySMO.ps1
. ./DBConfigFixes/Function_Set-AutoCloseOff.ps1
function Set-AutoClose ($s)
$svr = $s ¶
$dblist = get-sqldatabase $svr ¶
write-host "Checking Databases on Server $s" -foregroundcolor Green ¶
$dbc ={foreach ($dbn in $dblist) {$dbn | select @{name="DatabaseName";Expression= {$dbn.name}}, @{name="AutoClose";Expression = {$dbn.DatabaseOptions.AutoClose}}}} ¶
$dbr = $dbc.invoke() | where {$_.AutoClose -eq $True} | select DatabaseName, AutoClose ¶
if ($dbr -ne $null) {foreach ($dd in $dbr) {write-host "Changing AutoClose for Database $dd.DatabaseName on $s" -foregroundColor "RED"; Set-AutoCloseOff $svr $dd.DatabaseName}} Else {Continue} ¶

I've included carriage return characters in the code window above. If a line wraps when you copy and paste it and there is no hard carriage return, remove the line break.

What the above code does is get a list of databases on a given server and then create another list of databases on that server that are not configured correctly. These are then used in the foreach loop to update the setting using the functions defined above.

Now all I need is a list of servers, and for each server run the above. Thats the easiest bit:

$ss = gc 'servers.txt'
foreach ($s in $ss) {Set-AutoClose $s}


For the SQLPSX/LibrarySMO.ps1 library of SMO functions, search on www.sqlservercentral.com. This library contains the get-sqldatabase function.

I ran the above on my 150 servers in about 8 minutes. 3 passes for 3 different config fixes, 30 minutes. 438 config exceptions fixed. Badaboom!

Footnote: I've fixed the Set-PageVerify function to cater for the difference in behaviour between SQL 2000 and SQL 2005. If you want this function drop me a mail at dkorzennik@hotmail.com

No comments:

Post a Comment