How to add and remove a database to an availability group in Always On

How to add and remove a database to an availability group in Always On

Rule:

  1. Connect to the server instance that hosts the primary replica.
  2. Use the T-SQL statement as below
  3. 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

Syntax:

ALTER AVAILABILITY GROUP group_name ADD DATABASE database_name [,…n]

E.g.

Group Name = MirzaAG

Database Name= Mirza (Existing database in the instance)

T-SQL

ALTER AVAILABILITY GROUP MirzaAG ADD DATABASE Mirza

How to start data synchronization after adding a database to an Always On availability group

  1. 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.
  2. 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:

  1. 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,

Rule:

  1. This task is supported only on primary replicas. You must be connected to the server instance that hosts the primary replica.
  1. Requires ALTER AVAILABILITY GROUP permission on the availability group,
  2. Use the Alter Availability Group T-SQL statement

Syntax:

ALTER AVAILABILITY GROUP group_name REMOVE DATABASE availability_database_name

e.g.  Group_Name = MirzaAG

Database Name= Mirza

T-SQL

 Alter availability group MirzaAG remove database Mirza

Summary:

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/

 

 

 

Advertisements

About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Always on, Clustering, Database Administration, T-SQL and tagged , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s