dbachecks – Going parallel – Cut off your execution times

On one of the last clients I have worked, I have implemented dbachecks in order to get the state of art and know how cool or bad the environments are before start knocking down the bad practices.

This client has seven different environments with more than 100 instances and more than 2000 databases.

Serial execution

A non-parallel execution (single session) took more than 2 hours.
This is not a big problem when we run it out of hours and we don’t want/need to be looking or waiting for it to finish.
I set up it to run at 6 in the morning and when I arrive at the office I can refresh the Power BI dashboard a get a recent state of art.

Well, 2 hours seems not too much if we compare with some other dbachecks users

What if we want to lower down that 2 hours execution? Or the Hiram Fleitas’ (b | t) 4,6 hours?

Going parallel

First let me remember that this doesn’t come for free! I mean, if you set multiple checks in parallel (different sessions), you will consume more resources (CPU, memory, etc) on the machine where you are running them. Test the process and find a reasonable limit for it, otherwise this can become slower that the serial execution.

This brought some new needs.

By default, dbachecks works with the values previously saved (for that we use Set-DbcConfig or the Import-DbcCheck). This means when we start a new session and the last session have changed any configuration, that configuration is the one that will be used in the new session.

Can you see the problem?

Imagine that I want to check for databases in Full Recovery Model on the production environment and I want to start (in parallel) a new check for the development environment where I want to check for Simple Recovery Model if this setting is not changed in the correct time frame, we can end checking for Full Recovery Model on the development environment where we want the Simple Recovery Model.

The first time I tried to run tests for some environments in parallel, that had the need to change some configs, I didn’t realise about this detail so I ended up with much more failed tests than the expected! The bell rang when the majority of the failed tests were from a specific test…the one I had changed the value.

-Temporary parameter for the rescue!

On my last dbachecks blog post – dbachecks – Setting temporary configuration values I have explained how this works so if you haven’t read it yet, take a look before continuing.

Ok, now that you know you can use the -Temporary to run the tests without interfering with the persisted, you may already notice what we will do next..

My recipe to run in parallel

Disclaimer: First, let me say that this is just one option you can come up with a different one. Please drop a comment so I, and others, can become aware of different approaches.

  1. If you don’t have a configuration file for the environment yet, start by configuring all the settings and use Export-DbcConfig to save them.
  2. You need to do a split of your instances/hosts in one or more groups that can share the exact same configurations.
  3. Start a new powershell session, set (using Set-DbcConfig) or import (using Import-DbcConfig) your configurations (set up on number 1) but don’t forget to use the -Temporary parameter.
  4. Run the Invoke-DbcCheck
  5. Repeat steps 1, 2 and 3 as many times as you want – I encourage you to start with just 2 sessions and monitoring your computer resources. Then if you still have room, add one more.
  6. Grab a coffee, a beer or any other drink of your choice and wait until it finishes.

Again, take a look on your resources and then you can test with one more session. Do it until you find the sweet number of parallel sessions.

Here is the code you can use:
For 1st point:

#PROD environment
Set-DbcConfig -Name policy.recoverymodel.type -Value Full -Temporary
Export-DbcConfig -Path "D:\dbachecks\Prod_Configs.json"

 

#DEV environment
Set-DbcConfig -Name policy.recoverymodel.type -Value Simple -Temporary
Export-DbcConfig -Path "D:\dbachecks\Dev_Configs.json"

2nd, 3rd and 4th point together:

#PROD instances
$sqlInstances = "prod1", "prod2", "prod3"

#Import Prod_Configs.json with -Temporary
Import-DbcConfig -Path "D:\dbachecks\Prod_Configs.json" -Temporary

#Run the checks - Don't forget to add all the parameters you usually use
Invoke-DbcCheck -SqlInstance $sqlInstances

 

#DEV instances
$sqlInstances = "dev1", "dev2", "dev3"

#Import Dev_Configs.json with -Temporary
Import-DbcConfig -Path "D:\dbachecks\Dev_Configs.json" -Temporary

#Run the checks - Don't forget to add all the parameters you usually use
Invoke-DbcCheck -SqlInstance $sqlInstances

Save this scripts in two different ps1 files. Then, open two different PowerShell sessions and call each script on different session. Let it flow 🙂

Results

On my case I was able to drop from 2 hours to about 1 hour with 3 parallel sessions. Adding a 4th session made the whole process slower.

Wrap

We saw that we may have problems if we try to run more than one dbachecks session when using different configured values. Using -Temporary parameter when setting the values come in handy for this scenario.
This way we can run two or more sessions in parallel and each one on different environments without messing each other and hopefully, cut off our execution times.

Hope this helps! I would love to hear if you were able to drop down your execution times and what they are before and after.

Thanks for reading!

“Invalid class [0x80041010]” error when trying to access SQLServer’s WMI classes

I was using open source PowerShell module dbatools (GitHub repository) to get the list of SQL Server services I have on a bunch of hosts so I could confirm if they are in “running” state.

— Quick note —
For those who don’t know, dbatools is a module, written by the community, that makes SQL Server administration much easier using PowerShell. Today, the module has more than 260 commands. Go get it and try it! If you have any doubt you can join the team on the #dbatools channel at the Slack – SQL Server Community.
— Quick note —

To accomplish this, I’m using the Get-DbaSqlService initially written by Klaas Vandenberghe (b | t).

This command is very handy, as it will try different ways to connect to the host and we don’t need to do anything extra. Also, it has a -Credential parameter so we can use it to connect to hosts in different domains (I have 10 different credentials, one per domain).

Everything was running fine, for the first couple of hosts, until…

I got the following message when running on a specific host:

WARNING: Get-DbaSqlService – No ComputerManagement Namespace on HOST001. Please note that this function is available from SQL 2005 up.

Trying to get more information, I have executed the same command but added the -Verbose switch

From all the blue lines, I spotted this:

VERBOSE: [Get-DbaCmObject][12:23:31] [HOST001] Retrieving Management Information
VERBOSE: [Get-DbaCmObject][12:23:31] [HOST001] Accessing computer using Cim over WinRM
VERBOSE: [Get-DbaCmObject][12:23:47] [HOST001] Accessing computer using Cim over WinRM – Failed!
VERBOSE: [Get-DbaCmObject][12:23:47] [HOST001] Accessing computer using Cim over DCOM
VERBOSE: [Get-DbaCmObject][12:23:48] [HOST001] Accessing computer using Cim over DCOM – Success!

Ok, this means that for this specific host I can’t connect via WinRM (using WSMan) but I can when using the DCOM protocol. However,  the WMI query used to get the list of SQL services fails.

Going further

I open the Get-DbaSqlService.ps1 script and spotted where the warning message comes from. Then, I have copied the code to a new window in order to isolate it and do another execution tests.

The code is:

$sessionoption = New-CimSessionOption -Protocol DCOM
$CIMsession = New-CimSession -ComputerName $Computer -SessionOption $sessionoption -ErrorAction SilentlyContinue -Credential $Credential
#I have skipped an if ( $CIMSession ) that is here because we know that works.
$namespace = Get-CimInstance -CimSession $CIMsession -NameSpace root\Microsoft\SQLServer -ClassName "__NAMESPACE" -Filter "Name Like 'ComputerManagement%'" -ErrorAction SilentlyContinue |Where-Object {(Get-CimInstance -CimSession $CIMsession -Namespace $("root\Microsoft\SQLServer\" + $_.Name) -Query "SELECT * FROM SqlService" -ErrorAction SilentlyContinue).count -gt 0}

I splitted the last command to remove the pipeline since I would like to analyze each part of the code. I ended with the following code:

$sessionoption = New-CimSessionOption -Protocol DCOM
$CIMsession = New-CimSession -ComputerName "HOST001" -SessionOption $sessionoption -ErrorAction Continue -Credential $Credentials -Verbose

Get-CimInstance -CimSession $CIMsession -NameSpace root\Microsoft\SQLServer -Query "Select * FROM __NAMESPACE WHERE Name Like 'ComputerManagement%'"
#This one is comment for now
#Get-CimInstance -CimSession $CIMsession -Namespace $("root\Microsoft\SQLServer\ComputerManagement10") -Query "SELECT * FROM SqlService"

This can return more than one line with different ComputerManagement (like ComputerManagement10). It depends on the versions you have installed on the host. The number “10” refers to the SQL Server 2008.
Now I can uncomment the last command and run it. The result is:

Get-CimInstance : Invalid class
At line:1 char:1
+ Get-CimInstance -CimSession $CIMsession -Namespace $(“root\Microsoft\SQLServer\C …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : MetadataError: (:) [Get-CimInstance], CimException
+ FullyQualifiedErrorId : HRESULT 0x80041010,Microsoft.Management.Infrastructure.CimCmdlets.GetCimInstanceCommand
+ PSComputerName : HOST001

Ok, a different error message. Let’s dig in it. I logged in on the host and confirmed that I have a SQL Server 2008 R2 instance installed. This means that I’m not accessing a lower version than 2005 like the initial warning message was suggesting.

I tried to execute locally the same query but this time using Get-WmiObject instead of Get-CimInstance (which, in this case wasn’t available because the host only have PowerShell v2.0. It’s a Windows server 2008 SP2. CIM cmdlets appears on v3.0) and it failed with the same error.

Get-WmiObject : Invalid class
At line:1 char:5
+ gwmi <<<< -Namespace “root\Microsoft\SQLServer\ComputerManagement10” -Query “SELECT * FROM SqlService”
+ CategoryInfo : InvalidOperation: (:) [Get-WmiObject], ManagementException
+ FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

I remembered, from past experiences, that SQL Server Configuration manager relies on WMI classes to show the information, so I tried to open it and I got the following error message:

Cannot connect to WMI provider. You do not have permission or the server in unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]

Again, that 2005 callout, but…did you recognize the last sentence? It’s the same error I was getting with Get-CIMInstance remotely and Get-WmiObject locally.

Definitely something is broken.

Let’s fix it!

To fix this problem we need to reinstall the SQL Server WMI provider. To do this we need to run 2 commands. (I found this in this post)

  1. Install classes:
    Go to C:\Program Files (x86)\Microsoft SQL Server\{Version 110 is SQL2012}\Shared
    There you can find a file with mof extension. The file name sqlmgmproviderxpsp2up.mof
    Now on the command line run the following command:
    mofcomp sqlmgmproviderxpsp2up.mof
    The output:
  2. Install localization info:
    Navigate to the Shared sub-folder that indicates the locale of your SQL Server installation. In my case is the 1033 (english-US).
    Inside that folder you will find a file with the .mfl extension. The file name is sqlmgmprovider.mfl. On the command line run the following command:
    mofcomp sqlmgmprovider.mfl 
    The output:

With these 2 actions, we are done.

Now we can try to open the SQL Server Configuration Manager again and it opens like expected! Without error messages.

Let’s go back and rerun our commands.
On the host:

Remotely:

And from dbatools Get-DbaSqlService command:

No more “invalid class” messages and we get the output we want!

Thanks for reading.

HTTP 403 error – PowerShell Remoting, Different Domains and Proxies

On my day to day work I use Nagios monitoring software. I want to add some custom SQL Server scripts to enrich the monitoring, and to accomplish this I will need to:

  • Find a folder
  • Create a sub folder
  • Copy bunch of file
  • edit a ini file to verify/add new entries

all of this for every single host on my entire estate. Obviously (for me 🙂 ) I decided to use PowerShell!

Hold your horses!

Yes, calm down. I’m working on a client where the network it’s anything but simple. As far as I know they have 10 domains and few of them have trust configured, but even those that have, is not in both ways… so I didn’t expect an easy journey to get the task done.

Side note: For those thinking how I can live without PowerShell, I can’t! But,  the majority of my time using PowerShell is with SQL Server, mainly using SMO (with the help of dbatools), which means I haven’t struggle that much until now.

“…WinRM client received an HTTP status code of 403…”

Ok, here we go!

PowerShell Remoting and different domains…

….needs different credentials. This is a requirement when using ip address.
If we try to run the following code:

$DestinationComputer = '10.10.10.1'
Invoke-Command -ScriptBlock { Get-Service *sql* } -ComputerName $DestinationComputer

we will get the following error message:

Default authentication may be used with an IP address under the following conditions: the transport is HTTPS or the destination is in the TrustedHosts list, and explicit credentials are provided.

First, I add the destination computer to my TrustedHosts. We can do this in two ways:

Using Set-Item PowerShell cmdlet

Set-Item WSMan:\localhost\Client\TrustedHosts "10.10.10.1"

Or using winrm executable:

winrm s winrm/config/client '@{TrustedHosts="10.10.10.1"}'

Note: You can use “*” (asterisk) to say all remote hosts are trusted. Or just a segment of IPs like “10.10.10.*”.

But, there is another requirement like the error message says “…and explicit credentials are provided.”. This means that we need to add, and in this case I really want to use, a different credential so I have modified the script to:

$DestinationComputer = '10.10.10.1'
Invoke-Command -ScriptBlock { Get-Service *sql* } -ComputerName $DestinationComputer -Credential domain2\user1

Now I get prompted for the user password and I can… get a different error message (*sigh*):

[10.10.10.1] Connecting to remote server 10.10.10.1 failed with the following error message : The WinRM client received an HTTP status code of 403 from the remote WS-Management service. For more information, see the

about_Remote_Troubleshooting Help topic.

+ CategoryInfo : OpenError: (10.10.10.1:String) [], PSRemotingTransportException

+ FullyQualifiedErrorId : -2144108273,PSSessionStateBroken

This one was new for me so I jumped to google and started searching for this error message. Unfortunately all the references I found are to solve an IIS problem with SSL checkbox on the website like this example.

Clearly this is not the problem I was having.

Proxies

I jumped into PowerShell slack (you can ask for an invite here and join more than 3 thousand professionals) and ask for help on #powershell-help channel.
In the meantime, I continued my search and found something to do with proxies in the The dreaded 403 PowerShell Remoting blog post.
This actually could help, but I don’t want to remove the existing proxies from the remote machine. I had to find another way to do it.

Returning to Slack, Josh Duffney (b | t) and Daniel Silva (b | t) quickly prompted to help me and when I mentioned the blog post on proxies, Daniel has shown to me the PowerTip PowerShell Remoting and HTTP 403 Error that I haven’t found before (don’t ask me why…well, I have an idea, I copy & paste the whole error message that’s why).

ProxyAccessType

The answer, for my scenario, is the ProxyAccessType parameter. As it says on the help page, this option “defines the access type for the proxy connection”. There are 5 different options AutoDetect, IEConfig, None, NoProxyServer and WinHttpConfig.

I need to use NoProxyServer to “do not use a proxy server – resolves all host names locally”. Here is the full code:

$DestinationComputer = '10.10.10.1'
$option = New-PSSessionOption -ProxyAccessType NoProxyServer
Invoke-Command -ScriptBlock { Get-Service *sql* } -ComputerName $DestinationComputer -Credential domain2\user1 -SessionOption $option

This will:

  • create a new PowerShell Session option (line 2) with New-PSSessionOption cmdlet saying that -ProxyAccessType is NoProxyServer.
  • Then, just use the $option as the value of -SessionOption parameter on the Invoke-Command.

This did the trick! Finally I was able to run code on the remote host.

Thanks for reading.