locked
Migrate changed scope_info & scope_config data from Development to Test Environment RRS feed

  • Question

  • I am working with sync framework 2.1 synchronizing SQL Server 2005 with SQL Express 2008. I am wondering the best practice to migrate scope_info and scope_config data from my development SQL Server (DEV) to my test SQL Server (SIT).

    In development and testing regions I have synchronization set up and running.

    Given the following scenario: During current development I need to make a table change (add column). I run a de-provision/re-provision and it rebuilds my scope_info, scope_config, and tracking tables. No problem here in development.

    When I want release these changes to my test SQL Server what is the best way to get the new table schema (scope information) from development to test? I have previously run into several issues when doing data compares against the two SQL Server regions.

    Note: In my office I am not allowed to let the application make any SQL Server object changes. So de-provisioning/re-provisioning is not an option on the test SQL Server (SIT).

    I have read the following article but I can't find a clear picture of what to do for DB migration.

    How to: Provision a Server Database for Collaborative Synchronization

    Any advice would help.

    Thanks, Jason


    Wired4This
    Wednesday, July 27, 2011 12:57 PM

Answers

  • just to clarify, are you trying to migrate the scope definition only? or are you trying to migrate the sync knowledge as well?

    if its the former, assuming you've sync the SPs, UDTs, Tables, triggers already, you can simply update the scope_config.config_data column of your scope. you only need to invalidate the sync knowledge  and tracking tables on the test server if you've changed PKs or filters. Otherwise there is no need to wipe out the tracking tables and the sync knowledge.

    and btw, the scope name is unique in the scope_info, so you can simply use the scope name for the update. find the scope_id in the test matching your scope name, use that scope id to find the matching scope_config entry.

    • Marked as answer by Jason_Keith Friday, July 29, 2011 1:07 PM
    Thursday, July 28, 2011 11:08 PM

All replies

  • assuming you are able to give your DBAs a script to run the deprovisioning/provisioning process, both SqlSyncProvisioning and SqlSyncDeprovisioning classes has scripting capabalities.

    other than that, there is no way you can migrate your scope definition without changing database objects. provisioning creates stored procs, UDTs, triggers and tables. when you change the schema such as adding or removing a column, the stored procs and UDTs need to change. if you add filters, the tracking table and triggers need to change as well.

    Thursday, July 28, 2011 1:28 AM
  • Sorry I left the object that part out. Yes, I understand objects also need to be changed. I am using a database object compare tool which makes development SQL Server and test SQL Server exactly the same (ex. tables, triggers, stored procs, etc).

    I am only coming into problems when doing data compares when trying to update the scope data in the testing environment. The things I am most concerned about are migrating data for the following tables: scope_info, scope_config, and tracking (cleanup maybe). I know the scope config data needs to be updated in scope_config, but things like sync knowledge and scope_id relationship to tracking tables are causing problems for me (when migrating scope info data from DEV to TST) at the moment. 

    From previous experience I have learned that you can't copy knowledge information from one environment to another environment. There is an imbedded timestamp which will not match from server to server. This causes data to not sync properly. Also I have learned that there is a strong relationship between the scope_info.scope_id and tracking tables.

    When doing a deprovision/reprovision in development I notice the sync knowledge and learned info in tracking tables gets cleared.

    Is there any way to data compare the scope information from the development SQL Server to the test SQL Server? Can I copy scope information from development to test environment if I imitate a deprovision/reprovision (null the sync knowledge and remove any learned information (create_scope_local_id, update_scope_local_id) from the tracking tables)? I wish I could just update the scope_config.config_data column but the config_id changes when I do a deprovision/reprovision so the update statement doesn't have the same ID to update on.

    Any advice will be helpful.

    Thanks, Jason


    Wired4This
    Thursday, July 28, 2011 2:38 PM
  • just to clarify, are you trying to migrate the scope definition only? or are you trying to migrate the sync knowledge as well?

    if its the former, assuming you've sync the SPs, UDTs, Tables, triggers already, you can simply update the scope_config.config_data column of your scope. you only need to invalidate the sync knowledge  and tracking tables on the test server if you've changed PKs or filters. Otherwise there is no need to wipe out the tracking tables and the sync knowledge.

    and btw, the scope name is unique in the scope_info, so you can simply use the scope name for the update. find the scope_id in the test matching your scope name, use that scope id to find the matching scope_config entry.

    • Marked as answer by Jason_Keith Friday, July 29, 2011 1:07 PM
    Thursday, July 28, 2011 11:08 PM
  • Thanks June. I believe this is exactly what I was looking for. I believe I was missing the part that where I could use the scope name to reflect back to the scope_config.config_data. I am out of the office today. I'll give this a try on Monday.

    As far as migrating sync knowlege, I would suggest nobody should sync the knowledge from one sql server (DEV) to another sql server (TST) due to the min_active_rowversion being different on each server and I believe this is stored somewhere in sync knowledge. From previous experience this caused data not to sync properly.

    Thanks again, Jason


    Wired4This
    Friday, July 29, 2011 1:07 PM