LiveVault Information: Microsoft SQL General Considerations

Microsoft SQL Server General Considerations

This topic pertains to Microsoft SQL backup and restore policies, which LiveVault supports on SQL Server 2005, 2008, 2008 R2 and 2012.

• To back up SQL Server 2012 AlwaysOn Availability Group, contact us for assistance
• To back up SQL Server 2000 or 7.x, you must use a standard backup policy.

SQL Server Considerations

Before you create a SQL backup policy, consider the following factors:

• On a SQL Server you can run multiple SQL Server services, each with their own ports, logins, and databases. Each of these services is called an instance. You can further categorize these as the default instance and named instances. This topic uses the term instance and SQL Server instance without making a distinction between default instances or named instances.
• SQL Server instances must be running for a SQL policy to successfully back them up.
• In SQL 2005, the SQL VSS Writer service is set to Manual by default. Change it to Automatic, and then start it for SQL policies to succeed.
• To use the transaction log truncation feature, install SQLCMD.exe. It is part of Basic Management Tools in the SQL installation.
• For SQL backups and restores to succeed on 2-node Windows clusters with multiple instances, all instances must be running on the same cluster node.
• By default, LiveVault backs up 75 databases on a 32-bit system and 150 on a 64-bit system. However, you can increase this number to 300 databases on a 32-bit system and 600 databases on a 64-bit system if you adjust the max worker threads to an appropriate value.

SQL Server FILESTREAM Feature Backup Considerations

LiveVault does not support SQL backup policies on the FILESTREAM feature available in SQL. For more information on the FILESTREAM feature in SQL Server, refer to your SQL Server documentation.

To back up SQL databases using the FILESTREAM feature, you must use a standard backup policy to back up the database files. If a SQL backup policy already exists that protects FILESTREAM-enabled databases, the SQL backup policy must be deleted, and a standard backup policy created.

Adjust the SQL Max Worker Threads Option:
To adjust the SQL max worker threads to enable the backup of more SQL Server databases Launch SQL Management Studio.

• Provide the server name and login credentials.
• In the Object Explorer pane, right-click the SQL Server instance that you want to configure.
• Select Properties from the context menu.
• Select the Processors page.
• Type the number of worker threads required to back up your databases. SQL Server requires three worker threads for each database that you want to back up. Do not enter more than 1024 threads on a 32-bit system.
• For these changes to take effect, restart the SQL Server instance.

Prepare for Log Truncation

This option allows SQL to reuse the space consumed by transactions that have already been committed to the database. You should not choose this option if you are using another application besides LiveVault to manage or backup the transaction logs. If you choose not to use this feature, then transaction logs for any databases using full or bulk-logged recovery models will continue to grow unless they are periodically truncated by some other means.

• Master databases, model databases, and MSDB will not have their logs truncated.
• If you plan to truncate logs, you must have the SQLCMD.EXE utility installed. Whichever account you use to run the lvbackupservice service must have rights to run SQLCMD.EXE.
• Each SQL database can have its recovery model set to Simple, Bulk, or Full. LiveVault will protect SQL databases in all combinations of the Recovery Model and the Truncate Logs option the user chooses for those backup policies. In cases where the user sets a policy to Truncate Logs on a database where the Recovery Model is set to Simple, the backup log will contain a warning since LiveVault cannot truncate the logs on the database.

Best Practices for Log Truncation

• On databases where you are using the Simple recovery model, do not set the LiveVault backup option to truncate logs.
• On databases where you are using the Bulk or Full recovery models, use the LiveVault backup option to Truncate Logs, or manage the logs by other means.
• When a SQL server has multiple databases with different recovery model settings, and you are using LiveVault to manage the logs, create multiple LiveVault backup policies for the different databases, each with the appropriate setting for truncating the logs to match the Recovery Model of the databases the policy is protecting.

Back Up the Master Database
Every instance of Microsoft SQL Server has a master database that contains information about users and databases that are part of the instance. To protect your SQL Server installation, include the master database when you create SQL backup policies.

To recover the master database, consider the following factors:

• You must manually attach any databases you created after LiveVault backed up the master database. This is because the master database contains information about which databases are part of the instance.
• If you made any user login changes after LiveVault backed up the master database, the changes will be lost. You must redo the changes after the restore.