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 Reputation points
2024-04-28T17:44:17.5466667+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,897 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    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.


1 additional answer

Sort by: Most helpful
  1. ZoeHui-MSFT 33,626 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.