Backup strategy

[SatishPutcha]

Hello Everybody

This is my scenario;

I have developed an ERP system that runs on ASP-SQL for a multi-location business.

Now, we have taken a web-hosting plan and they host the IIS and the SQL Servers.

However, while we have two locations(Live and Back-up) from where to access the ASP front-end, our SQL Server is accessible on only one server.

My worst fears came true on Sunday when the SQL Server conked off leaving the system inoperable a whole day.

Now my question is: In such a situation what is the best route to take w.r.t having a fully-functional (atleast all data upto the previous day's) SQL database hosted on the other web host as a backup?

FYI, if it helps, my web host allows for creating a back-up(.BAK) of the DB.

Thanks everyone.

Regards
Satish Kumar

 

[Catadmin]

Do yourself a favor. Never host SQL Server on a Web machine if at all possible. You could be exposing your data to people who would steal it or corrupt it and then you get to have a discussion with your boss as to why he should let you keep your job. Not good. @=/

If you want your SQL Server to remain up 24/7, your 3 options (all of which involve getting another server so you don't have a single point of failure) is A) Clustering, B) Log Shipping and C) Replication. Of those three options, Replication is the worst because it is not truly a disaster recovery solution, so I don't advise even considering it.

With SQL Server 2005, you can also do Database Mirroring which is supposed to take the place of Log Shipping, however Log Shipping is still available with 2k5, so I'm not sure what Microsoft was thinking when they designed 2k5. Regardless, either of them is a good, semi-automatic and cheaper solution to having a quick failover than Clustering is.

Cluster, BTW, is very expensive because you have to have special hardware. With DB Mirroring and LogShipping, any ole' server will do and it doesn't have to be identical in OS / Hardware setup to the original server.

Anyway, the cheapest and simplest to implement option is the Log Shipping or Database Mirroring. Either way, you want to get another box so you don't expose yourself or your company's information to people who don't need to see it. And having a single production box for SQL is just tempting fate anyway.

Hope this helps.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SatishPutcha]

Hello Catadmin

Thanks for your reply. I am a little confused by a couple of your points. Could you please explain as my SQL Server knowledge is hazy at best:

"Never host SQL Server on a Web machine if at all possible."
We don't host our SQL Server. It is with a web-host and they have confirmed their SQL Server is hosted on a different machine. Is that what you meant?

"A) Clustering, B) Log Shipping and C) Replication"
Again, since this is not our SQL Server, can we do these ourselves or will our SQL Server host have to involved?

Personally, I liked Database Mirroring after I read an article on it(Microsoft's of course, so they won't tell how bad it could be!!).

Regards
Satish

 

[Catadmin]

If you don't host your own machines and either can't get access to them or don't have anything to do with setup, you'll have to get with your hoster to get this situation corrected.

As far as my first point, when you said "In such a situation what is the best route to take w.r.t having a fully-functional (atleast all data upto the previous day's) SQL database hosted on the other web host as a backup?" I took this to mean that you wanted to switch SQL over to a machine that actually hosts websites (an IIS machine or the like), which is why I said never host sql on a web machine. Sorry for the confusion.

Since you do have a third party taking care of your SQL Server, I would check your maintenence contract with them to verify what kind of failover time they guarantee you. I.E., do they promise to have your production machine back up within 2 hours or 2 days? If there is no such clause in your contract, I sincerely advise re-negotiating. Failover time is crucial in many corporate situations and if they haven't promised anything, then you could be down for days before they decide to fix the situation.





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"