Archive

Posts Tagged ‘SQL Server’

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]

Validate SQL Server Database Mail Settings

November 13, 2017 1 comment

In my last post, I shared a script to automate the migration of SQL Server Database Mail settings. In this post, I show how to send test e-mails from all Database Mail profiles on an instance. The migration I was working on contained 21 Database Mail profiles. The following script will send a test e-mail from each profile to confirm successful configuration. I hope you can put this code to use in your migrations.

[sourcecode language=”sql” collapse=”true” title=”GenerateSendDbMailStatements”]
USE msdb;

SET NOCOUNT ON;

/* Drop and create temp table
and declare local variables */
DROP TABLE IF EXISTS #profiles;

CREATE TABLE #profiles
(RowId INT IDENTITY(1,1)
,ProfileName SYSNAME);

/* Update @recipient
Set @noexec = 1 to print the EXEC sp_send_dbmail statements
Set @noexec = 0 to execute the statements immediately */
DECLARE @profilename SYSNAME,
@sqlstr NVARCHAR(2000),
@loopcount INT = 1,
@looplimit INT,
@noexec INT = 1,
@recipient NVARCHAR(255) = N’fgill@concurrency.com’;

/* Insert Database Mail profile names into temp table
and initialize the loop limit variable */
INSERT INTO #profiles
(ProfileName)
SELECT name
FROM sysmail_profile;

SELECT @looplimit = MAX(RowId) FROM #profiles;

/* Loop through the list of Database Mail profiles and
execute sp_send_dbmail for each one */
WHILE @loopcount <= @looplimit
BEGIN

SELECT @profilename = ProfileName FROM #profiles WHERE RowId = @loopcount;

SELECT @sqlstr = CONCAT(‘EXEC msdb.dbo.sp_send_dbmail
@profile_name = ”’,
@profilename,”’,
@recipients = ”’, @recipient, ”’,
@body = ”Database mail succeeded for ‘, @profilename, ‘ on SQLWEBDB-01.”,
@subject = ”Database mail test for ‘, @profilename, ‘;”’);

SELECT @loopcount += 1;

IF @noexec = 1
BEGIN

PRINT @sqlstr;

END
ELSE
BEGIN

EXEC sp_executesql @sqlstr;

END

END
[/sourcecode]