SQL Server DBA

SQL Server cluster

Step by step.
By
Mayukha IT Services

1
 Mayukha IT Services
H No:3-1-188/1( 1st floor),
Shobha Nilayam,
Varahi Hospital Line, Amaravathi Bar Line
Near Court ChowRasta
Karimnagar Telangana State-505001
Mobile No: +91-7569906955

Gmail: [email protected]
LinkedIn:
https://www.linkedin.com/in/mayukhaitservices/
Twitter: https://twitter.com/mayukhaITservic
Instagram: https://www.instagram.com/mayukhaitservices/
Facebook: https://www.facebook.com/mayukhaknr

2
Table of Contents

About Author ..................................................................................................................................................................2

Product Overview .......................................................................................................................................................... 3
Lab Setup........................................................................................................................................................................ 4
Cluster Network Object Name & IP .............................................................................................................................. 4
Virtual Machines Disk & Shared Storage Layout.......................................................................................................... 4
Partitions Layout.............................................................................................................................................................5
Drives Layout in My Computer ......................................................................................................................................5
OS and Network Properties........................................................................................................................................... 6
Network Properties ........................................................................................................................................................ 6
Installation of Failover Cluster Role ............................................................................................................................. 6
Configuration of SQL from Node 1 .............................................................................................................................. 10
Cluster Storage Information ........................................................................................................................................ 18
Cluster Network Information ...................................................................................................................................... 19
Assigning Permission to CNO ..................................................................................................................................... 20
Creating SQL Server Network Name ........................................................................................................................... 23
Installation of SQL Server 2016 Failover Cluster .........................................................................................................26
Installation of SSMS 2016 Management Tool ..............................................................................................................38

3
About Author

MayukhaIT is IT Consultancy with over 10 years of extensive experience working with TOP MNC
technologies like Microsoft Technologies (SQL Server, Power BI, SSIS and SSRS,
Windows Azure, PowerShell) Microsoft public clouds, and providing solutions to different local &
international Enterprise customers.

Mayukha IT has been involved in Infrastructure Designing and Implementation, Virtualization, and
Disaster Recovery. Extensive hands-on experience in Core Server Infrastructure/ DB administration,
Cloud Computing, Virtualization/ Management and Information Protection. Analysis and Support of
Microsoft Windows Server based Client/Server, SQL Always ON, Virtualization and System Center
Infrastructure Products. Mayukha IT employees have various industry certifications: MCT, MCITP,
Mongo Certified, MCPS, MCSE: Cloud Platform and Infrastructure and also providing trainings on
Microsoft Based Technologies. Mayukha IT Deals with Fresh graduates various institutions. Give
training on different technologies other than Microsoft technologies like Sybase DBA (SAP DBA),
Mongo DBA, Couchbase DBA and PostgreSQL DBA).
Mayukha IT has experience in BI Tool like SSIS(SQL Server integration Services,SSRS(SQL Server
Reporting Services and Power BI , Tibco application support, Middleware support(WebSphere, Jboss,
Tomcat and IIS(Internet information services).

4
Product Overview

SQL Server 2016 delivers mission critical performance across all workloads with in-memory built-in,
faster insights from any data with familiar tools, and a platform for hybrid cloud enabling organizations
to easily build, deploy, and manage solutions that span on-premises and cloud.

SQL Server 2016 is the biggest leap forward in the Microsoft data platform history with real-time
operational analytics, rich visualizations on mobile devices, built-in advanced analytics, new advanced
security technology, and new hybrid cloud scenarios.

SQL Server 2016 introduces many new features and enhancements:

• Enhanced in-memory performance

• New Always Encrypted technology
• Stretch Database technology
• Built-in advanced analytics
• Business insights through rich visualizations
• Simplified management
• Faster hybrid backups
• High-availability and disaster recovery scenarios

This document is purely written for everyone who wanted to setting up Microsoft SQL Server 2016 in
Cluster mode and those who are in process of setting up Microsoft SharePoint Server 2016 with backend
SQL 2016 2 Node Cluster with shared storage.

5
Lab Setup
This lab setup is based on Virtualized environment with ESX 6.0, so basic knowledge is required to setup
this lab in the same Virtualized environment. This can also be achieved by setting up the same in Hyper-
V. Table 1.1 is providing complete information about the Virtual Machines names, Network Cards,
IP/Subnet mask, and Replication Network.

Microsoft SQL Server Two Node Cluster

Sr. Server FQDN Roles Domain Network Replication Network NIC

No.
1 DC-01.saas.net DC 172.16.110.173 1
2 SP-SQL-01.saas.net SQL 172.16.110.151 192.168.200.1 2
3 SP-SQL-02.saas.net SQL 172.16.110.152 192.168.200.2 2

Cluster Network Object Name & IP

Cluster Name Cluster IP
SQLWINCLUSTER01 172.16.110.186

SQL Server Network Name & IP

Cluster Name Cluster IP
SQL-SNN 172.16.110.187

CPU and RAM of VMs

Server Name Virtual Socket/Core RAM
Per Socket
SP-SQL-01 2x2 16 GB
SP-SQL-02 2x2 16 GB

Virtual Machines Disk & Shared Storage Layout

Both SQL Server will use shared storage for Cluster, DB, Logs and Temps.

Server Name C Drive Quorum- Root- Database- Logs-Disk Temp-DB-

Disk Backup Disk Disk
SP-SQL-01 100 GB 4.90 GB 244.14 GB 255.66 GB 146.48 GB 153.34 GB
SP-SQL-02 100 GB 4.90 GB 244.14 GB 255.66 GB 146.48 GB 153.34 GB

Note: - You can use minimum storage for testing and lab purpose but for production environment you
have to look into the data growth in your organization and plan the storage requirement accordingly.

Drive 1, Drive 2 and Drive 3 are two LUNs which are physically connected with both SQL Virtual
6
Machines.

7
Partitions Layout

After mapping physical LUNs you can see the RAW partitions in each SQL VM disk management and
will be able to create your own partition as per the requirements. Same partition table will be appeared
in 2nd SQL Server VM.

Drives Layout in My Computer

8
OS and Network Properties
This step by step is Using Windows Server 2012R2 Datacenter 64bit and SQL Server 2016 Enterprise
Edition 64bit.

Network Properties

Installation of Failover Cluster Role

9
We need to install Failover Cluster Roles in each server of SQL.

10
11
12
13
Configuration of SQL from Node 1
After successful completion of Failover Cluster Feature you need to start configuring Failover Cluster
from Node 1.

14
15
16
17
At this point validation wizard will perform all tests for network, storage and nodes which required to
establish a cluster.

18
19
You can see all the test results are successfully passed and validated. You can further explore this
validation report by clicking on each category to view more details of each component.

You need to provide Cluster Name and the IP address.

You use an access point to administer a failover cluster or to communicate with a service or application

20
in the cluster.

21
22
You can view this report in details and can save for reference.

At this point you can see the cluster settings, disk, network and IP.

Cluster Storage Information

23
Cluster Network Information

24
Assigning Permission to CNO
The SQL Server Database Engine service is dependent on the Network Name resource. A failure of the Network
Name will result in the SQL Server Resource not coming online.

When the Windows Failover Cluster (WFC) is initially configured a Cluster Name object (CNO) will be created.
The CNO is visible as a computer object in your Activity Directory Users and Computer snap-in (dsa.msc). By
default, the CNO will be created in the Computers container and granted specific permissions:

When the SQL Server Network Name is first brought online during the FCI installation process, the CNO identity
is used to create the VCO (as long as the VCO doesn’t already exist). If the required permissions are not granted
to the CNO, the creation of the VCO will fail and so will your SQL Server FCI installation.

*Note: The Create Computer objects right only applies to Domain Functional Levels above Windows Server 2003.
For Windows Server 2003 the required privilege is “Add Workstations to the Domain”.

We must grant the permissions "Read all properties" and "Create Computer objects" to the CNO via the
container. Here’s an example of granting the required permissions for demonstration purposes:

1. Open the Active Directory Users and Computers Snap-in (dsa.msc).

2. Locate “Computers” container:

3. Make sure "Advanced Features" is selected:

25
4. Open the properties of the container and click the "Security" tab. Click "Add" and add the CNO. Make sure to
select “Computers” option in the “Object Types” window:

5. Select the cluster name which was created before in Active Directory Computer Objects, and click OK.

6. Click "Advanced", highlight the CNO, and click "Edit":

26
7. Make sure "Read all properties" and "Create Computer objects" are checked. Click OK until you’re back to the
AD Users and Computer window:

27
Creating SQL Server Network Name
We can also “Pre-Stage” the VCO, which is useful in situations where the Domain Administrator does not allow
the CNO “Read All Properties” and “Create computer Objects” permissions:

1. Ensure that you are logged in as a user that has permissions to create computer objects in the domain.

2. Open the Active Directory Users and Computers Snap-in (dsa.msc).

3. Select View -> Advanced Features.

4. Right click the OU/Container you want the VCO to reside in and click “New” -> “Computer”

We will create a computer object and assign required permission to this object which will later use in SQL Server
2016 installation as SQL Server Network Name.

5. Provide any name you like for this computer object.

6. Right click on the on the VCO you just created and select “Properties”. Click the security tab and then click
“Add”

28
29
7. Enter the CNO (Make sure to select “Computers” option in the “Object Types” window) and click “OK”.

8. Highlight the CNO, check the following permissions, and click “OK”.

Read
Allowed To Authenticate
Change Password
Receive As
Reset Password
Send As
Validate write To DNS Host Name
Validate Write To Service Principle Name
Read Account Restrictions
Write Account Restrictions
Read DNS Host Name Attributes
Read MS-TS-GatewayAccess
Read Personal Information
Read Public Information

30
*Note: You can replace step #8 by giving the CNO “Full Control” over the VCO

31
Installation of SQL Server 2016 Failover Cluster
At this point you need to move forward for the installation of SQL Server 2016 in failover mode. So let’s
start installing SQL Server 2016 in Failover Mode. Start from Node 1.

32
33
34
35
36
37
38
39
40
41
42
At this point we have successfully installed first Node of SQL Server 2016 in Failover Cluster Mode by
using shared storage for Database logs and temp.

With the SQL Server 2016 release Microsoft split out the installation of SQL Server Management Studio
from the main installation of SQL Server. So we need to install the management tool separately from the
same setup screen from where we started the installation steps.

The option on the SQL Server 2016 Installation Center takes you to a web download. You can also
download the SQL Server at Download SQL Server Management Studio (SSMS). After the download has
completed you can run the installation standalone without a connection to the Internet. You can see the
new SSMS 2016.

Let’s go through the installation of SSMS 2016☺

43
Installation of SSMS 2016 Management Tool

Click Install to continue.

44
Click Restart your SQL Node 1.

After restart open SQL Server 2016 Management with Server Network Name we configure during SQL
Installation in Active Directory.

You will connect with SNN which is created in AD and later used in SQL Server 2016 installation as shown
45
above.

46
We successfully connect with Node 1 from SQL Server Network Name.

Let’s move to the installation on Node 2.

47
48
I am going to Skip the same steps we performed above like Key, agreement and updates you can follow
from above.

At this screen you can see the Node 2 is going to be added in existing cluster of SQL Servers.

49
50
51
 At this point we have successfully complete Two Node SQL Server 2016 Installation. SQL Server
Management SSMS 2016 process of installation will remain same on Node 2. You can follow steps above
as we have installed SSMS 2016 on Node 1.

This Two Node Cluster will help you to setup in your Lab environment as well as production environment
with desired storage and computing requirements. This is also suitable for SharePoint 2016
implementation and deployments.

You can see in the last snapshot that both Nodes are connected with SQL Server Network Name.

I hope you will enjoy the smooth process of this Step by Step Two Node SQL Server 2016 Deployment.

Thanks & Regards.

Mayukha IT Services
H No: 3-1-188/1( 1st floor),Christian Colony
Shobha Nilayam,
Varahi hospital Line
Near Court ChowRasta
Karimnagar-505001
52
Mobile No: +91-7569906955

53