Best Practices - SQL Server iDataAgent

Concurrently Backing Up SQL Server with Other Applications

When running backups using the Microsoft SQL Server iDataAgent, do not concurrently perform backups using other applications (e.g., Microsoft SQL Server Enterprise Edition). Doing so may compromise future restore operations.

There are a few jobs that SQL Server restricts during a backup. If one of these jobs are initiated while a backup is already in progress (or if a backup is initiated while one of these jobs is in progress) the backup job will terminate. These jobs are:
  • creating or deleting database files
  • creating an index
  • performing any non-logged job
  • shrinking the database.

Changing the Recovery Model in SQL Server

If you change the recovery model from Simple Recovery Model to either Bulk-Logged or to Full, it is recommended that you run a full database backup.

Increasing the Speed of SQL Backups

If you want to increase the speed of SQL backups, you can increase the number of streams used by the backup to a number greater than 1 and enable software compression at the subclient level. Keep in mind that when you perform backups using multiple streams, you must have the same number of streams to restore the data.

You can set up multiple streams as described in Configuring Data Streams.

Reconfiguring Default Subclient Content

We recommend that you do not re-configure the content of a default subclient because this would disable its capability to serve as a catch-all entity for client data. As a result, some data will not get backed up or scanned.

Creating Subclients

As a best practice, it is recommended that you add only a few small databases to each subclient and add larger databases into separate subclients. On a restart condition, the system will back up all databases in the subclient from the beginning. For this reason, you would not want a large database that has completed its backup successfully to be backed up again only because a smaller one has caused a restart to occur.

File and File Group Backup and Restore

If you plan to execute File/File Group restore jobs, plan to back up Transaction Logs regularly, because File/File Group restores cannot run without them.

Transaction Log Backups are required, refer to Microsoft article at http://support.microsoft.com/kb/281122.

Backing Up and Restoring Replication Databases

SQL Server replication allows distributing data to various servers throughout an organization.

It is recommended to backup the following replicated databases and their associated system databases regularly:

  • The publication database at the Publisher

    The master and msdb system databases at the Publisher and the publication database must be backed up at the same time.

  • The distribution database at the Distributor

    The master and msdb system databases at the Distributor and the distribution database must be backed up at the same time.

  • The subscription database at each Subscriber

    The master and msdb system databases at the Subscriber and the subscription database must be backed up at the same time.

Also make sure, when these databases are restored, the master and msdb database are consistent with the associated system database in terms of replication configuration and settings.

For backups and restores recommendations and strategies, refer to Microsoft article Backing up and Restoring Replicated Databases at http://msdn.microsoft.com/en-us/library/ms151152.aspx.

Changing Storage Policies

If you change a storage policy for a default subclient, and want to restore to a point in time prior to the change (i.e., restore from previous storage policy), perform a Full backup of the database as soon as possible. Data from the old storage policy will not be considered as the most recent cycle and when it exceeds its retention period, and hence will be pruned.

Backing Up SQL Databases with Unicode Names

In farm configurations, SQL Databases with Unicode names should be backed up using other SQL backup tools since only standard alphanumeric characters are supported for the Microsoft SQL Server iDataAgent. The SQL Server iDataAgent does not support databases with Extended ASCII or UNICODE characters in their names.

Protecting SQL Databases without Using Availability Group Clients

In the SQL Management Studio, it is recommended to set primary as the preferred replica to ensure that complete backup cycles are available for restores later. Setting this preference will eliminate the possibility of the following errors:

  1. Skipping database [AlwaysOn_DataBase]. Not a preferred replica to run backup

    This error is displayed when the replica is not set as the backup preference and the SQL iDataAgent is set to recognize the backup preference setting of the availability groups. When a backup of the replica is attempted the databases which are a part of the availability group will be skipped.

  2. Skipping database [AlwaysOn_ DataBase]. Only BACKUP LOG is fully supported on secondary replicas. BACKUP DATABASE supports only copy-only full backups of the database, files, or file groups. Differential backups are not supported on secondary replicas

    This error will be displayed because the secondary replicas support copy only database backups. When backup of databases which are a part of availability group is attempted on secondary replica, such databases will be skipped from being backed up.

  3. If transaction log backups are running on all replicas, and truncation happens during backup on secondary replica it will lead to breaking LSN chain on secondary replica. This will automatically attempt to convert next transaction log backup into a full backup, which is not supported on secondary replicas.

Do Not Use Native SQL Backup Compression When Using Deduplication

To improve the SQL backup performance with deduplication, ensure that the Native SQL backup compression option is 'OFF' on the Advanced Backup Options dialog box.