Download File

Windows Operating System : Windows Server 2012
SQL Server Software : C:\Kamran\Software\sql2016ent\Microsoft SQL Server 2016 Enterprise SP1 X64
Database Node 1 : SQLN1 - 192.168.100.148
Database Node 2 : SQLN2 - 192.168.100.151
Windows Domain : HST - 192.168.100.157
Windows Cluster : WCLSQL - 192.168.100.161
SQL Server Availability Group : SCLSQL
SQL Server Listener : LCLSQL - 192.168.100.162
Sharing Folder : \kkdomain\CLQuorum

High Level Steps

• Create VM of Both Database Machines
• Install Operating System on Both Database Machines
• Enable Networks and Assign IP Address on both Machines
• Disable Firewall on both Machines
• Join Domain with Both Database Machines
• Create User “dba” for windows Login on Domain Controller
• Mount SQL Server CD on both Database Machines
• Verify Names of Both Database Machines
• Create Folders for Database Files if Possible each Create on Separate Drives.
• Install SQL Server on both Database Machines
• Copy SSMS (Management Studio) Software on Both Database Nodes.
• Install SSMS (Management Studio) Software on Both Database Nodes.
• Install Failover Cluster on both Database Nodes from Admin users.
• Create Windows Cluster on both Database Nodes from Admin users.
• Create Shared Folder for Quorum Disk.
• Configure Quorum Disk on Windows Cluster.
• Enable Alwayson Availability group options in SQL Server Service on both Databases nodes.
• Create New Empty Database on SQL Server
• Take Database backup of New Empty Database
• Create SQL Server Availability group from Database Node

Pre-Requisites

Windows Machine Configuration

1. Install OS on Two Database Machines
2. Enable Network and Provide IP Address on Both Machines
3. Disable Firewall on Both Machines.
4. Join Both Machines on Windows Domain e.g. "HST"

Windows User Privileges of Windows Domain.

• Create Windows User "DBA" on Domain Controller and Grant Required Privileges.
• Enable Machine for Remote Desktop on user "DBA".
• Allow "DBA" User Local Admin of Computer add in Local "Administrators" Group.

SQL Server Always on Configuration

• Login with DBA User on Both Machines
• Mount SQL Server ISO on Both Machine
• Verify Name of Both Database Machine
• Create Folders on both Databases Machines if Possible create Folder on Separate Drives.
  • Create Folders
  • Database
  • Backups
  • DataFiles
  • LogFiles
  • TempFiles
• Install SQL Server on Both Machines.
• Copy SSMS Software on Both Machines C:\software\SSMS-Setup-ENU
• Install SSMS Software on Both Machines
• Login with Admin User on both Machines
• Install Failover Cluster on Both Databases Machine > Server Manager > add Roles and Features > “Failover Clustering"
• Login Database Node 1 Machine as Admin User.
• Create Windows Cluster: Server Manager > Tools > Fail Over Cluster Manager > Create Cluster
  • Note: Uncheck "All add Eligible storage to the cluster"
• Create Shared Folder for Quorum Disk
  • IP: 192.168.100.157 C:\Disks\SCLSQL-Quorum
  • Folder Name = CLQuorum
  • Permission: Read / Write and Add Security for “WCLCluster$” All Privilege
  • Sharing Folder: \kkdomain\CLQuorum
  • Login Database Node 1 Machine as Admin User.
• Configure Server Manager > Tools > Fail Over Cluster Manager > More Action > Configure Cluster Quorum Settings
  • Select Quorum Witness > Configure File Sharing Witness
  • Sharing Folder: \kkdomain\CLQuorum
  • On Error : Change Permission of Share Folder then "Re-Execute Above Steps Again for Quorum Disk Creation"
• Login Database Node 1 Machine as "dba" User.
• Enable AlwaysOn Availability Groups Feature on SQL Server Services on Both Databases nodes
  • SQL Server 2016 Configuration Manager> Run as Administrator> enter Dba User / Password > SQL Server Service > SQL SERVER (MSSQLSERVER) > Properties >Always on High Availability > Enable Alwayson Availability Groups > Apply > Ok > Ok
• Restart SQL Server Services
  • SQL Server 2016 Configuration Manager> Run as Administrator> enter Dba User / Password > SQL Server Service > SQL SERVER (MSSQLSERVER) > Right Click > Restart
• Login as "DBA" User on DB Node 1
• Login on SQL Server Management Studio
  • Open "SQL Server Management Studio” > Authentication > SQL Server Authentication > Login “sa” and enter Password > Connect.
• Create New Empty Database > Databases > New Databases > Enter Name of Database > Ok
• Create Backup on New Test Database > Add Backup Device > Ok
• Create Alwayson High Availability Group
  • Expend SQLN1 >Always on High Availability > Right Click > New Availability Group Wizard