The last few cmdlets to cover don’t fit in any nice buckets. I’ll just address each one on its own to keep it simple. One I won’t cover is Invoke-SqlCmd, because I already did that a couple of months ago. I will say that I use Invoke-SqlCmd a LOT and recommend that you look into using it for your own scripts.
What’s left? Well, one cmdlet is Set-SqlAuthenticationMode. As the name implies, it allows us to change the authentication mode of our instance. The cmdlet requires to the Cloud Adapter Service to function and a Windows Administrator credential for the target machine. When called, you can specify one of two modes: Integrated and Mixed. Normal and Unknown will show up as accepted values by Intellisense, but if you try and use them the cmdlet will fail.
If we try to set Mixed, the cmdlet will require a SQL credential that contains the password for ‘sa’. When run, this cmdlet will force a service restart (as normal for changing an authentication mode). It will ask you to confirm a restart, which you can avoid using the -Force flag.
Is it useful? It’s really nice and handy to have a cmdlet that will wrap up the checking for credentials and manipulating the service, but needing that Cloud Adapter Service is frustrating. It’s another layer of functionality that I need to provide, one that does not offer me a whole lot of value outside of this one call. Especially when we already have a good way to do it using the SMO (hat tip Allen White(@SQLRunr)).
Next up is a cmdlet useful to those who leverage Policy Based Management, Invoke-PolicyEvaluation. Interestingly enough, Microsoft actually has this cmdlet documented, so this is helpful. Running it seems fairly simple: Get a policy (either from an XML declaration or a SQL Server object), then call the cmdlet with that and a target server:
Neat, right? A nice little report that my policy evaluation failed. Just one problem: I don’t know WHAT failed. As of writing this, I’ve been trying to figure out how to display the results in a readable format to the Powershell window. You can get the detailed results by using the -OutputXML flag to generate an XML string (not an XML doc object, unfortunately) which will contain the detail, but this isn’t all that helpful.
One nice little piece of fucntionality is the -AdHocPolicyEvaluationMode parameter. By default it is set to Check, which means it simply reports on whether or not the policy passed evaluation. However, we can pass Configure and then the cmdlet will attempt to correct the violations. I’m still testing with this, but this could be handy for cleaning up your environment using PBM.
The final cmdlet is Convert-UrnToPath. This cmdlet is built to assist Universal Resource Names that the SMO uses to name and organize SQL Server objects. Full disclosure: I’ve never used these. Fortunately, the URN for an object can be found as a property of the related SMO object:
The cmdlet will convert one of these URN paths to its appropriate SQL Server provider path:
This can be useful for those who work a lot with URNs, but I honestly have not had a need up until this point. The only real use case I can think of with it is if I want to get a provider path from an SMO object I’ve created.
That about wraps it up! There are two cmdlets that I should have covered in earlier posts that I will touch on tomorrow, then we’ll wrap up with one last post with some thoughts I have on the SQLPS module now that I’ve had a chance to explore it through this blog post series. Thanks for following this and feel free to let me know of any feedback you have via mike [at] [this domain] or the comments and I will try to cover them in my wrap up post.
I’m tweeting!