Personal Notes of Edwin Eu

How to fix the suspended data movement in MS SQL Server 2016 AlwaysOn Availability Group (AG)

In Microsoft SQL Server on 08/15/2018 at 7:17 AM

How To fix the suspended data movement in MS SQL Server 2016 AlwaysOn Availability Group (AG)

  • Right click the user database
  • Select Resume Data Movement

How to simulate a suspended data movement in SQL Server 2016 AlwaysOn Availability Group (AG)

In Microsoft SQL Server on 08/15/2018 at 7:16 AM

How to simulate a suspended data movement in SQL Server 2016 AlwaysOn Availability Group (AG)

Select one of the user database.

  • First right clicked the user database –> under Available Database à Select Suspend Data Movement
  • Re-launch the Dashboard
  • The Dashboard should show a critical state and several warnings

 

 

 

 

 

 

Click on “Critical and Warning” hyperlink to drill down and see the detail

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.