General discussion

Locked

Synchronising data across geographically

By david ·
Hi guys

We are busy with a project to set up our websites in 2 geographically seperate datacenters. We are doing this for redundancy purposes so that we can have business continuity should the primary data centre be unavailable for any reason.We are going to be using a device called Radware to direct traffic to the seconday site should the primary not be available. Our web applications are based on .NET principles and we will be using a SQL database where some of the data is housed to build ASP pages and related. We may also use this for other application specific data (ie. not client data, yet). The client's data is stored on several databases on site and we will have WAN links connecting our building (and thereby the backend databases) to the datacentres.

The question is, what are the best practises for maintaining some sort of synchronisation of the data sitting on the SQL databases at the 2 datacentres? I don;t want to limit you guys to SQL so if there is a better product to skin this cat please let me know.

I have been told that MS SQL synchronisation works fine as long as you are not accessing the secondary SQL database in any way. Is this correct? In our model it is quite possible that we could cutover to the secondary datacentre while the synchronisation is still happening.

There is a lot of information I have not included here because I don;t want to bore you with irrelevant stuff but if you need more I'm happy to deliver.

Thanks
dave

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Synchronising data across geographically

by david In reply to Synchronising data across ...

Point value changed by question poster.

Collapse -

Synchronising data across geographically

by Joseph Moore In reply to Synchronising data across ...

Sounds like you want to set up Transactional Replication between the two SQL Servers. You can set up the 1st server to do a Push Transactional Replication, pushing each record as it is written to the 2nd SQL Server. This way, both SQL Server are in sync.
So, if the 1st SQL Server dies suddenly, the 2nd SQL Server as an up-to-date copy of the data.
Replication is easy to set up. You create a Distributor on the 1st server, then set up a Publication, and then you configure the recipient.

Please go to this URL:
http://www.microsoft.com/SQL/techinfo/administration/2000/ReplPerf.asp
(please remove any spaces)
It is a Microsoft document on Transactional Replication.

hope this helps

Collapse -

Synchronising data across geographically

by Joseph Moore In reply to Synchronising data across ...

Here is a summary of the 3 types of Replication:

1. Snapshot. Copies an entire view of data to another computer. The destination database view is overwritten with the new version. Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values, low latency, is not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Collapse -

Synchronising data across geographically

by Joseph Moore In reply to Synchronising data across ...

2. Transactional. Transactions, INSERT, UPDATE, or DELETE statements, executed on one computer are replicated to another computer. With transactional replication, an initial snapshot of data is applied at subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers. Transactional replication is helpful when:

Incremental changes need to be propagated to subscribers as they occur.
Transactions need to adhere to the Atomic, Consistency, Isolation, and Durability (ACID) properties.
Subscribers are reliably and/or frequently connected to the Publisher

Collapse -

Synchronising data across geographically

by Joseph Moore In reply to Synchronising data across ...

3. Merge. Updates on any computer will be replicated to another computer at a later time. Merge replication is the process of distributing data from Publisher to Subscribers, allowing both the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when connected.

Merge replication allows various sites to work autonomously. At a later time, merge updates produce single, uniform result. The initial snapshot is applied to Subscribers, and then you have tracked changes to published data at the Publisher and at the Subscribers. The data is synchronized between servers continuously, at a scheduled time, or on demand. Because updates are made at more than one server, the same datamay have been updated by the Publisher, or by more than one Subscriber. Thus, conflicts can occur when updates are merged.

Merge replication includes default and custom choices for conflict resolution that the user can define while configuring amerge publication. When a conflict occurs, a resolver is invoked by the Merge Agent and determines which data will be accepted and propagated to other sites. Merge replication is helpful when:

Multiple Subscribers need to update data at various times and propagate those changes to the Publisher and to other Subscribers.
Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
Not many conflicts are expected when datais updated at multiple sites. This is because the data is filtered into partitions and then published to different Subscribers, or due to the uses of the application. However, if conflicts do occur, violations of ACID properties are acceptable.

Collapse -

Synchronising data across geographically

by david In reply to Synchronising data across ...

Great thanks a mill. Here are some other URL's I've found useful:
http://www.microsoft.com/TechNet/prodtechnol/sql/reskit/sql2000/part4/c1661.asp?frame=true#b
http://otn.oracle.com/deploy/availability/techlisting.html#DR

Collapse -

Synchronising data across geographically

by david In reply to Synchronising data across ...

This question was closed by the author

Back to Networks Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums