CRM Online vs. CRM On Premise

The CRM Online platform has really come along way over the years coming closer and closer to its on premise counterpart. There is however one aspect which poses significantly more challenges than the on premise version. Specifically this is the limitation that the CRM online data cannot be accessed directly via SQL.

This limitation typically impacts the following two fairly common-place requirements:

1. Reporting

For CRM Online you are forced to use FetchXML to retrieve data rather than SQL. And while most report requirements can be satisfied using this technology it requires a little more effort than standard SQL reporting. Frankly, it is far easier to construct a query in SQL than FetchXML and the comparative difficulty between the constructs only increases with increasing complexity of the query required (or you can break the report down differently to avoid more complex query requirements when using FetchXML - "tricks" you wouldn't normally need to consider when using straight SQL). And of course for a complex reporting requirement, FetchXML may not be flexible enough to extract the data in the way that is required making it necessary to perhaps "flatten" the data using plugins or using a tool such as Scribe Replication Services before reporting against. This as compared to on premise reporting where you can use techniques such as dynamic SQL where the sky is really the limit.

2. Bulk Updates

In the on premise version of CRM, you have the ability to easily make bulk updates to your data. Though running direct SQL updates is officially "frowned" upon by Microsoft, this is done as a form of CYA. Allow me to explain - the integrity of the CRM database is maintained via the web services business layer. The minute someone is allowed to "tamper" with the underlying data directly all havoc can break loose. There simply is no way to protect against someone who is intent on corrupting the database. Not that anyone will do that intentionally of course but someone who is insufficiently knowledgeable in SQL and/or the CRM database may unwittingly introduce "problems" by trying their hand at SQL. And Microsoft of course cannot control who would be performing such updates so therefore they issue a blanket statement saying that the direct updates to the CRM database is unsupported (I once had the rather amusing case with Microsoft support where the resolution required running a script that they provided with a cautionary note that direct SQL updates are not supported - in the same email!).

However if you are sufficiently knowledgeable and take the necessary cautionary measures - it is difficult to argue against performing direct SQL updates with a straight face. The first reason is due to the relative ease that such updates can be constructed and run. And the second and often more valid reason is due to the speed in which it will be applied. If you need to run an update in a table with millions of rows - the difference will be between a few minutes and potentially a few days via web service.



Please don't get me wrong - I am not at all down on the CRM Online option. In fact, I think it is actually a pretty amazing platform and has some significant upsides from the on premise version - especially for smaller companies where the playing field is levelled in that they also have access to an enterprise level strategic software solution at a very affordable price and without the necessity of having their infrastructure and/or IT department. The barrier to entry was significantly lowered when CRM first offered its online solution. However, when evaluating the merits of going on premise vs. online for a given company's requirements - I would definitely factor in the above against the stated long term requirements of the undertaking.

CRM 2011: Changing SQL Port

By default the CRM installation wizard uses the default SQL port i.e. 1433. I don't believe you are able to specify a different port as part of the initial installation, so you'll need to ensure this port is left open for this step. Once CRM is installed you can modify the SQL port that it uses.

To do so you can follow the steps described in this post which describes the steps required to do so for a CRM 4.0 installation. The steps are only marginally different for CRM 2011.

The difference lies in the modifications required to be made to the MSCRM key in the registry. You only need to modify the configdb string. The others (database, metabase) are no longer relevant for CRM 2011.

I'd recommend the following steps in terms of implementing the above change to isolate the port change in case of issues:

1. Open port 1433
2. Install CRM and confirm installation
3. Make port changes above in the CRM installation
4. Make sure the new port is opened
5. Restart IIS on the CRM server and restart the SQL service on the database server
6. Refresh CRM to ensure it is still running. If not, you did something wrong.
7. Block port 1433
8. Refresh CRM to ensure it is still running. 

TDE and CRM

Although the title of this post is "TDE and CRM" the reality is that it could be titled "TDE and SharePoint" or "TDE and Any App Running on SQL Server". The point is that the CRM database like any other application running on SQL can be encrypted using TDE. There really are no specific settings as far as the requirements for setting up TDE for CRM.

TDE stands for Transparent Data Encryption and became available in SQL 2008 R2 Enterprise. TDE essentially protects your "data at rest" i.e. it protects your database from being stolen. As in backing up the database and restoring it in some other location where the contents can be analyzed via SQL.

Essentially when you apply TDE you are applying a certificate used to encrypt your data to the back-end database files (it only encrypts to the database files, when you access a TDE database via SQL there is no difference to the usual experience). You will only be able to restore the database to another database server if that server has the same TDE certificate applied. Otherwise you will be prevented from restoring the database - you will receive the "cannot find certificate with thumbprint" error:

The backed up database file will be similarly unintelligible.

As the name itself implies, TDE is transparent. Meaning this is configuration is applied on the database itself and any applications with back-end SQL databases running on top of it will be oblivious to the TDE. The only impact will be a slightly increased level of CPU utilization on the database server as it handles the data encryption for the SQL transactions.

In light of this, it is difficult to not recommend using TDE. It is simple to apply, it is completely transparent to applications, it protects your data... with the only downside being higher CPU utilization which for most databases won't make a difference (assuming the old adage "use it or lose it") and easily compensated in cases where the additional churn pushes your CPU over the edge.

Below are some scripts for setting up TDE on a SQL 2008 R2 Enterprise server:

/* If you wish to copy the certificate from an old server you will need to back it up */
--Backup the certificate from the 4.0 database server:

USE master
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\TDECRMCERT.BAK'
WITH PRIVATE KEY ( FILE = 'C:\TDE.key',
ENCRYPTION BY PASSWORD = 'EncryptionPassword' )

/* On a new server you will need to create a certificate using the backed up one from the old server.
   This procedure assumes you copied the backed up certificate to C:\ root on the new server
 */


-- Create a master key. NB: Record the password for future reference.
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

-- Backup the master key. NB: Store the backup file and record the password for future reference.
USE master;
BACKUP MASTER KEY TO FILE = 'C:\Master_Key.bak'
ENCRYPTION BY PASSWORD = 'MasterKeyBackupFilePassword'

-- Create a certificate by importing the certificate backed up in the first step.
USE master
CREATE CERTIFICATE TDECRM 
    FROM FILE = 'C:\TDECRMCERT.BAK'
    WITH PRIVATE KEY (FILE = 'C:\TDE.key',
    DECRYPTION BY PASSWORD = 'EncryptionPassword')

-- Backup the certificate. If you’re using the imported certificate this step is optional since the file 
-- you used to create the new certificate is already the backup file so you’ll just be recreating this file. 
-- NB: Store the backup file and record the password for future reference.

BACKUP CERTIFICATE TDECRM
TO FILE = 'C:\TDECRMCERT.BAK'
 WITH PRIVATE KEY ( FILE = 'C:\TDE.key',
ENCRYPTION BY PASSWORD = 'EncryptionPassword' )

-- For each database you wish to restore to from a TDE enabled database you will need to apply the certificate
-- and enable encryption.
USE Vanilla_MSCRM
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
Encryption By Server Certificate TDECRM
GO

ALTER DATABASE Vanilla_MSCRM
SET ENCRYPTION ON

SQL Server Error

After having authenticated to CRM using the newly released Plugin Registration Tool I was then confronted with another issue namely that I could not in fact unregister the plugins. I imagine I would have encountered a similar error were I trying to register a new plugin.

Looking on the CRM server I discovered the issue was the fact that the log file for the MSCRM_CONFIG database was full.

Truncating the log in SQL 2008 is a slightly different affair than it was in SQL 2005. Long story short - you can use the following script to truncate your MSCRM_CONFIG  database:


USE MSCRM_CONFIG;
GO
ALTER DATABASE MSCRM_CONFIG
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (2, 1);
GO
ALTER DATABASE MSCRM_CONFIG
SET RECOVERY FULL;
GO

In order to not have this issue re-occur, I'd recommend changing the autogrowth for the MSCRM_CONFIG  database as shown in the screenshot. The MSCRM_CONFIG database should be a relatively static database therefore essentially setting it to "unrestricted growth" should be fine.