Microsoft SQL Enterprise Bacula Plugin Quick Guide

This Quick Guide presents how to use the MSSQL VDI Plugin feature with Bacula Enterprise Edition. The Plugin supports:

• Full and Differential backups
• Incremental (Log) level backup
• Database level backup
• Ability to include/exclude databases from the backup job
• “Copy Only” backups
• Restore MSSQL backup files to disk
• Bacula Storage Daemon directly backup data stream
• Point in time recovery
• MSSQL Cluster backups

The MSSQL VDI Plugin has been tested and is supported on Windows 2003 R2, Windows 2008 R2, Windows 2012, Windows 2016 and later, MSSQL 2005, 2008, 2014 and later.

Installation

The MSSQL VDI plugin package is typically installed in the same Windows host, where the Bacula Client must also be installed.

Download the Client and Plugin installer from your exclusive customer repository:

bacula-enterprise-win64-10.0.2.exe  
bacula-enterprise-win64MSSQLPlugin-10.0.2.exe

Proceed to usual Client configuration, if still not configured, always as Administrator.

After Plugin installation, restart the Bacula Client Service at the Windows Services Manager (services.msc), in order to load the plugin library. A status client bconsole/bweb command will display all loaded plugins.

If the SQL Server database is running under an account that is not NT AUTHORIZED/SYSTEM, it will be mandatory to configure the SQL Server instance to allow the Bacula File Daemon service account to connect and perform backup operations. By default, the Bacula File Daemon service runs under the NT AUTHORIZED/SYSTEM account.

The permission sysadmin can be granted with the following SQL command:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITYSYSTEM]

Or as shown in Figure 1, through MSSQL Studio:

Figure 1. Bacula Backup User Sysadmin Permission

Point In Time Restore (PITR) requires the database to be configured with the full recovery model. If the database uses the simple recovery model, the transaction
log file will be truncated after each checkpoint. For more information, see https://msdn.microsoft.com/en-us/library/ms189275.aspx.

The “Transaction Log Backup” MSSQL feature is implemented as the “Incremental” level with Bacula. The database must be configured with the full recovery model
or bulk-logged recovery model. If the database uses the simple recovery model, the transaction log file will be truncated after each checkpoint. The full restore will be possible, but not the restore to a point in time. For more information, see https://msdn.microsoft.com/en-us/library/ms189275.aspx.

Configuration

FileSet Configuration

As shown in Figure 2, create a FileSet and proceed to the include – Edit Plugin Options. Select MSSQL Plugin from the combo box if using bweb.

Figure 2. FileSet Include Edit Plugin Options, MSSQL VDI Configuration.

As displayed in Figure 3, if nothing is changed, the plugin will try to backup all MSSQL backup instance databases (tempdb is excluded by default) and the following is the resulting configuration.

Figure 3. MSSQL VDI Configuration for Windows Authentication (default) and All Databases Backup

A Windows user and password might also be specified, as follows:

mssql: username=hfaria password=x

It is possible to select different instances or databases to be backed up with the following parameters: instance=, database=, include=, exclude=, domain=. E.g.

mssql: include=test2 include=prod1 include=r7*

Another example, with specific MSSQL instance, Windows User authentication (for MSSQL change value to server), password, domain, and hostname (local) specification.

mssql: instance=MSSQLSERVER authtype=windows hostname=. domain=bacula.com.br user=usr_bacula password=xxx

A complete list of MSSQL VDI plugin options is available in the referenced whitepaper.

As exhibited in Figure 4, still in the FileSet configuration, the MSSQL Plugin does not use VSS snapshots to perform the backup so, unless some disk folder is present in the fileset, “Enable VSS” must be set to “no” (unchecked).

Figure 4. MSSQL VDI FileSet with Unchecked Enable VSS Option

If you are using a Microsoft SQL cluster the backup connection default method (Shared Memory) is not supported. You may also want to use another one, such as ODBC.

Create a new System DSN on the Windows Client Backup Server, remembering to save the DSN and Driver names. Ref.: https://support.microsoft.com/en-us/help/965049/how-to-set-up-a-microsoft-sql-server-odbc-data-source

In this case, FileSet could be configured like this:

mssql: instance=MSSQLSERVER connection_string="DSN=bacula;Driver={ODBC Driver 13 for SQL Server}"

Backup Job Configuration

Create a new backup Job, associating it with the MSSQL machine and the newly created FileSet. Apply the Director configuration changes.

Run a test backup job.

Restore

The bacula restore where= and database= options define what type of database restore will be performed.

Reference

For more examples and details, including MSSQL Always On availability groups backup procedures, refer to the MSSQL Backup and Restore – Bacula Enterprise Edition Whitepaper. https://baculasystems.com

Disponível em: Português (Portuguese (Brazil))EnglishEspañol (Spanish)