November 12, 2014 at 12:18 pm
So we're getting ready to install SQL 2012 and I've been brushing up on failover clusters (FCI) and availability groups (AG). Suddenly I find myself making an assumption and I would like someone to either verify I'm right or point out where I'm wrong.
1) Both FCI and AG require windows clustering in order to work.
2) AG does not require FCI and FCI does not require AG in order to work properly.
3) (The one I'm not sure about) SQL cannot have non-clustered instances on a windows clustered resource???
I'm getting this last bit from MSSQLTips[/url]:
SQL Server 2012 and all previous versions of SQL Server require databases be created on clustered resources. Internal drives or drives which are not part of the cluster group cannot hold user databases.
The thing is, this statement seems in direct contradiction to an article Perry Whittle published on this site[/url]. I've worked with SQL clusters on a clustered windows OS. I've worked with standalone (nonclustered) SQL instances on standalone servers. I've never worked with a nonclustered SQL Instance on a clustered windows OS. So let me ask this question... When the node, upon which a nonclustered SQL instance lives, fails, what happens to the nonclustered instance?
Insight is appreciated.
November 12, 2014 at 2:04 pm
Your quote from the MSSQLTips.com article is referring to creating databases on an FCI, which in that regard you cannot create the database on local disk, it has to be the shared disk within a cluster resource. That statement does not apply in the AlwaysOn Available Group part of things since you can use local storage.
When the node, upon which a nonclustered SQL instance lives, fails, what happens to the nonclustered instance?
Although Perry might answer this with a bit more detail, I'll give it a go...When you are working with AlwaysOn Availability Groups it is basically still mirroring with the cluster holding your network name of the listener. The SQL Server instances are considered stand-alone, in that regards.
I will say that PASS Summit 2014 aired a quick start to AlwaysOn that I found very useful to fill in these types of gaps, check it out while it is here.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
November 12, 2014 at 3:15 pm
Hey Brandie!! Please do make sure you REALLY know what your objectives/needs are and that you are doing things correctly to get you there. There are SOOO many caveats, limitations, gotchas, provisos, etc with AGs!! The one that shocks most people is that reading from a secondary modifies your PRIMARY data structures under the covers (leading to extra data/tlog activity and most likely massive page fragmentation for the vast majority out there that default all their indexes).
I have seen and heard of many bad implementations of AlwaysOn. I know you are a sharp cookie and should be able to get the job done though!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 12, 2014 at 3:44 pm
I've got limited knowledge here, but AFAIK, the Windows Cluster moves over those items that are clustered in Windows, which could be SQL Server. If you had an instance of SQL Server running on a Windows Cluster, but the SQL wasn't in an FCI or AG, then the instance is gone when things fail over.
A Windows Cluster node isn't a duplicate of another node. It provides a layer on which you can have the smooth startup and transfer of services that are cluster, and only those services. It also provides a central, network moveable address clients can use without knowing which node they are connecting to.
November 13, 2014 at 5:27 am
TheSQLGuru (11/12/2014)
Hey Brandie!! Please do make sure you REALLY know what your objectives/needs are and that you are doing things correctly to get you there. There are SOOO many caveats, limitations, gotchas, provisos, etc with AGs!!
Hence the reasons I am asking the questions before we do any implementation. @=)
Actually, corporate is dictating a lot of these standards, but we are the first group in the business to implement and I may be called on to so some of this installation. So I'm trying to learn what I can before I actually have to do it.
EDIT:
The one that shocks most people is that reading from a secondary modifies your PRIMARY data structures under the covers (leading to extra data/tlog activity and most likely massive page fragmentation for the vast majority out there that default all their indexes).
Count me in as one of the people who is shocked. Are there links I can reference to find out why this sort of thing happens?
November 13, 2014 at 5:30 am
Steve Jones - SSC Editor (11/12/2014)
I've got limited knowledge here, but AFAIK, the Windows Cluster moves over those items that are clustered in Windows, which could be SQL Server. If you had an instance of SQL Server running on a Windows Cluster, but the SQL wasn't in an FCI or AG, then the instance is gone when things fail over.
Thank you for that verification. I appreciate it.
November 13, 2014 at 5:33 am
Shawn Melton (11/12/2014)
I will say that PASS Summit 2014 aired a quick start to AlwaysOn that I found very useful to fill in these types of gaps, check it out while it is here.
Thanks for the link, Shawn. I'll watch it today.
November 13, 2014 at 8:24 am
Brandie Tarvin (11/12/2014)
1) Both FCI and AG require windows clustering in order to work.
Yes, both technologies require a functional Windows Server Failover Cluster.
Note that you may also use an FCI as an AlwaysOn group replica, the replicas do not all have to be stand alone. See my guide at this link[/url] for Combining FCIs with AO groups.
Brandie Tarvin (11/12/2014)
2) AG does not require FCI and FCI does not require AG in order to work properly.
Correct, the 2 are totally independent except that as i said above an FCI can be used as a replica in an AlwaysOn group configuration.
Brandie Tarvin (11/12/2014)
3) (The one I'm not sure about) SQL cannot have non-clustered instances on a windows clustered resource???
This is not correct. By default all sql server instances which are replicas in an AlwaysOn group would be stand alone and not clustered. This removes the storage single point of failure associated with clustered instances and removes the need for cross site storage replication. However, as i said above if you really want an FCI in your AO group you can do this. It does have restrictions
Brandie Tarvin (11/12/2014)
I'm getting this last bit from MSSQLTips[/url]:SQL Server 2012 and all previous versions of SQL Server require databases be created on clustered resources. Internal drives or drives which are not part of the cluster group cannot hold user databases.
Only where the instance is an FCI, a stand alone instance does not use clustered drives, although it can still use shared drives attached from a SAN. These just wouldnt be attached to all cluster nodes.
Brandie Tarvin (11/12/2014)
So let me ask this question... When the node, upon which a nonclustered SQL instance lives, fails, what happens to the nonclustered instance?Insight is appreciated.
Its down, if it's the Primary in an AO group and you have auto failover it will move to a partner node. This obviously only applies to databases on that instance that are in an AO group, any others would be down until the node is back online.
Check my stairway series on SSC starting at this link[/url]
Shawn Melton (11/12/2014)
Although Perry might answer this with a bit more detail,
Here i am, right on cue 🙂
Shawn Melton (11/12/2014)
When you are working with AlwaysOn Availability Groups it is basically still mirroring with the cluster holding your network name of the listener. The SQL Server instances are considered stand-alone, in that regards.
Not quite, even without a listener the AlwaysOn group will create a clustered role which will have one cluster resource of type "SQL Server Availability Group". If you create a listener then the clustered role will also include a Virtual Networkname resource and an IP address resource.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 13, 2014 at 9:17 am
Brandie Tarvin (11/12/2014)
I'm getting this last bit from MSSQLTips:this statement seems in direct contradiction to an article Perry Whittle published on this site.
If you scroll down the MSSQL Tips article you'll see my comments regarding some of the myths, there were some inconsistencies
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 13, 2014 at 10:56 am
Perry Whittle (11/13/2014)
Brandie Tarvin (11/12/2014)
1) Both FCI and AG require windows clustering in order to work.Yes, both technologies require a functional Windows Server Failover Cluster.
Note that you may also use an FCI as an AlwaysOn group replica, the replicas do not all have to be stand alone. See my guide at this link[/url] for Combining FCIs with AO groups.
Perry, that's actually the post that started my questions. @=)
BTW, I posted a comment question to that article yesterday. Which is sort of what you brought up in this post, anyway. Thank you for the info.
November 13, 2014 at 1:08 pm
don't forget to check out my stairway series here on SSC too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 13, 2014 at 5:02 pm
http://technet.microsoft.com/en-us/library/ff878253(v=sql.110).aspx
Search for whitepaper: Offloading Read-Only Workloads to Secondary Replicas
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 14, 2014 at 8:37 am
Perry Whittle (11/13/2014)
don't forget to check out my stairway series here on SSC too
Definitely do this and give us feedback.
We've got more coming as Perry writes and we edit as quickly as we can.
November 14, 2014 at 11:44 am
Kevin, thanks for the links. Now I have more questions.
Is Primary Replica the database of origin or just the first replica that was set up?
TheSQLGuru (11/13/2014)
http://technet.microsoft.com/en-us/library/ff878253(v=sql.110).aspx
Per the above link:
As soon as a readable secondary replica joins the availability group, the secondary replica can start accepting connections to its secondary databases. However, if any active transactions exist on a primary database, row versions will not be fully available immediately on the corresponding secondary database. Any active transactions that existed on the primary replica when the secondary replica was configured must be committed or rolled back. Until this process completes, the transaction isolation level mapping on the secondary database is incomplete and queries are temporarily blocked.
Say I have an OLTP db mapped to this secondary replica. Does this mean in periods of high traffic, the replica is unreadable?
As far as the second article goes, I'm going to have to re-read it a few times. The first two points are clear, but then it gets a little muddy for me.
November 14, 2014 at 3:01 pm
Brandie Tarvin (11/14/2014)
Kevin, thanks for the links. Now I have more questions.Is Primary Replica the database of origin or just the first replica that was set up?
TheSQLGuru (11/13/2014)
http://technet.microsoft.com/en-us/library/ff878253(v=sql.110).aspxPer the above link:
As soon as a readable secondary replica joins the availability group, the secondary replica can start accepting connections to its secondary databases. However, if any active transactions exist on a primary database, row versions will not be fully available immediately on the corresponding secondary database. Any active transactions that existed on the primary replica when the secondary replica was configured must be committed or rolled back. Until this process completes, the transaction isolation level mapping on the secondary database is incomplete and queries are temporarily blocked.
Say I have an OLTP db mapped to this secondary replica. Does this mean in periods of high traffic, the replica is unreadable?
As far as the second article goes, I'm going to have to re-read it a few times. The first two points are clear, but then it gets a little muddy for me.
Yes, primary is the GOLD MASTER VERSION of the database. You can only have one of those, although it can move locations on failover. That is where transactional DML activity gets "mirrored" over to 1 or more secondary copies of said database.
Your big blurb about blocked queries on secondary should be just when you first join the secondary to the primary. It is easy to set up a
test to see the effect of this. High traffic can run you into other problems, the most obvious being load and latency of secondary replay, especially if you are synchronous. Another gotcha that has caught a few companies out there is the SINGLE THREADED nature of the log read/send/replay, meaning you can get a max of somewhere around 50MB/sec of throughput, give or take. Some systems out there run into that wall, esp. if blobs or fat data is in play. Fortunately no one actually builds high-volume systems with bad design and blobs in it!! :Whistling:
I am not surprised you need multiple readings!!! This is VERY complicated stuff - which is partly why there are so many "features"! 🙂 At least you are taking the time to dig in some. Many do not, and it is never pretty...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply