Skip to main content


 

 

How can we help you?

 

Druva Documentation

User Guide - Preparing your environment for successful Druva MS-SQL Database Backup

Heads up!

We've transitioned to a new documentation portal to serve you better. Access the latest content by clicking here.

One-stop solution

This document provides a clear, step-by-step guide that focuses on how to prepare and configure backup of MS-SQL databases. Follow these instructions to effectively manage your backups using Druva MS-SQL Server Agent

Purpose

Prepare the environment for Druva MS-SQL Server Agent to have successful backup of MS-SQL databases

 ►1. What is MS-SQL server?
  • It is a powerful and popular relational database management system (RDBMS) used by many organizations for managing and storing their data
  • MS-SQL application is installed on top of the Windows operating system.
  • MS-SQL key concepts and terms
    • Transaction Logs (TLogs)
    • LSN (Log Sequence Number)
    • Log Sequence Chain
    • “Copy-only” native backup
    • Log shipping
    • DB shrinking
    • SQL Database Files
    • .mdf file (Master Data File)
    • .ldf file (Log Data File)
    • .ndf file(Secondary Data File)
 ►2. Why perform a MS-SQL server backup?
  • To protect crucial data which helps us in case of hardware failure, natural disasters, cyber-attacks, or accidental deletion.
  • Data Protection
  • Disaster Recovery
  • Point-in-Time Recovery
  • Compliance and Legal Requirements
  • Testing and Development
  • Data Migration and Upgrades
  • Database Maintenance
 ►3. What does Druva back up from MS-SQL Servers?
  • Druva supports backup and restore of MS SQL servers running on the on-premise physical and virtual infrastructure, as well as cloud infrastructure such as Amazon EC2, Azure VMs, Google Compute Engines, and so on.
  • On a system, you can have one or more MS-SQL server instances, and each instance can have multiple databases.
  • Druva lets you backup:
    • Standalone Databases
    • SQL Always On Availability Groups (AG). (Click Here)
    • Failover Cluster Instance (FCI). (Click Here).

Note:

  • Hybrid workload agent of MS-SQL needs to be installed in order to backup SQL DBs.(This will backup only SQL DB)
  • SQL DBs and flat files hosted on the operating system can not be selected for backups together.
  • MS-SQL server configuration, SQL management studio or any other dependent files or data is not backed up as a part of SQL DB backup.
 ►4. What are types of backups and workflow for MS-SQL databases?
  • For more information on the database backup workflow for different types of backups
 ►5. What are the requirements before you run an installation for Druva MS-SQL Server Agent ?
 ►6. Where should I download the for Druva MS-SQL Server Agent from?
  • You can download the Windows installer using one of the following three methods:
    • Downloads Page
    • Settings Page
      2023-06-08 01_53_24-Druva Hybrid Workloads.png
    • Management Console -> Protect ->  MS-SQL Servers -> Register New Server 
      2023-07-19 13_53_07-New _ Phoenix _ SQL _ Backup - Google Docs.png
 ►7. How do I install the Druva MS-SQL Server Agent ?
  • Stand-alone MS-SQL server: Copy the downloaded setup file (.msi / .exe) on the servers where you want to install it and follow the process mentioned. Click Here
  • AAG:
    • Install and activate the Hybrid Workloads agent on primary and all secondary nodes in the AG using the same procedure mentioned for standalone.
    • If the Hybrid Workloads agent is not installed on the primary node of the availability group, Druva does not list details of all nodes of the AG on the Management Console.
    • Enable the Readable secondary option for the secondary nodes for successful backups from that node.
    • All the nodes in an AG must have the same Hybrid Workloads agent version installed on them.
  • FCI (Failover Cluster Instance): Please follow the procedure mentioned. Click Here

Note: Once installed, verify that the service is in the running mode “Hybrid Workloads agent”. (start-run-services.msc)

 ►8. How do I activate for Druva MS-SQL Server Agent?
  • The step-by-step process is outlined in the  Activation article. (Note: In case of AAG, activate agent on primary and all secondary nodes)
  • Once activated, the server is listed on the Registered Servers page
    (Management Console - Protect - File servers - Registered Servers.)
    2023-06-08 02_02_12-Druva Hybrid Workloads-notconfi.png
  • The server is not ready for backup until you configure it.
  • The Hybrid Workloads agent discovers instances and AGs running on the MS-SQL server. After agent discovery is complete, you can see the instances and AGs in the All SQL Resources page under Protect > MS-SQL servers.
    2023-07-19 14_06_41-New _ Phoenix _ SQL _ Backup - Google Docs.png
 ►9. How do I configure sql server databases backup in Druva?
  • After activation, the registered SQL server appears on the All SQL Resources page.- Select the check box next to the resource name and  click  Create File Backup set
    2023-07-19 14_10_54-New _ Phoenix _ SQL _ Backup - Google Docs.png
  • SQL Backup Set: Define content rules and backup policy. (Note: All fields except the Storage field can be edited later. Storage once assigned cannot be changed)
  • Backup policy: How to back up (Backup Window, Schedule, Frequency, Retention, Bandwidth, Pre-post scripts)
 ►10. What are the prerequisites before you start a backup of configured MS-SQL Server?

Verify the pre-checks on the server where the SQL agent is installed.

  • Connection
    • Verify the connection between the server and Druva.
      (Use command prompt or powershell)
  • Service
    • The service “Hybrid Workloads agent” should be in Running mode.  
      (start-run-services.msc)
    • In case a service account is to be used for SQL backups ensure the ‘Log On As’ field reflects the service account instead of ‘Local System’
    • Ensure SQL Server service (MSSQLSERVER) along with all dependent services are running.
  • UI Status
    • The status of the SQL Resource on Druva console should show as “Connected”. 
      2023-07-19 20_09_36-New _ Phoenix _ SQL _ Backup - Google Docs.png
  • Anti-Virus
    • Druva executables should be added to the exclusions of Anti-virus. 
      (If not done, the anti-virus keeps the .exe on HOLD and the backup will stay in Queued state)
  • VSS Writers and Provider
    • Ensure SQL Server VSS Writer service is running or set to automatic.
    • In case you have a hardware provider, you can change it for backups.(once the setting is in place, Druva will use this provider for backups)
    • VSS writers should be stable. Verify the same in command prompt (Admin mode)
      vssadmin list writers
    • You can set VSS to write shadow copies to a separate NTFS volume, in case you want to change the snapshot volume, where data is snapshot temporary.
  • Operating System checks
    • All the drives on the FS server must have at least 10% free space. (This is a minimum requirement for the OS to run any operation smoothly).
  • MS-SQL checks
    • Ensure the SQL version and edition are listed on the support matrix.
    • Ensure required permissions are assigned to the service account being used or NT AUTHORITY/SYSTEM.
    • Ensure the recovery model is set to full for a DB for which you intend to perform Transaction log backups
    • Ensure SQL cmd is configured. Reference link.
    • Ensure DB status is online and healthy
    • Test ODBC connectivity(Reference Link)
  • FAQs - General questions about File servers 
 ►11. How do I run a backup of configured MS-SQL Server?
  • Scheduled Backup
    • Backups will run as per the backup schedule set in the backup policy.
  • Manual backup
    • To run manual backup,  use the Backup Now option.
    • Management Console -> Protect ->MS- SQL -> SQL Backup Sets -> 
    • Click on Backupset Name -> Backup Now 
      2023-07-19 20_14_54-New _ Phoenix _ SQL _ Backup - Google Docs.png
 ►12. What are commonly encountered issues with Druva MS-SQL Server Agent during backup?
 ►13. Do / Don’ts for MS-SQL database backup
  • MS-SQL server can be configured for only one organization in the Druva backup infra.
  • Any third-party or native SQL backups may cause TLog backup failure as the LSN chain might get broken if copy-only backups are not set.
  • Druva only supports databases created on NTFS volumes. 
  • Druva supports mirrored databases with an assumption that no other software including SQL Native (maintenance job) is taking backups of the mirrored databases.
  • Druva supports only active-passive FCI cluster setups.
  • Transaction log backup is not recommended if Mirroring with log shipping is configured. The log chain will break and it will impact the log backups as well as log shipping.
  • Druva does not support backups of databases having an apostrophe, comma, or any non-standard ASCII character in their names. Ensure that you exclude such databases from backups
  • Log backups are retained based on the daily retention period defined in the backup policy. If a log backup falls out of the daily retention period, it is deleted. Weekly, monthly, and yearly retention policies are not applicable for log backups.  
  • Log backups for the user database fail if it is the only database that is getting backed up. If a single-user database is not the only database, the log backup job is successfully completed with errors. 
  • Druva stops supporting versions of the Hybrid Workload client that are more than a year old from the date they were released, and no fixes will be made available. There is a lot of information about the Hybrid Workloads EOS policy
  • Any agent enhancements are always documented in the release notes.