Using PowerShell to Manage SQL Server

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:

  1. Windows
    PowerShell 2.0 – http://support.microsoft.com/kb/968929
  2. 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
  3. SQL
    Server PowerShell Extensions – version 2.3.1
  4. 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.

Continues…

Pages: 1 2




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |