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.
LVL 9
Pushpakumara MahagamageVPAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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).
0
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.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
"I need almost real time replication like Oracle RMAN sync [Oracle active data guard or data guard]"
yes, transaction replication can be your solution
0
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pushpakumara MahagamageVPAuthor Commented:
I have configured Transaction replication and it is working fine.
Thanks for your advice.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Disaster Recovery

From novice to tech pro — start learning today.