Database mirroring and log shipping

Today, we are going to discover or learn more about how a SQL database can be safely backed up. What is database mirroring? What is Log shipping? Are they different?

Let’s image you have an e-commerce website that is selling a lot of stuff, for every client that buys something this action is registered on the SQL database and on that database you check their address and the item that the person has bought. One day you wake up and see that the database you had connected to your website is now hacked, what are you going to do with all the clients that bought something, and you didn’t send anything yet? That’s why you should have a security method for SQL failovers.

What is database mirroring

When you have a PDF that is very important, and you don’t want to risk loosing it or being corrupted, what do you make? A copy!

That’s exactly what database mirroring is, a safe copy that has all the information of the primary database, in case this primary database is hacked or something goes wrong this copy takes over the primary database place. This process is configured to be automatic and there is no data loss, making your server/database highly available, with close to perfect redundancy and more protected.

So if you want to have a secure and almost perfect redundant data with less configuration and low cost? That is what you are looking for.

What is log shipping

It is a strategy to make one or more backups for your SQL primary server, it involves a primary server with at least one secondary server and an optional monitoring server. The log shipping process is basically creating a backup database based on the transaction logs of the primary server, with this backup database it is possible to restore data and make it as a failover database backup (this configuration to turn the log shipping into a primary server is usually manual).

This monitoring server has detailed information of the transactions between the primary server and the secondary server, with a configured system to detect failures on this process and alert if it happens.

Log shipping can have different ways of being structured or designed with optional server such as the monitoring server, and more than 1 secondary server, making it very flexible and versatile.

Want consistent logs about transactions, data redundancy, a monitor server to make sure every data is being processed in the right way and having availability to restore data? Log shipping is the right way to go.

Disaster recovery with database mirroring and log shipping

These concepts can be used in all SQL databases

It is essential for an important system to have meticulous plans for a possible recovery in case of disasters, when talking about not losing data in SQL server it is very necessary to have at least one of this configuration set on the server. Both of this methods complement each other, as the log shipping has a monitor server that can help to recover occasional lost data on a database that is mirrored, and the fact that the log shipping secondary server has to be manually configured to change places with the primary server makes a SQL mirrored database better for automatic replacement and highly available servers.

Of course, you have to think about what it suits you better, if your system is very simple, one mirrored database is enough, but if you want a strong security using both of them with an encrypted system would be the perfect choice.

Using both concepts on a real application

Think of a big social media that has a lot of user traffic, for its functionality and consistent availability it's very important to have these 2 concepts running on the database architecture. Using these methods the server won't have a failover and in case there is a need for analyzing data, the log shipping database full of logs can be used, and the primary server will not have a performance impact.

Comparing database mirroring, log shipping and replication

With this article at OpenGenus, you must have the complete idea of Database mirroring and log shipping.