August 26, 2001 at 2:47 pm
Hi,
I am facing a problem while implementing replication. My situation and problem are explained below.
Database A : Internet Database
Database B : Local LAN Database (City X)
Database C : Local LAN Database (City Y)
My databases have 2 way replication over all the databases ie.
B >> A
C >> A
A >> B
A >> C
so ultimately B has data of C. So after replication every office has the same data.
But this increases the overhead on the Internet database. I need to delete the data online but keep it offline on the the Local LANs.
How do I achieve this? This is because if I delete from Database A, by using transactional replication, the database fires a trigger on Database B and C too, to delete the records there. So how do I avoid that.
Please do let me know how does the whole process work.
Thankx
Paras Shah
Evision Technologies
Mumbai, India
August 26, 2001 at 6:20 pm
This is how replication is supposed to work! When you set up a transactional subscriber, it gets a set of stored procedures created that the dist agent uses to apply changes. You "could" change the delete sp to maybe just mark the record(s) as deleted instead of actually deleting them.
How much overhead are you talking about? Unless you are running a pretty intense site, I'd be surprised if deletes amount to much (depends on # of rows and size of rows).
Andy
August 26, 2001 at 6:40 pm
Is the purpose of your replication setup to have C and B synchronized, and you are using A as a "staging" database?
August 27, 2001 at 7:42 am
I am using replication so that I have common data on all my offices. The data on the web server is in a way kept somewhat for staging but not the whole of it.
There are a few records that need to be deleted from the web but have to be kept on the local office servers as archives.
So how do I achieve this.
I cannot just mark records as deleted as it would not serve my purpose of saving on webspace.
My aim is to save web space and keep the data on the local servers.
Paras Shah
Evision Technologies
Mumbai, India
August 27, 2001 at 1:48 pm
Well, you are going to have a mess. Why? Because replication is designed to keep the data in synch. It is not designed for "Keep my data in synch, except for the cases where someone does operation X and then ignore the fact that the data is no longer in synch and you can no longer perform the function you were designed to."
You need to create an archive process that supports this. What I would do is create an archive table on your publisher that logs all deleted rows. Now create a view that unions your transactional table and the archive table on the publisher so you have all of the data like you need.
Second part is GET RID of the bi-directional scheme you seem to have implemented. You are doing nothing but asking to lose data. There are MUCH better ways to move data in 2 directions like merge and immediate updating than it is to implement bi-directional replication which is guaranteed to lose data at some point.
Mike
SQL Server MVP
Mike
SQL Server MVP
http://www.mssqlserver.com
August 27, 2001 at 2:26 pm
[font=verdana]I have databases in 3 cities and 1 database on the internet.
Internet
|
|---------|-----------|
A.........B...........C
My data is being fed in from all 3+1 dbs. But the data is not being fed on the same tables and can also be on the same tables. So I need to synch all these servers.
Bi-directional synch is therefore necessary. But some data like a few messages that are kept on the web server are only for display for the users, once they expire the specified time limit they need to be deleted from the web and not from the local servers.
The point of archiving the deleted rows into a new table is also a good idea. But how do I do that, because I would be deleting rows from the table on a Scheduled Job Event.
Thankx for the suggestions but I need to freeze upon a viable solution. Lets think it over.
-----------------------------
Paras Shah
Evision Technologies Pvt Ltd
Mumbai, India[/font]
Paras Shah
Evision Technologies
Mumbai, India
August 28, 2001 at 12:52 pm
One simple way of doing this is to create an archive table for your production table (Messages in this example), and set a delete trigger on your production table to copy the deleted data to your archive table.
Hope this helps,
Sean
August 28, 2001 at 1:01 pm
Thankx Sean, I shall try this out and shall keep you informed on the developments in this regard.
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
August 28, 2001 at 1:21 pm
I would NOT suggest transformable subscriptions unless you are already 100% comfortable with all aspects of replication. Doing so makes it impossible to validate that you are synchronized, troubleshooting is also INFINITELY more difficult.
There is a SIGNIFICANT difference between bi-directional replication and replication that moves data 2 ways.
With bi-directional, each database is published to all others and also subscribed to all others. Each publisher has its own distributor. (Doesn't matter if they all point to the same distributor and distribution database since each publishers data is independent with the tables in the distribution database.) This means EVERY transaction, replicated or not, is a unique event to each database. You have to worry about causing endless loops of replicated transactions. Besides that, if the system goes down, there is NO WAY to determine the exact state of your data across the system. This means you have guaranteed data loss at some point in time.
Replication that moves data 2 ways has a single distributor and single distribution database that ALWAYS knows what the exact state of the data across your entire environment is.
HUGE difference. I've done bi-directional replication once in my life. I will NEVER, EVER do it again.
Mike
SQL Server MVP
Mike
SQL Server MVP
http://www.mssqlserver.com
August 28, 2001 at 6:05 pm
Hey Mike,
I've never done bi-directional, kinda had it on the "to-do" list since hearing a discussion about it at SQL Connections in Orlando earlier this year. Im still not sure that its not a solution in search of a problem, but seems like there would be a few scenarios where it would make sense - primarily ones that would naturally prohibit the endless loop you mentioned.
I'd appreciate hearing more about how/why your first attempt failed, plus exactly why you cannot establish data state exactly using this method. Seems like if you're pushing transactions, wouldnt be impossible to figure out where you stopped at.
And since Im asking, your comment about not being able to validate transformed subscriptions - do you consider it important that you be able to validate?
Andy
September 2, 2001 at 10:42 am
MIke / Andy, could any of you please elaborate on your problems and also elaborate more on the difference between bi-directional and 2-way replication.
Thankx
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply