November 18, 2009 at 4:58 am
Hello
I need some suggestions with this situation: I have a c# client application and a MSSQL 2005 database.
My application needs to run on 2 different locations (let's say location A and location B), each with its own SQL database.
The ideea is that all the data changes in location B must be reflected as well in location A, ie database from location B contains its own data, but the database from location A must contain the data operated locally as well as the data operated in the other location.
the tables from database have primary key id with autoincrement (1,1)
My approach is:
start database is called DBase
-copy DBase to location A server and change increment 2 and seed the next available ID odd
-copy DBase to location B server and change increment 2 and seed the next available ID even....
(that's to prevent conflict of primary keys)
- set database from location B as publisher
- set database from location A as subsriber
- create a transactional replication that runs conntinously
My first question is that this approach is correct, than:
- i know than in a replication, its recommanded than dailly to perform a snapshot replication also, for that the integrity and consistency of data
- in case that is performed an insert in location B, and, for whateverreason this new addly record itsn't performed in location A, what is the appropriate action? i have to use replication monitor and see that? how can i be secure then the data from location B always get to location A?
Thanks, and i wait for any suggestions...
November 18, 2009 at 8:37 am
murgocimark (11/18/2009)
My first question is that this approach is correct, than:
- i know than in a replication, its recommanded than dailly to perform a snapshot replication also, for that the integrity and consistency of data
- in case that is performed an insert in location B, and, for whateverreason this new addly record itsn't performed in location A, what is the appropriate action? i have to use replication monitor and see that? how can i be secure then the data from location B always get to location A?
Thanks, and i wait for any suggestions...
No, In transactional replication, you only run the snapshot 1 time during initial setup. That brings over the entire table. After that you only bring over transactions to keep it up to date. You can run this continuosly, or at scheduled intervals (every 10 minutes, every hour ...)
In snapshot replication, you bring over a fresh copy of the entire table everytime, wiping out the current version. This is usually done for small static or lightly updated tables, such as lookup tables (area codes, product numbers, office locations ...). This might run once a day before business starts.
How many tables are involved ? If you ever need to restart replication, you will need to isolate DB_A data (odd #), because the new snapshot from DB_B (even #) will overwrite the data on DB_A, then you will have to reload the old DB_A data back in to have both DB_A and DB_B together again.
November 19, 2009 at 3:40 am
Hi
thanks for the asnwer.....
my database contains about 150 tables
what do you mean by restart application? from what i know, when i first start the replication, the shapshot is copy to the subscriber. after that, the shapshot will not ever be used.
And i didn't get the part with lock the subscriber table DB_A? pls detail..
Thanks again
November 19, 2009 at 5:22 am
"...when i first start the replication, the shapshot is copy to the subscriber. after that, the shapshot will not ever be used...."
Correct, but in your first post you say " its recommanded than dailly to perform a snapshot replication "
--------------
"...what do you mean by restart application (sic) ..."
I mean that from time to time, I've found that for some reason I've had to drop the subscription tables and start replication from scratch. In your scenario, you'll have to separate the data first, because DB_A and DB_B data are mixed together, so you don't lose half of it from DB_A
November 19, 2009 at 7:38 am
ok, i got it...thanks again
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply