Last time I went through my script to gather server information for my inventory. Today I’m going to go through my script to collect SQL Server instance data. So, I think I made it clear last time that I’m not a Powershell guru, and there are probably countless ways in which this script can be improved, yada yada. ‘Nuff said.
Credits
Like the server info script, this script is based heavily on Allen White’s inventory script on Simple-Talk. If you haven’t already seen it, I highly recommend checking out the entire article. I’ve made some changes to Allen’s base script, to suit my own needs. Whereas Allen writes his output to a csv file, I wanted to load directly into my inventory database. Allen also reads in his list of servers to inventory from a file, I keep mine in a master table, also in my inventory database.
Tools you’ll need
In order to load your inventory data directly into SQL Server, you’ll need to download a couple of PowerShell functions.
Name: Write-DataTable.ps1
Author: Chad Miller
http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae
Loads data into from a datatable object into SQL Server tables
Name: Out-DataTable.ps1
Author: Chad Miller
http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd
Formats input into a datatable object which can then be imported into SQL Server using Write-DataTable.
Permissions required
You’ll need a SQL Server login on each instance you plan to inventory. The permissions for that login will depend on what data you’re collecting. You can use the principle of least permissions and only grant the minimum required, which is what I do. In fact I use certificate-signed procedures to gather any info that requires sysadmin-level rights. This method requires creating procedures on all target servers, which was a larger footprint than I would have preferred, but I felt it was a better option from a security standpoint. Or you can simply use a sysadmin login and be done with it. Potayto potahto.
The moment you’ve all been waiting for – The script
Parameters
My script accepts 4 parameters:
- Inventory database instance
- Inventory login
- Inventory password (I use the same login to collect my info and store it)
- Environment (Which environment do I want to inventory?)
A word of explanation about the Environment parameter. As I mentioned earlier, I store my master instance list in a table inside my inventory database. That table holds all instances across all of my environments, Production, Development, etc., and the environment for each instance is also stored in this master table. When I run this script I use this parameter to specify which environment I want to inventory.
Clean out tables pre-run
This script performs a full load every time, so my first step is simply to clear out any existing data.
$connection = new-object system.data.sqlclient.sqlconnection( `
"Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
##### Clean out all tables pre-run. Move Jobs to Jobs_Compare, that way we can see what jobs were dropped #####
$query = "select instance_name_short into #cmm from Servers where Environment in ('$ssi_env')
DELETE FROM [SQL].Login_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Users where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Files where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Roles where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Server_Roles where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Credentials where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Linked_Server_Logins where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Linked_Servers where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Schedules where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Schedule_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Info_Compare where instance_name in (select instance_name_short from #cmm);
INSERT INTO [SQL].Job_Info_Compare SELECT * FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Proxies where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Proxies where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Backup_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Instance_Info where instance_name in (select instance_name_short from #cmm);
"
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()
Get list of servers to inventory
The next step in the process is to read the master table to get the list of servers to inventory. Then we’ll step through them and for each one we’ll check and make sure it’s online and if it is, we’ll execute the get-sqlinfo function.
##### Get the list of servers to inventory #####
$query = "SELECT DISTINCT server_name as ServerName, server_name_short as ServerNameShort, instance_name as InstanceName, instance_name_short as InstanceNameShort FROM Servers WHERE Environment IN ('$ssi_env')"
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()
##### For every server gather data #####
while($reader.Read()) {
##### See if the server is alive #####
$svr = $reader['ServerName']
$svrshort = $reader['ServerNameShort']
$inst = $reader['InstanceName']
$instshort = $reader['InstanceNameShort']
$result = new-object Microsoft.SqlServer.Management.Common.ServerConnection($inst, $ssi_userid, $ssi_passwd)
$responds = $false
if ($result.isopen -eq 0) {
$responds = $true
}
##### If it is alive ... #####
If ($responds) {
#Write-Output "$inst is alive"
get-sqlinfo $svr $svrshort $inst $instshort
}
else {
# Let the user know we couldn't connect to the server
Write-Output "$svr does not respond"
}
}
Get-sqlinfo Function
The last part of the script is to actually gather the information.
First up on the list: Instance info. As you’ll soon see, most of my script uses the same basic format, over and over. The instance info section is the exception. Here I’m making use of the Server SMO object class to get additional instance-level information, like the version, edition, collation, whether it’s clustered, and database/log paths. I could have used T-SQL to get all of this info, like the rest of the script, but this way seemed easier and it let me add another tool to the arsenal. I then combine this information with some additional fields I’ve obtained using T-SQL. To load the data into our Inventory database, we use the same Write-DataTable and Out-Datatable functions we used in the server inventory script.
# Create an ADO.Net connection to the instance
$ServerConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$ServerConn.ServerInstance="$instance"
$ServerConn.LoginSecure = $false
$ServerConn.Login = $ssi_userid
$ServerConn.Password = $ssi_passwd
$ServerConn.Connect()
$s = new-object Microsoft.SqlServer.Management.SMO.Server($ServerConn)
##### Get SQL Instance Info #####
$ssi_table="SQL.Instance_Info"
$connection2 = new-object system.data.sqlclient.sqlconnection( `
"Data Source=$instance;Initial Catalog=master;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection2.Open()
$cmd2 = $connection2.CreateCommand()
$query = "DECLARE @ServiceAccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'
SELECT @ServiceAccount as svc_acct
,(select value from sys.configurations where name = 'cost threshold for parallelism') as parallel_threshold
,(select value from sys.configurations where name = 'max degree of parallelism') as max_dop
,(select value from sys.configurations where name = 'min server memory (MB)') as min_memory
,(select value from sys.configurations where name = 'max server memory (MB)') as max_memory
,(select value from sys.configurations where name = 'xp_cmdshell') as xp_cmdshell_enabled"
$cmd2.CommandText = $query
$reader2 = $cmd2.ExecuteReader()
if ($reader2.Read() )
{
$svcacct = $reader2['svc_acct']
$parallel_threshold = $reader2['parallel_threshold']
$max_dop = $reader2['max_dop']
$min_memory = $reader2['min_memory']
$max_memory = $reader2['max_memory']
$xp_cmdshell_enabled = $reader2['xp_cmdshell_enabled']
}
$dt= $s.Information | SELECT @{n="InstanceName";e={$instanceshort}}, VersionString, ProductLevel, EngineEdition, Collation, IsClustered, MasterDBPath, MasterDBLogPath, RootDirectory, @{n="ServiceAccount";e={$svcacct}},@{n="parallel_threshold";e={$parallel_threshold}}, @{n="max_dop";e={$max_dop}}, @{n="min_memory";e={$min_memory}}, @{n="max_memory";e={$max_memory}}, @{n="xp_cmdshell_enabled";e={$xp_cmdshell_enabled}} | Out-DataTable
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
For login information, I’m simply defining a connection string and a query string that gets me the login data I want. I create a SQLDataAdapter object ($da), use it to fill a DataTable object ($dt), and load it up into my Inventory table.
##### Get Login Info #####
$conn = "server=$instance;database=master;User ID=$ssi_userid;password=$ssi_passwd"
$ssi_table="SQL.Login_Info"
$query = "SELECT @@SERVERNAME as instance_name, sp.[name] as LoginName, sp.create_date AS AccountCreateDate,
sp.modify_date AS LastTimeAccountModified,
LOGINPROPERTY( sp.[name], 'PasswordLastSetTime' ) AS PasswordLastSetTime,
sp.default_database_name as DefaultDatabase,
sp.default_language_name as DefaultLanguage,
sl.is_policy_checked as IsPolicyChecked,
sl.is_expiration_checked as IsExpirationChecked,
LOGINPROPERTY( sp.[name], 'IsExpired' ) AS IsExpired,
LOGINPROPERTY (sp.[name], 'DaysUntilExpiration' ) AS DaysUntilExpiration,
LOGINPROPERTY( sp.[name], 'IsLocked' ) AS IsLocked,
LOGINPROPERTY( sp.[name], 'IsMustChange' ) AS IsMustChange,
LOGINPROPERTY( sp.[name], 'LockoutTime' ) AS LockoutTime,
LOGINPROPERTY( sp.[name], 'BadPasswordCount' ) AS BadPasswordCount,
LOGINPROPERTY( sp.[name], 'BadPasswordTime' ) AS BadPasswordTime,
LOGINPROPERTY( sp.[name], 'HistoryLength' ) AS HistoryLength,
sp.sid, sp.type
FROM sys.server_principals sp
LEFT OUTER JOIN sys.sql_logins sl on sl.sid = sp.sid
WHERE sp.type not in ('C', 'R')"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
For the remainder of the tables, I use the same basic format. The $ssi_table variable specifies my target table, and the $query variable holds the T-SQL query to execute. The rest is all the same, over and over. Could I have simplified it more by creating a function to handle the loading process and simply passed in the $ssi_table and $query? Absolutely. In fact, I’ll leave that for you to do. Call it homework. 🙂 (And this is why I will probably *not* be the next Ola.) Here’s an example.
##### Get Credential Info #####
$ssi_table="SQL.Credentials"
$query = "select @@servername as instance_name, credential_id, name, credential_identity, create_date, modify_date
from msdb.sys.credentials"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
One last thing
The last thing this script is execute a stored procedure in my inventory database called usp_StoreDBGrowth. All this procedure does is archive the newly inserted datafile information into a separate table for historical growth analysis.
The complete script
So here’s the complete script. Give it a whirl and let me know what you think. If you come up with some improvements, I’d love to hear them, too.
# get-SQLInfo.ps1
# usage: ./get-SQLInfo.ps1
# Collects all Inventory information into MyInventory database
# Including:
# [SQL].Instance_Info;
# [SQL].Database_Info;
# [SQL].Login_Info;
# [SQL].Database_Users;
# [SQL].Database_Files;
# [SQL].Server_Roles;
# [SQL].Database_Roles;
# [SQL].Linked_Servers;
# [SQL].Linked_Server_Logins;
param(
[string]$ssi_instance=$null,
[string]$ssi_userid=$null,
[string]$ssi_passwd=$null,
[string]$ssi_env=$null
)
. .\write-datatable.ps1
. .\out-datatable.ps1
$ssi_database = "MyInventory"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null
function get-sqlinfo ($server, $servershort, $instance, $instanceshort) {
# Create an ADO.Net connection to the instance
$ServerConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$ServerConn.ServerInstance="$instance"
$ServerConn.LoginSecure = $false
$ServerConn.Login = $ssi_userid
$ServerConn.Password = $ssi_passwd
$ServerConn.Connect()
$s = new-object Microsoft.SqlServer.Management.SMO.Server($ServerConn)
##### Get SQL Instance Info #####
$ssi_table="SQL.Instance_Info"
$connection2 = new-object system.data.sqlclient.sqlconnection( `
"Data Source=$instance;Initial Catalog=master;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection2.Open()
$cmd2 = $connection2.CreateCommand()
$query = "DECLARE @ServiceAccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'
SELECT @ServiceAccount as svc_acct
,(select value from sys.configurations where name = 'cost threshold for parallelism') as parallel_threshold
,(select value from sys.configurations where name = 'max degree of parallelism') as max_dop
,(select value from sys.configurations where name = 'min server memory (MB)') as min_memory
,(select value from sys.configurations where name = 'max server memory (MB)') as max_memory
,(select value from sys.configurations where name = 'xp_cmdshell') as xp_cmdshell_enabled"
$cmd2.CommandText = $query
$reader2 = $cmd2.ExecuteReader()
if ($reader2.Read() )
{
$svcacct = $reader2['svc_acct']
$parallel_threshold = $reader2['parallel_threshold']
$max_dop = $reader2['max_dop']
$min_memory = $reader2['min_memory']
$max_memory = $reader2['max_memory']
$xp_cmdshell_enabled = $reader2['xp_cmdshell_enabled']
}
$dt= $s.Information | SELECT @{n="InstanceName";e={$instanceshort}}, VersionString, ProductLevel, EngineEdition, Collation, IsClustered, MasterDBPath, MasterDBLogPath, RootDirectory, @{n="ServiceAccount";e={$svcacct}},@{n="parallel_threshold";e={$parallel_threshold}}, @{n="max_dop";e={$max_dop}}, @{n="min_memory";e={$min_memory}}, @{n="max_memory";e={$max_memory}}, @{n="xp_cmdshell_enabled";e={$xp_cmdshell_enabled}} | Out-DataTable
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Get Login Info #####
$conn = "server=$instance;database=master;User ID=$ssi_userid;password=$ssi_passwd"
$ssi_table="SQL.Login_Info"
$query = "SELECT @@SERVERNAME as instance_name, sp.[name] as LoginName, sp.create_date AS AccountCreateDate,
sp.modify_date AS LastTimeAccountModified,
LOGINPROPERTY( sp.[name], 'PasswordLastSetTime' ) AS PasswordLastSetTime,
sp.default_database_name as DefaultDatabase,
sp.default_language_name as DefaultLanguage,
sl.is_policy_checked as IsPolicyChecked,
sl.is_expiration_checked as IsExpirationChecked,
LOGINPROPERTY( sp.[name], 'IsExpired' ) AS IsExpired,
LOGINPROPERTY (sp.[name], 'DaysUntilExpiration' ) AS DaysUntilExpiration,
LOGINPROPERTY( sp.[name], 'IsLocked' ) AS IsLocked,
LOGINPROPERTY( sp.[name], 'IsMustChange' ) AS IsMustChange,
LOGINPROPERTY( sp.[name], 'LockoutTime' ) AS LockoutTime,
LOGINPROPERTY( sp.[name], 'BadPasswordCount' ) AS BadPasswordCount,
LOGINPROPERTY( sp.[name], 'BadPasswordTime' ) AS BadPasswordTime,
LOGINPROPERTY( sp.[name], 'HistoryLength' ) AS HistoryLength,
sp.sid, sp.type
FROM sys.server_principals sp
LEFT OUTER JOIN sys.sql_logins sl on sl.sid = sp.sid
WHERE sp.type not in ('C', 'R')"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Get Credential Info #####
$ssi_table="SQL.Credentials"
$query = "select @@servername as instance_name, credential_id, name, credential_identity, create_date, modify_date
from msdb.sys.credentials"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Jobs #####
$ssi_table = "SQL.Job_Info"
$query = ";with laststatus as (
select job_id, start_execution_date, stop_execution_date from
(select job_id, start_execution_date, stop_execution_date, dense_rank() over (partition by job_id order by start_execution_date desc) as rownum
from msdb..sysjobactivity where start_execution_date is not null) a
where a.rownum = 1)
, maxinstance as (
select max(instance_id) maxinst_id, job_id from msdb.dbo.sysjobhistory
group by job_id)
, laststep as (
select j.job_id, max(j.instance_id) as laststep_id
from msdb.dbo.sysjobhistory j
join maxinstance mi on j.job_id = mi.job_id
and j.instance_id < mi.maxinst_id join laststatus ls on ls.job_id = j.job_id and CAST( CONVERT(VARCHAR, j.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,j.run_time),6),5,0,':'),3,0,':') AS DATETIME ) >= ls.start_execution_date
where run_status = 0
group by j.job_id)
select @@SERVERNAME as instance_name, j.name as job_name, j.enabled, j.description, j.date_created, j.date_modified
, l.start_execution_date as last_run_date
, isnull(case
when l.stop_execution_date is null then 'In-Progress'
else case jh.run_status
when 0 then 'Failed '
when 1 then 'Succeeded '
when 2 then 'Retry'
when 3 then 'Canceled '
when 4 then 'In-progress'
when 5 then 'Unknown ' END END , 'Unknown') as 'last_run_status'
, jhm.message, j.job_id
FROM msdb.dbo.sysjobs j
left outer join laststatus l
on j.job_id = l.job_id
left outer join msdb.dbo.sysjobhistory jh
join maxinstance jh2
ON jh2.maxinst_id = jh.instance_id
and jh2.job_id = jh.job_id
ON j.job_id = jh.job_id
LEFT JOIN laststep ls
JOIN msdb.dbo.sysjobhistory jhm
on ls.laststep_id = jhm.instance_id
on ls.job_id = jh.job_id
WHERE j.enabled = 1 and l.start_execution_date is not null"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Get Schedule Info #####
$ssi_table="SQL.Schedule_Info"
$query = "select @@SERVERNAME as instance_name, s.schedule_id, s.enabled, s.freq_type, s.freq_interval,
s.freq_subday_type, s.freq_subday_interval, s.freq_relative_interval, s.freq_recurrence_factor, s.active_start_time, s.active_end_time
from msdb.dbo.sysschedules s"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Get Job Schedule Info #####
$ssi_table="SQL.Job_Schedules"
$query = "select @@SERVERNAME as instance_name, js.job_id, js.schedule_id
from msdb.dbo.sysjobschedules js"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Get Proxy Info #####
$ssi_table="SQL.Proxies"
$query = "select @@servername as instance_name, proxy_id, name, credential_id, enabled, description, user_sid, credential_date_created
from msdb.dbo.sysproxies"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Get Job Step Proxy Info #####
$ssi_table="SQL.Job_Proxies"
$query = "select @@servername as instance_name, j.job_id, j.name as job_name, s.step_name, s.proxy_id
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobsteps s on j.job_id = s.job_id and s.proxy_id is not null"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Server Roles #####
$ssi_table = "SQL.Server_Roles"
$query = "select @@Servername as instance_name, r.name as role_name, m.name as member_name, m.sid
from sys.server_principals r
join sys.server_role_members rm on r.principal_id = rm.role_principal_id
join sys.server_principals m on m.principal_id = rm.member_principal_id"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Linked Servers #####
$ssi_table = "SQL.Linked_Servers"
$query = "declare
@alias varchar(40)
,@regbase nvarchar(30)
,@regkey nvarchar(100)
,@dirbasepath nvarchar(250)
,@dirbasepath2 nvarchar(250)
SET @regbase = 'HKEY_LOCAL_MACHINE'
SET @regkey = 'Software\Microsoft\MSSQLServer\Client\ConnectTo\'
create table #aliases (
alias varchar(40),
resolved nvarchar(250))
declare a1 cursor for select data_source from sys.servers
open a1
fetch a1 into @alias
while @@FETCH_STATUS = 0
begin
set @dirbasepath = NULL
EXECUTE master..xp_regread @regbase
, @regkey
, @alias
, @dirbasepath OUTPUT
insert into #aliases
SELECT @alias, substring(@dirbasepath,CHARINDEX(',',@dirbasepath)+1,LEN(@dirbasepath))
fetch a1 into @alias
end
close a1
deallocate a1
select distinct @@servername as [Instance], s.name as [Linked_Server], ISNULL(tmp.resolved, s.data_source) as [Remote_Instance], s.provider as [Provider], s.catalog as [Default_Database]
from master.sys.servers s
LEFT OUTER JOIN #aliases tmp ON tmp.alias = s.name
where is_data_access_enabled = 1"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Linked Server Logins #####
$ssi_table = "SQL.Linked_Server_Logins"
$query = "SELECT @@servername as [instance_name]
, s.name as [linked_server]
, CASE l.uses_self_credential
WHEN 1 THEN 'UNMAPPED LOGINS'
ELSE ISNULL(p.name, 'UNMAPPED LOGINS')
END AS [local_login]
, CASE l.uses_self_credential
WHEN 1 THEN 'USE SELF'
ELSE l.remote_name
END AS [remote_login]
FROM sys.linked_logins l
JOIN sys.servers s ON s.server_id = l.server_id AND is_data_access_enabled = 1
LEFT OUTER JOIN sys.server_principals p ON p.principal_id = l.local_principal_id
ORDER BY [linked_server], [local_login]"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Get SQL Database Info #####
$ssi_table="SQL.Database_Info"
$query = "select @@SERVERNAME as [instance_name], d.database_id, d.name as [database_name], d.state, d.recovery_model, d.compatibility_level, d.collation_name,
b.bkup_dt as last_backup_date,
b.diff_dt as last_differential_backup_date,
b.log_dt as last_log_backup_date,
d.create_date,
--data space usage, index space usage
isnull(dm.mirroring_role, 0) as is_mirroring_enabled,
p.name as [owner]
--primary file path, backup dir
from master.sys.databases d
join master.sys.database_mirroring dm on d.database_id = dm.database_id
join master.sys.server_principals p on d.owner_sid = p.sid
left join
(SELECT bs.database_name,
MAX(case bs.type when 'D' then bs.backup_start_date end) as bkup_dt,
MAX(case bs.type when 'I' then bs.backup_start_date end) as diff_dt,
MAX(case bs.type when 'L' then bs.backup_start_date end) as log_dt
FROM msdb.dbo.backupset bs
WHERE bs.server_name = @@Servername
and bs.is_copy_only = 0
group by bs.database_name) b on b.database_name = d.name"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Get Database User Info #####
$ssi_table="SQL.Database_Users"
$query = "exec sp_get_database_users"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Database Roles #####
$ssi_table = "SQL.Database_Roles"
$query = "exec sp_get_database_roles"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Database Files #####
$ssi_table = "SQL.Database_Files"
$query = "select @@SERVERNAME as instance_name, DB_Name(database_id) as database_name, file_id, type_desc, name, physical_name, (size)*8/1024 as size_mb
,case (is_percent_growth) WHEN 1 THEN growth ELSE 0 END as growth_percent
,case (is_percent_growth) WHEN 0 THEN growth*8/1024 ELSE 0 END as growth_mb
from sys.master_files
WHERE type in (0, 1)"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### Backup Info #####
$ssi_table = "SQL.Backup_Info"
$query = "DECLARE @db_name sysname ,@restore_to_datetime datetime , @server_name nvarchar(512);
select @restore_to_datetime = GETDATE();
set @server_name = cast(serverproperty(N'Servername') as nvarchar(512));
CREATE TABLE #backupset(
backup_set_id INTEGER NOT NULL,
is_in_restore_plan BIT NOT NULL,
backup_start_date DATETIME NOT NULL,
type CHAR(1) NOT NULL,
database_name NVARCHAR(256) NOT NULL,
database_guid UNIQUEIDENTIFIER,
family_guid UNIQUEIDENTIFIER,
first_recovery_fork_guid UNIQUEIDENTIFIER,
last_recovery_fork_guid UNIQUEIDENTIFIER,
first_lsn NUMERIC(25, 0),
last_lsn NUMERIC(25, 0),
checkpoint_lsn NUMERIC(25, 0),
database_backup_lsn NUMERIC(25, 0),
fork_point_lsn NUMERIC(25, 0),
restore_till_lsn NUMERIC(25, 0),
backup_set_uuid UNIQUEIDENTIFIER,
differential_base_guid UNIQUEIDENTIFIER
);
SELECT backupset_outer.database_name
, backupset_outer.backup_set_id
,backupset_outer.backup_start_date
into #most_recent
FROM msdb.dbo.backupset backupset_outer
WHERE backupset_outer.server_name = @server_name
AND backupset_outer.type <> 'L' -- Full and differential Database Backups
AND backupset_outer.backup_start_date
= ( SELECT MAX(backupset_inner.backup_start_date)
FROM msdb.dbo.backupset backupset_inner
WHERE backupset_inner.database_name = backupset_outer.database_name
AND backupset_inner.server_name = @server_name
AND backupset_inner.type = backupset_outer.type
AND backupset_inner.backup_start_date <= @restore_to_datetime
AND backupset_inner.is_copy_only = 0 )
AND backupset_outer.is_copy_only = 0;
INSERT #backupset(
backup_set_id
,is_in_restore_plan
,backup_start_date
,type
,database_name
,last_recovery_fork_guid
)
SELECT msdb.dbo.backupset.backup_set_id
,1 -- The full database backup is always needed for the restore plan
,msdb.dbo.backupset.backup_start_date
,msdb.dbo.backupset.type
,msdb.dbo.backupset.database_name
,msdb.dbo.backupset.last_recovery_fork_guid
FROM msdb.dbo.backupset
JOIN #most_recent m
on msdb.dbo.backupset.backup_set_id = m.backup_set_id
AND msdb.dbo.backupset.server_name = @server_name;
select @server_name as instance_name, btmp.database_name, bkmf.media_set_id, bkmf.family_sequence_number, bkmf.physical_device_name
, bkps.backup_start_date, bkps.backup_finish_date, bkps.first_family_number, bkps.last_family_number, bkps.type as backup_type
from
#backupset AS btmp
INNER JOIN msdb.dbo.backupset AS bkps ON bkps.backup_set_id = btmp.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bkmf ON bkps.media_set_id = bkmf.media_set_id;
drop table #backupset;
drop table #most_recent;"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
} #get-sqlinfo
$connection = new-object system.data.sqlclient.sqlconnection( `
"Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
##### Clean out all tables pre-run. Move Jobs to Jobs_Compare, that way we can see what jobs were dropped #####
$query = "select instance_name_short into #cmm from Servers where Environment in ('$ssi_env')
DELETE FROM [SQL].Login_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Users where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Files where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Roles where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Server_Roles where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Credentials where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Linked_Server_Logins where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Linked_Servers where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Schedules where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Schedule_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Info_Compare where instance_name in (select instance_name_short from #cmm);
INSERT INTO [SQL].Job_Info_Compare SELECT * FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Proxies where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Proxies where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Backup_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Instance_Info where instance_name in (select instance_name_short from #cmm);
"
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()
##### Get the list of servers to inventory #####
$query = "SELECT DISTINCT server_name as ServerName, server_name_short as ServerNameShort, instance_name as InstanceName, instance_name_short as InstanceNameShort FROM Servers WHERE Environment IN ('$ssi_env')"
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()
##### For every server gather data #####
while($reader.Read()) {
##### See if the server is alive #####
$svr = $reader['ServerName']
$svrshort = $reader['ServerNameShort']
$inst = $reader['InstanceName']
$instshort = $reader['InstanceNameShort']
$result = new-object Microsoft.SqlServer.Management.Common.ServerConnection($inst, $ssi_userid, $ssi_passwd)
$responds = $false
if ($result.ProcessID -ne $null) {
$responds = $true
}
##### If it is alive ... #####
If ($responds) {
#Write-Output "$inst is alive"
get-sqlinfo $svr $svrshort $inst $instshort
}
else {
# Let the user know we couldn't connect to the server
Write-Output "$svr does not respond"
}
}
##### Execute usp_StoreDBGrowth procedure #####
$connection = new-object system.data.sqlclient.sqlconnection( `
"Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
$query = "exec [SQL].usp_StoreDBGrowth"
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()
Edit: Here’s the script to create the SQL tables.
USE [MyInventory] GO CREATE TABLE [SQL].[Job_Schedules]( [instance_name] [nvarchar](128) NULL, [job_id] [uniqueidentifier] NULL, [schedule_id] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Schedule_Info]( [instance_name] [nvarchar](128) NULL, [schedule_id] [int] NOT NULL, [enabled] [int] NOT NULL, [freq_type] [int] NOT NULL, [freq_interval] [int] NOT NULL, [freq_subday_type] [int] NOT NULL, [freq_subday_interval] [int] NOT NULL, [freq_relative_interval] [int] NOT NULL, [freq_recurrence_factor] [int] NOT NULL, [active_start_time] [int] NOT NULL, [active_end_time] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Backup_Info]( [instance_name] [nvarchar](128) NOT NULL, [database_name] [nvarchar](128) NOT NULL, [media_set_id] [int] NOT NULL, [family_sequence_number] [tinyint] NOT NULL, [physical_device_name] [nvarchar](260) NULL, [backup_start_date] [datetime] NULL, [backup_finish_date] [datetime] NULL, [first_family_number] [tinyint] NULL, [last_family_number] [tinyint] NULL, [backup_type] [char](1) NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Database_Info]( [instance_name] [nvarchar](128) NOT NULL, [database_id] [smallint] NULL, [database_name] [nvarchar](128) NOT NULL, [status] [varchar](40) NULL, [RecoveryModel] [varchar](20) NULL, [CompatibilityLevel] [varchar](20) NULL, [Collation] [varchar](50) NULL, [LastBackupDate] [datetime] NULL, [LastDifferentialBackupDate] [datetime] NULL, [LastLogBackupDate] [datetime] NULL, [CreateDate] [datetime] NULL, [IsMirroringEnabled] [varchar](5) NULL, [Owner] [varchar](50) NULL, CONSTRAINT [PK_Instance_Database] PRIMARY KEY CLUSTERED ( [instance_name] ASC, [database_name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [SQL].[Instance_Info]( [instance_name] [nvarchar](128) NOT NULL, [Version] [varchar](20) NULL, [SPLevel] [varchar](10) NULL, [Edition] [varchar](50) NULL, [Collation] [varchar](50) NULL, [IsClustered] [varchar](5) NULL, [MasterDBPath] [varchar](200) NULL, [MasterDBLogPath] [varchar](200) NULL, [RootDirectory] [varchar](200) NULL, [ServiceAccount] [varchar](50) NULL, [Parallel_Threshold] [int] NULL, [Max_DOP] [int] NULL, [Min_Memory] [int] NULL, [Max_Memory] [int] NULL, [XP_Cmdshell_Enabled] [tinyint] NULL, CONSTRAINT [PK_Instance_Name] PRIMARY KEY CLUSTERED ( [instance_name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [SQL].[Linked_Server_Logins]( [instance_name] [nvarchar](128) NOT NULL, [linked_server] [sysname] NOT NULL, [local_login] [nvarchar](128) NOT NULL, [remote_login] [nvarchar](128) NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Linked_Servers]( [Instance_name] [nvarchar](128) NOT NULL, [Linked_Server] [sysname] NOT NULL, [Remote_Instance] [nvarchar](4000) NULL, [Provider] [sysname] NOT NULL, [Default_Database] [sysname] NULL, CONSTRAINT [PK_instance_linked_server] PRIMARY KEY CLUSTERED ( [Instance_name] ASC, [Linked_Server] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [SQL].[Database_Growth]( [instance_name] [nvarchar](128) NULL, [database_name] [nvarchar](128) NULL, [datafile_mb] [int] NULL, [logfile_mb] [int] NULL, [run_date] [date] NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Job_Proxies]( [instance_name] [nvarchar](128) NOT NULL, [job_id] [uniqueidentifier] NOT NULL, [job_name] [sysname] NOT NULL, [step_name] [sysname] NOT NULL, [proxy_id] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Proxies]( [instance_name] [nvarchar](128) NOT NULL, [proxy_id] [int] NOT NULL, [name] [sysname] NOT NULL, [credential_id] [int] NOT NULL, [enabled] [tinyint] NOT NULL, [description] [nvarchar](512) NULL, [user_sid] [varbinary](85) NOT NULL, [credential_date_created] [datetime] NOT NULL, CONSTRAINT [PK_Instance_Proxy] PRIMARY KEY CLUSTERED ( [instance_name] ASC, [proxy_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [SQL].[Credentials]( [instance_name] [nvarchar](128) NOT NULL, [credential_id] [int] NOT NULL, [name] [sysname] NOT NULL, [credential_identity] [nvarchar](4000) NULL, [create_date] [datetime] NOT NULL, [modify_date] [datetime] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Database_Roles]( [instance_name] [nvarchar](128) NOT NULL, [database_name] [nvarchar](128) NOT NULL, [role_name] [varchar](100) NULL, [database_user] [varchar](200) NULL, [dbuser_sid] [varbinary](85) NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Job_Info_Compare]( [instance_name] [nvarchar](128) NULL, [job_name] [sysname] NOT NULL, [enabled] [tinyint] NOT NULL, [description] [nvarchar](512) NULL, [date_created] [datetime] NOT NULL, [date_modified] [datetime] NOT NULL, [last_run_date] [datetime] NULL, [last_run_status] [varchar](20) NULL, [message] [nvarchar](4000) NULL, [job_id] [uniqueidentifier] NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Database_Users]( [instance_name] [nvarchar](128) NOT NULL, [database_name] [nvarchar](128) NOT NULL, [database_user] [varchar](100) NULL, [create_date] [datetime] NULL, [dbuser_sid] [varbinary](max) NULL, [dbuser_type] [char](1) NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Login_Info]( [instance_name] [nvarchar](128) NOT NULL, [LoginName] [sysname] NOT NULL, [AccountCreateDate] [datetime] NOT NULL, [LastTimeAccountModified] [datetime] NOT NULL, [PasswordLastSetTime] [sql_variant] NULL, [DefaultDatabase] [sysname] NULL, [DefaultLanguage] [sysname] NULL, [IsPolicyChecked] [bit] NULL, [IsExpirationChecked] [bit] NULL, [IsExpired] [sql_variant] NULL, [DaysUntilExpiration] [sql_variant] NULL, [IsLocked] [sql_variant] NULL, [IsMustChange] [sql_variant] NULL, [LockoutTime] [sql_variant] NULL, [BadPasswordCount] [sql_variant] NULL, [BadPasswordTime] [sql_variant] NULL, [HistoryLength] [sql_variant] NULL, [LoginSid] [varbinary](85) NULL, [LoginType] [char](1) NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Server_Roles]( [instance_name] [nvarchar](128) NOT NULL, [RoleName] [nvarchar](128) NULL, [LoginName] [nvarchar](128) NULL, [LoginSid] [varbinary](85) NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Database_Files]( [instance_name] [nvarchar](128) NOT NULL, [database_name] [nvarchar](128) NOT NULL, [file_id] [int] NOT NULL, [type_desc] [nvarchar](60) NULL, [name] [sysname] NOT NULL, [physical_name] [nvarchar](260) NOT NULL, [size_mb] [int] NULL, [growth_percent] [int] NOT NULL, [growth_mb] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [SQL].[Job_Info]( [instance_name] [nvarchar](128) NOT NULL, [job_name] [sysname] NOT NULL, [enabled] [tinyint] NOT NULL, [description] [nvarchar](512) NULL, [date_created] [datetime] NOT NULL, [date_modified] [datetime] NOT NULL, [last_run_date] [datetime] NULL, [last_run_status] [varchar](20) NULL, [message] [nvarchar](4000) NULL, [job_id] [uniqueidentifier] NULL ) ON [PRIMARY] GO
Another amazing post! Will you be sharing your Scheduled and On-demand reports that you run against your inventory database in another post (say yes…say yes!)?
Thanks Jason! I’ll think about how to turn the reports (or at least the queries) into a post.
Colleen, this is exciting stuff. I’ve been building up my own solutions but nothing like this.
Would you please post the SQL to create the tables to store the SQL Server instance data?
Also, ditto on the request for the SSRS reports.
Thank you so much for sharing.
Yes, this has been very helpful!
Please post the script to creat the database tables.
Thanks!
Ok folks. The code to create the tables has been added to the end of the post. Happy Inventorying!
Hate to ask for more when you’ve already given us so much, but would you also post the stored procedures: usp_StoreDBGrowth, sp_get_database_users, and sp_get_database_roles?
Thanks!
Mark
Colleen,
Could you give me an idea of how to set this up and configure it? I have all the code, the db and tables created…just trying to get the powershell script to work. I would love to play around with this idea and see if it would be useful for me. I tried running the “complete script” passing in my own parameters but failed. Any help would be nice.
Thanks,
J
Jeremy – If you email me the details of the error you’re getting, I’ll be happy to help. colleen.morrow@gmail.com
Colleen, this script is great thanks..
i am trying to use this to collect some information from instances across our environment but want to use windows authentication as opposed to sql logins.
i am struggling with the validation of the instances (for each one that is online run the process) as thinks they are all online (but 1 is valid), any idea how i can test \ resolve this?
Hi Ian – So you’re using windows authentication and all of them are coming back as being online but only one is actually running? Can you post the validation portion of the code here? I’m assuming you altered it for the windows auth.
Hi Colleen,
Many thanks for your quick response, my script is an extermely cut down type compared to your own, but as i am learning powershell on the go think may pf made a silly mistake. All my script wants to do is use an instance list, i want to attempt to connect and if online carry on, if not online flag up (we have 100+ instances) – my problem is everything is coming up as being online (even a fake entry in my instance list)so data loads in from valid instances but attempts the fake entry also..
Script below, thanks in advance for your time :o)
. .\write-datatable.ps1
. .\out-datatable.ps1
$ssi_instance=”testinstance\testinstance”
$ssi_database = “DailyChecks”
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.ConnectionInfo’) | out-null
function get-DailyChecks ($instance) {
# Create an ADO.Net connection to the instance
$ServerConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection(“server=$instance;Integrated Security=true”);
$s = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) $ServerConn
##### Get Server Roles #####
$conn = “server=$instance;Integrated Security=true”
$ssi_table = “dbo.Server_Roles”
$query = “select @@Servername as instance_name, r.name as role_name, m.name as member_name, m.sid
from sys.server_principals r
join sys.server_role_members rm on r.principal_id = rm.role_principal_id
join sys.server_principals m on m.principal_id = rm.member_principal_id”
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt
} #get-DailyChecks
$connection = new-object system.data.sqlclient.sqlconnection(“server=$ssi_instance;database=$ssi_database;Integrated Security=true;”);
$connection.Open()
$cmd = $connection.CreateCommand()
##### Get the list of servers to inventory #####
$query = “SELECT DISTINCT Instance_Name AS InstanceName FROM Servers”
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()
##### For every server gather data #####
while($reader.Read()) {
##### See if the server is alive #####
$inst = $reader[‘InstanceName’]
$result = new-object Microsoft.SqlServer.Management.Common.ServerConnection($inst)
Write-Output “$responds”
$responds = $false
if ($result.isopen -eq 0) {
$responds = $true
}
##### If it is alive … #####
If ($responds) {
#run the procedure against the instances online
get-DailyChecks $inst
}
else {
# Let the user know we couldn’t connect to the instance
Write-Output “$inst not online”
}
}
Hi Ian –
Good news for you: you weren’t doing anything wrong. Bad news for me: I was. My use of the isOpen function to test for the existence of the instance wasn’t working as I expected. I don’t know why this never came up before, I know I’ve run this against servers that were offline. Interesting. Anyway, I’ve come up with a fix, which is basically checking for a process id, rather than using isopen. Give this a whirl and let me know if it works for you. If everything looks good I’ll be sure to update the original post and the version in my toolbox. Thanks for the heads up!
while($reader.Read()) {
##### See if the server is alive #####
$inst = $reader[‘InstanceName’]
$result = new-object Microsoft.SqlServer.Management.Common.ServerConnection($inst)
$responds = $false
if ($result.ProcessID -ne $null) {
$responds = $true
}
##### If it is alive … #####
If ($responds) {
#run the procedure against the instances online
Write-Output “$inst is alive!!”
#get-DailyChecks $inst
}
else {
# Let the user know we couldn’t connect to the instance
Write-Output “$inst not online”
}
}
Colleen, you are a star, thanks it works now as you expected! This is a real milestone for what i want to use this process for and really appreciate your help..
for my own info (and what it looks like when i have ran it) when the script finishes its run it closes the connection to each sql instance itself? – is there a need for any $connection.close anywhere or that is taken care of at the end of run of the function to the online instance?
Last but not least! for instances online i am running the function to obtain the info from the instances, as i aim to run this powershell script from one central server via an agent job would it be easy to write the information for those not online back into my $ssi_instance db?
Hello Colleen, having trouble locating the stored procedures required for the SQL inventory scripts.
sp_get_database_roles, usp_StoreDBGrowth and sp_get_database_users. would you happen to have those handy?
JD – If you go to my Toolbox page you can download all the scripts in one zip file.
Help required,
How to gather / list a report of all the sql servers and versions in my environment Prod / Dev / Test / UAT / SYST using a PS script or SQL Scripts.
Provide a version report for the SQL Servers in each environment. We just want to know which ones have SQL Server need, SP1 and which ones need it.
Is there something missing within the inner query around line 405? The where statement reads “AND backupset_inner.backup_start_date” then a bunch of white space before picking backup up again with “AND backupset_inner.is_copy_only = 0 )”.
Shannon – Yep, the end of that line went AWOL. It’s been updated, thanks for pointing it out! 🙂
Hi Colleen
I completely disagree…you are definately way beyond Ola, etc. This is brilliant work and thank you so very much for posting it. Just one tiny favor. Is there a way to get all the code (sql and powershell) without the line numbers that need removing. I am anxious to “give it a whirl” but the thought of removing the line numbers is slowing my “whirling” down. Thanks in advance.
Thanks for the kind words, Bill. If you go to the Toolbox section of this site, you’ll find all the code ready for download. Hope that helps!
It does. Once I subscribed to your site….all became available….thank you so very much and please continue this truly outstanding work.
Thanks Colleen, this is awesome! I am having a bit of trouble though with primary key violations during execution. I think it has to do with the way I’ve entered the data into the Servers table. Can you describe what the data should look like for the server table?
Right now I’ve got “servername\instance” in both instance_name and instance_name_short fields and I have “servername” in both of the server fields.