Forcing IIS to rewrite all requests to HTTPS

I often need to ensure that an IIS web site uses HTTPS instead of HTTP. The easiest way to do this is with a URL rewriting rule. The rule I use is below.

<rule name="Redirect to HTTPS" stopProcessing="true">
    <match url="(.*)" />
    <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
        <add input="{HTTPS}" pattern="^OFF$" />
    </conditions>
    <action type="Redirect" url="https://{HTTP_HOST}/{R:1}" redirectType="Permanent" />
</rule>

This will redirect any request that is not “HTTPS” to an “HTTPS” address.

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')

With that, you can really tell how far along a SQL restore is!

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 simply re-download what it needs when it needs it, which should be perfectly fine for most people who don’t make a habit of repeatedly reinstalling the same version of software over and over again. For me, it saved over 1GB of disk space. Which tells me, among other things, I install a lot of software!

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!

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 into CRM from this other system using Scribe Insight. The entities are read-only in CRM, so we don’t have to bi-directional synchronization of data. All was well, and we moved data for a couple weeks without issue.

In a recent release, we expanded the functionality of CRM and found the need to customize the owner of the Contract entity to one of three different CRM teams. So, we did what seemed sensible: update the Scribe package to set the owner based on the criteria we came up with. Unfortunately, running this package took down our CRM system within a minute. The database server was overloaded, and SQL blocks were everywhere.

The problem was our relationship. In a parental relationship, any deleting, sharing, and assigning of the parent will affect all of its children, The effect of this is:

  • Deleting a parent will delete all child records.
  • Sharing or un-sharing a parent will perform an identical share or un-share on all child records.
  • Assigning (changing the owner of) a parent will assign (change the owner of) all child records.

Think about the potential impact of this. If a single record has 10,000 child records, and you assign this record to another user, CRM must perform the same operation against all 10,000 child records — as one operation. Add on top of that CRM’s built-in overhead (auditing, principal object access, logging, etc.), and you’ve effectively killed your system, which likely can not keep up with the scope of your request while dealing with other people’s requests.

A better solution for us was to do the following:

  • Change the Parental relationship to a Referential relationships, where there is no cascading, and a delete will simply remove the parent reference, leaving the child an orphan with no parent.
  • Create a cleanup job to delete all the orphaned child records. This can be done via a a Scribe job, a recurring bulk delete job, or manually.

Dynamics CRM has a lot of power, but you have to think about the effect of the choices made, because they very easily can cause unintended consequences — as they did for us recently!

First day without coffee… or not?

I’m going to make an announcement that may come as a shock to anyone who knows me…

I’ve voluntarily decided to give up coffee.

Those that know me will probably react like the first person who saw me walking around today with a cup of water instead of a cup of coffee: they laughed. I was a heavy coffee drinker, about 5 cups a day. I enjoyed my coffee. I blogged about the wonders of coffee. I identified myself with a cup of coffee. I bought coffee for co-workers, and they bought it for me. I went out for coffee breaks when the smokers went out for cigarette breaks. I looked forward to drinking coffee…

So why the change? Ultimately, it came down to health and stress. I drink too much coffee (caffeine) and not enough water, and I’ve been too stressed lately. Coffee (caffeine) is not helping either of those.

After writing that, I decided to look up the negative effects of coffee. While typing into Google, the instant search instead brought me to this page: 7 Reasons Why Coffee Is Good For You (Popular Science). Not what I was looking for. Perhaps a more balanced review is here: 10 Reasons Coffee is Both the Best and the Worst Beverage Ever Invented (io9). Ugh. Why can’t this be easy?

Coffee isn’t a magic elixir, and it isn’t a doomsday device. Like most things in life, moderation is key. When it comes to coffee, I’ve tipped the moderation scale so far that it fell over. Time to recalibrate. I’ll be off the sauce for a while, and we’ll see where it goes from there…

Getting rid of nameless Contacts when Leads are qualified in Dynamics CRM

Some would wonder why such a question is asked… but in my Microsoft Dynamics CRM 2011 environment, we do not require a First Name or Last Name on a Lead. When a Lead is qualified, users often click the buttons to create an Account and a Contact, even when they haven’t specified any contact-specific information (such as first and last name). What results is often a Contact with no name, and that contact is the Primary Contact for the Account. Ouch!

To fix this, we created a workflow on the Contact entity, which would deactivate the Contact if it had a Qualifying Lead, and had a blank First Name or Last Name. This took care of the Contact, but there was still a problem: the Account’s Primary Contact pointed still pointed to the nameless Contact we just deactivated!

Looking at the Audit History on the Account, we were able to figure out that, when qualifying a lead, CRM creates and updates records as follows:

  1. A new Account record is created. The Primary Contact field is left blank.
  2. A new Contact record is created. The Contact’s Account is set to the Account created in step #1.
  3. The Account record created in step #1 is updated. The Primary Contact field is set to the Contact created in step #2.

To solve the deactivated Primary Contact problem, we created a workflow on the Account entity, which would clear the Primary Contact field if the Primary Contact was set to a deactivated Contact.

Problem solved! Users can continue trying to create Contacts with no names, and we’ll make sure they never see them.

Batch file to compress and copy a folder

Recently, I needed to compress all contents of a folder (including subfolders) to an archive file, and copy it to a remote (network) location, all from a command line. In other words, I wanted to do this:

compressAndCopyFolder <sourceFolder> <destinationFolder> <archiveFileName>

Here’s how each parameter would work:

  • sourceFolder is the folder, along with all subfolders, to be added to the archive.
  • destinationFolder is the folder where the archive would be created.
  • archiveFileName is the file name of the archive

I prefer 7zip for archiving, so I could have done this simply using one command:

7z.exe" a -r "%destinationFolder%\%archiveFileName%.7z" %sourceFolder%\*

The problem with this is that it is inefficient to work with a large archive file over the network — there’s a lot of chatter going on to add files to an archive file. Much faster to create the archive locally, then copy the final archive file to the network. So, instead of one command, we have two:

echo off
if "%3"=="" (
	echo usage: 7zfolder ^<sourcefolder^> ^<destinationfolder^> ^<destinationfilename^>
	echo Note: The suffix .7z will be added at the end of destinationFilename.
	goto :eof
)
if not exist "%1" (
	echo ERROR: Source folder does not exist: %1
	goto :eof
)
if not exist "%2" (
	echo ERROR: Destination folder does not exist: %2
	goto :eof
)
if exist "%2\%3.7z" (
	@echo ERROR: Destination file already exists in destination folder: %2\%3
	goto :eof
)

@"c:\Program Files\7-Zip\7z.exe" a -r "%temp%\%3.7z" %1\*
@copy "%temp%\%3.7z" %2\%3.7z /z

Now, from a command line, I just do this…

7zfolder c:\mysource \\myserver\mydest archive

… and all files in c:\mysource will be added to an archive \\myserver\mydest\archive.7z.

Don’t you just love batch files?

Sync or synch as short for synchronize?

Today at work, someone wrote the following as a work item summary: “Synch production and test data”.

Reading this, I thought, “Which is correct – sync or synch – when shortening synchronize?

The winner is sync, and here’s why:

  • We pronounce either variant as sink.
  • c alone is often pronounced k (cake, panic)
  • ch alone is often pronounced ch (church, match)
  • Sync = ”sink”
  • Synch = “sinch”
  • One less keystroke for sync

I was happy with that until reading an article on The Language Lover’s Blog, “Sync or Synch?“, which made me conscious of other common variants, such as psych as short for psychology.

Ultimately, my search was put to bed when I read this comment:

you guys are such nerds!!! the world really doesnt care about the voiceles velar fricative converting to a velar plosive. blogging should be eliminated from the internet.

Guilty as charged…