February 23, 2011 at 6:50 am
Can transactional Replication be set up with a one-way path, i.e. the publisher sends, but doesn't receive.
We wish to set up a database for querying and reporting, which will allow editing, creating tables, etc., but will in no way update the publisher. The database used for reporting will need to be updated every 24 hours. Is Transactional Replication the best method for this?
Thanks,
Dobe
February 23, 2011 at 9:43 am
Transactional replication or log shipping
or snapshot
Transactional replication will update the data all the time
If you update any data in the report server the replication jobs could fail
create a project database to hold report changes instead
Log shipping would work too put it will kick the users out when applying the logs and you cant add tables to that database, i get around that by creating a project database to hold all the new reporting information
otherwise a snapshot done every night would probably be the easiest
February 23, 2011 at 10:38 am
So, when using log shipping, as the log updates the data base, users are kicked out, or is it that when the log is updated?
What I am looking for is an editable environment, whereby I can edit data, create tables, procceedures, and the data is updated by the parent database. This will not be a production database, but rather an editable reporting database.
I was wondering if a "One-Way" path can be used in Transactional Replication", the one-way being updates to the subcriber and not to the publisher.
February 23, 2011 at 10:48 am
DobermannCanis (2/23/2011)
So, when using log shipping, as the log updates the data base, users are kicked out?
yes, because the database is in a Restoring state while the logs are being applied.
Depending on whether reporting does or does not need to be available 24/7 , I suppose you could have an overnight window where the logs get restored.
With transactional replication the subscriber database would be available all the time, and if I'm not mistaken you can schedule all the replication statements to be "saved up" and run on a schedule to push the changes to the subscriber, rather than as they happen on the publisher.
February 23, 2011 at 10:52 am
DobermannCanis (2/23/2011)
What I am looking for is an editable environment, whereby I can edit data, create tables, procceedures, and the data is updated by the parent database. This will not be a production database, but rather an editable reporting database.
You can do this - of course you would not be advised to make changes to tables etc in the subscriber that were replicated from the publisher, or you could end up in all sorts of trouble..
I was wondering if a "One-Way" path can be used in Transactional Replication", the one-way being updates to the subcriber and not to the publisher.
Transactional replication is one-way. We have a case here of a subscriber that holds, say, Table1, Table2,...,Table10 and these are replicated to the subscriber. The subscriber also has Table11, Table12,...,Table20 which do not exist in the publisher and can be changed etc (within reason)
February 23, 2011 at 11:17 am
From what you were telling me, Transactional Replicaton is always one-way. I assumed it was or could be both ways; the publisher updates the subscriber, and the subscriber in turn would update the publisher with changes.
From what I have described. How would you set up a simple data base for reporting? Would you just use a Replication Snapshot, and link a database to it for creating SPs, UDF, etc?
February 23, 2011 at 12:45 pm
How big is the database?
You could backup/restore every night. I know people that do that. However if you have lots of non-changing data, then it might be easier to do replication.
Log shipping won't work for you since the receiving side is not editable in log shipping.
Replication is typically one way, but there is peer to peer, or bidirectional transactional replication. There also is merge replication, but for your situation, the standard one-way publisher to subscriber is what you want. There is overhead here in that you have to set up each table (or table subset) to replicate, which can be an admin issue. Not a big one, but more overhead.
The main thing is are you looking for a "reset" of the reporting environment back to the main db every 24 hours or do you want changes made on the reporting instance/db to persist?
February 23, 2011 at 1:02 pm
Thanks for the information all. I have changed one criterion. I no longer need an editable environment. Therefore, since Transaction Replication seems to be more conducive to reporting, I think that is the way I'll recommend. As for as the size of the database, it's less than a gig. The database is supposed to be updated every 15 mins.
Are there any performance issue with updating moderate to moderately heavy updates from the publisher side to the subscriber?
Thanks
February 23, 2011 at 1:25 pm
Nope, transactional replication can definitely handle it. If you start running into latency give a post back and we can work through that.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 23, 2011 at 1:53 pm
Thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply