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

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.

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.

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:

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

Problem

I’ve been to restore a Microsoft SQL Server 2008 R2 production database to a development/test server in order to troubleshot Microsoft Dynamics AX problems.

Currently, I’m doing this process automatic with T-SQL scripts.

Solution

First,  I prepared the backup inventory and took or move the backup files into Development or Test server.

Second, I restored the full backup, then applied the differential.

Last, I applied the transaction log backup.

/***************************************************************************************************************************************

DATE : 07/23/2013

AUTHOR : Edwin Eu

COMPANY : Confidential

CLUSTER NODE1 : Confidential

SERVER INSTANCE NAME : Confidential\T1

DATABASE NAME : AXEU_PRD_20130723

DATABASE VERSION : Microsoft SQL Server 2008 R2 with Service Pack 1 Cumulative Update 2

***************************************************************************************************************************************

PURPOSES :

TO RESTORE BELGIUM DYNAMICS AX production database  (AXPRD) into DYNAMICS TEST ENVIRONMENT Confidential\T1 .

Restore AXPRD to a point in time to different server

Point in Time 07/23/2013 03:30 AM Central Time.

BACKUP SCHEDULE:

Full backup schedule on Every Sunday at 1:00 AM

Differential backup schedule on Monday, Tuesday, Wednesday, Thursday, Friday and Saturday at 1:00 AM

Log backup schedule Daily every one (1) hour

******************************************************************************************************************************************

NOTES:

SQL SERVER – FIX – ERROR : Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.)

http://blog.sqlauthority.com/2011/04/13/sql-server-fix-error-msg-3201-level-16-cannot-open-backup-device-operating-system-error-5access-is-denied/

*****************************************************************************************************************************************/

RESTORE FILELISTONLY FROM
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_1of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_2of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_3of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_4of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_5of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_6of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_7of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_8of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_9of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_10of10_20130721.bak’

GO

RESTORE DATABASE[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_1of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_2of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_3of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_4of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_5of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_6of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_7of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_8of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_9of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_10of10_20130721.bak’

WITH  MOVE’ZZZ_AX_PRDZ_AX50SP1_TEST’TO’R:\MP_T1SQLDATA1\T1SQLDATA1\ZZZ_AX_PRD_PRD.MDF’,
MOVE’ZZZ_AX_PRDZ_AX50SP1_TEST_Log’TO’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\ZZZ_AX_PRD_PRD_LOG\ZZZ_AX_PRD_PRD.LDF’,
STANDBY =’R:\MP_T1SQLDATA1\T1SQLDATA1\ZZZ_AX_PRD_PRD_20130723.STN’

GO

—— Restore a Differential Backup
RESTORE DATABASE  [ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_backup_2013_07_22_180003_2144074.bak’
WITH STANDBY  =’R:\MP_T1SQLDATA1\T1SQLDATA1\ZZZ_AX_PRD_PRD_20130723.STN’
GO

—— Restore a Transaction Log Backup
—— Transaction log dated 7/22/2013 7:00 PM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723000000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,   NOUNLOAD,  STATS= 10
GO

—— Transaction log dated 7/22/2013 8:00 PM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723010000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10
GO

—— Transaction log dated 7/22/2013 9:00 PM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723020000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD, STATS= 10

GO

—-  Transaction Log dated 7/22/2013 10:00 PM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723030000.trn’WITH  FILE= 1,
STANDBY=N’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10

GO

—- Transaction Log dated 7/22/2013 11:00 PM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723040000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10

GO

—- Transaction Log dated 7/23/2013 12:00 AM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_Log\ZZZ_AX_PRD_PRD_20130723050000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10
GO

—- Transaction Log dated 7/23/2013 01:00 AM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_Log\ZZZ_AX_PRD_PRD_20130723060000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10
GO

— Transaction Log dated 7/23/2013 02:00 AM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723070001.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10
GO

—- Transaction Log dated 7/23/2013 03:00 AM

RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_Log\ZZZ_AX_PRD_PRD_20130723080001.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10
GO

—- Transaction Log dated 7/23/2013 03:30 AM

RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723090000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10,
STOPAT=N’2013-07 23T03:30:00′

GO

—– The Final Step to bring database online.
RESTORE DATABASE[ZZZ_AX_PRD_PRD_20130723]
WITH RECOVERY

I am working on a client who running Microsoft SQL Server 2008 R2 Express that implented in Retail stores.

The database architecture setup was very unique due to the nature of business.

Each retail store has itown SQL Server 2008 R2 Express that connected to POS machine.

However, the database recovery model was setup to be SIMPLE.  (It’s not a best practice) in case a disaster.

In addition, the log file was named with extension MDF instead of LDF.

One day, the database was corrupted and down for 4 days.  The company DBAs was unable to recover the database.

I was assigned to recovery those SQL database.

Here are my steps to recover the corrupted database.

—- Step Create the exact database name

—- Step 1 Create the exact database name

CREATE DATABASE AxRetailPOS ON  PRIMARY

( NAME = N’AxRetailPOS’,

    FILENAME = N’D:\SQL_DATA\DATA\AxRetailPOS.mdf’,

    SIZE= 8MB, MAXSIZE = UNLIMITED, FILEGROWTH = 16MB)

LOG ON

( NAME = N’AxRetailPOS_log.mdf’,

 FILENAME = N’E:\SQL_LOG\AxRetailPOS_log.mdf’,

SIZE = 8MB, MAXSIZE = UNLIMITED, FILEGROWTH = 16MB)

GO

—- Step 2

Stop the SQL Server Services

Move the original MDL file from older server (or location) to new server (or location).

By replacing just create MDF file.  Then, delete the LDF file of the new server just created.

— Step 3

Started the SQL Server services.

— Step 4

Make sure system table of master database allow to update the value

USE MASTER

GO

 exec SP_CONFIGURE ‘allow updates’, 1

RECONFIGURE WITH  OVERRIDE

GO

—Step 5 – Change database mode to emergency mode

 ALTER DATABASE AxRetailPOS SET  EMERGENCY

GO

SELECT * FROM  SYSDATABASES

 WHERE NAME = ‘AxRetailPOS’

 GO

— Update only one row in sysdatabase

BEGIN

  UPDATE sysdatabases

  SET status = 32768

  WHERE name = ‘AxRetailPOS’

 COMMIT TRAN

END

select * from  sysdatabases

 go

— Find out the database id

select name, filename from sysdatabases

 where dbid = ‘7’

 go

 ALTER DATABASE AxRetailPOS Rebuild Log

ON (NAME = N’AxRetailPOS_Log’,

   FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.SBHDB01\MSSQL\DATA\AxRetailPOS.ldf’)

GO

ALTER DATABASE AxRetailPOS Set  online

go

 DBCC rebuild_log (‘AxRetailPOS’, ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SBHDB01\MSSQL\DATA\AxRetailPOS_log.ldf’)

 GO

Microsoft SQL Server 2012 finally release. There are three main version:

• Microsoft SQL Server 2012 Enterprise Edition
• Microsoft SQL Server 2012 Business Intelligence Edition
• Microsoft SQL Server 2012 Standard Edition

For more information, please go to http://www.microsoft.com/sqlserver/en/us/editions.aspx

Microsoft SQL Server 2012 innstallation step by step

 The “CREATE DATABASE <Database Name>” will impact to database performance issue  if it’s not utilize properly.

There are many articles has discussed and posted.

I would recommend to issues a complete a statement instead of CREATE DATABASE <Database Name> :

CREATE DATABASE BearCreek ONPRIMARY

(NAME =N’BearCreek_Data’,FILENAME=N’M:\SQL_Data\BearCreek.mdf’,

SIZE= 8MB, MAXSIZE =UNLIMITED, FILEGROWTH = 16MB),

FILEGROUP FG1

(NAME =N’BearCreek_Data2′,FILENAME=N’M:\SQL_Data\BearCreek.ndf’,

SIZE= 8MB, MAXSIZE =UNLIMITED, FILEGROWTH = 16GB),

FILEGROUP BearCreekDocuments

CONTAINSFILESTREAMDEFAULT

(NAME =N’BearCreekDocuments’,FILENAME=N’M:\SQL_Data\BearCreekDocuments’)

LOG ON

(NAME =N’BearCreek_Log’,FILENAME=‘L:\SQL_Log\BearCreek.ldf’,

SIZE= 8MB, MAXSIZE =UNLIMITED, FILEGROWTH = 16MB)

GO

ALTER DATABASE BearCreek

MODIFY FILEGROUP FG1

DEFAULT

GO

I wanted to keep two good references handy in case needed.

First resources is SQL Authority with Pinal Dave.

http://blog.sqlauthority.com/2007/05/15/sql-server-dbcc-commands-list-documented-and-undocumented/

The second resources is MSDN library

http://msdn.microsoft.com/en-us/library/ms188796