PowerShell – Check The number of SQL Databases & Availability modes

We have taken on new Customers, some know what they have in their SQL Server Estate, Some Don’t. Typically when we onboard a new client, we get a list of server names and we agree to support them (or at least the sales team agrees and the database administrators then have to figure out what the client actually has on them.)

Enter Due Diligence Checks; this is a recent PowerShell script I’ve written (Work in progress) to check for the following things;

1. How many AvailblityGroups There are and the number of databases in the AAG’s
2. Clusters
3. How many databases are Mirroring
4. How many databases are LogShipping
5. If Replication is installed and how many publications it’s running
6. Total Number of databases on the instance
7. TotalDatabaseSize
8. OtherComponents (SSRS/SSAS/SSIS)

I’m all about being open source so if this script can help you great! if you want to make it better go ahead, but throw some suggestions in the comments so I can see your improvements! it requires a config csv and an input file for the server list.

This was a quick first draft so there are issues with it but the basics are there. – unfortunately, WordPress removed the formatting!

#####################################################
### ###
### 18/03/2019 Created by Luke Salter ###
### ###
#####################################################

###

## Declare Variables

###

$Instances = get-content “.\Instances.txt”

$Config = import-csv “.\config.csv”

$SendMailFrom = $Config.SendMailFrom

$SendMailTo = $Config.SendMailTo

$SendMailcc = $Config.SendMailCC

$UseMailServer = $Config.SMTPServer

$CustomerName = $Config.CustomerName

$reporttitle = “- Due Diligence Report”

$outputFile = “.\” + $CustomerName + “_DueDiligence.csv”

$myServerList = @()

###

## Functions

###

function AvailabilityGroups {

## Connects to SQL using SQLCMD and pulls back number of AAG’s & Databases in those AAG’s

$GLOBAL:AAG = “”

TRY {$GLOBAL:AAG= Invoke-Sqlcmd -Query “select cast((select count(distinct(group_id)) from sys.dm_hadr_database_replica_states) as varchar) + ‘ ‘ + ‘Availability Group(s)’ + ‘ ‘ + cast((select count(distinct(database_id)) from sys.dm_hadr_database_replica_states) as varchar) + ‘ ‘ + ‘Databases'” -ServerInstance $Instance -ErrorAction Stop}

CATCH {$GLOBAL:AAG = $GLOBAL:AAG | Add-Member @{Column1=””} -PassThru}

if($GLOBAL:AAG.Column1 -eq ‘0 Availability Group(s) 0 Databases’){$GLOBAL:AAG.Column1=”}

}

function Clustering {

$GLOBAL:Cluster=””

TRY {$GLOBAL:Cluster= Invoke-Sqlcmd -Query “declare @nodeList varchar(128) select @nodeList = coalesce(@nodeList+’, ‘,”) + NodeName from sys.dm_os_cluster_nodes select isnull(@nodeList,”)” -ServerInstance $Instance -ErrorAction Stop}

CATCH {$GLOBAL:Cluster= $GLOBAL:Cluster | Add-Member @{Column1=””} -PassThru}

}

function Mirroring {

$GLOBAL:Mirroring=””

TRY {$GLOBAL:Mirroring=Invoke-Sqlcmd -Query “select cast((select Count(Distinct(database_id)) from sys.database_mirroring where mirroring_role is not null) as varchar) + ‘ ‘ + ‘Databases'” -ServerInstance $Instance -ErrorAction Stop}

CATCH {$GLOBAL:Mirroring = $GLOBAL:Mirroring | Add-Member @{Column1=””} -PassThru}

if($GLOBAL:Mirroring.Column1 -eq ‘0 Databases’){$GLOBAL:Mirroring.Column1=”}

}

function LogShipping {

$GLOBAL:LogShipping=””

TRY {$GLOBAL:LogShipping=Invoke-Sqlcmd -Query “select cast((select count(Primary_Database) from msdb.dbo.log_shipping_primary_databases) as varchar) + ‘ ‘ + ‘Databases'” -ServerInstance $Instance -ErrorAction Stop}

CATCH {$GLOBAL:LogShipping = $GLOBAL:LogShipping | Add-Member @{Column1=””} -PassThru}

if($GLOBAL:LogShipping.Column1 -eq ‘0 Databases’){$GLOBAL:LogShipping.Column1=”}

}

function Replication {

$GLOBAL:Replication=””

TRY {$GLOBAL:Replication=Invoke-Sqlcmd -Query “select cast((select count(publication_id) from mspublications) as varcharr) + ‘ ‘ + ‘Publications'” -Server $Instance -Database DISTRIBUTION -ErrorAction Stop}

CATCH {$GLOBAL:Replication = $GLOBAL:Replication | Add-Member @{Column1=””} -PassThru}

}

function DatabaseCount {

$GLOBAL:DatabaseCount=””

TRY {$GLOBAL:DatabaseCount=Invoke-Sqlcmd -Query “select cast((select count(name) from sys.databases where name not in (‘master’,’msdb’,’tempdb’,’model’,’distribution’))as varchar) + ‘ ‘ + ‘Databases'” -ServerInstance $Instance -ErrorAction Stop}

CATCH {$GLOBAL:DatabaseCount = $GLOBAL:DatabaseCount | Add-Member @{Column1=””} -PassThru}

if($GLOBAL:DatabaseCount.Column1 -eq ‘0 Databases’){$GLOBAL:DatabaseCount.Column1=’No databases!’}

}

function TotalDatabaseSize {

$GLOBAL:TotalDatabaseSize=””

TRY {$GLOBAL:TotalDatabaseSize=Invoke-Sqlcmd -Query “select cast((select convert(decimal(10,2),(sum(size * 8.00) / 1024.00 / 1024.00)) from master.sys.master_files)as varchar) + ‘ ‘ + ‘GB'” -ServerInstance $Instance -ErrorAction Stop}

CATCH {$GLOBAL:TotalDatabaseSize= $GLOBAL:TotalDatabaseSize | Add-Member @{Column1=””} -PassThru}

}

function OtherComponents {

$Server=$Instance.Split(‘\’) ## Split for named instances

$GLOBAL:OtherComponents=”

TRY {

IF (Get-WMIObject -class Win32_Service -computerName $Server[0] -ErrorAction Stop | Where-Object { $_.name -like “*MSSQLServerOLAPService*” }) {$GLOBAL:OtherComponents = $GLOBAL:OtherComponents+ ‘ SSAS ‘}

IF (Get-WMIObject -class Win32_Service -computerName $Server[0] -ErrorAction Stop | Where-Object { $_.name -like “*MsDtsServer*” }) {$GLOBAL:OtherComponents = $GLOBAL:OtherComponents+ ‘ SSIS ‘}

IF (Get-WMIObject -class Win32_Service -computerName $Server[0] -ErrorAction Stop | Where-Object { $_.name -like “*ReportServer*” }) {$GLOBAL:OtherComponents = $GLOBAL:OtherComponents+ ‘ SSRS ‘}

}

CATCH{if(!$GLOBAL:OtherComponents){$GLOBAL:OtherComponents=’Unable To Connect’}}

}

###

## MAIN

###

foreach ($Instance in $Instances){

write-host “$Instance : Running…” -ForegroundColor “Green”

## Runs Functions

Push-Location

AvailabilityGroups

Clustering

Mirroring

LogShipping

Replication

DatabaseCount

TotalDatabaseSize

OtherComponents

Pop-Location

## Pool Function Results into Array

$myObj = “” | Select-Object InstanceName, SupportCover, SupportTime, ServerTier, Environment, AvailabilityGroups , Clustering, Mirroring, LogShipping, Replication, DatabaseCount, TotalDatabaseSize, OtherComponents

$myObj.InstanceName = $Instance

$myObj.SupportCover = ‘Comprehensive or Reactive’

$myObj.SupportTime = ’24/7/365 or Business Hours’

$myObj.ServerTier = ‘Production / Dev / QA / Test’

$myObj.Environment = ‘PaaS / IaaS / On-prem’

$myObj.AvailabilityGroups = $AAG.Column1

$myObj.Clustering = $Cluster.Column1

$myObj.Mirroring = $Mirroring.Column1

$myObj.LogShipping = $Logshipping.Column1

$myObj.Replication = $Replication.Column1

$myObj.DatabaseCount = $DatabaseCount.Column1

$myObj.TotalDatabaseSize = $TotalDatabaseSize.Column1

$myObj.OtherComponents = $OtherComponents

$myServerList += $myObj

}

## Output Array to CSV File

$myServerList | Export-CSV $outputFile -notypeinformation

write-host “File created!” -ForegroundColor “Green”

$subject = $customername

$subject += ” – “

$subject += $reporttitle

$emailoutput = “<font face=calibri><br>”

$emailoutput += $subject

$emailoutput += ” attached.<br><br><b>End Of Line.</b><br></font>”

Send-MailMessage -from $sendmailfrom -to $sendmailto -subject $subject -body $emailoutput -BodyAsHtml -dno onSuccess, onFailure -smtpServer $usemailserver -Attachments $outputFile

write-host “Email sent!” -ForegroundColor “Green”

Connecting to outlook via powershell

I had a requirement to remove e-mails from a mailbox without connecting to exchange, it turned out to be surprisingly simple!

# Initialise variables
$TargetMailbox = “SMB-JSS-CSMS Event Management Team”
$DateTime= Get-Date
$Date = $DateTime.AddDays(-90) -f “dd/MM/yyyy hh:m:ss”
$Foldername = “Archive2”

# Connect to Outlook
$Outlook = New-Object -comobject Outlook.Application

# Select the mailbox by name
$Mailbox = $Outlook.Session.Folders | Where-Object { $_.Name -eq $TargetMailbox}

# Select Inbox by name
$Archive = $Mailbox.Folders | Where-Object { $_.Name -eq $Foldername }

$ArchiveItems = $Archive.Items | Where-Object {$_.CreationTime -lt “$Date”}

# Delete Archive Items
$ArchiveItems | foreach {
write-host “Deleting Archive\$($_.subject) (Item)”
$_.Delete()
}

once $Outlook = New-Object -comobject Outlook.Application has been run you can use $Outlook.Session.Folders and pipe it through to select-object name to find the name of your mailbox if required.

you can then use $Mailbox.Folders and pipe this through to select-object name to find a specific folder! then it’s a case of pumping in any criteria for the removal i used $DateTime.AddDays(-90) for emails in the last 90 days!

PowerShell Functions

Functions are brilliant for script organization. It allows you to call script blocks multiple times, reducing the risk of making and error and cutting down on the amount of code writing to accomplish tasks. Not to mention it improves readability and makes complex PowerShell scripts manageable.

Function Syntax:
Function(keyword) FunctionName (parameters) {script block}

a simple example of creating a function;

Function Archive {
move-item $Files $Archive
}

This allows you to call and re-call this script block (Although quite simple) as many times as you want. you can use the is many ways, as well as changing the variables as you go!;

$Archive=”C:\Temp\Archive”
$Files=”C:\temp\*.txt”
Archive
$Files=”C:\temp\*.csv”
Archive

you can make your functions as long and as complicated as you like, and you can also embed functions within functions!

Function HouseKeeping {
$RemoveItems = Get-ChildItem $Archive -Recurse | Where-Object { ( ($_.LastWriteTime.Date -le (Get-Date).Date.AddDays(-10)))} | Remove-Item
}

Function Archive {
move-item $Files $Archive
HouseKeeping
}

so when i call the Archive function it will move my files to archive and then run a separate function “housekeeping” to remove files older than 10 days..

E-Mail via PowerShell With Attachments.

Here’s a nice snippet of code displaying how to send out an e-mail via PowerShell. The account running the PowerShellsession needs access to the “From” mail address.

$smtpServer = “Your SMTP Server”
$att = new-object Net.Mail.Attachment($ZIP)
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg.From = “Your@YourCompany.Something”
$msg.To.Add(“Your@YourCompany.Something”)
$msg.Subject = “Your Subject”
$msg.Body = “Your Message”
$msg.Attachments.Add($att)
$smtp.Send($msg)
$att.Dispose()

Alias a Property from Select-Object

i used the same hashtable syntax in a previous post “Selecting an object from a property” where i modified the “Expression” into a different select statement. i would just like to expand on this slightly just to show another way this can be used;

Aliasing;

@{name=”YourName”;expression={$_.Name}}

The hashtable syntax adds great utility to select-object. This allows you to rename the property you are selecting to give a more user friendly output. i, for example am using in combination with out-file to give a clearer set of log files;

Get-Item “$Direcotry” | Select-Object @{name=”Files in $Directory”;expression={$_.Name}}

Setting $Directory to C:\Temp will list the files in C:\Temp whilst putting the “Name” object title as “Files in C:\Temp” this gives an output as follows;

Files in C:\Temp
—————————————————
New Text Document.txt
New Text Document(2).txt

Obviously then you can use Out-File to throw the results into a file or whatever!

Learning PowerShell

TechEd 2014

i think the first 5-6 mins of this video is very important. they come out with some pretty important comments;

The ability to discover whats in there, if you’re not willing to discover or play a little bit you probably wont be successful at PowerShell.

when you’re sitting there and your struggling to figure it out, don’t think “I’m a dope” or “this thing sucks”, that’s the process! you figure it out!

“Whats your favorite Command?” “Get-Help” – I totally support this, there’s no way i could remember the syntax of each cmdlet in each module. i also love “-verbose”. import your module with the verbose switch and you can see all the cmdlet’s it’s importing, you can then get-help on a cmdlet let of your choice then you can start constructing your script!

Useful start commands;

help get-help upgrade-help
get-command show-command
get-member show-object
get-psdrive

i would recommend watching this video if you’re starting PowerShell. they go through some useful routines of figuring out how to do things but really PowerShell has a massive community and everyone is willing to help you out, improve your code, solve your problems. i would say go off and understand how variables and arrays work if you don’t know! i use arrays/variables in almost everything i do.

Get in front of a machine and figure it out!

Selecting an object from a property

This is a nice little piece of power shell taken from “PowerShell PowerUsers”

Get-ADUser -filter * -properties SAMACCOUNTNAME,Manager -SearchBase “OU=Manchester,OU=EH Users,DC=english-heritage,DC=org,DC=uk” | Select-Object SAMACCOUNTNAME, @{Name=”Manager”; Expression={(Get-ADUser $_.Manager).Name}}

the section highlighted in bold is very powerful, this could be used to pull back any object by specifying the property you want to look at, in this case we are looking at another AD User which is the manager of our initial user!

 

Powershell – AD Group Creation

Here is the finalized script for the Active Directory Group creation. This was used to create 194 AD Groups for our SCCM Environment. users will be added via a different script.
This creates both Domain Local and Global Groups. — See Attached.

ADAccounts.ps1

Powershell Read a CSV File into an array

To give you an idea of how powershell imports and stores a CSV File in an array i have provided the 3 following images, this gives a clear break down of a basic way to read the CSV display the array’s it has stored and how to call the data in that array.

import-csv “C:\Temp\PowerShell\ADMembers\Members.CSV”

This is the command we use to import the CSV File, Powershells stores this information, you can either store it in a variable OR you can work with it on the fly.

ARRAY/VARIABLE

$Membership = import-csv “C:\Temp\PowerShell\ADMembers\Members.CSV”

Here you can see we are loading the entire CSV into $MEMBER from this we can display the columns by declaring the column name after the variable for example;

$Membership.APP
$Membership.User

From here you can use the data in many ways, it all depends on what you want to do…

On The Fly
My ultimate goal for working with this CSV file was to Add users to groups in Active Directory we had an csv file that was provided from another database to work with, it was given in the following format;

APP,User
Adobe,User1
Adobe,User2
Adobe,User3
Office,User1
Office,User4
Office,User2
Flash,User4
Flash,User3

For this i used the foreach loop;

import-csv “C:\Temp\PowerShell\ADMembers\Members.CSV” |
foreach-object {“Adding {1} to {0}” -f $_.app,$_.user}

We read the CSV As normal and then Pipe the CSV into the Foreach command,
$_ uses the current row of the array…
-f enables us to call out each column independantly, these are decalred after the -f, SO;
$_(Current Row).APP(Column) is set as {0} AND $_(Current Row).user(Column) is set as {1}
This little peice of script gives the following result;

Obviously from here we just need to write the section to add the users into the correct group, this is covered in a previous post.

Starting a remote PowerShell Session

Here’s a handy comand for starting a powershell session on a remote computer and passing through variables/running commands.

$CMSession = New-PSSession -computerName XXXXX -ConfigurationName Microsoft.PowerShell32
Invoke-Command -Session $CMSession -Argu $AppName -Scriptblock {param ($AppName)

}
Remove-PSSession $CMSession

“-Argu $AppName -Scriptblock {param ($AppName)” Allows you to pass through variables – $AppName being the variable to passthrough in this instance.

*** NOTE: The “New-PSSession” cmdlet is the best way of passing the commands to a remote comptuer as a script. if you wish to do it as a remote session in a powershell console you can use;

Enter-PSSession -ComputerName XXXXX -ConfigurationName Microsoft.PowerShell32