How to add and remove a database to an availability group in Always On
- Connect to the server instance that hosts the primary replica.
- Use the T-SQL statement as below
- After you add a database to an availability group, you need to configure the corresponding secondary database on each server instance that hosts a secondary replica.
To add a database to an availability group
ALTER AVAILABILITY GROUP group_name ADD DATABASE database_name [,…n]
Group Name = MirzaAG
Database Name= Mirza (Existing database in the instance)
ALTER AVAILABILITY GROUP MirzaAG ADD DATABASE Mirza
How to start data synchronization after adding a database to an Always On availability group
- For each new primary replica, secondary databases must be prepared on the server instances that host the secondary replicas. Then each of these secondary databases must be manually joined to the availability group.
- If the file paths are identical on every server instance that hosts a replica for an availability group then the data synchronization starts automatically else we have to manually to do it as below.
To start data synchronization manually follow these steps:
- Restore current backups of each primary database and its transaction log (using RESTORE WITH NORECOVERY). You can use either of the following alternative approaches:
- Manually restore a recent database backup of the primary database using RESTORE WITH NORECOVERY, and then restore each subsequent log backup using RESTORE WITH NORECOVERY. Perform this restore sequence on every server instance that hosts a secondary replica for the availability group.
- As soon as possible, join each newly prepared secondary database to the availability group.
To remove an availability database,
- This task is supported only on primary replicas. You must be connected to the server instance that hosts the primary replica.
- Requires ALTER AVAILABILITY GROUP permission on the availability group,
- Use the Alter Availability Group T-SQL statement
ALTER AVAILABILITY GROUP group_name REMOVE DATABASE availability_database_name
e.g. Group_Name = MirzaAG
Database Name= Mirza
Alter availability group MirzaAG remove database Mirza
Removing an availability database from its availability group ends data synchronization between the former primary database and the corresponding secondary databases. The former primary database remains online. Every corresponding secondary database is placed in the RESTORING state.
At this point there are alternative ways of dealing with a removed secondary database:
- If you no longer need a given secondary database, you can drop it.
- If you want to access a removed secondary database after it has been removed from the availability group, you can recover the database. However, if you recover a removed secondary database, two divergent, independent databases that have the same name are online. You must make sure that clients can access only one of them, typically the most recent primary database.
Looking forward your valuable likes and comments!
Facebook Page: https://www.facebook.com/MirzaDBA/