Availability Group didn't get secondary DB added?

Question

Availability Group didn't get secondary DB added?

Brandie Tarvin

SSC Guru

Points: 173061
More actions
October 13, 2023 at 12:33 pm

#4307382

Yesterday I was working on some T-SQL code to create an AG and add databases to the AG. My scripts worked fine, or so I thought. Silly me forgot to check the secondary nodes.
Apparently ALTER AVAILABILITY GROUP [MYAGName] ADD DATABASE MyDBName; only added the database to the primary node of the AG and it didn't get added / synchronized to the other nodes.
I know how to do this through the SSMS GUI, but for auditing reasons, I need paper trails. So T-SQL scripts are the order of the day. Can someone tell me what I did wrong here? Is it really as simple as just running that statement on all the AG nodes? Or is there another step I missed?
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

It's a bit more involved.

On the secondaries you need to do the restores manually, so you need to get the full/logs all restored and the final one done with NORECOVERY

Then once you have staged the DB's on the secondaries, you can run the ALTER AG as you did on the primary, then on secondaries you need to also run "ALTER DATABASE Db1 SET HADR AVAILABILITY GROUP = MyAG;"

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-add-a-database?view=sql-server-ver16#TsqlProcedure

Start data movement on a secondary database - SQL Server Always On | Microsoft Learn

Join a secondary database to an availability group - SQL Server Always On | Microsoft Learn

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/join-a-secondary-database-to-an-availability-group-sql-server?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/start-data-movement-on-an-always-on-secondary-database-sql-server?view=sql-server-ver16

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

this may not be the correct way but check https://www.mssqltips.com/sqlservertip/5437/adding-a-database-to-an-existing-sql-server-always-on-configuration/

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

If you setup the AG to use automatic seeding - then the database will be automatically added to each secondary in the AG. In order for that to work, the database (and history) must not exist on each secondary. If either the database or files exist then it will fail and the database will not be added.

If you are going to use automatic seeding - review this article: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/tune-compression-for-availability-group?view=sql-server-ver16

You will probably want to enable trace flag 9567 - at least while adding databases.

If you don't enable automatic seeding, then you have to restore a backup and as many transaction log backups to get the database joined and added to the group. The wizard provides an option where that is what it will do - backs up to a shared location and restored on each secondary.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs