Protect your SQL Server Data in a Virtualized Environment

SQL Server has specific requirements to ensure you get a good backup and more importantly are able to restore from backup. Although this Blog post is targeted to backup SQL Server in a virtualized environment, most of the concepts can be applied to all SQL Servers – physical or virtual. Many of the SQL Server backup issues center around the Authentication Mode (Windows or Mixed) and Recovery Model (Simple, Bulk-logged or Full). An explanation of these terms are listed below:

1. Authentication Mode. This is selected during the installation of SQL Server. There are two modes:
1. Windows Authentication (Recommended). This mode only allows authentication from Windows Active Directory. Because you can place much tighter security controls on Active Directory Users, this is the preferred configuration. Of course, you'll need to have Active Directory implemented in your environment with at least two Domain Controllers (DCs) on your network for fault tolerance.
2. Mixed Mode. This allows both Windows Active Directory Users and SQL Server Users. In the SQL Server Management Studio, you can create specific SQL Server Users that will be allowed to authenticate to the SQL Server. Some applications require Mixed Mode authentication, but use Windows Authentication whenever possible.
2. Recovery Model. There are three recovery models in SQL Server.
1. Full (Recommended). All changes to the SQL Server are recorded in the Transaction Log File. This file is usually truncated after a successful Full Backup of SQL Server to keep the size manageable. Using a transaction log backup, you can restore a SQL Server Backup from the previous day and then use the transaction log to roll forward the SQL Server to a desired point in time.
2. Bulk Logged. Performs minimal logging, but does allow for a point in time restore. However, you can recover to the end of any backup.
3. Simple. No logging is performed. Changes since the last backup are unprotected. This is no point in time restore.

For the purposes of this Blog Post we will focus on SQL Server in Windows Authentication Mode and SQL Server databases with the full recovery model. When backing up any SQL Server, make sure to properly quiesce the SQL Server during the backup. Quiescing (called Application-aware processing in Veeam) temporary "stuns" the SQL Server and stops the transaction flow. This ensures that either ALL or NONE of a transaction is included in the backup. If you do not quiesce the SQL Server, you risk getting a SQL Server Backup with partial transactions. This is referred to as a "dirty" SQL Server backup because it has partial transactions in the backup. If you have to restore from a "dirty" backup you'll have to remove any partial transactions before it can be used.

For any backup for any server, make sure to follow the 3-2-1 rule for backups: 3 copies of your backup, on 2 separate media (typically disk and tape) with at least 1 copy off site and off line. Off line backups are vital in today's environment because they give you Ransomware protection. If all of your backups are stored online, get hit with ransomware, and your backup files are encrypted you will not have anyway to recover.

Consider performing hourly log backups for critical SQL Server databases. These log backup files should be stored on a separate server in case the SQL Server crashes during the middle of the day. Assuming you backup your SQL Server nightly and the next day your SQL Server crashes. You can perform a restore from the night before and use the log files to recover the SQL Server to within an hour of when it crashed.

We highly recommend using Veeam Backup Enterprise and Replication to backup all virtual servers. Listed below are the typical steps we use to backup a SQL Server in a virtualized environment.

1.    Create a dedicated backup account in Active Directory that will be used to backup VMs. DO NOT use the Administrator account! For more information refer to https://helpcenter.veeam.com/docs/backup/vsphere/required_permissions.html?ver=95u4.

2.    For SQL Server this account should have the following permissions:

1. SQL Server instance-level roles: dbcreator and public
2. Database-level roles: db_backupoperator, db_denydatareader, public
3. For System Databases:
1. master - db_backupoperator, db_datareader, public;
2. msdb - db_backupoperator, db_datawriter, db_datareader, public
4. Securables: view any definition, view server state
5. For truncation of SQL Server 2012 or SQL Server 2014 database transaction logs, this account should have the db_backupoperator database role (minimal required) or the sysadmin server role.

3.    Adequate Backup storage. We suggest having at least three times the amount of disk space in production on disk so you can retain a reasonable amount of backup history on disk – approximately three weeks.

4.    Backup Job Selection. For VMware, we suggest backing up at the vCenter (Recommended), Datacenter or Cluster Level. That any new VMs that are added at a lower level should get automatically included in the backup.

5.    Veeam Daily SQL Server Backups.

1. We suggest performing a full backup of SQL Server on the weekend and incremental backups during the week with Veeam. It used to be that incremental backup was something to avoid. However, with Veeam incremental backups are a good thing. If you need to perform a restore it will take the last full backup along with any necessary incremental backups and present the server to you as of the last incremental backup. There's no need to worry about if a file was included in an incremental backup.
2. We suggest performing synthetic full backups on the weekend. A synthetic full takes the last full backup along with the incremental backups and consolidates the files into a new synthetic full.
3. We suggest performing an active/real full backup at least once a month in case the synthetic full backups get corrupted.

6.     Application-Aware Processing. This will ensure that your SQL Server is quiesced when a snapshot is taken for the Veeam backup. For any SQL Server we suggest the following settings in Veeam:

1. VSS: Require success.
2. Transaction Logs: SQL: Truncate Logs.
3. Exclusions: Disabled.
4. Scripts: No.

7.    Store backups to disk. Theses backups should be stored on disk.

8.    Secondary Target. Create a Tape Job that will copy the backup from disk to tape after the disk backup is completed.

9.    Secondary tape backup. At least once a week, we suggest performing a secondary tape backup to a different Veeam Media Pool that is taken off site.

10.    Hourly SQL Server database Log backups to a different server. If the SQL Server is critical, we suggest creating a Maintenance Plan that backs up critical databases to a different server during business hours. For more information on log backups refer to https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/transaction-log-backups-sql-server?view=sql-server-2017.

11.    Perform a test restore. After you have your backup strategy, perform a test restore of the SQL Server. You can restore at the following level with the Enterprise Version of Veeam:

1. Entire Server.
2. Disks on the Server.
3. Files on the server.
4. SQL Server Databases.

Do NOT skip this step! You don't want to find out that you can't restore when you really need it. Make sure you can perform a proper SQL Server restore without any issues before you have to perform a "real" SQL Server production database restore.

SQL Server backups can be intimidating to an IT professional. Hopefully this Blog Post can help demystify SQL Server backups for your company.