No matter which DBA you ask, there are really only four ways to protect your SQL Server data. Each method has its own strengths and weaknesses, but in the end picking the right protection technique depends entirely on what’s important to you. The three most common techniques are replication, log shipping, and database backups. There is a fourth method mentioned in Books Online called “Data Masking”, but it falls more into non-production data types of work rather than true production data protection.
Replication was added in SQL 7.0 and provides an efficient way to keep multiple databases in sync over long distances or otherwise separate servers. Replication can happen either within a single server using MSDB or across servers using third party software like DataSynapse. The biggest drawback to replication is that data changes are written directly to the subscriber, which can create a load on busy servers. Replication does not protect against hardware failure because the publisher holds all of its transaction logs and incoming snapshots in full text, but it does allow for easy failover in case of an issue by simply promoting one subscriber as the new publisher. An additional feature in SQL 2012 is Merge Replication, which provides automatic conflict resolution when multiple publishers are replicating data amongst each other.
MS SQL Server replication is an innovation for duplicating and synchronizing information between data sets persistently or consistently at planned spans. Concerning the replication bearing, MS SQL Server replication can be: one-way, one-to-many, bidirectional and many-to-one. There are four MS SQL Server replication types: depiction replication, value-based replication, shared replication and consolidation replication.
MS SQL Server replication is a valuable component that permits you to make a duplicate of your data set that can be utilized for recuperation after catastrophe, building appropriated information bases and synchronizing information between data set servers that can rarely associate with one another. There are four kinds of MS SQL Server replication – preview, value-based, shared and consolidate replication.
Log shipping protects against server failure or catastrophic data loss by keeping transaction logs intact on a primary server and rolling them forward on a secondary server after testing has been performed. Typically you’ll want to set up your logshipper to match your replication setup with the same subscriber and publisher names. This way, if your primary crashes you can promote the secondary to take over as publisher without any hiccups. Log shipping provides no protection against user error or hardware failure because it writes directly to the subscriber, but it does provide simple failover in case of an issue.
SQL Server Log delivering gives a DR (catastrophe recuperation) answer for the SQL Server information bases and it very well may be arranged at the data set level. In a particular delay, SQL Server Transaction Log reinforcement will be taken and replicated to the objective site and will be reestablished. This total action or arrangement will be finished by SQL Server work and each progression is designed by the client. The student might defy the hardships in several means or while investigating however for the accomplished client, it is too simple to even consider setting up and deal with the SQL Server log delivering set up mistakes.
Database backups are probably the most common method of protecting your data, although they require some additional effort on what is usually a busy production server. A backup taken from a full database will protect against nearly all disaster types (excluding total server loss) with very little performance impact on live users; however, this also means performing these regular backups takes more resources than log shipping or replication. The biggest advantage to using SQL Server’s built-in backup tools is that you can restore your entire database, individual files or even a single table, without the need for any third party software.
Data Masking is a new feature in SQL Server 2012 which provides random data for sensitive fields while leaving non-sensitive data intact. If you have a customer list stored in a table and don’t want to reveal their postal code information until they agree to share it, then Data Masking might be just what you’re looking for. The drawback here is that it doesn’t offer replication, log shipping or traditional backups so if your server goes down it will take all of them with it.
In this article I’ll compare and contrast each one of these methods from both a production and development perspective. In part two of this series I’ll discuss the pros and cons of each method in detail.
- Introducing SQL Data Protector (SQL DP) (formerly known as SQL Backup Pro) is a data protection solution for Microsoft SQL Server that offers low RTOs, transparent recovery, flexible reporting & auditing, granular control over restore operations and more. The product has been available since 2005 but was recently acquired by Quest Software.
- Quest Software officials said they plan to continue expanding on the original vision for SQL Backup Pro/Data Protector…One way it will do so is through better integration with virtualization technologies like VMware vSphere or Microsoft Hyper-V hypervisors, according to Jason Buffington, director of product management.
- “There is a lot of interest from customers to have data protection work in virtualized environments,” Buffington said…SQL Data Protector will get tighter integration with Quest’s InMage acquisition, which allows for seamless recovery of individual databases within virtual machines.
- Data Protection Tools Get A Little Backup Help, Virtualization Review
So there you have it – six ways to protect your data. The good news is they each offer their own benefits and are relatively inexpensive, but naturally each one has drawbacks as well. If I had to pick one method over the others it would be SQL Data Protector because of its high level support for all major virtualization platforms, ability to quickly recover only specific file types out of a full VM image, and granular control over what can be restored. In part two I’ll get into the specifics of each solution in greater detail so stay tuned!