CRM Database Log Growth Issue


A customer of ours had come to us facing a rather interesting issue. Every night around 1am their CRM database log file would grow to 31GB and cause the log drive to fill up. When users would log into CRM in the morning, they would receive SQL errors stating that their transactions could not be completed.   Given that this issue occurred on a regular schedule, we determined that the issue had to be attributed to some sort of automated job.

Figuring the first place to check would be the out-of-the-box CRM Maintenance Jobs, we downloaded the Job Editor from Codeplex

(https://crmjobeditor.codeplex.com/ – Very useful tool that everyone should be using) and got to investigating.

Right off the bat, we saw our error on the Deletion Service:
Deletion Service encountered an internal error: System.Data.SqlClient.SqlException (0x80131904): Invalid object name ‘SubscriptionTrackingDeletedObject’

From here we moved to SQL and queried the SubscriptionTrackingDeletedObject table of our CRM database.

What we found was astounding – the table contained 137 MILLION records. Basically after seeing this we knew that the job had to just be timing out – we tested our theory by running the job manually and immediately saw the log file grow to the expected 31GB.

It was decided to clean this table up manually via truncating it. Before you say “Oh no! Don’t delete records via SQL directly!” let’s explain what this table actually is.

When records are deleted from CRM, there are also records that get inserted into this SubscriptionTrackingDeletedObject table. This table gives the Deletion Service Job ObjectIDs that have been removed so that further cleanup can be performed asynchronously. So essentially, it is just a table of deleted records which gives the Deletion Service knowledge to clean up some other areas of CRM (e.g. POA records, duplicate detection records, etc…) if necessary. Once cleaned up, the records from the table are removed. We understood this and decided the need to clean this table outweighed having the other areas of CRM cleaned up (as you will learn later, this wasn’t a concern for us because of how the records got in there).

Please note we cannot condone the practice of editing SQL manually without full knowledge of the possible repercussions. Always consult with Microsoft support if in doubt and remember what works in one scenario, may not work in all.

After the table was truncated, CRM was tested and the deletion service job was run manually – this time not failing with the error above and the log did not grow to 31GB. Before calling this case closed, we still needed to understand what caused this problem in the first place. What could have possibly created so many records in such a short period of time? Luckily, we had the right people involved on the customer’s end and were able to determine that there had been a malfunctioning Scribe job that was running for a while unnoticed. It was a job that was bulk creating and deleting 50,000 records at a time within CRM but the job had since been fixed. Case closed.


Join me at CRMUG Summit in Reno NV October 13-16


Join me at the CRMUG Summit In Reno NV. This is the premier Dynamics CRM Event of the year. Don’t miss out, not too late to register below.

renosummit

 

More details..

http://www.crmugsummit.com/home

Here’s my preliminary schedule. Please refer to the website!

Monday, October 12

8:00pm-10:00pm @ Peppermill – Terrace Lounge Chapter Leader Bash

Tuesday, October 13

2:00pm-4:30pm @ Atlantis: Paradise D & E Volunteer Immersion at CRMUG Summit
5:00pm-8:00pm @ Hall 2 Welcoming Expo Reception

Wednesday, October 14

7:00am-8:00am @ Hall 1 Breakfast – Jump Into a Great Day
9:15am-9:45am @ Ballroom Lobby Networking Break
9:45am-10:45am @ D2 Getting Ready to Pull the CRM Trigger
11:15am-12:25pm @ C4 CRMUG Opening General Session – Microsoft Keynote
1:30pm-2:30pm @ D4 The CRM Superhero’s Tips, Tricks, and Toolbox
1:30pm-2:30pm @ D2 Scouting & Preparing as CRM GameDay Draws Near
2:30pm-3:00pm @ Ballroom Lobby Networking Break
4:00pm-4:30pm @ Ballroom Lobby Networking Break
4:30pm-6:00pm @ D2 ADFS & Upgrading with a Mobile State of Mind
6:00pm-8:00pm @ Hall 2 Expo Reception

Tribridge Customer Party – Invitation Only 7pm on..

Please contact me if your a customer or prospect and did not receive an invitation.

Thursday, October 15

7:00am-8:00am @ Hall 1 Breakfast – Jump Into a Great Day
8:00am-9:00am @ D2 Getting that CRM Upgrade to Purr Like a Kitten
9:00am-9:30am @ Ballroom Lobby Networking Break
10:30am-11:00am @ Ballroom Lobby Networking Break
11:00am-12:00pm @ C4 CRMUG General Community Session
12:00pm-2:00pm @ Halls 1 & 2 Networking Lunch and Expo
2:00pm-3:00pm @ D7 Going OnPremise to CRM Online
2:00pm-3:00pm @ C1 Take It to the Next Level with End User Experience Upgrades in CRM 2015
3:00pm-3:15pm @ Ballroom Lobby Networking Break
3:15pm-4:15pm @ D4 Ask the MVPs: Admin-Foundation Edition
3:15pm-4:15pm @ D7 Ask the Experts – User Adoption Edition: Enterprise CRM Strategies and Pitfalls to Avoid
4:15pm-4:45pm @ Ballroom Lobby Networking Break
4:45pm-5:45pm @ D2 Dissecting Multifaceted CRM Upgrades

Friday, October 16 – CANCLLED

8:00am-9:15am @ D2 Deployment Method of Choice  CANCELLED

 

 


Upgrade from CRM 2011 to 2013 fails with “Subquery returned more than 1 value. Not Permitted CRM 4.0″


We recently stumbled upon an issue when upgrading a customer from CRM 4.0 to CRM 2013. As is required, you must first upgrade the 4.0 database to 2011 and then from 2011 to 2013. The 4.0 to 2011 upgrade succeeded without error but threw an error when going from 2011 to 2013. 2013 was on update rollup 2. The error thrown was:

Error| Installer Complete: OrganizationUpgrader – Error encountered
14:56:47| Error| Exception occured during Microsoft.Crm.Tools.Admin.OrganizationUpgrader: Error.ActionFailed Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction
InnerException: System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

We upgraded the database from 4.0 to 2011 one afternoon and then tried going from 2011 to 2013 the following morning. As it turns out, CRM 2013 contains a query that checks to see if an organization is a new organization or an upgrade by checking the CreatedOn field of the system solution. It does this by seeing if the CreatedOn date/time is within one day (24 hours) of the current time. If the CreatedOn date of the System Solution is within 24 hours, it is considered a new organization. Here is the query:

IF EXISTS (SELECT * FROM SolutionBase WHERE SolutionId=’FD140AAD-4DF4-11DD-BD17-0019B9312238′
AND CreatedOn >= DATEADD(day, -1, GETUTCDATE()))
BEGIN

(This query goes on to Update prvAppendToOpportunity depth to global on sales person role and update prvAppendToQuote, prvAppendToOrder, prvAppendToInvoice to local on sales person role.)

When the 4.0 database is upgraded to 2011, it sets the CreatedOn date/time to whatever the UTC date/time is of that upgrade. So when going from 2011 to 2013 it sees the new date/time resulting from the upgrade to 2011 and considers it a new organization and errors out on the above query. Below is the query that can be run to check the CreatedOn date of your 2011 database.

crmquery

The easy solution to this is to just wait 24 hours between the upgrade stages but if you are in a time crunch or minimizing down time for a go-live, you can update the CreatedOn date/time of the system solution directly in the 2011 database. Remember that the time is in UTC, changing the date back one day should be easier than trying to figure out the time difference in your time zone. However, keep in mind that updating fields directly through SQL is not a change supported by Microsoft so this is done at your own risk! Not sure if this issue is isolated to just Update Rollup 2 or if this occurs with 2013 RTM, Update Rollup 1, or Service Pack 1 yet.

Thanks to my young CRM Jedi Gage Pennisi for putting the details together!


CRM 2013 Upgrade Flurry has Started! Upgrade Checklist


Microsoft has begun switching over many of our Microsoft Dynamics CRM customers to CRM 2013.

Here’s some things you should know about the overall process:

1. Ensure you have another environment (test/dev) that matches your CRM production setup.
This is a best practice by far. Not having a test environment, makes troubleshooting and changes in your production environment almost impossible to manage. This is no different for which deployment model you have choose: Online, On-Premise or Partner Hosted.

Ensure this environment has a copy of the latest customizations, solutions, ISV’s etc. Data can also be replicated from production, using tools like the CRM 2011 Instance Adapter or tool like Scribe.

If you do not have a CRM 2011 instance for test/dev. already, its now too late to create a new one as only CRM 2013 instances are available as of Nov 4th. You will have no choice but to create a test CRM 2013 trial and import your 2011 solution which if fully supported. This will provide you with what your system will look like after the upgrade.

You should also push out your production CRM Online date as far as possible. Today, the last date available to upgrade is 2/14/2014. For other implementations, you still have some time :).

2. Understanding your Environment
Ensuring that your understand all the components that have been deployed with your CRM system is critical to the success of your upgrade. You will need to review all your Solutions, Web Resources and Custom Code to ensure that your and your ISV’s are prepared for the upgrade.

Microsoft has provided a tool to help pin point this issues.
http://www.microsoft.com/en-us/download/details.aspx?id=30151

The information provided by the tool will be a roadmap for your upgrade. Check with ISV’s website’s for their plans in regards to CRM 2013.

3. Fixes Issues
It’s very important to record and fix your issues and test them fully before User Acceptance Training (UAT). Errors with the application can quickly kill your user adoption. If you’re not ready, wait! Once the issues have been resolved your ready for testing!

4. Testing your Upgrade
Once you have all the customizations, solutions and errors fixed in your development/test server, you can now perform your User Acceptance Training (UAT) and overall product training. Please note that even with no errors, your users will need training for CRM 2013’s completely redesigned interface.

Make sure you have a strong test plan, allowing users to fully test all of their daily functions. This should also include processes they don’t often run. This should include the Outlook and Web Clients as well as desktop versions Win7/Win8 as well as Office versions (2007/2010/2013) or the combination that is being deployed by your organization.

Once you have signoff, you will want to take the solutions and ISV’s files and you will need to deployment them in the production environment the night of Production Go-Live.

5. Planning your Upgrade
Once you have completed your testing, you can now select to move in your production upgrade date from the CRM Online System. This will allow you to choose the next available date. Once the upgrade starts, you will not be able to access the system for a few hours, and as long as 24 hours.


CRM 2011 Installation Error – SQL Server is unavailable


While working on a routine CRM 2011 installation, all system checks passed except for Microsoft SQL Server’s. It failed with “The SQL Server ‘Server Name’ is unavailable. Gut instinct was firewall issues here.CRMInstall_SQLServerUnavailable

 

 

 

 

 

 

 

 

 

 

 

 

Disabled Windows Firewall on SQL an CRM servers but still received the same error message. Error logs did not provide much to go on here. Ports and connectivity all checked out fine – even added the config database into SQL manually and the system checks were able to determine that the database already existed, throwing the error for that (then deleted it).

Thinking the error may be a bogus one, I set the Ignore Checks key in the registry to bypass the error. This let the installation proceed but ended up failing during the creation of the organization database (e.g XXX_MSCRM). CRM itself installed, created the MSCRM_CONFIG database, and I was able to pull up the Deployment Manager to see the organization state as ‘Failed’. This was the confusing part – how was one database created but the other failed? Rechecked the error log on the installation and was provided with:

Error| Install exception.System.Exception: Action Microsoft.Crm.Tools.Admin.CreateDatabaseAction failed. —> System.IO.IOException: The network path was not found.

After uninstalling CRM and doing a little research on this, it seems similar errors are thrown when drives are not shared appropriately. On the SQL server, I opened a command prompt to run a net share on the drive and was rejected with the “The Server service is not started.” When prompted to start it, system error 1508 was shot back stating that Server service was disabled.WindowsServerService

Opened up services, went down to the Server service and did see that it was disabled. Enabled the service and started it, and ran the system checks for the CRM installation again.ServerService

Voila! All green. Installation proceeded normally and finished successfully.

Thanks to Gage Pennisi, my fellow Tribridge Co-Worker for spending the hours to determine what the issue was. This issue does not happen on a fresh install of the OS, following best practice. The customer’s servers were built from an “image” that had the service turned off.

 


CRM 2011: The Item ‘/SharedReports/5.0.xxxx/MSCRM_FetchDataSource’ cannot be found


Installation of the Microsoft Dynamics CRM Reporting Extensions formally known as the SSRS Data Connector (Still in on the installation media in same folder) is necessary so that CRM and SQL servers can communicate over the kerboros double hop issue when using to seperate servers.

The error below was received because a SQL Server was replaced the SSRS Data Connector was not added back in.

This key is the word ITEM cannot be found. This simply means the SSRS data connector cannot find the original reports. It turns out the report databases that support SSRS were completely removed, disgarded when the SQL server was rebuilt, so the CRM reports were now missing. (Note: Custom Reports would have been deleted and cannot be restored using this procedure. A backup of the ReportDB\ReportTempDB would be required, with a repair of SSRS to get those reports working again).

In order to fix this, remove and reinstall Reporting Extensions first, then run the command line tool from the crm server directory C:\program files\Microsoft Dynamics CRM\Tools\  to recreate/republish the reports by typing the following command :

publishreports ORGNAME  – Organization name of the CRM Installation Missing Reports

Once completed, issue an IISRESET on the crm server. Then navigate back into CRM report section and run/find your missing reports! There are several other topics covered on this blog around SPNs and Service Accounts that could also be an issue if your reports are not working. Take a look! Enjoy.


CRM 2011: Outlook Client UR 10 provides Drastic Search & SQL Performance Improvement


Microsoft has just released Update Rollup 10 for Microsoft Dynamics CRM 2011. One of the main areas we have been testing with is within quick finds (searching) using the Outlook Client. To further explain, please understand that the Microsoft Dynamics CRM 2011 Outlook Client uses a completely different SQL query than its Web client counterpart prior to Update Rollup 7. This is a reason to immediately move to at least Update Rollup 7. Please note that when downloading CRM 2011 Server, Update Rollup 6 is now built-in to base code revision.

In our test Scenario below, just updating from Update Rollup 5 to Update Rollup 8 provided immediate performance improvements right off the bat, and the query executing provided similar performance results in either the Outlook Client or the Web Client

Example 1 Results:
1.5 Million Rows, Quick Search Contact Name, Rollup 5, Outlook Client 37.6 seconds, Web Client 11.0 Sec
1.5 Million Rows, Quick Search Contact Name, Rollup 8, Outlook Client 8.6 seconds, Web Client  8.7 Sec
– This is a drastic improvement just applying the Update Rollup.
– Note: This customer also has another issue that I will address in this post, please continue on..

Unfortunately, due to the nature of how the scripts are dynamically created, there is still limited performance improvements with Update Rollup 8.  Update Rollup 10 is really where the magic takes place. Please note that we have tested ONLY an Outlook Client using Rollup 10 (this changes the query being sent by the client) and have noted significant improvements.

We do not recommend this approach for a production system as the server should ALWAYS be on a higher or equal update rollup then the client machines, and there could be harmful client issues.

Update Rollup 10 completely changes how the dynamic SQL scripts are created & executed. With the old query structure, all of the columns were executed together, hence SQL Server was unable to generate a good query execution plan because of all the join statements required.

Now, with Update Rollup 10, the dynamic SQL statements use separate SQL statements combined using SQL UNIONS. This change now allows for drastic performance improvements, taking advantage of SQL Server Native Execution Plans, giving us the ability to now further tune and build indexes for these additional unions avoiding complete SQL table scans.

Old SQL Query

where ((((“contact0”.StateCode = @StateCode0)) and ((
“contact0”.ParentCustomerIdName like @ParentCustomerIdName0 or
“contact0”.MiddleName like @MiddleName0 or
“contact0”.LastName like @LastName0 or
“contact0”.FullName like @FullName0 or
“contact0”.FirstName like @FirstName0 or
“contact0”.EMailAddress1 like @EMailAddress10 or
“contact0”.new_ContactIdentifier like @new_ContactIdentifier0 or
“contact0”.Address2_Line1 like @Address2_Line10 or
“contact0”.Address1_City like @Address1_City0

New SQL Query

where ((((“contact0”.StateCode = @StateCode0)) and ([contact0].[ContactId] in (
SELECT [ContactId] from [ContactBase] as “contact0” where
(“contact0”.ParentCustomerIdName like @ParentCustomerIdName0) OR
(“contact0”.MiddleName like @MiddleName0) OR
(“contact0”.LastName like @LastName0) OR
(“contact0”.FullName like @FullName0) OR
(“contact0”.FirstName like @FirstName0) OR
(“contact0”.EMailAddress1 like @EMailAddress10)
UNION SELECT [ContactId] from [ContactExtensionBase] as “contact0” where
(“contact0”.new_ContactIdentifier like @new_ContactIdentifier0)
UNION SELECT [ParentId] from [CustomerAddressBase] as “contact0” where
(“contact0”.Line1 like @Address2_Line10 and “contact0”.AddressNumber = 2 and “contact0”.ObjectTypeCode = 2) OR
(“contact0”.City like @Address1_City0 and “contact0”.AddressNumber = 1 and “contact0”.ObjectTypeCode = 2)))))

Example 2 Results:
1.5 Million Rows, Quick Search Contact Name, Rollup 8, Outlook Client 8.6 seconds, Web Client 8.7 Sec
1.5 Million Rows, Quick Search Contact Name, Rollup 10, Outlook Client 2.6 seconds, Web Client 2.6 Sec
– Again another Drastic search improvement across the board.

Finally, still believing we can get even more performance out of the system, we researched more into the specific fields in the customers quick find view. The quick find view was using a search (find) field that was in the Contact Extension Base. Upon removing this field from the quick find view, the results where even more impressive:

Example 3
1.5 Million Rows, Quick Search Contact Name, Rollup 10, Outlook Client 2.6 seconds, Web Client 2.6 Sec
1.5 Million Rows, Quick Search Contact Name, Rollup 10, Outlook Client 1.6 secs, Web Client 1.7 Secs The response is so fast I can barely hit my stop watch and enter key at the same time!

In closing, further tuning of this field, now that we can identify the union, should allow us to re-add the field and continue SQL performance tuning using the Database Tuning Advisor (future article on this). I would recommending reviewing any fields in the extension base and consider their value before adding them to the quick find, and at least trying to performance tune those results prior to release.

This is quick way to lose system performance by a user adding fields to the quick find (search) that are outside the main entity.

I’d like to thank Special thanks to Eric Hagen from Microsoft support, and my Tribridge team mate Mike Hauck & Wife for working many late evenings with me, researching and testing while putting his new born daughter to sleep on conference calls! Dedication 🙂

 


Transparent Data Encryption in Microsoft Dynamics CRM 2011/(4.0)


The use of SQL TDE – Transparent Data Encryption is a great way to secure your Microsoft Dynamics CRM 2011 or 4.0 systems, especially when they contain sensitive data like credit cards or social security numbers. This ensures that even if someone gets a copy of your database, it cannot be used or restored on another system without a key.

Additionally because it’s TRANSPARENT, your CRM server or applications that access SQL server are NOT effected. That’s right no code changes etc. It is still best practice to test this out in your development environment first.

Please note SQL Server 2008 Enterprise is required in order to enabled SQL Transparent Data Encryption.






































First, let me stress the importance of saving the encrypted backup key and password, and not just a copy on the SQL server. Please take the time to store these keys in TFS, One-Note, Sharepoint, Outlook etc something that you can retrieve these items should they be needed. A customer had this stored on the local C drive of the SQL server and lost the entire server. They could not recover their databases as they did not know the password.

Setting UP TDE – Super Fast Only 10 minutes!

Step 1: From SQL Management Studio, on the MASTER DB
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘PASSWORDHERE’

Step 2: Create the TDE Certificate
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’

Step 3: Backup the Certificate
BACKUP CERTIFICATE tdeCert
TO FILE=’D:\Scripts\tdeCert.certbak’
WITH PRIVATE KEY (
FILE=’D:\Scripts\tdeCert.pkbak’,
ENCRYPTION BY PASSWORD=’PasswordHere’)

NOTE: Make sure you save the password and certificate OFF the SQL server. The D:\drive selected is a drive on the SQL Server directly.

Step 4: Select the CRM Database you want to use
USE CRM_MSCRM
GO

Step 5: Create the Database Encryption Key
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert

Step 6: Alter the DB
ALTER DATABASE CRM_MSCRM SET ENCRYPTION ON

Congratulations! You just enabled TDE! on your CRM Database!

How to Check for TDE Encryption:
SELECT DB_NAME (DATABASE_ID), encryption_state
FROM SYS.dm_database_encryption_keys

You may also read the Microsoft document covering SQL Server 2008 TDE right here:
http://msdn.microsoft.com/en-us/library/cc278098.aspx


CRM 2011 Upgrade Failure: The full-text component is not installed on SQL Server, Word Breaker Not Installed


A common error when upgrading or installing CRM 2011:

 

 

 

 

 

 

 

This error can be resolved by modifying the existing SQL server installation. From Control panel, click add/remove programs, click on SQL Server installation, change/ add features. You will be prompted for the original install media so make sure you have it mounted and avaiable. Select add new features to SQL instance, choose instance that CRM was installed on, and check the box for Full-Text indexing.

The SQL wizard will run some checks and apply your changes. Once completed, Now start and stop SQL service, and make sure to turn back on the SQL Agent (it’s turned off during installation changes). Re-run your CRM setup by pressing back and next, and both errors will go away! Enjoy!

 

 


CRM 2011 Install: Reporting Services Error 401 Not Authorized


Troubleshooting a customer’s CRM 2011 setup failure, we almost were thinking it was machine account issue, so we rejoined the machine to domain last night. Sorry to say, that didn’t help.. So a little more digging…

It turns out that whomever installed SQL on that box (might be a VM image copy), failed to enter the service accounts when configuring SQL during the install, and decided to change the SQL services after the install.

The big issue with this approach is that SQL install creates the specific SPN’s for these accounts. So changing them after will work on the local box, but anything connecting via kerboros (not GP), will have issues trying to pass on the authentication.

The fix was:

setspn –A http/HostName Domain\Reporting Services Sevice Account

setspn –A http/HostName.domain.com Domain\Reporting Services Sevice Account

A quick restart of IIS, and now the CRM server installation can proceed. Enjoy!