Upgrading 2.0 to 2.1 RRS feed

  • Question

  • Does anyone have detailed info on how SqlSyncStoreMetadataUpgrade Class works? And exactly what items it changes/adds? In our implementation of the SyncFX we don't use a scope_config table instead we build our scopes and adapters more dynamically. When I try to run it on our database it tells me that the db isn't provisioned which is probably because it doesn't see a scope_config table. I'm trying to figure out what info this class needs to do its job. Does it just upgrade the metadata in the scope_info table or does it also modify data in the tracking tables? Thanks!
    • Edited by Cody Ca Tuesday, May 29, 2012 5:58 PM
    Tuesday, May 29, 2012 5:52 PM

All replies

  • Okay, I was able to use reflector to see what it was doing and it actually doesn't modify any data regardless of what the documentation may lead you to believe. It only makes a few schema changes. I came up with the following script to upgrade our databases so we don't need to use the SqlSyncStoreMetadataUpgrade class.

    IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N'schema_info' AND s.name = N'dbo')
    CREATE TABLE [schema_info] ([schema_major_version] integer NOT NULL, [schema_minor_version] integer NOT NULL, [schema_extended_info] nvarchar(100) NOT NULL)
    ALTER TABLE [schema_info] ADD CONSTRAINT [PK_schema_info] PRIMARY KEY ([schema_major_version], [schema_minor_version])

    IF NOT EXISTS (SELECT [schema_major_version] FROM [schema_info]) INSERT INTO [schema_info] ([schema_major_version], [schema_minor_version], [schema_extended_info]) VALUES (2, 1, N'')

    ALTER TABLE [scope_info] ADD [scope_user_comment] nvarchar(max) NULL

    --TODO: Call this stored proc

    -- Param 1 "@objname" As SqlDbType.NVarChar = "[scope_info].[scope_name]"
    -- Param 2 "@newname" As SqlDbType.NVarChar = "sync_scope_name"
    -- Param 3 "@objtype" As SqlDbType.NVarChar = "COLUMN"

    IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N'scope_config' AND s.name = N'dbo')
    CREATE TABLE [scope_config] ([config_id] uniqueidentifier NOT NULL, [config_data] xml NOT NULL, [scope_status] char NULL)
    ALTER TABLE [scope_config] ADD CONSTRAINT [PK_scope_config] PRIMARY KEY ([config_id])

    Tuesday, May 29, 2012 7:56 PM
  • This is not quite accurate and will not successfully provision the changes to the scope_config table for an existing implementation.

    The SqlSyncStoreMetadataUpgrade class performs one more step after the last one shown above: It adds in the scope_status column to an existing implementation if that column does not exist (and it will not if the table already existed).

    You see, normally the scope_config table does exist and so the creation of it (the last action above) is not done and thus the resulting scope_config table is unchanged. The upgrade class, as a final step, adds in this column if it does not exist, thus upgrading the existing table.

    The step to add at the end goes something like this:

    				FROM sys.columns 
    				WHERE Name = N'scope_status'   
    				AND Object_ID = Object_ID(N'dbo.scope_config')
    	ALTER TABLE dbo.scope_config 
    		ADD [scope_status] char NULL CONSTRAINT defaultstatusvalue DEFAULT 'C' WITH VALUES;
    You will observe that when the column is added, the existing rows are populated with the "provisioning completed" status of "C".

    Of course, if you use ObjectSchema and ObjectPrefix (we do), the above script as well as the OP's are slightly different.

    Friday, June 22, 2012 4:49 PM