The Final Push – CRM 2016 is Live

After months of ups and downs, plans built and destroyed, and countless hours invested, CRM 2016 is live. (Actually, it’s been live since Easter weekend. But keeping a blog up to date is obviously not one of my talents.) For the last installment in this series I’d like to go over how we went about the upgrade and highlight some successes. Before I get into that, though, there is one essential thing that needs to be understood about this entire project. From the moment we really got started, it was placed in the hands of Jesus Christ. From planning to implementation, and everything in between, it was submitted to Him. I don’t say that to be cheesy or legalistic, but rather to give credit where credit is due.

As I’ve mentioned before, we were attempting to go from our production Dynamics CRM 2011 environment all the way to the latest Dynamics CRM 2016. That environment has a database that exceeds 1.5TB and numerous integration points with systems like our dialer. During planning we reached out to a vendor, who shall remain nameless, for their advice. The overview of their plan looked something like this:

Following this approach, the migration would flow as such:

  • Shut off the PROD 2011 servers.
  • Back up the PROD 2011 organization database.
  • Restore the PROD 2011 database to the temporary CRM 2013 environment.
  • Import the PROD 2011 database to CRM 2013.
  • Back up the upgraded CRM 2013 database.
  • Restore the upgraded CRM 2013 database to the temporary CRM 2015 environment.
  • Import the upgraded CRM 2013 database to CRM 2015.
  • Back up the upgraded CRM 2015 database.
  • Restore the upgraded CRM 2015 database to the temporary CRM 2016 environment.
  • Import the upgraded CRM 15 database to CRM 2016.
  • Back up the upgraded CRM 2016 database.
  • Restore the upgraded CRM 2016 database to PROD 2016.
  • Import the upgraded CRM 2016 database to PROD 2016.

We built out the “hop” environments as this plan suggested and began to test the theory. Aside from multiple technical challenges, some of which I’ve addressed in other posts, even when it worked the timeline was simply too long. End to end it took 1.5 weeks or more, and that’s not counting any additional steps such as importing our customizations or the work for our data warehouse. Taking down a tier zero application for one to two weeks was NOT an option for us.

The more I thought over the proposed plan, our past CRM experiences, and the options we had available to us, a new plan came into view. I wasn’t sure it would even work, but decided to do a “mock migration”, if you will, and test it out. The new approach looked like this:

The smaller database symbols represent the config database for each temporary environment. The temporary 2016 servers were eliminated altogether since our final (PROD) hop was 2016 anyway. Basically, I just made the 2011 organization database a central upgrade spot on a new SQL instance and pointed all the app servers there.

Following this new plan, the work flow became:

  • Shut off the PROD 2011 servers.
  • Remove the PROD 2011 organization database from our PROD Availability Group.
  • Detach the PROD 2011 organization database and reattach it under the new PROD 2016 instance.
  • Import the PROD 2011 database to CRM 2013.
  • Remove the organization from CRM 2013.
  • Import the upgraded CRM 2013 database to CRM 2015.
  • Remove the organization from CRM 2015.
  • Import the upgraded CRM 2015 database to CRM 2016 (using PROD App servers).
  • Remove the other copy of the CRM 2011 database and sync the upgraded 2016 database to the other Availability Group node.

This had a profound affect on our timeline. Each of our backup and restore operations had cost us around six hours each, for a total cost of around 48 hours. By pointing all of the app nodes at one central SQL server, that was completely eliminated. It also allowed us to utilize the beefier PROD hardware. Instead of 1.5 weeks our tentative database upgrade timeline became 31-36 hours, very doable for a weekend maintenance window. And that was on slower storage that had been provisioned only for the mock migration tests. In the end our actual time for full migration was less than 24 hours. That includes not only the database upgrade portion but also importing customizations, changing our data warehouse structure, reconfiguring connected systems, and troubleshooting issues.

There was truly a fantastic team that worked on this project and I’m honored to have shared in the adventure with them. There is a great deal more work not detailed in these blog posts because I didn’t have direct insight into those portions. I’d like to call out a few of them at a high level though.

  • Our development team spent months updating old code, much of which predated their employment here. They also developed innovative new ways of approaching old problems and bringing everything into compliance with CRM 2016. With the amount of customization we have, this is more than commendable.
  • My co-upgrader, Matt Norris, put in many long hours and handled with stride any challenge thrown at him. He was new to CRM when we started but I now refer to him as “battle-hardened”.
  • My fellow DBA, Bret Unbehagen, single handedly worked out the difference in table structures between CRM 2011 and 2016, determined how that would affect streaming data to our Oracle data warehouse, and created a process to mitigate those issues for the go-live weekend. I can’t even begin to describe to you how impressive this is because I don’t even fully understand it myself.
  • Our networking and storage teams were indispensable in preparing for and carrying out the upgrade. I asked for terabytes upon terabytes of space for various tests and mock migrations, as well as innumerable firewall requests, and they delivered every time. The compute guys also gave me additional resources on the app server virtual machines, to which I credit much of our surprisingly small timeline.

It’s a blessing and a privilege to have been a part of such a successful effort. There is nothing more satisfying than doing good work to God’s glory.

If you have any questions about how we approached the upgrade, mock migrations, etc please feel free to ask. I’ll be glad to answer anything that doesn’t put my job at risk ūüėČ

Technical Details

App Server Versions at Upgrade Time

  • 2013: 6.1.0001.0132
  • 2015: 7.0.0001.0129
  • 2016: 8.1.0000.0359

SQL Server Version

  • Microsoft SQL Server 2014 SP2

Dynamics CRM Install\Import “The SQL Server {sqlserver_name} is unavailable”

My apologies for the significant gap between my last post on our CRM 2016 Upgrade Adventure and this one. My time has been consumed with preparing for go-live, but I’ve been keeping track of the roadblocks and caveats we encounter as I go so that I can post about them at later dates.

One of the challenges with this deployment that we did not encounter with the last is a significant increase in firewall and context configuration. LU has, to their credit, made great efforts over the last several years to ensure our network is as secure as possible. With an increase in security, however, comes an increase in complexity.

While setting up CRM you might expect to open a port for the SQL instance (ie 1433). It might also occur to you that UDP 1434 should be opened for the SQL Browser Service. Now your app server has a clear line open to the SQL instance. Everything should be ready, so you go to create or import your organization only to encounter “The SQL Server {sqlserver_name} is unavailable”.

You might also encounter a message about not being able to verify that the SQL Agent is running. Being a thorough Sys Admin\DBA you check the SQL services for these and confirm both are up. You also use telnet or another utility to confirm that the ports are indeed open, so what on earth could CRM need in order to reach SQL?

TCP 445‚Ķ that’s right. Because of the unique setup of CRM it requires TCP 445 to do any kind of setup. What is TCP 445 you ask? “Active Directory service required for Active Directory access and authentication.” (https://technet.microsoft.com/en-us/library/hh699823.aspx). Why an app server would need an AD authentication port opened to the SQL server is anybody’s guess, but it cleared our issue right up. All system checks passed and it happily imported our database.

It should be noted, if you’re using an Availability Group setup then this port will need to be opened to the other servers in the AG as well. I have had the most success when opening it to the AG listener name as well as all nodes.

Bonus Round

If none of this helps you, here are some other things I’ve found are necessary to appease the install\import wizards.

  • Make sure you’re in the local Administrators group on the app servers as well as every node in the SQL cluster or AG (added explicitly, not through a group) .
  • Make sure your account has the sysadmin role on the SQL instance.
  • Specify the SQL server name using the backslash notation, even if the AG name doesn’t contain it. For instance, if your AG is normally accessed as SQLAGINSTANCE,50000 you would use SQLAGINSTANCE\SQLAGINSTANCE,50000 in the wizard. It seems to be hard-coded to only accept it in that manner.

Dynamics CRM Import Fails on “Upgrade Indexes”

As I mentioned in the last post, I’m taking you through our adventure in upgrading the existing on-premise Dynamics CRM 2011 environment to 2016 (and eventually 2016 cloud). Previously I discussed the first show-stopper error we received, “Must declare the scalar variable ‚Äú@table‚ÄĚ.” Following that resolution the import continued past the stage¬†‚ÄúMetadata xml upgrade: pass 1‚ÄĚ but then failed at¬†“Upgrade Indexes”.

Through the use of trace logs obtained by using the CRM diagnostic tool, we discovered that the import wizard was marking a number of indexes to be dropped and then recreated. However, as observed through a SQL Profiler deadlock trace, it was trying to drop and add indexes on the same table at the same time. As I mentioned in my previous post, our database is in excess of 1.5TB. One of the largest tables is ActivityPointerBase, and it’s also one on which many index operations were being executed by the import wizard. The result is that some of the index operations would be chosen as the deadlock victim, causing the import wizard to¬†throw an error and exit. Also, if you restarted the import it would process the entire list again, not taking into account any that it had dropped and recreated already.

My coworker, and local wizard, Bret Unbehagen used the trace logs to determine which tables the import wizard was using to store its index operation information. He then created the query below to produce a list of indexes that it was trying to recreate as well as generate a drop statement for each of those.

So, the basic workflow is 1) let the import wizard¬†proceed until it fails at¬†“Upgrade Indexes”, 2) run the script above against your organization database to list the indexes that it wants to rebuild, 3) use the generated drop statements to preemptively drop those indexes, 4) restart the import so that it continues were it left off (feature of 2013+).

In our experience, this allowed the import wizard to continue through the “Upgrade Indexes” section without deadlocking and proceed with the import. Hopefully it can help you achieve success as well. If you have any questions please feel free to comment. Also, if you’d like to see more from Bret his information is listed below.

Bret Unbehagen (Twitter: @dbaunbe; Web: dba.unbe.org)

Dynamics CRM Import Error “Must declare the scalar variable “@table”

This post is the first in a new series I’m going to call “CRM 2016 Upgrade Adventure”. Summary: my organization has taken on the ambitious¬†challenge of not only upgrading our existing Dynamics CRM 2011 environment to the 2016 version but ¬†of moving it to the cloud service as well. Aside from getting the vanilla components through three versions (2013, 2015, 2016) there are all of the custom integrations that have been tied into CRM over the years that must come along too. That is why it is neither a joke nor hyperbole when I label this as an adventure. We are only in the initial months of this effort and I promise you that plenty of adventure has already been had.

Our first headache… I mean, adventure… was encountered while importing the 2011 database to 2013. ¬†(In order to get to¬†2016 you have to “hop” your database through¬†2013, 2015, and 2016 by importing it to each version.) Initially we encountered some messages about incompatible components from the CRM 4.0 days, which our database started out in. That was no surprise. The developers quickly updated our current¬†system and we went to import again assuming the path was clear. What I encountered instead was almost instant failure. Within 20 minutes the import had failed. Knowing this was a process that should take several hours (our database exceeds 1.5TB), I took a look at the logs to see what the issue was. During the stage “Metadata xml upgrade: pass 1” the following was listed:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Data.SqlClient.SqlException: Must declare the scalar variable “@table”.

I’m sure you can appreciate my confusion since this is a closed-source application written by Microsoft in which I have no option to declare variables. Googling only returned articles about people writing their own applications. Feeling we had no other recourse, we opened up a support ticket with Microsoft. That in itself was quite an adventure that spanned two weeks, but I’ll give you the short version. (Props to my coworker John Dalton for his endless hours on the phone with Microsoft through nights and weekends.) In the end the culprit was a trigger that was on our database, but not just any trigger. This one is named “tr_MScdc_ddl_event” and is created by Microsoft when you enable CDC on a database. After scripting out that trigger to make sure we could recreate it later, then dropping it, the import continued past¬†“Metadata xml upgrade: pass 1” successfully.

TLDR version

Microsoft’s database level CDC trigger tr_MScdc_ddl_event interferes with the Dynamics CRM import operation. Drop that trigger before the import it then add it back once it’s finished and you shouldn’t have issues with this error.

So that’s the end of the adventure right? Everything worked fine after that? Not even close! Stay tuned…