SQL Replication Crash Course
A major change in System Center 2012 ConfigMgr is the use of SQL Server replication for intersite communications. SQL Server replication largely replaces the inbox structure and file transfer methods of data exchange used in ConfigMgr 2007 and SMS. ConfigMgr sites are now able to process data and replicate it to other sites rather than requiring multiple sites to process the same data files. When you add a site to an existing hierarchy, ConfigMgr automatically configures SQL replication during site installation.
ConfigMgr uses two types of database replication:
- Snapshot replication is used for initial replication when a new site is created in a hierarchy.
- The ConfigMgr Database Replication Service uses the SQL Server Service Broker for ongoing data replication.
SQL Server also supports other types of replication that are not used by ConfigMgr and are not discussed in this chapter.
When you add a new site to the hierarchy, the initial snapshot replication uses the SQL Server bulk copy program (BCP) to export site data to a file. ConfigMgr then uses file-based replication to replicate the database extract to the parent site and loads it into the database through the BCP process.
The SQL Server Service Broker provides messaging services for SQL Server applications. Some advantages of the Service Broker include
- Asynchronous messaging: When an application submits a message to a Service Broker queue, the application can continue to process other work and leave the message delivery details to the Service Broker.
- Transactional processing: Applications can send a set of related messages as a transaction. The transaction will not be committed until all messages are successfully processed, and can be rolled back if one of the messages fails.
- Message sequencing: The Service Broker handles the details of providing messages to the receiver in the correct order.
- Database engine integration: The Service Broker is part of the database engine, which improved performance and leverage the existing connection and security context.
Here are some of the key objects that Service Broker uses for message delivery:
- Messages: These are units of data. Each message has a specific message type. For example one of the message types defined by ConfigMgr is a notification that and Alert variable has changed.
- Queues: Queues receive messages and hold them for delivery.
- Conversations: These are asynchronous, reliable, long-running exchanges of messages. Each conversation has a priority so that messages in higher priority conversations will be processed before lower priority conversations.
- Services: Services are the endpoints for conversations. A service implements the set of tasks required to produce or consume messages.
ConfigMgr uses SQL Server change tracking to detect changes to the database tables that are in scope for replication. SQL Server change tracking is a new feature introduced with SQL Server 2008. Applications can enable database tables for change tracking. After a table is enabled for change tracking, the database engine maintains information about changes to the table. Applications can access the information to determine what rows in the table have changed and can then query the table to retrieve the modified data. Executing the following query against the ConfigMgr database displays a list of tables that are enabled for change tracking:
select name from sys.tables where object_id in (select object_id from sys.change_tracking_tables) order by name
These tables contain data that will be replicated to other sites if changes occur. The list will generally contain several hundred tables and will vary depending on the whether the site’s role in the hierarchy and the number of locally updated objects. Some ConfigMgr data is local to the site and not replicated. Tables containing local data are not enabled for change tracking. Chapter 5 discusses ConfigMgr replication scopes and planning considerations related to replication.