0

Run checks against servers

Import-Module dbatools
Import-Module dbachecks
$Server = "AMCB123"    
$Database = "DBA"

# Create recordset of servers to evaluate

$sconn = new-object System.Data.SqlClient.SqlConnection("server=$Server;Trusted_Connection=true");

$q = "SELECT DISTINCT servername FROM DBA.[dbo].[Server_Group] WHERE ID =1;"    
$sconn.Open()    
$cmd = new-object System.Data.SqlClient.SqlCommand ($q, $sconn);    
$cmd.CommandTimeout = 0;    
$dr = $cmd.ExecuteReader();

# Loop through the servers and build an array    
while ($dr.Read()) {     
 Get-DbaMaxMemory -SqlServer $dr.GetValue(0) | Format-Table    
}

$dr.Close()    
$sconn.Close()

I have Listed the sql server(stage, prod, DR servers in a table as per the groups), Now I want to compare the servers with group id's to check wethere the servers(stage,prod, DR) with same group id is having same MAXMemory cofiguration or not.

For this I'm using the below powershell script can you please help me with this, I have created a table with all the servewith grop id.

Request to please help me with the loop thorugh the servers and build an array, so that I can run the MAXMEMORY powershell command to compare it using the group id for all servers.

I have collected all the servers details into a table dbo.server groups
the powershell script should iterate through the table by using the ID and check whether the servers in the ID group has same MAXMEMORY configuration ID server_name Environment 1 ABC0123 prod
1 ABC5123 stage
1 ABC4123 DR
2 DEF0123 prod
2 DEF5123 stage
2 DEF4123 DR
I'm trying to use a powershell script which will check and compare the MAXMEMORY configuration as per the ID(to check whether stage, prod, DR server of the same group_id have similar setting or not), if not then it will display a warning/message as group_ids servers are not configured similarly. Please help me with the script

smd
  • 41
  • 1
  • 5

2 Answers2

0

You're making this script longer than it needs to be. Also, you're using Format-Table prematurely - you should only use the Format-* functions for displaying final information to the user; they output strings, not properly typed data/variables that can be used down the line.

Use the tools that PowerShell and dbatools give you to get your server list, and then pass that list to Get-DbaMaxMemory as a collection.

import-module dbatools
$ServerList = Invoke-DbaSqlQuery -ServerInstance $Server -query "select distinct servername from dba.dbo.server_group where group_id = 1" | Select-Object -ExpandProperty servername;
Get-DbaMaxMemory -ServerInstance $ServerList | Select-Object SqlInstance, SqlMaxMB;

This will give you a list of your SQL instances and the memory they're configured to use. What you do after that...it's hard to say as you haven't clearly defined what you're looking for.

But this may not tell the full story. Wouldn't it be better to check the configured values and what you're currently running with? You can do that with Get-DbaSpConfigure.

import-module dbatools
$ServerList = Invoke-DbaSqlQuery -ServerInstance $Server -query "select distinct servername from dba.dbo.server_group where group_id = 1" | Select-Object -ExpandProperty servername;
Get-DbaSpConfigure -ServerInstance $ServerList | Select-Object ServerName,ConfiguredValue,RunningValue;

You can even create a computed column in that final Select-Object to tell you if the configured & running values differ.

alroc
  • 27,574
  • 6
  • 51
  • 97
  • Thank you very much for the reply. I have edited and provided additional info request you to please help me with powershell script. – smd Mar 18 '18 at 19:34
  • I’m not going to do the whole thing for you. Stack Overflow isn’t a code-writing service. Take the fixed-up version here that gets your base data and try to work it out for yourself. – alroc Mar 18 '18 at 23:52
0

If you just wanted to use dbachecks (which uses dbatools in the background) you can use

$ServerList = (Invoke-DbaSqlQuery -ServerInstance $Server -query "select distinct servername from dba.dbo.server_group where group_id = 1").servername

and 

Invoke-DbcCheck -SQlInstance $ServerList -Check MaxMemory

Or you can set the configuration item app.computername and app.sqlinstance to your server list using

Set-DbcConfig -Name app.sqlinstance -Value $serverlist
Set-DbcConfig -Name app.computername -Value $serverlist

and then you can run this (or any other checks) using

Invoke-DbcCheck -Check MaxMemory
SQLDBAWithABeard
  • 220
  • 1
  • 6