sqlpowerdoc

SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations.

NOTE: Running a SQL Server Inventory will also perform a Windows Inventory!

Requirements

Before you can document your SQL Server environment with SQL Power Doc you'll to meet the following requirements:

Permissions
	- SQL Power Doc makes connections to standalone SQL Server instances using either Windows Authentication or with a SQL Server username and password. Whichever way you connect, the login will need to be a member of the sysadmin server role on all standalone SQL Server instances you're documenting. 
	
	- For Windows Azure SQL Database (WASD) a SQL username and password is the only way you can connect. This login should be the WASD Administrator login. 
	
	- SQL Power Doc also tries to collect information about the Operating System that SQL Server in installed on. The account used to run SQL Power Doc will need Administrator rights to the OS in order to do this part.

Windows Machine To Perform Inventory
	- This can be virtual or physical - either way it's recommended that its located on the same physical network as the servers you are collecting information from. SQL Power Doc collects a lot of information and you don't want network latency to become a bottleneck
		
		-- You can ignore this requirement if you're documenting a Windows Azure SQL Database since it's not likely that you'll have physical access to the hardware these databases run on!

	- Just like SQL Server, PowerShell likes memory; For documenting a 10-20 server environment you'll want at least 2 GB of RAM available. Logical CPU count isn't as important, but SQL Power Doc can split its workload across multiple CPUs when doing it's thing so the more CPUs you've got the faster the job will get done. 

	- The following software needs to be installed: 
		-- Windows PowerShell 2.0 or higher:
			Windows PowerShell 2.0 is available on on all Windows Operating Systems going back to Windows Server 2003 and Windows XP. Chances are you've already got it installed and enabled, but in case you're not sure head over to  http://support.microsoft.com/kb/968929 for a list of requirements and instructions on how to get PowerShell working on your system.

		-- SQL Server Management Objects (SMO):
			If SQL Server Management Studio is installed on this machine then it's already got SMO. 
			
			You don't need the absolute latest version installed, but it's a good idea to make sure that the version you do have installed at least matches the highest version of SQL Server that will be included in your inventory 
			
			SMO is part of the SQL 2012 Feature Pack and can be downloaded for free from  http://www.microsoft.com/en-us/download/details.aspx?id=29065 (Note - SMO requires the System CLR Types which are on the same download page)

	- Sometimes firewall and group policy restrictions will prevent SQL Power Doc from gathering information from servers. If that's your environment then you'll want to make sure to open up communications for both SQL Server and WMI (Windows Management Instrumentation). Start at  http://msdn.microsoft.com/en-us/library/windows/desktop/aa822854(v=vs.85).aspx for instructions on how to do so.

Windows Machine To Create The Documentation
	- Usually this will be your laptop or desktop 
	
	- You'll need the following software installed: 
		-- Windows PowerShell 2.0 or higher 
		-- Microsoft Excel 2007 or higher
	
	- You do not need SMO or access to SQL Server for this step

You can use the same machine to collect an inventory and create the documentation as long as it meets all the requirements outlined above. This will be the case if you're documenting one or more Windows Azure SQL Databases.

Configure Windows PowerShell

You'll want to make sure that PowerShell is configured properly on both the machine that you're using to perform the inventory and the machine that's building the documentation. (Repeat: Do this on both machines!)

Set Execution Policy

By default PowerShell tries to keep you from shooting yourself in the foot by not letting you run scripts that you download from the internet. In PowerShell lingo this is referred to as the Execution Policy (see http://technet.microsoft.com/en-us/library/hh847748.aspx) and in order to use SQL Power Doc you'll need to change it by following these steps:

Open a PowerShell console in elevated mode: Start -> All Programs -> Accessories -> Windows PowerShell -> Windows PowerShell (right click, choose "Run as Administrator")
Set the execution policy to allow for remotely signed scripts Set-ExecutionPolicy RemoteSigned -Force
Exit the PowerShell console

Configure Windows PowerShell Directory

Now you'll need to create a directory to hold PowerShell code.

Open a new PowerShell console (but not in elevated mode as when you set the execution policy): Start -> All Programs -> Accessories -> Windows PowerShell -> Windows PowerShell
Create PowerShell and PowerShell modules directory in your "My Documents" folder New-Item -type directory -path "$([Environment]::GetFolderPath([Environment+SpecialFolder]::MyDocuments))\WindowsPowerShell\Modules"
Exit the PowerShell console

Download And Install

Grab the latest version of the code from the downloads page but don't extract the ZIP file yet! Because the file came from the internet it needs to be unblocked or PowerShell gets cranky because it's considered untrusted.

To unblock a file, navigate to it in Windows Explorer, right click, and choose the Properties menu option. On the General tab, click the Unblock button, then click the OK button to close the Properties dialog.

Once the file is unblocked you can extract the contents to the WindowsPowerShell folder (in your "My Documents" directory) that you created in the last step.

Note: Make sure to keep the folder names in the zip file intact so that everything in the Modules folder is extracted into WindowsPowerShell\Modules and the .ps1 files are extracted into the WindowsPowerShell folder.

The Windows Inventory portion of SQL Power Doc will attempt to use the RDS-Manager PowerShell module provided by the Microsoft Remote Desktop Services team. You can download this optional module from http://gallery.technet.microsoft.com/ScriptCenter/e8c3af96-db10-45b0-88e3-328f087a8700/ . Make sure to save it in the WindowsPowerShell\Modules\RDS-Manager folder (in your "My Documents" directory) that you recently created. It's not the end of the world if it's missing but you'll get more details about users' desktop sessions when it's installed.

Collect A SQL Server And Windows Inventory

So far, so good...now it's time to discover your SQL Servers and perform an inventory! In this step you're going to run a PowerShell script which will discover SQL Servers on your network (or verify they're running), collect information about them and their underlying OS, and write the results as a Gzip compressed XML file that you'll use in the next step.

Start by opening a PowerShell console on the machine that will be collecting the information from your SQL Servers and set your current location to the WindowsPowerShell folder: Set-Location "$([Environment]::GetFolderPath([Environment+SpecialFolder]::MyDocuments))\WindowsPowerShell"

Running The Script, Choosing The Right Parameters

You're going to execute the script .\Get-SqlServerInventoryToClixml.ps1 to do all the work but it requires a few parameters to know what to do.

Discover & Verify SQL Server Services

	The first set of parameters define how to find & verify machines with SQL Server services installed on them.

		Find SQL Server services by querying Active Directory DNS for hosts

			-DnsServer
			Valid values are "automatic" or a comma delimited list of AD DNS server IP addresses to query for DNS A records that may be running SQL Server.

			-DnsDomain
			Optional. Valid values are "automatic" (the default if this parameter is not specified) or the AD domain name to query for DNS records from.

			-ExcludeSubnet
			Optional. This is a comma delimited list of  CIDR notation subnets to exclude when looking for SQL Servers.

			-LimitSubnet
			Optional. This is an inclusive comma delimited list of CIDR notation subnets to limit the scope when looking for SQL Servers.

			-ExcludeComputerName
			Optional. This is a comma delimited list of computer names to exclude when looking for SQL Servers.

			-PrivateOnly
			Optional. This switch limits the scope to private class A, B, and C IP addresses when looking for SQL Servers.


		Find SQL Server services by scanning a subnet of IP Addresses

			-Subnet
			Valid values are "automatic" or a comma delimited list of CIDR notation subnets to scan for IPv4 hosts that may be running SQL Server.

			-LimitSubnet
			Optional. This is an inclusive comma delimited list of CIDR notation subnets to limit the scope when looking for SQL Servers.

			-ExcludeComputerName
			Optional. This is a comma delimited list of computer names to exclude when looking for SQL Servers.

			-PrivateOnly
			Optional. This switch limits the scope to private class A, B, and C IP addresses when looking for SQL Servers.


		Find SQL Server services by computer name

			-ComputerName
			This is a comma delimited list of computer names that may be running SQL Server.

			-PrivateOnly
			Optional. This switch limits the scope to private class A, B, and C IP addresses when looking for SQL Servers.


Authentication

	SQL Power Doc will default to using Windows Authentication when attempting to connect to SQL Server instances that it finds. If you want to connect using SQL Server authentication instead, or if you are connecting to Windows Azure SQL Database instances, provide the following parameters:

		-Username
		SQL Server use

Sqlpowerdoc

Install / Use

README