MS SQL Database Backups

[antic]

Frankly I'd rather charge a client $60 (is that US$??) for a restore instead of telling them we don't have reliable backups. What if a rebuild is necessary and - oops - sorry, no backup!

Also, I'm not aware of any way of backing up data remotely that also backs up things like Diagrams, as normal backups do. I like Diagrams... Backups will be tedious, to say the least, if your client is on dial-up. Enterprise Manager is slow enough remotely as it is.

This is all astonishing really.. I don't even know why this is an issue. If Vortech is trying to backup all the MDF and LDF files directly to tape then they won't have much luck, as they're in use all the time of course.

This is the procedure as far as I'm aware:

1. Open Enterprise Manager and connect to your SQL Server.
2. Open the "Management" node.

For each database do the following:

3. Right-click on the "Bakckup" item and select "Backup a Database"
4. Select the database from the dropdown at the top.
5. Below that, make sure "Database - Complete" is selected.
6. At the bottom select "Overwrite Existing Media"
7. Below that - and this is the tricky bit - tick the "Schedule" box.
8. Click the "[...]" box on the right to set the schedule for 12AM every day.

Rinse and repeat 3-8 for each database.

This is usually why providers charge a small setup fee per database, as it takes a few minutes of their time to set a backup procedure and such. Personally I'd rather pay (and pass on) a setup fee, knowing it's being done properly!

Of course another idea is to give each reseller an SQL login with backup rights for their databases, so we can set the schedule to suit our time zone.

Then all Vortech has to do is make sure the .BAK files are being backed up to tape.

Would very much welcome some comments about setting it up this way..

[Brangwyn]

The problem isn't setting up the backups antic, thats already taken care of I understand, its the fact that the maint/backup plans have mysteriously been failing or stopping thats the issue.

Matt said earlier in this thread he'd check to see if last weeks changes have helped so I'd wait to hear from him.

[antic]

Cool, will do. Just want to make sure this is sorted ASAP, as it sounds like it's been going on far too long, which is scary.

[Brangwyn]

Yeah its a pain, but don't forget also that (at least as far as I'm aware) Vortech have no obligation under the terms of services we agreed to when signing up to maintain backups for anything.

Obviously it would be bad business practice if they didn't have any procedures in place for restoring a machine after a failure, but I'm pretty sure we can't hold them responsible for maintaining regular backups of "user data"

[antic]

That would contradict this statement in their FAQ:

http://support.vortechhosting.com/de...php?article=47

Doesn't mention SQL or other data backups though...

*raises hand* Vortech Admin - can I have some verification that daily backups are part of your SLA for resellers?

Brangwyn - what's the procedure you give your clients for backing up SQL remotely? Can one send a command to SQL Server via ADO to do a backup?

[Brangwyn]

Really? That would contradict this statement in their FAQ:

How does it contradict it ? I said they have no obligation to perform the backups (as in a legal sense), the fact that they do take them is just a bonus for us really.

Last time Brad documented the procedures they went something like this: Unix and NT are backed up 24 hourly with a 15 and 30 day generation being maintained also. Pretty sure things have changed a little since then becuase they're no longer doing the "single tar file" backup but backing up sites on an individual basis now (at least thats what i was told by support last week).

can I have some verification that daily backups are part of your SLA for resellers?

They are conducted as above however if you want something restored and it doesn't exist you can't hold Vortech in any way responsible for the data loss, therefore in my opinion you might as well place yourself as best you can to never have to rely on them either hence the reason i give my customer no guarantees on backup availability.

The only "SLA" that applies to resellers is this on here on downtime http://www.matrixwebhosting.net/sla/

Doesn't mention SQL or other data backups though

Or the fact it's $60 to request something to be restored either

Brangwyn - what's the procedure you give your clients for backing up SQL remotely? Can one send a command to SQL Server via ADO to do a backup?

I'm actually about to write a new FAQ for my customers, but basically those who use say MS SQL perform their own backups just using a full DTS export which they run from their local machine.

[mresell]

Guaranteeing backups is sorta like the 100% uptime guarantee. I would feel highly uncomfortable telling someone that. Tho a certain level of backup should be on vortech. Counting on someone else to backup your stuff just isn't wise.

[antic]

Maybe... In my view, paying for "managed servers" means:
a) the provider takes all reasonable measures to ensure services are always available, and
b) the provider takes all reasonable measures to ensure no loss of data.

I don't think a normal nightly backup procedure is beyond reasonable, and clients who know anything will think likewise.

But since we don't have control over that, we have to look to Vortech or offer some assurace that we can comfortably pass on to clients.

I'm glad Vortech is trying to do it anyway, since it's not in the SLA I guess they don't technically have to.

Perhaps it should ultimately be the client's responsibility, but it's problematic when data is constantly being updated online and we resellers aren't allowed permissions to make proper backups using the normal SQL backup procedure in EM.

If resellers had dba login permission to do backups and make them available to clients, I would be happy enough, and knowing Vortech does their best to back us up, so to speak.

[admin]

We do proper backups but if it fails what can we do. We can run it again, but from time to time the Microsoft backup made in to mssql just fails for no reason and gives no reason..

I think matt may of fixed it but I am also looking at some out side programs that might be able to do it a little better.

[Brangwyn]

In my view, paying for "managed servers" means:

I guess we differ a bit there, my idea of a managed server costs a lot more than $35 a month

I don't think a normal nightly backup procedure is beyond reasonable, and clients who know anything will think likewise.

No it's not beyond resonable, but unless you can personally guarantee to your customers that its being done and is working correctly (taking backups is one thing, they need to be regularly tested to make sure you can actually restore from them too), I wouldn't ever set an expectation with my customers that I can't personally ensure happens, I'd rather loose a customer than have one try and sue me because they lost data.

Unfortunately giving dba access opens up too many extra issues like people being able to change database quotas outside of H-Sphere etc. Not to mention the issues it would cause if they let everyone backup their databases manually like that such as people just leaving months worth of daily backups on the machine rapidly causing it to degrade. I'd personally much rather it was managed centrally by Vortech than let individuals have a free for all on the database servers.

[antic]

In Aussie dollars it's more like $55 a month, so maybe that's partly my reason for wanting certain things my way.

I'll ask the chappies at WebCentral - the people I got the DAF security info from - and ask them what their regime is for SQL backups. They do these three times a day every day, so they must have their system down to a fine art.

And on that point, don't forget to offer your support for DAF on the thread:

http://www.forum.psoft.net/showthrea...&threadid=5773

"iseletsk", their administrator, has given a positive response, so please push em while we got their attention.

[antic]

I just had an idea. It may allow us resellers to perform a backup any time we need to, and even scedule it with a CRON job.

I may be nuts, but picture this..

1. IIS is enabled on the SQL server machine. It is not open to the internet (bad news for an SQL server) but accepts requests only within the internal network.

2. A single ASP script resides on that site. This is the "Backup Script". It accepts URL parameters of database name, uid and pwd, as well as a UNC destination for the backup file.

3. The only way to call the Backup Script is via an internal, server-to-server HTTP call - from your reseller account to the SQL Server's web server. (If you use ASP on your reseller account, you would use the MSXML object to do this.)

This is the nifty bit:

4. The Backup Script, when called, first uses the given uid and pwd to make sure the caller has permission for the given database. If connection is granted, it then connects as a Backup User and performs and SQL BACKUP command straight to the given UNC location.

5. Each reseller has their own ASP page with which they can trigger the backup whenever they want to. They can even do a restore the same way as well, if we build that into the Backup Script.

Extra nifty bit:

6. The reseller's-end script (and therefore their datbase backups) can be triggered regularly by a simple CRON job, thereby achieving regular backups under the control of the reseller. Vortech doesn't have to do a thing.

Notes:

1. If passing a UNC is not secure enough (no reason to backup your data into someone else's folder unless you make a typo), then FTP details can be passed instead to make it extra certain it's going to go to the reseller's account folder.

2. The reseller's-end script will use MSXML to call the Backup Script on the SQL Server machine - the reseller just plugs in the paramerters for their account. No-one else gets to see their parameters, as the reseller's script resides only in their own account and communicates only with the SQL Server.

Requirements from Vortech:

1. Enabling IIS on the SQL Server for internal network access only.

2. Creating an SQL Server login with backup rights, and plugging that uid and pwd into the Backup Script. They can do that themselves, neither I nor anyone else needs to know what that uid and pwd is.


As far as I can see, this solution is very flexible and completely secure. Please let me know what you all think, even if it's that I am indeeed nuts.

Nevertheless, I can put this solution together for us in a few days; it's really very straightforward, just hard to describe.

OK - I checked today and....

*drumroll*

The backups are working!

They are "failing" sometimes, but the error log indicates that a single database failed to backup due to the transaction log being full. With the recent change to make "simple" and "auto-shrink" default options on a database, this shouldn't happen so much. And it doesn't seem to be stopping the entire backup either. I suspect that the failure before was due to trying to run optimizations and integrity checks on the databases themselves, but we wont be doing that anymore.

[Brangwyn]

Great news Matt

In Aussie dollars it's more like $55 a month,

*grin* it's even more in NZ$ ... actually my annual spend with vortech is around $5-$7k NZ per annum (depending on currency fluctuations), but I still don't consider this to be any thing like an expensive service, when I want true managed servers with guaranteed x,y,z I expect to pay closer to $1000 per month.

[antic]

And I stayed up so late thinking that up...

I still think it's a good idea... sometimes it's necessary to have access to live data for testing etc. This makes it easy to get hold of it.

Anyone think it's worthwhile giving it a go?