November 29, 2023 at 2:29 pm
Hi All,
I am planning for HA DR solution for transactional replication published database. (Not for distribution DB).
It is in SQL 2014 standard edition and having 4 subscriber for only one database, I am planning to have either log shipping or DB mirroring into another server.
Can anyone suggest which is best to for replicating publisher DB.
November 29, 2023 at 3:16 pm
Think the best thing first would be to also plan in a 2022 upgrade also.
2014 is end of life, and end of extended support in approx 8 months, after which you are on your own supporting it as MSFT wont touch it unless you pay them lots, move to Azure and get an ESU.
DB Mirroring is depreciated, while it still exists in the product, its there for backwards compatibility, so immedietly that would be one to rule out.
Your main choice really is an AOAG, as this will be less work to get replication working again after a failover.
With logshipping or DB Mirroring you can't easily failover the publication aspect of replication so it would generally be a rip it out and start again situation due to server names, servers not enabled for publication etc.
November 29, 2023 at 3:28 pm
Just piling on. I think @Ant-Green has nailed it. Availability Groups are where Microsoft has been investing all it's time and money. So if you're going to be building an HA/DR solution, you really can't go wrong with following where they are leading.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2023 at 3:28 pm
Just piling on. I think @Ant-Green has nailed it. Availability Groups are where Microsoft has been investing all it's time and money. So if you're going to be building an HA/DR solution, you really can't go wrong with following where they are leading.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 30, 2023 at 1:39 pm
Thanks both for your response. It is standard edition, I have to check with business for enterprise for Always ON.
Sorry, It is purely for DR purpose. In case of anything to the current publication database, we need to bring the DR up. Not sure weather replication can work or not. As per Ant-Green it looks like I need to reconfigure replication. (OR) wait for current publication to bring online.
November 30, 2023 at 1:46 pm
There is AOAG's in standard edition, but they have limitations, check out Basic Availability Groups.
But yeah this is the way you want to be doing it now, in an AOAG as they support replication on listeners.
Other methodologies don't so you have a replication headache on your hands on failover.
December 1, 2023 at 2:44 pm
Thank you very much, This is very clear now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply