Microsoft SQL Server is often one of the most critical applications in an organization, with too many uses to count. Due to its criticality, your SQL Server and its data should be thoroughly protected. Business operations rely on a core component like Microsoft SQL Server to manage databases and data. The importance of backing up this server and ensuring you have a recovery plan in place is tangible. People want consistent Availability of data. Any loss of critical application Availability can result in decreased productivity, lost sales, lost customer confidence and potentially loss of customers. Does your company have a recovery plan in place to protect its Microsoft SQL Server application Availability? Has this plan been thoroughly tested?
Microsoft SQL Server works on the backend of your critical applications, making it imperative to have a strategy set in place in case something happens to your server. Veeam specifically has tools to back up your SQL Server and restore it when needed. Veeam’s intuitive tool, Veeam Explorer for Microsoft SQL Server, is easy to use and doesn’t require you to be a database expert to quickly restore the database. This blog post aims to discuss using these tools and what Veeam can offer to help ensure your SQL Server databases are well protected and always available to your business.
The Basics
There are some things you should take note of when using Veeam to back up your Microsoft SQL Server. An important aspect and easy way to ensure your backup is consistent is to check that application-aware processing is enabled for the backup job. Application aware processing is Veeam’s proprietary technology based on Microsoft Volume Shadow Copy Service. This technology quiescences the applications running on the virtual machine to create a consistent view of data. This is done so there are no unfinished database transactions when a backup is performed. This technology creates a transactionally consistent backup of a running VM minimizing the potential for data loss.
Enabling Application Aware processing is just the first step, you must also consider how you want to handle the transaction logs. Veeam has different options available to help process the transaction logs. The options available are truncate logs, do not truncate logs, or backup logs periodically.
Figure 1: SQL Server Transaction logs Options
Figure 1 shows the Backup logs periodically option is selected in this scenario. This option supports any database restore operation offered through Veeam Backup & Replication. In this case, Veeam periodically will transfer transaction logs to the backup repository and store them with the SQL server VM backup, truncating logs on the original VM. Make sure you have set the recovery model for the required SQL Server database to full or bulk-logged.
If you decide you do not want to truncate logs, Veeam will preserve the logs. This option puts the control into the database administrator’s hands, allowing them to take care of the database logs. The other alternative is to truncate logs, this selection allows Veeam to perform a database restore to the state of the latest restore point. To read more about backing up transaction logs check out this blog post.
Data recovery
Veeam Explorer for Microsoft SQL Server delivers consistent application Availability through the different restore options it offers to you. These include the ability to restore a database to a specific point in time, restore a database to the same or different server, restore it back to its original location or export to a specified location. Other options include performing restores of multiple databases at once, the ability to perform a table-level recovery or running transaction log replay to perform quick point-in-time restores.
Figure 2: Veeam Explorer for Microsoft SQL Server
Recovery is the most important aspect of data Availability. SQL Transaction log backup allows you to back up your transaction logs on a regular basis meeting recovery point objectives (RPOs). This provides not only database recovery options, but also point-in-time database recovery. Transaction-level recovery saves you from a bad transaction such as a table drop, or a mass delete of records. This functionality allows you to do a restore to a point in time right before the bad transaction had occurred, for minimal data loss.
And it is available for FREE!
Veeam offers a variety of free products and Veeam Explorer for Microsoft SQL Server is one that is included in that bunch. If you are using Veeam Backup Free Edition already, you currently have this Explorer available to you. The free version allows you to view database information, export a database and export a database schema or data. If you’re interested in learning more about what you get with Veeam Backup Free Edition, be sure to download this HitchHikers Guide.
