Database replication: How to get it right!

29 Sep 2019 0 minutes to read Contributors

 

 

Many modern businesses need to have multiple copies of their database, or database objects, in different locations. You may have offices in several countries, or perhaps you have fully remote- working teams, or you want to distribute the load by separating, for example, your report server and production server. However your company is structured, there will probably be times when you need to ensure authorized users have access to your database in different places.

Replication is a great way to create multiple copies of your databases or database objects and distribute them over multiple locations across your organization, so they can be used for purposes such as operational reporting, testing, load balancing and disaster recovery.

“By failing to prepare, you are preparing to fail.” Benjamin Franklin

It’s worth remembering that there are different types of database replication and each has its own pros and cons. Every company also has individual needs. It’s essential to consider which type of replication will best fit your current requirements and future plans.

As a starting point for your business preparations, here’s an overview of the three main, and quite distinct, SQL database replication types.

Snapshot Replication

As the name suggests, this is where you take a snapshot of your data at that specific moment which is generated and sent to the subscribers. But, this does not check for updates done to the data. This type of replication is commonly used to initialize the following steps for transactional or merge replication. Snapshot replication is generally used in the following three scenarios: there are infrequent changes to the data or database objects, real-time data is not necessary compared to the publisher or in cases of small data sets or large data sets that occur in a small time frame.

Transactional Replication

Transactional replication offers four different types of publications. With standard transactional replication any data changes or modifications done on the publisher are replicated (delivered) almost real-time to the respective subscribers. This type of replication is commonly used if you want low latency between changes done on the publisher being replicated to the subscriber(s). Or, if there is a high activity of Data Manipulation Language (DML) statements on the publisher. It is recommended to treat the subscriber in a standard transactional replication as a read-only, as making modifications to the data or schema causes data consistency errors.

Merge Replication

Like transactional replication, merge replication also starts with a snapshot of the publication database. In merge replication, changes in data or modifications can be made both on the publisher and the subscriber(s). This is commonly used in scenarios where multiple subscribers update the same data and the changes need to be sent to the publisher to obtain a uniform (identical) result over all nodes.

The type of replication you use depends very much on how and why you are using the data. Is it just a one-way flow of information or do you want users to be able to make changes? If you are creating specific reports, do you need to replicate the entire database or just certain tables or database objects? You also need to think about security, such as encrypting the connections between the publisher and subscriber using several methods or providing only the necessary permissions to the replication agents.

You can gain many benefits from replicating your SQL databases. It can help you run your business more smoothly and efficiently, and deal with increased workloads and disaster recovery challenges. However, it is essential that you plan and prepare to ensure you have the optimal type of replication in place that fits your business requirements.

If you’d like to know more, we are always happy to answer questions and discuss individual requirements. You can contact us here.

In this article