March 19, 2013 at 5:39 am
We just upgrade our QA environment to SQL Server 2012 from 2008R2. Our standard process is to backup four databases from ETL server and restores each night on DB server. When we are on SQL 2008R2, the restore process was taking only 15-20min to restore four databases (total size = 130GB) but now we are facing issue where SQL Server2012 is taking same size of database almost 2:20hrs.
Currently, my Dev environment and production are still on 2008R2 and my restore job via SQL script finish in 15min but I am having hard time to figure out why 2012 version is taking 2+hrs?
Any help greatly appreciated.
SQL Server Version: Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 X64> (Build 7601: Service Pack 1)
March 19, 2013 at 9:35 am
Restoring a 2008 R2 backup to a 2012 instance means the restore process has to do an in-place upgrade of the involved database files which can add time to the restore process.
Are you watching the restore process, or do you have access to the output as it runs? If you supplied WITH STATUS then you'll see once it reaches 100% that upgrade messages start to be written. See if you can isolate which part is taking so long, the restore of the data files to disk, or the upgrade process that starts after that is done.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 19, 2013 at 11:29 am
Thanks OPC for your reply.
Currently, my both ETL and DB servers are on 2012 and I am taking FULL backup from ETL server and restore on DB server. Also, I am storing my backup on local drive.
Any thoughts...
March 19, 2013 at 11:44 am
Just so I understand the scenario, you have the same processes running between 2 2008R2 servers and 2 2012 servers, where the source and target are the same database versions. If that scenario is true, there isn't anything related to SQL 2012 that would make the backup / restore process take longer. The issue is more likely related to the hardware that the SQL 2012 servers are running on, more specifically the disks associated with those servers. If you're using DAS (Direct Attached Storage) on the SQL 2012 servers, ensure that the raid controller(s) are using a write-back cache setting and not a write-through. Older IBM servers used to automatically ship with the battery backup unit for the raid controller, the newer servers don't. It's an additional option. I'm not saying this is you're problem, but it's a good place to start.
March 19, 2013 at 1:01 pm
Richard,
Yes, it is on local storage and scenario is correct.
I need to check with my server team to find out the RAID config. Will post what I will find out later today.
Thanks
March 19, 2013 at 1:13 pm
The only other thing that comes to mind is if you're using compressed backups on the 2008R2 and not on the SQL 2012 servers.
March 19, 2013 at 1:21 pm
Richard,
NO, they both are regular backups, and i tried with compressed backup but has the same situation. So, now i am using regular backup. I have asked my server team to give me difference between two server's RAID config. since both server drives are local. (Partitioned)
March 20, 2013 at 3:35 pm
Have you checked the physical layer - are they all actually getting gigabit data transfer rates (try a Windows Explorer file copy) if you're restoring over the network.
Check the actual file sizes.
Is there excessive fragmentation of the free space on the 2012 restore server?
Does the 2012 restore server have a bad battery on the RAID card, or is cache otherwise disabled?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply