Home / server / Questions / 67612
Accepted
codeape
Asked: 2009-09-23 01:35:37 +0800 CST

Is replication suitable for this scenario?

  • 772

The case:

  • An industrial IT system, controlling and storing data on two production lines
  • There is one server per production line. Each server runs the automation objects for that production line. Each server has a local SQL server database that stores data for that production line.
  • The automation software has built-in redundancy. A server failure on one production line's server will cause the automation objects to run on the other server instead.

An imagined scenario: The disk controller on production line 1's server fails. Currently, the following will happen:

  • The automation objects will start running on production line 2's server instead.
  • But when the automation objects start doing data access to server 1, they will of course fail (since the disk controller is non-functional).

So we have discussed options. A colleague has suggested the following:

  • Replicate the server 1 database on server 2. And vice versa.
  • Set up the data access with localhost connection strings.
  • So when the automation objects start running on the other server, their data access will go to the database on the server they are running on.

I do not have experience with replication. Is replication a viable solution to the problem described above? Important things to remember when setting up replication? Other suggestions?

sql-server

2 Answers

  1. Best Answer

    If you're using SQL2005 or 2008 you could consider synchronous database mirroring between your two servers, with a third server acting as witness which handles automatic fail-over. This is known as "High safety without automatic fail-over"

    http://msdn.microsoft.com/en-us/library/ms189852.aspx

    http://technet.microsoft.com/en-us/library/cc917713.aspx

    Or if you can use shared storage between your servers you could consider an active/passive windows cluster.

    • 3
    1. how about a centralized SQL backend for all those local DBs?
    2. virtualize the controlling OS with everything in it, so if a physical server fails, the VM is started on another server.
    3. leave everything as is, but equip the servers with FT features,, i.e. dual PSUs, RAID10 over two controllers, two UPS units powering redundant powergrids etc.
    • 1