Art of the DBA Rotating Header Image

September 29th, 2015:

A Month of SQLPS: The Provider

Next up in my series on the SQLPS module is to talk about the fundamentals of the provider. This is the core of the SQL Server module, providing an extension to the shell for managing and working with SQL Server. Providers intended togive administrators a file system-like interface for a part of the Windows ecosystem, allowing for a more intuitive way for managing parts of their environment.  

To list all your providers, just use Get-PSDrive:

GET-PSDrive

SQLPS-2-1

We have several different types of providers, including FileSystem, Environment, and Registry. SqlServer is  listed once you’ve imported the module. Once loaded, we can treat the SQL Server components as a file system, with many of the usual commands. Let’s switch to that drive and see what we have available to us:

CD SQLSERVER:\
dir

SQLPS-2-2

Right away we can see many familiar components. Each of these folders is a different part of the SQL Server stack where we can access and manage our environment. We’ll go ahead and browse into the SQL Engine to see what’s there:

CD SQL
dir

CD SHION
dir

CD DEFAULT
dir

It’s that easy to start browsing around our SQL Servers.

Entering the ‘SQL’ folder means we will be working directly with the SQL Engine. Now, by default the provider will only list the local machine under the SQL folder, but we can access any SQL host from here. Just change your location to that machine name. Note, it’s the machine, not the SQL Server instance.

Once you’re in the target machine, the provider will list all instances on that server. Most of the time you’ll probably see DEFAULT, but if you’re using named instances, then those names will be listed. Next, we’ll move down into the instance itself to get all the components for the instance. It should look pretty familiar, since you probably see those components whenever you open up the object explorer in SSMS.

Powershell providers were written to provide an intuitive interface so that administrators wouldn’t get bogged down in trying to get to different parts of their environment, so it’s not surprising that navigating SQL Servers is easy. Take note of the this pattern:

SQLSERVER:\Folder\Machine\Instance\Components

This is the path for SQL Server components and is key to referencing objects with the SQL Server provider.

However, it’s still a little rough sailing. Since using the provider can involve remote servers, your experience will be bound by the ability to communicate with your SQL Instances. It also means that if you’re using tab complete or Intellisense in the Powershell ISE, it will likely not work with provider components because it will timeout when trying to communicate. You will need to rely more on your knowledge of your instances and some of the components than using auto-completion elements.

Why does this happen? It has to do with the provider’s fundamental building blocks the Server Management Objects (SMO). This is a standard .Net library and brings with it rich, consistent functionality that is used throughout the Microsoft ecosystem. In the next post, we’ll go into detail of the SMO and how it’s implemented in the provider, why this an advantage, and what considerations we need to keep in mind as we use it.