How to Manage SQL Server Replication04 May
SQL Server allows you to implement replication of data among several systems within the company. There are quite a few benefits to SQL Server Replication you should be aware of. By resorting to SQL Server Replication, you can achieve Load balancing: you will be able to distribute the data and the query load between several available servers.
If you have a system which is not always connected to the network and you want to manipulate data from your database on that machine, you can do this by using Server Replication. Server Replication also offers you redundancy. You will be able to have a fail-over database server which is always ready to take the load at any given moment.
A replication scenario has two main components: the Publishers and the Subscribers. The Publishers are the servers which offer the data found on them to other machines. There may be one or more publishers in a replication scheme. The Subscribers are the database servers from the network which want to receive data when the Publisher server updates its data. A server machine can function as a Publisher and as a Subscriber in the same time.
There are three types of database replication supported by Microsoft SQL Server: Snapshot replication, Merge replication and Transactional replication.
Snapshot Replication
Snapshot replication works as its name implies: The Publisher takes a snapshot of the database which required replication and then it shares this snapshot among the subscribers. Because this replication method is time and resource intensive, most database administrators do not use it if the data contained in the database is frequently updated. Snapshot replication is mostly used when the database is rarely changed. Another scenario in which the snapshot method is used is when a database administrator needs to set a baseline to establish replication between systems while updates are spread using the other replication types.
Merge Replication
When utilizing the Merge replication method, both the publisher and the subscriber are allowed to make changes to the database, independently from one another. Both the publisher and the subscriber can work even if there is no network connection between them. When the connection is reestablished, the merge replication agent looks to see if there are changes on both sets of data and modifies the other database accordingly. This type of replication is used by laptop owners when they cannot be constantly connected to the publisher.
Transactional Replication
When using the Transactional replication, a snapshot of the publication database objects and data is taken. After the snapshot has been taken, subsequent data changes and schema modifications made on the Publisher will be propagated to the Subscriber in near real time. The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher so that transactional consistency is guaranteed.
Each of the mentioned replication methods is appropriate to different particular scenarios.
Remember, if you are planning to use SQL Server 2005, you have to chose the version according to your data replication needs: while the Express and the Workgroup Edition have few replication facilities, the Enterprise Edition has numerous tools for database replication.
Popularity: 18% [?]
