Personal Notes of Edwin Eu

DBA Delivers

In Microsoft SQL Server on 03/22/2018 at 2:33 PM

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: