3

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
3

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
|improve this answer|||||
  • In your first script, you are performing BackupFull/Log and RestoreFull/Log as we can see in the first 4 lines, right? In the current script, you are creating just the Availability Groups. – Danilo Braga Aug 31 '16 at 19:35
  • 2
    I'm using direct seeding here. I've also done this with full backup and restore in T-SQL. For some reason the PowerShell cmdlets were just adding overhead in this case. – AlexLostDba Aug 31 '16 at 20:02
  • AlexLostDba, How did you go with the Basic availability performance? We have 20+ databases with basic availability enabled but having some performance issues. Was direct seeding helpful? Cheers, – hikmatune Jul 4 '18 at 21:27
  • Ultimately we ended up pulling all the availability groups and setting up a cluster. Setting up the AGs using T-SQL did seem alleviate some of our performance issues, but in the end it felt like using that many AGs was still adding overhead. We decided that if we really needed the uptime of availability groups we would be better off purchasing an enterprise license, but for this project we didn't think it was necessary. – AlexLostDba Jul 5 '18 at 21:38
-1


Are you creating one Availability Groups per database? Why don't you create just one to control all databases in the same AG?
I believe the resource issue is related with Backup/Restore between both servers. Have you checked the network during this process?

|improve this answer|||||
  • 7
    Because Standard Edition only supports single-db AGs. – Aaron Bertrand Aug 31 '16 at 1:47
  • Make sense now! Thx – Danilo Braga Aug 31 '16 at 2:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.