We are maintaining the UAT environment refresh from the Production environment using the availability method i.e. LOG SHIPPING.
Log shipping will enable us to maintain a clone SIMPra DB over the UAT environment from the PROD syncing every 15 mins.
Purpose
SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations
Operating Mode
We are using Restore mode – i.e. the target database where Log shipping is being done is not accessible for querying.
Frequency: 15 Minutes
There will be data loss of 15 minutes in case of an incident.
Note: With Log Shipping, however, there is no Listener support: the secondary server stays offline (in stand-by or restoring mode) until roles are switched manually. Therefore, after a failover you must update the application’s connection string—or change a DNS alias—because a virtual address does not automatically follow the active server.
Servers involved:
| Purpose | Environment | IP | Host Name | DB Version |
| DB Server | Production | 10.132.197.66 | INNOSIMDB1V0002 | MS SQL Server 2019 Standard Edition |
| DB Server | UAT | 10.132.129.194 | INNOSIDBT1V0001 | MS SQL Server 2019 Standard Edition |
Log Shipping works on three steps:
Step 1 : Job to take Production Database Backup
- Configured on 10.132.196.67- Production Primary Server
- Job name :LSBackup_SIMPra
- Backup Folder: \\ INNOSIMDB1V0002\E:\backup shared folder
- Database Name: SIMPra
Step 2 : Job to Copy T-Log Backups from Production Primary Server to UAT Server (configured on 10.132.196.61)
- Configured on 10.132.196.61 (UAT Server)
- Job name :LSCopy_INNOSIMDB1V0002_SIMPra
- Copy Folder : \\INNOSIDBTV0001\D:\backup shared folder
Step 3:Job to restore the copied T-log backups to the secondary DB i.e. SIMPraLS
h. Configured on 10.132.196.61 (UAT Server)
i. Job name: LSRestore_INNOSIMDB1V0002_SIMPra
j. Database name: SIMPraLS
To utilise the clone SIMPRALS DB over the UAT environment for the read/write operations.
Step1: Disable the Backup job over the SIMPra PROD server ( 10.132.197.66 ). Un-check the ‘enabled’ button.
Step 2: Disable the Copy job over the SIMPra UATserver ( 10.132.129.194 ). Un-check the ‘enabled’ button.
Step 3: Disable the Restore job over the SIMPra UATserver (10.132.129.194 ). Un-check the ‘enabled’ button.
Step 4:
- Connect 10.132.129.194 via SSMS.
- Connect master DB.
- Run below script:
RESTORE DATABASE SIMPraLS WITH RECOVERY;
We will have SIMPraLS DB recovered for R/W operations.
Key point:
We need to restore a FULL copy backup of SIMPra DB to the UAT environment as SIMPRALS in ‘no recovery’ mode before re-enabling the Log Shipping jobs.
Copy-FULL backup can be taken by the following script:
USE [master]
BACKUP DATABASE [SIMPra] TO DISK=N’F:\Microsoft SQL Server\Backup\FULL\FULLbackupfile.bak’ WITH STATS = 5;
Copy-FULL backup can be restored by the following script in no-recovery mode:
Make sure to copy the backup file from the PROD environment to the UAT environment.
USE [master]
RESTORE DATABASE [SIMPraLS] FROM DISK = N’UAT_server_path\FULLbackupfile.bak’ WITH NORECOVERY, NOUNLOAD, STATS = 5,
FREQUENCY of UAT refresh process: 15 days
We will be following the UAT refresh process and recover our SIMPraLS DB for R/W operations every 15 days.
END OF DOCUMENT