Archive for the ‘Availability Groups’ Category

SQL Server Availability Groups and Automatic Seeding

June 20, 2018 Leave a comment

Microsoft released Availability Groups (AG) as a feature in SQL Server 2012. Prior to SQL Server 2016, there were two methods of adding a database to a new AG replica.

  1. You could provide the Add Database to Availability Group wizard a file share accessible by the primary and secondary replicas.  SQL Server would run FULL and LOG backups of each database to the share and use them to restore the database(s) to each replica.
  2. You could manually run a FULL and LOG backup of each database, copy the backup files to each replica, and restore the databases WITH NORECOVERY.

With SQL Server 2016. Microsoft has provided a third option, Automatic Seeding.  With Automatic Seeding, you specify the databases and the replicas and SQL Server will begin transferring data to each replica.  The duration of the seeding process depends on the size of the database and the network bandwidth available between primary and secondary replica.

There are a few considerations to keep in mind when considering Automatic Seeding.  Virtual log files in the primary database cannot be freed while the automatic seeding process is running.  For a large, active database this can lead to excessive log growth.  AU\utomatic seeding is a single-threaded process and is limited to 5 databases.  Finally, compression is disabled for Automatic Seeding by default, but can be enabled by turning on trace flag 9567.  This can improve performance of the seeding process but will increase CPU overhead.

The status of the Automatic Seeding process can be monitored using the following query: (download)

USE master;

SELECT local_database_name,
database_size_bytes / 1045876 AS [Database Size (mb)],
transferred_size_bytes / 1045876 AS [Transferred Size (mb)],
transfer_rate_bytes_per_second / 1045876 AS [Transfer Rate/Sec (mb)],
(database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second AS [Time Remaining (sec)],
WHEN ((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) < 360000
THEN '0'
+ RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) / 3600)
+ ':' + RIGHT('0' + RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) % 3600 / 60), 2)
+ ':' + RIGHT('0' + RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) % 60), 2) AS [Time Remaining]
FROM sys.dm_hadr_physical_seeding_stats
WHERE internal_state_desc = 'ReadingAndSendingData'
ORDER BY remote_machine_name;

The query returns the following result set:

Note that one row will be returned per replica being seeded.

Automatic Seeding makes initializing AG replicas simple. I hope you can put the status query to good use.

PowerShell Function to Automate Availability Group Failover

November 18, 2017 1 comment

I’ve been working with Availability Groups (AG) since their release in SQL Server 2012 and have always wanted to leverage PowerShell to administer them.  Recently I received a request to develop an automated process for failing Availability Groups over gracefully prior to server patching.  Believe it or not, but a hard shutdown of the primary replica is NOT the best way to force AG failover.

The function takes a replica name as input and queries system tables for Availability Groups running as secondary that are online, healthy, and synchronous.  For each AG found, the function generates an ALTER AVAILABILITY GROUP statement.  If the -noexec parm is set to 0, the command will be executed.  If -noexec is set to 1, the command will be written out to a file.

When writing the function, I started out trying to use the native PowerShell Availability Group cmdlets.  After several false starts, I found it easier to develop the T-SQL code in Management Studio and use Invoke-Sqlcmd to execute the code.  The code is available below.  I hope you can put it to use.

Author: Frank Gill
Date: 2017-11-17
function Invoke-AgFailover {
Checks specified instance for healthy, synchronous Availability Groups running as secondary and
fails them over
Checks the instance passed in for healthy, synchronous Availability Groups running as secondary and
fails them over.  If the instance is not hosting secondary replicas, a message will be output.
If there are AGs running as secondary, a message will output for each, including AG name, destination,
and failover duration.
Invoke-AgFailover -Instance YourSecondaryInstance -NoExec 0;
Any Availability Groups running as secondary on YourSecondaryInstance will be failed over.
Invoke-AgFailover -Instance YourSecondaryInstance -NoExec 1;
If Availability Groups are running as secondary on YourSecondaryInstance, T-SQL commands for each AG failover
will be generated and written to C:\AGFailover\failover_YourAgName_YYYYMMDD_HHMMSS.sql.
The instance to check for secondary replicas.
Set to 1 to generate T-SQL script for failover.
Position = 1,
HelpMessage='Which instance do you want to check for secondary replicas?')]

Position = 2,
HelpMessage='Set to 1 if you want to execute the database restore.  Otherwise ')]
<# If the $noexec is set to 1, create file path to hold out files #>
if($noexec -eq 1)
$rundate = Get-Date -Format yyyyMMdd_HHmmss;
$outpath = "C:\AgFailover";
if((Test-Path -Path $outpath) -eq $true)
Remove-Item -Path "$outpath\*" -Recurse;
New-Item -Path $outpath -ItemType Directory;

<# Create query to check for failover-eligible AGs #>
$query = "SELECT g.[name], ar.replica_server_name
FROM sys.dm_hadr_availability_replica_states r
INNER JOIN sys.availability_replicas ar
ON ar.group_id = r.group_id
AND ar.replica_id = r.replica_id
INNER JOIN sys.availability_groups g
ON g.group_id = ar.group_id
WHERE r.role_desc = N'SECONDARY'
AND r.recovery_health_desc = N'ONLINE'
AND r.synchronization_health_desc = N'HEALTHY'
AND ar.availability_mode_desc = N'SYNCHRONOUS_COMMIT';"

<# Execute failover-eligible query #>
$secondaries = Invoke-Sqlcmd -ServerInstance "$instance" -Database master -Query $query;

<# Output message if there are no failover-eligible AGs #>
if($secondaries.Count -eq 0)
Write-Output "There are no Availability Group replicas available to fail over to $instance."

<# If eligible AGs exist, loop through them #>
foreach($secondary in $secondaries)
$secreplica = $secondary.replica_server_name;
$ag = $;


<# If $noexec is set to 0, execute the AG failover
and output a message when complete #>
if($noexec -eq 0)
$starttime = Get-Date;
Invoke-Sqlcmd -ServerInstance "$instance" -Database master -Query $query;
$endtime = Get-Date;
$duration = (New-TimeSpan -Start $starttime -End $endtime).Seconds;
Write-Output "Failed Availability Group $ag to replica $instance in $duration seconds";
<# If $noexec is not set to 0, write a file out to the path built above for each AG #>
$comment = "/* Run against instance $instance */" ;
$comment | Out-File -FilePath "$outpath\failover_$ag`_$rundate.sql" -Append;
$use = "USE master;";
$use | Out-File -FilePath "$outpath\failover_$ag`_$rundate.sql" -Append;
$query | Out-File -FilePath "$outpath\failover_$ag`_$rundate.sql" -Append;

Monitoring and Alerting for Availability Groups and Why I Love PASS Summit

October 31, 2016 Leave a comment

I have returned from my eighth PASS Summit and, as in years past, they just keep getting better.  I am amazed and humbled that I am a part of the PASS community.  PASS has given me the opportunity to meet and learn from many of the best and brightest in the SQL world.  When I meet first-timers, I tell them to talk to people, no matter who they are.  If you share an interest, they will share with you.  If you have never been to PASS Summit, I recommend it.  If you cannot make Summit, get involved with your local PASS Chapter or SQL Saturday.  Virtual Chapters are another great way to find out what PASS has to offer.

One of the highlights of this year’s Summit was Shawn Meyers’ (t) presentation on Monitoring and Alerting of Availability Groups. (You will need to be logged in to the PASS website to access the link.)  I have worked with Availability Group since its release in SQL Server 2012 and Shawn provided the best solution for monitoring and alerting.

Shawn suggested implementing three alerts:

  • 1480 – AG Role Change
  • 35264 – AG Data Movement Suspended
  • 35265 – AG Data Movement Resumed

Additionally, Shawn provided an MSDN link with recommendations for using Policy-Based Management (PBM) to monitor AGs.  The custom policies monitor the recovery time objective (RTO) and recovery point objective (RPO) for your availability groups.  Microsoft recommends 600 seconds, or 10 minutes, for RTO, and 3600 seconds, or 60 minutes, for RPO. You can set your own values when defining the policy conditions.

I have scripted out the PBM conditions, policies, and SQL Server Agent alerts.  The conditions and policies can be used as-is, because they use system schedules.  The alerts need to updated to replace the TestOperator with an SQL Agent operator of your own.

I hope you can make use of these scripts.  And I encourage you to get involved with PASS. You will be amazed at what it can do for you.