Sql-server – Multiple Basic Availability Groups and Performance – SQL Server

availability-groupssql-server-2016standard-edition

I'm migrating some of my company's SQL Server boxes to a new SQL 2016 setup. We're currently using database mirroring on SQL 2012. One of the instances I'm migrating currently has about 60 databases and despite the occasional hiccup, mirroring is working fine for us. And for various reasons this instance is running SQL Standard edition. As mirroring is being deprecated, I'm keen to try Basic Availability Groups.

However, I'm running into real performance issues putting together a lab with only 20 databases. During the initial setup, simply creating a new availability group will exhaust the 472 thread limit for Availability Groups (Max Threads – 40). On the primary server I'll see something like this:

time   active_workers idle_workers worker_limit worker_start_success 
--------------------- ------------ ------------ --------------------
12:01  473            0            472          true
12:02  473            0            472          false
... multiple rows elided
12:05  472            451          472          false
12:06  22             16           472          true

Simply trying to view the dashboard for one of the availability groups will peg the CPU for several minutes. This isn't a super powerful VM, but VMWare tells me it has allocated 3014 MHz to it.

This is also without instrumenting any database operations or I/O on the database side.

Are there performance considerations I'm missing? At this point, I feel like Basic Availability Groups won't work for my situation, but I want to be sure I'm not overlooking something. I've leaned heavily on these articles/blog posts:

Finally, this is the PowerShell script I've been using to automate the creation of availability groups. I may be doing something incorrect here:

Backup-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile -ServerInstance "vm-sqllab1"  
Backup-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance "vm-sqllab1" -BackupAction 'Log'  

Restore-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile -ServerInstance "vm-sqllab2" -NoRecovery  
Restore-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance "vm-sqllab2" -RestoreAction 'Log' -NORECOVERY

$PrimaryReplica = New-SqlAvailabilityReplica -Name "vm-sqllab1" -EndpointUrl "TCP://vm-sqllab1.domain:5022" -FailoverMode "Manual" -AvailabilityMode "AsynchronousCommit" -AsTemplate -Version 13
$SecondaryReplica = New-SqlAvailabilityReplica -Name "vm-sqllab2" -EndpointUrl "TCP://vm-sqllab2.domain:5022" -FailoverMode "Manual" -AvailabilityMode "AsynchronousCommit" -AsTemplate -Version 13

$AgName = "vm-ag-" + $DatabaseName
New-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\vm-sqllab1\Default\" -Name $AgName -AvailabilityReplica ($PrimaryReplica, $SecondaryReplica)  -BasicAvailabilityGroup -Database @($DatabaseName)
Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\vm-sqllab2\Default -Name $AgName
Add-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\vm-sqllab2\Default\AvailabilityGroups\$AgName -Database $DatabaseName

Best Answer

There are two parts to this. First, make sure you install CU1. Notably it contains a fix for

The second part is to use T-SQL instead of PowerShell. When I do it this way, the availability groups get created much, much faster and I'm not seeing any of the CPU/thread issues I complained about above. This is the code I'm using to create the Availability Groups with direct seeding. I've adapted it from this blog post:

On the primary:

CREATE AVAILABILITY GROUP [vm-ag-TestDb21] 
WITH (BASIC,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)  
FOR DATABASE [TestDb21]
REPLICA ON 
N'vm-sqllab1' WITH (ENDPOINT_URL = N'TCP://vm-sqllab1.domain:5022',  
    FAILOVER_MODE = MANUAL,  
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),   
    SEEDING_MODE = AUTOMATIC),   
N'vm-sqllab2' WITH (ENDPOINT_URL = N'TCP://vm-sqllab2.domain:5022',   
    FAILOVER_MODE = MANUAL,   
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),   
    SEEDING_MODE = AUTOMATIC)   

GO

And on the replica:

ALTER AVAILABILITY GROUP [vm-ag-TestDb21] JOIN
GO
ALTER AVAILABILITY GROUP [vm-ag-TestDb21] GRANT CREATE ANY DATABASE
GO
Related Question