Full vs Differential vs Transaction Log Backup

SQL Server provides three backup options - Full backup, Differential backup and Transaction Logs backup. Today, we will review the three backup strategies and key differences between them.

Full Backup
Full backup is just that, a full backup of your database at a point in time. You can restore the full backup on the same or a different SQL Server.

Differential Backup
Differential backup backs only the changes since the last full backup. The benefit of differential backup is that it is very fast and takes less space since you are only backing up the changes.

Differential backup has a backup chain which starts from the last full backup. All differential backups are from the previous full backup. It is possible to take another full backup without breaking the differential backup chain, i.e. to have it continue from the previous full backup. If you take a full backup with COPY_ONLY option, you will not break the differential backup chain, but without the COPY_ONLY option, the previous differential backup chain will be broken and a new chain will start from the most recent backup.

You can perform a restore at point in time by restoring a full backup and then applying the most recent differential backup.

Transaction Logs
Transaction Logs are the changes since the last transaction log backup. I have seen some confusion about whether transaction log backups are from the last full backup or from the last transaction log backup. If you are taking full database backup for the very first time, you transaction log back up chain will start after the full backup. Any subsequent full or differential backups will not break the log chain and the next transaction log backup will be from the last transaction log backup and not the last full backup.

The transaction log backup only works in Full and Bulk Logged recovery model and the only way to break the log chain is by either switching the recovery model to Simple or if you choose to override existing backup set when creating a full backup media set.

If your database is set to full or bulk logged recovery model, you must take frequent log backups otherwise your log file won't truncate, filling up your hard drive.

It is good to use all three backup schemes in your database environment to ensure you keep the restore media set or files as current as possible, so you can restore to a point in time and minimize data loss.

Thank you.

Database Replication

In previous posts we discussed transaction log shipping and database mirroring and the key differences between the two. Continuing the series, today we will discuss database replication.

There are several uses for replication. For Example, disaster recovery or for reporting (analysis).

When used for Disaster Recovery, replication is generally setup between geographically dispersed data-centers. For example, you are hosting your servers with a hosting company that has multiple data centers located around the country. You can setup replication service which will allow your database to be replicated in one more data centers. If the database in your primary data center is inaccessible, traffic could be re-routed to the secondary data center.

Apart from the disaster recovery, another usage I have seen is that the data is periodically replicated from the primary server to the secondary server for running reports or heavy analysis. This way, you can run your reports, setup data cubes etc. on a secondary server while the primary server is mainly for online transaction processing.

Key difference between Transaction Log shipping, Mirroring and Replication is that the secondary databases in both log shipping and mirroring are in standby mode and are mostly unusable but in replication both primary and secondary databases are active.

Replication can be configured in several ways such as...

• Snapshot - Data is simply copied from the main server to the one or more secondary servers, sometime even to a secondary database on the same server.
• Merging Replication - Data from two or more databases is merged into a single database
• Transactional Replication - Full initial copies of the databases are configured on one or more secondary servers and then periodic updates are applied.

Distributed database management system ensures that any inserts/updates/deletes that are performed on the data in one location are automatically synched with other locations.

There are two types of replication - Synchronous and Asynchronous. In Synchronous mode, the data is replicated on both primary and secondary server(s) at the same time, while in asynchronous replication, there is a delay before data is written to the secondary server. While asynchronous is a better option for performance reasons, a data loss could occur in case of a disaster.

Replication generally occurs either in the storage array (array of disks where the data is stored), at the server or in the network. Most SAN vendors provide a replication software that you can use to setup replication at the storage array level. There are also software solutions to setup replication at the server level, when you don't have a storage array. This is usually a cheaper solution but could impact server performance. Network replication usually is hardware based using an appliance.

Below are some of the Key differences between Log Shipping / Replication

• Latency - A minute or more in Log Shipping, just few seconds in Replication
• Schema altered at the primary server (also known as publisher)?
• Log Shipping - No
• Replication
• Snapshot - No
• Transactional - No
• Merge / Updates- Yes
• A GUID column is added if one doesn't exist.
• Schema altered at the secondary server (also known as subscriber)?
• Log Shipping - No
• Replication - Depends - see below
• Snapshot and Transactional Replication may make schema changes. Standard Snapshot and Transactional replication will not transfer identity attributes. Instead they become normal numeric columns on the secondary server.
• Are there any requirements at the Database Schema level?
• Log Shipping - No
• Replication - Primary Keys are required on the tables involved in replication.
• Can you Select individual Articles?
• Log Shipping - No
• Replication - Yes
• Is the Secondary server usable 
• Log Shipping - Mostly No - Requires exclusive lock to apply the transaction log.
• Replication - Yes

Hopefully this article will provide you enough insight into replication and how to get started if you are thinking about setting one.

Thank you and as always, your comments are welcome.

Database Mirroring

In one of my previous posts I talked about Transaction Log Shipping and how it can be used to create a copy of the database on a secondary server and periodically apply the transaction logs. Mirroring is a similar process except a few differences.

Whereas in Log Shipping, transaction logs are periodically backed up and physically copied to the secondary server and applied to the database in recovery mode. In mirroring, SQL Server directly reads the data from the transaction logs and copies them from the principal server to the mirrored server.

 Mirroring can be configured to operate synchronously or asynchronously. In synchronous configuration, primary or principal server sends the data to the mirrored server and waits until the mirrored server saves the data on the hard-drive before committing the transaction. In this scenario, basically buffered data is sent to the mirrored server and principal waits for a signal from the mirrored instance. Once the signal is received from the mirror, only then the transaction is committed on the principal. As you can imagine the performance of your database depends on several factors such as network bandwidth, disk write speed etc.

 In asynchronous mode, the principal server doesn't wait for the mirrored server before committing the transaction. It sends the buffered data to the mirrored instance, while simultaneously committing the transaction.

Mirroring supports only one mirrored instance. This is yet another difference between Mirroring and Transaction Log Shipping, which supports multiple instances of the secondary server.

Mirroring supports automatic failover. In case of a failure on primary server, SQL Server automatically brings the mirrored instance online, usually within a few seconds.

While Log Shipping has been available since SQL 2000, mirroring was introduced only in SQL Server 2005.

Another key advantage of mirroring is built-in support in .NET Framework which doesn't require special routing or switching code to handle the fail-over switch from one server to another. It requires ADO.NET 2.0 and higher.

SQL Server 2008 introduced another key feature in mirroring, i.e. ability to automatically repair corrupt pages. For example, if a page is corrupted on the principal server, SQL Server will replace it with a page from the mirror and vice versa.

We can go in more technical details in further posts if needed, but in a nutshell this is what mirroring is all about.

As always, your comments are welcome!