I don't think a remote backup is really required. How many disks does your server have? Assuming there's one spare for backups you could just do this to maximise reliability:
backup to backups to 2-3 different locations. e.g. lets say you have your main machine (named SV01), the other one you mentioned (SV02) and managed to find another computer that has quite a bit of free space & isn't used that much (SV03). and your work computer (DRNOR) also has quite a bit of space. You would then be able to do this:
BACKUP DATABASE DATA2007 TO DISK='E:\backups\db.bak'
MIRROR TO DISK='\\SV02\backups\db.bak'
MIRROR TO DISK='\\SV03\backups\db.bak'
MIRROR TO DISK='\\DRNOR\backups\db.bak'
Your backups are then copied to 4 different computers (the first is on the database server)...you'd have to be pretty unlucky for all 4 computers to have hardware failures on the same day 😛 The same process can be done with the transaction logs too.
I think email alerts is easy to set up if your company already has an SMTP mail server. I've never done it but if you have the details it should just be filling them into the Database Mail config wizard. You can ask someone at work whether you have SMTP there. If so then YouTube should have short videos for configuring the Database Mail feature.
drnorbert (6/15/2013)
Sorry They are changing/adding 100 records per Hour.
You will probably want to backup every 15-30 minutes then to limit the potential loss.
drnorbert (6/15/2013)
As we have 2 small servers, is any way to plan that when one is down the other takes over?
There is but if you're doing things "by the books" then this would probably cost a lot of money because of the licensing fees. I don't know of the license fees for Database Mirroring; this would be something for you to look at. I also don't know if the Enterprise Edition fee is a one-off payment or an annual charge. Whoever arranged for EE at your company will have a better idea on how the license costs work.
- If it is a one-off payment then I think they would be reluctant to pay an additional charge for mirroring.
- If it is an annual fee then something worth investigating is how much it would cost to have the 2 servers using Standard Edition with Database Mirroring instead of having 1 server using Enterprise Edition...I have no idea how much more expensive the mirroring will make it but it may be worth asking the question. This would have the advantage of the possibility of automatic failovers and near to 0 data loss. But at the cost of higher license fees (maybe) and added complexity to your database configuration.