Tip

When to use data replication

As it relates to SQL Server, replication is a way of keeping data synchronized in multiple databases. Implementing and maintaining replication might not be a simple proposition. If you have numerous database servers that need to be involved in various types of replication, a simple task can quickly become complex. Implementing replication can also be complicated by the application architecture. But there are numerous scenarios in which replication can be utilized. Before you make the decision to replicate your database, take a look at this tip from

    Requires Free Membership to View

InformIT which examines some of the reasons to use replication.

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

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.

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.

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.

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.


Read more about replication at InformIT.


This was first published in March 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.