Using PowerShell to Manage SQL Server
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:
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
SQL Server 2008 Feature Pack
Server PowerShell Extensions – version 2.3.1
- Set PowerShell execution policy
to remotesigned. Launch PowerShell and type Set-ExecutionPolicy
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).
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
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.
Pages: 1 2