Microsoft® SQL Server® 2012 Bible

785

Chapter 30: Confi guring and Managing SQL Server with PowerShell

30

example, to navigate through the SQL Server objects. The URN for the HumanResources

.Employee table in AdventureWorks2012 on SQLTWSS\INST01 is as follows:

Server[@Name='SQLTWSS\INST01']\Database[@Name='AdventureWorks2012']\

Table[@Name='Employee' and @Schema='HumanResources']

Converting that to a path using Convert-UrnToPath would yield the following:

SQLSERVER:\SQL\SQLTWSS\INST01\Databases\AdventureWorks2012\

Tables\HumanResources.Employee

Don’t Use SQLPS.exe

SQL Server 2008 incorporated PowerShell into its management toolset. Microsoft created a special

version of PowerShell (1.0) called sqlps.exe that includes the provider and preloads all the DLLs that

the provider requires, including the DLLs for SMO. Another difference between standard PowerShell

and sqlps.exe is that the execution policy of PowerShell in sqlps.exe is set to RemoteSigned. This

means that as soon as SQL Server 2008 is installed, sqlps.exe is ready to run scripts (on the local sys-

tem, at least). Unfor tunately, this closed shell approach has already been depricated and is continuing

to be provided only for backward compatibility.

Communicating with SQL Server via SMO

The two main reasons for communicating with SQL Server are to manage the server and to

use the data contained on the server in some way. Not only are administrators expected

to manage the server effi ciently, but they’re also frequently asked to extract some corpo-

rate data to send to another application, to make quick updates to correct a problem, or to

respond to other such requests. SQL Server management can be done from PowerShell or

any other .NET language using the Server Management Objects library, and the data can be

accessed using ADO.NET.

SQL Server Management Objects

SQL Server Management Objects (SMO) and its related sisters (RMO for Replication

Management Objects and AMO for Analysis Services Management Objects) are object librar-

ies that provide a programmatic way to manage Microsoft SQL Server. SMO can be used to

manage SQL Server 2005, 2008, 2008 R2, and 2012. It was introduced with SQL Server 2005

but supports the management of SQL Server 2000 instances as well. SMO was built using the

.NET Framework, so the objects it exposes are available in PowerShell.

c30.indd 785c30.indd 785 7/31/2012 9:46:23 AM7/31/2012 9:46:23 AM

http://www.it-ebooks.info