transactional teplication vs others in follwing situation.

rajesh yadav 171 Reputation points
2022-04-07T12:03:09.12+00:00

Hi, 

   I have set up transactional replication

``(publisher is where entry is going on and subscriber is at remote location which is read only),
but now i have seen that my publisher is at a good location where internet availability is good, but the subscriber is at a remote location where Internet is disruptive.

Q1) With replication is good for this scenario? Like merge. etc.

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,816 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
496 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-04-07T12:40:25.667+00:00

    Transactional replication is a good solution for your use case. The commands will be stored until they are delivered to the subscriber(s).

    0 comments
  2. Seeya Xi-MSFT 16,446 Reputation points
    2022-04-08T02:47:36.12+00:00

    Hi @rajesh yadav ,

    Welcome to Microsfoft Q&A!
    Please see this document about Transactional Replication: https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver15
    Transactional replication is implemented by the SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent. The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor.
    The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database, which acts as a reliable store-and-forward queue. The Distribution Agent copies the initial snapshot files from the snapshot folder and the transactions held in the distribution database tables to Subscribers.
    Incremental changes made at the Publisher flow to Subscribers according to the schedule of the Distribution Agent, which can run continuously for minimal latency, or at scheduled intervals. Because changes to the data must be made at the Publisher (when transactional replication is used without immediate updating or queued updating options), update conflicts are avoided. Ultimately, all Subscribers will achieve the same values as the Publisher. If immediate updating or queued updating options are used with transactional replication, updates can be made at the Subscriber, and with queued updating, conflicts might occur.
    Thus, you should not use options of immediate updating or queued updating, Transactional Replication in your situation is fine.

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.