Was sind die Sicherungsmodi VSS und ODBC mit einer MSSQL-Sicherung

Betriebssystem

Windows

Software

8.1 and higher

Problem

You want to backup an MSSQL server and see that you can choose from ODBC and VSS and don't know what these options mean.
VSS vs ODBC

Lösung

Pro Backup allows you to back up databases in your Microsoft SQL Server with the MS SQL Server Backup Module, which provides you with a set of tools to protect your MS SQL Server, whether in VSS backup mode or ODBC backup mode.

VSS-Modus

VSS-based backup utilizing the Microsoft SQL Server VSS Writer to obtain a consistent snapshot of the MS SQL databases, no spooling / staging of database file(s) is required during the backup process.

Temporary Folder Requirement

Location for temporary folder
The temporary directory folder is used by Pro Backup for storing backup set index files and incremental/differential delta files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows and or database.
Temporary folder capacity
With VSS-based backup, the disk space of the temporary folder required for storing the VSS image is significantly smaller than using the ODBC spooling backup method. As the extra space is not required to hold the full database.

It is recommended that the temporary directory should have at least free disk space of 50% of the total database size. The rationale behind this recommended free disk space is the default in-file delta ratio settings is 50%, therefore Pro Backup could generate incremental or differential delta file(s) of up to 50% of the total database size. The actual free disk space required depends on various factors including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.

Pros

Fast and minimal interruption
The database snapshot capture process is fast and can take place on a running server, as you may continue to work when the snapshot capturing is taking place, there may be another process that holds your input in some memory section until the snapshot capture is completed. That said, the whole snapshot capture is fast, so there is no need for you to stop working and it causes minimal interruption to your business operation.
Significantly lesser disk burden
VSS Snapshot typically requires much less additional disk space than clones which is the traditional backup method by spooling database into the temporary folder. Oftentimes, the capacity of the database to back up is huge and therefore the temporary folder would overload with the equal or even larger disk space if traditional backup method is used. By utilizing the VSS technology, it helps your system greatly reduce disk capacity burden and promote optimized performance.

Cons

No Transaction Log Backup
MS SQL does not support transaction log backup when VSS is used, therefore, transaction log backup will have to be done manually.
Workaround is time consuming
In order to truncate the transaction logs, you have to either change the Recovery model to Simple or perform a manual log truncation, which could be time consuming.

Transaction Log Handling
VSS based backup no longer requires backup of the transaction log files, however for databases configured in either full or bulk-logging recovery model, this may eventually result in transaction logs filling up the available disk space on the volume of the MS SQL Server. https://technet.microsoft.com/en-us/library/cc966520.aspx

To prevent this from occurring, it is recommended to change the recovery model of database selected for backup to simple recovery model. Refer to the following steps for details:

In SQL Server Management Studio, expand Databases, select a user database, or expand System Databases and select a system database.
Right-click the corresponding database, then click Properties to open the Database Properties dialog box.
In the Select a page pane, click Options.

The current recovery model is displayed in the Recovery model list box. Modify the recovery model by selecting Simple from the model list.

Only modify the recovery model of a live database during low activities hour.
It is also recommended to perform a full backup before changing the recovery model.

ODBC-Modus

By using the ODBC mode for MS SQL backup, database files are spooled to a temporary directory before being uploaded to the backup destination.

Temporary Folder Requirement

Location for temporary folder
The temporary directory folder is used by Pro Backup for storing; the database files, incremental/differential delta files, and backup set index files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows and or database.
Temporary folder capacity
ODBC backup requires a significantly larger disk space of temporary folder as it need to store the database files spooled during the backup process. It is recommended that the temporary directory have disk space of at least 150% of the total database size. For each database backup, Pro Backup will spool the database files to the temporary directory before they are uploaded to the backup destination. Also, additional space is required for in-file delta generation the default in-file delta ratio settings is 50%, therefore Pro Backup could generate incremental or differential delta file(s) of up to 50% of the total database size. The actual disk space required depends on various factors, including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.