Can We Get These 3 SQLPS Issues Fixed before SQL Server 2016 RTMs?

YES WE CAN! Microsoft responded and fixed these 3 major issues in SQL Server Management Studio March 2016 Refresh. Read more at “WE DID IT! Microsoft Fixed Those 3 SQLPS Issues in SQL Server 2016“.


I’ll be honest, I don’t use the SQLPS module even though I’m the perfect candidate. I’ve been a SQL Server DBA since 1999 and a PowerSheller since 2005. A large majority of my PowerShell work is done within SQL Server, and still, I’d rather look up the syntax to load SMO assemblies, than to Import-Module SQLPS, and I’m not the only one.

Reasons I don’t use SQLPS
  1. It takes 3-5 seconds to load
  2. It changes my directory
  3. It produces warnings upon load

I feel displaced when I load the SQLPS module. Why does it take so long? Why you gotta yell all these warnings at me? Wait, where am I? SQLPS’s behavior seems to mimic that of the sqlps.exe minishell when it should really just be itself.

Please upvote these items on Microsoft Connect

SQLPS has a lot of of bugs that need to be addressed (I’ll get to that soon), but I propose we start with these three.

  1. SQLPS module is slow to load
  2. Loading SQLPS module changes current directory to PS SQLSERVER:\>
  3. SQLPS module uses unapproved PowerShell verbs

Each item even has suggested fixes. The fixes are pretty straightforward (said the DBA who doesn’t do QA). Bugs 1 and 2 suggest modifying a few lines in SqlPsPostScript.ps1, while number 3 probably requires a recompile and we’re not really sure how challenging that will be.

If you’re thinking that Microsoft won’t approve the the disapproved verbs request because of backwards compatibility issues, no fear, the Connect item suggests using Set-Alias to address this concern.

Why now? We’ve been waiting for years.

Recently, I teamed up with Aaron Nelson, a SQL Server MVP and Lead of SQL PASS PowerShell Virtual Chapter. Aaron is a SQL Server DBA, a huge proponent of PowerShell and he is also not a user of SQLPS in its current state. But he wants to be. I do, too. You probably do too if you aren’t already.

Aaron helped get this ball rolling the other night when we were working on some SQL PASS abstracts. He was writing up a session proposal about SQLPS, and I decided to take a stab at figuring out why SQLPS loaded so slowly.

Ultimately, commenting out two lines and adding one changed the load time from 5.1 seconds to 0.345 seconds. JUST LOOK AT THE POSSIBLITIES:

loadsqlps

This doesn’t have to be a (partial) pipe dream

The fact that the change was so simple yet so significant gave us hope. Maybe beginning to fix the SQLPS module doesn’t have to be this big thing.

We all want to want to use SQLPS

SQLPS has sordid past and sometimes just talking about it seems kind of controversial. Some folks vigorously defend it. Some denounce it. Ultimately, though, we all just want it to work.

fixthree

(for now)

We want a module that imports quickly, with no warnings and no change of directory. And while we all would love a well-developed module like those bestowed upon Lync, Exchange and SharePoint, I know we’ll all accept, for now, for this RTM, a module that works well right out the box.

Share

Time is running out, so let’s rally right now for a better SQLPS in SQL Server 2016 RTM. Please share this post, a post of your own, or the Connect items themselves.

Can We Get These 3 SQLPS Issues Fixed before SQL Server 2016 RTMs?
Connect: SQLPS module is slow to load
Connect: SQLPS module uses unapproved PowerShell verbs
Connect: SQLPS module changes current directory


Going Forward

As for future bug fixes, I’m going to create a repository somewhere, perhaps this blog, perhaps somewhere more official, that will hopefully bring visibility to all SQLPS bugs that have been submitted. I’ll do what I can to keep the momentum going. Perhaps I’ll even throw out a Twitter bot that searches for SQLPS bugs and posts them for easy retweeting.

Either way, keep an eye out on my Twitter or this blog for more info.

Posted in PowerShell, SQL Server

SQL Server Solution Least Likely to Work, Actually Did

Two of my SQL Servers stopped working after an unexpected power outage. When I attempted to start them, they gave me an Access Denied error. These are two totally different machines (diff Win & diff SQL version) that both, after years of working, just stopped in their tracks with an Access Denied.

This isn’t an error that I’ve ever run into before, so I looked it up and found Jon Morisis’s post that detailed the error and solution that he found buried deep in some forums.

This photo is actually from Jon Morisis

To solve this Access Denied error, you simply have to disable the HotAdd/HotPlug capability of the VM. I doubted this would work for us for three reasons.

  1. I didn’t think we even enabled HotAdd/HotPlug (we did).
  2. Jon ran into his issue during install, and I ran into this after the servers had been up for an extended period of time.
  3. It seems so remotely related

To my and the VMware admin’s shock, it worked! Anyone know why?

Also, it appears that this access denied issue won’t just impact SQL Server, but a number of other things (admin shares were also mentioned). I’m pretty much blogging about it to remind myself about that setting when I encounter weird permissions issues that make no sense.

Posted in SQL Server, VMware

Failed Domain Relationships on SQL Server Cluster Nodes

You know what’s scary as hell? When one node of an important cluster loses its trust relationship with the domain and you see the error “the trust relationship between this workstation and the primary domain failed”. That happened to me late last year with one of my SQL Server 2008 R2 nodes. The scary part was that I just didn’t know what to expect. The fix could be simple, or it could require a node rebuild.

Generally, I address this issue not by removing servers from the domain, but by using PowerShell v3 by executing the following with an admin prompt:
Reset-ComputerMachinePassword

In this case, however, I did not have access to v3, so at an Admin prompt, I executed

netdom resetpwd /s:dc.ad.local /ud:ad\adminaccount /pd:*

This successfully reset the password and I was able to login again with a domain account. I then started the cluster service, and failed my SQL Server over with no issues. I then failed back and rebooted the server for good measure (and to ensure the trust still existed). I tested this as well on Windows 2012 R2 with a SQL 2014 cluster with success, though Reset-ComputerMachinePassword was easier to remember and worked just as well.

What caused the loss of trust? I haven’t figured it out yet, but I’m assuming that node probably cheated with his ex.

Posted in Active Directory, PowerShell, SQL Server

PowerShell Conference EU 2016, psconf.eu

I’ll be presenting some sessions about SQL Server and PowerShell at the PowerShell Conference EU in Hanover, Germany on April 20-22! You should join me, because the speaker and session lineup is amaaazing! Register at psconf.eu.

presenter

Want to know more? Check out PowerShellMagazine.com and psconf.eu!

Posted in PowerShell, SQL Server

PowerShell Tip: You may not need to load the DLL for that Assembly that can’t be found

Recently, I wanted to access the System.Windows.Forms.Integration.ElementHost type, which uses the System.Windows.Forms.Integration namespace.

When I tried to load that namespace, PowerShell complained that The assembly ‘System.Windows.Forms.Integration’ could not be found.

I read up and found that the WindowsFormsIntegration.dll would need to be loaded, and I begrudgingly added this to my script:

It’s ugly though, and I wondered if there was another way. Then I thought maybe I can just add the base name of the DLL file, and sure enough, this works!

So the next time that you see an assembly can’t be found can be loaded by using the DLL, try just the DLL name instead!

Posted in PowerShell

ShowDialog() Sucks: Use ApplicationContext and Run Instead

Update Jan 7: Added KeyboardInterop so that TextBoxes will work.

One of the lessons learned when making Popups and NotifyIcons in PowerShell and WPF is that the WPF window must be run the the proper context.

I was tipped off to this by Denniver Reining’s PowerShell / NotifyIcon article and Johnny J’s “Doing a (C#) NotifyIcon program the right way“.

While neither of the articles were using WPF, extensive testing showed that using ApplicationContexts and Application.Run instead of ShowDialog() made WPF (and WinForms) work far better. The NotifyIcon issue took a number of days to resolve as I battled with unresponsiveness when clicking on the ContextMenu to Exit. Then it would take about 5 seconds to disappear.

For about a week, this was the story of my life:

aero_busy

And it seemed especially true after hiding the Powershell taskbar application. So I scoured the Internet and found that a lot of other people had the same issue. Just search Google for “NotifyIcon doesn’t disappear.

In addition, the mouse sometimes showed as busy when hoovering over the popup window itself and sometimes the right clicking worked only once.

notify-wait

System.Windows.Forms to the rescue (!?)

So none of the solutions I found worked, but I remembered that Denniver’s NotifyIcon script was responsive, so I went back and noticed his app ended with this important line

[void][System.Windows.Forms.Application]::Run($form1)

This information, along with Johnny J’s article about NotifyIcons helped me figure out that the following would probably work, even for WPF. Now these two lines, along with Hide-PowerShell are always included in my finalized PowerShell-based WPF scripts.

$appContext = New-Object System.Windows.Forms.ApplicationContext
[void][System.Windows.Forms.Application]::Run($appContext)

According to Microsoft, “Application.Run begins running a standard application message loop on the current thread, with an ApplicationContext, which specifies the contextual information about an application thread.”

I’ll be honest, I’m probably not the best person to explain ApplicationContexts in depth. If you’d like to learn more, check out this article titled Use the ApplicationContext Class to Fully Encapsulate Splash Screen Functionality where the author goes into detail about the ApplicationContext class.

The code below (which is also the code from my previous post) shows a fully functioning WPF GUI App that runs in an application with an ApplicationContext. It also has a few other cool techniques you may enjoy.

Icon-Overlay

Note that this code should’t be copy/pasted into the console because the PowerShell window will disappear before it can complete the paste. Be sure to download the script or paste the code below into a .ps1 file and execute. It does appear to work well in the ISE, though!

Even though I had changed my own usage of WPF forms, it didn’t occur to me to blog about it until Doug Finke mentioned on Twitter that his WPF apps are sometimes unstable and crash. When I saw the ShowDialog() in his code, I knew exactly what the problem was, and knew I had to get the word out.

As of today, one of my servers has been running its WPF/PowerShell based-monitor for almost a month!

timespan

In conclusion, you should probably never use ShowDialog() to run your primary window in PowerShell unless you’re testing it (and I should probably update my old blog posts to clarify that).

Also, I don’t totally know what I’m doing, so take this with a grain of salt ;) It’s just worked better for me than ShowDialog(). I will probably revisit this one day with a PowerShell implementation of InitializeComponent().

Posted in PowerShell, WPF

Adding Toolbar Icons to Your PowerShell WPF GUI

This post builds on some previous post titled Use base64 for Images in WPF-based PowerShell Forms.

Show-WpfIconExample.ps1, which can be found below and on github, demonstrates how to overlay the default PowerShell toolbar icon with an image that’s been transformed to a base64 string to System.Windows.Media.ImageSource.

An icon overlay is the mini icon that sometimes shows up on top of a taskbar application icon.

Icon-Overlay

The overlay icon is controlled by $window.TaskbarItemInfo.Overlay. I’m actually unsure if the PowerShell toolbar icon can be replaced entirely ($window.Icon usually takes care of that) without a ton of code.

As a bonus, I also included how to change the taskbar popup description from “Windows PowerShell” or nothing to the title of the $window.

The secret sauce of this is
$window.TaskbarItemInfo.Overlay = $bitmap
$window.TaskbarItemInfo.Description = $window.Title

If you’d like to learn more about the flag of my people, the Cajuns, check out the Wikipedia article “Flag of Acadiana“.

Note that this code can’t be copy/pasted into the console because the PowerShell window will disappear before it can complete the paste. Be sure to download the script or paste the code below into a .ps1 file and execute. It does appear to work well in the ISE, though!

Up next, I’ll be talking more about running your WPF GUIs using ApplicationContext instead of $window.ShowDialog()

Posted in PowerShell, WPF

PowerShell Invoke-RestMethod / CloudFlare API v4 Code Sample

If you’re looking to create your own CloudFlare API v4-based PowerShell module, and would like some syntax examples, here’s a simplified breakdown of my CloudFlareDynDns module on github, which I discuss in “Use PowerShell and CloudFlare API v4 to Dynamically Update CloudFlare DNS to your External IP“.

Posted in Networking, PowerShell

Migrating SQL Server?

dbatools is an awesome PowerShell module that helps you migrate entire instances with a single command.


Available from dbatools.io and github

SQLPS Needs You

SQLPS now has a dedicated engineer and Microsoft is asking for our input!


 
Upvote priorities and cmdlets now

Upvotes Needed

Help persuade Microsoft to open source SQL Server's PowerShell module, SQLPS.


 
Upvote now on Microsoft Connect

Authors


Chrissy LeMaire


Brandon Abshire
View Brandon Abshire, MCDBA's profile on LinkedIn

Awards

Chrissy has been awarded the Microsoft MVP for her work in the PowerShell community.

Join us!

Belgian PowerShell
User Group

  SQL PASS PowerShell
Virtual User Group