'MSSQL RESTORE DATABASE Taking 10 times longer on AWS EC2 than On-Prem VM after Lift and Shift Migration
Scenario:
My company's IT departments are in the middle of a major migration from on-prem VM Ware server hosting to AWS based server hosting.
An on-prem server running Windows 2016 DataCenter and SQL Server 2016 was replicated and migrated from on-premise to an AWS EC2. The server is 1:1, Lift & Shift from the on-premise instance.
This server is a "test/simulation" box that is used by our account management team to preform "what-if" analysis prior to hitting production with account configuration changes, for this reason the databases on this box need to be regularly refreshed at the account managers discretion.
The Data teams step a simple backup and restore jobs within the SQL Agent that restores the given database from a network available share location that hosts the most recent .bak file. Runs a few scripts to make the system non-production and brings the database online.
USE TestSIM1
BEGIN TRY
IF EXISTS (SELECT EnvironmentLockedThru
FROM [dbo].[GlobalSettings] gs
WHERE EnvironmentLockedThru IS NOT NULL
AND EnvironmentLockedThru >= GETDATE())
BEGIN
PRINT 'Environment is locked'
RAISERROR ('Environment is locked', -- Message text.
16, -- Severity.
1 -- State.
);
END
ELSE
BEGIN
--PRINT 'Environment is unlocked or Lock has expired.'
USE Master
ALTER DATABASE [TestSIM1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [TestSIM1] FROM DISK = N'\\Storage22\SQL\Sim\ProdSvr.DBName.Mirror.bak' WITH FILE = 1, MOVE N'DBName' TO N'D:\Data\TestSIM1.mdf', MOVE N'DBName_log' TO N'E:\Logs\TestSIM1_log.ldf', NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [TestSIM1] SET MULTI_USER
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
This takes anywhere from 45-60 minutes to restore the FULL backup to the Test server on a given day on premise.
After completing the migration of the server from on-prem to the AWS EC2 version of the server, it is now taking upwards of 10 hours to complete the restore.
I am hoping someone might have an explanation for this behavior and a possible resolution for this.
EC2 Specs: 8 Core 32 GB RAM <Will update the exact EC2, class when my team provides it back to me.>
SQL Server: Processes: Set to Automatic Affinity for all Processers Memory: 24 GB allocated
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

