Replicating Repository Databases
Microsoft® SQL Server™ 2000 repository databases can take advantage of the replication features provided by SQL Server to publish a repository to other subscriber repositories.
You can use either transactional or snapshot replication to replicate a repository database. If you implement transactional replication, you can choose whether to support synchronization. Synchronization automatically updates your subscriber databases so that they contain the same content as the publisher. For more information, see Replication Overview.
Replication Requirements for Repository Databases
For repository databases, additional steps beyond those required by SQL Server should be followed to ensure successful replication.
Publishing a Repository Database
- Install information models into a repository database. Before you begin replication, you must install information models into the repository database that you want to publish, and then allow replication to propagate the content across all subscriber databases.
Installing new or revised information models after replication is enabled can produce unexpected results. In this case, new tables that are associated with new or revised information models are not automatically enabled for replication. If you are updating an existing publisher with newer models, you must manually select the additional tables as articles so that updates to those tables will propagate to subscriber databases.
Note that you cannot publish msdb, the default repository database in SQL Server. You must create an alternate repository database to enable replication.
- Verify that all repository SQL tables and information model tables are selected as articles in the publication. Repository SQL table names have an rtbl prefix. Information model table names are typically prefixed with the name of the model (for example, UML, UMX, GEN, and so on).
You cannot publish a subset of the tables in a repository database. A repository database stores type information in multiple tables. If you omit some tables from the publication, you may not get a complete definition for each repository object.
- Verify that repository stored procedures are not included in your publication. Repository stored procedures are part of the publisher database, but cannot be part of a subscriber database. Repository stored procedure names have an r_iRtbl prefix.
Stored procedures are used by the repository engine to install and update information models in a SQL Server repository database. Replicating a stored procedure can result in an attempt to reinstall an information model that is already installed on a subscriber database.
- Avoid creating data filters or enabling autonomous subscriptions.
Defining Subscriber Databases
After you create a publication, you can create one or more push subscriptions that propagate repository data from the publisher.
Avoid updating subscriber objects from any nonpublisher source. Only the publisher should be allowed to update subscriber objects.
Repository subscriber databases must be read-only. Furthermore, each subscriber can receive content from only one publisher. Repository databases use internal identifiers to store and manipulate meta data. While internal identifiers are unique within a specific repository, they may not be unique across multiple repositories. To avoid duplicate internal identifiers, you must require that each subscriber is read-only and receives all of its updates from a single publisher. To do this, specify that a publication for the publisher database has all of the repository tables as articles, then add read-only repository databases as subscribers.