Have your replication design practices gotten out of hand? You can simplify SQL Server replication management and monitoring by reducing the number of publications and subscribers in your database architecture.
It's not so difficult to end up managing mulfiple databases and publications. Let's say you use SQL Server to develop an application for one of your database sites. It quickly evolves, and you realize that with a few minor changes you can use the same application for another site. This should be done quickly due to users' requirements, so you decide so you decide to duplicate the code and database and make changes accordingly. Then, other sites ask for the application, each having its own needs and requirements.
The fastest way to reproduce an application is to have a replica of the application and database and to apply the necessary changes to them. After a while, a new requirement in the application may cause you to choose SQL Server replication to copy data between your sites and others (maybe to portal machines). For each site, you would create different publications and subscribers.
Then your company decides to consolidate the sites and you end up with multiple databases and multiple publications on the same server (central server). The databases and publications are very similar but not identical.
Most likely you'll begin to find it annoying and complex -- managing this replication design. Too many publications, too many subscribers and possibly the same subscribers participate in more than one publication. Sound familiar?
Are you about to make the same decisions about the replication design? There are steps you can take during SQL Server replication design to simplify replication management and monitoring.
Replication design goals
Your goal for a successful replication design should be to have less manageme
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
nt efforts and fewer points of failure. You can achieve that goal by maintaining fewer publications and subscriptions. However, that may mean making changes in the databases structures, which may not be feasible.
Database design
The best thing to do is consolidate replicated tables to fewer source databases (preferably one source database). This allows you to decrease the amount of publications. When you consolidate, it's often necessary to add site or database code to the tables in order to identify the data source.
Replication design
If it's too complicated to modify applications in order to use fewer databases, a better idea might be to replicate the tables into a temporary central database with common structure. This way, subscribers can get the data from the central database using fewer publications, as described in the following figures:
Current architecture:
There are few databases with similar structure, which are modified by few applications. Each database has its own publications and is replicated to multiple subscribers:
[IMAGE]
Recommended architecture:
The databases are replicated to a central database with a common structure and the changes are replicated to the subscribers using fewer publications:
[IMAGE]
When replicating consolidated data, you can publish the slice of data pertaining to a specific site. This allows you to create one publication and add a WHERE clause according to the site or database code.
Replication wrap-up
It is a good idea to simplify the SQL Server replication model when it becomes too complex because of application growth and evolvement. Otherwise, managing the monstrous amount of publications and subscriptions is likely to turn into a headache.
[TABLE]
