Using with default_location and for proxy_update

Without the for proxy_update clause, the behavior of the with default_location clause is the same as that provided by sp_defaultloc—a default storage location is established for new and existing table creation, but proxy table definitions are not automatically imported during the processing of create database.

If for proxy_update is specified with no default_location, an error is reported.

When a proxy database is created (using the for proxy_update option), CIS is called upon to:
  • Provide an estimate of the database size required to contain all proxy tables representing the actual tables and views found in the primary server’s database. This estimate is the number of database pages needed to contain all proxy tables and indexes. The estimate is used if no size is specified, and no database devices are specified.

  • Create all proxy tables representing the actual tables and views found in the companion server’s database.

  • Grant all permissions on proxy tables to public.

  • Add the guest user to the proxy database.

  • The database status is set to indicate that this database “Is_A_Proxy”. This status is contained in master.dbo.sysdatabases.status3.