SQL Database facts of Dynamics CRM – In Details

SQL Server is the back end database of the Dynamics CRM web application. The complex database architecture ensures all the security features .

Follow this link how to extend Dynamics CRM application in a detailed explanation. Here we will discuss how tables and views are organized in SQL level when CRM is installed.

Sometimes we directly have to access the SQL data without calling CRM SDK API, so how Dynamics CRM gives this flexibility and how the security feature also behaved the same way although we retrieve data directly from CRM SQL database.

Let’s explore the database area of Dynamics CRM and how to Use SQL and filtered views to retrieve data for reports and all other aspects of SQL server details.

CRM SQL Databases

The Dynamics CRM architecture is so complex. and below is a schematic dynamics CRM application architecture.

When a Dynamics CRM application installed at-least two database gets created in SQL server

• MSCRM_CONFIG
• OrganizationName_MSCRM

Additionally Microsoft Dynamics CRM requires the default master and msdb SQL Server databases for database services and the default report server SQL Server databases for Reporting Services.

• ReportServer
• ReportServertempdb
• master
• msdb

MSCRM_CONFIG

• license key you used for the installation is stored within the MSCRM_CONFIG database and some important information.

  USE MSCRM_CONFIG

 SELECT LicenseKeyV5RTM,*

FROM ConfigSettings

• Deployment Administrator information are stored in this database. Check how to add a deployment administrator in Dynamics CRM here.
• This database stores the Organization information specific to each CRM tenant in the deployment.This database is needed to get e.g. the organization settings.The config database contains deployment settings and user mappings and information related to crm installation

OrganizationName_MSCRM

• The Metadata is stored in the [OrgName]_MSCRM database.
• All CRM entity and field metadata are stored in this database.
• All System and user views and SQL jobs are stored in this database.
• The metadata information can be retrieved using CRM SDK API but we can directly retrieve data from SQL server using normal select query operation but direct access of CRM data from SQL server is not supported as the security model gets bypassed in this way and this is not supported by microsoft.
• Here are some points why we should not access CRM data directly from database.
  • Direct changes to the SQL table are unsupported, this will result in Microsoft offering no or limited support if your CRM installation goes wrong. You can read more about why you should not create unsupported changes here
  • When you use the CRM SDK to change a value it could update other fields and values, direct changes to the SQL will not
  • There is no validation on the SQL tables, you could easily put in an incorrect value
  • If you write SQL stored procedure directly against the CRM database tables this could stop working when a rollup or service patch is applied to your CRM organisation
• Microsoft introduced filtered views which solves all these problems. we will discuss on this filtered views in Views section.

ReportServer and ReportServertempdb

Reporting Services is usually considered at most as just the engine for executing and rendering reports. However, it also has its own SQL database that contains information that can be useful

• This database server stores all data that are required for reporting
• The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

Master and MSDB Database

Microsoft Dynamics CRM requires the default master and msdb SQL Server databases for database services

• Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
• MSDB database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

CRM SQL Tables

There is two tables for each entity. If your entity name is new_sample then below tables are created in org_mscrm database

1. new_samplebase
   1. this table contains all out-of-the-box fields like modifiedon, createdby, createdon, modifiedby etc.
2. new_sampleextensionbase
   1. this table contains all custom fields

NOTE :

In CRM 2013 and latest version of CRM this two tables are merged to one table.

The merging  gives below benefits

• Fewer Joins
• Fewer Locks
• Higher efficiency in queries and views
• Streamlined design
• Less data overhead
• Redundant keys are removed

SavedQuery table

• This entity stores a saved query against the database. This is used for both views and for Microsoft Dynamics CRM for Microsoft Office Outlook with Offline Access filters and templates. The schema name for this entity is SavedQuery.
• The fetchxml attribute stores the query

UserQuery tables

• Saved queries are business entities that define the parameters and criteria of a Microsoft Dynamics CRM database search. Saved queries support cross-entity searches.
• The advance find queries which are saved are stored in this entity. the query will be stored in fetchxml attribute of this table.

• A user query, called a saved view in the application, is owned by an individual user, can be assigned and shared with other users, and can be viewed by other users depending on the query’s access privileges. This is appropriate for frequently used queries that span entity types and queries that perform aggregation. A saved query, called a view in the application, is owned by an organization making it visible to all users in the organization. Saved queries (views) are used for both views defined for an entity and for filters and templates for Microsoft Dynamics CRM for Outlook.

CRM SQL VIEWS

Different types of views are available in CRM database.

Filtered Views

For every entity a filtered view is created in org_mscrm database. If your entity name is new_sample then below view is created in org_mscrm database. Here is an blog you should read.

1. filterednew_sample

Every entity has a filtered view.

• Filtered views have the same name as the CRM table but with the word Filtered prefixing it. Not all Entities have filtered views because some entities are system entities
• Filtered views will be automatically created for any custom entities you create.
• In CRM 2011 the filtered view contained all the fields in the base and the extended table.
• CRM filtered views exist for individual activity types e.g. (email, task, letter, etc)

        Advantages of Filtered views:

• Filtered views won’t break after patches, services releases and maybe upgrades
• Security is embedded in the view
• All the column names are in lowercase
• Filtered views are automatically created and updated for custom entities
• Datetime fields are held in datetime and UTC
• Drop down values are held in filtered views

a good article is published here. another article in technet.

Accessing a SQL Database from a Microsoft Dynamics CRM Plug-in

SQL Database Optimization facts and tools, Whitepapers, SQL JOBs, Clean up jobs, scripts, views

A fantastic topic on SQL Deadlocks in CRM

Data management views (DMV’s)

Dynamic management views store SQL transactional information which can be used to monitor health, performance and diagnose problems. DMV’s are extremely helpful in particular around performance-tuning. The information is stored until you restart SQL server, after a restart all is reset to 0. A nice article is here Data Management Views

Cheers..