I'm Just An Engineer: 2018

The output of RedistributeActiveDatabases.ps1 –ShowDatabaseCurrentActives leaves much to be desired, when you are verifying the dag distribution after failover and/or failback, in my opinion. It outputs an endless list of moves you didn't attempt and it takes a fair amount of time to give the result.

I've created a multithreaded powershell script that does the same thing in about 1/3 of the time of the default MS script. You can either use the script name in powershell with the parameter -dagName or you're going to get a pop up box asking for the dag name. The way that it works is that it leverages the add-type cmdlet in powershell to create an object using c# code, which uses multithreading to return an object to the powershell code, which then parses that object and displays the results.

You'll have to insert your Exchange server or load balancing url you use with your domain, but that's all you should have to change unless there is authentication settings specific to your environment. If you don't use kerberos, you'll have to change that part of the code to negotiate or whatever suits your environment. If it's basic you'll need to include explicit credentials, an example of that is shown at the bottom of this blog. If you aren't familiar with c#, implementing that could be confusing to you. Another hangup could be if you use http or https for your connection to your Exchange server or load balanced url. You'll need to run it on a server to ensure it has access to all of the dlls that need to be imported.

All of that being said, hopefully you don't have to make any changes aside from the url name and it works right out of the box for you. Enjoy!

<# CHANGELOG

9/10/18 - added a load balanced url

#>

<# Notes

You will have to alter the exchangeUri variable for your environments

If you have any issues with this, you may have to alter the kerberos authentication information

wmc.AuthenticationMechanism = AuthenticationMechanism.Kerberos; may need to be .Negotiate and/or you may need to connect

directly to a server with the exchangeUri via http or https, instead of a load balanced url

Another hurdle will occur if you don't have the proper assmemblies on the machine and loaded into the GAC so they are

registered, in other words this probalby won't run on your desktop

#>

<# PARAMETERS #>

param

(

[Parameter(Mandatory=$false,Position=1)]

[string]$dagName

#[Parameter(Mandatory=$False,Position=2)]

#[string]$emailAddressNamesList

)

<# /PARAMETERS #>

cls

$localMachineName = $env:computername;

if (($localMachineName.ToString().ToLower() -notlike "*-dag*") -and ($localMachineName.ToString().ToLower() -notlike "*-cas*"))

{

Write-Host $localMachineName.ToString().ToLower();

$session = new-pssession -ConfigurationName Microsoft.Exchange -ConnectionUri 'http://[exchange server or load balancer url here]/powershell';

import-pssession $session -AllowClobber;

}

set-adserversettings -viewentireforest $true;

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null;

if (($dagName -eq $null) -or ($dagName -eq ""))

{

$dagName = [Microsoft.VisualBasic.Interaction]::InputBox("Enter a dag name", "Dag");

}

$time = New-Object system.Diagnostics.Stopwatch

$time.Start()

$system = $time.Elapsed.TotalMilliseconds

$dagObject = Get-DatabaseAvailabilityGroup -Identity $dagName;

$servers = New-Object System.Collections.ArrayList;

foreach($server in $dagObject.Servers) # foreach($server in $dagObject.Servers){$servers.Add($server) | Out-Null;

{

$servers.Add($server) | Out-Null;

}

$servers = $servers | Sort-Object;

Write-Host "Database distribution check for" $dagName -ForegroundColor Yellow -BackgroundColor Black;

Write-Host "";

$distribution =

@'

using System;

using System.Collections.Generic;

using System.Collections.ObjectModel;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Management.Automation;

using System.Management.Automation.Runspaces;

using System.Security;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Threading;

using System.Collections.Concurrent;

using System.Diagnostics;

using System.Security.Principal;

namespace MultiThreading

{

public class dagDistribution

{

public string exchangeUri = "http://[exchange server or load balancer url here]/powershell";

public List<string> get(string dag)

{

DateTime start = DateTime.Now;

var response = new ConcurrentBag<Collection<PSObject>>();

var exceptions = new ConcurrentQueue<Exception>();

List<string> serversUnsorted = getDagMembers(dag);

var servers = from s in serversUnsorted orderby s select s;

try

{

Parallel.ForEach(servers, server =>

{

response.Add(runPowerShellScript(server));

});

}

catch (AggregateException ae)

{

foreach (var aex in ae.InnerExceptions)

{

exceptions.Enqueue(aex);

}

}

List<string> returnValues = new List<string>();

foreach (var item in response)

{

string returnValue = parseServerResults(item);

returnValues.Add(returnValue);

}

returnValues.Sort();

return returnValues;

}

public List<string> getServers(string[] serverz)

{

DateTime start = DateTime.Now;

var response = new ConcurrentBag<Collection<PSObject>>();

var exceptions = new ConcurrentQueue<Exception>();

string[] serversUnsorted = serverz;

var servers = from s in serversUnsorted orderby s select s;

try

{

Parallel.ForEach(servers, server =>

{

response.Add(runPowerShellScript(server));

});

}

catch (AggregateException ae)

{

foreach (var aex in ae.InnerExceptions)

{

exceptions.Enqueue(aex);

}

}

List<string> returnValues = new List<string>();

foreach (var item in response)

{

string returnValue = parseServerResults(item);

returnValues.Add(returnValue);

}

returnValues.Sort();

return returnValues;

}

private Collection<PSObject> runPowerShellScript(object server)

{

Collection<PSObject> psobjs = new Collection<PSObject>();

string result = "";

string serverName = server.ToString();

WSManConnectionInfo wmc = new WSManConnectionInfo(new Uri(exchangeUri));

wmc.AuthenticationMechanism = AuthenticationMechanism.Kerberos;

wmc.ShellUri = "http://schemas.microsoft.com/powershell/Microsoft.Exchange";

using (Runspace runspace = RunspaceFactory.CreateRunspace(wmc))

{

PowerShell powershell = PowerShell.Create();



if (runspace.RunspaceStateInfo.State == RunspaceState.Opened)

{

// do nothing

}

else

{

runspace.Open();

powershell.Runspace = runspace;

}

try

{

PSCommand command = new PSCommand();

command.AddScript("get-mailboxdatabase -Server " + server + " -Status");

powershell.Commands = command;

psobjs = powershell.Invoke();

if (powershell.HadErrors == true)

{

result = "Failed - " + powershell.Streams.Error[0].ToString();

result = result.Replace("\"", "*");

}

}

catch (Exception ex)

{

string fail = ex.Message;

}

}

object serverNameO = server;

PSObject serverNameObj = new PSObject(serverNameO);

psobjs.Insert(0, serverNameObj);

return psobjs;

}

  private List<string> getDagMembers(string dagName)
{
Collection<PSObject> psobjs = new Collection<PSObject>();
string result = "";
string[] servers = null;
List<string> serverList = new List<string>();

WSManConnectionInfo wmc = new WSManConnectionInfo(new Uri(exchangeUri));
wmc.AuthenticationMechanism = AuthenticationMechanism.Kerberos;
wmc.ShellUri = "http://schemas.microsoft.com/powershell/Microsoft.Exchange";

using (Runspace runspace = RunspaceFactory.CreateRunspace(wmc))
{
PowerShell powershell = PowerShell.Create();

if (runspace.RunspaceStateInfo.State == RunspaceState.Opened)
{
// do nothing
}
else
{
runspace.Open();
powershell.Runspace = runspace;
}

try
{
PSCommand command = new PSCommand();
command.AddScript("Get-DatabaseAvailabilityGroup -Identity " + dagName);
powershell.Commands = command;
psobjs = powershell.Invoke();

if (powershell.HadErrors == true)
{
result = "Failed - " + powershell.Streams.Error[0].ToString();
result = result.Replace("\"", "*");
}

PSPropertyInfo serversTemp = null;
foreach (PSObject psobj in psobjs)
{
serversTemp = psobj.Properties["servers"];
string s_servers = serversTemp.Value.ToString();
servers = s_servers.Split(' ');
foreach (string server in servers)
{
serverList.Add(server);
}
}

}
catch (Exception ex)
{
string fail = ex.Message;
}
}

return serverList;
}

private string parseServerResults(Collection<PSObject> serverObjs) // needs servername, totaldbs, activedbs, passivedbs, preferencecount (11,11,11,11), mounteddbs, dismounteddbs, dagname

{

// called independently with each server, first object is always the server name

Stopwatch stopwatch = new Stopwatch();

stopwatch.Start();

int index = 0;

string returnValue = "";

string serverName = "";

int totalDbs = 0;

int activeDbs = 0; // whichever has activation preference 1

int passiveDbs = 0; // whichever has activation preference 2, 3 or 4

string activeCopyServerName = "";

int activationPreferenceOne = 0;

int activationPreferenceTwo = 0;

int activationPreferenceThree = 0;

int activationPreferenceFour = 0;

int mountedCount = 0;

int dismountedCount = 0;

string dagName = "";

string dagServerAndDatabaseName = "";

foreach (PSObject obj in serverObjs)

{

if (index == 0)

{

serverName = obj.ToString();

}

totalDbs = (serverObjs.Count - 1);

PSMemberInfoCollection<PSPropertyInfo> props = obj.Properties;

string currentPrimaryActivationServer = "";

foreach (PSPropertyInfo prop in props)

{

if (prop.Name == "MountedOnServer")

{

currentPrimaryActivationServer = prop.Value.ToString();

break;

}

}



List<string> propertyNames = new List<string>();

foreach (PSPropertyInfo prop in props)

{

string result = prop.Name + " | " + prop.Value;

if (prop.Name == "Mounted")

{

if (prop.Value.ToString() == "True")

{

if (currentPrimaryActivationServer.ToLower().StartsWith(serverName.ToLower()))

{

mountedCount++;

}

}

else

{

dismountedCount++;

}

}

else if (prop.Name == "MountedOnServer")

{

activeCopyServerName = prop.Value.ToString();

}

else if (prop.Name == "ActivationPreference")

{

string arr = prop.Value.ToString();

string[] vals = arr.Split(']');

foreach (string val in vals)

{

if (val != "")

{

string valTemp = val;

if (val.Contains("["))

{

valTemp = val.Replace("[", "");

}

string[] preference = valTemp.Split(',');

string preferenceZero = preference[0].ToString().Trim();

string preferenceOne = preference[1].ToString().Trim();

if (preferenceZero.ToLower() == serverName.ToLower())

{

if (preferenceOne == "1")

{

if (currentPrimaryActivationServer.ToLower().StartsWith(serverName.ToLower()))

{

activeDbs++;

}

else

{

passiveDbs++;

}

}

else

{

if (!(currentPrimaryActivationServer.ToLower().StartsWith(serverName.ToLower())))

{

passiveDbs++;

}

else

{

activeDbs++;

}

}

switch (preferenceOne)

{

case "1":

activationPreferenceOne++;

break;

case "2":

activationPreferenceTwo++;

break;

case "3":

activationPreferenceThree++;

break;

case "4":

activationPreferenceFour++;

break;

default:

break;

}

}

}

}

}

else if (prop.Name == "Server")

{

string activeCopyServerName2 = prop.Value.ToString();

}

else if (prop.Name == "MasterServerOrAvailabilityGroup")

{

dagName = prop.Value.ToString();

}

else if (prop.Name == "MailboxProvisioningAttributes")

{

dagServerAndDatabaseName = prop.Value.ToString();

}

propertyNames.Add(prop.Name.ToString()); // cumulative count of the property names

}

index++;

}

stopwatch.Stop();

return returnValue = serverName + "|" + totalDbs + "|" + activeDbs + "|" + passiveDbs + "|" + activationPreferenceOne + "," + activationPreferenceTwo + "," +

activationPreferenceThree + "," + activationPreferenceFour + "|" + mountedCount + "|" + dismountedCount + "|" + dagName;

}

}

}

'@

try

{

$dagDistribution = New-Object MultiThreading.dagDistribution;

}

catch

{

Add-Type -TypeDefinition $distribution -ReferencedAssemblies System.Collections, System.ComponentModel, System.Data, System.Drawing, System.Linq, System.Management.Automation, System.Security, System.Threading.Tasks, System.Windows.Forms, System.Threading, System.Collections.Concurrent, System.Security.Principal

$dagDistribution = New-Object MultiThreading.dagDistribution;

}

# $results = $dagDistribution.get($dagName);

if ($servers.Count -gt 0)

{

$results = $dagDistribution.getServers($servers);

}

else

{

$results = $dagDistribution.get($dagName);

}

$results = $results | sort;

$outputTable = New-Object system.Data.DataTable “$TableName”;

#servername totaldbs activedbs passivedbs preferencecountlist mounteddbs dismounteddbs dagname

$col0 = New-Object system.Data.DataColumn " ",([string]);

$col1 = New-Object system.Data.DataColumn ServerName,([string]);

$col2 = New-Object system.Data.DataColumn TotalDbs,([string]);

$col3 = New-Object system.Data.DataColumn ActiveDbs,([string]);

$col4 = New-Object system.Data.DataColumn PassiveDbs,([string]);

$col5 = New-Object system.Data.DataColumn PreferenceCountList,([string]);

$col6 = New-Object system.Data.DataColumn MountedDbs,([string]);

$col7 = New-Object system.Data.DataColumn DismountedDbs,([string]);

$col8 = New-Object system.Data.DataColumn DagName,([string]);

$outputTable.columns.add($col0);

$outputTable.columns.add($col1);

$outputTable.columns.add($col2);

$outputTable.columns.add($col3);

$outputTable.columns.add($col4);

$outputTable.columns.add($col5);

$outputTable.columns.add($col6);

$outputTable.columns.add($col7);

$outputTable.columns.add($col8);

foreach($result in $results)

{

[string]$resultSplit = $result;

$resultSplit1 = $resultSplit.Split("|"); # -split "|";

$row = $outputTable.NewRow();

$temp0 = $resultSplit1[0];

$temp1 = $resultSplit1[1];

$temp2 = $resultSplit1[2];

$temp3 = $resultSplit1[3];

$temp4 = $resultSplit1[4];

$temp5 = $resultSplit1[5];

if ($resultSplit1[6] -ne "")

{

$temp6 = $resultSplit1[6];

}

else

{

$temp6 = "0";

}

$temp7 = $resultSplit1[7];

$row.ServerName = $resultSplit1[0];

$row.TotalDbs = $resultSplit1[1];

$row.ActiveDbs = $resultSplit1[2];

$row.PassiveDbs = $resultSplit1[3];

$row.PreferenceCountList = $resultSplit1[4];

$row.MountedDbs = $resultSplit1[5];

if ($resultSplit1[6] -ne "")

{

$row.DismountedDbs = $resultSplit1[6];

}

else

{

$row.DismountedDbs = "0";

}

$row.DagName = $resultSplit1[7];

$outputTable.Rows.Add($row);

}

$outputTable | format-table -AutoSize;

Write-host "Function completed" -ForegroundColor Yellow -BackgroundColor Black;

Write-host " ";

$time.Stop();

$totalTime = $time.Elapsed.TotalMilliseconds - $system;

[float]$runTimeSeconds = [math]::Round($totalTime/1000, 2);

[float]$runTimeMinutes = [math]::Round($totalTime/60000, 2);

Write-Host "This function took " $runTimeSeconds.ToString() " seconds (or " $runTimeMinutes.ToString() " minutes) to run";

Explicit credential example

string loginPassword = "password here";

System.Security.SecureString secpassword = new SecureString();

foreach (char c in loginPassword)

{

secpassword.AppendChar(c);

}

PSCredential credential = new PSCredential(@"domain\samaccount", secpassword);

WSManConnectionInfo connectionInfo = new WSManConnectionInfo(new Uri("https://[server name or load balancer url]/powershell"), "http://schemas.microsoft.com/powershell/Microsoft.Exchange", credential);

connectionInfo.AuthenticationMechanism = AuthenticationMechanism.Basic;

I'm Just An Engineer

Monday, September 10, 2018

(Powershell) Faster: RedistributeActiveDatabases.ps1 –ShowDatabaseCurrentActives or dag distribution script

Contributors

Support Gkrew Productions