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
  • Full backup (Click Here)
  • Differential backup (Click Here)
  • Transaction Log backup (Click Here)

 ►5. What are the requirements before you run an installation for Druva MS-SQL Server Agent ?

• Installation: Prerequisites for Hybrid Workloads Agent installation
• Configuration: Prerequisites for MS-SQL database backup configuration

 ►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
    
  • Management Console -> Protect ->  MS-SQL Servers -> Register New Server 
    

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

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

 ►12. What are commonly encountered issues with Druva MS-SQL Server Agent during backup?

• Database discovery fails on SQL server and server doesn’t get registered under “All SQL Resources” in the Phoenix console
• SQL Transaction Log backup succeeded with errors
• SQL Backup fails with SQL3
• Troubleshoot common SQL server issues

 ►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 or a comma 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.