January 27, 2020 at 2:25 pm
Hello,
Our company has used SQL replication to refresh our staging data on a weekly or monthly basis in the past. I've been asked to investigate how feasible it would be to set up a replication job that refreshes data constantly in real time.
Basically, we have a 3rd party vendor that needs access to a fraction of our production data. Rather than allowing them access to the production instance, where they could run queries against it and potentially slow things down for our users (not to mention being less secure), we'd like for them to run their queries against a different environment that has a copy of that production data. But we're hoping to find a way to have that copy getting refreshed in real time.
My initial attempts at researching this weren't fruitful, but the idea makes me nervous from a performance standpoint. Has anyone attempted anything like this? Is there a better solution for the problem we're trying to solve then a replication job?
Help is greatly appreciated!
January 27, 2020 at 3:27 pm
Transactional replication sounds like the way to achieve your goal. It allows replication of all or a subset of the database objects (tables, views, etc) called articles in replication.
To get started with transactional replication, see these articles:
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 27, 2020 at 5:24 pm
I run transactional replication all day every day (to get around linked server performance issues)
performance won't be an issue, but as soon as your subscriber (target) has data deleted, modified or worse inserted where there is an identity column then replication will fail and it is no longer realtime.
your subscriber has to be treated as "read only", otherwise you will be constantly be re-initialising your subscriptions and pushing a new snapshot out (basically BCP copy of each table in your publication)
security can be an issue (in terms of service accounts depending on whether you go for push or pull subscriptions) and the location of your distributor is key to that decision.
MVDBA
January 27, 2020 at 5:32 pm
Thanks for the responses SQL_Hacker and Mike. It sounds like transaction replication might be the way to go.
I'm surprised to learn that performance wouldn't be an issue. As far as performance goes, I'm most concerned about any impact to the publisher environment, but you're saying that you haven't noticed any slowdown as result of doing this?
Mike, can you elaborate a bit more on your last paragraph (regarding security)?
January 28, 2020 at 9:06 am
Any slowdown would be on your T-Log drive (the commands are read from t-log and pushed to the distributor database) - if replication fails, then you might get some issues with t-logs not truncating after a t-log backup
with regards to security, if you have a domain service account that is common to both (maybe not a good idea) then it will be easy.
best advice …. have a look at the wizard to create a new publication then figure out how it fits in with your security.
a lot of people use "run as service account" or "local security context" (there are a few settings you have to configure) - but where possible I use sql authentication and create an account just for replication
MVDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply