November 11, 2014 at 5:41 am
I have two databases say A and B in two different production servers. Weekly once we take full backup from the PROD servers and restoring it on its respective COB servers. Please refer few details about both databases
Server_Name DB_Name Backup_Size Full_Backup_Time Restore_Time COB_Srv_Name
S1(2012 Ver) A 650 GB 1 hr 10 mins 1 hr 30 min C1
S2(2008 R2) B 975 GB 17 hrs 19 hrs C2
Database "A" is in 2012 version and B is in 2008 R2. Both PROD servers are in same data centre and COB servers are also located in same data centre. But hopefully they are not sharing same SAN disks.
Now client asks us, though B is around 25% bigger than A in size, why is 'B' taking too much time for backup and restoration comparatively database 'A'. What is wrong?
Please help me, how to proceed with this question.
Thanks.
November 11, 2014 at 6:55 am
is server B a virtual machine?
what is the back end storage configuration for Server B?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 11, 2014 at 7:12 am
Try using perfmon to look at the disk read and write per second, disk queue length, NIC queue length and network IO.
Possible causes that I've seen in the past that are easily overlooked:
- Network interface not configured correctly (giving you 10 megabit instead of 100 or 100 instead of gigabit), or teamed NICs not configured correctly.
- Netbackup (or other infrastructure backups) flooding the network, particularly if scheduled infrastructure backups take longer than the schedule gap so they start running concurrently.
- SAN level maintenance operations like defrag.
November 11, 2014 at 7:22 am
In addition to the good suggestions so far, you should also factor in the number of transactions each supports. The recovery process of the restore operation when transactions are rolled forward and back can be the longest part of the restore. It's completely dependent on load when the backup is taken, so it's quite variable, regardless of the size of the database.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 11, 2014 at 7:31 am
Perry Whittle (11/11/2014)
is server B a virtual machine?Yes all are Virtual machines only in my Environment. Absolutely there is no physical servers.
what is the back end storage configuration for Server B?
I am very sorry I can't understand what do you mean back end storage configuration. Can you please let me know a bit more which are all the information you need in the storage configuration.
November 11, 2014 at 9:04 am
balasaukri (11/11/2014)
Perry Whittle (11/11/2014)
is server B a virtual machine?Yes all are Virtual machines only in my Environment. Absolutely there is no physical servers.
what is the back end storage configuration for Server B?
I am very sorry I can't understand what do you mean back end storage configuration. Can you please let me know a bit more which are all the information you need in the storage configuration.
Are the windows disks where the sql server data and backups stored coming from a Storage area network or are they locally attached drives
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 11, 2014 at 9:11 am
[/quote]
Are the windows disks where the sql server data and backups stored coming from a Storage area network or are they locally attached drives[/quote]
They are SAN drives! Not local drives.
November 11, 2014 at 9:16 am
balasaukri (11/11/2014)
They are SAN drives! Not local drives.
OK so engage the help of your SAN administrator and as previously suggested capture perfmon counters to check for throughput and latency.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 11, 2014 at 9:22 am
Thank you Perry, Don and Grant! Your inputs are so helpful, I am doing that.
Once again Thank you so much for your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply