Microsoft SQL Server Protection and Recovery with Rubrik

SQL Instance Addition and Windows Service Configuration

  • The interaction between Rubrik and Microsoft SQL Server instances is facilitated by the Windows service account.
  • Management of SQL instances begins with the installation and configuration of the Rubrik Backup Service (RBS) on the host.
  • The recommended best practice for the Windows service account is to use a Group Managed Service Account (GMSA).
  • A GMSA is a unique, non-interactive account characterized by high-end security.
  • Configuration of the service account is performed under the Rubrik Backup Service Properties.

SQL Inventory Detection and Network Requirements

  • Rubrik automatically detects SQL instances on any previously added Windows host.
  • Detected instances appear within the SQL Server database inventory tile.
  • Users can navigate the inventory to view all added SQL instances and databases.
  • Network communication for RBS requires ports 1280012800 and 801801.
  • For Live Mount operations, the SMB port 445445 must be open.

Failover Clusters and Availability Groups (AG)

  • Protection of SQL Server clusters or Availability Groups requires the Rubrik Backup Service to be installed on every individual member of the cluster or AG.
  • It is optimal to have RBS configured on these members before they are added to the Rubrik system.
  • In a Failover Cluster, RBS detects failovers and releases input to the cluster, ensuring the backup is performed on the active node.
  • For Availability Groups, Rubrik conducts snapshots from members based on the backup preferences defined within the AG properties.

Multi-DC Availability Group Support

  • Introduced in version 9.3.19.3.1, this feature supports disaster recovery (DR) configurations involving primary and secondary replicas.
  • Primary replicas are utilized by applications and allow transaction processing.
  • Secondary replicas are synchronized with the primary via mechanisms such as transaction log shipping.
  • Failover to a secondary replica can be manual or automated if the primary becomes unavailable.
  • When replicas are added to the same Rubrik cluster, they are recognized as part of one Availability Group and represented as a single object.
  • If replicas are across different Rubrik clusters, they are traditionally managed as independent objects requiring separate SLA assignments.
  • Multi-DC support allows protection management for replicas across different data centers using a single SLA.
  • The backup runs on one replica based on preferences and is replicated to other clusters; in a failover scenario, the backup runs on the other cluster seamlessly, and the replication direction reverses.
  • This centralized management provides storage savings through incremental backups.
  • Multi-DC settings are configurable in CDM 9.3.19.3.1 (initially supporting two CDM clusters) via the "link the selected availability group database" toggle.

Database Protection and Transaction Log Settings

  • SLA Domains can be applied at multiple levels: Failover Cluster, Availability Group, host instance, or individual database level.
  • Specific settings include the Log Backup toggle, which allows users to specify frequency and retention for transaction logs.
  • If log backup is not configured in the SLA domain, the settings will not be effective until a value is specified.
  • Copy-only backups are supported; these do not truncate logs and do not interfere with existing log chains.
  • Rubrik explicitly does not perform shrinking of any SQL transaction logs.
  • Host Log Retention allows logs to be retained on the Windows host; however, setting this value too high can adversely affect host memory.
  • Global Host Log Retention is a default maximum value for the cluster; Rubrik Security Cloud (RSC) retains logs based on the combined total of global and specific SLA retention.

Troubleshooting Service Permissions

  • If a database cannot be protected due to insufficient permissions after addition, the issue typically resides in the service account settings within Rubrik Backup Service Properties.
  • The service account may be incorrect or lack necessary permissions within the SQL Server.
  • Diagnostic information is located in the Rubrik Backup Service logs on the Windows OS, typically stored in ProgramData (configurable via registry entries).
  • Once permissions are corrected, Rubrik detects the changes during the next automatic or manual host refresh.

Incremental Forever and Change Block Tracking (CBT)

  • Rubrik utilizes an incremental forever model. After the initial full snapshot, only changed blocks are copied.
  • Change Block Tracking (CBT) uses a file system mini-filter driver and an in-memory sparse bitmap to record changes.
  • The default block size for CBT is 64 kilobytes64\,\text{kilobytes}.
  • CBT avoids the need for disk-based scans, significantly speeding up the backup process.
  • CBT is initialized by default on databases that are 50 GB50\,\text{GB} or larger.
  • In CDM 9.4.09.4.0 and 9.4.19.4.1, the CBT driver supports Windows Server 2012 R22012\,R2 and 20162016.
  • If CBT is reset or disabled, the system falls back to "fingerprint calculation," which is a disk-based scan mechanism for incremental forever.
  • CBT can be enabled or disabled at either the cluster level or the individual host level.

Batch and High Scale Snapshots

  • Batch Snapshots use VSS (Volume Shadow Copy Service) to quiesce and capture multiple databases simultaneously.
  • Quiescing involves temporarily pausing the application to ensure application-consistent backups.
  • Batching limits: A maximum of 1616 databases per group and a maximum cumulative size of 1 TB1\,\text{TB}.
  • High Scale SQL Snapshots are designed for environments with thousands of databases.
  • This feature uses both VSS and VDI (Virtual Device Interface) to limit the number of VSS commands, improving performance.
  • High Scale Snapshots are enabled by Rubrik support and can be configured globally or per host.

Transaction Log Backup Methods

  • Microsoft Virtual Device Interface (VDI) Method: Requires the service account to have sysadmin permissions. Logs are backed up directly to Rubrik with no temporary storage required on the host.
  • Temporary Location Method: Used when the service account lacks sysadmin permissions. Logs are copied to a temporary local path before being moved to the Rubrik cluster.
  • Users can opt to change the default log backup location via SQL Server Management Studio (SSMS) and then enable the "opt-in" toggle in RSC to use that specific path.

Virtual Machine Best Practices

  • To avoid SQL queries during VM-level backups, virtual machines can be configured as crash-consistent.
  • Users can exclude specific VMDKs from being backed up to stop Rubrik from ingesting that specific data.
  • For total exclusion from VMware snapshots, the disk should be set to "Independent Disk Mode," which requires a VM reboot.

Rubrik Log Shipping

  • Log shipping involves a primary database and a target (secondary) that stays current by receiving transaction logs from the primary.
  • Log shipping provides redundancy and can be centrally managed via RSC, replacing the manual steps required in native SQL log shipping.
  • Prerequisites: The database and its logs must be protected, and both primary and target hosts must be on the same Rubrik deployment.
  • Log shipping chaining is supported, where a target acts as a new primary for a subsequent secondary.
  • Configuration options include providing an export path, setting the target state (Restoring or Standby), and options to automatically disconnect users or re-seed the configuration if the log chain breaks.

Management and Dashboard Monitoring

  • The Database Dashboard allows admins to monitor the state of databases and identify the latest recoverable point (SLA status).
  • Activities such as log archival, log backup, and log replication can be filtered and downloaded as reports.
  • Because Rubrik uses documented Microsoft SQL workflows, DBAs can see the backup history directly within SQL Server Management Studio.

Recovery Operations

  • Recovery points are visualized with camera icons (snapshots) and green lines (transaction log availability).
  • Recovery can be performed to a specific second, millisecond, or a Log Sequence Number (LSN).
  • Mount (Live Mount): Rubrik mounts storage directly to the SQL host via SMB. The database is added to SQL Server from the SMB share, and logs are replayed to reach the desired point in time.
  • Restore: Replaces the existing database with a copy from a selected point in time.
  • Export: Creates a copy of the database on a different host.
  • Download Files: Bundles the snapshot and transaction logs into a single zip file for audit or emergency use (it is not considered a standard recovery operation).
  • Download from Archive: Stages snapshots and logs from an archival location to the local cluster to speed up recovery.
  • Force Unmount: Used if a normal unmount fails. It deletes storage and metadata from the Rubrik cluster and runs the DROP DATABASE command, even if the host is unreachable.

High-Level Full Recovery Process

  1. Restore a full snapshot of the database.
  2. Add the database in a "restoring" state.
  3. Replay transaction logs to reach the specific point in time.
  4. Exit the restoring/target state to finalize the database for use.