on 26-Aug-2020 (Wed)

• If someone changes some setting or configuration on one of your servers, how quickly will you know about it?
• Do you have a list of databases with all their properties?
• When do the SQL Server agent jobs run?
• How would you find out the Edition of each of your SQL Server instances, and the hardware on which it is running?
• Do you know all the SQL Server logins and users with all their permissions?
• If asked to setup a new server, in an identical configuration to an existing server, how quickly could you do it?

Good documentation gives you control over your environment, and is an effective communication tool. A few examples of situations where documentation is important:

• Create baselines – gather performance information from your instance. A baseline can also be an initial configuration of your SQL Server instance installation.
• Comparing different servers – server documentation allows you to enforce standards. If someone sets up a server in a non-standard fashion, or changes some setting, it could cause unexpected behavior and/or performances issues, so you need to know about it (and ideally be alerted to the change)
• Database settings – creating an overview of all the databases with their properties makes it possible to see if the database has grown. Another reason would be to see if there are settings in the databases which are non-standard. For instance, which database run in SIMPLE recovery mode, which databases have a page verification option set to NONE or TORN_PAGE_DETECTION.
• License audits – Documenting your SQL Server editions and hardware will ensure that your organization is always license compliant.
• Security audits – If you work in a bank or other financial institution you are probably familiar with security audits. Can you show the auditor a list of all the people who have access to the database? Has there been any change in the last 6 months? How do you prove you are in control?
• Troubleshooting -the first question you ask in response to a performance issue is often “what changed?” It’s much harder to troubleshoot a performance issue without any server documentation.

At a minimum, I’d say you need to document the following for every server:

• Machine information
  • Memory – Total memory, available memory.
  • CPU – Amount of CPUs, amount of cores, architecture
  • OS – Architecture, version, service pack, build
  • Disks – Disk, name, size, free space
• SQL Server engine
  • Configuration – Min and max memory, clr enabled, xp_cmdshell enabled
  • Edition – Edition, build
  • Collation – Default collation
  • Logins – Present logins with server roles involved
• Databases
  • Size – Size of database files, space available
  • File configuration – Location of files. Different drives for data and log?
  • Growth configuration – Growth settings for data and log files.
  • Collation – Collation of the database. Is it different from the default server collation?
  • Users – Created users and their database roles.
• SQL Agent
  • Jobs – The job and job steps.
  • Schedules – Schedules for jobs, next run date and time.

PSSQLLIB: Custom PowerShell Functions to Return Server Information

I’d rather program for a few hours than do anything twice, so I created a PowerShell module called PSSQLLib that has all the functions necessary to retrieve the following information:

• Host hard disk information
• Host hardware
• Host operating system information
• SQL Server instance settings
• SQL Server instance configuration settings
• SQL Server login server privileges
• SQL Server databases
• SQL Server database files
• SQL Server database users
• SQL Server database user privileges
• SQL Server Agent jobs
• SQL Server disk latencies

function Load-Assembly
		{
		    <# 
		    .SYNOPSIS
		        Check if a assembly is loaded and load it if necessary
		    .DESCRIPTION
		        The script will check if an assembly is already loaded.
		        If it isn't already loaded it will try to load the assembly
		    .PARAMETER  name
		        Full name of the assembly to be loaded
		    .EXAMPLE
		        Load-Assembly -name 'Microsoft.SqlServer.SMO'
		    .INPUTS
		    .OUTPUTS
		    .NOTES
		    .LINK
		    #>
		     param(
		          [Parameter(Mandatory = $true)][ValidateNotNullOrEmpty()]
		          [String] $name
		     )
		     
		     if(([System.AppDomain]::Currentdomain.GetAssemblies() | where {$_ -match $name}) -eq $null)
		     {
		        try{
		            [System.Reflection.Assembly]::LoadWithPartialName($name) | Out-Null
		        } 
		        catch [System.Exception]
		        {
		            Write-Host "Failed to load assembly!" -ForegroundColor Red
		            Write-Host "$_.Exception.GetType().FullName, $_.Exception.Message" -ForegroundColor Red
		        }
		     }
	} 

function Get-SQLServerPrivileges
		{
		    <# 
		    .SYNOPSIS
		        Returns each server login with their server roles
		    .DESCRIPTION
		        This function will return all the logins on the database server
		        and check whether they are member of a server role.
		    .PARAMETER  instance
		        This is the instance that needs to be connected
		    .EXAMPLE
		        Get-SQLServerPrivileges "SQL01"
		    .EXAMPLE
		        Get-SQLServerPrivileges "SQL01\INST01"
		    .EXAMPLE
		        Get-SQLServerPrivileges -inst "SQL01\INST01"
		    .INPUTS
		    .OUTPUTS
		        System.Array
		    .NOTES
		    .LINK
		    #>
		    
		    param
		    (
		        [Parameter(Mandatory = $true, Position=1)]
		        [ValidateNotNullOrEmpty()]
		        [string]$inst = $null
		    )
		    
		    # Check if assembly is loaded
		    Load-Assembly -name 'Microsoft.SqlServer.SMO'
		 
		    # Check if the instance object is already initiated
		    if($server -eq $null)
		    {
		        try{
		            $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $inst
		        }
		        catch [Exception]
		        {
		            Write-Host "$_.Exception.GetType().FullName, $_.Exception.Message" -ForegroundColor Red
		        }
		    }
		 
		    # Create the result array
		    $result = @()
		 
		    # Create the array for the server roles
		    $serverRoles = @()
		 
		    # Get all the logins
		    $logins = $server.Logins
		 
		    # Loop through the logins
		    foreach($login in $logins)
		    {
		        
		        if(($login.Name -notlike "##*"))
		        {
		            # Get all the server
		            $serverRoles = ($login.ListMembers()) -join ","
		 
		            # Make the result
		            if($serverRoles.Count -gt 1)
		            {
		                $result += $login | Select `
		                             Name,LoginType,CreateDate,DateLastModified,IsDisabled,`
		                             @{N="ServerRoles";E=([string]::Join(",", $serverRoles))} | Sort-Object Name 
		            }
		            else
		            {
		                $result += $logn | Select `
		                             Name,LoginType,CreateDate,DateLastModified,IsDisabled,`
		                             @{N="ServerRoles";E={$serverRoles}} | Sort-Object Name 
		            }
		 
		            # Clear the array
		            $serverRoles = @()
		        }
		    }
		 
		    return $result
		 
	} 

[default - edit me]

. At the start of the function, we supply a parameter with the name of the SQL Server instance, and call the Load-Assembly function with the name of the assembly:

param
		    (
		        [Parameter(Mandatory = $true, Position=1)]
		        [ValidateNotNullOrEmpty()]
		        [string]$inst = $null
		    )
		 
# Check if assembly is loaded
Load-Assembly -name 'Microsoft.SqlServer.SMO' 

Before we can retrieve any information from the server we have to create a SMO Server object:

  # Check if the instance object is already initiated
if($server -eq $null)
  {
    try{
	  $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $inst
	}
	catch [Exception]
	{
	  Write-Host "$_.Exception.GetType().FullName, $_.Exception.Message" -ForegroundColor Red
	}
}

CONTEXT: Get-SQLServerPrivileges

How to iterate through the login values ?

 # Get all the logins
$logins = $server.Logins
foreach($login in $logins)
{
  if(($login.Name -notlike "##*"))
  {
    # Get all the server
    $serverRoles = ($login.ListMembers()) -join ","
		 
	# Make the result
	if($serverRoles.Count -gt 1)
	{
	  $result += $login | Select `
	  Name,LoginType,CreateDate,DateLastModified,IsDisabled,`
	  @{N="ServerRoles";E=([string]::Join(",", $serverRoles))} | Sort-Object Name 
	}
	else
    {
	  $result += $login | Select `
	  Name,LoginType,CreateDate,DateLastModified,IsDisabled,`
	  @{N="ServerRoles";E={$serverRoles}} | Sort-Object Name 
	}
		 
    # Clear the array
	  $serverRoles = @()
    }
}
		 
return $result
		 
} 

To get the SQL Server logins, and associated server roles, we call the Logins property from the server object and save the values to the $logins variable, and then iterate through these login values (excluding system accounts, which start with “##”). If the login is a member of Windows group we create a comma separated list of all the server roles of which that group is a member. Finally, we clear the array of the server roles to be sure the data is correct, and then return the result.

The idea is to automate the documentation of multiple SQL Server instances. To make this possible I’ll create a function that takes an argument for the server name, the instance name, and the destination to which to export the Excel file.

function Generate-Documentation()
{
  param(
   [Parameter(Mandatory=$true, Position=1)]
   [ValidateNotNullOrEmpty()]
   [string]$server,
   [Parameter(Mandatory=$true, Position=2)]
   [string]$instance = '',
   [Parameter(Mandatory=$true, Position=3)]
   [ValidateNotNullOrEmpty()]
   [string]$destination
) 

The name of the default instance can be “MSSQLSERVER” or be left out. To make sure the server object is created in the same way for both situation the following code will check the name and declare the target variable.

# Make up the target in case we have a default instance
if(($instance -eq '') -or (($instance).ToUpper() -eq 'MSSQLSERVER'))
{
  $target = $server
} 
else
{
  $target = "$server\$instance"
} 

How to specifies the details of the output Excel file ?

- server name

- instance name

- timestamp

The next section of code specifies the details of the output Excel file. It will take the name of the server, the name of the instance and put a timestamp on the file. The timestamp is in the file is important to know when the file was created especially when you document your servers regularly.

# Create timestamp
$timestamp = Get-Date -f yyyyddMMHHmmss
		 
# Set the destination
$outputFile = $destination + '\MachineInformation_' + ($server).ToUpper() + '_' + ($instance).ToUpper() + '_' + $timestamp + '.xlsx'