MSSQL server replication

[jensenc391]

What would anybody recommend if I needed to have access to an MSSQL server from multiple locations and it needed to be edited on the fly. I know there is a replication feature embedded into MSSQL but am not sure if that would be the right choice? And if it is, What topology is commonly used?

 

[Catadmin]

You have access to the same database from multiple locations or multiple locations each have their own database?

Before determining if you should use replication, first you need to determine the answers to the following questions:

Does each location have it's own copy of a primary database?

If so, does each location make edits to their local copy?

If not, does each location make edits to the Primary copy?

Is there only one location that makes edits to the database and other locations that read?

How are the locations connecting to the database? (i.e., SQL Client tools or third party applications)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[jensenc391]

Each location will have its own primary database. I want to be able to read/write from all locations and have all the servers clustered together so that if something fails, there won't be any problems.
Any "in production" solutions?

 

[Catadmin]

If you don't need data from all the individual DBs in one database (i.e., no one is referencing another location's data except you or the main location), then you could get away with setting up the separate databases on clustered servers in the a couple of instances.

Say you have 2 servers clustered. Split up the databases and put half on one instance and half on another instance. Have one instance apiece on each clustered server (so you have a true active-active server and can take advantage of load balancing, then, if you need to, set up linked servers between the instances.

Or, you could use Merge Replication to merge all the individual location data into one parent database, but Replication gets tricky and can break easily.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[jensenc391]

The problem is I need all the servers to have access to every other server's database and be able to read/write simultaniously

 

[ptheriault]

What version of SQL is this? If it is 2005 look up peer to peer replication. If not. You might try merge replication. The problem you are going to run into is how are you going to handle conflicts when two users update the same record at the same time?

- Paul
- Database performance looks fine, it must be the Network!