AnsweredAssumed Answered

Migrating from MySQL to Microsoft SQL Server

Question asked by staganyi staganyi on Apr 4, 2014
Latest reply on Apr 4, 2014 by Jason Eggers
I migrated our SugarCRM CE v6.5.13 MySQL database to Microsoft SQL Server 2008 R2 using the Microsoft SQL Server Migration Assistant for MySql. That process worked fine without any errors. Then I copied over the Sugar install folder from a Windows Server 2012 web server to a Windows Server 2008 R2 one. Once copied over, I logged into the site and navigated to "Admin->Repair->Quick Repair and Rebuild". Naturally, it found many differences between the database and vardefs. They were all fine except for the following float fields: campaigns.budget, campaigns.expected_cost, campaigns.actual_cost, campaigns.expected_revenue, opportunities.amount, opportunities.amount_usdollar & opportunities.probability. Here's the generated SQL statement for one of them (they're all the same):

/* Table : campaigns *//*COLUMNS*/
/*MISMATCH WITH DATABASE - budget -  ROW [name] => 'budget'  [type] => 'float'  [len] => '8'  [default] => '(NULL)'  */
/* VARDEF - budget -  ROW[name] => 'budget'  [vname] => 'LBL_CAMPAIGN_BUDGET'  [type] => 'float'  [dbType] => 'double'  [comment] => 'Budgeted amount for the campaign'  [len] => '8'  [required] => ''  */
 ALTER TABLE campaigns DROP CONSTRAINT DF__campaigns__budge__47A6A41B ALTER TABLE campaigns ALTER COLUMN budget float(8) NULL ;

After lots of testing, here's what happens. If I run it, the field is converted to type "real" in the db. Running "Quick Repair and Rebuild" again will obviously complain because it is no longer a float. Changing the field back to type float in SQL Server requires a table drop first.

For the fun of it, I did a fresh install of Sugar on SQL Server and the db and vardefs appear to exactly match the migrated one. But the fresh install has no complaints when running a "Quick Repair and Rebuild".

I guess the real question is why is it complaining in the first place because the db and vardefs are both set to float(8)? What is the "Quick Repair and Rebuild" actually comparing so I can try and troubleshoot this issue further.