782
Part V: Enterprise Data Management
tasks such as running queries and backing up databases. On the other end of the spectrum
(for the vast majority of DBAs) is the need to learn and leverage the SMO.The easiest way to get comprehensive abilities to work with SQL Server from PowerShell
is to download the SQL Server PowerShell Extensions project from CodePlex ( http://
sqlpsx.codeplex.com ). As of this writing SQLPSX comes with 163 advanced functions,
2 cmdlets, and 7 scripts. The download includes an installer to streamline the process. If
you want to (or need to for security reasons) you can download just the source code, which
is simply a bunch of text fi les.Importing Modules
Starting with PowerShell 2.0 you can extend the native capabilities of PowerShell by
importing modules. With SSMS 2012 the PowerShell functionality of SQL Server is fi nally
available in module form. When working with the relational engine and its various compo-
nents, you use the command IMPORT-MODULE SQLPS. When working with SSAS you use
IMPORT-MODULE SQLASCMDLETS.You need to import only a module once per PowerShell session; however, if you do acciden-
tally import a module a second time, it won’t throw an error like Snapins did. To see a list
of all modules available on your machine, run Get-Module -ListAvailable.The SQL PSDrive — SQLSERVER
The SQLSERVER PSDrive provides a wonderful way to programmatically access informa-
tion from your SQL Server instances. The great thing is that you are already familiar with
much of the hierarchy within the SQLSERVER PSDrive because it uses the same SMO tree
that you use in Object Explorer in SSMS.Native PowerShell provides the ability to navigate not only the disk fi le system, but also
the system Registry as though it were a fi le system. The SQL Server provider adds a new
PowerShell drive, also referred to as a PSDrive, called SQLSERVER:. Use of the
Set-Location cmdlet (usually aliased as cd) can be used to change to the SQLSERVER:
drive, and then SQL Server can be navigated like the fi le system. However, due to severe
performance limitations, this approach should be avoided if possible. Instead keep your
directory set to something basic such as C:\temp and run commands specifying the path of
the database object in question.There are nine main directories under SQLSERVER: DAC, DataCollection, SQL, SQLAS,
SQLPolicy, SQLRegistration, SSIS, Utility, andXEvent:■ (^) The SQL folder provides access to the database engine, SQL Server Agent, Service
Broker, and Database Mail, all using the various SMO DLLs.
■ (^) The SQLRegistration folder enables access to the Registered Servers and Central
Management Server.
c30.indd 782c30.indd 782 7/31/2012 9:46:22 AM7/31/2012 9:46:22 AM
http://www.it-ebooks.info