Database migration

Have a question? Welcome here!
User avatar
jjeffman
Posts: 91
Joined: Thu Dec 08, 2011 1:05 pm
Location: Porto Alegre, Brazil

Database migration

Postby jjeffman » Fri Mar 04, 2016 2:17 pm

Hello Artem,

I would like to migrate from MySQL to MSSQL. Is it an easy task ? I have already several records on the MySQL database.
Best regards.

Jayme Jeffman

User avatar
Artem
eTraxis Support
Posts: 227
Joined: Mon Sep 15, 2008 2:51 am
Location: Auckland, New Zealand
Contact:

Re: Database migration

Postby Artem » Sat Mar 05, 2016 7:08 am

Hello Jayme,

No that easy at all. If I had to do it, I would try the following for each datatable:

- disable autoincrements in MSSQL table,
- select the MySQL table and save it in some portable form (e.g. CSV),
- insert data from resulted CSV into MSSQL datatable,
- enabled autoincrements in the MSSQL table.

Also, it's better to do it for each table one by one in exactly the following order:
  1. tbl_sys_vars
  2. tbl_accounts
  3. tbl_projects
  4. tbl_groups
  5. tbl_membership
  6. tbl_templates
  7. tbl_group_perms
  8. tbl_states
  9. tbl_state_assignees
  10. tbl_group_trans
  11. tbl_role_trans
  12. tbl_fields
  13. tbl_field_perms
  14. tbl_records
  15. tbl_children
  16. tbl_reads
  17. tbl_record_subscribes
  18. tbl_events
  19. tbl_field_values
  20. tbl_changes
  21. tbl_float_values
  22. tbl_string_values
  23. tbl_text_values
  24. tbl_list_values
  25. tbl_comments
  26. tbl_attachments
  27. tbl_filters
  28. tbl_filter_sharing
  29. tbl_filter_activation
  30. tbl_filter_accounts
  31. tbl_filter_states
  32. tbl_filter_trans
  33. tbl_filter_fields
  34. tbl_views
  35. tbl_view_columns
  36. tbl_view_filters
  37. tbl_subscribes
  38. tbl_reminders
To disable autoincrements you can use "SET IDENTITY_INSERT" MSSQL command. For example, for "tbl_views" table the script could look like below:

Code: Select all

SET IDENTITY_INSERT [dbo].[tbl_views] ON
GO

INSERT INTO [dbo].[tbl_views] (view_id, account_id, view_name) VALUES (1, 1, 'My view')
INSERT INTO [dbo].[tbl_views] (view_id, account_id, view_name) VALUES (2, 1, 'My another view')
INSERT INTO [dbo].[tbl_views] (view_id, account_id, view_name) VALUES ...
...
GO

SET IDENTITY_INSERT [dbo].[tbl_views] OFF
GO
All you need here is to generate approriate list of "INSERT" commands, which I believe you're able to script in some way having CSV-files with data.
Best regards,
Artem

User avatar
jjeffman
Posts: 91
Joined: Thu Dec 08, 2011 1:05 pm
Location: Porto Alegre, Brazil

Re: Database migration

Postby jjeffman » Mon Mar 07, 2016 2:26 pm

Thank you very much Artem.

I will rethink the need of migration.

Thank you very much.
Best regards.

Jayme Jeffman


Who is online

Users browsing this forum: No registered users and 1 guest