replication in SQL 2005

[creieru]

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...

 

[mrdenny]

i know than in a replication, its recommanded than dailly to perform a snapshot replication also, for that the integrity and consistency of data

Nope, there is no need to push a snapshot unless you are first setting up replication, or adding new articles to the replication publication.

There's no need to do any funky reseeding of the identity value. First of all, when you run the snapshot the first thing that SQL Server will do by default is drop the table on the subscriber. So all that work you did would be useless.

Second transaction repliation won't work as it is one way only. Do have a database in both sites be writeable you would need to setup merge replication beween the two servers. However unless you really need to have a copy of the database in the remote site I would recommend that you simply have the clients in the second site connect to the database in the first site.

If that isn't an option, publish your database, and add your database objects as articles in the publication. The setup a merge subscription to the subscriber. SQL Server will automatically assign a range of IDs to the subscriber and the publisher. When that range runs out the subscriber will automatically contact the publisher to get the next range of IDs to use.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[creieru]

First of all, thanks for the answer.

i don't know if you get my approach....

i've said that i have one databases.
than i set up a transactional replication with location B as publisher and location A as subscriber.
The location in A will contain the initial data from start of the replication, and the data that will be added locally, and the data newly added from location B.
In location B i don't need data from location A, so i don't think that the transaction should be both ways (or merge).
After i set up the transaction, the snapshot will be created on location A, and than i've said that i will change the seed in tables so the records coming from location B not to interfere with the local ID's.

So this is the situation....
Do you still think that a merge replication is needed?

Thanks.

 

[mrdenny]

That should work for what you have described. However if you ever need to push a new snapshot because of a change to the table schema then you will loose all the data which is only located in location A as the snapshot will by default wipe out all the data in location A's table. There are ways to prevent this, but if it isn't done correctly you'll end up loosing a lot of data.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog