Tests Disabled by Default for MS SQL Server Layer

Tests related to database replication are disabled by default for the Microsoft SQL server.

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Replication is typically performed to improve scalability and high availability of the database and for data warehousing and reporting purposes.

Transactional replication is the mechanism that Microsoft SQL Server provides to publish incremental data and schema changes to subscribers. The changes are published (the replication stream) in the order in which they occur, and typically there is low latency between the time the change is made on the Publisher and the time the change takes effect on the Subscriber. This enables a number of scenarios, such as scaling out a query workload or propagating data from a central office to remote offices and vice-versa. This form of replication always uses a hierarchical hub and spoke topology.

The following illustration is an overview of the components involved in transactional replication.

ReplicationAndDBMWhitepaper01.GIF

Figure 1 : Architecture of Transactional replication

As is evident from Figure 1, a minimum of three server roles are required for transactional replication:

  • Publisher: A database server that makes data available for replication (source server) is referred to as the publisher; a collection of one or more database objects that are enabled for replication is called a publication.
  • Distributor: Replication is managed by the system database, which by default is called distribution. A distribution database - which can reside on the publisher, subscriber, or on a separate server - is created when you configure replication. The server that hosts the distribution database is referred to as the distribution server or distributor.
  • Subscriber: One or more servers that get data and/or transactions from the publisher are called subscribers.

Depending on the complexity of the replication topology, there may be multiple Subscriber servers. Furthermore, the roles of the various replication servers can be played by one server or by individual servers (the more common case), and it is possible for a server to play any combination of roles. Regardless, the various servers and databases must be protected to ensure that the replication stream is highly available.

Transactional replication relies on various agents to perform the tasks associated with tracking changes and distributing data. Soon after the Snapshot agent records information about the synchronization in the Distribution database, the Log Reader agent moves transactions marked for replication from the Publisher to the Distributor. These transactions are then moved to the Subscriber by the Distribution agent. If any changes are made on the Subscriber, then the Queue Reader agent moves these changes back to the Publisher.

Since replication saves the day by simplifying data recovery in the event of a database failure, care should be taken to ensure that the data on the Publisher and the Subscriber are always in sync. If one/more of the agents involved experience delays while discharging their duties, then the source and destination databases may remain out-of-sync for prolonged periods. At this juncture, if the source database becomes unavailable for any reason, the destination database cannot be used owing to the data non-sync, thereby beating the core purpose of replication! The eG Enterprise system introduces administrators to such slowdowns, much before users start complaining. Using the replication tests provided by the eG SQL Monitor, administrators can closely observe the operations of every type of agent discussed above, and can proactively capture potential latencies in their operations.

As stated earlier, these replication tests are disabled by default. To enable them, go to the enable / disable tests page using the menu sequence : Agents -> Tests -> Enable/Disable, pick Microsoft SQL as the Component type, Performance as the Test type, choose a ‘replication test’ from the disabled tests list, and click on the << button to move the test to the ENABLED TESTS list. Finally, click the Update button.

The following topics discuss these tests in detail;