Thursday, September 8, 2016

Collect SQL Server Registry information using PowerShell

Do you use third party tools to document state of your SQL Server?
If not, that script is for you!

At first, you will know what is your SQL Server is up to.
At second, that might be your baseline document, to which you can compare a current SQL Server state over the time.
At third, that is a priceless piece of documentation!!! (I mean FREE!!!) which you can put in a folder and report to your boss. 

So, use it with a little disclaimer:
1. You have to run this script as an administrator. To do this: Right click on PowerShell Icon and choose: "Run As administrator":

2. ONLY YOU - who is responsible to run this script. If accidentally it will format your hard drive or delete all your backups - you will be guilty! So, see what it is doing and decide if it is safe for you to run.

3. I've tested it on SQL Servers 2014 & 2016. It also supposed to work with SQL 2012. Versions 2008 & 2005 might have some slide problems.

4. I'm SQL guy and know that this script is not perfect. It might miss some data or even go with an error in some special situation. So, see #2 and make changes/adjustments yourself and if you can't, leave your angry note here and I might help.

5. The script collects info ONLY regarding SQL Engine. If I see the popularity of it I might extend it to SSAS/SSIS/SSRS/SQL Agent etc.

# PowerShell script to retrieve SQL Server Information from the registry
$m = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server"
$inst = (get-itemproperty $m).InstalledInstances
foreach ($i in $inst)
       $in = (Get-ItemProperty "$m\Instance Names\SQL").$i
       $p = $m+"\"+$in
       "-------------------------------------------------------------------`nServer Properties: "
       If ($i -Eq "MSSQLSERVER") {$r=" (Default)"} Else {$r=""}
       "`tInstance Name : $i$r"
       "`tVersion       : "+(Get-ItemProperty "$p\Setup").Version
       "`tSP            : "+(Get-ItemProperty "$p\Setup").SP
       "`tEdition       : "+(Get-ItemProperty "$p\Setup").Edition
       "`tLanguage      : "+(Get-ItemProperty "$p\Setup").Language
       "`tCollation     : "+(Get-ItemProperty "$p\Setup").Collation
       "`tSqlProgramDir : "+(Get-ItemProperty "$p\Setup").SqlProgramDir
"`tSQLPath       : "+(Get-ItemProperty "$p\Setup").SQLPath
       "`tSQLBinRoot    : "+(Get-ItemProperty "$p\Setup").SQLBinRoot
       "`tSQLDataRoot   : "+(Get-ItemProperty "$p\Setup").SQLDataRoot
"`tErrorDumpDir  : "+(Get-ItemProperty "$p\CPE").ErrorDumpDir
       "`tBackupDir     : "+(Get-ItemProperty "$p\").BackupDirectory
       If ((Get-ItemProperty "$p\").LoginMode -Eq 1) {$r="Windows"} `
       ElseIf ((Get-ItemProperty "$p\").LoginMode -Eq 2) {$r="Mixed"} Else {$r="Other"}
       "`tLoginMode     : $r Authentication Mode"
       If ((Get-ItemProperty "$p\HADR").HADR_Enabled) {$r="Enabled"} Else {$r="Disabled"}
       "`tHADR          : $r"
       "`nStartup Parameters: "
       (Get-ItemProperty "$p\Parameters" | Select SQLArg*  | Format-List | Out-String ).trim() -replace "SQLArg","`tSQLArg"

       If ((Get-ItemProperty "$p").ForceEncryption) {$r="Yes"} Else {$r="No"}
       "`nProtocol Properties: "
       "`tForceEncryption: $r"
       If ((Get-ItemProperty "$p").HideInstance) {$r="Yes"} Else {$r="No"}
       "`tHideInstance: $r"
       $r="`t"+(Get-ItemProperty "$p\AdminConnection").DisplayName+": "
       $r=$r+(Get-ItemProperty "$p\AdminConnection\Tcp").DisplayName+" "
       $r+(Get-ItemProperty "$p\AdminConnection\Tcp").TcpDynamicPorts
       "`nNetwork Protocols: "
       If ((Get-ItemProperty "$p\Np").Enabled) {$r="Enabled"} Else {$r="Disabled"}; "`t"+((Get-ItemProperty "$p\Np").DisplayName)+": $r";
       If ((Get-ItemProperty "$p\Sm").Enabled) {$r="Enabled"} Else {$r="Disabled"}; "`t"+((Get-ItemProperty "$p\Sm").DisplayName)+": $r"
       If ((Get-ItemProperty "$p\Via").Enabled) {$r="Enabled"} Else {$r="Disabled"};"`t"+((Get-ItemProperty "$p\Via").DisplayName)+": $r"
       If ((Get-ItemProperty "$p\Tcp").Enabled) {$r="Enabled"} Else {$r="Disabled"};"`t"+((Get-ItemProperty "$p\Tcp").DisplayName)+": $r"
       If ((Get-ItemProperty "$p\Tcp").ListenOnAllIPs) {$r="Yes"} Else {$r="No"}; "`t"+"TCP ListenOnAllIPs:$r "

       Get-ChildItem "$p\Tcp" | ForEach-Object {Get-ItemProperty $_.pspath} `
       | Format-Table -Autosize -Property PSChildName, Enabled, Active, TcpPort, TcpDynamicPorts, IpAddress, DisplayName

Just in case you have an idea what to expect from this script, here is an output from my test system:

Instance Name: Test2016

Version       : 13.0.1601.5
SP            : 0
Edition       : Developer Edition
Language      : 1033
Collation     : SQL_Latin1_General_CP1_CI_AS
SqlProgramDir : C:\SQLServer2016\
SQLPath       : C:\SQLServer2016\MSSQL13.Test2016\MSSQL
SQLBinRoot    : C:\SQLServer2016\MSSQL13.Test2016\MSSQL\Binn
SQLDataRoot   : C:\SQLServer2016\MSSQL13.Test2016\MSSQL
FeatureList   : SQL_Engine_Core_Inst=3 SQL_DataFiles_Core_Inst=3 SQL_ENGINE_DB_CNI=3 SQL_CMDLINETOOLS_CNI=3 SQL_DUMPER_CNI=3
ErrorDumpDir: C:\SQLServer2016\MSSQL13.Test2016\MSSQL\LOG\
BackupDirectory: C:\SQLServer2016\MSSQL13.Test2016\MSSQL\Backup
LoginMode: Mixed Authentication Mode
HADR: Disabled
ForceEncryption: No
HideInstance: No
Dedicated Administrative Connection: TCP/IP 56212

Startup Parameters:
SQLArg0 : -dC:\SQLServer2016\MSSQL13.Test2016\MSSQL\DATA\master.mdf
SQLArg1 : -eC:\SQLServer2016\MSSQL13.Test2016\MSSQL\Log\ERRORLOG
SQLArg2 : -lC:\SQLServer2016\MSSQL13.Test2016\MSSQL\DATA\mastlog.ldf

Network Protocols:
Named Pipes: Disabled
Shared Memory: Disabled
VIA: Disabled
TCP/IP: Disabled
TCP ListenOnAllIPs: Yes
PSChildName Enabled Active TcpPort TcpDynamicPorts IpAddress                    DisplayName
----------- ------- ------ ------- --------------- ---------                    -----------
IP1               0      1         0               fe80::30d2:a78a:a5f2:62d8%97 Specific IP Address
IP10              0      1         0                      Specific IP Address
IP11              0      1         0               fe80::7d86:4ce4:724a:743d%5  Specific IP Address
IP12              0      1         0                    Specific IP Address
IP13              0      1         0               ::1                          Specific IP Address
IP14              0      1         0                         Specific IP Address
IP15              0      1         0               fe80::5efe:    Specific IP Address
IP16              0      1         0               fe80::5efe:   Specific IP Address
IP17              0      1         0               fe80::5efe:   Specific IP Address
IP18              0      1         0               fe80::5efe: Specific IP Address
IP2               0      1         0                      Specific IP Address
IP3               0      1         0               fe80::65c2:c76:2b5c:a889%93  Specific IP Address
IP4               0      1         0                      Specific IP Address
IP5               0      1         0               fe80::7944:dba3:1931:86e2%36 Specific IP Address
IP6               0      1         0                     Specific IP Address
IP7               0      1         0               fe80::d156:33ec:a379:337a%34 Specific IP Address
IP8               0      1         0                    Specific IP Address
IP9               0      1         0               fe80::b882:c715:7b1e:4a75%20 Specific IP Address
IPAll                              0                                            Any IP Address

No comments:

Post a Comment