February 2, 2006 at 10:27 am
I am fairly new to the SQL world, and don't claim to know much about anything SQL, I am trying to grasp some of the complexity of SQL. Having said all that, my situation is that we have a single SQL 2000 machine at our main office. We have 3 satallite offices that connect over VPN tunnels back to the main site to run our software that is based off SQL. We are looking to increase our speed in one of our bigger remote sites, and also increase our redundency by placing a SQL server 2000 machine in this location and having it be a hot box. In otherwords we would replicate between the 2 SQL machines and keep the database current with each other and the users at Remote site would work of remote site SQL machine and all other users would work off main office SQL machine. We have 2 databases runnning in SQL. I have talked to a few people and they have told me this will not work or that you would have a very very high risk of corruption on both databases. Anyone ever deal with a simular situation, I would be curious to hear back from you.
Thanks, Shawn
February 2, 2006 at 6:36 pm
Obviously the people you have been talking to don't know how to use SQL Server Replication or don't know how to keep data synchronised with SQL Replication!
You have 2 choices.
1. Transactional replication. Basically the head office database does all the Inserts Updates and/or Deletes and replicates the changesto the Subscribers in your case the 2 satelite offices.
2. Transactional Replication with Immediate Updating Subscribers. Same as the first option with the addition that the Subscribers are also allowed to make modifications to the Databases and send the chnages back to head office to keep it all in Sync.
There is a 3rd option and that is Merge Replication however, I don't think you need to go that far unless you wish to replicate Text & Image Datatypes.
February 3, 2006 at 1:16 pm
Will there be updating at the different locations, or is the data updated just at one location, then pushed to the other location for inquiry only ? Log Shipping is often an easier way to keep entire databases in sync. Replication is better for subsets of databases, such as specific tables or groups of tables.
February 3, 2006 at 8:18 pm
I would really consider on using transactional replication with immediate subscribers, there is another option that is to user bidirectional transacional replication.
I have used in prod and it has worked fine for me. You don't have the risk of using the MSDTC and you can replicate changes made at both servers.
March 23, 2006 at 8:38 am
Homebrew01, the databaseses will be accessed byt the people at the location where the server will be. So any changes made at each will need to be replicated back and forth. I have heard that with SQL 2005 that you could set it to be an active, active cluster and that would be able to replicate to the other and be able to be access at the location where the server is. Any thoughts or expeirences with Active-Active cluster. Thanks.
March 23, 2006 at 8:48 am
Sorry ... I haven't dealt with those scenarios.
March 27, 2006 at 5:23 am
Hi Shawn, I believe you can use Merge replication using which, you can update the data at any end and replicate it to other server. It works fine as long as there're no conflicts.
Cheers,
Puneet
March 27, 2006 at 2:31 pm
You can use peer-to-peer SQL Server 2005 Transactional replication. This one allows changes made at both nodes and being replicated to all the subscribers in both directions.
Also, you can use bidirectional transactioan SQL Server 2000 replication. You have to configure all nodes as publishers and subscribers. You must be carefull to filter the published articles so you won't generate consistency conflict. Read also about loopback_detection of transactional replication option.
And also, you can use merge, that that is was designed for.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply