The purpose of this article is to demonstrate a custom scripting framework which enables the management of SQLServer databases from a Windows PowerShell session and also to extend Powershell’s flexibility by creating a set of commands customized to our needs. I will call this framework comprised of a library of modules and functions specialized to manage SQLServer the DBA profile.
Requirements for the PowerShell DBA Profile:
- Windows PowerShell 2.0 – http://support.microsoft.com/kb/968929
- SMO – This step is unnecessary if SQL Server Management Studio is installed (SMO is included) otherwise SMO is available as separate download as part of the Microsoft SQL Server 2008 Feature Pack
- SQL Server PowerShell Extensions – version 2.3.1
- Set PowerShell execution policy to remotesigned. Launch PowerShell and type Set-ExecutionPolicy RemoteSigned
SQLPSX
SQLPSX is a project maintained by CodePlex at http://sqlpsx.codeplex.com and its primary goal is to provide intuitive functions around the SMO objects which are backward compatible to SQL Server 2000. A secondary goal is to provide an application which reports security settings. SQLPSX offers us a rich library with many ready-to-use functions and modules which give us the ability to build custom functions by consuming or extending them and thus create a rich set of custom commands.
Version 2.3.1 can be downloaded as a MSI file or zip file. I prefer the zip version in order to freely handle all of the files (the folders of the are shown in the below screenshot).
Folders
representing each module contained in SQLPSX_2.3.1.zip file.
I suggest you extract all files to a central location if you intent to maintain the DBA profile regardless of the user account. In this case, extract the Modules folder to the default WindowsPowershell folder located under Windows folder C:WindowsSystem32WindowsPowerShellv1.0Modules. For example, adoLib should be copied to C:WindowsSystem32WindowsPowerShellv1.0ModulesadoLib. In order to verify modules are available, type get-module –listAvailable. The next step is to import all modules one by one; I will be showing this step in the next topic “loading the DBA Profile”.
List of available modules by executing
the get-module command.
Loading the DBA profile
The DBA profile represents a tool box specialized in handling SQLServer instances and databases under a PowerShell session, and loading it gives the session the ability to use all its database tools. The profile is maintained in the C:WindowsSystem32WindowsPowerShellv1.0 folder. This folder is the primary location where Windows PowerShell looks for its default profile; it is considered the root folder for any session, even if you are using PowerShell 2.0.
All 3 PowerShell scripts used in this example can be downloaded here
As with the SQLPSX “Modules”, we will create a folder named DBA in the PowerShell root folder – C:WindowsSystem32WindowsPowerShellv1.0DBA. In DBA folder will reside a script file named DBAprofile.ps1 and a folder named Library.
The DBA profile session will be made up by DBAprofile.ps1 and Library folder.
The library folder contains all the custom functions required to create the commands called during the use of our Windows PowerShell’s DBA session. In this folder I maintain two main custom libraries: CustomLibraryWMI.ps1 and CustomSqlLibrary.ps1. As names suggest, I use the CustomLibraryWMI file to maintain my custom functions that make use of WMI API, this library specializes to manage information and operations related to the SQL Server instance such as server memory, local time, server uptime, server description, SQL services status and so on. The CustomSqlLibray contains my custom functions that focus on SQLServer management from the SMO perspective, from handling a simple database to the entire SQL Agent . The DBAProfile.ps1 will load the SQLPSX modules and our DBA custom functions.