3

edit
I'm testing possible solutions for database redundancy and high availability,I tested mirroring it worked fine, but lacks scalability plus there will be a lot of work in one server. Now I discovered Merge Replication, and in paper looks great (although I still need to know what happens if a server goes down and them comes alive again, will it synchronize? can you answer me to this one too?)

edit

My objective is to have various applications working simultaneous and communicating with one database only. But having the databases synchronized between them and that in case of one database goes down, all its data is not lost.

desired architecture

Is it possible to do this with Merge Replication?? where all sql instances are both publishers and subscribers, and have an distributor inside them.

Any considerations I should take, or other possible solutions for my problem?

regards

Improve this question
8
  • 7
    You haven't stated what the problem you're trying to solve is. Take a step back, start at the beginning, why do you need this topology?
    – Mark Storey-Smith
    Commented Nov 9, 2011 at 17:46
  • I'm testing possible solutions for database redundancy and high availability,I tested mirroring it worked fine, but lacks scalability plus there will be a lot of work in one server. Now I find about Merge Replication, and in paper looks great (although I still need to know what happens if a server goes down and them comes alive again, will it synchronize?)
    – RagnaRock
    Commented Nov 9, 2011 at 18:29
  • 5
    You're mixing objectives, redundancy/availability and scalability. If you could add a description of the application, the anticipated workload and the budget you have, you'll get far more valuable answers. At the moment, all I can suggest with the information you've provided is that you are probably "barking up the wrong tree".
    – Mark Storey-Smith
    Commented Nov 9, 2011 at 18:47
  • @RagnaRock I think you're going to have to do as MarkStoreySmith suggested and give us more information. You seem to be off on a few concepts here.
    – jcolebrand ♦
    Commented Nov 9, 2011 at 19:06
  • @MarkStorey-Smith each application will save data from a sensor into the database, a second kind of application will connect to one of the databases and analyse/process that data to generate some reports, etc. these databases will be on a boat, so I'll need more than one so that if one goes down, the 'sensor' application can still work by connecting to other database. that's pretty much it.
    – RagnaRock
    Commented Nov 10, 2011 at 12:28

3 Answers 3

Reset to default
3

You can do it with SQL replication, but in my experience it is an exotic and somewhat problematic technology.

Are your server's geographically separated? What is the need for having 3 copies of the data?

Improve this answer
1
  • the 3 servers will be in a ship, if one goes down, we need another ready to do his job. I thought about merge replication as this would be easier for the aplications who connect to the databases, they could simply connect to one of the available servers, without needing to check wich of them is the main one
    – RagnaRock
    Commented Nov 25, 2011 at 17:36
8
  • SQL Server scales up not out
  • HA/DR is separate to scaling
  • Replication is not designed to publish entire databases
  • Replication or mirroring add an overhead that defeats your scaling out
  • Merge replication will have conflicts at some point

Personally, I'd buy a bigger server, go Enterprise x64 edition, and wait for SQL Server 2012 HADRON

Improve this answer
4

I'm assuming you are in the cruise industry as you mentioned a ship?

I would go with mirroring with automatic failover. Your workload should be pretty light, and with mirroring the application can failover from between the machines with no change to the application. When you configure everything you simply setup the mirroring in the applications connection string, then if there is a failure while the ship is at see there is no support that needs to be done to the application. With replication you would need to manually tell the application, by editing the connection string while the ship is at sea, that the database has changed. This means downtime for the application and support costs and time as you walk someone through changing the config file on the fly.

If you then needed to replicate the data from the ship to the home office this would be where replication would be a good use case. I really don't see replicate as a good use case for the work on board the ship.

Improve this answer
2
  • the "applications" are sensors to collect data from the ship, and must be kept for monitoring possible illegal activities of a ship. And there is another application that uses the information to generate reports, and send information to a control center in land. My fear is that 2 databases might not be enough in case the ship stays in sea for a month or so...all that balancing of the ship might not be healthy.
    – RagnaRock
    Commented Dec 2, 2011 at 18:42
  • For additional redundancy I'd probably add log shipping into the mix to get a third (or more) copy available for a more manual failover. When SQL 2012 comes out (assuming you can upgrade quickly) you have have Always On which is basically mirroring with 4 mirrors instead of one. It does have the requirement of having a Windows cluster under it, which requires AD but I assume that the ships have AD installed.
    – mrdenny
    Commented Dec 2, 2011 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.