Solved

SQL Server Failover with Sharepoint

Posted on 2010-09-07
14
1,242 Views
Last Modified: 2012-05-10
Hi, I am currently trying to set up a high availability environment with Sharepoint using a SQL Server 2008 database.

Right now I have two servers:
A: Sharepoint 2007, Project Server 2007, SQL Server 2008
B: Sharepoint 2007, Project Server 2007

Server B essentially acts as a replica web front-end of Server A.

I am interested in adding a SQL Server Failover Cluster Installation to Server B.  When I go to try to install it, I get a number of errors stating: "The local computer is not a member of a Windows failover cluster".

So I assume I'd go to Windows Server Manager under "features" and add "Failover Clustering".  I have never created a Windows failover cluster before so I am unfamiliar with how this works.

--Am I going about this the right way?
--Instead of adding the SQL Server failover feature to Server B, should I create a Server C instead?
--Do I need to add the Windows Failover Clustering to Server A since the database is located here to take advantage of this feature?  Is this going to break my front end if I add clustering at this point?
0
Comment
Question by:jamorlando
  • 7
  • 7
14 Comments
 
LVL 21

Accepted Solution

by:
chapmanjw earned 500 total points
ID: 33621842
SQL Clustering relies on Windows clustering.  In an ideal environment, you would have a separate SQL cluster from your SharePoint/Project servers.  Here is some instruction on setting up a SQL Cluster: http://msdn.microsoft.com/en-us/library/ms179410.aspx.

Ideally you would have:
A: SP2007, Project 2007
B: SP2007, Project 2007
C: SQL Cluster Node
D: SQL Cluster Node

The only other thing you might consider is having 2 front ends and 1 application server (for central admin, indexing, etc).  So:
A: SP2007, Project 2007 - Web Front End
B: SP2007, Project 2007 - Web Front End
C: SP2007 - Application Server
D: SQL Cluster Node
E: SQL Cluster Node
0
 

Author Comment

by:jamorlando
ID: 33661908
There is no way I would ever get approved to buy all those new servers, as ideal as it would be.  I think virtualization might also not be a good idea, for both web and database servers.

Using my current setup is there a way to add a SQL Server failover cluster to server B?

Thanks!
0
 
LVL 21

Expert Comment

by:chapmanjw
ID: 33662522
You would basically have two options here.  You could setup server B to replicate the SQL data on server A and manually fail it over in case of a failure.  SharePoint doesn't specifically support SQL replication in that method automatically.  For SQL replication, see http://msdn.microsoft.com/en-us/library/ms151198.aspx.

If you wanted to do a full SQL Cluster (which would be transparent to SharePoint in failing over and such), you would need to setup Windows clustering on the servers.  However, SharePoint is not designed to run on a Windows cluster.  

Seeing that you are limited to your two server setup, I would go with the replication route and manually fail over if the primary database fails.  
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:jamorlando
ID: 33662573
This is great information.  Would I manually fail it over from Central Admin by relinking the database to the replicated database server?
0
 
LVL 21

Expert Comment

by:chapmanjw
ID: 33662951
That is one method.

One, more rudimentary, method you could use is this:

In the Windows HOSTS file (c:\windows\system32\drivers\etc\HOSTS) on both servers, create a manual entry for the SQL server IP address that is different from the actual name (like SPSQL or something like that).  Then have SharePoint point to that made up name for SQL.

In the event you need to fail over, you would simply change the Windows HOSTS file to the other IP address.
0
 

Author Comment

by:jamorlando
ID: 33662998
Wow, such a good idea.  Let me just try implementing this, and I'll get back to you.  Thanks for your help so far!
0
 

Author Comment

by:jamorlando
ID: 33665051
When I'm setting up replication, should I choose Snapshot, Transaction or Merge?
0
 
LVL 21

Expert Comment

by:chapmanjw
ID: 33665132
Merge might be the best.  Snapshot is for one way, whole database at a time replication.  Transaction is for one way only.  Merge lets you connect to either server and have changes replicated through both.
0
 
LVL 21

Expert Comment

by:chapmanjw
ID: 33665138
0
 

Author Comment

by:jamorlando
ID: 33672744
I came across this article that says that replication isn't supported with Sharepoint, and it tends to be unstable.
http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/6fa96127-8298-4018-b875-209ec6ba71f0

So would I be right to say that for database redundancy I should set up a Windows Cluster, followed by a clustered SQL server install?
0
 
LVL 21

Expert Comment

by:chapmanjw
ID: 33672777
Yes, it is best to have a SQL Cluster (which requires a Windows cluster).  The replication idea would only be if you cannot do that and so that you have a contingency plan if the primary SQL server fails.  

SharePoint doesn't support automatically failing over with replication.  That is where tricking it with the Windows HOSTS file, etc, came into play to manually fail over.
0
 

Author Comment

by:jamorlando
ID: 33672800
So really, if I use SQL Server clustering I require a minimum of 4 servers.
Like you said:
A: SP2007, Project 2007
B: SP2007, Project 2007
C: SQL Cluster Node
D: SQL Cluster Node

Now, if I want to split out Central Admin to yet another server, what do I do if that server fails?
0
 
LVL 21

Expert Comment

by:chapmanjw
ID: 33672851
Technically, central admin would be available from both of your front-ends, it would just be the application pieces (like indexing) that would run on the "application" server.  In my company network, that application server is where we point to for our central admin access so that the main front ends dont incur the traffic.

If the application server goes down, you can move the functions to one of your front-ends until you get it back up.  You can only have the application services running on one server at a time.
0
 

Author Comment

by:jamorlando
ID: 33672875
Ok, I may need clarification, so if I do I'll open a new thread, as this topic is outside the scope of this thread.  Thanks for your great help and timely responses!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note:  There are two main ways to deploy InfoPath forms:  Server-side and directly through the SharePoint site.  Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

679 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