All Collections
Knowledge Base
Enterprise Workloads
How To - Enterprise Workloads
User Guide - Preparing your environment for successful Druva MS-SQL Database Backup
User Guide - Preparing your environment for successful Druva MS-SQL Database Backup
Updated over a week ago

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 duringbackup?

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

Related Articles
About data protection for MS SQL servers
System requirements for MS-SQL servers
Quick steps to set up Druva to back up MS SQL Server databases
Restore checklist and considerations for MS SQL Server
User Guide - Preparing your environment for successful Druva MS-SQL Database Restore
Did this answer your question?