Solved

SQL HA without Shared Storage

Posted on 2014-04-05
18
1,643 Views
Last Modified: 2014-04-13
Hey guys

I did a little research and found these steps on how to create a HA SQL without shared storage.  Now, if you read down to step 6 it says to use a shared drive.  My thought was to use two file servers that are employing DFS and NamedSpaces.  Is that cool?

Here are the high-level steps to follow to deploy SQL 2012 AlwaysOn in a SQL 2012 failover cluster (without shared storage).


1. Install Windows Server 2012 in two computers or virtual machines (VMs), each with a single network interface card (NIC).
2. Create a two-node failover cluster without shared storage. You will need a cluster name and an IP address for the cluster network name.
3. Install SQL Server 2012 Enterprise on both computers as if they were going to be stand-alone SQL servers. ¿ When you install, use a domain account for the SQL server services.
¿ Open the Windows Firewall on ports TCP 1433 and TCP 5022.

4. Create a temporary "seed" database on the first SQL server using SQL Server 2012 Management Studio. This database will be used to establish the AlwaysOn cluster, and then can be deleted after the first production database is deployed.
5. Make sure the database is of the "Full" type model, and perform a SQL Backup job.
6. In the Management Studio, create an AlwaysOn Availability Group and an Availability Group Listener. (The Availability Group Listener is essentially the virtual (or clustered) SQL Server instance. There is a one-to-one relationship between availability groups and listeners.) ¿ Assign a DNS name and TCP IP address for the AlwaysOn Availability Group and an Availability Group Listener.
¿ Assign a shared network folder that is accessible to all SQL servers that will have AlwaysOn database replicas.

7. At the AlwaysOn High Availability node, right-click and select Add A Database To An Availability Group. If your database is of the Full type and has been backed up, the status will be "Meets Requirements". Click Next.
8. Select that you will perform a full synchronization, using the shared network folder you specified in step 6(b). Click Next.
9. Enter security information to access the primary database replica. Click Next, observe the validation and click Next, and then Finish.
10. Observe after a moment that new database replica on the secondary node in the SQL
0
Comment
Question by:jonmenefee
  • 7
  • 5
  • 3
  • +1
18 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39981069
Did you just want to share a tip, or do you also have a question other than "is that cool?"?

It is indeed, compared to high costs of a SAN.

I also found an article including these steps here: http://www.techrepublic.com/blog/the-enterprise-cloud/sql-server-2012-alwayson-high-availability-database-for-cloud-data-centers/

It says

The independent database copies are presumed to exist locally in direct attached storage (DAS) on each SQL node, or over on the network using economical Windows Server 2012 SMB 3.0 file shares on dedicated Windows Server 2012 file servers.

This says a file share isn't necessary at all, but the solution described in the 10 steps does make use of a file share. Hm.

I'm not an expert on HA, but I'd say without any shared storage the system would even provide higher availability. Though the file share is decoupling the servers, which is also not a bad thing. If it fails, only the synchronisation will stop, not the servers. Using a DFS would ensure better availability of that file share, sure, but then you could also go back to SAN.

Bye, Olaf.
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 39981692
there  are different  types of SQL 2012 HA.

one is  SQL Cluster  on Windows  cluster ( needs shared storage)
Other is individual SQL Instance but on Windows Cluster AlwaysOn practice.

decide what to do  ? depends on ur needs..

Both works fine.

and like olaf said? what is the purpose of ur q ?
0
 

Author Comment

by:jonmenefee
ID: 39981709
The question I had was would the DFS replication process cause any corruption of the SQL data. The steps that I outlined are basing the data on a shared drive and if I used DFS replication for redundancy could that cause a corruption?
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 39982252
DFS Replication is not an Alternate for Data Replication. Maybe a replcation of backup files.
But While SQL service is using Database files(mdf,ndf,ldf files) they are all exclusive mode and not be shared with any other application even the system services itself
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39982607
That's what I would guess, too. DFS would replicate whole files, this is not really working for MDF/NDF/LDF.

The author of your instruction steps tells, the two SQL Server instances could also each have database files which "exist locally in direct attached storage (DAS) on each SQL node". Then the setup step 6 would most probably be different and not using a file share, but he doesn't show how this would be done. Obviously the setup would let a primary server trigger the secondary what it should replicate directly instead of via a (database on a) file share.

Bye, Olaf.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39983560
That shared location accessible for all nodes with database replicas is used to actually propagate the primary node transactions to the other nodes. It is just a network shared location on any of the nodes or on a separate computer or storage unit available on the network. That location has to be accessible to the domain user for SQL accounts , for the cluster and for the availability group accounts.
0
 

Author Comment

by:jonmenefee
ID: 39983594
But the shared location cannot be using DFS Replication to another file server. It can only be on a single file server I am guessing (from earlier replies
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 39983739
The question came up from a misunderstanding i guess..

"Shared Storage"  ..

In a windows Cluster Shared storage means that, I do ave a Storage(like EMC,HP or IBM) and u publish it to physical or Virtual servers. and ur windows cluster service connects or disconnects this storage on the active cluster node..

on the first part
2. Create a two-node failover cluster without shared storage
means "create a cluster without a shared resource"

second shared storage is "shared place(network Location) that 2 sql servers can connect".
And this shared place(network Location) ll be used for replication or Log shipping,

u must not mix this 2 situations.
0
 

Author Comment

by:jonmenefee
ID: 39983824
Yes. I understand the difference. I meant to say that the storage location is a network share and that the network share cannot use DFS replication to stay redundant
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 39986117
How did you setup the quorum?
0
 

Author Comment

by:jonmenefee
ID: 39986248
Using File Share Witness on a separate server for Quorum.  Its even a separate server from the Shared Folder that the instructions call for in step 6.  

Can I assume that the  shared folder that is called for in Step 6 will not be the location for the mdf, ldf and other similar files?  If that location is not used for storing the database, but is instead some sort of intermediary then making that file part of a DFS Replication group really wont do any good.  But, maybe making it part of a DFS NameSpaces might be alright?  

Now I guess the question has changed a bit.  Instead of using DFS Replication to keep that file share available to the Nodes, maybe I should make a DFS NameSpace instead?
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 39986421
DFS file share or anykind of file share is not a good option for MDF,ndf,ldf files.

The way to keep these files replication, u need to replicate data.

Why ?

Every data or log file has checkpoints that must be related to each individual database. If one this check points are not the same(or same as desired from SQL service) u r ll have data loss. At least ur data in ur log files will be lost. file replication or copy operations cannot do the copy real time.  And database files ll be exclusively locked by sql service. u cant open em for any kind of file manupulation apps.

Only way is detaching database , copying data files and then attaching it. but while copy process runs ur database will not be avaiable.
0
 

Author Comment

by:jonmenefee
ID: 39986430
Thanks. If step 6 of the instructions put those kinds of files on the shared folder then you are right, DFS replication cannot be used. I sent an email to the author of the article and am awaiting his reply
0
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 500 total points
ID: 39986618
step 6. explains the creation of SQL AlwaysOn Group.

while creating an always on group, SQL server does these steps,
  1. Create a full backup of the source database to shared folder
  2 .Restore Full backup to target sql server from shared folder.
  3.create a log backup of source( to get the changes while the full backup) to shared folder
  4. restore Log backup to target sql server from shared folder
  5. Join target server in to availabilty group
  6. sync data between source and target databases.

As u see from the explanation, nothing explained about database replicas on shared folder. shared folder is used only while creating availibity group. and then everything goes on real time after that.
0
 

Author Comment

by:jonmenefee
ID: 39986658
Ah. Thank you!  So if I wanted to use DFS replication on that folder to simply make it redundant then that would be alright?
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39987830
>if I wanted to use DFS replication on that folder then that would be alright?

No, Melih's explanation tells you the file share is only needed temporarily and adding DFS for it is just overkill and wouldn't work anyway,

Melliah>everything goes on real time after that.
Which means a file share isn't needed after the first replication is made and every further syncing is done live by server to server communication without a file share. It's even not used to exchange current transaction log data after the first full database has been copied and the logs occuring during that copy also have been streamed over. From then on it's just directly communicated to the secondary server.

Using DFS, no matter if during the creation of the AlwaysOnGroup or permanently, wouldn't work anyway, as it works on whole files. Even if it would get access to eg mdf, the moment it begins syncing that to a secondary part of the DFS, the original mdf would already be in a new state invalidating the current syncing process. You'll never get a consistent and working state of a file copy, even just for readonly purpose in a secondary sql server.

There is a reason why database replication in the end works with forwarding the current transactions to secondary servers to put the secondary databases into the same state. You need this knowledge about the modifications to be made (eg by INSERTS,UPDATES,DELETES) to redo them.

Bye, Olaf.
0
 

Author Comment

by:jonmenefee
ID: 39988354
Thank you very much.  Yes. Last night we got the cluster up and running and it's working like a charm. All of your explanations were timely and very helpful. Thank you!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39988360
Hi guys, I have a question of my own that is somewhat on the same page and I would be grateful if you could give your insight:

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Q_28400947.html

Thank you and I apologize for the spam.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This is an issue that we can get adding / removing permissions in the vCSA 6.0. We can also have issues searching for users / groups in the AD (using your identify sources). This is how one of the ways to handle this issues and fix it.
Last article we focus in how to VMware: How to create and use VMs TAGs – Part 1 so before follow this article and perform the next tasks, you should read the first article how to create the TAG before using them in Veeam Backup Jobs.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
How to install and configure Citrix XenApp 6.5 - Part 1. In this video tutorial we have explained step by step installation of Citrix XenApp 6.5 Server on Windows Server 2008 R2 is explained in this video. We have explained the difference between…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now