May 9, 2006 at 9:06 am
Hello all,
i was wandering if there are any documentation/suggestions on how to implement a backup that will not interrupt usage of database?
Basically we have a application that need to be available 24/7.
We have 2 servers, one is a primary, second using the log shipping function in case primary server dies. Howerver once a day when we perform full database backup system become non-responsive for 30-45 minutes.
Is there any ways around it? what are the best practice? We are thinking of creating the cluster of 2-3 SQL servers, however from what i have read, there is still going to be the same issue with backup on clusters.
Thanks.
PS. this is a MSSQL 2000 Standard edition.
May 9, 2006 at 11:16 am
You have other problems......I backup a 200+ GB database every day (full backup) and it doesn't have any affect on the server.
Maybe your system isn't powerful enough? Maybe you have other things running at the same time? Are you backing up to the same drive as your database?
-SQLBill
May 9, 2006 at 11:24 am
probably you are correct.
1. yes we do backing up (at this moment) to the same HDD (i know it's not the best option but is this the only thing that affects me?).
2. powerfull enough - that's a tough question i mean how powerfull it should be? (Dual CPU, 2 Gb of RAM and etc)
3. i do believe that nothing else is running at that moment.
May 9, 2006 at 1:03 pm
It's probably an issue of you doing the backup to the same disk(s) as the database. It's trying to read from AND write to the same drives at the same time. And.....while it's doing the backup, the database is probably also still having transactions.
So, the drives are have data written to them - insert/update/delete transactions, data read from them - select transactions, data written to them - backups, and data read from them - data for the backup. Lot's of I/O on those disks.
-SQLBill
May 10, 2006 at 5:59 am
As a short term solution, try redirecting the backup to another server.
May 10, 2006 at 3:58 pm
We do this, backing up a 40 gig database across a 1 gigabit fiber link to another server. The impact on the primary server is noticible but certainly not unacceptable. The backups land on the backup server, which is where they would be used if the primary did a complete meltdown. The "1 gig" fiber link performs at about 600 meg in this configuration. The full backup takes about 2 hours.
Student of SQL and Golf, Master of Neither
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply