I needed to create an identical index on a bunch of tables within one of my projects (yes, I know this is problematic on multiple levels and I’m working on that too). Rather than sitting around typing this up, I decided to use PowerShell to do the work for me. I’m still very much learning how to do things in PowerShell so this took me almost as long as it would have to type them up, but now I know more than I did.
Having gone through the pain of trying to find a good example on the web that did exactly what I wanted (they’re out there, just hard to find), I decided I’d add this one in so the next person had at least one more source of information.
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Smo") | out-null
Set-Location C:\Scripts
$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'
$database = $server.Databases["MyDB"]
foreach($table in Get-Content "tables.txt")
{
#Set the table to the latest one from the list need to make this more generic
$currenttable = $database.Tables.Item($table.TrimStart("SCH."),$table.substring(0,3))
#create an index, linked to the table
$index = new-object Microsoft.SqlServer.Management.Smo.Index #($currenttable)
$index.name = ("ix_"+$table.Replace(".","_"))
$index.Parent = $currenttable
#create the columns & set their properties
$col = new-object Microsoft.SqlServer.Management.Smo.IndexedColumn
$col2 = new-object Microsoft.SqlServer.Management.Smo.IndexedColumn
$col.Name = "MyFirstColumn"
$col2.Name = "MySecondColumn"
$col.Parent = $index
$col2.Parent = $index
#add the columns to the index
$index.IndexedColumns.Add($col)
$index.IndexedColumns.Add($col2)
#add the index to the table
$currenttable.Indexes.Add($index)
}
Basically the script opens a file and walks through the entries in the file. Make sure you don’t have extra carriage returns or blank lines or it’ll try to find blank tables which will raise an error (error handling is my next task). You have to set the $currenttable using either the Item or use the Where-Object function. The documentation on that is either flat out wrong, or there’s a bug. Once you’ve got the table set, it’s pretty simple. Create an Index object, IndexedColumn objects and add the IndexedColumns to the Index and the Index to the table. You’re done.
I created about 50 indexes in almost no time with the script and it would have taken quite a bit longer to do manually. That’s not counting the time pounding my head against the wall because the documented syntax wouldn’t work.
Easy stuff, totally straight-forward, but if you’re trying to learn PowerShell, things like this seem daunting.
Thanks to @BenchmarkIT, @Laertejuniordba (who has a new article on Simple Talk and will be writing one for SQL Server Standard) and @cmille19, who all contributed to getting me over the hurdle on create the table object. Read everything that Allen White writes and commit it to memory.
Permalink
1 Comment
Because of the data center move, we have a number of different functions that we’re running on totally different sets of servers on different days. None of this is part of our normal maintenance routines, so I’ve been using it as a great opportunity to stretch a little with PowerShell. I’m still learning, a lot.
The latest task was to get the databases of a list of servers backed up. I initially tried it using SMO. It works great. But it’s serial across all the servers as well as across the databases. I’m fine with serial backups on the databases (very, very fine, I saw a parallel backup of all databases once, pretty sparks, like the Fourth of July) but I really wanted all the servers to get backed up in parallel. So I tossed the idea of having PowerShell run the backups, but I did have PowerShell generate the backup scripts for me.
Again, this is simplistic, I know, but I’m really just getting started. I went to a switch instead of the if conditional for more of an excercise than anything. The last script I posted I’ve updated to a switch, but this one should still be an if, but hey, sue me.
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
foreach ($svr in Get-Content "c:\scripts\serverlist.txt")
{
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
$query = "SELECT bmf.physical_device_name ,bs.database_name FROM msdb.dbo.backupmediafamily AS bmf JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id where bs.backup_start_date > GETDATE() - 1 AND bs.[type] = 'D'"
$table = Invoke-Sqlcmd -Query $query -ServerInstance $svr
foreach($rows in $table)
{
$path = "c:\scripts\bu_" + $svr.Replace("\","_") + ".sql"
switch ($srv.Information.VersionMajor)
{
10 $val="backup database [" + $rows["database_name"] + "] to disk = '" + $rows["physical_device_name"] + "' with compression"}
default{$val = "master.dbo.xp_backup_database @database ='[" + $rows["database_name"] + "]', @filename ='" + $rows["physical_device_name"] + "', @threads = 1"}Add-Content -path $path -value $val
}
Add-Content -path $path -value $val
}
}
Permalink
4 Comments
I’m just learning how to use PowerShell. I’ve been trying to spend time with it over the last year, ever since I saw Allen White‘s presentations at the PASS Summit last year. I just haven’t had the time. But recently, my company has been performing a multi-phased data center move. Because of it, I’ve needed to run tests & updates, disable jobs, all kinds of things on different sets of servers on different days. To paraphrase the old Superman cartoons “This looks like a job <voice gets deeper> for PowerShell.”
I’m not even remotely qualified to begin teaching anyone PowerShell. I had problems recently getting an IF conditional statement to work correctly. However, in the interest of sharing, in case you’re thinking about picking it up (do) or you’re actively working on trying to apply it to your SQL Server maintenance routines, let me give you a small piece of advice. There are millions of things you can do with this. Maybe more. But one of the most common that I see is simply getting a list of servers and then looping through the list performing an action on the server. You can do this using some of the built in sql server gadgets, or, because PowerShell is a .NET language, you can take advantage of constructs such as SMO (SQL Server Management Objects). I’ve done both, depending on whether I was trying to manipulate data, running scripts through the PS equivalent of sqlcmd, or manipulating the servers & objects, SMO. This really simple script can help you get going (at least, I’m using it for tons of stuff):
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
foreach ($server in Get-Content "c:\Scripts\ServerList.txt")
{
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$server"
if($srv.Information.VersionMajor -eq "10")
{
#Perform a command on SQL Server 2008
}
elseif($srv.Information.VersionMajor -eq "9")
{
#Perform a command on SQL Server 2005
}
else
{
#Perform a command on SQL Server 2000
}
}
Assuming I typed all that correctly (a little tighter syntax checking would be nice), this will cycle through all the servers included in the text file ServerList.txt.
This isn’t rocket science. You could have figured this out for yourself. Hopefully this saves you five or ten minutes. As I figure out more interesting things (assuming I do), I’ll post them up here.
Permalink
2 Comments
It took a while, but I finally tracked down the information I needed. SCOM has an SDK. In the SDK is a nice little page showing how to test & debug scripts. One of the links on that page leads out here to a great article on the VBScript debugger. That’s what I needed (along with the DLL’s to register). Anyway, I’ve almost got my first monitoring script up & running. I’ll post it on here once I do.
Permalink
2 Comments
Home of the Scary DBA 