Backing up and restoring virtualized Microsoft SQL Server databases

  • Tutorial
Version 8.0 of Veeam Backup & Replication provides a wide range of options for backing up and restoring virtualized SQL servers and databases, including popular methods for full virtual machine recovery, granular file-level recovery and database recovery using the Veeam Explorer tool for Microsoft SQL Server. The last of these will be my story today.



Of course, you need to start “from the stove” - that is, from an understanding of how recovery options relate to the backup settings and the virtualized SQL Server itself.

Everyone knows that Veeam creates a backup copy of the virtual machine at the image level - that is, in the case of SQL Server, the entire server will be backed up, including all instances and databases on them. In order for the backup to turn out to be consistent with respect to transactions (transactionally-consistent), when creating it, you should use image processing taking into account the operation of applications (application-aware image processing). Moreover, you can prepare a backup copy of the server so that later specific databases will be restored from it to a state at any time or even to a selected transaction.

We plan resources


Here are a few factors to consider when planning your SQL Server backup for later database recovery:
  1. Infrastructure resources and requirements of backup and recovery policies, namely:
    • What SQL servers should I backup?
      Veeam works with Microsoft SQL Server 2005 SP4 and higher; All editions are supported. For Microsoft SQL Server 2012 and above, AlwaysOn Availability Groups are supported.
    • What are the required RTO and RPOs for databases?
      The frequency of backups will depend on these values ​​as well. As a rule, the more often backups are done, the less time it will take to recover.
    • How intensively do applications access the database?
      Along with the RTO indicator, this factor must be taken into account when setting up the backup window, the backup job schedule and calculating the place to store backups of transaction logs (if necessary).
    • Is the backup repository big enough?
      There should be enough space for backups of the server itself and transaction logs. It is useful to estimate how much space they will need approximately. This can be done, for example, using a calculator, or using the VM Change Rate Estimation report from the Infrastructure Assessment Reports package (if you have Veeam ONE installed).

  2. The logging and recovery model for the required database. It depends on which options for processing transaction logs can be used, and what recovery scenarios can be applied in the future:
    • If the Simple model is specified for the database , then it will be possible to restore the database only to the state in which it was at the time of the creation of a specific recovery point (backup or replica). In this scenario, SQL Server will automatically trancate logs.
    • If the model is different ( Full or Bulk-logged ), then automatic trankate will not occur, and you can configure the necessary options for processing logs.

  3. What tool will be used for backup and recovery?
    • If Veeam will be used for backups of a virtual machine with SQL Server and transaction logs of the corresponding databases, then in the settings for the backup job of the machine you will need to select the option Process transaction logs with this job :



      After that, the settings for processing transaction logs will become available (more on this later).
      Note: If you select this option when setting up a backup task for a machine with another application (not SQL, but, say, Exchange Server), then it will enable automatic transaction log transiting for this application. For more information about working for Exchange, see the article on Habré .
    • If you plan to use Veeam only to create a backup of the entire virtual machine, and you are going to transfer transaction logs for processing to, say, a third-party application, then you need to notify Veeam Backup & Replication about this - that is, indicate that the backup chain will be created by another tool, and Veeam should create its backup with the flag COPY_ONLY (only in this case the chain will remain unchanged). This is done by selecting Perform copy only (lets another application use logs) :

      image

      After this, transaction processing processing settings will be “hidden” as unnecessary; Veeam will backup this SQL Server using the VSS_BS_COPY method to create a snapshot; transaction logs will not be touched, so the database administrator will need to take care of their future fate.


A bit of theory: we understand how the processes go


In order to provide the ability to restore the database at any time or for the selected transaction, we need to create:
  1. A backup copy of the virtual machine, taking into account the operation of the application (i.e. SQL Server)
  2. A backup copy of the transaction log, which will then be “rolled” onto the existing backup to bring the database to the desired state

Let's see how Veeam Backup & Replication manages these tasks.

So, if we need to backup logs (which will give us the possibility of the most “targeted” recovery of the database), then the backup task of our SQL Server will, in fact, include 2 interrelated tasks:
  1. Virtual Machine Backup with SQL Server - runs at the machine image level, appears hereinafter as the "parent", bears the name that you specified for it in the UI (for example, Test Job ). It is created by the user in the console, launched according to a schedule or manually.
  2. Backup of transaction logs - appears hereinafter as “child”, bears the name of the “parent” and the suffix SQL Backup (in our example, we get Test Job SQL Backup ). It is created without user intervention, automatically, by the Job Manager component under both conditions:
    • there is at least one “parent” that will run on a schedule and create a backup based on the application (in the settings of the “parent” Enable application-aware image processing is selected )
    • the option of backup logs is enabled (also in the settings of the "parent")


This "bunch" of tasks works like this:

image

Steps 1 and 2


According to the schedule, Job Manager running on the Veeam backup server launches a “parent”, which creates a backup of the virtual machine with SQL Server and puts it in the repository.

Step 3


The “child” session starts: with its beginning, Veeam Backup & Replication installs a component called Veeam Log Shipper Service inside the guest OS of our SQL server. This service will work throughout the "children's" session and will be responsible for processing logs. In particular, it collects information about which logs of which databases should be backed up during the session, and how exactly it is possible to transfer data to the repository (either directly or through the log shipping server - let's call it “journal proxy”). At the end of the "child" session, this component stops and is demolished from the guest OS; with the beginning of a new session, everything repeats (installation, data collection, stop, uninstall).

A backup of the transaction log on the guest OS is created using SQL Server itself; he also performs trankate logs on the guesthouse. The resulting copies are saved as .BAK files in a temporary folder on the guest file system (by default, this is % allusersprofile% \ Veeam \ Backup ).

Step 4


Every 15 minutes (default frequency) Job Manager identifies which databases are available on this SQL Server and compares the resulting list with data on the contents of the virtual machine backup (taken from the Veeam Backup & Replication database). Thus, it turns out for which databases there are logs that should be "taken" to the repository and put next to the corresponding SQL server backup during this 15-minute interval.

Note: If there are still copies of the logs that for some reason were not “taken” to the repository during the previous intervals, Veeam will identify them by enumerating .BAK files in the temporary folder and mark them as “to be transported”.

Step 5


.BAK files are transferred from the guest to the repository (directly or through a "journal proxy"); the Veeam transport service on the source side before that still performs their compression according to its default settings. On the repository side, compression is performed according to the settings of the “parent” task (more details can be found here in Russian).

After the "transportation" of data to the repository, they are deleted from the temporary folder on the virtual machine. If something could not be "transported" during the allotted interval, then these files will not be deleted, and during the next 15-minute Veeam will try again.

The operations that are performed during the 15-minute interval (the so-called log backup interval) are shown in the figure:

image

The totality of such intervals (their duration, by the way, can be changed) between two consecutive starts of the “parent” task is a “children's” session.
  • The initial session starts at the moment when “Activate Schedule” was indicated in the “parent” settings.
  • The “child” task continues to work in the background between the starts of the “parent”, performing the operations described above. With the launch of the “parent”, the current “children's” session ends, and a new one starts immediately, along with the “parent” they go through steps 1-5 (see above).
  • The "children's" session ends immediately before the start of the "parent", or when the "parent" is deactivated (via the menu command or through the deactivation of the schedule).

If you draw a picture, it will look like this:

image

Example


I will explain the picture with words. Let's say the “parent” task is set to run daily at 11 o’clock in the evening, starting on May 5. Then the following sequence will take place:
  1. The very first “children's” session starts at the moment when the automatic start schedule was activated for the “parent” (the backup of the virtual machine itself has not yet been created) - this setting was made at 7 o’clock on May 5. The “child” task is in the Idle state , waiting for the backup of the machine to be created.
  2. At 11 pm on May 5, the “parent” is launched and creates a backup of the SQL server.
  3. The “child” task enters the Working state , and the sequence of 15-minute intervals begins. If all the backups of the logs were successfully “transferred” to the repository before the time for starting a new session approaches, the “child” will switch back to the Idle state and will wait until the session expires.
  4. At 11 pm on May 6, the “parent” starts again, and a new “children's” session starts.


Note: Since backup of logs requires a backup of the corresponding database (read, server), it should be remembered that the logs for each new database will be backed up only after the backup of the server with this new database has been successful.

What is saved to the repository?


Backup copies of transaction logs in the form of .VLB files are saved in the repository (next to the backup of the corresponding server); the default path to the folder is C: \ backup \. The metadata of the backup chain in the form of .VBM files is also placed there.

By default, the policy for storing .VLB files is configured to comply with the policy for storing backups of the SQL server, so that there are always logs that could be rolled onto the selected server restore point and get the desired state of the database. Naturally, with the removal of the restore point after the expiration of the storage period, the corresponding log backups will also be deleted.

There is another option - to store .VLB backups for the specified number of days; it can come in handy if you want to save space and plan to store only the latest data to restore to a recent state. However, you should make sure that the storage policies for the server backup and .VLB files are configured so that the server restore point will not be deleted earlier than the corresponding .VLB - otherwise there will be nothing to “roll” the logs.

Enough theory, let's practice now! Set up a backup task


Selecting objects to set


At the step of selecting objects for inclusion in the "parent" task, the following should be considered:
  • Databases located on one instance will be processed sequentially.
  • If you want to create backups of transaction logs for all databases, except for one or several specific ones, then for these exception databases you need to specify the recovery model simple .
  • For SQL Server 2012 and above, Veeam supports AlwaysOn Availability Groups (we verify that SQL instances are on WSFC nodes).


Setting up virtual machine processing taking into account a running application (SQL server)


At the Guest Processing step, when setting up guest OS processing, we select these options:
  1. We check the Enable application-aware processing checkbox so that the processing of the virtual machine goes taking into account the operation of the applications.

    image

  2. We go to the Guest OS credentials section and specify the account under which we want to perform actions on the guest OS (including the backup of the logs from the guest OS to the repository and log trankate). This account will require the following rights:
    • For backup, the sysadmin fixed server role on the processed SQL Server.
    • For trankeyta - if you have SQL Server 2012 or SQL Server 2014, then at least the db_backupoperator role for the desired database, or again the sysadmin server role .

    Important note: Beginning with Veeam Backup & Replication 8.0 Update 2 (build 8.0.0.2021), in the event of an unsuccessful attempt to trankite under the specified account, one more attempt will be made under the NT AUTHORITY \ SYSTEM account.

    Therefore, for SQL Server 2012 or SQL Server 2014, you should make sure that this account has sufficient rights (for more details see http://www.veeam.com/kb1746 ).
    As for SQL Server 2005, 2008 and 2008 R2, the default settings for them provide the necessary rights for local SYSTEM (however, there is always the possibility that someone could change them - in this case, we recommend that you still double-check it again).
  3. Then we press the Application button , in the list presented, select the SQL Server we need and click the Edit button , after which a dialog opens with the processing settings of the guest OS.
  4. We go to the General tab , find the Applications section there and select the Require successful processing (recommended) option - with this setting, Veeam will stop the backup process for any error received from VSS during the “freezing” of our application. Thus, we aim at creating a transactionally-consistent backup backup copy.

    image

  5. Next, we go to the Transaction logs section and select the Process transaction logs with this job option there - as described above, this means that both the backup of the virtual machine and the backup of the logs will be performed using Veeam.
  6. After that , the SQL tab will become available , where you need to choose what you want to do with the logs (that is, transaction logs). The following options are available:
    • Truncate logs (prevent logs from growing forever) - we want to trankeytit logs. Do not forget that the account that will be used to work with the guest OS must have the appropriate rights. In this version, Veeam will wait for the virtual machine to backup and then give a signal to the log trankate (which will be performed using VSS). This option will allow you to restore the database only to the selected recovery point (more “targeted” recovery will be impossible).
    • Do not truncate logs (requires simple recovery model) - we want to leave the logs on the SQL server as is, i.e. so that Veeam does nothing with them (neither backup, nor trankate). In this option, your database administrator will have to do something with them (otherwise the logs can grow and take up a lot of space, that’s why it says “use the simple model if this option is selected”). This option will also allow you to recover only to the selected point.
    • Backup logs periodically - we want to save log backups to the repository next to the backup of the SQL server itself. This option allows you to use any recovery scenario.

      We choose with what frequency we want to “transport” the logs to the repository (by default, these are the same every 15 minutes); set storage policy (Recommended According to the corresponding image-level backup ). Well, in conclusion, we indicate the “journal proxy” through which the data will “go” to the repository.

      image

  7. To transfer files to the repository from the guest OS, 2 types of transport are supported:
    • Directly from the guest OS to the repository is the best method, since it does not require additional resources and at least loads the guest system.
    • If a direct connection cannot be established, then we use the log shipping server, or the “journal proxy” - this can be any Windows server added to the Veeam Backup & Replication console.

    You can specify that the selection of the “journal proxy” should occur automatically, or you can select the appropriate servers from the list yourself. Veeam will automatically assign one of the servers to the proxy role, focusing on 2 criteria - possible methods of data transfer and availability over the network. It is also reasonable to have more than one proxy for this purpose (in case of failure). Learn more about journal proxies here .
  8. As for the schedule, it is recommended that the “parental” task be run at the minimum production hours. The “child” task (ie, log backup) will by default try to “transfer” the logs to the repository every 15 minutes - this interval can be changed. Of course, one should take into account the real time during which the files are transferred to the repository (i.e., do not set the interval to 5 minutes if the process actually takes all 15).

That, in general, is all. Click OK to save the settings, close the dialog, return to the task wizard and go through it to the finish. At the same time, we remember that at the Schedule step of our "parent" task, it is MANDATORY to select the Run the job check box automatically , otherwise the "child" task will not be activated.

What's next?


In the next part I will talk about how you can implement the selected recovery scenario and what points you should pay attention to when preparing and planning this process. In the meantime, you can read more:

Also popular now: