1

I have a SQL server database with data supplied from a 3rd party. They supply a tool to update the database data weekly. The tool is pretty old and uses ODBC. The update can take several hours. In order to have high availability, it was suggested to have 2 SQL databases, and store a "active database" setting in another database to determine which of the two databases applications should use (while the other could be being updated).

One issue we are running into is: How to do reference the active database in stored procedures in other databases?

Is this the right approach, or is there a better way?

Notes: Updates can either be incremental or can delete all database data then recreate the data. Databases are currently read-only besides the update tool.

Improve this question

1 Answer 1

Reset to default
1

What a DELIGHTFULLY CONVOLUTED solution they offer - Try the following:

  • Have two database servers, A & B
  • Have a DB IP address (in addition to the base IP addresses of your systems)
    • Assign the DB IP address to whichever DB server you want queries to go to

When update time comes:

  • Update the inactive machine ("Machine B")
  • Swap the DB IP address onto "Machine B"
  • Update "Machine A"

Note that this is susceptible to ARP cache issues -- You could also get the same results with DNS & a (very) short TTL (assuming your DNS TTLs are respected), a high-availability package that uses a virtual MAC, or with a load-balancing front-end.

Improve this answer
1
  • I begin to see the wisdom of MongoDBs solution for this problem. Just assume (or guarantee for the ones you write) the drivers take multiple hostnames/ips in their connection strings and let the clients sort it out. Of course that only works when multi master is a given.
    – Justin Dearing
    Nov 16, 2010 at 16:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .