- PowerShell is a procedural scripting language and the maintenance plan would have a few procedural steps and iterations through objects.
- PowerShell was also built on top of the .Net framework. This means I could make use of the SQL Shared Management Object (SMO) library within my script.
- SQL Server also has a SQL PowerShell provider, which is pre-loaded when you run SQLPS. Support also has been extended to SQL Server Agent as there is a native PowerShell, which uses SQLPS, job subsystem.
- I have been using PowerShell for a while and found it really useful; it allows you to solve problems, much more easily, than using the standard SQL tools.
The maintenance script, SQLServerMaintanceTask.PS1, does the following, depending of the day of execution:
- Removes the old backup files from the previous week.
- Backup the database by doing either a full backup or differential.
- Checks the fragmentation level of indexes and perform if required either the rebuild or reorganisation.
- Update any statistics which hadn't been updated.
- Defrag-SQLServer-Indexes: iterates through a database index objects performs the required rebuild or reorganised. (Defrag-SQLServer-Indexes.ps1)
- Update-SQLServer-DBStatistics: iterates through database statistics objects and performs an update is they are a day-old. (Update-SQLServer-DBStatistics.ps1)
- BackUp-Database / BackUp-DatabaseLog: performs a database backup / performs a database log backup. (BackUp-Database.ps1 / BackUp-DatabaseLog.ps1)
- Get-ManagedPassword / Set-ManagedPassword: functions used to create and store a password for the backup file. (ManagedPassword.ps1)
While working on previous projects I found I was always doing a common task / asking a common question, which was to get the file stats of a database. This task was easy to accomplish if there is only one database, but annoying if there were more than one. While working on the PowerShell SQL server maintenance script I also came up with this little function to help get the stats of all the databases on a server: Get-DBFileStats. (Get-DBFileStats.ps1)
There are some benefits using a PowerShell maintenance script:
- One it is easier to have a centralised maintenance server. As the scripts can connect to remote servers, currently only using Windows authentication, without the need to have a SQL Server Agent multi-server administration configured.
- Using a PowerShell script you're not tied into using SQL Server agent. The automation of the script can be done by using Windows Scheduler or some other scheduling component.
- PowerShell can interact with interact with the file system and SQL server within the same script.
- Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2
- Microsoft® SQL Server® 2008 R2 Shared Management Objects.
Also by using power shell the outputs can be stored easier to file in a number of formats: HTML,CVS or XML