Protect Microsoft SQL Server Databases

Once you have Registered a Microsoft SQL Server as a source, you're ready 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.

    Clickto turn on Auto Protect. By enabling this option:

    • All the SQL databases that are added to the SQL server in the future are automatically added to the Protection Group and are protected from the next protection run.

    • All the SQL databases that are removed from the SQL server in the future are automatically removed from the Protection Group and are not protected from the next protection run. The existing backup for the user is preserved until the snapshot expires.

    For example, you create a Protection Group that backs up a SQL Server. The Protection Group runs for two weeks and then a new database is added to the SQL Server. The next time the Protection Group runs, if the object hierarchy has been refreshed on the Cohesity cluster, the new database is also backed up even though the new database has not been explicitly selected to be included in the Protection Group. The object hierarchy is automatically refreshed every four hours.

    To manually refresh the object hierarchy, select Data Protect as a Service > Sources, find the source in the list and click . To exclude an object from Auto Protect, click .

    • Indicates an Auto Protected object.

    • Use this option to exclude SQL databases from the protection group. Excluded objects are not protected. The icon indicates that an ancestor of the object is Auto Protected but this object is explicitly excluded.

      To exclude a database from the protection group, specify the absolute path of the database to be excluded from the backup at a protection job level. You can optionally use:

      • Wildcard Exclusion - You can optionally use the Wildcard * in any location in the search string to exclude all the matching databases from the backup at a protection job level.

      • Regex Exclusion - You can use the RegEx string name match and manual user selection to exclude DBs from backup at a protection job level.

    You can use the exclude SQL database feature only if Auto Protect is enabled for a backup job.

    Protection of dismounted databases, recovery database types, and deleted databases are automatically skipped by the Protection Group.

    After defining the filer click Apply to save the selection.

    The following table lists the examples for excluding objects in a SQL database:

    Sample Configuration

    10.2.157.20 (Host)

    * SQL2012

    (SQL Server Instance)

      - UserDB1

      - UserDB2

      - TestDB1

      - TestDB2


    * SQL2014

    (SQL Server Instance)

      - UserDB3

      - UserDB4

      - TestDB3

      - TestDB4


    * MSSQLSERVER

    (SQL Server Instance)

      - ProdDB1

      - ProdDB2

      

     

    Example of Absolute Path

    • 10.2.157.20/SQL2012/UserDB1 - Excludes the database 'UserDB1' from the SQL server instance 'SQL2012' on the host 10.2.157.20.
    • 10.2.157.20/SQL2012/ - Excludes the 'SQL2012' from the SQL server instance and its databases.

    The absolute instance path must end with '/' and the plain text instance path must contain two or more slashes.

    Example of Wildcard Exclusion

    • 10.2.157.20/SQL2012/User* - Excludes the databases 'UserDB1' and 'UserDB2' from the SQL server instance 'SQL2012' on the host 10.2.157.20.
    • 10.2.157.20/SQL201?/* - Excludes the SQL server instances 'SQL2012' and 'SQL2014' from the host 10.2.157.20.

    Example of Regex Exclusion

    10.2.157.20/SQL.*/.* - Excludes the SQL server instances 'SQL2012' and 'SQL2014' from the host 10.2.157.20.

    Considerations

    Consider the following when using the Auto Protect option on the backup job

    • If new databases are added on the host, the existing filters are applied on the newly added databases. For example, if the exclude filter matches the newly added databases then these databases will be excluded in the backup job.
    • If any system databases are excluded by the filters in a SQL server, it will exclude all the system databases belonging to that SQL server.
    • When defining filters to exclude AG databases, ensure that the filters are configured on all the AG hosts. For example, to exclude an AG database and if the AG database has 4 hosts, then ensure that the filters are defined on all the four AG hosts.

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

    If you select a policy that includes Log Backup (Database), the Log Backup Settings option will be enabled.

  4. Click More Options and review the following settings:

    Microsoft SQL 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.

      The WITH clause text box allows you to invoke the SQL Server general options during backup and recovery. The text field accepts any SQL Server general backup or recovery option like “ENCRYPTION”. Note that specifying an option could add to the overall duration of the backup or restore process.

      This is the full/incremental WITH clause with the default value "WITH MAXTRANSFERSIZE = 4194304, BUFFERCOUNT = 64".

    • Number of Streams. Define the number of .bak files you want to create for better backup performance. By default, Cohesity DataProtect as a Service 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).

    Log Backup Settings
    Cohesity enhances the SQL Server native T-log backup and restoration performance by splitting the T-log backup into multiple backup files and ingesting them in parallel through the Cohesity nodes.

    • 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.

      The WITH clause text box allows you to invoke the SQL Server general options during backups and restores. The text field accepts any SQL Server general backup or restore option like “ENCRYPTION”. Note that specifying an option could add to the overall duration of the backup or restore process.

      The default WITH clause is “WITH MAXTRANSFERSIZE = 4194304, BUFFERCOUNT = 16".

    • Number of Streams. Define the number of .bak files you want to create for better backup performance. The default number of streams for log backup is 1.

  5. Click Protect.

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

Cohesity DataProtect as a Service 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.

Troubleshoot

  • The log back up of AG MS SQL Server fails with the following error if there is a break in the log chain.

    Log chain break error: Discovered a break in the log chain for <Database Names>

    Resolution: To resolve this issue, ensure that no other third-party applications are running a log backup, and then perform a full backup run to reset the log chain.

  • The log back up of the AG MS SQL Server fails with the following error if a database is added or removed from the AG MS SQL Server.

    AG relationship error: Discovered a AG relationship error for database <Database Names>

    Resolution: To resolve this error, perform a full backup run.

For more troubleshooting information, see the following KB article:

Log in to the Cohesity Support Portal to see more KB articles.