Last week I wrote about how easy it is to protect a SQL environment with Cohesity. This week we continue the conversation.
Organizations that use Microsoft SQL Server sometimes struggle with how best to protect their SQL databases against accidental data loss. As is the case for other database platforms, there are a wide variety of tools and techniques available for protecting SQL Server, ranging from native built-in tools to third party commercial backup applications.
In addition to there being a diverse collection of tools available for SQL Server backups, there are a variety of backup techniques that can be applied. Two of the more common techniques for example, are copy and dump.
Dump can be thought of as either a tool or a technique. As a technique, dump refers to dumping SQL Server data to backup. As a tool, Dump refers to a command that is supported natively by Microsoft SQL Server.
Although the Dump command still exists in SQL Server today, it is actually a legacy tool that exists solely for backward compatibility purposes. Originally, the Dump statement provided a way for administrators to backup SQL Server databases and transaction logs. An administrator could back up a database by using the DUMP DATABASE command. Similarly, transaction logs could be backed up by using the DUMP TRANSACTION command.
The BACKUP command has largely replaced the DUMP command. SQL Server databases and log files can be backed up by using the BACKUP DATABASE and BACKUP LOG commands respectively.
Whether an administrator uses the DUMP command or the BACKUP command, dump backups are very flexible. An administrator can use these commands to perform full, incremental, or differential backups. There are also a variety of management and monitoring capabilities that are supported by the command.
Copy backups, which are sometimes referred to as copy only backups, are a variation on dump backups. In fact, a copy only backup uses the BACKUP command just like a DUMP backup does, but appends the COPY_ONLY argument.
Copy only backups were first introduced in SQL Server 2005, and serve as a mechanism for creating a one-off backup that does not interfere with the normal backup sequence (from a transaction log file processing standpoint). Copy only backups are sometimes taken in an effort to preserve log files prior to attempting a restore operation, but they can be used for other purposes such as making a backup that can later be used as the basis for a test / dev environment.
As previously noted, dump and copy only backups are both based on the BACKUP command. The BACKUP command has existed in SQL Server long enough for it to be considered stable and reliable. Even so, the BACKUP command is just that – a command. It provides a way of initiating backup operations from the command line.
The main issue with using the BACKUP command is its sheer complexity. As you can see in the command’s documentation, the BACKUP command allows for a massive number of arguments and parameters. Admittedly, SQL Server administrators are used to dealing with complexity. When it comes to backup and restoration operations however, complexity is generally regarded as a bad thing. An overly complex backup solution carries with it a high potential for human error. Such errors can leave SQL Servers unprotected, while giving administrators the illusion that their backups are working properly.
Although SQL Server has a reputation for being complex, data protection for SQL Server can actually be quite simple. Cohesity has created a backup platform for SQL Server that is surprisingly easy to use.
To get started, backup administrators must register their SQL Servers, which involves little more than specifying the servers that need to be protected, and supplying administrative credentials. Once the organization’s SQL Servers have been registered, the administrator needs only to create a protection job.
Although the creation of SQL Server protection jobs is often a complex undertaking, Cohesity has pared the process down to four simple steps. Those steps consist of:
- Selecting the objects to be protected.
- Selecting a data protection policy.
- Specifying the job settings.
- Confirming the information presented on the summary screen.
Better still, Cohesity uses the same basic approach to creating a protection job, regardless of the type of resource that is being protected. As such, an administrator who knows how to use the Cohesity platform to backup resources such as VMware or Hyper-V already knows how to back up SQL Server.
In my third and final post in this three-part series, I will discuss using Cohesity for more efficient Test / Dev.
All blogs in this series:
- How Easy is it to Protect a SQL Environment with Cohesity?
- Protecting SQL Catabases: Cohesity as an Alternative to Dumps and Copies
