RIP @sqlsoldier

April 3, 2018 Leave a comment

Today I learned that Robert Davis, aka @sqlsoldier, passed away. Among other things, Robert literally wrote the book on SQL Server mirroring. I met him briefly at SQL Saturday Oregon, but interacted with him frequently via the #sqlhelp hashtag. He was often one of the first people to reply to my many questions about the transaction log. One of my proudest moments in IT came when I answered a question about reading the transaction log. Robert asked what I meant, and when I explained myself, he wrote, “In that case, I’d do it Frank’s way.” I don’t think my feet touched the ground for the rest of the day.

Robert was a prime example of the strength of the SQL Server community. That someone with his depth and breadth of knowledge would take time to help others leaves me awed and humbled. While his death leaves me shocked and saddened, I will try to keep his memory alive by learning and sharing knowledge with the community.

Categories: #sqlhelp,

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.

[sourcecode language=”powershell” collapse=”true” title=”Invoke-AgFailover”]

<#
Author: Frank Gill
Date: 2017-11-17
#>
function Invoke-AgFailover {
<#
.SYNOPSIS
Checks specified instance for healthy, synchronous Availability Groups running as secondary and
fails them over
.DESCRIPTION
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.
.EXAMPLE
Invoke-AgFailover -Instance YourSecondaryInstance -NoExec 0;
Any Availability Groups running as secondary on YourSecondaryInstance will be failed over.
.EXAMPLE
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.
.PARAMETER Instance
The instance to check for secondary replicas.
.PARAMETER NoExec
Set to 1 to generate T-SQL script for failover.
#>
[CmdletBinding()]
param
(
[Parameter(Mandatory=$True,
Position = 1,
ValueFromPipeline=$True,
ValueFromPipelineByPropertyName=$True,
HelpMessage=’Which instance do you want to check for secondary replicas?’)]
[Alias(‘secondaryinstance’)]
[string[]]$instance,

[Parameter(Mandatory=$True,
Position = 2,
ValueFromPipeline=$True,
ValueFromPipelineByPropertyName=$True,
HelpMessage=’Set to 1 if you want to execute the database restore. Otherwise ‘)]
[Alias(‘dontrun’)]
[string]$noexec
)
process
{
<# 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;
}
else
{
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 = $secondary.name;

$query = "ALTER AVAILABILITY GROUP $ag FAILOVER;"

<# 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 #>
else
{
$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;
}
}
}
}

[/sourcecode]

TSQL Tuesday #96: Folks Who Have Made a Difference

November 14, 2017 1 comment

T-SQL Tuesday
I started working as a SQL Server DBA in November of 2007. In November of 2010, I attended my second PASS Summit in Seattle. At the time I was working for a large insurance company, and our Microsoft sales rep had scheduled a dinner for my team at 6pm. The afternoon of the dinner, I went to see Paul Randal (b|t) do a 90-minute Spotlight Session called DBA Mythbusters scheduled until 6:15. About 15 minutes into Paul’s session, I texted my boss to let him know I would be late to dinner. The information Paul presented and the way in which he presented made me not want to miss a minute of the session. Walking out of the Convention Center that night, I remember thinking, “I want to do that! I want to teach people about SQL Server!”

The following March, I attended my first SQL Saturday in Chicago. For the session after lunch, I wanted to see Brent Ozar (b|t) present about SQL Server storage. By the time I got to the room for Brent’s presentation, it was SRO. Two doors down, Jes Borland (b|t) was giving a presentation called Make Your Voice Heard! In it, Jes provided advice on blogging, presenting, and leveraging social media to increase visibility within the community.

Attending Jes’s presentation introduced me to the force of nature known as Jes Borland. Her unbridled enthusiasm for the SQL community gave me the motivation to start teaching people about SQL Server. Within a year I started this blog and started presenting at PASS Local Chapters. I am blessed to have become friends with Jes. She continues to inspire me both professionally and personally.

In April of 2011, I attended SQL Skills Immersion Event on Performance Tuning and Optimization – Part 1, taught by Paul Randal and Kimberly Tripp. Since then I have been fortunate to become friends with the two of them. Their involvement in the community never ceases to amaze me. To close I will share my favorite story about the strength of the SQL community.

I attended PASS Summit 2015 with a co-worker who was a first timer. At the Tuesday Welcome Reception, I saw Paul and Kimberly across the room. My colleague and walked over, and as we were waiting to say hello, Paul turned, looked at my colleagues badge and said, “Michael, first timer, how are you! I’m Paul Randal!” Paul welcoming nature and openness to new members of the community is something that I try and emulate on a daily basis.

I volunteer with PASS because I will never be able to give back as much as I’ve gotten. Paul and Jes are two individuals that have given me an enormous amount through the years and I thank them for it.

Categories: PASS,