+1-888-365-2779
Try Now
More in this section

Forums / Developing with Sitefinity / Migrating Database From MySQL to MsSql

Migrating Database From MySQL to MsSql

3 posts, 0 answered
  1. Chris
    Chris avatar
    5 posts
    Registered:
    21 Mar 2012
    28 Mar 2012
    Link to this post
    Hello,

    I just made an attempt at migrating a Sitefinity site using a MySQL database over to MsSql, but I am running into a few issues with key constraints.  I am wondering if anyone has any suggestions on how I might be able to complete this migration successfully. 

    Here are the steps I have followed thus far, along with the issues I am currently experiencing:

    1. Install Microsoft SQL Server Migration Assistant for MySQL
    2. I used mostly default settings when migrating the MySQL table structures over to an empty MsSql database
    3. After the table structures were migrated and synched over to MsSql, I migrated the data from the MySQL Sitefinity database over to the newly created MsSql database.  This completed with all items showing 100% success rate
    4. Next, I ran through all tables in the MsSql database that the migration assistant created, and updated the following data types (to match what Sitefinity expects to find):
        a. varchar(40) --> uniqueidentifier (only on ID columns)
        b. binary(1) --> tinyint
    5. Lastly, I modified the connectionString found inside /wwwroot/SITENAME/App_Data/Sitefinity/Configuration/DataConfig.config to point to the MsSql database instead of the MySQL database

    After all of that was complete, I tried load the site, but ran into the following exception:

    The constraint 'PK_sf_permissions_inheritance_map_sf_permissions_inheritance_map_id' is being referenced by table 'sf_security_roots_sf_permissions_inheritance_map', foreign key constraint 'sf_security_roots_sf_permissions_inheritance_map$ref_sf_scrty_rts_sf_prmssns_nhrtnc_mp_sf_prmssns_inheritance_map'.
    Could not drop constraint. See previous errors.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: Telerik.OpenAccess.RT.sql.SQLException: The constraint 'PK_sf_permissions_inheritance_map_sf_permissions_inheritance_map_id' is being referenced by table 'sf_security_roots_sf_permissions_inheritance_map', foreign key constraint 'sf_security_roots_sf_permissions_inheritance_map$ref_sf_scrty_rts_sf_prmssns_nhrtnc_mp_sf_prmssns_inheritance_map'.
    Could not drop constraint. See previous errors.


    Here's the stack trace:

    [SQLException: The constraint 'PK_sf_permissions_inheritance_map_sf_permissions_inheritance_map_id' is being referenced by table 'sf_security_roots_sf_permissions_inheritance_map', foreign key constraint 'sf_security_roots_sf_permissions_inheritance_map$ref_sf_scrty_rts_sf_prmssns_nhrtnc_mp_sf_prmssns_inheritance_map'.
    Could not drop constraint. See previous errors.]
       Telerik.OpenAccess.RT.Adonet2Generic.Impl.StatementImp.executeUpdate(String sql) +420
       Telerik.OpenAccess.RT.Adonet2Generic.Impl.StatementImp.execute(String sql) +9
       OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.ExecuteSqlScript(String script, Connection con, SqlDriver sqlDriver, Boolean noShredder) +459
    
    [OpenAccessException: SQL exception on 'ALTER TABLE [sf_permissions_inheritance_map] DROP CONSTRAINT [PK_sf_permissions_inheritance_map_sf_permissions_inheritance_map_id]' : The constraint 'PK_sf_permissions_inheritance_map_sf_permissions_inheritance_map_id' is being referenced by table 'sf_security_roots_sf_permissions_inheritance_map', foreign key constraint 'sf_security_roots_sf_permissions_inheritance_map$ref_sf_scrty_rts_sf_prmssns_nhrtnc_mp_sf_prmssns_inheritance_map'.
    Could not drop constraint. See previous errors.]
       Telerik.OpenAccess.RT.ExceptionWrapper.Throw() +13
       OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.throwBuildException(String str, Exception e) +47
       OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.ExecuteSqlScript(String script, Connection con, SqlDriver sqlDriver, Boolean noShredder) +639
       OpenAccessRuntime.DataObjects.tools.ant.DataObjectsTaskBase.runScript(String script) +142
       OpenAccessRuntime.DataObjects.tools.ant.ExecuteScriptTask.execute() +359
    
    [OpenAccessException: Telerik.OpenAccess.OpenAccessException: SQL exception on 'ALTER TABLE [sf_permissions_inheritance_map] DROP CONSTRAINT [PK_sf_permissions_inheritance_map_sf_permissions_inheritance_map_id]' : The constraint 'PK_sf_permissions_inheritance_map_sf_permissions_inheritance_map_id' is being referenced by table 'sf_security_roots_sf_permissions_inheritance_map', foreign key constraint 'sf_security_roots_sf_permissions_inheritance_map$ref_sf_scrty_rts_sf_prmssns_nhrtnc_mp_sf_prmssns_inheritance_map'.
    Could not drop constraint. See previous errors.]
       Telerik.Sitefinity.Data.OA.OpenAccessConnection.UpgradeDatabase(Database database) +309
       Telerik.Sitefinity.Data.OA.OpenAccessConnection.UpdateMetadata(MetadataSource metadataSource, String moduleName, List`1 dynamicTypesToRegister, IOpenAccessMetadataProvider provider) +2873
    
    [Exception: Unable to upgrade database schema metadataSource provided by 'SitefinityDynamicBase': Telerik.OpenAccess.OpenAccessException: SQL exception on 'ALTER TABLE [sf_permissions_inheritance_map] DROP CONSTRAINT [PK_sf_permissions_inheritance_map_sf_permissions_inheritance_map_id]' : The constraint 'PK_sf_permissions_inheritance_map_sf_permissions_inheritance_map_id' is being referenced by table 'sf_security_roots_sf_permissions_inheritance_map', foreign key constraint 'sf_security_roots_sf_permissions_inheritance_map$ref_sf_scrty_rts_sf_prmssns_nhrtnc_mp_sf_prmssns_inheritance_map'.
    Could not drop constraint. See previous errors.]
       DynamicModule.ns.Wrapped_OpenAccessModuleBuilderDataProvider_7c30230f96f84aeba49e498c6687f2d7.Initialize(String providerName, NameValueCollection config, Type managerType) +405
       Telerik.Sitefinity.Data.ManagerBase`1.InstantiateProvider(IDataProviderSettings providerSettings, Type providerType, ExceptionPolicyName policy, ManagerBase`1 manager) +1976
       Telerik.Sitefinity.Data.ManagerBase`1.InstantiateProvider(IDataProviderSettings providerSettings, ExceptionPolicyName policy, ManagerBase`1 manager) +74
       Telerik.Sitefinity.Data.ManagerBase`1.SetProvider(String providerName, String transactionName) +526
       Telerik.Sitefinity.DynamicModules.Builder.ModuleBuilderManager..ctor() +14
    
    [TargetInvocationException: Exception has been thrown by the target of an invocation.]
       System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck) +0
       System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache) +117
       System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean skipCheckThis, Boolean fillCache) +247
       System.Activator.CreateInstance() +88
       Telerik.Sitefinity.Data.ManagerBase`1.GetManager(String providerName, String transactionName) +125
       Telerik.Sitefinity.DynamicModules.Builder.Install.ModuleInstaller.RegisterAllPipes() +38
       Telerik.Sitefinity.Publishing.PublishingModule.Initialize(ModuleSettings settings) +108
       Telerik.Sitefinity.Services.SystemManager.InitializeModule(ModuleSettings settings, InstallContext installContext, Boolean start) +573
       Telerik.Sitefinity.Services.SystemManager.Initialize() +1231
       Telerik.Sitefinity.Abstractions.Bootstrapper.RegisterRoutes(RouteCollection routes) +300
       Telerik.Sitefinity.Abstractions.Bootstrapper.Bootstrap() +386
       Telerik.Sitefinity.Web.SitefinityHttpModule.Init(HttpApplication context) +114
       System.Web.HttpApplication.InitModulesCommon() +192
       System.Web.HttpApplication.InitInternal(HttpContext context, HttpApplicationState state, MethodInfo[] handlers) +1504
       System.Web.HttpApplicationFactory.GetNormalApplicationInstance(HttpContext context) +348
       System.Web.HttpApplicationFactory.GetApplicationInstance(HttpContext context) +200
       System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr) +286
    

    There are apparently issues with every single key constraint that was setup for the inheritance maps.  Has anyone seen this before?  Does anyone know what I might be doing incorrectly in the process that I've followed, or have any suggestions on how I can get this working?

    Thank you,

    Chris
  2. Syarif
    Syarif avatar
    1 posts
    Registered:
    30 Jun 2015
    27 Oct 2015
    Link to this post

    Hi is there any answer to this post? kinf od need it the soonest

    Thanks, 

     Syarif

  3. Angel Moret
    Angel Moret avatar
    29 posts
    Registered:
    26 Sep 2016
    29 Oct 2015
    Link to this post
    Hello Syarif,

    A migration from one database platform to another might work, but is not officially supported. We haven't tested such a migration, which means doing so could carry potential risks to your site.

    One idea of how this migration could be possible is to transfer the data from one Sitefinity site to another via API usage. By creating a Sitefinity site with a MsSql backend database and then, using the Sitefinity API, get data from one site and make it available in the other site. This could be done by connecting the providers for modules and make them available in the MsSql-backed site.

    For example, to connect the News module providers between the two sites:

    1. Add a connection string to the MySql site by going to Administration > Settings > Advanced > Data > Connection strings and create new connection string "Test".

    2. Go to Administration > Settings > Advanced > News > Providers and create new provider, copy the settings from the default NewsProvider and add two Parameters to the new provider:

    Key: applicationName
    Value: /News

    and another parameter:

    Key: connectionString
    Value: Test (this is taken from step 1)

    Restart the application and now the news module will have the data available from the other database.

    With this to transfer the data from the other database to this one, by calling the provider. For example:

    NewsManager.GetManager("MyNewNewsProvider");

    This will query the data from one provider, which you can then and transfer it to the other.

    Revision history can also use the above approach. The manager class for working with revision history is VersionManager and also providers can be added from Administration > Settings > Advanced > Version.

    Again I would like to mention that a scenario such as this has not been tested on our end.

    Regards,
    Angel Moret
    Telerik
     
    Do you want to have your say in the Sitefinity development roadmap? Do you want to know when a feature you requested is added or when a bug fixed? Explore the Telerik Sitefinity CMS Ideas&Feedback Portal and vote to affect the priority of the items
     
3 posts, 0 answered