June 23, 2016 at 4:15 am
Hi Brandie
thank you for your responses, I'll address all of the questions here in this single post if that's ok.
Brandie Tarvin (6/22/2016)
Quick question on the backups... Does Always On take its own backups? Or does it rely on the backups I am setting up through other means?I'm worried about chain breakage and whether or not the recovery/backup solution I am designing will be interfered with by Always On, or if I can just point it to my pre-existing backup location and have it use those backups.
Following that thought... I can't seem to find much information on what the Always On backup types actually mean. The way your article phrases it, it sounds as if Always On will be doing its own backups. So if I choose Prefer Secondary, the secondary instance will be getting its databases backed up but the primary instance won't, which if I have these two in Asynch mode, might miss some data in case of failure. Could you clarify?
No, there is no automated Availability Group backup solution builtin, standard backup scripts such as Ola Hallengren's offering address the AG backup scenario.
The backup log chain can be broken if the backups are stored on the actual node where the backup was taken, storing the backups centrally makes the files available to all nodes in the AG and help to ensure none "go missing".
Even in asynch mode a log backup on the secondary will be consistent, the backup checkpoint mark will ensure consistency. Since even an asynch session will have a common LSN mark between it and the primary there is no reason to worry.
Brandie Tarvin (6/22/2016)
And now a question about the Listener... I need an IP address and a Network name, I'm assuming this is a DNS record of A Name type? Or do I need to get a C Name record for the network name?If I'm reading this correctly, an AG with 3 secondary replicas only needs 1 IP / Network name, not an IP for each replica... Yes / No / Maybe?
EDIT: Except looking at George's comments, maybe I need two IPs / Network names? I have a 4 node cluster, two nodes on Data Center A and two nodes on Data Center B. DC-A will have synchronous replicas between them and then both asynch to DC-B. Both DCs are on different subnets.
I just want to be sure I understand this before I order these items from our DNS admin.
BTW, beautifully written articles, Perry. Very detailed.
Thank you for your feedback Brandie.
The listener requires a unique IP address and a unique computername account in the domain, this computername account has a host (A record). Cname records are reference name records to an existing host record.
An AG with 3 secondary replicas and one primary (4 nodes in total) only uses 1 shared IP and computername for the WSFC client access point and 1 further shared IP and computername for the Listener client access point. Each node would have it's own IP address assigned but these are not shared.
In different subnets the computername account for WSFC and listener will have more than 1 IP which is usable, this is the only case where it differs from above.
DC-A subnet 172.10.10.0
DC-B subnet 10.10.10.0
4 node group assigned as
DC-A
Node1 172.10.10.85
Node2 172.10.10.86
WSFCName 172.10.10.100
Listener 172.10.10.101
DC-B
Node3 10.10.10.51
Node4 10.10.10.52
WSFCName 10.10.10.100
Listener 10.10.10.101
Your DNS server will have a zone for each subnet, upon failover the record and IP for the live subnet will be registered and used.
Brandie Tarvin (6/22/2016)
Another question. Does Always On require a quorum drive?Windows 2012 (clustered) can do with or without a quorum drive. But you mention quorum in your article so much, I wanted to verify whether or not you knew if it was a requirement of AG.
Yes, it is required for the WSFC which sits under the AG. Quorum is probably the biggest area where people get it wrong, which is why i looked at it in detail.
A WSFC should ideally have an odd number of nodes, with this configuration the cluster relies on Majority Node Set, a voting system between the cluster nodes (and it's massively improved in Windows 2012 R2).
If you have an even number of nodes you need to define quorum using one of the following;
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 23, 2016 at 5:26 am
Thank you, George and Perry. Appreciate the quick answers.
August 9, 2017 at 6:36 am
Hi Perry
I am reading your article and i have 3 questions.
1. On page 1 it mentions "A single database may only belong to one AlwaysOn Availability Group although you may have multiple AGs across your replicas."
So if i have a geo-cluster (Not using FCI) with 1 node each with one instance per node. Can i have the same database on node 1 with the same name as the database on node 2? So i will have two replicas (I am guessing replica means instance) with one per node. 1 Primary and 1 seconday
So if i have 1 AG is it allowed to have one database in the primary and the same in the secondary if they span the same AG?
2. Is it recommended to use the default instance name or use a named instance in AGs? I didnt read anything about this in your article
3. Do the instance names on both nodes need to be the same name? I know the collation needs to be the same and OS and etc but the instance name on both nodes are they required to be the same?
Thanks in advance
Have a good day
Kal
August 9, 2017 at 6:47 am
hurricaneDBA - Wednesday, August 9, 2017 6:36 AMHi Perry
I am reading your article and i have 3 questions.1. On page 1 it mentions "A single database may only belong to one AlwaysOn Availability Group although you may have multiple AGs across your replicas."
So if i have a geo-cluster (Not using FCI) with 1 node each with one instance per node. Can i have the same database on node 1 with the same name as the database on node 2? So i will have two replicas (I am guessing replica means instance) with one per node. 1 Primary and 1 seconday
So if i have 1 AG is it allowed to have one database in the primary and the same in the secondary if they span the same AG?
Replicas and instances are two different things. The instance is the base SQL Server install. It can be defaulted or named (to answer question #2). Either is fine. The replica fits "under" or "within" an instance. So if you're defining "nodes" as different servers or different instances, then you would have a primary replica on node 1 then a secondary replica on node 2 for the same AG, then this counts as one database per availability group.
EDIT: To be clear, if you have multiple instances on one server, each instance can have its own AG setup. Or you could (if you wanted to risk a DR incident) use all the instances on that same server to host all the replicas for one AG, but I wouldn't recommend that setup.
If you're defining "nodes" some other way, I'm not sure how to answer your question. I can tell you that if you have AG_1 and AG_2, the exact same database from InstanceA cannot be on both. But if you have a database on InstanceA and a database on InstanceB with the same name, the db on InstanceA can be on one AG and the db on InstanceB can be on the other. NOTE: I do NOT recommend a setup like this.
3. Do the instance names on both nodes need to be the same name? I know the collation needs to be the same and OS and etc but the instance name on both nodes are they required to be the same?
No. The instance names on both nodes do not need to be the same. At my workplace we have a 4 node FCI with different instance names on each server and a single AG crosses all for servers with one primary replica and three secondary replicas.
I hope this helps.
August 9, 2017 at 11:23 pm
Hi Brandie
First of all thanks for the reply
For the reply below:
Replicas and instances are two different things. The instance is the base SQL Server install. It can be defaulted or named (to answer question #2). Either is fine. The replica fits "under" or "within" an instance. So if you're defining "nodes" as different servers or different instances, then you would have a primary replica on node 1 then a secondary replica on node 2 for the same AG, then this counts as one database per availability group.
Kal - So if i define a node as a server which is what this consultant is coming to do next week at my company we will install an SQL Instance on Node 1 and another on Node 2. Then we will migrate the existing database from SQL Server 2012 to 2016 to sit in the new instance on Node 1.
My question here this new database in node 1 will it be 'inside' replica 1 and then synchronously copied over to replica2 on node 2?
EDIT: To be clear, if you have multiple instances on one server, each instance can have its own AG setup. Or you could (if you wanted to risk a DR incident) use all the instances on that same server to host all the replicas for one AG, but I wouldn't recommend that setup.
Kal - If i want to add another node later then if we setup a node as a server in the beginning we cant setup future nodes as instances. Is this reasoning correct?
If you're defining "nodes" some other way, I'm not sure how to answer your question. I can tell you that if you have AG_1 and AG_2, the exact same database from InstanceA cannot be on both. But if you have a database on InstanceA and a database on InstanceB with the same name, the db on InstanceA can be on one AG and the db on InstanceB can be on the other. NOTE: I do NOT recommend a setup like this.
Kal - The setup is having one database on node 1 in country A and the same one on node 2 in Country B and then a file share witness for multi-site cluster configuration on another location / server in Country B. I heard that this configuration is risky and not recommended. What do you think?
Kal
August 10, 2017 at 4:30 am
hurricaneDBA - Wednesday, August 9, 2017 11:23 PMHi Brandie
First of all thanks for the reply
For the reply below:Replicas and instances are two different things. The instance is the base SQL Server install. It can be defaulted or named (to answer question #2). Either is fine. The replica fits "under" or "within" an instance. So if you're defining "nodes" as different servers or different instances, then you would have a primary replica on node 1 then a secondary replica on node 2 for the same AG, then this counts as one database per availability group.
Kal - So if i define a node as a server which is what this consultant is coming to do next week at my company we will install an SQL Instance on Node 1 and another on Node 2. Then we will migrate the existing database from SQL Server 2012 to 2016 to sit in the new instance on Node 1.
My question here this new database in node 1 will it be 'inside' replica 1 and then synchronously copied over to replica2 on node 2?
Only after you setup the availability group. So in SSMS, under Databases, you'll see the migrated database. Then once the Availability Group is set up, you'll see a note after the database that says "(Synchronizing)" or "(Sychronized)" in the Database menu but also you'll see the database as part of the AG under this directory: AlwaysOn High Availability\Availability Groups\<your AG name here> (Primary)\Availability Databases.
Note that the (Primary) will be (Secondary) on node 2, but the path will essentially be the same.
EDIT: To be clear, if you have multiple instances on one server, each instance can have its own AG setup. Or you could (if you wanted to risk a DR incident) use all the instances on that same server to host all the replicas for one AG, but I wouldn't recommend that setup.
Kal - If i want to add another node later then if we setup a node as a server in the beginning we cant setup future nodes as instances. Is this reasoning correct?
If you set up an instance on a new node later on, you can add that node into existing availability groups (as node 3 to the current AG) or create a new availability group for a different database on nodes 1 or 2.
If you're defining "nodes" some other way, I'm not sure how to answer your question. I can tell you that if you have AG_1 and AG_2, the exact same database from InstanceA cannot be on both. But if you have a database on InstanceA and a database on InstanceB with the same name, the db on InstanceA can be on one AG and the db on InstanceB can be on the other. NOTE: I do NOT recommend a setup like this.
Kal - The setup is having one database on node 1 in country A and the same one on node 2 in Country B and then a file share witness for multi-site cluster configuration on another location / server in Country B. I heard that this configuration is risky and not recommended. What do you think?
I'm not sure about the risk factor. I'd need to understand who told you that, exactly what they told you and what they were thinking about when they told you. Risky could be anything between "Well Country B is in the middle of a political coup so we don't recommend storing any data there in case the country catches on fire" to "the data center (DC) in Country A is using an unstable infrastructure and they could lose power at any time." Risk can be a semantic issue or a real issue. It really depends on your company's tolerance for it and what risk is actually being discussed
In our environment, we don't have a file share witness. Our AGs are spread across 2 data centers (in two different states) with asynch setup between the DCs and a manual failover. It's similar to what you're talking about with the difference being that both DCs are in U.S. jurisdiction.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply