Windows Internals 6th Edition, and a Bonus Powershell Script

I started reading Windows Internals, 6th Edition about a week ago. In case you don't know, it was authored by Mark Russinovich, David Solomon and Alex Ionescu. It's been great so far, packed full of ridiculously detailed technical information on how the Windows operating system works at its most fundamental level. And there is no one on the planet who knows more about that very topic than those three guys. Weighing in at about 750 very dense pages - and that's just part 1 - it's not for the faint of heart. But if you do have the fortitude and desire to consume this kind of material, you'll be rewarded with being able to explain to people what the differences between the Kernel and the Executive are, how to examine the Kernel Processor Control Block in Windbg, etc.  Good stuff.

Now, context switch:

I wrote this little Powershell script a few days ago to help me automate some SQL stuff.  I realize that there are already other ways to do distributed SQL queries and so I'm sort of reinventing the wheel here, but hey... now it's in Powershell. Automation-ready and no Management Studio required.

<#
.NOTES
	Name  : Execute-DistributedSQLQuery.ps1
	Author: Ryan Ries
	Email : ryanries09@gmail.com
	Date  : June 07, 2012	

.LINK	
	http://www.myotherpcisacloud.com

.SYNOPSIS
	This script executes a SQL query across multiple SQL servers as defined
	either on the command line or in a file.

.DESCRIPTION
	This script executes a SQL query across multiple SQL servers as defined
	either on the command line or in a file. Use the -Servers parameter to
	define multiple SQL servers on the command line. Alternatively, use the
	-File parameter to specify a text file of SQL servers, one per line.
	Use the NonQuery switch if your SQL statement is not a SELECT-style
	query, but a stored procedure or other operation. If Username and 
	Password is specified, then SQL authentication will be used. Otherwise,
	SSPI will be used. If you want to specify a different database for each
	server, use a ! between the server name and the DB name. (On either
	the command line or in a file.) Otherwise, "master" will be the default
	database and you must specify the desired database name as part of
	your query.
	
	Use Get-Help <script> -Full for examples and more info.

.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -Servers SQLSERVER01,SQLSERVER02 -Query "SELECT * FROM DB.dbo.Inventory"
	Queries the Inventory table in the DB database on both SQLSERVER01 and SQLSERVER02. Uses SSPI authentication.
.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -File servers.txt -Query "SELECT * FROM DB.dbo.Inventory"
	Runs identical queries on each server found in servers.txt.
.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -File svrs.txt -Query "SELECT * FROM Inv" -Username ryan -Password xyz
	By specifying a username and password, the authentication method is changed from SSPI to SQL authentication.
.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -File servers.txt -Query "EXEC Clear_Inventory" -NonQuery
	Use the -NonQuery switch if executing a SQL statement that is not a SELECT query.
.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -Servers SVR01!DB1,SVR02!MgtDB -Query "SELECT * FROM Inv"
	You can specify a separate database on each server by separating the server\instance name and the database
	name with an exclamation mark. This is useful if you want to run an identical query on multiple SQL
	servers with differently-named databases. The exclamation mark syntax works both on the command line
	and in a file.
.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -Servers SVR01,SVR02 -Query "SELECT * FROM DB.dbo.Inv"
	Remember that if no database is specified by using an exclamation mark, the master database
	will be selected by default, so to run a query on a different database on the server, you must
	specify that in your query.
#>
Param([Parameter(Mandatory=$false)][String[]]$Servers,
      [Parameter(Mandatory=$false)][ValidateScript({Test-Path $_ -PathType Leaf})][String]$File,
	  [Parameter(Mandatory=$false)][String]$Username,
	  [Parameter(Mandatory=$false)][String]$Password,
	  [Parameter(Mandatory=$true)] [String]$Query,
	  [Parameter(Mandatory=$false)][Switch]$NonQuery)

If(($Servers -And $File) -Or (!$Servers -And !$File))
{
	Throw "You must specify either -Servers or -File; not both, not neither."
}
If(($Username -And !$Password) -Or (!$Username -And $Password))
{
	Throw "You need to specify both Username and Password if using SQL authentication."
}

If($File) {	$Servers = Get-Content $File }

ForEach ($_ in $Servers)
{
	If($_.Split("!").Count -gt 1)
	{
		If($_.Split("!").Count -gt 2)
		{
			Throw "Error parsing Server.DB name. Did you use too many exclamation marks?"
		}
		$Instance = $_.Split("!")[0]
		$DB = $_.Split("!")[1]
	}
	Else
	{
		$Instance = $_
		$DB = "master"
	}
	
	If($Username)
	{
		$ConnectionString = "server=$Instance;database=$DB;user=$Username;password=$Password"
	}
	Else
	{	
		$ConnectionString = "server=$Instance;database=$DB;Integrated Security=SSPI"
	}	
	
	If($NonQuery)
	{
		$SQLConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
		$SQLConnection.Open()
		$SQLCommand = $SQLConnection.CreateCommand()   
		$SQLCommand.CommandText = $Query
		$rdr = $SQLCommand.ExecuteNonQuery();
		$SQLConnection.Close()
	}
	Else
	{
		$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter ($Query, $ConnectionString)
		$DataTable = New-Object System.Data.DataTable
		$DataAdapter.Fill($DataTable) | Out-Null
		$DataTable | Out-GridView -Title "$Instance      DB: $DB"
	}	
}
Comments are closed