Over the years, servers have become virtualized and databases are consolidated and now data is growing at a much faster pace. Microsoft SQL Server is an enterprise data management product that supports large and complex relational database environments using capabilities such as compression, Always On availability groups, and Resource Governor.
SQL Server offers common database maintenance capabilities. But a huge challenge exists in the area of data protection requirements such as time-consuming restore tasks, cost of recovering data (RPO), and management of multiple backup sets.
Let’s look at the case of a consolidated SQL Server environment. Suppose storage is laid out with 15 midsize databases residing on the same volume. The databases are backed up using native SQL Server commands and the retention policy is set to 7 days.
For 15 databases and 7 days of full backup, backup files in a folder would total about 105 copies. The following challenges might occur:
- The number of backup copies to be maintained
- The size of each copy
- The time it takes to restore the complete set to original instance
NetApp® SnapCenter® Plug-in for Microsoft SQL Server helps overcome these challenges. SnapCenter 4.1 is a unified, scalable data protection and management system that uses NetApp Snapshot™ and SnapRestore® technology.
This blog describes how to use SnapCenter resource groups to achieve consolidated backup. It also discusses how to use PowerShell scripts to restore all the databases of a SQL Server instance from a consolidated backup.
Using SnapCenter Resource Groups to Configure Backup Jobs
SnapCenter Plug-in for Microsoft SQL Server lets you consolidate backups to a single Snapshot copy by using the SnapCenter resource group feature. Consolidation of backup works on a per volume and per host basis. Irrespective of whether there are single or multiple instances running on a host, a single Snapshot copy is created per volume.
SnapCenter determines whether the group of databases are on the same volume and then takes a single Snapshot copy. The size of the Snapshot copy is small because it only contains pointers to the original data location at the time the Snapshot copy was created.
Figure 1 shows a single Snapshot copy created on NetApp ONTAP® storage for an instance of 15 databases residing on the same volume.
In the SnapCenter policy settings, you can choose the maximum number of databases to be backed up. The default value is 100. If the total database count is more than 100, a consolidated Snapshot copy is created for a set of 100 databases on the same volume for the same host. Then another Snapshot copy is created for the next set of 100 databases, and so on.
Using PowerShell Cmdlets to Restore Databases
Restoring databases in SnapCenter is straightforward using the SnapCenter Restore wizard.
Continuing with the example of multiple databases on the same volume of the same host, restoring databases is time consuming because of repetitive work. SnapCenter does not currently offer instance-level restore, but you can automate the process using PowerShell scripting.
- Connect to the SnapCenter Server from the SQL Server instance. Provide the account name and URL of the SnapCenter Server along with its port, as highlighted in this example:
- Get the list of databases to be restored and pass them as parameters. The database name should include the host name in the form hostname\databasename. If a named instance is present, use the format Hostname\Instancename\Databasename.
- Identify the Snapshot copy from which the databases need to be recovered. The name of the Snapshot copy can be retrieved from SnapCenter Server > Resources > Database > Backup Name.
Note: The Snapshot copy name is the same across all databases that are part of the same resource group.
- To perform the restoration, replace the Snapshot name in the highlighted section of the following code, and run the code in a loop.
The following snippet shows the complete code passing database name as parameter.
- After this code executes, check the status of the restore from SnapCenter > Monitor. In this example, multiple restore processes have initiated.
You can also check the status of the job from the following PowerShell command:
Get-smjosummaryreport -jobid <id>
The following screenshot is a summary of job status:
Alternatively, instead of passing the database names as parameters, you can list the database names in a text file and pass the text file as an input to PowerShell.
First, save the list of databases in one text file.
In this example, suppose that the text file is saved as db.txt in c:\mssql.
Now pass the text file as an input to PowerShell. Execute the following code to restore all the databases from a single Snapshot copy.
Note: System databases should be restored separately and should not be included in this loop.
Get More information
For more information about configuring backups from resource groups, refer to the document Data Protection Guide for Microsoft SQL Server.