September 19, 2019 at 1:53 pm
I've been tasked with setting up and configuring our SQL Server for Always On and have a couple questions. We have a customer database, lets say its DB1. DB1 contains call logging information for medical translation services. This database cannot be down for more that 1 minute (preferably less). Note, DB1 tends to make a lot of server calls. I already have Always On configured on both servers.
Questions
September 20, 2019 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 26, 2019 at 3:50 pm
Are we able to add this database to an AG during business hours? YES. Definitely.
Is there a performance difference between Synchronous and Async? YES. Many people claim it is not much. For me it is big enough that we cannot tolerate synchronous AG between datacenters (e.g. for DR), so we only use synchronous AG for HA, and use asynchronous for DR.
Is there anything else to do to further optimize performance when being part of an AG? Probably. I've got nothing off the top of my head, though.
Back to your first question. Fact that you asked this indicates to me that you're maybe not that confident/comfortable with all this yet. I recommend building a playground environment where you can simulate all of this. Add the DB to the AG...can you find any problems, e.g. blocking? Script the whole thing, so you can readily repeat it in prod. Failover to the secondary with data loss...what happens, and how do you deal with it? You need to know. Power off the primary node...what happens? etc.
PS my license plate is SQLDude.
September 26, 2019 at 4:55 pm
Mike, thank you for your response. I'm fairly comfortable setting up AGs. However, I've always been able to do this outside of business hours, or had permission to take a DB offline for a couple minutes to set this up. I honestly wish that the DB could afford some data loss, however, due to it being for medical purposes I have to use an Synchronous setup. I'm wondering if we shouldn't add a dedicated NIC for the listener specifically for this database? That way, the NIC can handle the traffic for SQL server and not get congested with other traffic.
As far as setting up the AG during business hours, due to having to change the connection string for the application(s), wouldn't that cause a period of data loss?
By the way, that's awesome your license plate is SQLDude!
September 26, 2019 at 7:26 pm
You're right...changing connection strings is another story. But you should be able to have the AG ready for that during business hrs, whenever you want, with no impact. I can't speak to the dedicated NIC. We used to do that, but seems like we're getting away from it lately.
September 27, 2019 at 9:28 am
In terms of a separate NIC - I can't give you a specific answer but we had something similar which you might need to look out for. The servers were running on VM's which were stored on network attached storage. When the backups ran it maxed the network connection. I didn't see any connection loss on the listener but the Windows level cluster did lose quorum sometimes and failover.
September 27, 2019 at 4:52 pm
Thanks for the heads-up on that. Once we get it squared away, I'll be sure to keep an eye out for this.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply