Remove a Secondary Replica from an Availability Group & Remove a Database from AlwaysOn for Maintenance or Migration

Remove a Secondary Replica from an Availability Group & Remove a Database from AlwaysOn for Maintenance or Migration & Remove primary replica or drop Availability Group for Reconfiguration of AlwaysOn.

--------------------------------------------------------------------------------

To remove a secondary replica

-----------------------------------------------------------------------------------------------

1. Connect to the server instance that hosts the primary replica.
2. Use the ALTER AVAILABILITY GROUP statement, as follows:
   
   ALTER AVAILABILITY GROUP group_name REMOVE REPLICA ON 'instance_name' [,...n]
   where group_name is the name of the availability group and instance_name is the server instance where the secondary replica is located.
   The following example removes a secondary replica from NVAGCORPGRP AG 

3. Example:-

4. USE [master]

5. go
   Alter availability group [NVAGCORPGRP] remove replica on N'Rakesh\NSQL'

6. if Command execute successfully it means, you have successfully removed secondary replica.

if you receive below error:-

Msg 41190, Level 16, State 10, Line 1

Availability group 'NVAGCORPGRP' failed to process remove-replica command. The local availability replica is not in a state that could process the command. Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command.

Then you need to check whether you are executing above command from primary Replica or not.

-->How to check Current replica is primary replica..

and

-Make sure you are putting correct secondary replica name in above command.

and

--Make sure you are executing above command from primary replica

3.Then Go to other secondary Replica Targets  and drop the database  from Secondary replica.

4. Make sure in secondary Replica  database will be showing in restoring state.  

How To Remove Databases from AlwaysOn for Maintenance or Migration

--------------------------------------------------------------------------------------------------------------------------

Assume we have 4 nodes with 2 AG  and 3  databases like below.

Servers : Summary
Primary   Server	MgmtIP	secondary  Server	MgmtIP
Node1	192.168.1.10	Node3	192.168.1.12
Node2	192.168.1.11	Node4	192.168.1.13

Listeners

Listener Name/Ip	AvailabilityGroup - Name	Primary Server	Secondary Replica	Database
192.168.1.14  - NodesListner1	NodesAG1	Node1	Node2  Node3 Node4	AdventureWorks_oltp AdventureWorks_olap
192.168.1.15  -NodeListener2	NodesAG2	Node2	Node1 Node3 Node4	AdventureWorks_Access

Now the task is to remove all database one by one from primary replica and its corresponding secondary replica.

The Steps would be:-

*****Steps to remove AdventureWorks_oltp database from availability group NodesAG1**********

To remove AdventureWorks_oltp database from AG [NodesAG1]

RDP to Node1

SSMS à Node1 instance

                          use master;

                           go

                          Alter availability group [NodesAG1] remove database [AdventureWorks_oltp]

                          go

After successful execution of this command on Node1

All secondary replicas(Node2,Node3,Node4) AdventureWorks_oltp   database will come to restoring state..

Change connection and connect to SQL Server Instance  Node2

Execute command

        use master

                go

  drop database AdventureWorks_oltp   

              go

Change connection and connect to   Node3

            use master

                go

        drop database AdventureWorks_oltp   

              go

Change connection and connect to   Node4

              use master

                go

           drop database AdventureWorks_oltp   

              go

Change SQL connection back to   Node1

                                use master

                                go

                                Alter database [AdventureWorks_oltp] set single_user with rollback immediate

                                Go

                            Select  ‘KILL‘ + convert(varchar(3), spid) from sys.sysprocesses where dbid=db_id(‘AdventureWorks_oltp’)

--Copy output and execute in query window

     KILL <SPID>

                                Use master

                                go

                                Drop database [AdventureWorks_oltp]

After successful execution of all commands, database "AdventureWorks_oltp" should not appear in any of Replica.

*****Steps to remove AdventureWorks_olap database from availability group NodesAG1**********

RDP to Node1

SSMS à Node1 instance

                            use master;

                                go

                                Alter availability group [NodesAG1] remove database [AdventureWorks_olap]

                               Go

After successful execution of this command on Node1

All secondary replicas(Node2,Node3,Node4) AdventureWorks_olap database will come to restoring state..

Change connection and connect to Instance  Node2

Execute command

        use master

                go

  drop database AdventureWorks_olap

              go

Change connection and connect to   Node3

            use master

                go

        drop database AdventureWorks_olap

              go

Change connection and connect to   Node4

              use master

                go

           drop database AdventureWorks_olap

              go

Change SQL connection back to   Node1

                                use master

                                go

                                Alter database [AdventureWorks_olap] set single_user with rollback immediate

                                Go

                            Select  ‘KILL‘ + convert(varchar(3), spid) from sys.sysprocesses where dbid=db_id(‘AdventureWorks_olap’)

--Copy output and execute in query window

     KILL <SPID>

                                Use master

                                go

                                Drop database [AdventureWorks_olap]

After successful execution of all commands, database "AdventureWorks_olap" should not appear in any of Replicas (Node1,Node2,Node3,Node4).

*****Steps to remove AdventureWorks_Access database from availability group NodesAG1**********

Make sure AdventureWorks_Access  database is primary on Node2 use this Are You the Primary Replica for this database

you get AdventureWorks_Access database primary replica is Node2

RDP to Node2 

SSMS à Node2 instance

                            use master;

                                go

                                Alter availability group [NodesAG2] remove database [AdventureWorks_Access]

                               Go

After successful execution of this command on Node2

All secondary replicas(Node1,Node3,Node4) AdventureWorks_Access database will come to restoring state..

Change connection and connect to Instance  Node1

Execute command

        use master

                go

  drop database AdventureWorks_Access

              go

Change connection and connect to   Node3

            use master

                go

             drop database AdventureWorks_Access

              go

Change connection and connect to   Node4

              use master

                go

           drop database AdventureWorks_Access

              go

Change SQL connection back to   Node2

                                use master

                                go

                                Alter database [AdventureWorks_Access] set single_user with rollback immediate

                                Go

                            Select  ‘KILL‘ + convert(varchar(3), spid) from sys.sysprocesses where dbid=db_id(‘AdventureWorks_Access’)

--Copy output and execute in query window

     KILL <SPID>

                                Use master

                                go

                                Drop database [AdventureWorks_Access]

After successful execution of all commands, database "AdventureWorks_Access" should not appear in any of Replicas (Node1,Node2,Node3,Node4).

In this way, we ca remove all databases from all nodes.

How to remove primary replica or Drop Availability Group for Reconfiguration of AlwaysOn.

Because of some unknown issue, if in case  you have to drop Primary replica from AlwaysOn to reconfigure AlwaysOn again, you have to drop Availability Group.

In order to do so, go to Primary Replica and execute the command

USE [master]

GO

DROP AVAILABILITY GROUP [NVAGCORPGRP];


GO