Migrating MS Dynamics CRM to SugarCRM

I’ve been a bit quiet for the last couple of month.  That’s because I was leading an ambicious project at my new job – migration of a Microsoft Dynamics CRM version 3 to SugarCRM Community Edition version 5.0.0.  There were only three people involved, non of us could afford to work full time on the project, and we only had three weeks to do it.

Read on for a story on why it took us longer, how we did, and if it was a success at all.

First of all, a little background.  The company that I work for now is in financial services sector.  CRM is one of the two vital systems, with trading platform being the second one.  All processes are tied into CRM – registrations of new clients, incoming and outgoing money, email tracking, and so on and so forth.  The system has been installed about a year ago and has accomodated data for a few thousands of clients.

Another important bit to know is that CRM is used quite a lot 24 hours a day, 5 days a week.  Some data is coming in over the weekends too, but there are no real users who access the system.  Also, since the company is working with global clientele, weekend is really between 23:00 of Friday and 23:00 of Sunday.

By now you probably have this question in your head: “Why migrate?”.  What is so wrong with the existing system that it needs to be replaced so fast and so furious?

The company was preparing for a huge expansion.  Firtst of all, there was a take over of another company scheduled later that month.  That meant an almost sudden expansion of a few thousands of clients to a few tens of thousands of clients.  Secondly, a huge marketing campaign was starting up.  Thirdly, a few key processes were about to be change (the way new clients were registered).  Fourthly, there was a huge need for automation and extended functionality.

With all that, there was a very limited in-house expertise for the existing system.   SugarCRM had this nice advantage of being implemented with LAMP stack (Linux, Apache, MySQL, PHP), which was way more familiar to most of the IT people in the company.  And all the source code was there.

Another big reason for a change, was realization of how tied in the MS CRM is into company’s network.   While considering the upgrade of the MS Dynamics CRM to version 4, most of the people were horrified of the upgrade path.  In order to upgrade the CRM, we first had to upgrade our MS Exchange server.  Which meant that we had to upgrade the operating system of the machine.  Which meant that we had to upgrade the hardware of the server.  And MS Exchange upgrade was strongly suggesting upgrades of quite a few other bits and pieces of software and hardware.  Seemed like a really complicated and dangerous thing to do.

One thing I wanted to note separately, was how easy it was to sell SugarCRM to the management.  An open source stack meant that we were could be in full control of the system.  Independent software pieces meant that we could replace and upgrade them separately.  Fully web based technology aligned very well with many other company tools.  The brief demonstration of SugarCRM got them totally convinced that the system is mature enough and can handle everything the company needs, and more.  The fact that SugarCRM could be easily installed on a laptop in just a couple of minutes helped.  The nice look and selection of themes in default out of the box installation helped.  The selection of modules in out of the box installation helped.  And the possibility to install the system with plenty of sample data helped too.  Overall, it took me less than two hours to convince the management that the system is good enough.  And I wasn’t even trying hard.

So, what’s with the timing?  We were given three weeks.  In fact, it was two weeks for migration and one week for user training.  We had to create a full replica of the existing system.  Migrate all the data and implement all the functionality to support all the company processes.  It took us three weeks to do it.  Because we had no time to train the users, and because the company was on the doorstep of a large take over, we had to suspend the CRM migration efforts.

It took the company about three weeks to integrate all the new data.  Also, quite a few processes were changed.  And the company grew plenty of new staff in all departments.

When the question of the CRM migration was important once again, we had to face quite a few changes.  Much much much more data than we originally tested with.  A lot of changes in the ways things were handled.  More users.  And more automation was needed.  The replica of the old CRM was of no value anymore.

It took us about three more weeks to adjust our installation of SugarCRM to fit the new requirements.  Even now that the migration is officially over, we still have quite a few things to fix and improve.  But that’s going to be a live process from now on.

The project was very challanging for the company, the team, and me personally.  It was a complex thing technically, politically, and socially.  For me personally, it was also quite a new management experience.  I’ve been doing some project management before, but never had to deal with such magnitude, and especially in such tight deadlines.  The good thing though was that I had to pick my own team.

Here are a few things that I’ve learned and a few things that I think are important to share.

Technology-wise, thanks heavens for CSV (file format for comma separated values).  This was the backbone of the whole migration process.  Here is how it works.  MS Dynamics CRM has a very limited exporting functionality.  Getting things directly out of the database don’t make sense at all.  For example, our specific installation had more than 220 tables, all linked to each other.  On top of that there were almost 400 views and something around 60 stored procedures.  There was no way that we could analyze these structures in any meaningful period of time.  So we did an export with entity by entity method.  Gladly, there weren’t that many entities.  There is a way to list all entities in tabular form, with columns upon columns of entity values.  The only sensible format to save that list was in HTML.

Because we had huge sets of data, MS CRM couldn’t handle the listing properly.  So we had to come up with search criterias to separate the listings into smaller chunks of about 10,000 records each.  Now, I’ll give you a second to imagine an HTML file with a table, which has around 10,000 rows and anywhere from 100 to 500 columns.

The way to handle these HTML files was to load them up in MS Excel.  It should also better be a newer version of Excel, that can handle more than 256 columns.  MS Excel is wonderful at pasing those HTML tables, but it doesn’t offer much help in saving them in CSV.  CSV in MS Excel is weird.  So, what we ended up doing was using MS Excel to convert HTML tables into older version of Excel, so that we could open them up in OpenOffice.  OpenOffice is wonderful at saving CSV.  You can pick and choose delimeters, quoting symbols, etc.  The point is to make CSV files which can be parsed by PHP.

Here are two things to remember.  First, older Excel formats can’t work with more than 256 columns.  The trick we were using was the sheets.  Copy data of the extra columns into another sheet of the same file, and it will be much easier to process later.  Second, if you will do several exports of data from MS CRM, remember that columsn won’t necessarily be in the same order.  In fact, chances are that columns won’t be in the same order.  The way go about it is to use hashes, or, sorry, associative arrays.  Take the first row, of data, which has the column titles, build an index array out of it, and then go through data rows, assigning values to appropriate keys.  Don’t use indexes for data, or you will have to inspect the order and adjust your SugarCRM importing scripts on every export.

Another technical issue is the deployment process of SugarCRM.  We are still in the mess with this and haven’t solved it properly.  We used Subversion for the version control of all the files.  But it’s not enough for SugarCRM, since some of the modules are bulit with its own Studio, and loaded with its own Module Loader.  When this happens, the database is modified, and a few local files are changed also.  We sort of have it working now, with plenty of magic and strict procedure involved, but it’s far from perfect, requires too much manual labour, and is very prone to problems still.  If you have any suggestions for this one, please let me know.

Something else worth noting, is a very unpleasant behavior of SugarCRM in terms of usage tracking.  Surely, there is an option to not send anything, but somehow I find it always sending way too much stuff out.  And the worst part is that these things are hidden, and written in a way that makes them hard to find.  have a look in include/utils/mvc_utils.php and in include/utils/tracker_utils.hp .  These are the two ugliest places.  There is another one which deals with update notifications, which I think I found, but didn’t have time to cut out yet.  I am really surprised to see these in an open source project.  And then again, I’m glad to have all the sources and being able to remove this stuff.  Who knows what goes on in closed source applications…

In terms of project management, I’ve learned to talk to people a lot.  Talking to all the people all the time was the bigger part of my participation in the project actually.  I think I’ve spent close to 80% of time just talking.  I was talking to the members of the team, trying to figure out the best ways to approach problems.  I was talking to people who were supporting MS CRM installation, trying to get as much as possible from them regarding the details of the running system.  I was talking to each user and each user group trying to see what they were using, how they were using it, and what they wanted and needed from the new system.  I was talking to the management a lot, trying to figure out where the company was going and what would be the changes in the near, intermediate and far away future.  I was also talking to a lot of  people outside of the company, trying to see how similar problems were solved there, how they managed projects of this magnitude, how they were dealing with their users, developers, and managers.

I know that many projects take a lot of talking, but in this one we had practically no documentation of the existing system and process, and we had very tight deadlines, so talking ended up being a very effecient way of getting information.

Also, talking helped a lot in extending deadlines where needed, and in keeping users calm.  Somehow, people are taking delays and system limitations very much easier if they know why those limitations are in and when there are problems will be solved.  Sometimes I had to go into technical details explaining why certain things weren’t working or were working different from the previous system.  Sometimes I had do a totally non-technical talk showing to people which other departments exist in the company and how they interact, and how things are prioritiezed and why.

As I said, I did a lot of talking.  More than during any other project that I ever handled.  Of course, I was blessed with excellent teammates who were handling the technical parts of the project.  One person was doing everything and anything about SugarCRM.  Another one was working on all the exports, figuring out other systems and getting stuff off of them.  He also helped a lot with integration of SugarCRM with the other company tools.  Great guys both of them!

Now, this pretty much covers the migration process.  But since the SugarCRM saga in our company is just started, expect more stuff posted.  In the mean time, if you have any questions regarding any aspect of this migration, feel free to ask in the comments.

20 thoughts on “Migrating MS Dynamics CRM to SugarCRM”

  1. Hello there. I didn’t understand why did you use CSV, HTML and excel to export database data? There are several markup languages, created for it. For example, YAML, JSON. Those languages were created for those tasks and, I believe, there were some another way to transfer data between two different databases. Or I there ware some issues, which didn’t let u to do it?

  2. Tiamat,

    First of all, have you ever seen any Microsoft application that allows you to export to YAML or JSON? We were really glad to get the data in a parse-able format.

    Secondly, CSV is one of the most compact formats out there. It contains pure data with the minimum of overhead. Both YAML and JSON would have resulted in much larger files. That would make exporting and importing them harder (longer, more resources needed, etc).

    Thirdly, since we were dealing with a hostile system (MS CRM), we had to keep an eye on what we were getting. CSV, as any other tabular format is excellent for human readability. You can just open a file, scroll through a few lines and columns and notice a problem. JSON is practically unreadable by humans. YAML is not pleasant either.

  3. I don’t remember it now, but we went through all of the formats, and none were actually usable. I think it even has its own CSV export, but it fails to escape data properly, so international strings and separators were all screwed.

  4. Just curious… what metric system did you use to estimate the effort required for the migration exercise? It strikes me that estimated two weeks was always going to be tight to extract, cleanse, transform and load between two different data structures.

    Your comments about the ease of sale are also interesting. Limited inhouse experience, previous microsoft reliance, and they just except this “open source stuff”? Sure being able to demo it on a lappy helps but I think you under-estimate your sales skills or the management team are a lot more progressive than the lot I deal with!

  5. Nick,

    the estimation was indeed very tight. I had less than two days to decide if it would be possible to migrate or not, so I didn’t have much time for the formal analysis. I walked around the office, talked to every type of user (heads of the departments and front line clerks alike), studied integration with other systems (trading platform, mail server, Active Directory, etc), and decided that we could pull it off.

    As for the management, on one hand, they are taking risks easier than any other management I’ve seen before. On the other hand, they are further from the technological issues than many other “rulers” that I’ve worked for. I guess that they were sort of glad that someone was taking the initiative and responsibility, so they let us do it.

    On the selling point, one of the strong arguments turned out to be the LAMP technology behind SugarCRM, which matched nicely with LAMP technology behind all company web sites. The idea of full integration with less programmers (same people can work on CRM and on the web site) seemed obvious. :)

  6. Moving from one CRM to another is a big decision.
    Now let me ask you: how do you compare Dynamics-CRM to SugarCRM-CE in terms of functionality, usage, and covering your sales activities and objectives.

    Thank you

    1. I Totally Understand Leonid.
      We’ve done the same.
      Why? sure CRM is CRM and the basic products do roughly the same.
      -- Licensing -- In Dynamics you pay for the server OS, the clients OS, the database license, the dynamics license, the upgrade assurance, etc etc etc… in essence if you consume oxigen in front of the terminal computer, you are paying for that too. So it’s bad to invest and worse to grow. Sugar can run in anything that runs PHP so you can have-it free or pay for your windows OS and Database as you so see fit. Sugar also allows you to decide on the investment. If you have internal coding team you can go the 100% free way and grow your CRM from there… you you don’t you can evaluate the cost of buying one of the payed versions VS the cost of paying for development services… and decide. Do that with Dynam….eerrrrrr… can’t!
      -- Technology -- It’s common knowledge that Linux and Unix are far more efficient that MS OS, so in truth the cost of scaling up your CRM is Higher on MS both for OS license and the number of needed machines (or hardware specs) due to lack of efficiency from the OS it’s self. Also, MS technology is .NET based, meaning that the framework being used will eat up memory like crazy and instance millions of objects that will probably never be used or needed. This is bad as the way Windows addresses memory space (with 16k chunks) will mean that either those instances are reused frequently, or if collected by garbage will leave huge memory holes leading to memory fragmentation and degrading performance (hence the needed reboot every now and then). That’s something than you can only control up to some point, and the rest is the mayhem of .NET its self…and that you can’t control.
      -- Customization -- Here I see 2 things. For small, simple customization they are both simple to customize. However try something more deep with each one and find Sugar opensourced and ready to work. Dynamics, on the other hand is closed so that path will be harder to walk.
      -- Browser compatibility -- Ever tried a .NET website outside MS Iexplorer world? that you know exactly what I mean. It can be done….ish.

      So Sugar, ProjectOpen, and most Open Source solutions PHP or Java based are my preferred solutions. I could only see an MS Dynamics solution as valid IF:
      -- if it had a free option
      -- the source was available and compilable (or, at least, extend-able with proper low lever operation documentation; making it too close to open-source to make the MS badge!)
      And even so, I would be giving-up a lot of important advantages the OpenSource-OpenTechnology world offers.

  7. Hesham,

    in terms of functionality, both systems are roughly the same. Each of them comes with a bunch of modules, which are connected to each other in a certain way. They both implement the same business concept with Leads, Accounts, Opportunities, Documents, Emails, etc.

    But the thing about CRM systems is that they need customization. Every company is similar to other companies, but it is also unique in one way or another. For CRM to be really useful, it should cover the uniqueness of the company. And that can only be achieved through customization.

    And this is exactly where SugarCRM is a stronger alternatives, in my opinion. Having full access to the source code and straight forward, simple database structure is of great help in customization.

  8. The conversion would have been alot easier with Jitterbit. Take a look at it http://www.jitterbit.com it allows conversion from any data format to any format or protocal. This will talk to databases and SOAP interfaces. Microsoft dynamics and SurgarCRM both have SOAP API’s and database connectors for MS SQL and MySQL. and has data transformations. not to mention syncronization capabilities

  9. “Another technical issue is the deployment process of SugarCRM…….If you have any suggestions for this one, please let me know.”

    I tiered of loosing time (needed to actually deploy and build up my crm) with code versioning, incompatible modules (and some idiocracy embedded into some sugarforge free modules) and decided to use-and-abuse on VMware ESXi feature “snapshot”.

    I suggest you virtualize your infrastructure. Try to have your production servers (front-end and Database) independent from you dev servers, (front-end and Database). You can setup unidirectional scripts from the production Database towards the Development Database (if you must test live Data).
    Then you can snapshot your “versions” of CRM and Database without downtime, test your code on the Dev servers, pass-it onto production servers and rollback easy if thing go wrong.

    Virtualization has been my time/money saver from 1999. That’s the only way I can have a life and have a job in the same 24h.

  10. You are not kidding. My current employer wanted to migrate a sub-set of data from a MsCRM to SugarCRM. We are making progress but it is slow going. One of the largest hurdles was getting MySQL Migration tools to work correctly.

    You stated CSV was used as a inter-mediate data layer. On my side we converted all the data fields to either nvarchar(256), in, or decimal. Then ran it through the convert, re-converting to MySQL datatypes as possible once the migration tool executed successfully.

    Thanks for you article though, it gave us a heads up on how much of a task this would be…

  11. Hi,

    We have similar requirement of migrating data from MSCRM 4.0 to Sugar CRM.I didnt understand the below line “There is a way to list all entities in tabular form, with columns upon columns of entity values.” in this article.

    Could you please explain that?

    We are checking for the ways to get millions of records in MSCRM to CSV format to be able to import into SUGAR CRM.

    please let us know your thoughts.


    1. Hi Swathi,

      this post was written a few years back, so I don’t quite remember how and what exactly we did. But basically, we had to analyze the database schema of the MS Dynamics, which wasn’t the easiest of things. Dynamics itself, if I remember correctly, didn’t provide any useful tools, so we were using MS SQL admin/dev/modeling tools to figure out which tables and fields stored what. Table and field names were all non-descriptive hashes, which didn’t help either.

      I wish you all the good luck and lots of patience -- you’ll need it. :)

      Also, would you mind sharing the reasons for you to go to the SugarCRM solution? After it’s 2016, not 2008, when this post was written. :)

Leave a Comment