Query a SQL server to find the progress of a database restore

This just came in from a co-worker. Too valuable not to share!

In case you might want to monitor the progress of a database restore on your SQL server, this query shows the progress in percentage, elapsed time, etc…

SELECT r.session_id, r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete], CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Stopping Internet Explorer 11 from showing the mobile express version of Dynamics CRM 2011

If you have a Dynamics CRM 2011 installation, and users have started using Internet Explorer 11 (either by upgrading from IE10, or by installing Windows 8), then you’ve undoubtedly noticed that, when an IE11 user goes to your CRM site, they are greeted by the Mobile Express version, rather than the standard site. There are a few manual workarounds to this:

  • Downgrade from IE11 to IE10 (not an option for Windows 8).
  • Add your CRM domain name to the compatibility list in IE11 (not an option if you don’t want the entire domain to be in compatibility mode, or if you have group policy settings which prohibit this).
  • Instruct users to go to https://yourcrmdomain.com/main.aspx.

That third bullet is interesting… If you go to the root of your CRM domain name in IE11, you will be redirected to the Mobile Express site. If you go to the “main.aspx” page on the root, you go to the full CRM site. Which got me thinking… How can we identify IE11 users accessing the Mobile Express site, and redirect them to /main.aspx?

The solution is in IIS’s URL Rewrite library, which, if you don’t already have, you should get, because I’ve used it before to fix issues related to Dynamics CRM (not to mention its multitude of other uses).

Here’s what our rewrite rule will do:

The full IIS rewrite rule is below. You can drop this into the web.config in the root of your Dynamics CRM 2011 web site, or set it up manually using the URL Rewrite wizard. Either way, when you’re done, you’ll always get the full version of Dynamics CRM when using the IE11 browser.

<rule name="Redirect IE11 from mobile site to main.aspx" patternSyntax="Wildcard" stopProcessing="true">
 <match url="*m/default.aspx" />
 <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
 <add input="{HTTP_USER_AGENT}" pattern="*; rv:11.0) like Gecko" />
 </conditions>
 <action type="Redirect" url="https://crm.innovatix.com/main.aspx" redirectType="Temporary" />
</rule>

By the way, there is only one gotcha: You can’t use IE11 and deliberately go to the Mobile Express site. A small price to pay to fix a much larger problem.

Clearing the Web Platform Installer’s installer cache

My laptop has two hard drives — a 128GB SSD and a 1TB old school hard drive (taking the place of the DVD drive, which I never used). I love the SSD, but it’s size limits it to the operating system, some important program files, and a handful of documents.

Every now and then, it fills up. I recently found a silent disk space usage offender: Microsoft’s Web Platform Installer. Apparently, the Web Platform Installer (WebPI) caches all installer files to a local folder, and never deletes them. Which isn’t a problem if you have plenty of disk space, but for me, it’s a problem.

The path to the WebPI installer cache folder is “%LOCALAPPDATA%\Microsoft\Web Platform Installer\installers” – where the variable LOCALAPPDATA points to a folder in your user profile directory. For example, on my machine, the full path is C:\Users\demarzo\AppData\Local\Microsoft\Web Platform Installer\installers.

Deleting all the files in this folder is safe, as the WebPI will . . .

→ Read More: Clearing the Web Platform Installer’s installer cache

Getting Dynamics CRM ObjectTypeCode values via SQL

Lots of tables in Dynamics CRM use the ObjectTypeCode to identify the entity. Sure, we all remember that Account is ObjecTypeCode 1 and Contact is ObjectTypeCode 2… But what about your custom entities, which start at 10,000 and are not guaranteed to be the same in different organizations, even if you have the same solutions installed — how will we remember their ObjecTypeCode values?

You don’t remember them, you query them when they need you. It’s quick and easy if you are on premises and have access to the SQL database. (If you don’t, ask your DBA to create a view with this query and give you rights to it.)

select coalesce(OriginalLocalizedName,name) as DisplayName, Name as SchemaName, ObjectTypeCode
from EntityLogicalView
order by ObjectTypeCode

Much easier than memorizing, and avoids the problems of remembering the wrong number for the wrong organization or deployment. Oops!

. . .

→ Read More: Getting Dynamics CRM ObjectTypeCode values via SQL

How entity relationships can kill performance in Dynamics CRM 2011

Microsoft Dynamics CRM 2011 allows us to specify relationships between entities, and gives us some configuration as to how those relationships work. One of those relationship types is “parental” — which effectively means, “anything that happens to the parent happens to the child.” Therein lies a lot of power — and a lot of risk.

In the environment where I work, we have a lot of entities. Two of those entities are a “Contract” entity (not the out-of-the-box Contract entity, which we renamed to a more appropriate “Service Contract”) and a “Contract Volume” entity. Intuitively, since you can not have Contract Volume without a Contract, we set the relationship type to parental, with Contract being the parent to Contract Volume.

We have thousands of Contract records, and hundreds of thousands of Contract Volume records. The data for these comes from a separate proprietary application. Data is inserted/updated . . .

→ Read More: How entity relationships can kill performance in Dynamics CRM 2011