Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1281
  • Last Modified:

SQL Server Failover with Sharepoint

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
jamorlando
Asked:
jamorlando
  • 7
  • 7
1 Solution
 
chapmanjwCommented:
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
 
jamorlandoAuthor Commented:
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
 
chapmanjwCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
jamorlandoAuthor Commented:
This is great information.  Would I manually fail it over from Central Admin by relinking the database to the replicated database server?
0
 
chapmanjwCommented:
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
 
jamorlandoAuthor Commented:
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
 
jamorlandoAuthor Commented:
When I'm setting up replication, should I choose Snapshot, Transaction or Merge?
0
 
chapmanjwCommented:
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
 
chapmanjwCommented:
0
 
jamorlandoAuthor Commented:
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
 
chapmanjwCommented:
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
 
jamorlandoAuthor Commented:
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
 
chapmanjwCommented:
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
 
jamorlandoAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now