IIS Web Server w\ SQL Backend Backup

[tyanni]

I've got a 2003 Box with RAID 1 for both the data and OS driver. Its running IIS6, hosting a webbased app and SQL Server 2005 for the backend. We are currently backing the db up using the built in maintenance wizard and then a simply batch file copies the backup to a network share each night. I'd like to backup the DB directly to the network (which I know can be done by changing the user account which the sql services run under) but am also looking for a backup solution which will cover the webpage, app install folder, and also allow us to get up and running very quickly if the OS Array fails. So far, I've come up with the following:

1. Acronis True Image for Servers, creating a full image of the OS Array and then one full image of the Data Array w\ differential images each night - unfortunately, this produces a LOT of large files

2. Backup Exec, Danz Retrospect, or a similar product doing snapshots of the OS array, and then file and folder backups of the Data we need each night - much slower restore of the OS, but less storage needed.

Any thoughts? Anyone used Retrospect before?

Thanks,
Tim

 

[spyordie007]

Not sure what your requirements are but here are a couple of thoughts:

1. No need for the batch file copying your backups across the network; NTBACKUP as well as the native SQL backup client can write their backup files directly to a network path.
2. Any kind of imaging solution is less than ideal for SQL transaction logs; you really need to be backing it up with a native client.
3. You might want to look into some of the new SQL 2005 backup/restore capabilities, they've added a lot of new options since SQL 2000 for backing up and restoring SQL.
4. If fast restore is the desired need you might want to consider a warm-failover server. Syncing file-changes in your web-root would be easy enough and you could use something like SQL failover clustering (SQL standard) to fairly easily keep a second server ready to go in the event of a failure of the first. This would be ideal since you could be up and running in minutes and than take your time restoring the "main" server.

I have not used retrospect before. I use Veritas (aka Symantec)'s BackupExec for remote Server/SQL backups here. Once we migrate over to SQL 2005 in addition to our backups I'm considering a warm-failover options for SQL.

Regards,
Erik

 

[tyanni]

I've yet to change the sql services to run as an account with network access, which is why we aren't using the direct to the network option. Its on my list of things to do, should it be necessary. Unfortunately, a second server is not available, otherwise this would definitely be a good idea. What did you think of BackupExec?

Tim

 

[spyordie007]

What did you think of BackupExec?

I've been using BackupExec for years; consistantly does a good job and doesnt require much fuss. The only complaint I have is licensing; you have to have a BE server license, and a remote agent, and a SQL agent... (though other vendors create similar headaches with their licensing schemes).

Regards,
Erik

 

[Wizkid]

Stay away from Retrospect... I found it to be unnecessarily complex and not as flexible as Backup Exec. I have been using BU for years without any problems.

Your OS should never really change, other than patches, so if you really want an image, just do it once, and then backup the web files and sql database every night.

 

[tyanni]

Thats the plan - OS image before\after patches, otherwise never. Its really the DB\Web Files which are important.

Spyordie007 - Have you ever had to do a SQL DB restore from Backup Exec? I've heard mixed reviews about the restoring capability of the program.

 

[spyordie007]

Have you ever had to do a SQL DB restore from Backup Exec?

Yes, havent had too much problems; have done a full SQL Server restore as well as individual databases.

I dont know what the problems that you've read about are but the key is to test your restore process in the lab before you have a failure that requires it; makes the process much easier

Regards,
Erik

 

[tyanni]

Are you running BU on the server itself, or doing it remotely? I assume its cheaper to run on the server itself (no license required for remote agent) but I could see how it might be nice to use the program to backup more than one server. Any thoughts?

Tim

 

[spyordie007]

I'm backing up SQL (and all of my servers) remotely; makes it much easier because I can back them all up from a single location.

If I were just looking to back up SQL locally I would probably stick with NTBACKUP, no need to spend the extra money on a 3rd party tool.

 

[tyanni]

It would be SQL plus the app directory, plus I want email confirmation that this has all happened... I don't think that can be done with NTBackup, although it can be done with the SQL Maintenance Wizard. Plus, we'll probably use the app more in the future to do remote backups.

 

[spyordie007]

The real advantage to using an application such as BackupExec (et al) is the ability to do remote backups. If you're looking to do remote backups moving forward than I think you're on the right track looking for an application to manage this.

If I were only doing local backups I would find it very hard to justify using additional tools for backups.

 

[tyanni]

spyordie007, sent you a PM

 

[spyordie007]

Tim-

So I'm not the only one looking at this I'm going to post your PM:

Hey -

I have another question regarding BackupExec and SQL - hopefully you remember the thread you helped me out in. I thought these questions might be better left to PMs -

I was curious as to the users you ran backup exec and sql under - domain? local? It seems backupexec needs to be a domain user to write the backups to a network share, but does the accout it uses to access the SQL DB need to be a domain user? Are there security concerns associated with running sql as a domain user? Should the backup exec user for SQL be the SA user, or another one created specially for it (which is what I am doing on my test machine).

Thanks -

i appreciate all of your help so far.

Tim

I've got a specific backupexec service account on the domain; it has access to the resources that it needs to backup as well as the file locations it needs to write to for online backups.

SQL on the other hand runs as "Network Service"; your backupexec account just needs to be granted access to the SQL server.

Veritas/Symantec has tons of documentation online about stratagies for backing up a lot of differant types of application data.

Regards,
Erik

 

[tyanni]

Thanks - I've seen conflicting info on running sql as a domain acct vs. running it as local service or network service as far as security is concerned. Sounds like local acct is fine and just run backup exec as a network user.

 

[spyordie007]

There are cases where you would want SQL to run as a domain account (if you wanted it to be going out and accessing network resources) but that doesnt really have a bearing on this conversation.

Regards