Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
DB Mirroring in CRM 4.0 - Failover not working RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Everyone,

    I have set up db mirroring (as discussed in this forum thread: http://social.microsoft.com/Forums/en-US/crmdeployment/thread/8ea41bcb-46ca-4447-b1e0-f69cadd144aa) for CRM 4.0. Here's what we have:

    -org_MSCRM db is mirrored in high-availability mode (auto-failover with a witness).
    -MSCRM_CONFIG db is mirrored the same way to the same mirror server (we also tried without mirroring this db, only the org_MSCRM)
    -I have followed the steps in this document to set up CRM to auto-failover: http://support.microsoft.com/kb/952278, including updating registry keys in the web servers and updating the MSCRM_CONFIG db to have the "Failover Partner" parameter in the connection string.

    When we failover from the principal to the mirror by clicking "failover" in SQL Server Management Studio, everything works as expected. The Mirror becomes the principal, and CRM continues to work. However, when we just unplug the network cable from the principal to imitate an actual failure, we cannot log in to CRM - we get a SQL connection failure.

    Apparently, the failover doesn't work when the principal server fails and becomes unavailable. It only works if the principal server fails and becomes the mirror. I assumed that we changed the connection strings in both the web server registries and the MSCRM_CONFIG db for this very reason ...

    Thanks,

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    Thursday, April 8, 2010 1:02 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Finally got an answer to this, when working with MS Support.

    The reason we could not connect to the DB was because the connection was timing out too quickly. We added

    Connection Timeout = 80

    to the connection string in the "configdb" subkey in the webserver's registry, and also in the connection string that you modify in the MSCRM_CONFIG database.

    In other words, in both places where we modified the connection string to include the "Failover Partner" attribute as directed by this article http://support.microsoft.com/kb/952278/en-us, we also had to add the "Connection Timeout" attribute and set it to a higher number of seconds.

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    Friday, June 18, 2010 7:19 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    No one has experienced this before? Again, the issue occurs when the original principal is totally unavailable, causing the mirror to become the new principal. If I click the "failover" button from within SQL Management Studio to make the db failover, but the original principal (which, after pushing the failover button, becomes the mirror) is still up and available, CRM maintains continuity.

    Are there other pieces that we need to include that aren't referenced in the support document I linked to? The document only instructs us to update 2 values in the MSCRM_CONFIG db, but I've seen posts where others updated several others. I ignored those because they weren't well documented and didn't match the official MS document.

    Or, is there a piece that may be only available on the original principal db server that CRM won't work without? Once that db server is down, the web server will not even connect to a db, even though the mirror is now running as the principal.


    Thanks,

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    Friday, April 9, 2010 6:54 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi Andy,

    In the past I setup mirroring on the Organization DB only and it was working. I used this article: http://consulting.ascentium.com/blog/crm/Post193.aspx

    My Dynamics CRM Blog: http://bovoweb.blogspot.com
    Monday, April 12, 2010 8:54 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi Andy,

    We are Using Database mirroring for CRM 4.0.

    We have modified the Value ... Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM
    Also some scripts on SQL ....... Update Org connection String>>> like 

    Update Organization set ConnectionString = 'Provider=SQLOLEDB;Data Source=sql1;Failover Partner=sql2;

    Initial Catalog=<organization>_MSCRM;Integrated Security=SSPI'

    where DatabaseName = '<organization>_MSCRM'

     

    Update Organization set MirroredSQLServerName = 'sql2' where DatabaseName = '<organization>_MSCRM'

     

    Every thing works fine in both cases mannual or Atuo failover..

     

     

    Jameel

    Tuesday, April 13, 2010 5:13 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks guys - we are doing those things as well. We followed the steps in the ascentium blog post, which are pretty much the same as what you wrote Jameel. We have a successful fail over when we manually go into SQL Management Studio and tell the principal db to failover. But when that server becomes completely unavailable, the web servers can't get to CRM.

    I know that the problem revolves around the fact that the web servers can't get to the MSCRM_CONFIG db, because deployment manager doesn't work on either web server when we shut down the server with the principal db. We are mirroring MSCRM_CONFIG as well, and the web servers have the key change documented in the links above to include the failover partner parameter in the connection string for the configdb subkey. However, it doesn't seem to be enough. If I shut the principal server down, and then go and change that subkey to point directly at the mirrored MSCRM_CONFIG db on the mirror server, then I can open Deployment Manager, but still cannot browse to CRM. I assume that if I were to use Deployment Manager at that point to re-point the deployment to the new dbs on the mirror server, I could get it to work, but I understood that this was supposed to work better than that.

    Has anyone seen this?

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    Monday, April 19, 2010 5:19 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi Andy,

    When failover occur (wheather mannual or auto) let say we have three instances

    sql1, sql2 and sql3

    SQL1= Principal
    SQL2= Mirror
    SQL3=  Wintness

    If after a failover SQL2 becomes Principal and SQL1 mirror but after some time SQL1 unavailable or SQL Service stopped. CRM still working, we can browse CRM !

    But you are correct we are Unable to open Deployment Manager due to -:)

    Jameel

    Tuesday, April 20, 2010 5:33 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Huh. Well that's odd - my understanding is that this subkey -

    HKEY_LOCAL_MACHINE/Software/Microsoft/MSCRM/configdb

    the value of which is a connection string, like this:

    Data Source=SQL1;Failover Partner=<var>SQL2</var>;Initial Catalog=MSCRM_CONFIG;Integrated Security=SSPI

    should redirect the web server to the new MSCRM_CONFIG db on the mirror server (which becomes the principal when the principal becomes unavailable). This should enable both browsing CRM and opening deployment manager on the web server.

    Anyone?

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    Tuesday, April 20, 2010 1:43 PM
  • Question
    Sign in to vote
    2
    Sign in to vote

    This may seem an odd suggest, but it may be worth a go. For some reason MS got their naming conventions a bit twisted for the 'Failover Partner' part of the connection string - depending on the provider it could be Failover Partner, FailoverPartner or Failover_Partner (see http://msdn.microsoft.com/en-us/library/ms366348(v=SQL.100).aspx)

    Based on the connection strings, it looks like the code to access the MSCRM_CONFIG and the <org>_MSCRM may use different data providers, as the latter includes the Provider keyword and the former doesn't.

    So, I'd try using FailoverPartner or Failover_Partner in the connection string for the MSCRM_CONFIG

     

    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Tuesday, April 20, 2010 2:47 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks David,

    I'll give it a shot. I appreciate the suggestion.

    Best Regards,

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    Thursday, April 22, 2010 1:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi David,

    Great idea, but it wasn't the answer. Whenever I replaced "Failover Partner" with "FailoverPartner" or "Failover_Partner" in the subkey at

    HKEY Local Machine/Software/Microsoft/MSCRM/configdb

    the application threw an exception saying the keyword "failoverpartner" or "failover_partner" was incorrect.

    Great idea though, I had high hopes for this one, especially as I knew that the connection string in the actual configdb specifies an OLEDB connection, which according to the article you referenced, should use the "FailoverPartner" key word.

    Any other ideas?

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    Friday, April 23, 2010 1:53 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi David,

    I have also checked your suggestion but not worked?

    David did you checked this  before?

     

    Jameel

    Friday, April 23, 2010 3:35 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    I'm afraid I've never tried using mirroring with CRM 4, so cannot be sure it will work with the MSCRM_Config database. We normally recommend either SQL clustering for automatic failover (though you've got the additional hardware costs), or log shipping with manual failover
    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Monday, April 26, 2010 5:07 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Would you please give me any step by step link for SQL Clustring for CRM 4.0

     

    Jameel

    Monday, April 26, 2010 7:13 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    When the principle becomes the mirror the database shouldn't be accessable on that server. Since crm works with a manual failover is it possible that your mirror server doesn't know that the P server has gone offline and isn't bringing it's database online?

    If you unplug your P server what is the M server doing?

    SJ
    Monday, April 26, 2010 2:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Good questions Sean. \

    Manually failing over frm the SQL Management Studio works because it's only failing the one database - ORG_MSCRM. The web servers are still connecting the MSCRM_CONFIG db on the principal at that point, even though they're pointing to the ORG_MSCRM db on the mirror. When we unplug the principal, the mirror becomes the principal, but its state is something like "principal, disconnected".

    The web servers aren't successfully switching over to the CONFIG_MSCRM db on the 2nd server. I've tried at that point to go into the registry on the web server, and change the "configdb" subkey from

    "data source="principal" failover partner = "mirror"

    to

    "data source="mirror"

    This allows me to open Deployment Manager, and if I got Reporting Services set up right on the mirror, I could probably manually change the db server for the organization. But the problem is that when the principal fails completely, the web servers will not look at the MSCRM_CONFIG on the mirror server.

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog

    Monday, April 26, 2010 3:25 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    So the mirror server is working,

    Can you tell which sql server the crm server is trying to connect to when it fails? If it's still going to the P server then your "failover partner" setting is being ignored, if it's going to the failover server then the error may shed some light.

    What if you use the ip , failover partner=ip

     

     

    SJ
    Monday, April 26, 2010 3:55 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    It's still looking at the Principal when the principal is down - the failover partner setting isn't working.

    Good question, I haven't tried the IP address. Maybe I'll give it a shot.

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    Tuesday, April 27, 2010 12:33 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Check out this KB ,

    http://support.microsoft.com/kb/941526

     

    SJ
    Tuesday, April 27, 2010 12:54 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi Andy,

     

    How are you getting on?

     

    We're running a CRM environment on Windows 2008 and SQL 2008 (all servers are VMWare ESX virtual machines)

    Mirroring has been configured in high-availability mode (and have ensured using a SQL alert and job that both databases failover in the event of one of them failing over).

     

    I experienced exactly the same problem as you and reproduced your steps exactly, YESTERDAY.

    The issue was the same as yours: manual failover using SSMS leads to the app server looking at the new principle. But auto failover (caused by disconnecting the NIC on the principle SQL server) leads to the app server not being able to connect to the mirror (the new principle).

    However TODAY, I cannot recreate it. Disconnecting the NIC on the principle does (in about 30 seconds) cause the application to successfully adapt to the database failover, and it can start serving pages again.

     

    Sorry, I realise this post is not that helpful. But the only thing did a lot yesterday (which is not evident from your posting) were iisresets. I guess you are, but you're doing an iisreset on the app server when you change the configdb registry key value?

     

    While I am pleased its working, I'd like to understand any frailties CRM may have with automatically failing over.

     

    If I find anything more out I'll let you know.

     

    Cheers,

    Ade.

    Wednesday, April 28, 2010 11:33 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    Beside all can any one tell how to configure reporting in CRM mirrord enviornment. i mean how to configure report server so reporting will Up all time
    like in case of failover...

     

     

    jameel

    Thursday, May 6, 2010 8:01 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Ade,

    Yes - multitudes of IIS resets. I'm glad to know that you saw some progress! No one has really even said that they've got it working the way we have it set up before.

    Did you change anything other than the 3 documented things:

    1. configdb registry subkey on the web server (a connection string) - to include "failover partner" attribute

    2. The "ConnectionString" column in the MSCRM_CONFIG db to include "failover partner" attribute

    3. The "MirroredSQLServerName" column in the MSCRM_CONFIG db to have the name of the mirrored server

    Glad it's working for you - that's a positive for me as well!

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    Thursday, May 6, 2010 5:17 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Finally got an answer to this, when working with MS Support.

    The reason we could not connect to the DB was because the connection was timing out too quickly. We added

    Connection Timeout = 80

    to the connection string in the "configdb" subkey in the webserver's registry, and also in the connection string that you modify in the MSCRM_CONFIG database.

    In other words, in both places where we modified the connection string to include the "Failover Partner" attribute as directed by this article http://support.microsoft.com/kb/952278/en-us, we also had to add the "Connection Timeout" attribute and set it to a higher number of seconds.

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    Friday, June 18, 2010 7:19 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Andy !

    Any Solution for Report Server, i mean failover partner for reporting services or any workaround  ??????

    because when principal server becomes mirror CRM Reports gives error!

     

    BR

    Jameel

    Tuesday, July 6, 2010 5:33 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Jameel,

    Yes - I believe the only way to address that is to install the Data Connector on your failover machine, and then when the failover occurs, use Deployment Manager to re-point the ORG's report server property to your failover machine. We're actually failing over the report server database as well, so we had to install the Data Connector while the failover machine was the principle.

    When doing this, you actually have to restore the encryption key or delete all encrypted data in the Reporting Services Configuration Manager every time you change the ORG's Report Server. So here are the steps we took:

    To Set Up (assuming Reporting Services is already set up):
    1. Fail the db so the backup server becomes the principal
    2. Install the Data Connector on the backup server
    3. Configure Reporting Services so that it works properly (for us, this involved deleting the encrypted data - you could probably restore a key here also).

    At this point, if your situation works the same as ours did, you can go ahead and update the ORG in deployment manager by going to Organizations, highlighting your organization and clicking "Disable", and then clicking "Edit", and replacing your primary Report Server name with the failover Report Server name, and then clicking "Enable" to bring the ORG back on line. This should allow Reporting Services to work. Moving forward, you just have to failover, restore the encryption key or delete all encrypted data in Reporting Services Configuration Wizard, Open Deployment manager and update the ORG's Report Server property, and maybe do an IIS reset.

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    Tuesday, July 6, 2010 12:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Andy !

    thanks for quick response. i have not cleared with certain things you mentioned.

    should we mirror reportserver and reportserver tempdb?

    we have following deployment !

    SQL1= Principal

    SQL2= mirror

    SQL3 = witness.

    mirroring is configured and working against crm organization.

    CRM1 and CRM 2 (hardware based load balancer) also running ssrs.

    as you did mentioned that re-point org's property not cleared to me. as you know when we edit any organization we can change the srs data connector url and sql server name

    by changing srs data connector url this will not work in any case because report servert db on sql1 ?

    BR

    Jameel

    ja
    Tuesday, July 6, 2010 4:26 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Exactly - which is why we mirrored it (the Report Server db). We don't need to mirror tempdb. The process of changing the Report Server in deployment manager is necessary because we didn't have any clusters between the SQL servers - if we did, I don't know that mirroring would be necessary. So we had 2 separate SSRS servers with the data connector installed on both.

    You will need to have the report server db on the 2nd sql server for this to work the way we did it.

    Web: http://invoc.net Blog: http://invoc.net/CRM_BPOS_Blog
    • Proposed as answer by MEverald Tuesday, August 30, 2011 9:06 PM
    Tuesday, July 6, 2010 5:17 PM