Should We Use SQL Standard or SQL Enterprise With Microsoft Dynamics CRM?

As part of my role as CRM Technical Specialist, I help our clients who use Microsoft Dynamics CRM On Premise design their server architecture. Any good architecture design is built to handle the expected initial usage volume, along with expected growth, and offer good long term application performance.

A frequent question I get is “should we use SQL Standard or SQL Enterprise Edition?”

Both SQL Standard and SQL Enterprise will work with Microsoft Dynamics CRM, and you will get full functionality in the application with either edition.

However, there are three main advantages that Enterprise Edition will give you specific to CRM:

1. Data compression: The performance of CRM is dependent on fast disk I/O from SQL server, as the CRM application runs many small SQL queries under normal operation. SQL data compression offers the fastest possible performance by reducing the amount of disk I/O. It can also significantly reduce the size of your database. With SQL Standard you will still probably get acceptable speed, but sql compression can give you faster performance.

2. Encryption: CRM supports transparent data encryption, and that is only offered with SQL Enterprise. This is good to know if you have regulations requiring sensitive data to be encrypted.

3. Quick restore: SQL Enterprise offers data mirroring and quick restore that can be very useful for your disaster recovery strategy.

Those are the main three reasons why our customers who want true high availability performance usually go with SQL Enterprise. There are also some new capabilities in SQL Enterprise 2012, such as business intelligence features like PowerView and the new AlwaysOn features that are supported for use with CRM. That is why our customers who view CRM as a mission critical high availability application go with Enterprise Edition of SQL Server. However, that is not for everybody.

My recommendation is always based on a client’s environment and actual requirements. For small to midsized environments with normal uptime requirements (<250 users), SQL Standard is usually OK.  Consider the performance benefits vs the increased license cost.

The following resources will help:

http://www.microsoft.com/sqlserver/en/us/editions.aspx Comparison of the different version of SQL 2012

http://www.microsoft.com/en-us/download/details.aspx?id=8438 Microsoft white paper that details how features like transparent data encryption and database compression can improve performance and make your data more secure.

4 Steps to Improve Data Consolidation Using Microsoft Dynamics CRM

We often hear from business executives about their concerns with duplicate systems and duplicate data sets. We also hear the desire to create or improve “a grand all-encompassing data warehouse” so executives can better understand customer trends as well as all sorts of business informatics. Mostly, there is a desire for one easy to use consolidated database – the one single database of truth for all customers, contacts, and related information AND accessible across all devices - phone, tablet, laptop.

Here are four steps to improve data consolidation and get to that one database of truth using Microsoft Dynamics CRM.

1. Understand why data consolidation is important
2. Recognize reasons why data silos occur
3. Catalog all the data sets
4. Form a strategic plan on how CRM will be leveraged

 

STEP 1 - Understand Why Data Consolidation is Important

The first step is to understand the value of good customer data. Many executives would already agree that consolidating customer data is critical in business today but there are some that are hesitant to invest in the endeavor or they are unsure where to start. There are many valuable advantages to data consolidation, but in a nut shell, businesses are more effective, more competitive, and have greater insight when they can see one shared customer record that has a deep 360 degree view. This view could include all customer activities, purchases, visits to your website and where they went, accounting information from the ERP, real time data from data aggregators, customer complaints and their resolutions, and yes, the customer’s correct email. That is at the record level. Good data then rolls up for quality analytics. Reporting on your key performance indicators and customer buying trends and even predictive buying just got a whole lot easier now that everything is in one place. Keep in mind, in your current state, you probably have most of this data already, but it is just spread out in too many duplicate databases and Excel files to be usable.

 

STEP 2 - Recognize the Reasons Why Users Create Data Silos

The next step is to try to stop data silo creation. There is usually a central system in place but then users remove the data they want to an Excel file and continue to update that Excel file outside the central system. Why? Why are users compelled to create Access databases and Excel files? There can be many reasons but usually it is because the central system is hard to use and it is just easier using Excel or their own Outlook. So how do you stop users from creating their own data sets?  Improve the user interface is one way and that is where Microsoft Dynamics CRM can help. Although Microsoft CRM uses a powerful Microsoft SQL Server database to handle Big Data, it is the user Interface of Microsoft CRM that will often help reduce data silo creation. Microsoft CRM has the potential to be designed putting the user first – meaning the interface is clean and purpose driven. It also helps that Microsoft Dynamics CRM can be accessed from within the very tools that frequently drive silo creation to begin with – in this case Excel and Outlook. All of CRM can be accessed from Outlook and there is one click in CRM to bring data to Excel.

 

STEP 3 - Identify and Catalog all the Data Sets

Next, start to identify all your data. Data you have and even the data you don’t have. Catalog all the In-house data like main central systems, the many rogue Access databases and Excel files, as well as website databases and SharePoint Lists and so on. Just focus on the data that is being managed and updated away from a central database. Think also about the data you may not have now but could get from an online subscription to a data aggregator such as InsideView to sync real time business and social data or Trillium to validate and clean your data for total data quality.

 

STEP 4 - Form a Strategic Plan for Data Consolidation and How Best to Leverage CRM

The last and most important step is to form a long term strategy plan. This is where some companies may need assistance. Without going into too much detail, the plan should attempt to include what data will be migrated and what will be integrated.

· Migration: With data migration, this is the data that will be completely moved permanently to live in CRM. These aren’t just the rogue Excel files; this could potentially be retiring some completely separate business applications – meaning move the data along with the functionality and business process to CRM using the innovative xRM approach to duplicate functionality where logical.

· Integration: What data then gets integrated in and out of CRM? Is the integration one way (asynchronous) or two way (synchronous)? What are the outside data sources and services that will be utilized? What data integration tools such as Scribe may be needed?

The strategic plan may also include new reporting needs, how data will be displayed across devices, and what users get to access and not access based on their security. As you can see, having a well thought out plan divided into logical phases will provide the long term blue print and framework for continued data success.

If you understand the value of one consolidated database and you know why users make data silos and you have cataloged all your data sets and then formed and documented a well thought out plan that leverages Microsoft Dynamics CRM, well then, you should be on your way to improved data consolidation.  

Transform Multi-Select / Delimited fields into CRM Many-To-Many Relationships.

In many source systems (such as salesforce.com), the data from multi-select pick-lists is stored in a memo field as a long list of semicolon delimitated values:

ID	Value
1	Option1;Option2;Option4
2	Option1;Option3

When upgrading to Microsoft Dynamics CRM, you may choose to convert those individual fields into N:N (many to many) relationships, but first you’ll need to de-concatenate those values into multiple rows along with the id of the source record.

ID	Value
1	Option1
1	Option2
1	Option4
2	Option1
2	Option3

How To:

The following example code demonstrates converting a semicolon delimited text field on the opportunity record into a list that is then matched to a related entity and any missing many-to-many relationships are identified for creation.

Return All-but-One Duplicate Records in CRM using SQL

Frequently when importing / scrubbing data within CRM, identifying duplicates to remove or ‘flag’ may be needed.  It’s helpful to have a query that will return all the duplicates in the database – but exclude a single ‘surviving’ record.  (i.e. Return all-but-one of the duplicated records.)

This query is one of my go-to query patterns – I rarely, if ever, use it as-is, but will modify it to fit the particular entity / scenario I need to investigate.

The returned record set might used as the source for a Scribe Insight job to either ‘flag’ or inactivate or delete the duplicates depending on the situation. – Of course you’ll need to take into account the record’s relationship to other entities etc.

Microsoft Dynamics CRM Anywhere

This is a great video overview by Microsoft product manager Reuben Krippner covering what is coming in the CRM 2011 R8 update, including cross-browser support, mobile client, and other enhancements coming in Q2 2012.  The video includes demonstrations of the mobile client and some of the new business intelligence functionality.

Microsoft Dynamics CRM SQL Sizing Rules of Thumb

SQL Server sizing is critical for good CRM Performance.  Maybe you are standing up a new environment and wanting to ensure that your SQL environment will handle your estimated load of data and users, or maybe you have an existing CRM deployment and you want to enhance it to improve performance.

The following are rules of thumb to follow when sizing your CRM database server.  These are general recommendations—you will want to take into account your expected user count and transaction levels, but the following principles should be helpful guidelines to follow when estimating your sizing requirements.

Microsoft Dynamics CRM 2011 Standalone Sandbox Using VirtualBox Part 1

One of the best approaches to learn CRM is to have a sandbox.  A lot of recommendations out there suggest having your IT administrator provide you with a CRM sandbox to play in.  That is fine and is surely a good idea, but personally creating a sandbox will certainly give you the satisfaction of getting your hands dirty and will give you a little respect with your colleagues.  So here we are going to look at getting a basic MS CRM 2011 up and running with Oracle’s VirtualBox.

Top Five Tips for Troubleshooting Generic SQL Error Messages in Microsoft Dynamics CRM 2011

When working with Microsoft Dynamics CRM 2011, you may occasionally see an error message that says “A SQL Server error occurred.  Try this action again.  If the problem continues, check the Microsoft Dynamics CRM Community for solutions or contact your organization’s Microsoft Dynamics CRM Administrator.”

When you get these error messages, you will want to focus your troubleshooting on the database level.  The following are five of the most common causes of these SQL errors, and good things to check when troubleshooting these types of issues:

1.  Check the event log on both CRM and SQL servers.  The event log will typically give you a more detailed error message that will help you troubleshoot the issue more precisely.

2.  Check the SQL server--are you low on disk space on the SQL server, or are there possibly limits to how much your Microsoft CRM database (*_MSCRM) can grow?

Even if you are on CRM online, this may be applicable--at one time I received generic SQL errors with CRM Online, and it turned out there was a limit on the amount of growth on our database at the Microsoft hosting center.  Call Microsoft support if this is the case.

3.  Increase the OLEDBTIMEOUT registry setting (or create it if it doesn't exist).  See http://support.microsoft.com/kb/918609 for more details.

4.  Are there any unsupported database hacks like triggers that are running on the base tables, or are there any integration processes reading or writing to CRM that could be creating database locks?  These are the kind of things that can create generic errors in CRM, especially if they are “unsupported.”

5.  Are there any big/complex reports being run at the time you got the errors, or reports reading from CRM views or tables without "with (nolock)?"  These can cause database locks and result in SQL errors in CRM.