SQL - transaction replication

Colm 1 Reputation point
2022-06-14T08:47:43.953+00:00

Hi,

We are running SQL 2016 SP2 and i am trying to setup transaction replication for HA (and to create a read-only secondary for backups and such).

I am testing this on 2 isolated servers in azure, i have recovered a backup of the DB and configured replication to a secondary server.
server1 runs the DB and the distribution DB and the publication
server2 runs the subscriber.

all this appears to work, however, the snapshot agent runs every few hours taking 2-4 hours to run.
we want the replica to be a near realtime replica of the primary but at the moment i think we will be lucky if its 4 hours out.
and right now its running on a static DB, there are no apps/connections to this test environemt so if i configure this on the actual production DB i just dont see this as a viable option.

The DB in questions is about 1.5TB with a few hundred tables
we currently are running log shipping which does work but there is a preference to use transaction replication instead.

would appreciate any help/advice/thoughts on this.

Thanks

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

2 answers

Sort by: Most helpful
  1. YufeiShao-msft 6,871 Reputation points
    2022-06-15T08:43:53.177+00:00

    Hi @Colm ,

    Snapshot replication is most appropriate when data changes are substantial but infrequent and does not montior updates to the data

    If you want to real time, transaction replication is a good choice
    In the best case, transactional replication can be near real-time, changes that occur in the pubilsher are delivered to the subscriber as they happen(in near real-time), in addition to several factors, such as network and workload can have an impact on latency, of course, the latency you also can measure:https://learn.microsoft.com/en-us/sql/relational-databases/replication/monitor/measure-latency-and-validate-connections-for-transactional-replication?redirectedfrom=MSDN&view=sql-server-ver16

    -------------

    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.

    No comments

  2. Colm 1 Reputation point
    2022-06-23T11:38:24.24+00:00

    hi,
    there should be no latency as the 2 servers replicating are in the same VNET and subnet in Azure.
    there are no changes happening on the DB, which is why i dont understand the snapshot agent running for 2-4 hours every 2-4 hours.
    if this is how it works, theres no way i could use this for a realtime replica.

    Thanks

    No comments