Log shipping failover, Backup plan, Disk performance

I have been recently contracted for an NT Administrator position. I am
looking to come up with a backup plan, security plan, and we also have
problems with our SQL server (2000 Enterprise, We have two SQL servers set up
with log shipping). The primary has high CPU. Microsoft tech suggested that
certain stored procedures have recompiled which may contribute to high CPU.
More troubling is they said that the disk performance is not within
acceptable limits. On drives C and D, it took 4.13 seconds to do a single
transfer. Those values should be around 10 milliseconds. We contacted our
hardware vendor (Dell), and they said we must take the server offline to test
the disks.
We have no backup other than the transaction logs from log shipping. I am
not a DBA, but I would like to backup the data before working with our DBA to
fail the database over to the secondary server. What must I backup? What is
the best method? As far as I know there was no backup previous, although we
do have Backup Exec 2000 agent for SQL server v. 8.0, agent accelerator,
Intelligent disaster recovery option v2.0 (NT8.0). Assuming they were backed
up to a folder, what files would I search for to find a previous backup?
We have both SQL servers in a Colo site close by. For disaster recovery,
should we move one of them to another location or backup the logs to another
location? There is also a Merak Mail server and two Web servers that need
backing up. I hear that most companies are going away from tape backup, so
maybe I can get a NAS server and store it in a new location and backup the
SQL data and the rest to this server. What do you think? I know this is too
much to ask, but I don't know anyone to discuss strategy. If you give me
some direction or can answer any specific portions, that would be great.
Thanks for listening to my 10 questions!Hi there,
brendang wrote:
[...]
> More troubling is they said that the disk performance is not within
> acceptable limits. On drives C and D, it took 4.13 seconds to do a single
> transfer. Those values should be around 10 milliseconds. We contacted our
> hardware vendor (Dell), and they said we must take the server offline to test
[...]
> We have no backup other than the transaction logs from log shipping. I am
> not a DBA, but I would like to backup the data before working with our DBA to
> fail the database over to the secondary server. What must I backup? What is
> the best method? As far as I know there was no backup previous, although we
> do have Backup Exec 2000 agent for SQL server v. 8.0, agent accelerator,
> Intelligent disaster recovery option v2.0 (NT8.0). Assuming they were backed
> up to a folder, what files would I search for to find a previous backup?
You can use SQL Server's Enterprise Manager to do a complete backup (and
later a complete restore) of your databae. After opening Enterprise
Manager, you can right click on the database you want to backup and then
choose to do a backup from the menus there. There are other ways to go
about a backup, but that may be the easiest for you. You can also find
much more helpful information in the Enterprise Manager's help menu.
I should point out that you should do a complete restore of your
database to a separate server before doing any maintenance on your
production data. After you do a complete restore, you'll want to make
sure that everything is there -- recent transactions, stored procedures,
scheduled tasks, and anything else that might be of importance to you.
You should verify that your backups are good by doing a full restore.
You wouldn't want to go to do a restore and find your data isn't there.
> We have both SQL servers in a Colo site close by. For disaster recovery,
> should we move one of them to another location or backup the logs to another
Move one of them to Cleveland so you can come visit our great city! ;)
> location? There is also a Merak Mail server and two Web servers that need
> backing up. I hear that most companies are going away from tape backup, so
> maybe I can get a NAS server and store it in a new location and backup the
> SQL data and the rest to this server. What do you think? I know this is too
> much to ask, but I don't know anyone to discuss strategy. If you give me
> some direction or can answer any specific portions, that would be great.
In all seriousness though, you may consider looking into a solution
consisting of both database replication and regular offline backups.
It's true that many people are moving away from tape backups to
more-reliable Storage Area Networks (SANs) and Network Attached Storage
(NAS) devices, but it's also important to keep a few things in mind when
implementing a backup solution using network-connected storage. For
example, what happens if someone hacks your network and your
network-attached storage gets modified or erased? Will you be able to
take your storage to a remote location for safety (removable disks)?
There's many things I'm sure you'll want to think through. I couldn't
write enough about the things you'll want to investigate. I also can't
be held responsible for anything you implement, and I strongly encourage
you to do your own homework, consult multiple sources for advice, and
make sure you're comfortable with your solution in the end by doing a
complete database disaster recovery drill, from start to finish.
Hope this helps.