Protect Microsoft SQL Server Databases

Once you have registered a Microsoft SQL Server as a source, you're ready to use Cohesity DataProtect to protect the Microsoft SQL Server databases on that server.

To protect your Microsoft SQL Server databases:

  1. In DataProtect as a Service, under Sources, find the Microsoft SQL Server source, click the Actions menu (⋮), and select Protect.

  2. Click Add Objects. Browse through the SQL Server instances and select the databases that you want to protect including MS SQL standalone, FCI, and AG. Click Continue.

    AG and FCI are represented using the icon.
    For AG, the AG Replica details like Hostname, Server Name, Primary Role Allow Connections, Secondary Role Allow Connections, and Role are available in the Settings tab.

  3. Choose a policy to specify backup frequency and retention.* If you don't have a policy, you can easily create one.

  4. Click More Options and review the following Microsoft SQL Server Settings:

    • Make Full Backups Copy-only. Enable if you want full backups to be copy-only backups so they do not affect the differential base. Note that copy-only full backups do not take log backups even if the policy schedules them.

    • WITH Clause. Define the WITH clause that you want to use to customize the backup. For more information, see BACKUP (Transact-SQL) in the Microsoft documentation.

    • Number of Streams. Define the number of .bak files you want to create for better backup performance. By default, Cohesity DataProtect creates three .bak files for each database backup for better backup performance.

    • User Databases. Select how to handle AG databases during backup.

    • AG Backup Preferences. Select this option if AG databases will be backed up.

      • Use Server Preferences uses Microsoft SQL preferences.

      • Override Preferences enables you to override Microsoft SQL preferences with your selection.

        AG replica preference for Microsoft SQL backups

        Cohesity uses "replica priority" to select the best replica when more than one qualified replica matches the backup preference. For the following AG Backup Preference settings, Cohesity uses replicas in the indicated order of preference to back up Microsoft SQL databases:

        Backup Preference Setting Replica Used for MS SQL Backup
        Prefer Secondary or Any

        1. Sync Secondary Replica

        2. Async Secondary Replica

        3. Primary Replica
        Secondary Only*

        1. Sync Secondary Replica

        2. Async Secondary Replica
        Primary Only 1. Primary Replica

        * If the AG uses the Secondary Only Backup Preference setting, ensure the AG replicas are set to "Readable secondary=Yes" or "Readable secondary=Read-Intent”.

        You can set Readable Secondary field to No if the Backup Preference Setting is set to Prefer Secondary or Any or Primary Only.

        Differential backup can be performed only when the Backup Preference is set to Primary Only.

        You must apply the exclusion filter on each AG replica to ensure the database is excluded when a SQL Server failover occurs.

    • System Databases. Select whether to back up or skip system databases.

    • Databases to Backup. Select the User Databases and System Databases. For AG, select the AG Backup Preferences. You can Use Server Preferences or Override Preferences (the options include Primary Only, Secondary Only, Preferred Secondary, and Any).

  5. Click Protect.

The backups start immediately after you protect the objects, regardless of the time you set for the protection run.

Cohesity DataProtect starts backing up the databases you selected.

When choosing or configuring your policy, ensure the full, incremental (SQL Differential), and T-Log backup retention periods are properly configured. The retention period requirements for SQL VDI are identical to those for SQL native backups. For example, we recommend aligning your retention periods for each backup type along these lines:

  • Full Backups. Daily at 1 AM with a 7-day retention.

  • Incremental Backups (equivalent to SQL Differential backups). Every 12 hours with a 3-day retention.

  • T-Log Backups. Every 15 minutes with a 1-day retention.

The following error message is displayed when the Physical SQL Server source includes manually protected AG databases:"Protected objects are missing from the source. This might lead to backup failures."
You can click View and unprotect the objects or use the search box to filter the objects with status, Unavailable, and unprotect them.

Next > When the first protection run completes, you will be ready to recover your protected databases when and if you need to.