Tag: dbatools

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

Get status of SQL server services on one or more servers remotely

'SQLSERVER01','SQLSERVER02','SQLSERVER03'| get-dbaservice -Type Engine, Agent | Out-GridView

This will provide output as shown below:

The great thing about this approach is that, I can swap out the command get-dbaservice with restart-dbaservice. This will restart the SQL services remotely without having to RDP into a machine.

'SQLSERVER01','SQLSERVER02','SQLSERVER03'| Restart-dbaservice -Type Engine, Agent | Out-GridView

If you don’t have a monitoring tool, this is a great way to check if the services are running or not on a specific SQL server when you are troubleshooting a connection issue.

Update SQL service account on multiple SQL servers using dbatools

Typically, I use the following statement whenever i want to update a SQL service account

Get-DbaService sql1 -Type Engine -Instance MSSQLSERVER | Update-DbaServiceAccount -Username 'MyDomain\sqluser1'

Configures SQL Server engine service on the machine sql1 to run under MyDomain\sqluser1. Will request user to input the account password.

Here is a simple one liner to update SQL service account on multiple servers:

'SQLSERVER01','SQLSERVER02','SQLSERVER03'| Get-DbaService -Type Engine,Agent -Instance MSSQLSERVER | Update-DbaServiceAccount -Username 'MyDomain\sqluser1'

The type is inputs for both Engine and Agent. Therefore, both services will be set to run as that domain user.

Copy permissions from one user to another using dbatools

Often, there is a request to replicate permission to a new hire or team member using an existing user. Typically, I prefer to use Active directory groups manage permissions for team members.

However, if your SQL instance has native SQL user accounts, you cant easily maintain similar permissions among team members. Here is a way to do that using dbatools. Credit goes to Claudio Silva 

$sqlInstance = "SQLSERVER01"
$existingUser = "<srcUser>"
$newUser = "<newUser>"

$ExportedUser = Export-DbaUser -SqlInstance $sqlInstance -User $existingUser -PassThru

$NewUserPermissions = $ExportedUser -replace $($existingUser -replace '\\', '\\'), $newUser

# Copy the permission to the clipboard. Paste on your query editor and paste there.
$newUserPermissions | Set-Clipboard

Backup and Restore a SQL database with a two line script

Here is a simple method to backup and restore a database from Server A to Server B using dbatools. The script will backup DB1 from Server A and restore the backup onto Server B as DB2.

Please note that the SQL service account on both servers need to have access to the network shared folder in order for this to work.

Backup script:

Backup-DbaDatabase -sqlinstance ServerA -database DB1 -path \\NetworkShare\SQLBackup\ServerA -copyonly -compressbackup

Restore script:

Restore-DbaDatabase -SqlInstance ServerB -Path "\\NetworkShare\SQLBackup\ServerA\DB1_202201261123.bak" -UseDestinationDefaultDirectories -DatabaseName DB2 -ReplaceDbNameInFile

However, if you ever need to simply copy a database DB1 from Server A to Server B. You can use the below script:

$ss = 'ServerA'
$ds = 'ServerB'

# Copy one database
Copy-DbaDatabase -Source $ss -Destination $ds -BackupRestore -Database DB1 -SharedPath \\NetworkShare\SQLBackup\ServerA

If you need to do a full-on migration of databases from Server A to Server B, here is the script:

$ss = 'ServerA'
$ds = 'ServerB'

# Copy all databases
Copy-DbaDatabase -Source $ss -Destination $ds -BackupRestore -Alldatabases -SharedPath \\NetworkShare\SQLBackup\ServerA