1

We have a database intensive Java application. The DB we use is SQL Server 2008.

We are thinking of shifting our Java application to a 2-3 node load-balancing cluster. We have frequent application feature additions/bug fixes (and hence new releases). Whenever any database change is not involved in a release, we could probably update the Java application by taking one node at a time from the cluster for maintenance and deploying the newer application on it.

However, sometimes we make releases that involve updating the DB. (We run scripts and stored procedures that add, remove or update data on the DB.) These operations sometimes require about an hour. Is there a way we can handle this in some way so that we don't have to take the application down during this period of updating the DB?

Can any type/configuration of clustering help in this situation? Is any kind of solution possible where in we have the DB on 2 servers (with mirroring or something) and we take one server offline for our update process and when update is over, we bring the server back online and it is updated with the changes that took place since it was taken down from the other server?

I am new to clustering (and more so to SQL Clustering). So pardon me if the above paragraph amuses you or sounds awkward.

Thanks for your answers.

Improve this question

3 Answers 3

Reset to default
1

In short, no you can't run the SQL Server database on two servers and split brain them when doing updates. If you did, the SQL Server wouldn't have a way to get the two systems back in sync. Once you split them you could have the same row changed on both sides, or schema changes, etc. which would make it impossible to get everything back together.

When doing upgrades, take the outage. Put a flag in your config file so that you can take the site offline quickly and easily while the database changes are being done. Or write the app and the changes in such a way that the changes don't take the site down, allowing the site to keep working while the changes are made then put the new website on top of the changes.

If done correctly most database changes should be able to be made without taking the UI down.

Improve this answer
0

The real question is do you need to be able to write to the database from your application while the updates are being done? If you can deal with read-only, you could take a snapshot of the database, run your application in read-only mode off the snapshot, apply your updates to the actual database, then switch the application to use the real db once everything is finished.

I think there is a fundamental problem here though with "clustering" because what you are talking about isn't really clustering, you are really talking about something that is more similar to transactional replication (to be able to get the "load balancing" you are talking about). With clustering, you only have one active node, unless you have multiple SQL instances on a single cluster, but this would require reprogramming your application to handle the separate instances, and it wouldn't really accomplish what it sounds like you want.

TomTom's mention of atomicity is the real problem though when dealing with the kind of change management you are talking about. If you keep one of the instances in a writable mode while upgrading the others, you can have inconsistent data between instances when they are all upgraded since your scripts change code on the way to the new release.

Improve this answer
-4

You tell us. Can you handle with the application updating the database on one server while the other is being updated? You run into a serious problem with simply losing a lot of guaranteed SQL behaviours (atomicity). This can ONLY be solved by programming, not by an automated technology.

So, you can not ask whether clustering is good (hint: it is not). if you run multiple copies of a database, and take one down, how do you handle interim updates and resync them?

Improve this answer
11
  • TomTom, I know it sounds awkward and the little I knew in this area told me it is not possible. I was just wondering if there was some magic bullet technology out there that I didn't know of :) Commented Oct 5, 2010 at 5:12
  • I hope you don't mean that clustering in general is not good, because that would be an unfair statement to make. In the situation here, it would not be the best thing to do, however I use clustering to great success in production. Commented Oct 13, 2010 at 7:02
  • Well, pretty often clustering does not solve more problems thatn it creates, especially pre 2008. That said, no - it is not generally bad. BUt for high availability SQL Server, clusttering as first line is dump. Because you have no db copy it leaves things open to a dying server taking out the file system or db file (been there, seen that more than once). The first line of higher availability is mirroring. I would not go into clustering before using that for sql server. Commented Oct 13, 2010 at 7:11
  • Hold on - we've been using MS SQL clustering on over 100 multi-node clusters for over half a decade and had literally zero issues with them - ok we have on-site MS specialists that help us plan and deploy QFEs and updates but we've found SQL clustering to be enormously benefitial. Commented Oct 13, 2010 at 7:52
  • 1
    There is no neglect or ignorance going on here, I am simply cognizant to the fact that there are different applications and each high availability tech has it's pluses and minuses. You can't really compare DBM directly with Clustering or Log Shipping because they have different purposes. Anyone who has read Alan Hirt's book amazon.com/Pro-Server-2005-High-Availability/dp/159059780X would know this (and it sounds like you probably have.) Ultimately, we both agree upon the issue facing the OP, although I would be interested in talking more with you about this other topic offline. Commented Oct 29, 2010 at 20:24

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.