Solved

SQL Server Failover with Sharepoint

Posted on 2010-09-07
14
1,264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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