locked
SQL 2005 Mirroring with Sharepoint 2007 RRS feed

  • Question

  • Hi, iam from Mexico,
    somebody has installed SQL 2005 with mirroring and sharepoint 2007?
    regards.
    thanks
    Thursday, December 7, 2006 1:03 AM

All replies

  • Humberto,

    Have you gotten any responses for this question?  I am wondering how SharePoint 2007 will be able to failover to the mirror in the even that the prinicpal goes down.  My testing indicates that SharePoint does not automatically failover to the mirror in the event of a principal outage.

    If there is a connection string for the SharePoint databases stored somewhere, I would think it could be modified to include the mirror server, but I can't find any connection strings!!

    Thanks,

    Ryan

    Tuesday, January 2, 2007 5:59 PM
  • Ryan,

    I have the exact same concerns.  Have you come across ANY information regarding SPS 2007 with failover to SQL Server 2005 mirror database? I understand the mirroring aspects and the steps to take once a principal database server goes down...but how to have SPS 2007 utilize the mirror (or what would then be the new principal) is still a mystery.

    Thanks,

    Mark

    Wednesday, January 17, 2007 10:20 PM
  • Mark,

    I opened a call with Microsoft a couple of weeks ago and it turns out that SPS 2007 does not have any support for automatic failover to the mirror server. 

    Microsoft suggested using a SQL Alias or the following approach:

    The approach for failover is

    Ø  If the SQL server falls over, use the stsadm command RenameServer to change the db server name, then stop & restart IIS & the timer service.

    Ø  If a single db other than configdb falls over, detach & reattach the db using the UI or via stsadm commands.

    Ø  If configdb falls over, the admin must use PSConfig.exe’s configdb command, or the renameserver command.  If they use the latter, they’ll have to detach & reattach the other db’s on the same server, to move those back to their original sql server location.

    The above approach seemed to be pretty complicated so I decided to use the SQL Alias approach and have written a windows service to monitor the things, here is an overview of the process:

    • using cliconfg.exe, create an alias on each SharePoint server in the farm with a name of the currently configured SQL Server that SharePoint is pointed to.  Set the value of the alias to the same server name. Example, if SharePoint is using server1 as the database server, create an alias called server1 and set the value of the alias to server1.
      • the cliconfg.exe creates a registry value called server1 with the name of the alias in the following key:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

    The data for the server1 value will be DBMSSOCN,server1

    • My service periodically queries the database_mirroring_witnesses catalog of the witness server to see what the principal server is for the SharePoint_Config database.  This object resides in the master database.  Once you query this catalog there is a column that has the current principal server information. 
    • Remotely query the registries of all the SharePoint servers in the farm to see what database server the SQL alias created above is pointing to.  If the alias is pointed at a different server than what the witness server thinks is the principal, remotely update the alias's value in the registry and run IISRESET.exe <computername> /noforce.

    The account that runs my service needed to have select access to the  databse_mirroring_witnesses catalog on the Witness server.

    Also, since I am only querying the witness server to see what the principal server is for the SharePoint_Config database, I am assuming that if this database fails over to the mirror, all the databases have failed over and I am redirecting SharePoint via the alias on all SharePoint servers in the farm.

    So far it is working pretty good.  Microsoft also said that they will be releasing a whitepaper on this subject within a few weeks.  I haven't seen anything yet...

    Hope this helps.

    Ryan

     

    Wednesday, January 17, 2007 11:14 PM
  • Thanks, Ryan.  I'm hoping MS makes an effort to link SPS 2007 with the failover functionality of SQL Server 2005.  Here's a blog I came across reflecting that MS is allegedly working on this:  https://blogs.pointbridge.com/Blogs/scheele_dave/Lists/Categories/Category.aspx?Name=SQL

    Regards,

    Mark

    Thursday, January 18, 2007 4:17 AM
  • Microsoft have release the white paper Using Database Mirroring with Office SharePoint Server and Windows SharePoint Services  

    I still have some problems with this however as it recommends that a maximum of 10 databases are mirrored.   If you have a large server farm with significant content you are likey to exceed this if you follow best practice on content database sizing.

    Andrew

    Tuesday, March 20, 2007 12:10 PM
  •  

    I've been trying to follow the whitepaper released by Microsoft about SQL Server 2005 Mirroring and MOSS (http://go.microsoft.com/fwlink/?LinkId=83725&clcid=0x409), but I've found that the document has a drastical error --it indicates that database mirroring session must be started from the principal server, and it must really be done from the mirror one. Ok let's pass it and move ahead (I could configure high availability mirroring with a witness server, finally) and when the backend fails over and I have to reconfigure MOSS, I follow the steps provided in the whitepaper but I can't reconnect to the Configuration Database, even though I have done

     

    stsadm.exe -o renameserver -oldservername <SQLPRINCIPAL> -newservername <SQLMIRROR>

     

    So I'm stuck here! I've tried to restart each and every service, restarted IIS, checked SQL Server services (mirror server active holding the active copies of the databases), I've run the MOSS configuration wizard first doing nothing but "Next .... next ..... next", then deattaching from the farm and reattaching again. Nothing. I've also tried with

     

    stsadm.exe –o setconfigdb –databaseserver <SQLMIRROR> –farmuser <MYDOMAIN\MOSS_Service_Account> –farmpassword <myFarmPassw0rd>

     

    and get nothing. If I swith SQL roles back and use the stsadm.exe -o renameserver to reverse to the initial situation, then it works!! (MOSS back online). So what's wrong!? I'm really stuck here. Two days by far.

     

    Marcel Jeanneau

     

    PS: Humberto yo soy de Peru, quizas podamos ayudarnos, marceljeanneau@hotmail.com

    Thursday, April 19, 2007 7:59 PM
  • Has anyone found more information on this topic?

    Thursday, November 1, 2007 6:43 PM
  •  

    See... that is already known to do that.  The issue would be having it automatically after the db is mirrored to switch the SharePoint stuff.  With this there is still a manual process behind it, correct?
    Wednesday, January 16, 2008 7:37 PM
  • Hi all,
    I've been working with db mirroring for some time. I've got it up and running in our production environment. I've written a batch file that automates all of the failing over to the mirror server and vice versa. Here's a link to the script. I've tested this extensively and it works great for me. Enjoy.

    http://spadmin.spaces.live.com/
    Monday, January 21, 2008 1:37 PM