Art of the DBA Rotating Header Image

#Powershell and SQL Server: SQLPS Challenges and Hurdles

Over the last two posts, I’ve covered what the SQLPS provider is and how you can get started with it. Now it’s time for other side of the story. If you’ve started working with the provider or done some reading on the web, various hurdles have probably popped up. These hurdles can frustrate and discourage you, so let’s discuss them before that happens.

Slower than Molasses in January

The initial complaint you’ll usually see with the provider is that it’s slow. Mind numbingly slow. So slow that it breaks tab completion in the ISE. Unfortunately, due to the way the provider was implemented using the SMO, we don’t have a lot of options

What can we do? Unfortunately, there are no magic workarounds to implement. This limitation is why you will see a lot of Powershell/SQL development either use the SMO directly or implement .Net code that avoids this completely. The challenge here is that these approaches can be too advanced for administrators who are not used to code development. My recommendation is to be patient with it. The provider is your entry point and is your starting point. As you get more comfortable with Powershell, you will find SMO and other .Net methods will perform better for you. Begin with understanding the language.

What You Least Expect

Another challenge with the provider is that sometimes it will behave in a fashion that doesn’t quite make sense. As with the speed, we’re tied to how the SQL Server team at Microsoft implemented the provider. It is, after all, an API and this means we can only use the it as it was written. What I struggle with is that there there are elements of the provider that weren’t thought all the way through and occasionally they catch me by surprise.

One example of this is an interesting piece of behavior I discovered recently with a several online colleagues (Paul Timmerman, Chris Sommer, Derik Hammer, and Johan Bijnens). Basically, we found that there was no real way to refresh a Central Management Server listing within an existing Powershell session. Once a CMS registry was populated in your provider session, you were stuck with it. If you made changes to your CMS in SSMS, you wouldn’t see those changes propagated until you opened a new Powershell session.

While frustrating, it’s not a show stopper. The challenge, however, is that there are other little land mines like this in the provider and they pop up when you least expect it. This particular issue is not really a problem because you can always save a script and re-open your host. However, it should behave a little more consistently and at least give the user the opportunity to execute a manual refresh of the cached object. I’m hoping that the SQL Server team will be able to address this in future versions of SQL Server.

The Mini-Shell

SQLPS_warts_1Speaking of things that behave not as you would expect, I want to cover the notorious mini-shell. If you have ever right clicked in Management Studio Object Explorer, you’ve probably seen a Start Powershell option.

The intent of this option is to open up a command window with the SQL provider already loaded. This shell also duplicates what SQL Server does if you ever run a Powershell script within a SQL Server Agent job step. What catches people by surprise is how it behaves. In SQL Server 2012 or prior, the host machine will load using Powershell version 1.0 or 2.0, no matter what version you have installed on the host machine. This can be annoying because modules won’t be automatically loaded and some cmdlets aren’t available to you. This was fixed in SQL 2014, so whatever version is installed on the host machine will be used.

While this can present a hurdle for developing scripts to be used in the SQL Agent, it will not block you completely. You can always double check this by starting the Powershell task in Management Studio and interrogating the $PSVersionTable system variable. Beyond that, I haven’t had much reason to use the mini-shell for any Powershell tasks and prefer to work in the ISE or the standard Powershell command window host.

A Fixer-Upper

The SQLPS provider is klunky. It suffers from a little neglect by the SQL Server team and some slapdash coding to implement it. In light of some of the Powershell tools provided for Windows Server, Exchange, and Active Directory, it’s pretty frustrating to see the SQLPS provider languish the way it has. I have some theories around that and I hope that someday I can talk with the product team to see what changes can be made.

However, it should be noted that if you’re just getting started with Powershell, the SQLPS provider is still the best place to start. If you’ve read my other posts, you know by now there has been an effort to make the provider an intuitive path for building automation around SQL Server. Don’t let the hurdles stop you,. The provider will let you get far enough on its own and help you develop patterns that you can later improve and enhance.

Please feel free to leave any comments below on your experience with the provider and other questions you may have regarding it.

One Comment

  1. […] #Powershell and SQL Server: SQLPS Challenges and Hurdles – Mike Fal (Blog|Twitter) […]

Leave a Reply

Your email address will not be published. Required fields are marked *