What are the best practices for backing up and restoring Microsoft SQL databases with a Datto backup appliance?
- Datto SIRIS
- Datto ALTO
- Microsoft SQL Server
Given that SQL (in this instance) is a Microsoft-leveraged service held within Windows, the Datto solution can back up the databases with these considerations:
- If you are performing local backups of the SQL database on your production machine in addition to Datto backups of the production machine itself, you should save the local backups of the database to a volume excluded from the Datto appliance's Volume-Level Backup Control. Otherwise, the machine's incremental backups will be more significant as a result of taking backups of backups.
- Large backups remain a possibility if a backed-up partition includes a SQL server.
- Methods of restoration may be different from a typical file restore, as our file restores do not preserve permissions.
- Configure SQL backups separately from volume-based image backups, to allow for the most prudent database-level restoration methods.
- Alternatively, if you need to restore a database from your existing backups, we can leverage the virtualization capabilities of the Datto device to take SQL backups off of the running VM.
If the database is on an isolated partition that does not hold any other crucial files, you can exclude the partition from a backup, then back up the database onto a NAS share hosted by the Datto device.
- Ensure the SQL writer is correctly configured to take SQL backups.
- The configuration of these backups is beyond the scope of Datto Technical Support.
The following sections are from the Microsoft document Backup Overview (SQL Server) (external link) and describe various types of backups the SQL Server can take. All links in these sections are external:
- You can create and assign particular backup jobs given the space limitations of the Datto device.
- Once the agent establishes the jobs, you can direct them to a Datto NAS share.
- You can establish a restore through a network share depending on the selected recovery model.
If a SQL Server goes down in a scenario where a system state backup is needed to leverage Microsoft recovery tools, the next step is to create a virtual machine of the protected server.
- Once you have created the VM, set it up as a Firewalled with a Private Subnet.
- To prevent live communication on the network, you may need to get the machine behind a KVM NAT.
- Datto Technical Support is happy to assist you in bringing the machine online in a state that the Microsoft SQL backup system can perform a SQL backup.
- The same practice of directing the backup to a NAS share should be employed.
- After the restore's completion, you can stop and dismount the VM.
SQL clustering does not affect how the agent operates. The protected machine presents volumes to our driver as one logical volume, upon which we only deal in file system blocks. The physical storage, whether it be any means of clustering, shared storage, RAID, etc., is abstract and unknown to the driver.
The SQL Writer handles all operations between the Volume Shadow Copy Service and the agent, including all interactions with the cluster. An application-aware backup should not impact the cluster or related processes such as log truncation.
As a best practice, back up the passive machines that are part of the cluster to prevent performance or service disruptions of the active one.