• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 151
  • Last Modified:

SQL Server 2016 off site DR replication except Always on

Hi Experts,

I have A Database on SQL server 2016 SP1 on Primary site and another SQL Server as a DR server in DR site. DB is very small one and data growth is also small. DB is less than 1 GB right now and there may be about 1GB per month growth.
Connection between Primary site and DR site is 2Mbps dedicated
I need almost real time replication like Oracle RMAN sync [Oracle active data guard or data guard]

SQL and windows server - Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: )

What would be the best SQL Server replication method except SQL Server Always on ? I need your opinion for DATA replication.

Hardware level, VM level replication will not work.
Pushpakumara Mahagamage
Pushpakumara Mahagamage
  • 2
  • 2
2 Solutions
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can try to use one of the following solutions:
Mirroring - This is basically the predecessor of the AlwaysOn. Main difference is that you can't access the Secondary Replica without stopping the Mirror first (usually what you need to do when a Disaster occurs).

Log Shipping - Oracle also has this option and acts the same for SQL Server. Like Mirroring, you can't access the Secondary Replica. But unlike Mirroring you don't need to stop the Log Shipping to Recover the Secondary Replica but only pause it.

Transactional Replication - This isn't normally used as DR solution since the goal here is to replicate tables or set of records but you can always replicate all objects of the Publisher database (PROD) to the Subscriber database (DR).
Pushpakumara MahagamageVPAuthor Commented:
Hi Vitor,

You are spot on.

I have log shipping enabled for some projects. DR DB is on standby-read only mode.
This project I'm Planing to use that DR database as SSRS data source. then I need real time data, about 15 min delay is acceptable for reports.
Is it good idea ?

I'm using Oracle DR as reporting data source.

Actually I need to replicate Transactions and Masters only(report related data).  There are so many temp data tables and I can exclude them from replication. and I can fail over to the DR database in a disaster situation.
By the way Can I have separate indexes on Secondary server, if I use transaction replication.  
Both servers are Vertebral servers on wmware esxi cluster, hardware level fail over and nightly Veeam backup is also there.

Do you recommend transaction replication for my scenario.
Eugene ZCommented:
"I need almost real time replication like Oracle RMAN sync [Oracle active data guard or data guard]"
yes, transaction replication can be your solution
Vitor MontalvãoMSSQL Senior EngineerCommented:
I really think the Transactional Replication should be the solution that fits you better.
And yes, you can have different indexes created.
Pushpakumara MahagamageVPAuthor Commented:
I have configured Transaction replication and it is working fine.
Thanks for your advice.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now