Personal Notes of Edwin Eu

Archive for the ‘Microsoft SQL Server’ Category

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.

Setting up Microsoft SQL Server 2016 AlwaysOn

In Microsoft SQL Server on 03/05/2018 at 2:18 PM

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 »

Common SQL Server Wait Types

In Microsoft SQL Server on 12/01/2017 at 9:32 AM

I found a solid article that discussed a common SQL wait type that provided by several SQL MVPs

https://logicalread.com/common-sql-server-wait-types/#.WiF8YnmWyUk

 

 

DBA – How to add additional article into existing Publication. 

In Microsoft SQL Server on 11/07/2017 at 8:42 AM

How to add additional article into existing Publication.

Method 1: To add an article after a publication is created

  1. On the Articles page of the Publication Properties – <Publication> dialog box, clear the Show only checked objects in the list check box. This allows you to see the unpublished objects in the publication database.
  2. Select the check box next to each article you want to add.
  3. Click OK.

https://technet.microsoft.com/en-us/library/ms152571(v=sql.90).aspx

https://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/

 

Method 2:  To add an article after a publication is created through Replication Transact-SQL Programming.

EXEC sp_addarticle @publication = N’RMS_Publication’,
@article = N’lwcase’, @source_owner = N’dbo’, @source_object = N’lwcase’,
@type = N’logbased’, @description  = null, @creation_script = null,
@pre_creation_cmd = N’drop’,
@identityrangemanagementoption = N’manual’, @destination_table = N’lwcase’,
@destination_owner = N’dbo’, @vertical_partition = N’false’,
@ins_cmd = N’CALL sp_MSins_dbolwcase’, @del_cmd = N’CALL sp_MSdel_dbolwcase’, @upd_cmd = N’SCALL sp_MSupd_dbolwcase’
GO

DBA Notes – Transactional Database Replication

In Microsoft SQL Server on 11/06/2017 at 3:09 PM

I was assigned a database replication projects to setup database replication that supported ETL projects for a Fire and Policy department.

The Source and Target database are located in the same domain.

I used my previous experiences and I know how to setup database replication. My previous working experiences were setting – up a Transactional replication to replication wind farm database.

It’s important to understand the database replication terminology terms like:

    • Publisher
      • The MS SQL Server Database instance that provided data available to Subscribers. The Articles like Tables, and Views which are to be replicated are defined at the publisher.
    • Distributor 
      • The MS SQL Server Database instance that distributes data from one or more Publisher to one or more Subscribers
    • Subscriber
      • The MS SQL Server Database that going to receive data from Publishers.
    • Pull Distribution
      • Setup and located in Subscriber MS SQL Server
    • Push Distribution
      • Setup  and located in Publisher MS SQL Server.
    • Articles
      • An article is a SQL Server object like Table, View, Function, stored procedure, etc.
    • The Snapshot Agent
      • Distributes data exactly as of a point in time.
      • Prepares snapshot files containing schema and data of publication articles
      • Stores the files in the snapshot folder
    • The Distribution Agent
    • The Log Reader Agent

My DBA Story – October 2017

In Microsoft SQL Server on 10/26/2017 at 2:47 PM

My DBA Story.

As a production DBA, I wanted to make sure all production sql servers are backup properly.

And it’s amazing to me that when I assigned to Audit production Microsoft SQL Servers environment.

And I found many production databases did not have a properly backup plans as show below:

 

 

 

 

 

 

 

 

Even the database backup is known by many. Un-fortune, one did not appreciate that how important database backups

When databases failed or disaster.

So, I started implementing the backup and recovery plan like:

  • Setup and Created an operator
  • Setup database mail.
  • Full Back up is scheduled to run on Sunday
  • Differential Backup is scheduled to run from Monday through Saturday
  • Log Backup is scheduled to run a daily every hour
  • Added Alerts notifications

 

Azure SQL 2016 Clustering

In Microsoft SQL Server on 07/14/2017 at 1:50 PM

On my continue IT learning and passion.  I have setup two nodes Microsoft SQL Server 2016 clustering on Azure.

There are many preparations that must be done like:

  • Setup Azure Networking
  • Setup Windows Server 2012 R2
  • Setup Domain Controller
  • Setup Window Server Clustering.
  • Setup Microsoft SQL Server 2016 Clustering
  • Setup SQL Service accounts, sql ports, firewall, etc
  • Setup the storage like
    • msdt
    • quarum
    • SQLDATA1
    • SQLMASTER1
    • SQLTEMPDB1
    • SQLTRANSLOG1
    • SQLBACKUP1

 

 

 

 

 

 

 

 

The Domain Controller computer is called IND-DC01

 

 

 

 

 

 

 

 

 

The two nodes computers are called  INDAXSQLT1N1 and INDAXSQLT1N2 as shown picture below:

 

 

 

 

 

 

 

 

 

Then, I started the Microsoft SQL Server 2016 setup on Node1 and Node2.

Run Setup.exe as administrator.

Agree to license terms

Choose Instance Features

Database Engine Services (SQL Server Replication, Full-Text Search and Data Quality Services are automatically selected)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Uncheck DHCP and enter the virtual IP address for this instance.  You may need to enter the Subnet Mask if this is the very first installation of sql on this box.  Leave the Network name as is.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Enter an account and password for each service.  Leave startup type as manual because it is a cluster.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Adding the second nodes

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MS SQL Server AlwaysOn

In Microsoft SQL Server on 01/19/2017 at 11:05 AM

I continued to explorer my skills and knowledge on MS SQL Server 2016 on AlwaysOn.

I have been tested and  setup three (3) nodes clustering that running on VMWare environment on MS Window Server 2012 R2:

  • Node 1 – CRMSQLQ1N2 has installed, setup and configure MS SQL Server 2016.
  • Node 2 – CRMSQLQ1N3 has installed, setup and configure MS SQL Server 2016
  • Node 3 – CRMSQLQ1N4 has installed, setup and configure MS SQL Server 2016

On this example, on node 1 CRMSQLQ1N2  I will create several user databases.Then, turned on the SQL Server AlwaysOn features.

After completed setup, and configure SQL 2016 AlwaysOn.  The CRMSQLQ1N2 node1 should replicate those user databases into Node 2 and 3.

sql2016-alwayson sql2016-alwayson_page5 sql2016-alwayson_page4 sql2016-alwayson_page3 sql2016-alwayson_page2

Cluster resource SQL Server Agent in clustered role ‘ZZZCRMSQLP1VS1’ failed

In Microsoft SQL Server on 12/13/2016 at 8:42 AM

Problems: Cluster resource SQL Server Agent in clustered role ‘ZZZCRMSQLP1VS1’ failed

Resolutions:

  • Requesting a temp local admin right on both cluster nodes
  • RPD into both WIndow Server 2012 cluster nodes
  • Opened the Failover CLuster Manager
  • Trying to bright online MS SQL Agent, but the MS SQL Agent Failed to  run and be online.
  • Then, I opened up SQL Server Configuration Manager and make sure all the SQL Server services are running.  Immediate, I found SQL Server Agent stopped running.  Then, I tried to restart the SQL Agent services but the SQL Agent services failed.
  • Next, I investigated the WIndow Server Cluster event and logs and the cluster event and logs’ showed:

Cluster resource ‘SQL Server Agent (ZZZCRMSQLP1VS1)’ of type ‘SQL Server Agent’ in clustered role ‘ZZZCRMSQLP1VS1’ failed.

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

The Cluster service failed to bring clustered role ‘ZZZCRMSQLP1VS1’ completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.

I recalled that one of DBA team just updated he SQL Agent service account passwords,  could the password updated caused this errors.

So, I requested the SQL Agent service account password from Server team and re-enter the password.  Then, bring the SQL Agent resource online.  Amazing to me that the SQL Agent resources are back online and running.  It’ safe my day!!!

Moving  or Fail-over the SQL Cluster Role from Node 1 to Node 2. And make sure the SQL Server cluster instance and Server Agent resource were online, up and running.

Manually fail-over the SQL Cluster Roles from Node2 to Node1 and make sure the SQL Server cluster instance and resources were online,  up and running.

sqlcluster_issues

SQL Server 2012 AlwaysOn

In Microsoft SQL Server on 10/11/2014 at 2:16 PM

Starting Microsoft SQL Server 2012 version, the SQL Server clustering is called SQL Server 2012 AlwaysOn.

Recently, I have setup, configured, and installed SQL Server 2012 Clustering in VMware environment.

These are five nodes clustering  that consisted of:

SQL-Node1 

  • O/S                        : Window Server 2012 R2
  • RAM                      : 8 GM
  • 2 Network Cards  : Private LAN  +  Public LAN

SQL-Node2

  • O/S                        : Window Server 2012 R2
  • RAM                      : 8 GM
  • 2 Network Cards  : Private LAN  +  Public LAN

SQL-Node3

  • O/S                        : Window Server 2012 R2
  • RAM                      : 8 GM
  • 2 Network Cards  : Private LAN  +  Public LAN

SQL-Node4

  • O/S                        : Window Server 2012 R2
  • RAM                      : 8 GM
  • 2 Network Cards  : Private LAN  +  Public LAN

SQL-Node5

  • O/S                        : Window Server 2012 R2
  • RAM                      : 8 GM
  • 2 Network Cards  : Private LAN + Public LAN

Next, configured and setup the Microsoft Failover Cluster as show below:

AlwaysOn5Nodes_Setup1

 

 

 

 

 

 

Run the Cluster Failover test and it should completed as showed below;

AlwaysOn5Nodes_Setup2