Dynamics CRM 2011 beta setup

I've created a short video guide for setting up the Dynamics CRM 2011 on-premise beta. I installed all of the services on to the same virtual machine, so some of the things done will not reflect best practices. The whole installation (excluding Windows setup and updates) took took just over an hour but I've tried to cut as many of the progress bars as possible.

Before I started recording, I installed Windows Server 2008 with SP2 and all available updates. I also turned off UAC and the Windows firewall.

If the video below does not work, please try here. Be sure to watch it in full screen HD, otherwise the quality is not great.

 

Tagged CRM 2011 SQL Video

Checking SQL table sizes

Sqlsize

I wanted a way to find which tables were using the most storage in my SQL databases. The stored procedure sp_spaceused will show this data for a single table, but I wanted to view every table at once. The below SQL query will run against any SQL 2005 database and show you it's table sizes in descending order. As expected, the ActivityMimeAttachment table in my CRM database was pretty huge!

SELECT sys.schemas.[name] AS [Schema],
sys.tables.name AS [Table],
COALESCE([Row Count].[Count], 0) AS [Rows],COALESCE(8192 * [Data Pages].[Count],0) AS [Data Bytes],
COALESCE(8192 * [Index Pages].[Count],0) AS [Index Bytes]
FROM sys.tables
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
LEFT OUTER JOIN (SELECT object_id,SUM(rows) AS [Count]
FROM sys.partitions
WHERE index_id < 2
GROUP BY object_id) AS [Row Count] ON [Row Count].object_id = sys.tables.object_id
LEFT OUTER JOIN (SELECT sys.indexes.object_id,SUM(CASE WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0 END) AS [Count]
FROM sys.indexes
INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id
AND p.index_id = sys.indexes.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY sys.indexes.object_id) AS [Data Pages] ON [Data Pages].object_id = sys.tables.object_id
LEFT OUTER JOIN (SELECT sys.indexes.object_id,SUM(a.used_pages - CASE
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0 END) AS [Count]
FROM sys.indexes
INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id
AND p.index_id = sys.indexes.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY sys.indexes.object_id) AS [Index Pages] ON [Index Pages].object_id = sys.tables.object_id
ORDER BY [Data Bytes] desc

Tagged SQL

Get past the 250 records per page limit

Screen_shot_2010-07-18_at_17

Dynamics CRM 4 only allows users to view a maximum of 250 records per page within a view. This can be quite limiting when you need to run an on demand workflow against thousands of records.

There are 2 ways to get around this limit. The Microsoft supported solution is to use a plugin as described here. The unsupported and more risky workaround (and what I did...) is to update the organisation database directly as below.

UPDATE UserSettingsBase SET PagingLimit=1000 WHERE SystemUserId IN
(Select SystemUserId from SystemUserBase WHERE FullName = 'neil mcdonald')

Screen_shot_2010-07-18_at_17

If using the SQL method, I recommend that you change it back to 250 rows or less once you've accomplished your tasks, as this is not supported by Microsoft and could cause errors with future updates.

Tagged Personal Options Records SQL

Changing an Active Directory username in CRM

Add_user_error

One of our users had complained that their name was spelled incorrectly. I made the changes in AD, Exchange and in her CRM user account, but when I tried to change her domain login name in CRM, I received the error 'You are attempting to create a user with a domain logon that is already used by another user...'

To get around it, I made the change directly in the organisation database. The domain login name is stored in the 'DomainName' column of the 'SystemUserBase' table, so I just edited the data within SQL management studio.

FYI - Although I do this, I don’t recommend making any changes to the CRM databases directly. A safer workaround is posted here

Tagged Active Directory SQL