July 25, 2012 at 7:46 pm
In the microsoft documentation for restrictions regarding alwayson availability groups ( http://msdn.microsoft.com/en-us/library/ff878487.aspx ), the following appears:
"If the file path (including the drive letter) of a secondary database differs from the path of the corresponding primary database, the following restrictions apply: (snip)"
Can anyone explain how the drive letter of a secondary database could ever be the same as the drive letter of the primary if the secondary is on a failover cluster node used by the primary?
If the primary node fails over, it must bring across the resources of the cluster node, including the drive letters. Therefore those same drive letters cannot already exist on the failover node, which means the replica databases on that node can never use the same drive letters as those used by the primary, which means the restrictions listed above must always be true.
I must be missing something, aye?
July 26, 2012 at 10:04 am
allmhuran (7/25/2012)
In the microsoft documentation for restrictions regarding alwayson availability groups ( http://msdn.microsoft.com/en-us/library/ff878487.aspx ), the following appears:"If the file path (including the drive letter) of a secondary database differs from the path of the corresponding primary database, the following restrictions apply: (snip)"
Can anyone explain how the drive letter of a secondary database could ever be the same as the drive letter of the primary if the secondary is on a failover cluster node used by the primary?
If the primary node fails over, it must bring across the resources of the cluster node, including the drive letters. Therefore those same drive letters cannot already exist on the failover node, which means the replica databases on that node can never use the same drive letters as those used by the primary, which means the restrictions listed above must always be true.
I must be missing something, aye?
Well, you can't host two replicas of an availability group on the same node, so it's a moot point. http://msdn.microsoft.com/en-us/library/ff878487.aspx#FciArLimitations
Also, kb976097 http://support.microsoft.com/kb/976097 is applicable, as it allows asymmetric storage configurations, aka, drive j: on nodes 1 and 2, and a different drive j: on nodes 3 and 4, which should allow you to keep the drive letters and paths the same.
July 26, 2012 at 4:04 pm
allmhuran (7/25/2012)
I must be missing something, aye?
Yes, both instances must reside on separate nodes and do not use shared storage, they use local disks which you would configure with the same drives and paths but they must be joined to the same Windows cluster.
Now, for AO groups incorporating failover cluster instances it gets more complicated. I'm currently putting together an article that explains all this. I'll add a separate reply with the detailed scenarios to help you understand
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 26, 2012 at 11:08 pm
Certainly looking forward to that article.
I haven't explained the situation clearly enough. Imagine the following:
Cluster = C1
Servers = S1, S2.
SQL Server instances = SQL1 (clustered), SQL2 (not clustered).
Database = DB1.
Configuration:
Instance SQL1 runs on S1 and fails over to S2.
Database DB1 is hosted on instance SQL1. Files are on path, eg, e:\database_files
Goal: Create an asynchronous replica of DB1 hosted on instance SQL2 on server S2 (used as a reporting source, NOT as a DR solution).
If DB1_replica lives on S2, then the path to the database files for DB1_replica cannot be e:\database_files\, because during a failover of SQL1 the E drive from S1 will be failed over to S2 with the resource group containing SQL1. Thus an E:\ drive cannot already be provisioned on S2, thus DB1_replica on SQL2 on S2 cannot have the same data path as DB1 on SQL1 on S1.
July 27, 2012 at 12:54 am
I just saw this, which describes the desired scenario and states that it would result in a senario in voliation of the AG constraint:
The following example scenario illustrates how this configuration could lead to problems:
Marcel configures two a WSFC cluster with two nodes, NODE01 and NODE02. He installs a SQL Server failover cluster instance, fciInstance1, on both NODE01 and NODE02 where NODE01 is the current owner for fciInstance1.On NODE02, Marcel installs another instance of SQL Server, Instance3, which is a stand-alone instance.On NODE01, Marcel enables fciInstance1 for AlwaysOn Availability Groups. On NODE02, he enables Instance3 for AlwaysOn Availability Groups. Then he sets up an availability group for which fciInstance1 hosts the primary replica, and Instance3 hosts the secondary replica.At some point fciInstance1 becomes unavailable on NODE01, and the WSFC cluster causes a failover of fciInstance1 to NODE02. After the failover, fciInstance1 is a AlwaysOn Availability Groups-enabled instance running under the primary role on NODE02. However, Instance3 now resides on the same WSFC node as fciInstance1. This violates the AlwaysOn Availability Groups constraint.
However, the suggested solution is this:
To correct the problem that this scenario presents, the stand-alone instance, Instance3, must reside on another node in the same WSFC cluster as NODE01 and NODE02.
I don't see how this solves the problem. If node 1 fails and then node 2 also fails, then fciInstance1 will fail over to node 3, and the same violation will occur. UNLESS node 3 is part of the windows cluster, but NOT part of the SQL cluster, and the always on AG is OK with this.
July 27, 2012 at 3:23 am
allmhuran (7/26/2012)
Certainly looking forward to that article.I haven't explained the situation clearly enough. Imagine the following:
Cluster = C1
Servers = S1, S2.
SQL Server instances = SQL1 (clustered), SQL2 (not clustered).
Database = DB1.
Configuration:
Instance SQL1 runs on S1 and fails over to S2.
Database DB1 is hosted on instance SQL1. Files are on path, eg, e:\database_files
Goal: Create an asynchronous replica of DB1 hosted on instance SQL2 on server S2 (used as a reporting source, NOT as a DR solution).
If DB1_replica lives on S2, then the path to the database files for DB1_replica cannot be e:\database_files\, because during a failover of SQL1 the E drive from S1 will be failed over to S2 with the resource group containing SQL1. Thus an E:\ drive cannot already be provisioned on S2, thus DB1_replica on SQL2 on S2 cannot have the same data path as DB1 on SQL1 on S1.
This configuration violates the AlwaysOn requirements that each replica (sql instance) must reside on a separate physical node.
On failover of SQL1 instance to server S2 both replicas now reside on the same host and this is not allowed.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 27, 2012 at 3:33 am
allmhuran (7/27/2012)
However, the suggested solution is this:To correct the problem that this scenario presents, the stand-alone instance, Instance3, must reside on another node in the same WSFC cluster as NODE01 and NODE02.
I don't see how this solves the problem. If node 1 fails and then node 2 also fails, then fciInstance1 will fail over to node 3, and the same violation will occur. UNLESS node 3 is part of the windows cluster, but NOT part of the SQL cluster, and the always on AG is OK with this.
This is correct, as you have an FCI installed across S1 and S2 you would need a thrid node S3 joined to the Windows cluster and with a standalone instance of SQL server installed.
The examples below should help you understand a little more, let me know if this helps
Basic example of an AlwaysOn configuration;
Computer Nodes
SQLHANode1.UKtrading.co.uk 192.168.1.41
SQLHANode2.UKTrading.co.uk 192.168.1.42
WSFC Joined
SQLCluster01.UKTrading.co.uk 192.168.1.45
Non Clustered SQL Server Instances
AOINST1 on SQLHANode1
AOINST2 on SQLHANode2
AlwaysOn Group
SQLAOGrp1
AlwaysOn Listener
SQLAOListener.UKTrading.co.uk 192.168.1.46
Each server has the following local drive storage and stand alone instances of SQL Server.
C: = 80GB
D: = 80GB
F: = 750GB
G: = 500GB
H: = 350GB
The AlwaysOn group has a listener using the virtual network name and IP detailed above. This would represent a standard AlwaysOn configuration and does not use a SQL Server FCI.
AlwaysOn incorporating FCI;
Computer Nodes
SQLHANode1.UKTrading.co.uk 192.168.1.41
SQLHANode2.UKTrading.co.uk 192.168.1.42
SQLHANode3.UKTrading.co.uk 192.168.1.43
SQLHANode4.UKTrading.co.uk 192.168.1.44
WSFC Joined
SQLCluster01.UKTrading.co.uk 192.168.1.50
Cluster SQL Server Instance
SQL-CL-INST\SiteLive 192.168.1.51
Non Clustered SQL Server Instances
SQLHANode3\DRRepl
SQLHANode4\CLMaint
AlwaysOn Group
SQLAOGrp1
AlwaysOn Listener
SQLAOListener.UKTrading.co.uk 192.168.1.46
Each server has the following local drive storage.
C: = 80GB
D: = 80GB
F: = 750GB
G: = 500GB
H: = 350GB
In addition to the above local disks, SQLHANode1 and SQLHANode2 have the following shared LUNs attached from a SAN.
L: = 500GB
M: = 350GB
N: = 350GB
P: = 250GB
We have our WSFC "SQLCluster01.UKTrading.co.uk", this is the cluster entry point for all 4 nodes and each server is joined to this cluster.
A failover cluster instance of SQL Server has been installed first to SQLHANode1 and SQLHANode2 has been added as a partner node, the instance is "SQL-CL-INST\SiteLive" as detailed above. The FCI is using the following shared drives
L:
M:
N:
P:
2 stand alone instances of SQL Server have been deployed as follows;
SQLHANode3 has an instance named "DRRepl"
SQLHANode4 has an instance named "CLMaint"
We have created an availability group titled SQLAOGrp1 on SQL-CL-INST\SiteLive and we include the following instances as replicas
SQLHANode3\DRRepl
SQLHANode4\CLMaint
This completes our AlwaysOn availability group incorporating FCI's.
Note here that SQLHANode1 and SQLHANode2 are part of the same Windows Server Failover Cluster as SQLHANode3 and SQLHANode4, only SQLHANode1 and SQLHANode2 are set up as failover partners for the clustered instance SQL-CL-INST\SiteLive.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 30, 2012 at 10:38 pm
Cheers, the separation of SQL and windows cluster responsibilities with respect to this new tech wasn't quite clear to me. Makes sense now.
July 31, 2012 at 12:11 am
You're welcome
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply