August 28, 2002 at 9:02 am
I am trying to implement an offline server to run all of our backend processes. In order to do this I will need to replicate all of the objects down to the backend server every night, except for 5 tables that will be push up to the production database each night. Now the problem I have is with the initial snapshot of the 5 tables that I will push up to the production database. By default if the snapshot finds a table with the same name it will drop the table and recreate it. Naturally I can't have this due to these are production tables. I've changed that option to "leave table as is", but the snapshot will still try and push all the records to this table, which will either cause a ton of duplicates or error out due to duplicate records. The question I have is how can I get the stored procedures that replication needs to this server without the snapshot trying to drop the existing table or duplicating all of the records? Right now the only way I know is to schedule down time to do this, unless someone knows a way to get around the problem I mentioned above.
Thanks!
Jeff Matthews
Jeff Matthews
August 28, 2002 at 9:12 am
If you tell it the subscriber already has the data you won't have to deal with the updates. I'd recommend breaking it up into two pubs, the first being all the tables that just get replicated to the other server using standard transactional. A second pub for the five tables that get updated using transactional with immediately updating/queued updating subscribers. This will let you do the updates on the subscriber and post back, table will never need to get changed/snapshotted since it IS the production table. If you rebuild your spare box you can just snapshot everything to it.
Andy
August 28, 2002 at 9:19 am
Correct me if I'm wrong but even if you tell it that it already has the Schema and Data, it will still need the sp_MSins, sp_MSups, and sp_MSdel stored procedures. Which cannot be placed on the server until the snapshot is ran. As far as immediate updating of transactions these 5 tables are huge and would cause too many locks during the day. So I have to schedule the distribution to run at night.
Thanks for you quick reply!
Jeff Matthews
Jeff Matthews
August 28, 2002 at 10:51 am
The first time, yes. After that you'd have the procs and you can always create them manually anyway. Not sure why it matters? Maybe I dont understand totally what you're trying to do. If the master tables are all on your main server you should only have to snapshot if something really breaks, even if you do it doesn't hurt because you're pushing data from production to reporting, not the other way around.
As far as immediately updating, it depends on what the source of the load is whether you can afford it or not. If it's a cpu intensive task that results in simple inserts/updates/etc, you probably could do it. If you just want to defer the activity you can break the connection (possibly a better way to do it) so that the updates queue, then you post them later on.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply