SQL replication overview

About SQL transactional replication

SQL Server Transactional Replication is a method used to copy data and database objects from one database (referred to as the publisher) to another (referred to as the subscriber). A copy/snapshot of the publisher database is created and is then used to initialize the synchronization between the two databases. After this initial synchronization, any changes made to the publisher database’s data are quickly sent to the subscriber database to keep it up to date/synchronized. This method is ideal when you need fast, frequent updates and minimal delay between the two databases.

Once implemented, any transactions done in the FactoryLogix system will—almost instantaneously—be copied over to the new replicated database. Due to the minimal delay in the replication, the reporting database (subscriber) is effectively a mirror image of the production database (publisher)—this is what allows the reporting database to be utilized effectively and efficiently in all aspects of report generation in FactoryLogix.

SQL replication recommendations

Taking full advantage of offloading the reporting capabilities of FactoryLogix from the production database lies heavily in the infrastructure and implementation of SQL replication. The following are recommendations and best practices relating to the implementation of SQL replication and the use of a reporting database with FactoryLogix.

• The production database and reporting database are hosted on separate servers—each server has a separate allocation of computing resources.

  • The two servers require the ability to communicate with each other over the network.

• The reporting database server infrastructure requirements are the same as the FactoryLogix database server (see FactoryLogix Infrastructure Requirements for details).