January 25, 2019 at 6:49 am
Hello
The company I work for hosts hundreds of databases and uses database mirroring as a high availability solution. Microsoft's suggested replacement for mirroring in standard edition deployments is "Basic Availability Groups".
I'd appreciate hearing from anyone who's gone through the process of converting from database mirroring to BAGs. What has been your experience? Have you had any hiccups or gotchas?
Thank you
Mike G
January 25, 2019 at 6:58 am
Do you have any experience with non-basic Availability Groups, or is this your first experience of any of them?
January 25, 2019 at 7:32 am
We use High Availability Groups in a limited fashion due to it only being available in Enterprise Edition (which is about 5X the cost of SE). But in that scenario we have multiple databases per availability group and we're not as concerned with performance, database propagation (typically only one database per customer) and our impetus for adopting HAG was to use the replica for reporting purposes. In those scenarios where we use database mirroring (typically 4 CPU servers), we'll have several databases per customer and I'll notice that nearly a third of the available worker threads are being used for database mirroring.
Do BAGs use more worker threads than mirroring? Is performance better / worse / the same on servers that use BAGs vs Mirroring?
January 25, 2019 at 8:58 am
We have six on a server and that works fine.
Keep in mind that LS is a thread for restore on a schedule. You control this and with multiple databases, you spread schedules to not overwhelm worker threads.
For AGs, the threads are more like replication threads. It's a max of worker threads minus 40. With hundreds of BAGs, depending on workload, you might get thread exhaustion for other stuff. You need to read the changes on the primary, along with normal workload stuff. Then this is replayed on the secondary, though this is less an issue as it's usually a no workload server. However, if some fail over, you might have issues.
The docs I see from MS show they test a lot with 10 AGs and 100 databases, which works. I thinkthis might be a lower load than 100AGs of 1 database.
January 28, 2019 at 5:43 am
This is a good read: https://www.red-gate.com/simple-talk/sql/database-administration/alwayson-availability-groups-what-not-to-do-when-adding-databases/
It's even from Redgate :D.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply