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 |
|
netzorro
Starting Member
6 Posts |
 Posted - 2011-11-15 : 09:52:54
|
| Hi all, this is basic. Currently two different sites uses the same database. Now I have to separate both. I have to replicate sqlserver 2005 database.Application on both sites use the same tables. SQLServer has the capability of replicate in both ways?Does the sql server replication solve the identity conflicts? Should I take care the identities to be different on each server?Thanks,Diego |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-15 : 11:30:14
|
| Have a loko at merge replication but it's not something to take on lightly.Also have a look at conflict resolution - but yes make identities unique or include a site id.Are you sure you want to merge the data - might be easier to keep databases separate and extract for reporting.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
netzorro
Starting Member
6 Posts |
Posted - 2011-11-15 : 11:50:24
|
| Yes, the main problem is reporting. Reports should use both servers data and maybe the other (remote) server is not always available.Also, another reason is param tables, security tables, and so, they are controlled from the main site.Any advice is appreciated.I've been reading about replication (public/subscribe), pear-to-pear. Not sure what to use. Merge replication is the correct name for what I want?Thanks nigelrivett |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-15 : 20:05:38
|
| I would say that Peer to Peer is what you want.You WILL need to seed your identity columns appropriately to aviod collisions. If there are two nodes then you can seed one odd and one even. Don't make the mistake of boxing yourself out of numbers by allocating blocks of numbers for each node.That's really the only "gotcha." I've found it easy to setup and maintain. It is really the same as transactional replication in almost every respect. Main difference is that each publisher is also a subscriber to the other "nodes." |
|
|
|
netzorro
Starting Member
6 Posts |
Posted - 2011-11-16 : 22:15:10
|
| Yes, you're right russell. Thanks, peer to peer is the correct answer of what I need. But I have 2005 Standard Edition, so I am helpless.I will read further to the merge replication documentation to check differences with pear to pear.Thank you very much |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-17 : 10:02:21
|
| If reporting is the main issue, then I'd have a much closer look at extracting data as Nigel suggested.If the remote site isn't always available then you're going to have problems with replication anyway.How much data needs to be synched and what is the acceptable latency? What is the bandwidth between the 2 sites? These are questions we don't necessarily need the answers to, but you need to consider. |
|
|
|
|
|
|
|
|
Replication and identities