Protecting SQL Server Using Cohesity | Cohesity

Protecting SQL Server Using Cohesity

By Brien Posey • August 23, 2016

SQL Server is one of the most popular database engines in use today, and commonly hosts mission critical workloads. As such, SQL Server administrators work hard to ensure that their SQL Server deployments are protected against data loss. Even so, data loss can occur, even when a backup exists.

As is the case for most other Microsoft server products, the primary mechanism used for backing up SQL Server is the Volume Shadow Copy Services (VSS). The backup application, whatever it may be, acts as a VSS requestor. The VSS requestor communicates with the SQL Server VSS writer in an effort to coordinate the database backup process.

When the backup application informs the SQL Server VSS writer that it needs to create a backup, the VSS writer initiates the VSS snapshot process. This results in the database being brought into an application consistent state, and being briefly frozen while a snapshot is created. The freezing process prevents database I/O while the snapshot is created. As soon as the snapshot is complete (which usually only takes a few seconds), the database is thawed and normal database I/O resumes. The backup application can then back up the snapshot.

The previous paragraph contains a simplified explanation of the process that almost every backup application uses to back up SQL Server. VSS is a Microsoft technology, and is Microsoft’s preferred mechanism for protecting SQL Server. Even so, VSS can’t completely protect SQL Server by itself.

Unless a SQL Server database is configured to use simple recovery, SQL Server writes each transaction to a set of transaction logs. To ensure granular, transaction-level recoveries, these SQLtransaction logs must be backed up, these transaction logs should be backed up alongside the database. However, not every backup product is designed to back up SQL Server transaction logs.

Cohesity is a hyper converged backup solution that has been purpose built for protecting SQL Server. It not only protects SQL Server databases and their corresponding log files, but also allows for granular restoration. Cohesity also supports advanced features such as instant recovery for SQL servers, and the ability to clone production SQL servers for use in dev / test environments. Even so, Cohesity has managed to keep its user interface simple and intuitive – quite an accomplishment for an enterprise backup application.

Cohesity recently commissioned a whitepaper on protecting SQL Server. This paper, written by 15-time Microsoft MVP Brien Posey, explains at a technical level the protective mechanisms that must be put into place in order to fully protect SQL Server, and how Cohesity meets these requirements.