SQL Replication

What is SQL Replication?

SQL replication is a technology designed to allow storing identical data in multiple locations. First lets examine why replication may be useful and how it solves common data distribution problems. There are several classical examples in which SQL replication solves business problems. One of the most popular ones is the case when a business has mobile employees, who need to access data from their portable computers while they are away from they office. Another example is when the workforce of a business is distributed around the world and all employees need to access one and the same set of data, but network connectivity has poor quality. In both the above examples using SQL replication is the right thing to do. Replication is used in many other scenarios as well for example as a backup solution, and for offloading database intensive processing like reporting and data mining from main live databases.

Types of SQL Replication

MS SQL Server supports 3 main types of SQL replication - Snapshot replication, Transactional replication and Merge replication. The Snapshot replication uses a fresh database copy every time you run it, as it title suggests. This replication type is relatively simple, however it doesn't have many applications in real live and is mainly used to create an initial copy of a database, which will be used in more complex replication types. The second SQL replication type is the Transactional replication, which sits somewhere in between Snapshot and merge replications as far as complexity is concerned. The Transactional replication uses Snapshot replication to get a starting copy of the data that needs to be replicated, but from this point on it updates this copy only the data that has been updated since its latest update. The Merge replication is used in situations, which require updates to be made to any of the replication copies. When we have a scenario like that, the data from the different replication locations needs to be ultimately merged to a central location.