May 27, 2011 at 7:49 am
I am trying to make sure my understanding is correct with the so called active/active and active/passive.
1) If i have a active/passive cluster can I use the passive server for logshipping or mirroring?
2) If not then would that be the benefit of having an active/active cluster?
3) Also with active/active clustering can have example: 2dbs 1db on node1 to failover to node2 and db2 on node2 to failover to node1?
May 27, 2011 at 9:42 am
Active/Passive, all you can legally do is use the passive as a failover if the active goes down (intentionally or by failing). That's why you don't have to pay licensing fees for the passive node.
Active/Active is usually more about spreading the workload than anything else. With active/active, half your queries get resolved by one server, half by another. Works well, if you have your database federated properly, or if all you're doing is OLAP.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 27, 2011 at 10:07 am
Hey GSquared
I thought spreading out the work load is called load balancing and SQL server does not support true load balancing?
May 27, 2011 at 10:17 am
You can two independant databases that were on one instance seperated onto two on a cluster. Both instances get the benefits of high availability but dont impact each other in terms of performance.
May 27, 2011 at 10:48 am
First, just as an FYI, Microsoft (at least the SQL Server High Availability team) is trying to get away from the "active/active" / "active/passive" nomenclature - they're trying to call it single-instance or multiple-instance. (Not catching on too quickly... even their tests still refer to it this way.)
In "active/passive", you have a single instance of SQL Server installed on the cluster. It runs on one node, but can fail over to any other node that it has been installed on. This is the "single-instance" referred to above. Any other setup gets into the "multiple-instance".
The "passive" node is not actually running the sql server instance, so it does not have the databases for that instance connected to it. In the event of a failover, the passive node becomes the active node for that sql server instance - that instance of sql starts up, connects to the database files (performs crash recovery and other normal startup processes). Thus, the passive node cannot be used for log shipping/mirroring. However, the node that is active can be involved in log shipping and/or mirroring. (Note that there is a startup cost associated with a failover - it is NOT immediate. At my last job, it was taking about 2 minutes for our relatively small databases to become active upon a failover.)
In "active/active", you have 2 instances of SQL Server installed on the cluster (each with their own drives, allocated memory/processors, etc.). The implication is that there are 2 nodes ("active/active/active" implies 3 nodes). There is a separate instance of SQL Server actively running on each of the different "active" nodes, but they can also fail over to any other node. Including any node that is already active with other instance(s). Each installed SQL Server instance will have it's own IP address and computer name. Multiple-instance clustering is NOT any form of load sharing/balancing.
Benefit of "active/active" -
1. Simply put, reducing hardware/software costs. You can get one souped up cluster (lots of processors, memory), and by installing multiple SQL Server instances, replace multiple other SQL Server installations. Say you have 5 sql servers now - they could all go onto this one server (different instances), and you reduce both the separate hardware requirements, and the windows/sql licensing for them.
2. Simplified installation/maintenance.
Drawback: you need to ensure that each node has enough resources to handle the entire cluster (all instances). An "active/active" cluster cannot have multiple instances of sql server connected to the same database files.
This MS link might be helpful to you.
Does this answer it all? Anything else?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 27, 2011 at 10:59 am
WayneS
So if active/active does not have the same resource how is the sql server able to failover with ur copy of production db?
May 27, 2011 at 11:36 am
bopeavy,
The database exists on a shared resource (disk), however, only one node "has" the resource at a time, when the failover occurs the disk goes with the name and IP address to the passive node which then becomes the active node for that instance.
A lot of this has to do with licensing, in an active/passive model you only pay for the one SQL license because as long as you don't failover for more than 30-days the second node is not required to be license. In an active/active model you are actually running two seperately licensed SQL servers, each on its own node with the other as a failover. In this case each instance is assigned its own disks and those disks ALWAYS go with that server, they cannot be shared between instances.
You don't need to do log shipping or replication or mirroring because the data in on the shared disk that is carried from node to node. The upside is that it is ALWAYS at the same name and IP address so your apps don't need to know anything about the individual nodes that are involved JUST the ip or virtual name.
CEWII
May 27, 2011 at 11:40 am
Elliott
With that said if node one is active and node two is active and node 2 is the fail over can I use the node 2 for log shipping to 3rd off site server?
May 27, 2011 at 11:57 am
bopeavy (5/27/2011)
WayneSSo if active/active does not have the same resource how is the sql server able to failover with ur copy of production db?
When an instance of SQL Server is installed on a cluster, you need to install it on each of the nodes that will be participating FOR THAT INSTANCE. For that instance, all of the resources needed (SQL, disks, name, IP, databases, etc.) are combined into a cluster resource group. When the active node fails over, another node (that participates for that instance) will start up their SQL Server instance and restart the instance, bringing all of the resources online (if possible - it might have failed because a resource is not available, such as the SAN drives).
You don't think of it in terms of the instance being on ONE computer; you think of it in terms of WHICH of these computers is currently running this instance.
Any instance can log ship / mirror to any other instance/server, assuming the proper edition requirements are met.
An instance CAN NOT log ship/mirror from an active node to a passive node.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 27, 2011 at 12:01 pm
WayneS
Thanks, I was trying to set up where I have 3 sql servers on standard edition so that I have a failover and can log shipfrom node 2 with out effecting the node 1 or prod enviroment.
May 27, 2011 at 12:05 pm
bopeavy (5/27/2011)
ElliottWith that said if node one is active and node two is active and node 2 is the fail over can I use the node 2 for log shipping to 3rd off site server?
Active /Active is a bit different. In Active/Active you have two different clusters in the Microsoft world that are on the same two boxes.
Think of it this way. Two clusters and two instances that will be named ClusterA and ClusterB. ClusterA (first instance) is active on Primary NodeA and is passive on secondary NodeB. ClusterB (second instance) is active on NodeB (primary for ClusterB) and passive on NodeA(secondary for ClusterB). If ClusterB fails, it will failover from NodeB to NodeA. At this point you will have both clusters running on NodeA and both will show NodeB as the secondary/passive node.
Each of these instances have dedicated share resources available only to that cluster/instance.
As for logshipping - you can logship either one of those instances to the other instance or even to a third offsite instance.
Does that help?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 27, 2011 at 12:12 pm
GSquared (5/27/2011)
Active/Passive, all you can legally do is use the passive as a failover if the active goes down (intentionally or by failing). That's why you don't have to pay licensing fees for the passive node.Active/Active is usually more about spreading the workload than anything else. With active/active, half your queries get resolved by one server, half by another. Works well, if you have your database federated properly, or if all you're doing is OLAP.
Each active node in a cluster must be licensed. Thus, in an active/active cluster you need to have each server licensed. As Gus mentions, one use of Active/Active could be with federated databases. Most implementations of Active/Active though is to utilize the server power and not let a server stand idle (A/P is somewhat costly and inefficient). With A/A clusters you have separate instances installed and typically you have different databases and applications hitting the different instances. By using A/A you utilize both servers and provide HA should one instance fail.
For a federated setup, I would look at spreading the participating servers in the federation across servers that are not in related cluster groups. The reason being is that you are trying to distribute some workload. If the Federated servers are in the same A/A cluster then you run the risk of the federation running off the same server - which doesn't accomplish the goal at hand.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 27, 2011 at 12:26 pm
bopeavy (5/27/2011)
ElliottWith that said if node one is active and node two is active and node 2 is the fail over can I use the node 2 for log shipping to 3rd off site server?
An active server can log ship to another active server. Your example here is unclear, are we talking active/active? If we are keep in mind we have 2 ENTIRELY seperate SQL instances in play that have nothing to do with each other other than the ability to be failed over. Perhaps this is the way to think about it, Instance 1 and Instance 2, the node that either is running on is not important.
Is that clear?
CEWII
May 27, 2011 at 12:28 pm
bopeavy (5/27/2011)
WayneSThanks, I was trying to set up where I have 3 sql servers on standard edition so that I have a failover and can log shipfrom node 2 with out effecting the node 1 or prod enviroment.
Okay, so three nodes (servers/computers).
I believe that standard edition only supports 2 nodes. All this means is that a particular instance can only be installed on two nodes.
So, you could have:
NODE 1 NODE 2 NODE 3
ClusterA ClusterA
ClusterB ClusterB
ClusterC ClusterC
Each Cluster[A/B/C] is one installation of SQL Server on a cluster (installation on each node for that instance), and has it's own resources that are swapped from node to node as necessary.
Each Cluster[A/B/C] instance can fail over from whatever node it is active on, to the node that is it's passive node. The instance can only be active on one node at a time. For ClusterA, either Node 1 or Node 2 can be it's active node; the other node will be it's passive node.
Now, at this point, forget about nodes. Think only of instances now.
Each SQL Server instance can log ship to any other instance - just not itself. The instance receiving the log shipping does not need to be on a cluster (it can be, but not required). ClusterA can log ship to ClusterB, or ClusterC, or any other instance on another server accessible via your network.
I think that it would really benefit you to read this Microsoft Clustering White Paper.
I'm not entirely sure what you mean by the "prod environment" above. If you are log shipping from/to the instance running prod environment, you are affecting it. If not, then your not affecting it.
Well, this may not be entirely true. If the prod environment is one of those Cluster[A/B/C] instances, and any of the other instances are currently running on the same node that the prod instance is running on, then it is possible that there will be resource contention that may affect it - especially if instances are not set up properly for sharing CPUs/Memory.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 28, 2011 at 1:20 am
Active/Active means in term of microsoft multiple instances on mutiple nodes and it follow round robin algorithm for fail over,there is no any Load Balancing like Oracle RAC
Active/Passive means,fail over only if 1 node down then services start on another node automatically
If you have required Logshipping and database mirroring on another node then you have required
Active/Active cluster of microsoft SQL server,
1 instance for Primary Database
1 instance for logshipping or database mirroring
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply