Add a Database to an Availability Group

You have additional databases that you want to add to your already created Availability Group, what do you do?  Follow these directions, and you will have them added in no time.

The business wants you to add another important database to the Availability Group since they now recognize the benefits of Always On.

Below is a screenshot of our current environment.  We have two databases, Surveillance and Training in our Prod_Dbs availability group.

agn1

 

First thing we need to do is restore the database on our Primary replica.  In this environment, that is Wincomputer1.  You can use the restore wizard or by TSQL commands.  Below I am restoring the WideWorldImporters database.

agn2

If we refresh our databases in SSMS, we can see that the database was restored successfully, but has not been added to the Availability Group.

agn3

 

There are some prerequisites that need to be done on the database before it can be added to the AG.  First the database needs to be in Full Recovery Model and we need to make a full and transactional log backup.

First change the Recovery Model to Full.

agn4

 

If we check the General tab, we can see that there has not been a recent backup made.

agn5

 

I will now create a full backup of the database.

agn6

 

And a transaction log backup.

agn7

 

We can check the General properties page again, and see that backups have been made.

agn8

 

Now we can add the database to the Availability Group.  In SSMS, right click Availability Databases and choose Add Database.

agn9

 

This starts the Add Database wizard.

agn10

 

First it wants to know which database to add.  This shows the databases on the server, and which ones have the ability to be joined.  WideWorldImporters meets the prerequisites since we created our backups.

agn11

 

Now it wants us to sign in to our secondary replicas.  You can either press the connect button next to each server if you want to use a different login, or click the connect all button at the bottom to login as the current user.

agn12

 

After you login to each server it will look like this.

agn13

 

Now it wants us to choose an initial data synchronization preference.  Since I am using SQL Server 2016 and my directory structure is the same on all servers, I use Automatic Seeding.  It makes everything very easy.

agn14

It then does a quick validation.

 

agn15

 

Below is the summary page.  You can either click Finish to start or you can click the script button to get the SQL so you can see the commands.

agn16

If everything goes well, you will get a completed successfully message.

agn17

 

If we go right to our dashboard, we can see we have some errors and warnings.  This is because it is still seeding to the other servers.  Nothing to worry about…..yet.

agn18

Depending on the size of the database and network speed, this may take a while, but eventually everything will synch up.  Below it shows that the WideWorldImporters has been added to the Availability Group.  If you still have errors, you will need to start investigating.  Click the warning and errors and work through the issues.

agn19

 

If we refresh our SSMS, we can see that under database, WideWorldImporters is now in a Synchronized state.  If we look under Availability Databases, WideWorldImporters is showing that it was added as an Availability Database.

agn21

That’s it.  Now you are able to protect additional databases in your environment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s