How to: DB failover via web

[m0nty005]

I'm working w/ W2K Server, IIS, ASP, SQL Server, odbc. My question here is how do I make it so that when one of my db server 1 goes down, my website would automatically redirect its source db to db server 2. This can be a complex process... any ideas? Dunno if it helps, but I've setup replication on db server 2.

 

[Catadmin]

The only thing I can think of is to set up Clustering for your SQL Servers. That way, the servers all have the same virtual name to the outside world and any failover is transparent.

Other than that, there is no way to have the process automatic. Everything else I can think of would require manual labor at the time of failure (or soon after) to repoint everything.

Sorry.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[m0nty005]

Can/should replication take place on these virtual db servers? I've setup replication, but currently it serves no purpose. My goal is to setup a way so that my timecard application will be failsafe if there's a problem w/ one of the server. That means data on both db server needs to be consistent!

 

[Catadmin]

I've never actually used Clustered Services before. However, the theory is that both machines are supposed to be identical anyway, otherwise Clustering serves no purpose.

I'm pretty sure you could/should use Replication between them to keep them current. Although, if there's another way to do it, I don't know what that tool is.

SQLBill, didn't you say once that you had set up or dealt with Clustering? Can you (or anyone else) answer this Clustering/Replication question?

Thanks,



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[SQLBill]

Clustering is different from Replication.

Clustering:

You have two servers, exactly the same. And you have one SANS or other storage device that is used by BOTH servers. On each server, you load SQL Server. BUT the database and log files go on the shared storage device. With this set up, you don't have/need a second copy of all the data. When one server fails, the second server comes online, but it's still using the shared storage device with all the data.

Replication:

You have two complete systems. At the very basic, each system has one server and everything is on that one server. Then replication is set up to copy everything from one server to the other. Normally the second server is a standby server.

In clustering, you use a virtual IP address for the shared storage. Everything is 'pointed' to that virtual IP. When failover happens, it's 'invisible' to your users.

In replication, each server has it's own IP address and you must use some method to point to the standby server when the hot server fails.

Disclaimer: I've done clustering with my SQL Server, but I've never done replication.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

I have done Replication. And it's not fun.

If you don't set up replication correctly the first time, you have to manually remove every little item in order to set it up correctly the second time. If you don't uninstall the components (including using some system SPs in QA once you've killed the subscriptions & publications), replication will NEVER re-install correctly.

After setup and testing, Replication will work fine.

For about a week.

Then you get this wonderful and very generic "Row was not found at subscriber" message that may or may not give details on which piece of data if failed on. If it does give details, resolving the error is rather easy because you can go into the column filters and row filters to track down the problem and re-write the row filters. If it doesn't specify, you get to truncate tables and re-do the snapshot or (depending on your type of replication) re-initialize all your subscriptions. Sometimes you get to delete the subscriptions, etc....

I could go on, but the synopsis of this is:

SQL Server Replication is the most picky creature you will ever have met. It doesn't know how to deal with "different" and re-prioritize. There are other programs which can say "Oh, this part didn't work, I'll stick it at the end of my current job and retry it". SQL Server is capable of doing that. If it can't get something to work or doesn't recognize a piece of data, Replication shuts down. And the DBA gets to restart it and tell the boss "Umm, I have no idea why it broke. Maybe it wanted a coffee break?"

Anyway, Clustering is much more reliable than Replication. Replication can be fun, but it requires a LOT of babysitting.

Hope that helps.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Catadmin]

Gah! I meant to say:

There are other programs which can say "Oh, this part didn't work, I'll stick it at the end of my current job and retry it". SQL Server *isn't* capable of doing that.

Cannot type today...



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

You may want to check out this thread thread962-1059501. We are talking about the same things there.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)