Need to create Powershell report for SQL Always on secondary serves.

Parag Kambli 0

Hello All,
A strange request.

We are running multiple always on servers.

Backups takes place on secondary replica of always on server.

I need to create a report in powershell or t-sql that would identify secondary replica and verify backup status.

DM1-- instance name

M1-- primary instance

M2 --secondary instance

$sqlservers = @( 
     'DM1',
     'M2',
     'M3')
$dbrecords = @() ## array for query results
Foreach($server in $sqlservers) {
   $dbrecords += invoke-sqlcmd -query 'DROP TABLE IF EXISTS #mysgbackupstatus1;
DROP TABLE IF EXISTS ##mysgbackupstatus;


CREATE TABLE #mysgbackupstatus1(
replica_server_name nvarchar(256),
role_desc nvarchar(60))
-----below query writing only secondary replica to #mysgbackupstatus1 table 
INSERT INTO #mysgbackupstatus1 (replica_server_name, role_desc) 
SELECT replica_server_name
from sys.dm_hadr_availability_replica_states as ha
inner join sys.availability_replicas as a on  ha.replica_id=a.replica_id
WHERE role_desc = ''SECONDARY'';

Each row in #mysgbackupstatus represents secondary SQL instance.

I am failing here as I do not know how to connect to SQL instance by fetching row from #mysgbackupstatus

once connected I am planning to run below CTE into a CSV file.

   $dbrecords += invoke-sqlcmd -query ';with backup_cte as(    select        database_name,        backup_type =            case type                when ''D'' then ''database''                when ''L'' then ''log''                when ''I'' then ''differential''                else ''other''            end,        backup_finish_date,        rownum =             row_number() over            (                partition by database_name, type                 order by backup_finish_date desc            )    from msdb.dbo.backupset)' -ServerInstance $server}$dbrecords | Export-Csv "C:\Temp\SQLOutput.csv"## $dbrecords | Out-GridView

I am messed up and do not know if what I am talking is feasible or not.

Please help and guide.
I am rookie in world of development.

Thank you in advance.

1 answer

Erland Sommarskog 101K MVP

I am not sure that I understand. But that if you are connecting to many servers as suggested by the Foreach loop, you are creating a temp table on each server. And when you disconnect from that server to move on to the next one, the temp table is dropped. You need to return the data to the PowerShell script with a query and then save that to some data structure in the PowerShell script. (Or write to a table on a master server.)

For an example of how to capture the data, see Example 7 in the documentation for Invoke-Sqlcmd.

Parag Kambli 0

Right,
But temp table will be active till transaction is complete and result is written.

Now I am planning convert server name to string and feed it back to new powershell array .

first array will contain all SQL servers.

after verifying SQL will identify secondary servers only and write to temp table.

comment:

Export statement in first block listed below is actually not needed but I am using it to verify if I am getting output or not. It is working for me as of now.

$sqlservers = @( 
     'DM1', 
     'M2', 
     'M3' )
$dbrecords = @() ## array for query results
Foreach($server in $sqlservers) {
   $dbrecords += invoke-sqlcmd -query 'DROP TABLE IF EXISTS #mysgbackupstatus1
CREATE TABLE #mysgbackupstatus1(
replica_server_name nvarchar(256))
-----below query writing only secondary replica to #mysgbackupstatus1 table 
INSERT INTO #mysgbackupstatus1 (replica_server_name) 
SELECT replica_server_name
from sys.dm_hadr_availability_replica_states as ha
inner join sys.availability_replicas as a on  ha.replica_id=a.replica_id
WHERE role_desc = ''SECONDARY''
declare @sql_output varchar (max)
set @sql_output = '' ''      
select @sql_output =       
       coalesce (@sql_output + sql + char (10), '' '')
  from #mysgbackupstatus1
print @sql_output
' -ServerInstance $server
}
$dbrecords | Export-Csv "C:\Temp\SQLOutput.csv"
## $dbrecords | Out-GridView

Now convert servernames in temp table to string and push it into new powershell array one at a time.--- I am stuck at this step.

Second array contains always on secondary servers and it will run verify backup script.

$sqlservers = @( 
     'DM1', 
     'M2', 
     'M3' )
$dbrecords = @() ## array for query results
Foreach($server in $sqlservers) {
$dbrecords += invoke-sqlcmd -query ';with backup_cte as
(
    select
        database_name,
        backup_type =
            case type
                when ''D'' then ''database''
                when ''L'' then ''log''
                when ''I'' then ''differential''
                else ''other''
            end,
        backup_finish_date,
        rownum = 
            row_number() over
            (
                partition by database_name, type 
                order by backup_finish_date desc
            )
    from msdb.dbo.backupset
)select database_name,backup_type,backup_finish_date from backup_cte' -ServerInstance $server
}
$dbrecords | Export-Csv "C:\Temp\SQLOutput.csv"
## $dbrecords | Out-GridView

Erland Sommarskog 101K Reputation points MVP

2024-03-29T17:18:52.7433333+00:00

declare @sql_output varchar (max) set @sql_output = '' ''
select @sql_output =
coalesce (@sql_output + sql + char (10), '' '') from #mysgbackupstatus1 print @sql_output

There are two problems here. First, the correct result of that SELECT statement is undefined. You may get what you are looking for. You may get something else. But that would not be a bug.

Use the STRING_AGG function instead. Or the FOR XML PATH quirk if you need to support versions older than SQL 2017.

The other problem is that the PRINT statement only supports 8000 bytes, so there is a risk for truncation here.

Parag Kambli 0

Hello Erland,

Thanks for help.

Below is final script.

abc.txt file contains input server names.

SQLOutput.txt contains secondary server names sorted by first query.

Final Output i.e. backup report is in SQLOutput2.csv file.

SQLOutput2 output is not formatted, and I need to group by using server name. I am getting errors when running this query, but it works.

$dbrecords = @() ## array for query results
$servers2Vlidate = Get-Content -Path 'C:\Temp\abc.txt'
Foreach($server in $servers2Vlidate) {

   $dbrecords += invoke-sqlcmd -query 'SELECT replica_server_name
from sys.dm_hadr_availability_replica_states as ha
inner join sys.availability_replicas as a on  ha.replica_id=a.replica_id
WHERE role_desc = ''SECONDARY''' -ServerInstance $server

}

$dbrecords | Out-File  "C:\Temp\SQLOutput.txt"
## $dbrecords | Out-GridView

$dbrecords2 = @() ## array for query results
$servers2Vlidate = Get-Content -Path 'C:\Temp\SQLOutput.txt'
Foreach($server in $servers2Vlidate) {

   $dbrecords2 += invoke-sqlcmd -query ';with backup_cte as
(
    select
        database_name,
        backup_type =
            case type
                when ''D'' then ''database''
                when ''L'' then ''log''
                when ''I'' then ''differential''
                else ''other''
            end,
        backup_finish_date,
        rownum = 
            row_number() over
            (
                partition by database_name, type 
                order by backup_finish_date desc
            )
    from msdb.dbo.backupset
)select database_name,backup_type,backup_finish_date from backup_cte' -ServerInstance $server

}


$dbrecords2 | Export-Csv "C:\Temp\SQLOutput2.csv"
## $dbrecords | Out-GridView
 Out-File -FilePath .\Process.txt

Erland Sommarskog 101K Reputation points MVP

2024-04-09T20:16:59.76+00:00

I'm glad to hear that you got things working!