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.

I’m continued to learn, enhance my DBA skills and knowledge on Microsoft SQL Server AlwaysOn technology on New Windows Server 2016 and MS SQL Server 2016.

There are many  an Availability Group Architecture terms that I needed to get familiars like:

• Availability Group –
  • AG is a container that represents a unit of fail-over
  • AG can have one or more user databases
  • When the availability  Group fails over from one replica to another replica, all the user databases will fail over.
• Primary Replica
  • Microsoft SQL Server instance that is currently hosting the AG (Availability Group).
  • The AG is active and hosting a user database that can be modified.
  • There is only one primary instance at any give point in time.
• Secondary Replica
  • SQL Server 2012 supports four secondary replicas
  • SQL Server 2014- 2016 supports eight secondary replicas
  • is hosting a copy user database that cannot be modified.
• Failover partner
  • SQL Server 2012 – 2014 support one failover partner
  • SQL Server 2016 – support two failover partners
  • Is the secondary replica that configured to be auto failover destination when the Primary Replica goes wrong.
• Synchronous vs Asynchronous
  • Synchronous is designed to setup Microsoft SQL Server AlwaysOn on Premises
  • Asynchronous is designed to setup Microsoft SQL Serer AlwaysOn  outside Premises.

And, I used VMWare technology  to setup my environments.    First, I’m going to preparing three (3) Virtual Machine in VMWare

• • Computer Name: INDSQLP1N1
    • Processor: Intel @R Core (TM) i7-6700 HQ CPU @2.6GHz
    • Memory: 8 GB
    • O/S: Window Server 2016
    • Installed Microsoft SQL Server 2016 SP1
  • Computer Name: INDSQLP2N2
    • Processor: Intel @R Core (TM) i7-6700 HQ CPU @2.6GHz
    • Memory: 8 GB
    • O/S: Window Server 2016
    • Installed Microsoft SQL Server 2016 SP1
  • Computer Name: INDSQLP3N
    • Processor: Intel @R Core (TM) i7-6700 HQ CPU @2.6GHz
    • Memory: 8 GB
    • O/S: Window Server 2016
    • Installed Microsoft SQL Server 2016 Sp1
• Second, Installed and setup Window Server 2016 Standard edition on each VM.
• Third, I joined the Virtual Machine into my Domain Controller.
• Forth, I am going to configure Window Server Failover Clustering with Node majority.
  • There are no share disk storage
• Forth, I installed and setup Microsoft SQL Server 2016 SP1 on each Virtual Machine.
• Next, I turned on the AlwaysOn High Availability features.
• And, make sure the user database must be using full recovery mode.
• A full database backup must have been performed.
• Finally,  I  started setting up and configure the new Availability Group that called “365_AvailabilityGroup”

Read the rest of this entry »