November 25, 2008 at 2:04 pm
In order to copy databases from Production to Development for Software Developers I have created an SSIS task for each Database.
This works great for all small databases. It simply copies the database from production using WITH COPY_ONLY. It then restores the database on the Development Server and reconnects the logins. The full steps are below.
The real problem that I am having is with large databases. If the .bak file is over 4 GBs, the Restore operation on the Development Server takes over an hour to finish and our Development Server crawls to the point where no developers can use it and we just restart it.
I have a theory as to what needs to be upgraded, but I could really use suggestions/advice.
Is there a better way to accomplish this task? I am primarily a ColdFusion developer, not a DBA, so I could really use your advice. Please help!
Our Production and Development Server Specs are below:
Production Server
OS NameMicrosoft(R) Windows(R) Server 2003 Standard x64 Edition
Version5.2.3790 Service Pack 2 Build 3790
Other OS Description Not Available
OS ManufacturerMicrosoft Corporation
System NameSQL
System ManufacturerIBM
System ModelIBM x3850-[88644RU]-
System Typex64-based PC
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 8 GenuineIntel ~3336 Mhz
BIOS Version/DateIBM -[ZSE106AUS-1.01]-, 9/18/2006
SMBIOS Version2.4
Windows DirectoryC:\WINDOWS
System DirectoryC:\WINDOWS\system32
Boot Device\Device\HarddiskVolume1
LocaleUnited States
Hardware Abstraction LayerVersion = "5.2.3790.3959 (srv03_sp2_rtm.070216-1710)"
User NameNot Available
Time ZoneCentral Standard Time
Total Physical Memory32,767.71 MB
Available Physical Memory235.32 MB
Total Virtual Memory34.82 GB
Available Virtual Memory3.12 GB
Page File Space3.76 GB
Page FileC:\pagefile.sys
SCSI
NameIBM ServeRAID 8i Controller
ManufacturerAdaptec
NameLSI Logic MegaRAID SAS 8480E RAID Controller
ManufacturerLSI Logic Corp.,
NameLSI Logic MegaRAID SAS 8480E RAID Controller
ManufacturerLSI Logic Corp.,
Disks
DescriptionDisk drive
ModelAdaptec Array SCSI Disk Device
Bytes/Sector512
Size33.77 GB (36,265,259,520 bytes)
Total Cylinders4,409
DescriptionDisk drive
ModelLSI MegaRAID 8480E SCSI Disk Device
Bytes/Sector512
Size407.92 GB (437,996,160,000 bytes)
Total Cylinders53,250
DescriptionDisk drive
ModelLSI MegaRAID 8480E SCSI Disk Device
Bytes/Sector512
Size407.92 GB (437,996,160,000 bytes)
Total Cylinders53,250
DescriptionDisk drive
ModelLSI MegaRAID 8480E SCSI Disk Device
Bytes/Sector512
Size407.92 GB (437,996,160,000 bytes)
Total Cylinders53,250
DescriptionDisk drive
ModelLSI MegaRAID 8480E SCSI Disk Device
Bytes/Sector512
Size407.92 GB (437,996,160,000 bytes)
Total Cylinders53,250
Development Server
OS NameMicrosoft(R) Windows(R) Server 2003 Standard x64 Edition
Version5.2.3790 Service Pack 2 Build 3790
Other OS Description Not Available
OS ManufacturerMicrosoft Corporation
System NameSQLDEV
System ManufacturerDell Inc.
System ModelPowerEdge SC440
System Typex64-based PC
ProcessorEM64T Family 15 Model 6 Stepping 4 GenuineIntel ~2793 Mhz
ProcessorEM64T Family 15 Model 6 Stepping 4 GenuineIntel ~2793 Mhz
BIOS Version/DateDell Inc. 1.1.0, 9/27/2006
SMBIOS Version2.3
Windows DirectoryC:\WINDOWS
System DirectoryC:\WINDOWS\system32
Boot Device\Device\HarddiskVolume1
LocaleUnited States
Hardware Abstraction LayerVersion = "5.2.3790.3959 (srv03_sp2_rtm.070216-1710)"
User NameNot Available
Time ZoneCentral Standard Time
Total Physical Memory4,029.81 MB
Available Physical Memory2.23 GB
Total Virtual Memory6.19 GB
Available Virtual Memory4.61 GB
Page File Space2.50 GB
Page FileC:\pagefile.sys
SCSI
None
Disks
DescriptionDisk drive
ModelST3160812AS
Bytes/Sector512
Size149.01 GB (159,998,146,560 bytes)
Total Cylinders19,452
SSIS Steps
1. Backup production database. I use the WITH COPY_ONLY to not mess up the backup chain.
USE master
GO
DECLARE @databaseName Varchar(100);
DECLARE @backupDataPath Varchar(500);
SET @databaseName = 'Utility';
SET @backupDataPath = '\\sqldev\f$\Backup\' + @databaseName + 'Data.bak';
-- Back up the full database.
BACKUP DATABASE @databaseName
TO DISK = @backupDataPath
WITH COPY_ONLY;
UPDATEmsdb..backupset
SETname = 'Backup Exec SQL Server Agent'
WHEREdatabase_name = @databaseName
PRINT 'Backup successful!';
GO
2. Restore the database on our development server.
USE master
GO
DECLARE @databaseName Varchar(100);
DECLARE @databaseNameOriginal Varchar(100);
DECLARE @exec_stmt Varchar(2000);
DECLARE @backupDataPath Varchar(500);
SET @exec_stmt = '';
SET @databaseNameOriginal = 'Utility';
SET @databaseName = 'Utility';
SET @backupDataPath = '\\sqldev\f$\Backup\' + @databaseNameOriginal + 'Data.bak';
select @exec_stmt = 'ALTER DATABASE ' + quotename( @databaseName , '[') + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE';
EXEC (@exec_stmt);
--Restore the full database backup.
RESTORE DATABASE @databaseName
FROM DISK = @backupDataPath
WITH RECOVERY, REPLACE
select @exec_stmt = 'ALTER DATABASE ' + quotename( @databaseName , '[') + ' SET MULTI_USER';
EXEC (@exec_stmt);
GO
3. Logins
DECLARE @databaseName Varchar(100);
DECLARE @exec_stmt Varchar(2000);
SET @databaseName = 'Utility';
select @exec_stmt = 'use ' + @databaseName
EXEC (@exec_stmt);
EXEC sp_change_users_login 'Update_One', @databaseName, @databaseName;
EXEC sp_change_users_login 'Update_One', 'master', 'master';
EXEC sp_addrolemember N'db_owner', N'WELCOMELINK\SQLDevelopers'
EXEC sp_addrolemember N'db_datareader', @databaseNameOriginal
EXEC sp_addrolemember N'db_datawriter', @databaseNameOriginal
EXEC sp_addrolemember N'db_owner', @databaseNameOriginal
GO
November 25, 2008 at 2:48 pm
One option you have without having to upgrade the hardware would be to restore from a local drive instead of across the network. My guess is that your network connectivity to the development server is not fast enough.
If that doesn't help out - then, of course you are going to need additional processors and more memory.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 25, 2008 at 2:56 pm
Thank you for the quick reply.
The restore is actually done on a local drive.
The production server runs the backup and saves the backup file on the development server.
This backup step is very fast and finishes in a few minutes.
The restore operation on the Development Server uses the .bak file on its local drive.
The restore operation is the problem step.
Can you please look at our Development Server specs and provide an upgrade recommendation.
We thought it might be an I/O bottleneck because our Development Server only have one hard drive.
Our Production Server has 2 Raid 10 Arrays.
Should we be looking at CPUs and RAM instead of I/O???
I'm sure you are more of an expert than I am about the internals of the Restore operation, which is the piece that is having the problem.
Thanks,
Ryan
November 25, 2008 at 10:28 pm
Ryan Duckworth (11/25/2008)
Thank you for the quick reply.The restore is actually done on a local drive.
The production server runs the backup and saves the backup file on the development server.
This backup step is very fast and finishes in a few minutes.
The restore operation on the Development Server uses the .bak file on its local drive.
The restore operation is the problem step.
Can you please look at our Development Server specs and provide an upgrade recommendation.
We thought it might be an I/O bottleneck because our Development Server only have one hard drive.
Our Production Server has 2 Raid 10 Arrays.
Should we be looking at CPUs and RAM instead of I/O???
I'm sure you are more of an expert than I am about the internals of the Restore operation, which is the piece that is having the problem.
Thanks,
Ryan
Okay - did not notice that you were backing up to the dev server because you are still using the UNC path and hidden F share to access the database. I would recommend that this part of the script be changed to reference the local drive. Probably won't make a difference, but at least it will show that the drive is local to that server.
Looking at the dev server - I see that you have two processors and 4GB of memory. Increasing both of those would definitely help on the restore process by allowing more CPU's for parallel operations and additional memory can never hurt. Adding additional spindles will increase the throughput and definitely help also.
I really can't say which of the above would the best to fix your problem. I would definitely look at adding two CPU's and as many spindles as possible.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply