SQL Server 2012 Integration Services – Introduction to Managing SQL Server with PowerShell

It is rather obvious to anyone who follows manageability trends that Windows Powershell has emerged as the primary scripting and automation technology, relevant in practically the entire portfolio of Microsoft server-based products. One of many benefits of this trend was the development of new ways of dealing with the most common administrative tasks in SQL Server 2012 Integration Services. The purpose of this article is to present the fundamental principles of PowerShell in the context of SQL Server 2012, step through its initial setup and configuration, and review specific management areas where its advantages can be realized.

The strength of PowerShell results mainly from its object-oriented nature, which is a direct result of its .NET Framework origins. However, its capabilities extend beyond the managed code, providing full access to such programming environments as Component Object Model and Windows Management Instrumentation. Its most basic components are the scripting engine, command-line shell and GUI-based host (referred to as Integrated Scripting Environment), as well as a scripting language. This core functionality is enhanced through so-called cmdlets (with syntax following Name-Verb naming convention) which represent individual methods of underlying .NET classes that implement a specific task. Cmdlets associated with specific server or application roles are typically combined (along with other programming entities, such as providers, functions, variables, or aliases) into modules, facilitating self-containment, portability, and isolation (similar functionality can be accomplished through snap-ins implemented as dynamically linked libraries).

The importance of PowerShell in regard to SQL Server 2012 operations is accentuated by its inclusion in the installation prerequisites. This dependency results from the addition of two PowerShell modules named SQLPS and SQLASCMDLETS into the collection of setup components (they are responsible, respectively, for support of Database Engine and Integration Services, as well as Analysis Services specific features). You can verify their presence following the SQL Server 2012 installation by running Get-Module -ListAvailable SQL* from the Windows PowerShell command prompt, which should display both of them as so-called manifest modules (which you can read more about at the Dev Center site).

SQLPS module contains the SQL Server PowerShell provider that facilitates access to the SQL Server management object (SMO) model, which hierarchy and relationship diagram can be found in MSDN Library. (Note that in order to interact with its assemblies via programming methods, you will need to include Client Tools SDK as part of your SQL Server 2012 setup). The provider also offers a navigation mechanism that allows you to explore the underlying object hierarchy in the manner equivalent to traversing drives and folders of a file system. In addition, the module hosts a set of cmdlets that carry out the most common database administration tasks (for their full listing, refer to MSDN Library). While the module is by default incorporated into the SQL Server setup, it is also possible to provision it independently, by installing relevant components of the SQL Server 2012 Feature Pack available from the Microsoft Download Center (in particular, Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2012, Shared Management Objects, and Microsoft System CLR Types for Microsoft SQL Server 2012).

In order to take advantage of the benefits offered by SQLPS and SQLASCMDLETS, you need to import them first, unless you are running PowerShell v3 built into Windows Server 2012, in which case any required modules are imported dynamically on demand . (Alternatively, you can launch a PowerShell session from the context-sensitive menu of SQL Server entities in the Object Explorer window of the SQL Server Management Studio console, which also automatically loads both modules). However, since this process violates the default security setting of Powershell execution policy, you will first need to change it to less restrictive (such as, for example, RemoteSigned which prevents running unsigned scripts originating from the Internet, but does not impose any restrictions on the local ones). Effectively, the import would consist of two steps:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Import-Module SQLPS -DisableNameChecking

DisableNameChecking parameter supresses warnings about Encode-Sqlname and Decode-Sqlname cmdlets, which violate the cmdlet design guidelines and trigger a warning stating Some imported command names include unapproved verbs, which might make them less discoverable (for more information regarding this topic, refer to Windows PowerShell Blog). Note that importing the module will also automatically load the SQL Server management object model .NET assemblies if you are running PowerShell v3 (built into Windows Server 2012). Otherewise, when using PowerShell v2 (the default in Windows Server 2008 R2), you will additionally need to execute Add-Type -AssemblyName “Microsoft.SqlServer.Smo” cmdlet.

At this point, you should notice that your prompt changes to PS SQLSERVER, indicating that you are at the root of the SQL Server management object model hierarchy. This mechanism utilizes the concept of PowerShell drives, which expose various data stores that you can subsequently inspect and manage by using traditional Windows shell commands (such as, for example, dir, cd, or del). You can identify the drive designation by runing Get-PSProvider, which will return the listing of all available providers with the corresponding drives, including SqlServer. Similarly, you can obtain the same information by invoking Get-PSDrive, which will display all currently available PowerShell drives, with their respective providers and root entries.

From the root node of the SQL Server, you can easily determine its child-level folders (where each of them represents a related set of SQL Server management object model entities) by running Get-ChildItem cmdlet. The output should contain the following entries:

  • SQL – SQL Server Database Engine (providing access to standard database objects, such as tables or views)
  • SQLPolicy – SQL Server Policy Management (dealing with policy-based management objects, such as policies)
  • SQLRegistration – SQL Server Registration (exposing registered server objects, such as registered servers or server groups)
  • DataCollection – SQL Server Data Collection (intended for interfacing with data collection objects, such as collection sets)
  • XEvent – SQL Server Extended Events (representing SQL Server exteneded event infrastructure geared towards event handling as well as performance monitoring and troubleshooting)
  • Utility – SQL Server Utility (implementing utility objects, such as managed instances of the Database Engine)
  • DAC – SQL Server Data-Tier Application Components (facilitating management of data-tier application objects, including their deployment)
  • SSIS – SQL Server Integration Services (focusing on SSIS components, which we will be exploring in detail in our upcoming articles)
  • SQLAS – SQL Server Analysis Services (applicable to SQL Server Analysis Services objects, such as cubes or their dimensions).

To continue your travel through the object hierarchy, simply use cd alias (which actually represents Set-Location cmdlet) followed by another invocation of Get-ChildItem (which also can be substituted by its dir alias) . If you are interested in identifying cmdlets and functions that have been incorporated in the SQL Server modules, you can generate their listing by running Get-Command -Module SQLPS and Get-Command -Module SQLASCMDLETS (or simply Get-Command -Module SQL* for the combined view). Note that if you launch a PowerShell session via context-sensitive menu of an arbitrary item in the Object Explorer window in the SQL Server Management Studio console, you will be automatically redirected to the corresponding node in the SQL Server object model.

This concludes our introduction of the principles of PowerShell in the context of managing SQL Server 2012. In our upcoming articles, we will discuss how these principles can be put into practical use in order to automate most common administrative tasks, with the focus on SQL Server Integration Services.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles