January 22, 2013
CRM Outlook Client - Best Practice for Add-In and SQL Server
We had an challenge at a client that I wanted to share so that this challenge, which could have been difficult to resolve, does not happen during a future rollout. The client spent a substantial amount of time creating scripts that would automatically install and configure the Outlook client on user’s machines. Since most users in their domain do not have sufficient rights to do the installation, this was found to be the most secure and consistent way to roll out the software. After significant testing, the processed was ironed out and the rollout was begun.
For the most part, things went as planned but on a couple dozen new computers, the installation would appear to go fine but when the user tried to Go Offline with the Outlook client, an error would appear as it tried to set up the offline database.
The short answer to how this was resolved is that we had to uninstall the Add-In, uninstall SQL Server Express and reinstall both products. The issue arose because when the SQL Server Express Addition was originally installed, the computer had one name (i.e. ComputerA) and then after the installation, was renamed (i.e. WorkstationA). It would be enough to say that it is critical that after the installation of the Add-In with offline mode enabled, the computer name cannot be changed without causing connectivity issues but I thought it important to give you the “Why” behind the “What”.
A little bit about SQL Server
SQL server, regardless of which edition, is always installed into 1 or more instances. If you choose the default installation, a Default Instance (unnamed) will be created that can be found by simply using the computer name in the connection string. You can also choose to install multiple named instances so that from one SQL server computer you can have multiple instances of SQL server, each with its own security and operating parameters. This is roughly synonymous to having multiple organization in an installation of Dynamics CRM.
When an instance of SQL server is installed, there is an entry in the sys.servers table. This is one of a number of tables that are used for the internal workings of MS SQL Server and in this case contains metadata about the instances that are a part of this server. If we create a SQL server computer called SQL1 and then two named instances called instance1 and instance2, the sys.servers table will contain records relating to SQL1\Instance1 and SQL1\Instance2.
These values are used when a client tries to connect to the named instances as a lookup for the internal connection parameters needed to access data in the given instance. If I change the name of the computer from SQL1 to SQL2, then when a client subsequently access this table to get to Instance1, the path of the server will still be SQL1\Instance1 in this metadata and the client will not be able to connect to the database.
Back to the Dynamics CRM Add-In
The offline mode of the CRM Add-In creates an instance called CRM in SQL Server Express that is installed when the Add-In is originally installed. This instance is entered in the sys.servers table as [machine-name-at-the-time-of-install]\CRM. As pointed out above, this data would no longer be accurate if we were to change the name of the computer.
Microsoft has published a work around for fixing this metadata for a full SQL server install and it could be implemented in the Express edition though changing this offline database is not supported. I have included the link here but think the best course of action is the following.
- Make it clear that after installing the Add-In, any changes to the naming of the computer will have adverse effects.
- If a computer is renamed, the Add-In and SQL Express Edition should be removed and reinstalled under the new computer name.
If you must, you can connect up to the MSCRM_MSDE.mdf file (Location in windows 7: Drive: \users\<Username>\local\Microsoft\MSCRM\data\) using a data connection in Visual Studio and enter the queries discussed in the link but I would only do this if there was data in the offline files that had to be synced back to CRM (i.e. the client went into offline mode, changed the computer name, and is now unable to sync those changes back to CRM) and I would make sure I had a current back-up of CRM.