Windows Internals 6th Edition, and a Bonus Powershell Script

by Ryan 16. June 2012 13:11

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"
	}	
}

Add comment

About Me

Name: Ryan Ries
Location: Texas, USA
Occupation: Systems Engineer 

I am a Windows engineer and Microsoft advocate, but I can run with pretty much any system that uses electricity.  I'm all about getting closer to the cutting edge of technology while using the right tool for the job.

This blog is about exploring IT and documenting the journey.


Blog Posts (or Vids) You Must Read (or See):

Pushing the Limits of Windows by Mark Russinovich
Mysteries of Windows Memory Management by Mark Russinovich
Accelerating Your IT Career by Ned Pyle
Post-Graduate AD Studies by Ned Pyle
MCM: Active Directory Series by PFE Platforms Team
Encodings And Character Sets by David C. Zentgraf
Active Directory Maximum Limits by Microsoft
How Kerberos Works in AD by Microsoft
How Active Directory Replication Topology Works by Microsoft
Hardcore Debugging by Andrew Richards
The NIST Definition of Cloud by NIST


MCITP: Enterprise Administrator

VCP5-DCV

Profile for Ryan Ries at Server Fault, Q&A for system administrators

LOPSA

GitHub: github.com/ryanries

 

I do not discuss my employers on this blog and all opinions expressed are mine and do not reflect the opinions of my employers.