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
$p="$p\MSSQLServer"
"`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"
$p="$p\SuperSocketNetLib"
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 SQL_Engine_CNI=3 SQL_ENGINE_CORE_CNI=3 SQL_LEGACYTOOLS_CNI=3 SQL_REPL_ENGINE_SUPPORT_CNI=3 SQL_SLP_ENGINE_SUPPORT_CNI=3 MPT_AGENT_CORE_CNI=3 MPT_AGENT_CORE_MNGD_CNI=3 SQL_AGENT_FNI=3 SQL_DATA_COLLECTOR_FNI=3 SQL_LEGACYTOOLS_FNI=3 SQL_MAIL_FNI=3 SQL_REPL_ENGINE_SUPPORT_FNI=3 SQL_UPGRADESCRIPTS_FNI=3 Properties: 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 192.168.56.1 Specific IP Address IP11 0 1 0 fe80::7d86:4ce4:724a:743d%5 Specific IP Address IP12 0 1 0 169.254.116.61 Specific IP Address IP13 0 1 0 ::1 Specific IP Address IP14 0 1 0 127.0.0.1 Specific IP Address IP15 0 1 0 fe80::5efe:192.168.58.1%9 Specific IP Address IP16 0 1 0 fe80::5efe:192.168.57.1%21 Specific IP Address IP17 0 1 0 fe80::5efe:192.168.56.1%11 Specific IP Address IP18 0 1 0 fe80::5efe:192.168.1.141%101 Specific IP Address IP2 0 1 0 192.168.57.1 Specific IP Address IP3 0 1 0 fe80::65c2:c76:2b5c:a889%93 Specific IP Address IP4 0 1 0 192.168.58.1 Specific IP Address IP5 0 1 0 fe80::7944:dba3:1931:86e2%36 Specific IP Address IP6 0 1 0 192.168.1.141 Specific IP Address IP7 0 1 0 fe80::d156:33ec:a379:337a%34 Specific IP Address IP8 0 1 0 169.254.51.122 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