none
default values of primary keys not intialized in SqlSyncScopeProvisioning.PopulateFromScopeDescription RRS feed

  • Question

  • We have built a SQL to SQL synchronization system that uses WCF to process changes. In part this is based on the samples provided. The synchronization seems to work ok, however the "cloning" of a peer database seems to be missing default value assignments. Specifically we have a primary key column that uses the newsequentialid(), we extract the scope on the source side with SqlSyncDescriptionBuilder.GetDescriptionForScope and apply it on the client side with SqlSyncScopeProvisioning.PopulateFromScopeDescription. Everything except the default values for our primary key colum is created correctly.


    Robert G
    Friday, February 12, 2010 3:23 PM

Answers

  • looking at SyncFx using Reflector, the default constraints looks like it should have been included in the GetDescriptionForScope and should have been applied in the PopulateFromScopeDescription.

    have you tried checking the contents of the DbSyncScopeDescription Tables collection and inspecting the Columns collection?  Or maybe using GetDescriptionForTable method instead to retrieve the schema information.

    Tuesday, February 16, 2010 12:25 AM
    Moderator

All replies

  • 1. What did you on "Clone" exactly?  Backup/Restore or ...
    2. The PK column is defined as UNIQUEIDENTIFIER ROWGUIDCOL with default constraint newsequentialid(), right?

    If you overall concern is that the constraint newsequentialid() is not script out during table/column provisioning, then you could modify the script and apply it manually on the target server/database.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 12, 2010 10:34 PM
    Answerer
  • By "cloning" I am refering to the creation of a peer database. Essentially we have a synchronization pair where one side is authoratiative with regards to schema. Since we cant update scopes dynamically , if the schema of the side that is authoratative changes, we create a new scope, include everything in it and call  SqlSyncDescriptionBuilder.GetDescriptionForScope, this is transmitted to the partner. When that scope does not match the local scope, we drop the database completely and rebuild it using SqlSyncScopeProvisioning.PopulateFromScopeDescription. That process is what I refered to as our "clone".

    yes the PK column is defined as you indicate,my concern is that this is not included in the actual definition, so I need to find any differences between the description generated by GetDescriptionForScopeand the actual underlying table defintions. So I guess my actual question is, other than the default value constraints , what is not included in the GetDescriptionForScope or applied in the PopulateFromScopeDescription ?


    Robert G
    Saturday, February 13, 2010 2:38 PM
  • So you have at one database provisioned with for sync scope.  And you would like to create another database with the same table schema for sync.

    1. Use the SQL Management Studio, expand the *USER* tables from the object explore, under the source database.  Script out table creation object from the context menu "Script Table as" -> "Create to" of each USER table.
    2. Apply all USER table creation script to the target database.
    3. Get DbSyncScopeDescription from SqlSyncDescriptionBuilder.GetDescriptionForScope.
    4. Get SqlSyncScopeProvisioning object from new SqlSyncScopeProvisioning(DbSyncScopeDescription )
    5. On SqlSyncScopeProvisioning object, do SetCreateTableDefault(DbSyncCreationOption.Skip)
    6. On SqlSyncScopeProvisioning object, do Apply(target database connection).

    All story here is to use the SMO object (used by the SQL management studio) to help you to get the table creation script.  And when you do the provisioning on the target database, skip the table creation part.

    By the way, I have reported the default constraint scripting issue in the database provisioning process to the development team and they are going to triage it.

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, February 14, 2010 9:55 PM
    Answerer
  • looking at SyncFx using Reflector, the default constraints looks like it should have been included in the GetDescriptionForScope and should have been applied in the PopulateFromScopeDescription.

    have you tried checking the contents of the DbSyncScopeDescription Tables collection and inspecting the Columns collection?  Or maybe using GetDescriptionForTable method instead to retrieve the schema information.

    Tuesday, February 16, 2010 12:25 AM
    Moderator