In a typical database system, most of the database load is due to read operations. In a multi-database system, this load can be distributed to a large number of databases. The capacity of the master database is then left for processing the transactions that have been propagated from the replica databases of the system.
Because all shared or synchronized transactions of the system are committed in the master database, it is very important that the resources of the master database are used as efficiently as possible. The following actions can help optimize the resource usage of the master database:
▪ If possible, dedicate the master database for synchronization use only. User access to this database might have unpredictable response times if heavy synchronization processes are being run simultaneously.
▪ Optimize the indexing of the database for synchronization use only. For example, if the database is not directly accessed by users, provide only those indexes that are used by the search criteria and joins of the publications, as well as those needed by the transactions.
▪ If user access to a master database is required, it is often preferable to have a full replica of the master database available for that purpose. This database can then have indexing that is optimized for the online usage.
▪ Keep the publications simple. Complex publications with lots of joins between tables mean more complex queries, which require more server resources.
Since incremental refreshes usually use fewer resources than full refreshes, you can enable incremental refreshes by setting the SYNCHISTORY property for the tables of the publication. This allows the master server to send only the master data that has changed in the publication, rather than a full publication. For more information, see ALTER TABLE ... SET SYNCHISTORY.
▪ Do not synchronize your databases more frequently than necessary.
▪ Use off-peak hours in the synchronization processes. Synchronize the less-urgent data when the online usage of the system is at its minimum.