Simplify SQL Server replication
You can simplify SQL Server replication management and monitoring with advice from SQL Server expert Michelle Gutzait. She shows you how to reduce the number of publications and subscribers for improved database architecture.
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.
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
By submitting your personal information, you agree to receive emails regarding relevant products and special offers from TechTarget and its partners. You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.
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 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 management 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:
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:
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.
| ABOUT THE AUTHOR: |
| Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T-SQL/packages coding and more. Copyright 2007 TechTarget |
Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning
Pro+
Features
Enjoy the benefits of Pro+ membership, learn more and join.
0 comments
Oldest Newest