Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2010
    Posts
    7

    Unanswered: setting up two copies of the same database with SQL server 2008

    Hello, I am new to SQL server 2008 and have been given a mini-research project from my supervisor to find out possible solutions or options to a problem. I have searched the web for possible configurations and solutions and have not found any good documentation. This question might have been answered elsewhere in the forum, but I have not located it as of yet.

    We are using SQL server 2008. The ultimate goal is to have the same database in two different physical locations (two different states), both are updatable, and they update each other if one has any changes made to it. I have read about mirroring the database, doing one of the three kinds of replication, and also possibly simply installing SQL server 2008 on the two domain controllers. I was wondering if anyone knew of any good documentation or had any ideas about the possible configurations, solutions, system/network requirements, any third party software, or any other helpful information?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What business problem is your solution expected to solve? High availability? Geographically separated load balancing? Disaster recovery?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Dec 2010
    Posts
    7
    The biggest business solution that is trying to be solved is load balancing between two different geographical locations. At the moment, we have two different geographical locations. Everything is being routed through the network in order to access the database in one geographical location. We are looking to set up a copy of the database in the second location in order to alleviate the load on the one database and attempt to reduce network latency. We need to have each database update the other with any and all changes that are applied. I have done research on SQL server replication and database mirroring with the implementation of a witness. I am just not sure what the correct solution would be. I have been continuing my research online with little to no success. Any information or help would be greatly appreciated!

  4. #4
    Join Date
    Dec 2010
    Posts
    7
    They also would like it to be highly available at the same time.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, db mirroring is solely for high availability (some think it can also be used for DR). The mirror is inaccessible for WRITEs, and accessible for READs via db snapshot. This means it's not what you need.

    Merge replication may be what you're looking for, but always keep in mind, that nothing comes for free, there's always cost associated with every benefit/feature you get or intend to use. In the case of merge replication, - network throughput may be your biggest bottleneck. The changes have to travel in both directions, regardless of what database the client initiated the transaction against. So for load balancing it really isn't fully serving the purpose, but that's my opinion.

    I am not fully sold on your statement that load balancing is needed, when you're presenting only 2 sites. It would have made a difference if you were going to 10 or 15.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Dec 2010
    Posts
    7
    The main concern is that there will be a 500 employee increase at each location. They will all be accessing the one database throughout the day. They are really concerned with everyone accessing the one single database over the network. I have mentioned merge replication to my superiors and they wanted to know more specific. I just wanted to make sure that there were no other options that would be more feasible for this application. I have also looked at possible third party software and products for possible solutions. I wanted to cover all bases before presenting anything concrete to my superiors. If there are any other ideas, I would greatly appreciate them. I appreciate your time and help.

  7. #7
    Join Date
    Jun 2005
    Posts
    319
    Yeah you need merge replication, hire a consultant to get up and running. Also look into training if you have not used it before. Well I might retract the "need" part, but what you are looking for is merge replication.

  8. #8
    Join Date
    Dec 2010
    Posts
    7
    Thank you for the advice and help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •