- \n
- Unzip the file downloaded from GitHub into a temp directory.<\/li>\n
- Open PowerShell and navigate to the Modules folder of the unzipped folder.\n
cd C:\\temp\\SQLPSX-master\\SQLPSX-master\\Modules <\/pre>\n<\/li>\n
- Run this command to unblock the files.\n
get-childitem -Recurse | unblock-file<\/pre>\n<\/li>\n
- Copy the contents of the Modules folder from temp into the Modules folder in the $PSHome path.<\/li>\n<\/ol>\n\n
NOTE: Always be sure to fully review any PowerShell modules or scripts before executing them.<\/em><\/p>\n<\/div>\n
For a good understanding of the profiles you can have in PowerShell, review this article: Understanding the Six PowerShell Profiles<\/a>.<\/p>\n
Playing with SQLPSX<\/h2>\n
To use the module, you must import it:<\/p>\n
Import-Module sqlpsxserver <\/pre>\n
Then you will have access to all the features of this powerful library:<\/p>\n
get-command -Module sqlpsxserver <\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/10\/word-image-146.png\")
<\/p>\nYou can see from the list that there is a wealth of commands for managing SQL Server. To get the information about a SQL Server instance run:<\/p>\n
Get-SqlServer -sqlserver <server\\instance> <\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/10\/word-image-147.png\")
<\/p>\nGathering Information from SQL Server<\/h2>\n
Several commands allow you to gather information from the instance:<\/p>\n
<\/p>\n
\n\n
\n \n Command<\/h3>\n<\/td>\n
\n Description<\/h3>\n<\/td>\n<\/tr>\n
\n \n get-sqlprocess <server\\instance> <\/pre>\n<\/td>\n
\n Get the server process<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-SqlVersion -sqlserver <server\\instance> <\/pre>\n<\/td>\n
\n Check the version<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-SqlServer -sqlserver <server\\instance> | Select -ExpandProperty Databases <\/pre>\n<\/td>\n
\n Return a list of the databases<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-SysDatabases -sqlserver <server\\instance> <\/pre>\n<\/td>\n
\n Return the system databases<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-SqlServer -sqlserver <server\\instance> |\r\nSelect -ExpandProperty Databases | \r\nWhere-Object {$_.name -eq 'Master'} |\r\nSelect * <\/pre>\n<\/td>\n\n Return all the information about the Master database<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-SqlServer -sqlserver <server\\instance> |\r\nSelect -ExpandProperty Databases | \r\nWhere-Object {$_.name -eq 'Master'} |\r\nSelect -ExpandProperty tables <\/pre>\n<\/td>\n\n Return a list of the tables in Master<\/p>\n<\/td>\n<\/tr>\n
\n \n get-SqlpsxDatabase -sqlserver <server\\instance> -dbname <database> | Get-SqlTable<\/pre>\n<\/td>\n
\n Check for tables<\/p>\n<\/td>\n<\/tr>\n
\n \n get-SqlpsxDatabase -sqlserver <server\\instance> -dbname <database> | Get-SqlStoredProcedure<\/pre>\n<\/td>\n
\n Check for procedures<\/p>\n<\/td>\n<\/tr>\n
\n \n get-SqlpsxDatabase -sqlserver <server\\instance> -dbname <database> | Get-sqlview <\/pre>\n<\/td>\n
\n Check for views<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-SQLPSXDatabase <server\\instance> <database> | Get-SqlTable | Get-SqlScripter <\/pre>\n<\/td>\n
\n Creating the script of all the tables in the database<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-SqlServerPermission -sqlserver <server\\instance><\/pre>\n<\/td>\n
\n Check the permissions<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-sqllogin -sqlserver <server\\instance> <\/pre>\n<\/td>\n
\n List the logins<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-InvalidLogins -sqlserver <server\\instance> <\/pre>\n<\/td>\n
\n Check for invalid logins (from restore)<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-SQLErrorLog -sqlserver <server\\instance> -lognumber 1 <\/pre>\n<\/td>\n
\n Check error Log<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-SqlServerRole <server\\instance><\/pre>\n<\/td>\n
\n Return the server roles<\/p>\n<\/td>\n<\/tr>\n
\n \n $server = Get-SqlServer -sqlserver <server\\instance>\r\n$server.Configuration.XPCmdShellEnabled.ConfigValue\r\n$server.Configuration.XPCmdShellEnabled.RunValue <\/pre>\n<\/td>\n
\n Check to see if XP_CMDSHELL is enabled<\/p>\n<\/td>\n<\/tr>\n
\n \n $server = Get-SqlServer -sqlserver <server\\instance>\r\n$server.Configuration.DefaultBackupCompression.ConfigValue\r\n$server.Configuration.DefaultBackupCompression.runvalue<\/pre>\n<\/td>\n
\n Check the default backup compression setting<\/p>\n<\/td>\n<\/tr>\n
\n \n $server = Get-SqlServer -sqlserver <server\\instance>\r\n$server.Configuration.PriorityBoost.ConfigValue\r\n$server.Configuration.PriorityBoost.runvalue <\/pre>\n<\/td>\n
\n Check the priority boost setting<\/p>\n<\/td>\n<\/tr>\n
\n \n $server = Get-SqlServer -sqlserver <server\\instance>\r\n$server.Configuration.MaxServerMemory.ConfigValue\r\n$server.Configuration.MaxServerMemory.RunValue<\/pre>\n<\/td>\n
\n Check the max server memory value<\/p>\n<\/td>\n<\/tr>\n
\n \n $server = Get-SqlServer -sqlserver <server\\instance>\r\n$server.Configuration.MaxDegreeOfParallelism.ConfigValue\r\n$server.ConfigurationMaxDegreeOfParallelism.runvalue <\/pre>\n<\/td>\n
\n Check the max degree of parallelism setting<\/p>\n<\/td>\n<\/tr>\n
\n \n get-agentjob <server\\instance> |\r\n Where-Object {$_.lastrunoutcome -eq 'Failed'} <\/pre>\n<\/td>\n\n Get a list of failed SQL Agent jobs<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
You can control the output of each command by using the format-table or format-list cmdlets, for instance:<\/p>\n
get-agentjob <server\\instance> | Where-Object {$_.lastrunoutcome -eq 'Failed'} |Format-list\r\nget-agentjob <server\\instance> | Where-Object {$_.lastrunoutcome -eq 'Failed'} | SELECT Parent, Name, LastRunDate | Format-table<\/pre>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/10\/word-image-148.png\")
<\/p>\nSQL Maintenance with SQLPSX<\/h2>\n
DBAs should be spending time on more important work, not manually repeating the same tasks day after day. Automating much of this work using the SQLPSX library is possible. Here is a list of useful commands to make changes or perform maintenance:<\/p>\n
\n\n
\n \n Command<\/h3>\n<\/td>\n
\n Description<\/h3>\n<\/td>\n<\/tr>\n
\n \n Add-SqlLogin -sqlserver <server\\instance> -name <loginname> -password <password>\r\n -logintype SqlLogin -DefaultDatabase <database><\/pre>\n<\/td>\n
\n Adding a new login<\/p>\n<\/td>\n<\/tr>\n
\n \n Add-SqlUser -sqlserver <server\\instance> -dbname <database> -name <username>\r\n -login <loginname><\/pre>\n<\/td>\n
\n And add a user with an existing login<\/p>\n<\/td>\n<\/tr>\n
\n \n Remove-SqlUser -sqlserver <server\\instance> -dbname <database>\r\n -name <username><\/pre>\n<\/td>\n
\n Or remove a user<\/p>\n<\/td>\n<\/tr>\n
\n \n Remove-SqlLogin -sqlserver <server\\instance> -name <loginname> <\/pre>\n<\/td>\n
\n And remove a login<\/p>\n<\/td>\n<\/tr>\n
\n \n get-SqlpsxDatabase -sqlserver <server\\instance> -dbname <database> | \r\nGet-SqlTable -name <table> | \r\nGet-SQLIndex | \r\nGet-SQLIndexFragmentation | \r\nWhere-Object {$_.AverageFragmentation -ge 50} | \r\nSelect-Object index_Name,\r\n Pages,\r\n Rows,\r\n IndexType,\r\n AverageFragmentation <\/pre>\n<\/td>\n\n Checking index fragmentation > 50% in one table<\/p>\n<\/td>\n<\/tr>\n
\n \n get-SqlpsxDatabase -sqlserver <server\\instance> -dbname <database>| \r\nGet-SqlTable -name <table> | \r\nGet-SQLIndex | \r\nInvoke-SqlIndexDefrag \r\nget-SqlpsxDatabase -sqlserver <server\\instance> -dbname <database> | \r\nGet-SqlTable -name <table> | \r\nGet-SQLIndex | \r\nInvoke-SqlIndexRebuild <\/pre>\n<\/td>\n
\n And if it is bad, invoke a rebuild or reorg<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-SQLPSXDatabase <server\\instance> <database> | \r\nGet-SqlTable -name <table> |\r\nGet-SqlStatistic <\/pre>\n<\/td>\n
\n Or get the statistics<\/p>\n<\/td>\n<\/tr>\n
\n \n Get-SQLPSXDatabase <server\\instance> <database> | \r\nGet-SqlTable -name <table> | \r\nGet-SqlStatistic | \r\nUpdate-SqlStatistic <\/pre>\n<\/td>\n
\n And update statistics<\/p>\n<\/td>\n<\/tr>\n
\n \n Add-SqlDatabase -sqlserver <server\\instance> -dbname <database> <\/pre>\n<\/td>\n
\n Add a new database<\/p>\n<\/td>\n<\/tr>\n
\n \n Remove-SqlDatabase -sqlserver <server\\instance> -dbname <database><\/pre>\n<\/td>\n
\n Remove a database<\/p>\n<\/td>\n<\/tr>\n
\n \n get-SqlpsxDatabase -sqlserver <server\\instance> -dbname <database> | Invoke-SqlDatabaseCheck <\/pre>\n<\/td>\n
\n Perform a database check<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Send Results in an Email<\/h2>\n
You may have a process that returns the last backup date of the databases, and the output is using
write-host<\/code>, but what I have is a procedure that returns the last backup date of my databases and sends it by email to me.<\/p>\nFirst export the list of backups to a CSV file and then email as an attachment.<\/p>\n
get-SqlpsxDatabase -sqlserver DESKTOP-SQEVVO1\\SQL2017 | \r\nSelect name, lastbackupdate |\r\nexport-csv c:\\temp\\lastbackup.csv -NoTypeInformation -noclobber\r\n$SmtpServer = 'smtp.live.com'\r\n$SmtpUser = 'youremail@outlook.com'\r\n$smtpPassword = 'youroutlookpasword'\r\n$MailtTo = 'youremail@outlook.com'\r\n$MailFrom = 'youremail@outlook.com'\r\n$MailSubject = \"Last Backup\" \r\n$Credentials = New-Object System.Management.Automation.PSCredential -ArgumentList $SmtpUser, $($smtpPassword | ConvertTo-SecureString -AsPlainText -Force) \r\nSend-MailMessage -To \"$MailtTo\" -from \"$MailFrom\" -Subject $MailSubject -SmtpServer $SmtpServer -UseSsl -Credential $Credentials -Attachments c:\\temp\\lastbackup.csv <\/pre>\n
Automating the Daily Checklist with SQLPSX<\/h2>\n
As you can see from the examples shown here, you can gather a large amount of information from SQL Server and perform many maintenance tasks using this library. One of the daily tasks of the DBA in charge is the daily checks. Every morning, by reviewing these, you have a way to guarantee that your environment is online and available. In the days of Kubernetes, Docker, CDI CDR, and large estates, DBAs should not do this kind of job manually. Well, in this article I will show how to automate some of those daily checklists.<\/p>\n
Setting Up<\/h3>\n
Create a folder called c:\\checklist<\/em> and a text file called InstanceNames.txt<\/em> with all the SQL Servers listed. Create a text file called hostnames.txt<\/em> with the name of the servers (instead of SQL Server instances here, add hostnames of the servers). The folder also will accommodate all the CSV files that will be created. The text file instance names are the SQL Server instance names to be checked, and the hostnames are the servers that need checks like disk space.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/10\/word-image-149.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/10\/word-image-150.png\")
<\/p>\nFailed Jobs<\/h3>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/10\/word-image-151.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/10\/word-image-152.png\")
<\/p>\n