Lesson 5: Perform a schema change on the remote database

In this lesson, you modify the remote database to add the new column to the customer table and to change the script version used to synchronize. Before you do this you must ensure that there are no operations for the customer table that need to be uploaded. The best way to do this is to perform the schema change in the sp_hook_dbmlsync_schema_upgrade hook. When you use this hook, dbmlsync ensures that the schema change is performed safely by locking the synchronizing tables at the start of synchronization and holding the locks until the schema change is complete.

Caution

If you change the schema when there are operations to be uploaded, the remote is always unable to synchronize after the schema change.

To perform a schema change on the remote database

Create an sp_hook_dbmlsync_schema_upgrade hook by executing the following SQL against the remote database. The hook adds a new column to the customer table and changes the value of the ScriptVersion extended option stored with the subscription. The hook is deleted by dbmlsync after it has executed.

CREATE PROCEDURE sp_hook_dbmlsync_schema_upgrade()
BEGIN
    ALTER TABLE customer 
    ADD cell_phone varchar(12) default null;

    ALTER SYNCHRONIZATION SUBSCRIPTION my_sub
    ALTER OPTION ScriptVersion='my_ver2';

    UPDATE #hook_dict
    SET value = 'always'
        WHERE name = 'drop hook';
END

Next, synchronize to upload any operations that need to be uploaded and to perform the schema change by executing the sp_hook_dbmlsync_schema_change hook. Run the following command:
dbmlsync -v+ -ot sync2.txt -c uid=dba;pwd=sql;eng=remote -s my_sub -k

After this synchronization, it is a very good idea to look at the dbmlsync log sync2.txt to ensure that there are no errors to indicate that the schema change was not completed.

The schema change is now complete and you can continue synchronizing normally.

Discuss this page in DocCommentXchange.