Solved

SQL Server Failover with Sharepoint

Posted on 2010-09-07
14
1,195 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

22 Experts available now in Live!

Get 1:1 Help Now