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 |
|
jsharp
Starting Member
3 Posts |
 Posted - 2007-01-05 : 12:43:00
|
| Hello everyone,We are looking to expand our network to allow for both High Availability and Distribution of Load for our web servers and SQL servers. These servers will be in different geographic regions (WA and MN).Currently, we are using a single SQL 2000 Server which is hosting 5 production databases. All 5 of these databases are accessed via our Coldfusion webserver. What we want to do is update our existing SQL Server and setup a second SQL server (and webserver) in our Minnesota office that will provide both High Availability redundancy and Distribution of Load for all 5 of these production databases.As I understand it, Offsite Database Mirroring via SQL Server 2005 will provide High Availability (with a 3rd server) but it will not provide Distribution of Load. What is the best solution to allow for High Availability and Distribution of Load over large geographic area?Thanks |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-05 : 12:56:40
|
| Does data have to be writeable at both locations? If so replication is going to be your only option. |
 |
|
|
jsharp
Starting Member
3 Posts |
Posted - 2007-01-05 : 13:22:01
|
| Yes, it needs to be writable at both locations.I am assuming that Transactional Replication is going to be the way to go. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-05 : 13:38:21
|
| That depends. With transactional you can make the data updateable in both locations but you have to partition it so that truly it is only updateable in one location.What that means, for example, is thisIf you had a customer table, you could have a branch code in the customer table - if a row in the table has the WA branch code, then that row can be updated at the WA location, but only read (not updated) at the MN location. A different row in the same table with an MN branch code can be updated at the MN location but is read only at the WA location. So the table is partitioned to be updateable at both locations, but the same row cannot be updated at both locations.If you need to be able to update the same row at both locations then you need to use merge replication. The downside of merge replication is that you have to write code to handle merge conflicts (when users at both locations update the same row at the same time). |
|
|
|
jsharp
Starting Member
3 Posts |
Posted - 2007-01-05 : 14:45:32
|
| Thanks for the clarification. Merge replication will have to be used. Merge conflicts should be very rare. Users seldom access the same record in our system, though we will have to take that into account.Thanks for the information! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-05 : 15:13:24
|
| Are your servers currently overloaded?It is likely that going to the work of distributing the load across multiple SQL Server and setting up a merge replication plan would be more costly that just investing in more powerful servers.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Distribution of Load and High Availability