Software Architecture – Case Study – Why and Why not SQL Server Replication

 Cases Study Related To Replication

                                                             By: Shahzad Sarwar

                                                            To: Dubai Office

                                                            Dated: 7th June 2010

Problem Definition:

Ships of shipping companies move around in sea, which have a system where check list (as Questions/answer) are supposed to be saved. These check list identify the health, safety and operational status of ship and its different components. On regular intervals, these checklists are filled in ships.

There will be one centralized server, where data of these check lists from different ships will be pushed. So central server will have consolidated view of data from all the ships moving in the Sea.


Lets first analyze what are the objective of replication, Why to use and Why to not use replication in eyes of MSDN-the most valid source of information.

1         Objectives of Replication:

Replication can be used effectively for many different purposes, as discussed in the following sections.

1.1      Separating Data Entry and Reporting

If you have worked in an environment in which the same database is used for data entry and reporting, you probably know that things aren’t always rosy. Constantly reading and modifying data in the same set of tables just doesn’t work very well if you care about data integrity. Transactions that run against a set of tables prevent reading the locked data rows and pages, or perhaps prevent even an entire table from being read by a report. In such an environment, you are bound to see blocking locks. Although there are ways to avoid blocking (please see my earlier article about transactions and locking), it is best to separate data entry and reporting databases. Transactional replication works well by delivering data changes from the data entry server to the reporting server.

1.2      Distributing Load Across Servers

As your organization grows, you might find yourself in a situation in which a single database server is utilized by too many users. If CPU utilization on your database servers is constantly over 80 percent and you have tuned database design and queries appropriately, chances are you could benefit by spreading the user base over multiple servers. For instance, a server named South could serve all employees working in the southern United States, and a server called North could serve all Northerners. If you need to combine all data for reporting, you could use replication to move transactions from North and South to a server named Central_Reporting.

1.3      Providing High Availability

Occasionally, you might consider using replication for high availability; that is, to replicate transactions from the main server to a standby server. If the main server fails, you can then point your data sources to the standby server. Be aware that using replication for high availability takes careful planning and testing. Replication does not provide any sort of automatic fail-over. SQL Server supports other methods of providing high availability, such as clustering and log-shipping, which might be more appropriate for your environment.

1.4      Transporting Data

Another common use for replication is to simply move data changes from publishers to subscribers. This method is particularly useful for moving transactional data to a data warehousing server, in which it is transformed and aggregated for OLAP reporting. SQL Server provides other ways of transporting data: DTS, BCP, BULK INSERT statements, and others. Be sure to carefully consider the alternatives before implementing replication because other solutions might be cheaper or even faster than replication.

2         Why and Why not Replication

2.1      Why Use Database Replication?

Imagine that a client has asked you to develop a contact-management solution that the company’s field sales staff can use to monitor sales and orders. Each sales representative has a laptop computer that can be connected to the company’s network.

A traditional approach to building this solution is to separate the tables from the other objects in the database so that the data can reside in a back-end database on a network server, or on the Internet or an intranet, while the queries, forms, reports, macros, and modules reside in a separate front-end database on the user’s computer. The objects in the front-end database are based on tables that are linked to the back-end database. When sales representatives want to retrieve or update information in the database, they use the front-end database.

Database replication enables you to take a new approach to building this solution by creating a single database that contains both the data and objects, and then making replicas of the database for each sales representative. You can make replicas for each user and synchronize each replica with the Design Master on a network server. Sales representatives update the replicas on their computers during the course of a work session, and users synchronize their replicas with the Design Master on the server as needed.

In addition, you can choose to replicate only a portion of the data in the Design Master, and you can replicate different portions for different users by creating partial replicas. In the scenario involving sales representatives who use replica databases, each individual salesperson typically needs only the sales data related to his or her own territory. Replicating all sales data for all sales representatives would involve unnecessary processing and duplication of data. By using partial replicas, you can duplicate only the data that each salesperson actually needs. A complete set of data is still contained in the Design Master, but each replica handles only a subset of that data.

2.2      When Database Replication Should Not Be Used?

Although database replication can solve many of the problems inherent in distributed-database processing, it is important to recognize that there are situations in which replication is less than ideal. You may not want to use replication if:

2.2.1      There are large numbers of record updates at multiple replicas. 

Solutions that require frequent updates of existing records in different replicas are likely to have more record conflicts than solutions that simply insert new records in a database. Record conflicts occur when any changes are made to the same record by users at different locations at the same time. Solutions with many record conflicts require more administrative time because the conflicts must be resolved manually. This is true even if different fields are updated within the same record.

2.2.2      Data consistency is critical at all times. 

Solutions that rely on information being correct at all times, such as funds transfers, airline reservations, and the tracking of package shipments, usually use a transaction method. Although transactions can be processed within a replica, there is no support for processing transactions across replicas. The information exchanged between replicas during synchronization is the result of the transaction, not the transaction itself.

3         Solution points:


  • As consolidation of all ships data to central database is primary objective, so general case supports implementation as replication.


  • With reference to point 2.2.1, as all ships have separate records of data of checklist, so we don’t have multiple updates of same record by different replicas.  So we are on safe side for replication.


  • With reference to point 2.2.2, as we don’t have data consistency issues with data, as logically data from different ships don’t have relationship between them.


  • Point under query is, “Is data really worth transferring via replication?” or simple Import or export via XML file and transfer by ftp will serve the purpose. Volume of data and frequency of data transfer will be a deciding factor.



                 Microsoft MSDN



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: