Category: sql server

Find and Drop a user across multiple database servers

If you ever wished to find a user and drop them from multiple servers. Here is a one-liner:

Get-DbaRegServer -SqlInstance SQLSERVER01 -Group 'Production' | Get-DbaDbUser -User User1 | Remove-DbaDbUser -Force
Get-DbaRegServer -SqlInstance SQLSERVER01 -Group 'Production' | Get-DbaLogin -Login User1 | Remove-DbaLogin

The first line of the code remove user from all the databases. The second line drops the login from each SQL instance.

This is especially useful when you need to drop a user when they leave the company.

Get Backup History

DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'mydb'
-- Get Backup History
SELECT TOP (30) s.database_name, s.name, s.description
,m.physical_device_name
,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize
,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
,s.backup_start_date
,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
,CASE s.[type] WHEN 'D'
THEN 'Full'
WHEN 'I'
THEN 'Differential'
WHEN 'L'
THEN 'Transaction Log'
END AS BackupType
,s.server_name
,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date DESC
,backup_finish_date

Log shipping with Standby/ReadOnly

Run the below on the Primary

BACKUP DATABASE LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_full1.bak'
WITH INIT, FORMAT,COMPRESSION

INSERT INTO t1(Id)
VALUES (1)
BACKUP LOG LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log1.trn'
WITH INIT, COMPRESSION

INSERT INTO t1(Id)
VALUES (2)
BACKUP DATABASE LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log2.trn'
WITH INIT, COMPRESSION

INSERT INTO t1(Id)
VALUES (3)
BACKUP DATABASE LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log3.trn'
WITH INIT, COMPRESSION

Run the below on the Secondary to restore the database in standby/read-only mode


ALTER DATABASE LogRestoreDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_Full.bak'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO

--Check the table t1
-- SELECT * from t1;

USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log1.trn'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO

--Check the table t1
-- SELECT * from t1;

USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log2.trn'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO

--Check the table t1
-- SELECT * from t1;

USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log3.trn'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO

--Check the table t1
-- SELECT * from t1;

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

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.

Wait statistics

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.