September 18, 2018 at 7:48 am
Hi All
Quick question, we're migrating onto an infrastructure with SQL Server Availability groups, and we have one large database we'd like to include in the group, among others. To save time, the team has proposed creating the group by restoring the same backup (and subsequent diffs and log backups) onto both primary and secondary replicas, and then adding them into the Availability Group with T-SQL.
I've never done it this way before, and I've no way to test it myself at the moment. I'm wondering if anyone could tell me if it would work?
Many thanks
September 18, 2018 at 11:55 am
Hi,
I don't see any reason why it shouldn't work.
As long as primary and secondary replicas have same LSN, you should just be able to JOIN them in AG.
adding them into the Availability Group with T-SQL.
You are usingT-SQL to add replicas into AG ?
September 19, 2018 at 3:28 am
Joy Smith San - Tuesday, September 18, 2018 11:55 AMHi,
I don't see any reason why it shouldn't work.
As long as primary and secondary replicas have same LSN, you should just be able to JOIN them in AG.adding them into the Availability Group with T-SQL.
You are usingT-SQL to add replicas into AG ?
I can't give any reason why it won't work either, something about it just doesn't sit right with me. Since I can't test it myself I was hoping for someone else might be able to give me some reassurance before our next migration test.
I believe we are using T-SQL to add them to the group, simple ALTER AVAILABILITY GROUP statements and the like.
September 19, 2018 at 4:04 am
As long as you restore the secondary database with norecovery and then add them to the AG with JOIN_ONLY it should be fine. Try it with a smaller DB first.
September 19, 2018 at 5:17 am
Go ahead.
We have successfully migrated and configured AG for 20+ servers in last one month (SQL 2016).
Servers were in multiple subnets and we also configured multiple AGs on each Primary
Database size was around 500GB.
Well, we din't automate TLog restores while moving the DB, though we could have easily done.
Instead we preferred preparing script and manually executed the same on a daily basis, till the cutover.
In case of any issues we resolved it then there itself and ensured Primary and Replicas have exactly same data all the time.
Then it was just about JOIN them in AG for final go-live.
Btw, Take care of the logins in all secondaries, test it by failing over.
Also you may want to ensure that NO transactions are coming into existing DB during the go-live and do a final TLog backup/restore on all servers, before adding to AG.
September 20, 2018 at 2:14 pm
If your servers are all sitting on the same rack (or under the same roof and not separated by region) and have the resources and IO, you could go with the auto-seed method.
I prefer the logshipping method with no recovery. https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server?view=sql-server-2017
Do yourself a favor and make sure your database has the correct owner before you add additionals to the AG. Often times it will default the owner to the person who restored it. It's annoying to fix after the fact as it involves failovers and an outage window.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply