none
Project Server 2010 SQL mirror fail over RRS feed

  • Question

  • Hi,

    I have recently been testing with SQL mirroring for SharePoint and Project Server 2010 and want to share my findings and at the same time confirm that I have followed the correct process.

    After configuring the SQL databases for mirroring, I then set up the failover servers for all of the SharePoint databases via PowerShell. I then reconfigured the PWA instance using the Set-SPProjectWebInstance command to set the mirrored DB servers for PWA. From what I've read on TechNet PWA should now work when failing over the databases to the fail over SQL Server, but when failing over to the mirrored SQL PWA doesn't load and throws an internal error (custom errors off in web.config). SharePoint Central Admin and the SharePoint Portal (PWA is in the same Content Database) site all work as expected. On further investigation I could see that when accessing the PWA site there were failed login attempts on the primary SQL server, the login attempts were failing as the DBs were in restoring state as they had been failed over. I then decided to add a failover server to the four PWA databases using the AddFailoverServiceInstance method as you do for SharePoint databases, I can’t see this documented for the PWA dbs. This gave a different error message in IE when accessing PWA, a failed login to the SessionStateService database. I then decided to manually edit the session state connection string in IIS for the PWA site to add in a failover partner. PWA now loads fine when failing over between SQL servers.

    I'd be interested to hear if anyone else has come across this before or advise me if I have done something wrong because I can’t find any of the changes I made to get this working documented on TechNet anywhere for configuring Project Server 2010 high availability.

    Thanks

    Paul


    Paul Mather
    Wednesday, May 25, 2011 3:31 PM
    Moderator

All replies

  • Hi Paul,

    I'm a Program Manager on the Project Team and the current owner of the backup/restore/mirroring features. As described in the TechNet article, this is a supported scenario, and it has been tested. The Session State service is managed by SharePoint natively, so updating the session state connection string is tranparent to Project Server.

    Please feel free to contact us if the issue persists.

    Thanks,

    -Smith

     

     


    SC
    Monday, June 6, 2011 10:53 PM
  • Hi Smith,

    Many thanks for the reply.

    All of the SharePoint databases were configured for mirroring using the AddFailoverServiceInstance method including the sessionstateservice database, this still doesn't add the failover partner to the connection string for the session state database in IIS. When failing over all of the databases (SharePoint and PWA) to the mirror server and loading the PWA page it would error with failed login to the SessionStateService database on the Principal SQL server, so I updated the connection string for the session state database in IIS manually and PWA then loaded without any errors. Also as mentioned above in the earlier post, just running the Set-SPProjectWebInstance to add the mirror DB servers didn't work (PWA didn’t load, internal error) and I had to then run the AddFailoverServiceInstance method for the four PWA databases which then worked. Do you know what I could have done wrong, or is it the correct process to also run the AddFailoerServiceInstance command for the PWA databases?

    Is there any way to check the Failover SQL server for the four PWA databases like you can with the SharePoint databases because when checking the four PWA database properties via PowerShell (Get-SPDatabase) after adding the failover SQL servers using Set-SPProjectWebInstance the failover server is blank. After running the AddFailoverServiceInstance command for the four PWA databases the database properties then displayed the failover server and as mentioned PWA then loaded.

    The issue is now resolved using the steps described above but I'm keen to know what I had done wrong / was missing as mirroring for project server didn't work by just following the TechNet article on two different environments, SharePoint worked fine – Central Admin and Portal site collection loaded. One thing worth mentioning is that we were failing over all of the databases including all of the SharePoint databases to the mirror SQL server, I know the video on the TechNet article only shows failing over the Project Server PWA databases and none of the other databases used by PWA (sessionstateservice etc.). Is failing over all SharePoint and Project Server 2010 databases to the mirror SQL server a supported and tested scenario?

    Once again, many thanks for the reply.

    Paul


    Paul Mather
    Tuesday, June 7, 2011 2:20 PM
    Moderator
  • Hi All,

    Has anyone else set up Project Server 2010 SQL Mirroring and tested failing over all SharePoint and Project Server databases?

    Many thanks

    Paul


    Paul Mather
    Monday, June 13, 2011 8:54 PM
    Moderator
  • Hi Paul,

    we just setup Mirroring successfully with a customer. First we were not able to open the PWA site like you mentioned. We checked for the SessionStateService first as recommended by you. But that was OK. It showed up with its failover server configured in get-spdatabase and the connection string in IIS already contained the configuration necessary for failover that you had to enter manually.

    What we found out that you can set the failover parameters with set-spprojectwebinstance but the get-spprojectwebinstance will never show the changes (looks like a bug in my eyes...)

    If you check with get-spdatabase you find that the failover has been set correctly. In our case the failover parameters for the reporting database did not work. We provided them in the  set-spprojectwebinstance cmdlet, but this did not work. It did not show up in get-spdatabase, so we set it manually using

    $db = get-spdatabase | where {$_.Name -eq "PWA_Reporting"}

    $db.AddFailoverServiceInstance("sql02")

    $db.Update()

    Since we did that the system does the failover without further problems until now.

    I would like to add that we decided to do an automatic failover for the Project Server databases ignoring the official recommendation in Technet.

    We believe that the failover of the databases will only occur if the primary server fails - and in that case all databases will do the failover together. So there is no real danger that the three primary databases Archive, Draft and Published will not failover together what would be a problem for the system.

    Kind regards

    Christoph

    __________________________________

    Christoph Mülder
    SOLVIN information management GmbH, Hamburg, Germany

    Wednesday, May 2, 2012 12:10 PM
  • Hi Paul,

    one more year past away...

    But now I was just doing the second mirroring in my life and now I had exactly your issue with the session_state database.

    After Failover the PWA site was showing some broken Information and an unexpected error leading to a database permission denied issue in ULS log.

    I asked Google for session state mirroring and found this nice thread with you having the issue in 2011..... So it seems to be something that not always, but sometimes happens - as last year the connection string was OK....

    I edited it as suggested by you and - everything nice and beautiful....

    :-)

    Kind regards

    Christoph


    Christoph Muelder | Senior Consultant, MCSE, MCT | SOLVIN information management GmbH, Germany

    Wednesday, March 13, 2013 4:41 PM
  • Hi Christoph,

    Glad you found this post helpful :)

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Wednesday, March 13, 2013 8:37 PM
    Moderator