Problem
We have a SQL Server database that is part of an AlwaysOn Availability Group that has one secondary read-only replica. Every once in a while there is a need to refresh the primary database from another server which requires us to remove AlwaysOn and reconfigure. How can this process be automated?
Solution
As you probably know, we cannot restore a database that is part of a SQL Server AlwaysOn Availability Group. We need to first remove the SQL Server database from the Availability Group in order to restore the database. In this tip we look at how we can automate this process to ensure that AlwaysOn is re-established after the restore without manual intervention.
First we walk through the steps that need to take place and then talk about how this can be automated using SQL Server Agent Jobs.
Remove SQL Server Database From AlwaysOn Availability Group on Primary
The first step is to remove the database from the Availability Group. We can achieve this using the below command.
Note: you would need to replace the <Availability Group> and <Database Name> with your values.
-- runs on primary server
USE master
GO
ALTER AVAILABILITY GROUP [<Availability Group>] REMOVE DATABASE [<Database Name>];
GO
Restore SQL Server Database on Primary
The next step would be to restore the backup to refresh the database on your primary server. You can also add additional steps for any specific requirements to run after the restore such as granting permissions, etc.
This is the backup that is coming from the other server that will be used to refresh the database on the primary server.
-- runs on primary server
USE master
GO
RESTORE DATABASE [<Database Name>]
FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH REPLACE
GO
Ensure Restored SQL Server Database is in Full Recovery on Primary
We need to ensure the database recovery mode is set to FULL in order to meet AlwaysOn requirements.
-- runs on primary server
USE master
GO
ALTER DATABASE [<Database Name>] SET RECOVERY FULL WITH NO_WAIT
GO
Backup SQL Server Database and Log on Primary
Next you need to take a full backup and a transaction log backup of this restored database. To make this easier for the restore you should create the backups on a network share which is accessible from both the PRIMARY and SECONDARY servers. This will avoid having to copy the backup from the PRIMARY to the SECONDARY and will also save time by eliminating the copy step.
-- runs on primary server
BACKUP DATABASE [<database Name>]
TO DISK='<Shared Network Location>\<Database Name>.bak' WITH FORMAT, INIT, COMPRESSION
GO
BACKUP LOG [<database Name>]
TO DISK='<Shared Network Location>\<Database Name>.trn' WITH FORMAT, INIT, COMPRESSION
GO
Add SQL Server Database to Availability Group on Primary
Next we need to add the database back to the Availability Group.
-- runs on primary server
USE master
GO
ALTER AVAILABILITY GROUP [<Availability Group>] ADD DATABASE [<Database Name>];
GO
Now you are done with all the steps on the PRIMARY server.
Restore SQL Server Database on Secondary
Next we need to restore the full backup and log backup on the secondary server.
-- runs on secondary server
USE master
GO
RESTORE DATABASE [<database Name>]
FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH FILE=1, REPLACE, NORECOVERY
GO
RESTORE LOG [<database Name>]
FROM DISK='<Shared Network Location>\<Database Name>.trn' WITH FILE=1, REPLACE, NORECOVERY
GO
Check Status of SQL Server Database and then Add to Availability Group on Secondary
The next step is important, this enables data synchronization after the database has been restored and is ready to join the Availability Group.
-- runs on secondary server
-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes
if (serverproperty('IsHadrEnabled') = 1)
and (isnull((select member_state
from master.sys.dm_hadr_cluster_members
where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
and (isnull((select state
from master.sys.database_mirroring_endpoints), 1) = 0)
begin
select @group_id = ags.group_id
from master.sys.availability_groups as ags
where name = N'<Availability Group>'
select @replica_id = replicas.replica_id
from master.sys.availability_replicas as replicas
where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
while @conn <> 1 and @count > 0
begin
set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
if @conn = 1
begin
-- exit loop when the replica is connected, or if the query cannot find the replica status
break
end
waitfor delay '00:00:10'
set @count = @count - 1
end
end
end try
begin catch
-- If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE [<Database Name>] SET HADR AVAILABILITY GROUP = [<Availability Group>];
GO
SQL Server Agent Jobs
To automate this process we can create SQL Server Agent jobs on both servers:
- Primary Server – Create one job that does the following:
- Removes database from Availability Group
- Restores the database
- Sets recovery model of database to FULL
- Adds database to Availability Group
- Creates database backup and log backup
- optional – runs job on secondary server using sp_start_job (there are other things you would need to setup to do this)
- Secondary Server – Create one job that does the following:
- Restores database backup and log backup
- Checks database status and adds database to Availability Group
If you don’t have the primary server start the job on the secondary server you would need to figure out when to schedule this job to run after the job on the primary server completes.
Next Steps
