Setting up availability group

Question

We are trying to test HADR , we have two single node cluster servers with SQL Denali CTP3 instance each.

When we try adding replicas it asks for connect to server and when connected the other single node cluster instance it pops a message "The specified instance of SQL Server is hosted by a system that is not a windows failover cluster (WSFC) node."

As mentioned it is a windows failover cluster (2008 R2) single node cluster not sure why we keep getting this. 

Also tried to create manually but the secondary instance in the availability groups>availability replicas is showing down.

A dumb question, does the secondary instance need to have a DB in recovery mode just like when we create mirroring, for HADR to work?

 

Below code

CREATE AVAILABILITY GROUP AG2

   FOR 

      DATABASE T2

   REPLICA ON 

      'DENALI-MRR\D5' WITH 

         (

         ENDPOINT_URL = 'TCP://DENALI-MIR:5022',

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

FAILOVER_MODE =  MANUAL,

PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE )

         ),

 

      'DENALI\D2' WITH 

         (

         ENDPOINT_URL = 'TCP://DENALI:5022',

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

FAILOVER_MODE =  MANUAL,

         SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY),

         PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE)

         ); 

Answer 1

I think you have a basic misconfiguration.  The replicas should be in the same "cluster", not two seperate "1 node" clusters.

---

David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

Answer 2

Also, if done correctly, you don't have to do anything to backup and restore the database like you do in mirroring.  I've only done it through the UI, but part of the wizard ask for a "network share" which I assume is to store the backup and subsequent restore of the database, which is automated.  I do believe though that you need to have the same path available on all the replicas so the database is stored in the same location.

---

David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

Answer 3

David,

 

Thanks for the reply, I had originally started with two node cluster and two separate instances as that is how I had read in few docs, but what got me confusing was the fact it keeps giving me an error message during validation phase when I created AG on the two node cluster , the error is

"The following folder location(s) do not exist on the secondary replica DENALI-MIR\D3 D:\..path"

I am not aware of any way to get the same drive letter on both the nodes at the same time. Please help

 

Thanks

Answer 4

Did you setup shared storage in your original two-node cluster or sth?

If so, this is not needed for AG setup. you just need to create a cluster which includes 2 nodes.

Answer 5

Thanks I was able to get the HADR setup but was not convinced if this is how it should work

1) I have two nodes with two individual drives (shared), I took out one shared drive from the cluster on one node , changed the drive letter to D: outside the cluster (same as the other node)

2) Installed clustred SQL instance specifing D: when the wizard asked for clustered disk requirement

3) After installation now the cluster had D:  on both nodes (sql installation automatically added the disk when creating the new SQL group) and my sql was started, i then created the HADR (had to create the path manually)

4) HADR was established and was able to failover and failback

5) When I restart the SQL group on one node, the disk drive is now letter less, I need to remove the disk from cluster change it (D:) outside and add it back to cluster for my sql to start

Is the requirement only to have two nodes in a cluster, and sql should be two stand alone setups?

Also if there is a guide for the HADR if you can point me to it that will help with my ignorance.

Answer 6

It depends on how you define HADR.  There are really two options:

• AlwaysOn Availability Groups (think database mirroring on steroids)

• AlwaysOn Failover Clustering (this is good old fashioned failover clusters using shared storage - no replication)

Where it gets confusing is that both options use "Failover Clustering" to an extent, but they both are completely different animals.

Assuming you want to try out the "new" stuff, I think you want AlwaysOn Availability Groups.  If so - this is the article that helped me pull it all together.

http://sql-articles.com/articles/high-availability/steps-to-configure-sql-alwayson/

The requirements were also a big help, make sure you read this.

http://msdn.microsoft.com/en-us/library/ff878487%28v=SQL.110%29.aspx

However, what you described above sounds more like you tried setting up a traditional failover cluster - which is not what you want to do if you intend to replicate your databases.

---

David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

Answer 7

David and Goden, Thanks for all the answers