Are there enhancements in SQL SERVER 2019 transactional replication working as compared to SQL SERVER 2008 R2 transactional replication. Also, of all HA/DR solutions available in SQL SERVER 2019 which allows minimum data loss in case of disaster.

DM 521

Are there enhancements in SQL SERVER 2019 transactional replication working as compared to SQL SERVER 2008 R2 transactional replication. Also, of all HA/DR solutions available in SQL SERVER 2019; which allows for minimum data loss in case of disaster. Which of SQL Server's HA/DR option(s) is/are mostly used by mission critical businesses say hospitals/banks etc and will be supported for longest time period in SQL SERVER. Thanks in advance.

Accepted answer

Erland Sommarskog 101.9K Reputation points MVP

2024-04-28T18:55:01.97+00:00

Replication has been a "mature" technology for a long time, so there are no dramatic changes in that area. There might have been minor improvements. I don't use Replication a lot myself, so I have not paid attention. To see what changes there may have been in SQL 2016/17/19, see here: https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16.

As for HA/DR solutions, I would say the best choice would be a local availability group with synchronous commit. To protect yourself against a data centre disaster, you need a failover site of some sort. You could set up a distributed availability group. You can also use Azure Managed Instance, at least with SQL 2022. You remote site must have asynchronous commit. Beware that these solutions are complex and requires good understanding, or else you may experience downtime or data loss due incorrect actions.
Please sign in to rate this answer.
DM 521 Reputation points

2024-05-05T18:41:04.6533333+00:00

Hello Erland Sir,

Thanks for your suggestions. Your suggestions will surely help us to move in right direction. We are in process of finding/deciding that apart from 'Replication' which technologies can be used for outcome equal or better than 'Replication' on specs of input effort/infra needed/time for which it will be supported by Microsoft/ease of deploying (Replication is a bit complex when it comes to setting up transactional one) and ease of using in case of downtime or disaster/cost of solutions/impact on database performance of the solution as apart from user activity; these solutions also impact performance of a db; etc.

Can you please suggest official documentation/resource having steps for setting up local availability group with synchronous commit. I suppose synchronous commit means data on 'production server' and 'replica server' will be exactly identical in every respect (down to each field level for each table). Also, request for distributed availability group.

On the specs of 'ease of setting up/deploying' which ones are easier than Replication/db mirroring.

On the specs of 'availability' compared to replication/db mirroring which are the 3 most recommended solutions even though they may be complex in setup and may be requiring more infra than replication. Is there some matrix showing these details.

Thanks in advance.

Erland Sommarskog 101.9K Reputation points MVP

2024-05-05T18:58:35.4566667+00:00

Can you please suggest official documentation/resource having steps for setting up local availability group with synchronous commit.

I would have to Google to find links - but then again, you could use Google yourself. Official documentation - learn.microsoft.com, that's what you should have in the URL.

I suppose synchronous commit means data on 'production server' and 'replica server' will be exactly identical in every respect (down to each field level for each table). Also, request for distributed availability group.

They will be identical eventually, no matter if you have synchronous or asynchronous commit. With synchronous commit, a transaction does not commit until it has been written to the log file on the replica server. The replica server will then have to apply that log record locally, which could happen later. But if you lose the primary and the replica becomes the new primary, you cannot access the replica until all log records have been applied. So you will get the state of the primary when it crashed.

With asynchronous commit, writes to the replica's log file are made outside the transaction asynchronously. The process for making the replica is the new primary is the same. But, since write to the log file is asynchronous, transactions that were committed to the primary in case of a crash, may not have been written to the log file for the replica, and thus the result of these transactions may be lost.

On the specs of 'ease of setting up/deploying' which ones are easier than Replication/db mirroring.

In terms of simplicity and ease of understanding, I think nothing beats log shipping. But with log shipping, data loss in case of a disaster is inevitable.

There is also failover clustering, which has to count as simpler than Availability Groups, but since disks are shared, this means than you are less protected against disk failures.

DM 521 Reputation points

2024-05-10T18:58:27.28+00:00

Hello Erland Sir,

Accepting your suggestions as answer. We are still doing tests and trying to see what suffices our needs. Thanks.
Sign in to comment

1 additional answer

ZoeHui-MSFT 33,301 Reputation points

2024-04-29T03:00:35.3833333+00:00

Hi @DM,

For the newest transactional replication, you may check the official documentation.

SQL Server high availability (HA) is about providing service availability and 100% uptime through redundant and fault-tolerant components at the same location. Disaster Recovery (DR) is about providing service continuity and minimizing downtime through redundant & independent site in a distinct location. Commonly Solutions for HA/DR are failover clustering, database mirroring, always availability groups, log shipping, and replications.

Business Continuity can be achieved via both HA and DR solutions, and in several scenarios, both complement each other. You will need to determine the better HA and DR solutions to meet business requirements.

Details please check SQL Server High Availability and Disaster Recovery Plan

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.
DM 521 Reputation points

2024-05-05T18:57:28.0433333+00:00

Hello ZoeHui-MSFT,

Thanks for revert. We are in process of finding/deciding that apart from 'Replication' which technologies can be used for outcome equal or better than 'Replication' on specs of input effort/infra needed/time for which it will be supported by Microsoft/ease of deploying (Replication is complex when it comes to setting up transactional one) and ease of using in case of downtime or disaster/cost of solutions/impact on database performance of the solution as apart from user activity; these solutions also impact performance of a db; etc.

Which amongst following are most promising considering future: failover clustering, database mirroring, always availability groups, log shipping. Replication has a good potential. Also, is there any link/resource which shows comparison between above technologies from ease of deploying to complexity of deploying/infra needed/cost in terms of money and cost in terms of time to deploy/length for which MS will support this technology/impact on production db performance/any of above solutions mostly run by business critical applications/mission critical scenarios like banks/hospitals/railways/airlines/army etc.

Thanks for your help in advance.

DM 521 Reputation points

2024-05-05T18:59:23.1066667+00:00

Hello ZoeHui-MSFT,

Thanks for revert. We are in process of finding/deciding that apart from 'Replication' which technologies can be used for outcome equal or better than 'Replication' on specs of input effort/infra needed/time for which it will be supported by Microsoft/ease of deploying (Replication is complex when it comes to setting up transactional one) and ease of using in case of downtime or disaster/cost of solutions/impact on database performance of the solution as apart from user activity; these solutions also impact performance of a db; etc.

Which amongst following are most promising considering future: failover clustering, database mirroring, always availability groups, log shipping. Replication has a good potential. Also, is there any link/resource which shows comparison between above technologies from ease of deploying to complexity of deploying/infra needed/cost in terms of money and cost in terms of time to deploy/length for which MS will support this technology/impact on production db performance/any of above solutions mostly run by business critical applications/mission critical scenarios like banks/hospitals/railways/airlines/army etc.

Thanks for your help in advance.

DM 521 Reputation points

2024-05-10T18:59:29.1+00:00

Hello ZoeHui-MSFT,

Am confident your guidance will surely us. Thanks for same.
Sign in to comment