DBA Delivers!!!

I was assigned to a project to help GIS Department to access a production SQL Server database.

The production database will offer real time mapping for the field engineers.

However, there was concerns of performance to business applications.

Then, I offered several solution that Microsoft SQL Server 2016 can offers like Database Replication or AlwaysOn technologies.

However, Database Replication or AlwaysOn are not options and to work around solutions:

GIS Department requested to create databases on a separate VM and refresh the database once a day.

As DBA, It’s not a best practice but I’m delivered to help GIS team and field engineers.

To accomplish these tasks, I created two SSIS packages that utilize File System Task and a SQL Agent jobs that contained 8 Steps like

Source Server: MS SQL Server 2012 that support a production business application

Target Server: MS SQL Server 2016

SQL Agent job  : _DailyRestore_GIS_DBs

Steps:

• Step 1 – Moving File from Source to Destination folder
• Step 2 – GIS Production DB access
• Step 3 – Restoring GIS Production Database
• Step 4 – Granting users access GIS Production DB
• Step 5 – GIS Development DB access
• Step 6 – Restoring GIS Development Database
• Step 7 – Granting users access GIS Development DB
• Step 8 – Deleting the old backup file from Source Folder.

Then, I tested the SQL Agent job and it ran successfully.