Solved

SQL Server Failover with Sharepoint

Posted on 2010-09-07
14
1,220 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ORA-00923: FROM keyword not found where expected 3 61
SQL Server merge records in one table 2 21
Anyway to make these 2 SQL statements into one? 13 38
Query Syntax 17 34
In this article I will describe the Backup & Restore 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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