How to handle Table Schema changes in SQL Azure Data Sync Preview*

Question

Changing of a tables schema and sync dataset is currently unsupported in the Preview version of SQL Azure Data Sync. However I present this method to all that find yourself in need of this ability *without any warranty implied.

1. Disable Sync
2. Alter <Table> with new column
3. Perform the following in each of your hub/member databases.
  a. Update the config_data in DataSync.scope_config_dss with the new column details
  b. Update the table specific Table Type
    i. DataSync.<Table>_dss_BulkType
    ii. *Several stored procedures rely on this so chose your method of updating/dropping.  There are many guides on how to alter Table Types.
  c. Update the Stored Procedures defined in the config_data.  A sample set below are using the standard naming conventions but be aware that  there may be Guids as suffixes
    i. DataSync.<Table>_dss_bulkinsert
    ii. DataSync.<Table>_dss_bulkupdate
    iii. DataSync.<Table>_dss_insert
    iv. DataSync.<Table>_dss_selectchanges
    v. DataSync.<Table>_dss_selectrow
    vi. DataSync.<Table>_dss_update
4. Renable

Highlighted below are snippets to assist in your update:

<SqlSyncProviderScopeConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" IsTemplate="false">
  <Adapter Name="[dbo].[SyncTest]" GlobalName="[dbo].[SyncTest]" TrackingTable="[DataSync].[SyncTest_dss_tracking]" SelChngProc="[DataSync].[SyncTest_dss_selectchanges]" SelRowProc="[DataSync].[SyncTest_dss_selectrow]" InsProc="[DataSync].[SyncTest_dss_insert]" UpdProc="[DataSync].[SyncTest_dss_update]" DelProc="[DataSync].[SyncTest_dss_delete]" InsMetaProc="[DataSync].[SyncTest_dss_insertmetadata]" UpdMetaProc="[DataSync].[SyncTest_dss_updatemetadata]" DelMetaProc="[DataSync].[SyncTest_dss_deletemetadata]" BulkTableType="[DataSync].[SyncTest_dss_BulkType]" BulkInsProc="[DataSync].[SyncTest_dss_bulkinsert]" BulkUpdProc="[DataSync].[SyncTest_dss_bulkupdate]" BulkDelProc="[DataSync].[SyncTest_dss_bulkdelete]" InsTrig="[dbo].[SyncTest_dss_insert_trigger]" UpdTrig="[dbo].[SyncTest_dss_update_trigger]" DelTrig="[dbo].[SyncTest_dss_delete_trigger]">
    <Col name="Uid" type="uniqueidentifier" size="16" param="@P_1" pk="true" />
    <Col name="BitTest" type="bit" size="1" param="@P_2" />
    <Col name="NvarcharTest" type="nvarchar" size="15" param="@P_3" />
    <Col name="NullableIntTest" type="int" size="4" null="true" param="@P_4" />
    <Col name="NewColumn" type="varchar" size="20" null="true" param="@P_5" />
  </Adapter>
</SqlSyncProviderScopeConfiguration>

Note the addition the NewColumn and parameter name :

    <Col name="NewColumn" type="varchar" size="20" null="true" param="@P_5" />

Sample DataSync.<Table>_dss_BulkType change:

CREATE TYPE [DataSync].[SyncTest_dss_BulkType] AS TABLE(
	[Uid] [uniqueidentifier] NOT NULL,
	[BitTest] [bit] NULL,
	[NvarcharTest] [nvarchar](15) NULL,
	[NullableIntTest] [int] NULL,
	[NewColumn] [varchar](20) NULL, --Add the new column to the BulkType
	[sync_update_peer_timestamp] [bigint] NULL,
	[sync_update_peer_key] [int] NULL,
	[sync_create_peer_timestamp] [bigint] NULL,
	[sync_create_peer_key] [int] NULL,
	PRIMARY KEY CLUSTERED 
(
	[Uid] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

Sample <Table>_dss_selectrow change:

CREATE PROCEDURE [DataSync].[SyncTest_dss_selectrow]
	@P_1 UniqueIdentifier,
	@sync_scope_local_id Int,
	@sync_scope_restore_count Int
AS
BEGIN
SELECT [side].[Uid], [base].[BitTest], [base].[NvarcharTest], [base].[NullableIntTest]
, [base].[NewColumn] -- Add NewColumn
, [side].[sync_row_is_tombstone], [side].[local_update_peer_timestamp] as sync_row_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then [side].[local_update_peer_timestamp] else [side].[scope_update_peer_timestamp] end as sync_update_peer_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_update_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_update_peer_key] end else [side].[scope_update_peer_key] end as sync_update_peer_key, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then [side].[local_create_peer_timestamp] else [side].[scope_create_peer_timestamp] end as sync_create_peer_timestamp, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_create_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_create_peer_key] end else [side].[scope_create_peer_key] end as sync_create_peer_key from [dbo].[SyncTest] [base] right join [DataSync].[SyncTest_dss_tracking] [side] on [base].[Uid] = [side].[Uid] WHERE [side].[Uid] = @P_1
END

GO

Sample DataSync.<Table>_dss_insert change:

CREATE PROCEDURE [DataSync].[SyncTest_dss_insert]
	@P_1 UniqueIdentifier,
	@P_2 Bit,
	@P_3 NVarChar(15),
	@P_4 Int,
	@P_5 VarChar(20), -- Add Param Matching Config
	@sync_row_count Int OUTPUT
AS
BEGIN
SET @sync_row_count = 0; IF NOT EXISTS (SELECT * FROM [DataSync].[SyncTest_dss_tracking] WHERE [Uid] = @P_1) BEGIN INSERT INTO [dbo].[SyncTest]([Uid], [BitTest], [NvarcharTest], [NullableIntTest]
, [NewColumn] --Add New Column
) VALUES (@P_1, @P_2, @P_3, @P_4
, @P_5 -- Add Param Matching Signature
);  SET @sync_row_count = @@rowcount;  END 
END

No changes are necessary to the triggers, delete or metadata stored procedures.

If all goes well, SQL Azure Data Sync will now insert/update the new values.  You can use the following statement to see if all sync knowledge is being purged or becoming fragmented with the change:

Select len(scope_sync_knowledge)from DataSync.scope_info_dss

If this value goes over 10megabytes datasync will begin to fail.

This is by no means foolproof but I hope this helps,

Dusten Salinas

Answer 1

Thank you for your interest and suggestion.

--DSSMonitor