User Proxy Database Schema Synchronization

At times, it may be necessary for a DBA to force re-synchronization of the proxy tables contained within the proxy database. This can be done through the alter database command:

alter database <dbname>
		[alter database options]
		[for proxy_update]

If the for proxy_update clause is entered with no other options, the size of the database will not be extended; instead, the proxy tables, if any, are dropped from the proxy database and re-created from the metadata obtained from the pathname specified during create database ... with default_location = ’pathname’

If this command is used with other options to extend the size of the database, the proxy table synchronization is performed after the size extensions are made.

The purpose of this alter database extension is to provide a DBA with an easy-to-use, single step operation with which to obtain an accurate and up-to-date proxy representation of all tables at a single remote site.

This re-synchronization is supported for all external data sources, and not just the primary server in a HA-cluster environment. Also, a database need not have been created with the for proxy_update clause. If a default storage location has been specified, either through the create database command or using sp_defaultloc, the metadata contained within the database can be synchronized with the metadata at the remote storage location.

Certain behavior is implied by the use of create/alter database to specify a proxy database: