'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.

Screenshot of Restore progress

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