Near Real time copy of OLTP for SQL2014

Question

Near Real time copy of OLTP for SQL2014

Andrew..Peterson

SSCertifiable

Points: 6838
More actions
January 9, 2017 at 7:21 pm

#325165

Using SQL 2014, I'm looking for options to create/manage a reporting copy of an OLTP database on a separate server. The reporting copy could be read-only. Since there are a large number of tables, hoping to avoid replication. The server is not clustered. Open to third party tools. Any and all suggestions appreciated.
The more you are prepared, the less you need it.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

The question is, do you really need "real time"? If the answer is no, consider "SAN Replication", which is nearly instantaneous. It may be an added piece of software that you have to buy for your SAN but it's worth it for this type of application. People just have to remember that anything they add to one of the SAN replicated database will be gone on the next replication cycle.

Check with your SAN dealer... if you have a SAN.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

What's your concern with replication?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andrew..Peterson SSCertifiable Points: 6838 More actions · Answer 3

Jeff, thanks for the tip on SAN Replication. I'll look into. No problems with replication, but with lots of tables, it adds more administrative work that is desired.

The more you are prepared, the less you need it.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

Andrew..Peterson (1/10/2017)
Jeff, thanks for the tip on SAN Replication. I'll look into. No problems with replication, but with lots of tables, it adds more administrative work that is desired.

It's been years since I've done "Replication" but, from what I remember, it was pretty easy to write a stored procedure that would do it all with a bit o' help in the form of dynamic SQL. I also remember having no love for it, though.