July 28, 2020 at 1:12 pm
Hi,
I'm hoping for some opinions on the best options for how to replicate, in real time, data from a production SQL Server database onto another SQL instance. This second instance would be for vendors who need to access some of the tables from the production database, but we would prefer that they not directly access our production instance. This would be read-only, and the actual replication can be a few minutes behind without a problem (it does not need to be truly "real time" in that sense, just getting updated to reflect current data frequently throughout the day).
I have some awareness of transactional replication as a possible solution, and I've heard a bit about replication for always on availability groups, but I would be interested in pros/cons of these approaches, or superior alternative solutions you may have tried.
July 28, 2020 at 1:48 pm
For maintaining a copy that is allowed to be slightly behind the 'live' instance, I would go for transactional replication.
It can take a little practice to set up and maintain but in my experience it has been quite reliable. I've worked on several systems that have used it for staging to another system or for reporting, as in your requirement.
Mirroring used to be quite easy to set up but is deprecated, so I wouldn't consider that now.
If you aren't already using an Availability Group then that might be a bit more work than you would like, to set up for this purpose.
Because I can never recall the details of how such things work (and it was several years ago), I did write a series of articles on Transactional Replication
July 28, 2020 at 3:08 pm
If the vendors only need to access a sub-set of the data, then I would probably choose transactional replication.
If they need access to the entire database, then I would likely implement an availability group.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 28, 2020 at 5:57 pm
Thank you both for your helpful responses!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply