May 25, 2018 at 5:26 pm
As part of DR exercise, we have replicated one of the AlwaysOn configured SQL 2014 servers to Azure DR environment. This is done by ASR agents and replicating SMB3 storage locations to DR.
In a mock test, they spinup the node in DR and asked DBA team to recover the databases. When i logged in i see the AG status as Resolving and the DB status as Not Synchronizing/Recovery Pending status.
The steps i followed to bring them online is
DROP AVAILABILITY GROUP agBOP;
GO
ALTER DATABASE <database_name> SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <database_name> SET ONLINE WITH ROLLBACK IMMEDIATE
GO
----However i made them online and accessible. This process took very long time. I am looking for help on better methods and quickest way to bring them online.
May 29, 2018 at 7:00 am
modify the code as per your requirement
declare @cmd varchar(max)
declare @cmd_suspend varchar(max)
declare @cmd_resume varchar(max)
declare c1 cursor read_only for
select 'ALTER DATABASE ['+DB_name(database_id)+'] SET HADR ' from master.sys.dm_hadr_database_replica_states where is_local = 1
open c1
fetch next from c1 into @cmd
while @@FETCH_STATUS = 0
begin
set @cmd_suspend = @cmd + 'SUSPEND'
print @cmd_suspend
exec(@cmd_suspend)
print '--executed --> ' + @cmd_suspend
set @cmd_resume = @cmd + 'RESUME'
print @cmd_resume
exec(@cmd_resume)
print '--executed --> ' + @cmd_resume
fetch next from c1 into @cmd
end
close c1
deallocate c1
May 29, 2018 at 4:05 pm
Thanks for the reply. I hope my question was not framed correctly.
What are we doing ? - We are replicating only once AG node to DR environment. And at the time of DR test, this node will be bring into Azure DR. Once the VM is online, it is no longer in AG and it acts as a StandAlone node. In this case, the databases which are configured for AG in Prod environment will be in restoring state in DR. I was trying to execute below steps to made online i.e., completely to move them out of AG and make them as a Standalone SQL DB's
DROP AVAILABILITY GROUP agBOP;
GO
ALTER DATABASE <database_name> SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <database_name> SET ONLINE WITH ROLLBACK IMMEDIATE
GO
The above steps are taking a long time. So, i want to check if my way of approach to bring these DB's available is correct or is there any other quick and more appropriate method.
May 30, 2018 at 6:04 am
Have you tried just removing the database from the AG and next take it out of restoring state by setting it to recovery?
ALTER DATABASE database_name SET HADR OFF
RESTORE DATABASE database_name WITH RECOVERY
May 30, 2018 at 9:51 am
Yes, i tried...it failed that restoring can not be done.
May 31, 2018 at 11:01 am
When i execute this command
ALTER DATABASE Test SET HADR OFF
GO
Output is as below
Msg 35220, Level 16, State 1, Line 1
Could not process the operation. AlwaysOn Availability Groups replica manager is waiting for the host computer to start a Windows Server Failover Clustering (WSFC) cluster and join it.
Either the local computer is not a cluster node, or the local cluster node is not online. If the computer is a cluster node, wait for it to join the cluster.
If the computer is not a cluster node, add the computer to a WSFC cluster. Then, retry the operation.
Databases are in Restoring Mode. Any advise?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply