SQL Server – Did Instance Ever Participate in Replication?

Replication should be considered during the design phase of the application. If you have a geographically distributed workforce/userbase, having regional databases and central databases may make sense. Disconnected databases on laptops can "synchronize" when they finally reconnect with the network (think sales staff on the road).

As for reporting purposes, replication is NOT the answer. Most of the performance problems in a reporting environment stem from the fact that the reports are being written against a system that is configured for online transaction processing (OLTP).

Replication for reporting purposes is simply moving your OLTP data to another server, but keeping the same report-unfriendly structure. In essence, you are throwing more hardware at the problem and increasing your maintenance costs, but for marginal gains.

What you should be looking at is how to get the specific data that your reports need, and transform them into a more useful format. Create a feed that grabs new transactions from your production database and stores it in a reporting database, preferably on a separate server. Every time the feed runs, it should grab all the data that is newer than the last time it ran, transform that data, and store it. The reports that you currently are running will give you a good idea of the types of transformations required.

By following this approach, you are going to reap huge performance benefits.

SQL Server Replication is a notorious error hider.

Usually if replication just stops, a problem occurred and there is on place where you can see what that problem is. All other places you might look at show no sign of trouble.

Places to include when hunting for errors:

1. The replication monitor
2. The job history of each job involved (Look at the history. Even the SQL Agent Monitor hides a replication problem every once in a while.)
3. The SQL Server error log
4. The windows event log

3 and 4 are rarely needed. 2 is usually all you need to look at to find the problem if 1 did not help.

There is a lot more information about potential errors and how to identify them in my replication stairway articles. Particularly in Level 10.