Bagikan melalui

Run the base and extension table merge as a separate operation

  • Artikel

 

Applies To: Dynamics CRM 2013

As part of the upgrade from Microsoft Dynamics CRM Server 2011 to Microsoft Dynamics CRM Server 2013, the entitynameBase and entitynameExtensionBase tables in all organization databases will be merged into a single entitynameBase table. Reducing the number of tables in the organization database improves overall performance of transactional operations in Microsoft Dynamics CRM.

However, for enterprise customers with organization databases having complex customizations or solutions that create large tables spanning hundreds of columns, the table merge may take several hours to complete. To understand the impact of the table merge process, use a test environment to determine how much downtime it may cause. You may want to defer the table merge until sometime after you upgrade to Microsoft Dynamics CRM Server 2013.

Important

You can’t defer the table merge if you select the organization during Microsoft Dynamics CRM Server 2013 Setup.

Although you can continue to use Microsoft Dynamics CRM 2013 without merging the base and extension base tables, we recommend that you complete the merge as soon as possible. This can help improve application performance and will allow you to upgrade to the next major version when it becomes available.

In this topic

Do the table merge as a separate operation

Determine how much ExtensionBase table data will need to merge

Determine the entities that weren’t merged in an organization database

Run the table merge tool

Do the table merge as a separate operation

To defer table merger, follow these steps:

  1. Don’t upgrade any organizations during Microsoft Dynamics CRM Server 2013 Setup. During Microsoft Dynamics CRM Server 2013 Setup, on the Upgrade to Microsoft Dynamics CRM 2013 page, you must leave the choice of the organization database as <None>. If you select an organization database, Setup attempts to merge all entity Base and ExtensionBase tables in that organization. Even if you set the Windows registry MergeBaseAndExtensionTables subkey to bypass the table merge before you run the upgrade, the merge will still occur because Setup removes the subkey. This sets the table merge back to the default, which is to merge the base and extension tables during organization upgrade.

  2. Configure table merge bypass. To do this, add the following subkey to the Windows registry on the computer where Microsoft Dynamics CRM Server 2013 Deployment Manager is running.

    Important

    To defer the table merge, you must set this Windows registry subkey before you upgrade the Microsoft Dynamics CRM 2011 organization databases.

    • Location: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\MergeBaseAndExtensionTables

    • Type: DWORD (32-bit)

    • Value: 0

  3. Upgrade existing organizations by using Deployment Manager. To do this, start Deployment Manager, right-click the organization that you want to upgrade, and then click Upgrade. During the organization upgrade the table merge process won’t be invoked.

  4. Merge the tables during a suitable maintenance window. To run the table merge tool, follow these steps:

    1. Configure table merge back to true. After all the organization databases that you want to bypass the table merge are upgraded, set the table merge back to true so that the merge tool will merge the base and extension entity tables. To do this, change the following subkey in the Windows registry that was created or modified previously or on the on the computer where the table merge tool is to run.

      • Location: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\MergeBaseAndExtensionTables

      • Type: DWORD (32-bit)

      • Value: 1

    2. Optional: Set custom index re-creation. By default, custom indexes that are configured for base and extensionbase entity tables will be dropped (removed) during the table merge process. To allow the table merge process to try and re-create the custom indexes, create the following subkey in the Windows registry on the computer where the table merge tool is to run.

      • Location: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\EnableRecreateCustomIndexes

      • Type: DWORD (32-bit)

      • Value: 1

    3. Optional: Set transaction log truncation. Merging data from the entity extension tables into the entity base tables is done in transacted batches as an update statement. SQL Server logs those transactions in a transaction log (Tlog), which may significantly grow in size during the table merge. To mitigate transaction log growth, the transaction log is truncated after each entity merge operation completes. By default, transaction log truncation is turned on and truncates when the log growth during entity table merge exceeds 1 GB. If table merge performance is poor because of transaction log truncation, you can increase the transaction log size or disable truncation (not recommended).

      Turn transaction log truncation on or off

      • Location: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\MergeTruncateTlog

      • Type: DWORD (32-bit)

      • Value: 1 (true) the default. Any other value is false and will disable transaction log truncation.

      Set the truncation threshold

      • Location: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\MergeTruncateTlogGrowthValueInMb

      • Type: DWORD (32-bit)

      • Value: 1024 (MB) the default. In megabytes. Determines the threshold for truncation of database transaction log growth.

    4. Disable the organization before you run the table merge tool. To do this, start Deployment Manager, click Organizations, right-click the organization that you want to merge tables, and then click Disable.

    5. Run the table merge tool. You can run the table merge to merge all entity tables or specify a single entity or several entities to complete the table merge in batches.

      • Merge all entities at one time. By default, when you run the entity merge tool, all entity Base and ExtensionBase tables are merged.

      • Merge specific entities. Entity merges can be conducted in batches of entities over multiple maintenance windows.

      For instructions on how to run CrmMergeBaseAndExtensionTableTool.exe, see Run the table merge tool later in this topic.

    6. After the table merge is completed, enable the organization so that it may be accessed by users.

Determine how much ExtensionBase table data will need to merge

When you have very large datasets, you may need to delay the table merge process until after the upgrade in order to complete the upgrade quickly. You can use a SQL query to determine which tables are the largest and therefore will take the most time to merge.

The following statement returns the total number of records, total number of fields, and row size (KB) in the ExtensionBase table for each entity type. Use this information to help make decisions, such as grouping ExtensionBase tables for batch merge operations.

SELECT e.Name as EntityName, e.ExtensionTableName, COUNT(AttributeId) AS CustomAttributeCount, SUM(spa.rows) AS TotalRecords, ps.in_row_data_page_count * 8 as ExtensionTableSizeInKB
FROM MetadataSchema.Attribute a
RIGHT OUTER JOIN EntityView e on a.EntityId = e.EntityId and a.IsCustomField = 1
INNER JOIN sys.partitions spa ON object_ID(e.ExtensionTableName) = spa.object_id
INNER JOIN sys.dm_db_partition_stats ps on ps.partition_id = spa.partition_id
WHERE e.IsActivity = 0 
AND e.ExtensionTableName IS NOT null
AND e.IsIntersect = 0
AND e.IsLogicalEntity = 0
AND [spa].[index_id] = 1
GROUP BY e.Name, e.ExtensionTableName,ps.in_row_data_page_count
ORDER BY e.Name

Determine the entities that weren’t merged in an organization database

Run this query against the organization database to find out the entities that haven’t been merged.

SELECT e.Name, e.ExtensionTableName
FROM EntityView e
where e.IsActivity = 0 and e.ExtensionTableName is not null
    and e.IsIntersect = 0
    and e.IsLogicalEntity = 0
order by e.Name

An “out-of-the-box” Microsoft Dynamics CRM 2011 organization returns 35 entitynameExtensionBase tables.

Run the table merge tool

The CrmMergeBaseAndExtensionTableTool.exe tool is included with Microsoft Dynamics CRM Server 2013 and is installed in the c:\Program Files\Microsoft Dynamics CRM\Tools\ folder as part of the Deployment Administration Server role. The table merge tool is a console application and must be run from a command prompt where the Deployment Administration Server role is installed or can be run on the computer where the instance of SQL Server is running.

Important

Don’t run the table merge tool while users are accessing the system. Disable the organization by using Deployment Manager before you run the merge tool.

You must set the Windows Registry MergeBaseAndExtensionTables subkey back to 1 (true) for the table merge tool to merge entity tables.

Don’t close the command prompt window while the table merge tool is running. Doing so cancels the merge.

You can run the table merge tool multiple times and it will only merge the base and extension tables that you specify and only those that haven’t already been merged.

To run the table merge tool, you must have the following permissions.

  • Microsoft Dynamics CRM Deployment Administrators group membership.

  • Be a member of the Administrators group on the local computer where the instance of SQL Server is located that will be used to store the Microsoft Dynamics CRM databases.

  • Db_owner permission on the organization database that will be merged.

Syntax

CrmMergeBaseAndExtensionTableTool /s: /o: [/b:] [/log:] [/u:] [/p:] [/e:,...]

Example that merges all entity tables:

CrmMergeBaseAndExtensionTableTool /s:CRMSQLServer /o:Contoso_MSCRM /log:c:\Logs\mergetoollog.txt

Example that merges only the account and contact entity tables:

CrmMergeBaseAndExtensionTableTool /s:10.125.156.135  /o:Contoso_MSCRM /e:Account,Contact /log:c:\Logs\mergetoollog.txt

Parameters

For usage and a list of parameters, type CrmMergeBaseAndExtensionTableTool at a command prompt and press ENTER.

/s:[SQLServer]

  • This required parameter specifies the name of the instance of SQL Server. An IP address can be used instead of a computer or instance name.

/o:[OrgDBName]

  • This required parameter specifies the organization database name. You must also include the _MSCRM suffix.

/b:[BatchSize]

  • This optional parameter specifies the batch size number for merging tables. The default is 50,000 rows.

/log:[path]

  • This optional parameter records information to a text file. If this parameter isn’t specified, information is only displayed in the console window where the tool is running.

/u:[SQLLogin]

  • This optional parameter specifies that a SQL Server login will use SQL Server Authentication for the connection to the computer running SQL Server. If not specified, Windows authentication is used.

/p:[SQLLoginPassword]

  • Specifies the SQL Server login password when you use the /u parameter.

/e:[entityname1],[entityname2]...

  • This optional parameter specifies which specific entities to merge. To specify an entity name, use the name of the table. There are 35 entities included with Microsoft Dynamics CRM. For a list, see “Out-of-the-box” entities.

    Note

    If this parameter is not specified, all entities are merged.

“Out-of-the-box” entities

Account, BusinessUnit, Campaign, Competitor, Connection, Contact, Contract, ContractDetail, CustomerAddress, Equipment, Goal, Incident, Invoice, InvoiceDetail, KbArticle, Lead, List, MailMergeTemplate, Opportunity, OpportunityProduct, PriceLevel, Product, Queue, QueueItem, Quote, QuoteDetail, ReportCategory, SalesLiterature, SalesOrder, SalesOrderDetail, SharePointDocumentLocation, SharePointSite, SystemUser, Team, Territory

See Also

After the upgrade
Troubleshooting installation and upgrade