Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
creieru
Starting Member
12 Posts |
 Posted - 2009-11-18 : 07:09:33
|
| HelloI 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 conntinouslyMy 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... |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-11-18 : 10:48:21
|
quote:
- i know than in a replication, its recommanded than dailly to perform a snapshot replication also, for that the integrity and consistency of data
This is definitely not recommended in the real world. The only time you should be snapshotting is if there are structure changes or you have serious problems with replication.Other than that, what is your maximum acceptable latency between the servers? Peer-to-Peer replication sounds like the way to go if you need to update at both ends and you can be sure a third node will not be added at anytime in the future.The data will get from one to the other as long as the network is up. If it goes down, the distributor will know and store any changes until it is up again. |
 |
|
|
creieru
Starting Member
12 Posts |
Posted - 2009-11-24 : 05:52:42
|
| hi againi'm a little confused...Again, my situation is like this. I have a initial database, which i deploy on 2 servers (A and B).I install in both locations a client application that uses this databases.So, a workstation in location A operates with database in server A, and a workstation in location B with the one in B server.All i need is that the operation that are performed in server B to be performed also in database from location A.so, the database from server A will contain the data localy proccessed and the data from B.The database from server B will contain only it's on proccessed data.This task can be achieved with an standard transactional replication?OR i need to setup a merge replication, or some other type of replication?Thanks |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-24 : 11:34:08
|
| Peer to Peer will cause bidirectional updates -- changes to A are reflected on B and vice versa.Transactional is one way only.If you set up transactional replication, and allow users/applications to perform write operations at the subscriber, you're asking for problems. The subscriber should be treated as a read only database in most cases |
|
|
|
creieru
Starting Member
12 Posts |
Posted - 2009-11-25 : 03:39:56
|
| So what's your suggestion than?both users from location A and location B will modify their local database. The difference is that in that the database from location A must contain all the changes made in both locations.i.e. think at a usual client-server application (.NET/MSSQL) that runs in location B (MSSQL server is in the local network).A similar application runs in location A, with a yet another MSSQL server in it's own LAN.Both server's, at first, contains the same initial database.All i have to do is that in server from location A i must see all the changes made on the database which is in location B. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-25 : 12:23:46
|
| transactional. or some custom exports at regular intervals.if the subscriber is allowed to delete or modify data from published tables, then replication will fail. let me give u an example:Say we replicate the table:t1 (a int primary key, b char(1))now we insert on server Ainsert t1 values (1, 'a')insert t1 values (2, 'b')insert t1 values (3, 'c')the inserts get replicated to server Bnow someone deletes a record on server Bdelete t1 where a = 1Finally, we execute an update on server AUPDATE t1 SET b = 'z' where a = 1The update will succeed on server A but replication will raise an error "Row not found at the subscriber"Long explanation...but if that type of scenario is likely, you should probably write SSIS packages to copy data over periodically. You can also set the replication agent to ignore these types of errors, but you're still likely to run into issues. |
|
|
|
|
|
|
|
|
suggestion for replication in SQL 2005