Welcome To TechBrothersIT: March 2019

SQL Server High Availability on Azure Tutorial

SQL Server High Availability on Azure Tutorial covers all below topics. You can learn other topics as SQL Server DBA here.

How to Configure SQL Server Availability Group in MS Azure

How to Configure SQL Server Availability Group in MS Azure explains all below steps

How to enable AlwaysOn Availability in SQL Server?
How to prepare database to be added in Availability group?
How to change SQL Server Service accounts ?
Resolved: Can't create Availability Group
Resolved: Account doesn't have permission to access end points
Resolved: Joining takes long time and then fails

How to configure SQL Server Availability Group in MS Azure

How to create SQL Server High Availability Listener in MS Azure

How to create SQL Server High Availability Listener in MS Azure explains

How to create SQL Server High Availability Listener in MS Azure?
Error: Cannot connect to High Availability Listener
Resolution: Why can't I connect to HA listener name from Secondary Replica Azure?
How to configure Static IP of HA Listener in Azure?

How to create SQL Server High Availability Listener in Microsoft Azure

How to Configure Cluster Cloud Witness Quorum in MS Azure - SQL Server High Availability on Azure

How to Configure Cluster Cloud Witness Quorum in MS Azure

How to Configure Cluster Cloud Witness Quorum in MS Azure explains all below steps

How to configure Cluster Quorum Cloud witness in MS Azure?
How to access Storage Account in MS Azure?
How to access Access key in MS Azure?
Resolved: Access key can't be valided
Resolved: An error occurred while validating access key to Azure
Resolved: An error occurred while validating access to Azure from cluster node

How to Configure Cluster Cloud Witness Quorum in MS Azure

How to Create and Configure Load Balancer for AG Listener in MS Azure - SQL Server HA on Azure

How to Create and Configure Load Balancer for AG Listener in MS Azure

How to Create and Configure Load Balancer for AG Listener in MS Azure explains all below steps.

How to create load balancer in Azure Portal?
How to Configure Internal Load Balancer in Azure?
How to Configure FrontEndIP of Load balancer in Azure?
How to configure backend pool in Load balancer in Azure?
How to configure Health Probes in Azure?
How to configure Load Balancer for Availability Group Listener in Azure?
How to Configure AG listener port in Load Balancer in Azure?
How to Configure Cluster to use Load balancer in Azure?

Script Used in this video:

$AG = Get-ClusterResource | Where-Object { $_.resourcetype -eq "SQL Server Availability Group" }

$Listener = Get-ClusterResource | Where-Object { $_.Name -like $AG.Name + "*" -and $_.resourcetype -eq "Network Name" }

$IP = Get-ClusterResource | Where-Object { $_.Name -like $AG.Name + "*" -and $_.resourcetype -eq "IP Address" }

Get-ClusterResource $IP.Name | Set-ClusterParameter -Multiple @{"ProbePort"="80";"OverrideAddressMatch"=1;}

Get-ClusterResource $Listener.Name | Set-ClusterParameter -Name "PublishPTRRecords" -Value 1

Get-ClusterResource $Listener.Name | Set-ClusterParameter -Name "HostRecordTTL" -Value 60

How to Create and Configure Load Balancer for AG Listener in MS Azure

How to Create SQL Server Availability Group Cluster in MS Azure - SQL Server on Azure Tutorial

How to Create SQL Server Availability Group Cluster in MS Azure explain all below steps

How to add Failover clustering feature of windows in Azure?
How to Prepare nodes to be added in cluster in Azure?
How to run custom cluster configuration tests in Azure?
How to Change IP address of Cluster in DNS in Azure?
How to resolve IP conflict of Cluster in Azure?
How to start cluster services on Nodes in Azure?
How to start cluster forcefully in Azure?
How to change Cluster IP in Cluster Manager in Azure?

How to create SQL Server Availability Group Cluster in MS Azure

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure explains all below steps.

1. How to create SQL Server Virtual Machine in Azure?

2. How to create new Availability Group for SQL Server Virtual Machines in Azure?

3. How to setup Static IP of SQL Server Virtual Machine in Azure?

4. How to Setup Custom DNS of a virtual Machine in Azure?

5. How to add Azure Virtual Machine to a domain?

6. How to setup memory of SQL Server in Azure?

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure

How to create Virtual Machine for Domain Controller in Azure

How to create Virtual Machine for Domain Controller in Azure video explains all below topics

How to create virtual machine in MS Azure?
How to create Windows Virtual Machine in Azure?
How to create resource group in Azure?
How to create Virtual Network in Azure?
How to create Storage Account in Azure?
How to create new disk in Azure?
How to create Availability Set in Azure?
How to open specific open ports in Azure Virtual Machine?

How to create Virtual Machine for Domain Controller in Azure

How to add second Domain Controller to an existing Domain Controller in Azure

How to add second Domain Controller to an existing Domain Controller in Azure explains below topics

How to configure DNS of Azure Virtual machine?
Resolved error: Domain Controller can't be contacted in Azure
Adding virtual Machine to existing Domain Controller to act as Domain as well
How to setup replication between two domain Controllers in Azure?
How to create user in Active Directory in Azure?

How to add second Domain Controller to an existing Domain Controller in Azure

How to create your own Domain Controller in MS Azure -SQL Server High Availability On Azure Tutorial

How to create your own Domain Controller in MS Azure video explains all below steps in detail

How to connect to virtual machine in MS Azure?
How to configure Virtual Machine's static IP in MS Azure?
How to Enable/Disable Ports and protocol in MS Azure?
How to configure your Own DNS and Active Directory in Azure?

How to create your own Domain Controller in MS Azure

Introduction To MS Azure Portal - SQL Server High Availability On Azure Tutorial

Introduction To MS Azure Portal - SQL Server High Availability On Azure Tutorial explains all below steps for SQL Server on Azure starting point

Setting up SQL Server High Availability in MS Azure Portal
How to access MS Azure Portal?
How to setup free Account of MS Azure?
Introduction to MS Azure Portal components

What is MS Azure Portal and How to Setup step by step

How to manually failover SQL Server Availability Group in Linux CentOS 7 - SQL Server Linux Tutorial

How to manually failover SQL Server Availability Group in Linux CentOS 7

How to manually failover SQL Server Availability Group in Linux CentOS 7 explains all the steps you need to perform to failover Availability Group in SQL Server when installed on Linux. You will learn below items in this video 1-- Check Constraints a) Location Constraint b) Ordering Constraint 2 -- Failover Availability Group to Specific Node 3 -- Remove Location Constraint to remove preferred node settings. Scripts used in this video: # Check current owner node of SQL Sever AG pcs status # Check Constraints pcs constraint list --full # Move SQL Server Availability Group resources from One node to another pcs resource move TBSLinuxRG-master TBSLinuxNode2 --master # Remove location constraint for successful Failover pcs constraint remove cli-prefer-TBSLinuxRG-master

How to manually failover SQL Server Availability Group in Linux CentOS 7

How to configure SQL Server High Availability Groups in Linux CentOS 7- SQL Server on Linux Tutorial

How to configure SQL Server High Availability Groups in Linux CentOS 7

How to configure SQL Server High Availability Groups in Linux CentOS 7 explains all below steps to configuration SQL Server High Availability installed on Linux. Installing SQL Server High Availability Package Installing and Enabling SQL Server Agent if its not installed and enabled already Enable SQL server High Availability on each Node Creating AG Group EndPoint and Certificates Copy Certificates of each node into all other Nodes Change ownership and group association to mysql(User) Restore each Certificate with authenticated user ( create user if you don't have already one) Grant AG Group using SSMS Create SQL Server Login and Permission for Pacemaker Create Availability Group resource in pacemaker Create IP for Listener in PackeMaker Create Listener using same IP Test Failover Script used in this video: # Install SQL Server High Availability Package sudo yum install mssql-server-ha # Enable AlwaysOn Avaiability Groups and resetart SQL Server on both nodes sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1 sudo systemctl restart mssql-server # Open SSMS and create Certificate for each node # Node Name : TBSLinuxNode1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@123'; GO CREATE CERTIFICATE TBSLinuxNode1_Cert WITH SUBJECT = 'TBSLinuxNode1 AG Certificate'; GO BACKUP CERTIFICATE TBSLinuxNode1_Cert TO FILE = '/var/opt/mssql/data/TBSLinuxNode1_Cert.cer'; GO CREATE ENDPOINT TBSSQLAG STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE TBSLinuxNode1_Cert, ROLE = ALL); GO #Now samething on Node2 (TBSLinuxNode2) CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@123'; GO CREATE CERTIFICATE TBSLinuxNode2_Cert WITH SUBJECT = 'TBSLinuxNode2 AG Certificate'; GO BACKUP CERTIFICATE TBSLinuxNode2_Cert TO FILE = '/var/opt/mssql/data/TBSLinuxNode2_Cert.cer'; GO CREATE ENDPOINT TBSSQLAG STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE TBSLinuxNode2_Cert, ROLE = ALL); GO # Copy Certificate of one node to other using SCP # on Node1 scp -r root@TBSLinuxNode1:/var/opt/mssql/data/TBSLinuxNode1_Cert.cer root@TBSLinuxNode2:/var/opt/mssql/data/TBSLinuxNode1_Cert.cer # On Node 2 scp -r root@TBSLinuxNode2:/var/opt/mssql/data/TBSLinuxNode2_Cert.cer root@TBSLinuxNode1:/var/opt/mssql/data/TBSLinuxNode2_Cert.cer # Change Ownership of certificate to mssql on each node(In my case I have only two nodes) sudo chown mssql:mssql TBSLinuxNode2_Cert.cer sudo chown mssql:mssql TBSLinuxNode1_Cert.cer # Create instance Level SQL User (TBSAGUser in my case on each node) using SSMS Open SSMS and create User # Restore certificate of Other Nodes into the present node using SSMS below: Login to TBSLinuxNode1 CREATE CERTIFICATE TBSLinuxNode2_Cert AUTHORIZATION TBSAGUser FROM FILE = '/var/opt/mssql/data/TBSLinuxNode2_Cert.cer'; # Grant permission to connec to the endpoint of TBSLinuxNode1 GRANT CONNECT ON ENDPOINT::TBSSQLAG TO TBSAGUser; # Let's do the same thing by connecting to TBSLinuxNode2 and restore TBSLinuxNode1.cert CREATE CERTIFICATE TBSLinuxNode1_Cert AUTHORIZATION TBSAGUser FROM FILE = '/var/opt/mssql/data/TBSLinuxNode1_Cert.cer'; # Grant permission to connec to the endpoint of TBSLinuxNode2 GRANT CONNECT ON ENDPOINT::TBSSQLAG TO TBSAGUser; # Create Availability Group using SSMS with Cluster type External # Create a new login or use the same login to give Pacemaker permission and provide view server permission, I will give #sysadmin to this user just for this demo # On all Nodes Edit vi /var/opt/mssql/secrets/passwd using emacs and update with user and password that you created for Pacemaker and save it TBSAGUser Pass@123 # Hold down the CTRL key and then press X, then C, to exit and save the file # setup right permission sudo chmod 400 /var/opt/mssql/secrets/passwd # Create the AG resource in the Pacemaker cluster sudo pcs resource create TBSLinuxRG ocf:mssql:ag ag_name=TBSLinuxAG meta failure-timeout=30s --master meta notify=true # Create IP resource for Listener sudo pcs resource create LinuxSQLProdList ocf:heartbeat:IPaddr2 ip=192.168.1.104 cidr_netmask=24 # Create an ordering constraint to ensure that the AG resource is up and running before the IP address. While the colocation #constraint implies an ordering constraint, this enforces it sudo pcs constraint order promote TBSLinuxRG-master then start LinuxSQLProdList

How to configure SQL Server High Availability Group in Linux CentOS 7

How to install cluster Pacemaker on linux CentOS 7 for SQL Server High Availability

How to Install cluster pacemaker on linux CentOS 7 for SQL Server High Availability video explains below Validating Nodes IP address Disable firewall and SELINUX (for Installation Only) -- Not Recommended for Prod Installing pacemaker Configuring Cluster User, i.e. hacluster Starting and Enabling Pacemaker services Adding members node to cluster Creating and starting cluster Confuring cluster resources a) Virtual IP of the cluster b) Apache Services -httpd-- Just to test the failover cluster Create Constraint of cluster resources Test Fail-over Script used in this video: #How to create Pacemaker Cluster on Linux CentoS 7 # IP Configuration verification of all possible nodes # installing httpd to verify in the end of cluster on both nodes yum install httpd -y # Disable SELINUX and Firewall vi /etc/sysconfig/selinux Change SELINUX=enforcing to Permissive and save the file on both nodes and setenforce 0 systemctl stop firewalld systemctl disable firewalld # Install Pacemaker software on both nodes yum install pacemaker pcs fence-agents-all -y # Once installed, you will see user created named hacluster - we need reset the password for this user on both nodes tail /etc/passwd --- To see if user is created passwd hacluster # Start and Enable PaceMaker Services on both nodes systemctl start pcsd systemctl enable pcsd # Add membership of the cluster using hacluster username pcs cluster auth TBSLinuxNode1 TBSLinuxNode2 -u hacluster # Create Cluster named "LinuxSQLCluster" in my case pcs cluster setup --name LinuxSQLCluster TBSLinuxNode1 TBSLinuxNode2 # View Configuration of the Cluster on both nodes Cat /etc/corosync/corosync.conf #Start the cluster pcs cluster start --all #Check the status of the cluster and enable the cluster pcs status pcs cluster enable -all # Configure the resources of the cluster a) Virtual IP of the cluster pcs resource create VirtIP IPAddr ip=192.168.1.102 cidr_netmask=24 op monitor interval=60 b) apache services (To check the failover, not necessary for SQL Server) pcs resource create Httpd apache Configuration="/etc/httpd/conf/httpd.conf" op monitor interval=60s -- # Check the status again to see if resources are configured right and you will notice both resources are created and in stopped state # Let's create Constraint and Policies on the cluster pcs constraint colocation add Httpd with VirtIP INFINITY pcs property set stonith-enabled=false pcs property set no-quorum-policy=ignore pcs property set default-resource-stickiness="INFINITY" # Check the status again to see if resources are online pcs status # Check virtual IP on active node ip a # Now let's create an HTML page to see where which node our resources are running vi /var/www/html/index.html #Type anything for example Hey I am TBSClusterNode1 (if creating on TBSLinuxNode1 in my case) etc. and save it on each node # Now let's view the page using IE or any browser # Let's failover the cluster and recheck the html page file we just created pcs cluster stop TBSLinuxNode1 (If it is running on node1, if node2, stop cluster services on that node)

How to Install cluster pacemaker on Linux CentOS 7 for SQL Server High Availability

How to Configure SQL Server Windows Authentication in Linux CentOS 7 - SQL Server on Linux Tutorial

How to Configure SQL Server Windows Authentication in Linux CentOS 7

How to Configure SQL Server Windows Authentication in Linux CentOS 7 video explains all below steps

Create Active Directory Service Account for SQL Server Setup SPN for SQL Server AG Service Account Validating Key version Number (kvno) Create MSSQLScv Keytab Setting Proper Permission of Keytab Configure SQL Server Instance to use Keytab file for kerberos Authentication Restarting SQL server and Testing Using SSMS Script Used in this Demo:

#Create SQL Server Service Account in Your Active Directory Domain UserName= mssql PasswordExpire= never # Setting ServicePrincipalName (SPN) for SQL Server Service account on Your Domain Conroller setspn -A MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433 mssql # Checking KVNO (Key Version Number) number for AD Account-usually its 2 kinit mssql@TECHBROTHERS.LOCAL kvno MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433 # Creating SQL Keytab File (AD Account with password must set this up) using ktutil sudo ktutil ktutil: addent -password -p MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433@TECHBROTHERS.LOCAL -k 3 -e aes256-cts-hmac-sha1-96 ktutil: addent -password -p MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433@TECHBROTHERS.LOCAL -k 3 -e rc4-hmac ktutil: wkt /var/opt/mssql/secrets/mssql.keytab quit # Setting up Proper permission (ownership) sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab # Configuring SQL Server to Point to KeyTab File sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab # Restarting SQL Server Services sudo systemctl restart mssql-server # Connecting to SSMS and Testing Windows Authentication

How to Configure SQL Server Windows Authentication in Linux CentOS 7

How to Install SQL Server 2017 on CentOS 7 Step by Step

How to Install SQL Server 2017 on CentOS7 Step by Step

In this video we are going to learn how to install SQL Server on Linux Step by Step. How to Configure SQL Server Repository Download SQL Server Red Hat Repository configuration File Install SQL Server using yum Command Configure SQL Server and setup SA Password Restart SQL Server services Open Firewall port to allow remote connection to SQL Server Enable and start SQL Server Agent Services Download and Install SQL server Tools to connect to SQL Server Connecting to SQL Server using SSMS 2017 with SQL Server Authentication Scripts used in this video: # Let's check if mssql-server repository already exists? sudo ls /etc/yum.repos.d | grep mssql-server.repo # If it does, we need to check contents of this repository and remove that, otherwise we will face issues during the install sudo cat/etc/yum.repos.d/mssql-server.repo sudo rm -rf /etc/yum.repos.d/mssql-server.repo # Configure and Download SQL Server Repository sudo curl -o /etc/yum.repos.d/mssql-server.repo
https://packages.microsoft.com/config... # Install SQL Server using below commands sudo yum install -y mssql-server # Configure SQL Server and Setup SA password sudo /opt/mssql/bin/mssql-conf setup # Restart SQL Server Services systemctl restart mssql-server # Configure repository and install Sqlcmd and other Tools to internally connect to newly installed SQL Server sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config... sudo yum install -y mssql-tools unixODBC-devel #Set Environment Tools to your Path Environment # Connect to SQL Server using sqlcmd tool sqlcmd -S localhost -U SA -P Pass@123 # Connect to SQL Server Remotely using SQL Server Authentication SSMS - Error and Resolution # Enable Firewall port 1433 sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent sudo firewall-cmd --reload # Enable and start SQL Server Agent services sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

How to Install SQL Server 2017 on CentOS 7

SQL Server on Linux Tutorial

How to Join CentOS 7 to an Existing Windows Domain

In this video, you will learn How to Join CentOS 7 to an Existing Windows Domain. This video is part of effort in which we finally will install SQL Server on Linux Box. In our last video, you learn how to install Linux CentOS from starch. Script used in this video:

# Installing realmd package

# yum install sssd realmd oddjob oddjob-mkhomedir adcli samba-common samba-common-tools krb5-workstation openldap-clients policycoreutils-python

Edit Host File to add DNS IP address and Server Information

# vi /etc/hosts

View File /etc/resolv.conf It should resolve Domain name and IP address Join with Windows Domain

#realm join --user=clusteradmin tbsdc.Techbrothers.local

Verify domain Join

# realm list

# id clusteradmin@Techbrothers.local

Turning off Fully Qualified Name requirement of AD user

# vi /etc/sssd/sssd.conf

systemctl restart sssd systemctl daemon-reload

Adding user to sudo or admin - Where "wheel" is group and cluster admin is active directory user

# sudo usermod -a -G wheel clusteradmin

How to Join Linux CentOS 7 to Windows Active Directory

How to Install Linux CentOS 7 for SQL Server Installation

In this video you are going to learn how to install Linux Operation System for SQL Server Installation. below are the items you are going to learn in this step by step video for CentOS installation.

Download CentOS 7 ISO (Minimul ISO)
Create Bootable Flash Drive to boot Physical Machine
Boot Server from BIOS with Bootable Flash Drive
Setting up Language in CentOS
Setting up Installation Destination Media
Network Configuration
User Configuration
Test Installation

Download CentoS 7.6 Link:

https://www.centos.org/download/

Label

SQL Server High Availability on Azure Tutorial

How to Configure SQL Server Availability Group in MS Azure

How to create SQL Server High Availability Listener in MS Azure

How to Configure Cluster Cloud Witness Quorum in MS Azure

How to Create and Configure Load Balancer for AG Listener in MS Azure

How to Create SQL Server Availability Group Cluster in MS Azure - SQL Server on Azure Tutorial

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure

How to create Virtual Machine for Domain Controller in Azure

How to add second Domain Controller to an existing Domain Controller in Azure

How to create your own Domain Controller in MS Azure -SQL Server High Availability On Azure Tutorial

Introduction To MS Azure Portal - SQL Server High Availability On Azure Tutorial

How to manually failover SQL Server Availability Group in Linux CentOS 7

How to configure SQL Server High Availability Groups in Linux CentOS 7

How to install cluster Pacemaker on linux CentOS 7 for SQL Server High Availability

How to Configure SQL Server Windows Authentication in Linux CentOS 7

How to Install SQL Server 2017 on CentOS7 Step by Step

SQL Server on Linux Tutorial

How to Join CentOS 7 to an Existing Windows Domain

How to Install Linux CentOS 7 for SQL Server Installation