April 2022 – Database Blog

Create Windows Failover Cluster and SQL AlwaysOn availablity group using dbatools

This post is a condensed version of the original post by Andreas Jordan. Prerequisites were taken from sqlservercentral. They have compiled a nice and extensive list of them.

With SQL Server 2012, Microsoft introduced the AlwaysOn Availability Group feature, and since then many changes and improvements have been made. This article will cover the prerequisites and steps to install AlwaysOn in a SQL 2019 environment.

Prerequisites

Windows

Do not install AlwaysOn on a domain controller
The operating system must be Windows 2012 or later
Install all available Windows hotfixes on every server including replicas
Windows Server Failure Cluster must be installed on every server (I will include powershell code to install it using dbatools module)

SQL Server

Each server must be a node in the WFSC
No replica can run Active Directory services
Each replica must run on comparable hardware that can handle identical workloads
Each instance must run the same version of SQL server, and have the same SQL server collation
The account that runs SQL Services should be a domain account

Network

It is recommended to use the same network links for communication between WFSC nodes and AlwaysOn replicas

Databases in the AG

user databases (you cannot replicate system databases)
read/write
multi-user
AUTO_CLOSE disabled
databases should be to set full recovery mode
not configured for database mirroring

Step 1: Add Windows Failover Cluster to each server

Define the variables

#Define the variables for SQLCluster
$DomainName = 'DOMAIN'
$DomainController = 'DC1.com'
$Networkshare = 'FS01.com'
$ClusterNodes = 'SQLSERVER01.com', 'SQLSERVER02.com','SQLSERVER03.com'
$ClusterName = 'SQLSERVERCL01'
$ClusterIP = '10.0.0.28'

Below code will install the failover cluster feature on all the nodes (servers/replicas whatever you want to call them)

#Install Failover cluster on all the nodes
Invoke-Command -ComputerName $ClusterNodes -ScriptBlock { Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools } | Format-Table

Move the servers to a fixed OU in Active directory (I have had to create an OU named SQL Always ON for this purpose)

Move-ADObject -Identity 'CN=SQLSERVER01,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local' 
Move-ADObject -Identity 'CN=SQLSERVER02,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local' 
Move-ADObject -Identity 'CN=SQLSERVER03,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local'

Next up is create the windows cluster, which is the basis for SQL AlwaysOn

#We do not have shared storage for the quorum in the environment
#Therefore, we will create the network share on the domain controller and authorize the computer account of the failover cluster we just created
Invoke-Command -ComputerName $DomainController -ScriptBlock { 
	New-Item -Path "C:\WindowsClusterQuorum_$using:ClusterName" -ItemType Directory | Out-Null
	New-SmbShare -Path "C:\WindowsClusterQuorum_$using:ClusterName" -Name "WindowsClusterQuorum_$using:ClusterName" | Out-Null
	Grant-SmbShareAccess -Name "WindowsClusterQuorum_$using:ClusterName" -AccountName "$using:DomainName\$using:ClusterName$" -AccessRight Full -Force | Out-Null
}
$Cluster | Set-ClusterQuorum -NodeAndFileShareMajority "\\$DomainController\WindowsClusterQuorum_$ClusterName" | Format-List

If you have reached this point, then you have completed the windows server level prerequisites.

Next step is to create the availability group. Before, we do that, we need to enable the AlwaysOn on SQL instances using the below code. Don’t you love not having to RDP into a machine and do all this manually!

#Define the variable for AlwaysOn group
$SQLServerServiceAccount = 'svc-sql-serviceaccount'
$Password = 'SecurePassword'
$BackupPath = '\FS01.com.local\SQLBackup\Temp'
$DatabaseName = 'DB01'
$AvailabilityGroupName = 'SQLAG01'
$AvailabilityGroupIP = '10.0.0.19' 

#Enable Always On on SQL instances, typically done via SQL Server Configuration Manager
Enable-DbaAgHadr -SqlInstance $SqlInstances -Force | Format-Table

Next, setup the endpoints

#Setup of the endpoints
New-DbaEndpoint -SqlInstance $SqlInstances -Name hadr_endpoint -Port 5022 | Start-DbaEndpoint | Format-Table
New-DbaLogin -SqlInstance $SqlInstances -Login "$DomainName\$SQLServerServiceAccount" | Format-Table
Invoke-DbaQuery -SqlInstance $SqlInstances -Query "GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [$DomainName\$SQLServerServiceAccount]"

Finally, we need to backup the database on primary and restore them onto each replica with norecovery in order to be able to join them to an availability group.

#Transfer databases to replica
$Database = Get-DbaDatabase -SqlInstance $SqlInstances[0] -Database $DatabaseName
$Database | Backup-DbaDatabase -Path $BackupPath -Type Database | Restore-DbaDatabase -SqlInstance $SqlInstances[1] -NoRecovery | Out-Null
$Database | Backup-DbaDatabase -Path $BackupPath -Type Log | Restore-DbaDatabase -SqlInstance $SqlInstances[1] -Continue -NoRecovery | Out-Null

Finally, create the availability group and resume the data movement

#Create the availability group
$AvailabilityGroup = New-DbaAvailabilityGroup `
		-Name $AvailabilityGroupName `
		-Database $DatabaseName `
    	-ClusterType Wsfc `
    	-Primary $SqlInstances[0] `
    	-Secondary $SqlInstances[1] `
    	-SeedingMode Automatic `
        -IPAddress $AvailabilityGroupIP `
    	-Confirm:$false
$AvailabilityGroup | Format-List

Get-DbaAgReplica -SqlInstance $SqlInstances[0] -AvailabilityGroup $AvailabilityGroupName | Format-Table
Get-DbaAgDatabase -SqlInstance $SqlInstances -AvailabilityGroup $AvailabilityGroupName -Database $DatabaseName | Format-Table

Here is the complete code in one piece for your ease of copying it for implementation on your own environment. You will have to replicate the last piece of code in order to other databases. I have yet to experiment if I can pass multiple databases as parameters to backup-dbadatabase and join them to the AG.

#Define the variables for Windows SQLCluster
$DomainName = 'DOMAIN'
$DomainController = 'DC1.com'
$Networkshare = 'FS01.com'
$ClusterNodes = 'SQLSERVER01.com', 'SQLSERVER02.com','SQLSERVER03.com'
$ClusterName = 'SQLSERVERCL01'
$ClusterIP = '10.0.0.28' 

#Install Failover cluster on all the nodes
Invoke-Command -ComputerName $ClusterNodes -ScriptBlock { Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools } | Format-Table

#Move the servers to a fixed OU in Active directory
Move-ADObject -Identity 'CN=SQLSERVER01,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local' 
Move-ADObject -Identity 'CN=SQLSERVER02,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local' 
Move-ADObject -Identity 'CN=SQLSERVER03,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local' 

#We do not have shared storage for the quorum in the environment
#Therefore, we will create the network share on the domain controller and authorize the computer account of the failover cluster we just created
Invoke-Command -ComputerName $DomainController -ScriptBlock { 
	New-Item -Path "C:\WindowsClusterQuorum_$using:ClusterName" -ItemType Directory | Out-Null
	New-SmbShare -Path "C:\WindowsClusterQuorum_$using:ClusterName" -Name "WindowsClusterQuorum_$using:ClusterName" | Out-Null
	Grant-SmbShareAccess -Name "WindowsClusterQuorum_$using:ClusterName" -AccountName "$using:DomainName\$using:ClusterName$" -AccessRight Full -Force | Out-Null
}
$Cluster | Set-ClusterQuorum -NodeAndFileShareMajority "\\$DomainController\WindowsClusterQuorum_$ClusterName" | Format-List 


#Define the variable for AlwaysOn group
$SQLServerServiceAccount = 'svc-sql-serviceaccount'
$Password = 'SecurePassword'
$BackupPath = '\FS01.com.local\Temp'
$DatabaseName = 'DB01'
$AvailabilityGroupName = 'SQLAG01'
$AvailabilityGroupIP = '10.0.0.19' 

#Enable Always On on SQL instances, typically done via SQL Server Configuration Manager
Enable-DbaAgHadr -SqlInstance $SqlInstances -Force | Format-Table 

#Setup of the endpoints
New-DbaEndpoint -SqlInstance $SqlInstances -Name hadr_endpoint -Port 5022 | Start-DbaEndpoint | Format-Table
New-DbaLogin -SqlInstance $SqlInstances -Login "$DomainName\$SQLServerServiceAccount" | Format-Table
Invoke-DbaQuery -SqlInstance $SqlInstances -Query "GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [$DomainName\$SQLServerServiceAccount]"

#Transfer databases to replica
$Database = Get-DbaDatabase -SqlInstance $SqlInstances[0] -Database $DatabaseName
$Database | Backup-DbaDatabase -Path $BackupPath -Type Database | Restore-DbaDatabase -SqlInstance $SqlInstances[1] -NoRecovery | Out-Null
$Database | Backup-DbaDatabase -Path $BackupPath -Type Log | Restore-DbaDatabase -SqlInstance $SqlInstances[1] -Continue -NoRecovery | Out-Null

#Create the availability group
$AvailabilityGroup = New-DbaAvailabilityGroup `
		-Name $AvailabilityGroupName `
		-Database $DatabaseName `
    	-ClusterType Wsfc `
    	-Primary $SqlInstances[0] `
    	-Secondary $SqlInstances[1] `
    	-SeedingMode Automatic `
        -IPAddress $AvailabilityGroupIP `
    	-Confirm:$false
$AvailabilityGroup | Format-List

Get-DbaAgReplica -SqlInstance $SqlInstances[0] -AvailabilityGroup $AvailabilityGroupName | Format-Table
Get-DbaAgDatabase -SqlInstance $SqlInstances -AvailabilityGroup $AvailabilityGroupName -Database $DatabaseName | Format-Table

Deploy an AWS EC2 instance using TerraForm

Recently, I needed to create a test VM to play around with a POC. I figured why not use TerraForm to recreate a test VM with a pre-set configuration each time rather than having to do it through GUI over multiple click of buttons.

Prerequisites:

Install Terraform on your machine (Will cover the basics on how to do it in another post). However, you have plenty of tutorials online on how to do that. Pretty straight forward.
You will need to do the following step only once. Go to AWS console, create an IAM user with access to deploy EC2 instance. Store the access_key and secret_key in a file named “credentials” without a .txt extension
Create a folder named “.aws” and copy credentials file to this newly created folder. On my machine, I created it under C:/Users/username/.aws/

Steps:

Open Git Bash, navigate to the folder where you will store the terraform code (example: C:\Users\username\Documents\Github\terraform). You can pretty store your code anywhere. Above is just my example.
Create file named variables.tf (You will define the region where you would like to deploy your EC2 instance)

#Variable for AZ
variable "availability_zone" {
    type = string
    default = "us-east-1a"
}

3. Next, create a file named main.tf (You will define the infrastructure piece here)

provider "aws" {
	region = "us-east-1"
}

#AWS Instance
resource "aws_instance" "example" {
    ami = data.aws_ami.windows.id
    instance_type = "t2.micro"
    availability_zone = var.availability_zone
  
  lifecycle {
    ignore_changes = [ami]
  }
}

#AMI Filter for Windows Server 2019 Base
data "aws_ami" "windows" {
  most_recent = true

  filter {
    name   = "name"
    values = ["Windows_Server-2019-English-Full-Base-*"]
  }

  filter {
    name   = "virtualization-type"
    values = ["hvm"]
  }

  owners = ["801119661308"] # Canonical
}

#EBS Volume and Attachment

#resource "aws_ebs_volume" "example" {
 # availability_zone = var.availability_zone
  #size              = 30
#}

#resource "aws_volume_attachment" "ebs_att" {
 # device_name = "/dev/sdh"
  #volume_id   = aws_ebs_volume.example.id
  #instance_id = aws_instance.example.id
#}

4. Once you have the .tf files in place, run the below command to install the necessary plugins. It will download the highlighted folder below.

terraform init

5. After this, run the below statement to show the deployment plan

terraform plan

6. This will allow you to review all the items that terraform is going to create for you. I did have an issue with reaching aws sometimes. Error is shown below as an example:

7. Once everything looks good, run the following to create the EC2 instance. type “yes” upon receiving a prompt

terraform apply

8. If you ever need to destroy the VM you created, run the following:

terraform destroy --target aws_instance.example

I named my VM as example in the code above. You will need to provide the name of your VM that is applicable on your end.

That is all there is to create an EC2 instance using terraform. You can also adjust the code to deploy more than one instances. The above is a very simple example.

My next idea is to use terraform to deploy EC2 instance, and then use something like dbatools or powershell to download and install SQL server on it without ever logging into the VM.

9. If you ever would like to reproduce the above result, you can save the plan using the command below:

terraform apply -out=exampleEC2.plan
#Use the below to apply the saved plan
terraform apply "exampleEC2.plan"

How do you overcome CPU bottlenecks – under utilization / over utilization?

Lets take a sample query (CTOP 10, MAXDOP 0)

SELECT * FROM Sales.SalesOrderDetail 
ORDER BY UnitPrice DESC

This query ran in parallel on my test machine (16 CPUS / 128GB).

Run the query 5 times, it takes about 15 seconds to complete.

Now i am going to run with OPTION (MAXDOP 8)

SELECT * FROM Sales.SalesOrderDetail 
ORDER BY UnitPrice 
OPTION (MAXDOP 8) DESC

It took about 15 seconds to complete (same as above).

There is usually a common misconception that when you give more CPU, a query runs faster. That is not actually true in most cases.

Please note that when your query uses 16 CPUs , it is actually doing a lot more work than you think. The work is being divided across 16 worker threads, and could end up being more work coordinating and merging results across more CPUs.

Also, please note that you will see thread 0, this does not process the query. Thread 0 is called a coordinator thread.

If you have an OLTP workload, would recommend to use MAXDOP as 1 or 2. Anything higher would slow down the queries in general. If your environment uses a warehouse environment, a higher MAXDOP is recommended.

How do you overcome Blocking in SQL Server?

Lets say that we created database on a single drive (both data and log file). Insert one Million records into the database table. Now query the table and it takes about 8 to 10 seconds to retrieve the data. It took about 2 seconds to insert the rows.
Now, lets say that we create database with data and log files on separate drives. Do the same thing, insert rows and run a select query. What do you think happens now? Now, it took longer to insert the million rows. It took about 45 seconds to insert the rows. Why do you think this ran longer? Check the top waits, i see that the top waits are CXPACKET and ASYNC_NETWORK_IO. It shows that the data is being inserted in parallel and ASYNC_NETWORK_IO shows that your app is slow in retrieving the data. Now, when you look at the waits at the first scenario, top wait is WRITELOG. It had to waits for 10 seconds out of 12 sec execution time in order to complete the insert statement. In this scenario, the slowness was caused due to latency on the disk. In a real world scenario, you would have to investigate by talking to system admin or network admin to check if a particular disk is slow (measure the latency on the disk), or check if a network adapter is slow.

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: