June 26, 2015 at 6:45 am
We are planning to upgrade our production servers from mirroring to alwayson.
Our current mirror setup gives the advantage that it can failover a single database.
To have a similar setup in alwayson we are probably going to create an availability group per database.
Does anyone see any disadvantage in this except for the extra initial configuration work?
Thanks in advance
June 26, 2015 at 7:39 am
Compare to Mirroring, always on will have more flexibility. I would prefer always on. e.g. multiple database, multiple secondary and much more.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 26, 2015 at 12:25 pm
I think the real disadvantages are the requirements for AlwaysOn that mirroring doesn't have. Mainly:
- Requires all nodes be a part of the same domain
- Requires AD
- Requires all replicas involved with AlwaysOn be a part of a Windows Server Failover cluster
- Additional IPs/computer accounts required for WSFC and AO
- Certain ALTER DATABASE commands cannot run because of the AG like setting service broker and RCSI
Joie Andrew
"Since 1982"
June 26, 2015 at 3:47 pm
Joie Andrew (6/26/2015)
I think the real disadvantages are the requirements for AlwaysOn that mirroring doesn't have. Mainly:- Requires all nodes be a part of the same domain
- Requires AD
- Requires all replicas involved with AlwaysOn be a part of a Windows Server Failover cluster
- Additional IPs/computer accounts required for WSFC and AO
- Certain ALTER DATABASE commands cannot run because of the AG like setting service broker and RCSI
I'm intrigued by the RCSI part, as I know I've enabled RCSI on databases in an AG (and readable secondaries always use it). Is there perhaps some incorrect documentation from MS about that? A quick google didn't turn up anything, but I'd be interested if there were.
Cheers!
June 26, 2015 at 4:31 pm
I'm intrigued by the RCSI part, as I know I've enabled RCSI on databases in an AG (and readable secondaries always use it). Is there perhaps some incorrect documentation from MS about that? A quick google didn't turn up anything, but I'd be interested if there were.
Cheers!
It is not that you cannot do it for a db in an AG, it is just that you cannot enable it for a database while it is in an AG. RCSI can only be enabled when a single connection to the db is made so things like AGs and mirroring cause that operation to fail.
From BOL:
To set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no active connections to the database except for the connection executing the ALTER DATABASE command. However, the database does not have to be in single-user mode. You cannot change the state of this option when the database is OFFLINE.
ALTER DATABASE SET Options (Transact-SQL)
Here is more on the error you will get if you try:
How to Enable RCSI for a Database with Database Mirroring
Joie Andrew
"Since 1982"
June 26, 2015 at 4:46 pm
Sure, I know it has to take the X lock on the DB, so it's unlikely you'd get it to run on a busy DB, but it's not just impossible. I've enabled RCSI several times for databases already participating in AGs with no errors.
That article is talking about mirroring, not AGs, so that's probably a restriction of mirroring and not of AGs. Either that, or all my AGs are magical, which I doubt 🙂
EDIT: If I get the time, I'll put up a demo showing it, for sanity's sake.
June 26, 2015 at 5:30 pm
Yeah, I realize the article is mentioning mirroring but the principals are the same. AGs have connections to the database open pretty much all the time and the only connection that can exist for setting up RCSI is the one executing the ALTER DATABASE command.
If you can set it while a db is participating in an AG I would love to see a step-by-step of how you did it, along with your configuration. I have a CRM environment over here that at times I forget to setup RCSI for and each time I have to drop the db from the AG, make the change and then re-add it. It would be a lot easier to forego those extra steps if possible.
Joie Andrew
"Since 1982"
June 27, 2015 at 6:31 am
I'll need to test to confirm but if you suspend data movement at the primary you should be able to set the RCSI option on the primary database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 29, 2015 at 1:57 am
Thanks for the interesting replies.
My initial question was though if there would be any disadvantage if i create an availabilitygroup per database.
So in the end i would have the same amount of availabilitygroups as databases.
This would allow me to failover a single database, but i wonder if this has an impact on performance or anything else.
June 29, 2015 at 5:25 am
All AGs on the instance will use the same mirror endpoint but then the same is true if all the dbs are in the same group, they'll still use the same network connection. This could quite possibly be a bottleneck. Typically you would identify groups of databases that would be made highly available and may even depend upon each other.
For every AG you create there will be a cluster role in the WSFC and a set of resources within that role, if you're creating lots of groups just be aware of this. If you create a listener per group the number of VIPs and VNNs would grow rapidly and this is directly related to the number of cluster resources.
Whether a single group with lots of dbs or multiple groups with fewer dbs, worker thread exhaustion can be an issue, check this link for more info
To sum up, for me the main areas of concern are network and worker thread issues, these are present whether you have one group or lots of groups, so in answer to your original question would be a no and a yes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 29, 2015 at 5:31 am
Thanks Perry
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply