I recently wrote an article about the advantages of centralized data protection titled Delegating Backup Operations for SQL Server on my @GotDisk blog. In this article I'm going to show an example of one of the points I made in that article:
- DBAs need a way to execute on demand backup and restores outside of centrally scheduled jobs.
I'm going to show you screen shots with comments describing the use of the Networker Module for Microsoft SQL Server Management Studio (SSMS) Plug-in that shipped with the Networker 8.2.1 release. There is complete documentation available for this software on support.emc.com. You will need to register but the download is free.
Background On the Environment
I have some equipment that I'm using in the Microsoft Technology Center in Silicon Valley. My long time colleague and friend @RossMistry is the director there. It is a great partnership and enjoy working with the team there. I have 2 of my Cisco C200 rack mount servers that I installed SQL 2014 w/ SP1. I haven't been adding any CUs for 2014 SP1 in the lab yet. I also have a HyperV virtual machine on a Windows 2012R2 cluster that is hosting the Networker Server and Storage Node. I'm not going to spend anytime in this article discussing the architecture or installation of the target service for Networker backup. This article is intended for SQL Server DBAs interested in what the experience is like for on demand backup and restores in Networker data protection environments. I will just add that this setup is not built for scale but will do quite nicely to demonstrate features and functionality.
The Networker client tools for working with SQL Server come in a package called the Networker Module for Microsoft (NMM). This package also contains the integration components for Exchange, SharePoint and HyperV. The installer will discover what applications are running on the host so you can select what to install. I'm not going to include all the setup screens in this article because I want to focus on the features and functionality more than being a "how to install" guide. One thing that I do want to discuss about setup is that you need to have SSMS on the SQL Server that you want to backup with the NMM add-in . The add-in will only connect to local instances for doing on demand backup and restores. If having SSMS on your managed SQL Server (production server for example) is not desired or allowed (which I agree with) then you can still do on demand backups and restores with scripting but not with the GUI. If this is a dev/test or reporting environment and the frequency of on demand operations vs the performance impact of running the GUI is acceptable then the GUI can be a good solution. I'm going to write another article soon on scripting so follow this blog so you get notified when new content gets added or check back every week or so.
Just a quick reminder, this is not a step-by-step how to install. I want to start this section where you get the option to install the Plug-in a few screens into the setup wizard:
Notice that the SQL SSMS Plug-in is not installed by default. This way if you are installing on a server without SSMS you won't get any errors when taking the default selections. If you choose to install the Plug-in they you will have to decided on a couple of related options. First this:
The wording here is a bit tricky. If you answer No to this dialog, you will still get an add-in tab for NNM with ONLY RESTORE capability. If you answer Yes, you will both BACKUP and RESTORE capability in SSMS.
The second choice you need to make is about allowing scripts to be generated from BACKUP tab in SSMS. You only get this option only if you said Yes to the enable BACKUP tab above.
I understand why the developers built in this level of control during installation. On demand backup can be very disruptive in production environments. As I mentioned in the article referenced in the first paragraph at the top, restore is typically so dependent on the situation that on demand restore capability is mandatory.
For this demo setup I answered yes to both questions so I have both on demand backup and restore from SSMS with script generation. I'm going to finish up with some screen shots from the SSMS plug-in and explain a few of the options that are unique to Networker environments.
Backups with the SSMS Plug-in
I have been working with this environment for the last couple of days so there is already some backup/restore history in the msdb database for my testing DB called DataGen.
I'm generating load with Perfmon as I described in this article from about a year ago. I'm now going to take a new full, differential, and some log backups on demand. Finally I will walk through the restore with some final screen shots.
Here is the General settings screen from the Backup tab UI:
You can connect to any instance running on the server that you are attached to. Networker will backup selected databases or all including all the system databases if you tell it to. Below is an example of the General settings with selections filled in for one user database - DataGen
I left the Backup Type drop down open so you could see that all three types of SQL backup (Full, Differential, and Transaction Log} are available with NMM. The next screen below show what advanced options are available through the GUI. Options that you select here will be added to any scripts that you generate through the GUI.
- The perform DBCC consistency check and checksum are straightforward.
- Stripes allows parallel backup streams to be used for better performance with a higher CPU requirement.
- Pool is related to the Networker server setup. In my case, I'm using the default pool for everything so I can leave this blank. In more realistic environments you may need to coordinate with the server admins to see what Pool to use here.
- Deduplication settings support backup directly to a Data Domain or Avamar backup system. I'm not going to cover those systems in this article.
- Finally, you have several options for encryption and compression.
I left all these unchecked in order to run through a complete set of backups and restores for full, differential and transaction log. I am not going to show all those steps but will now show you the UI after the completion of the full database backup below. I hastily scribbled over any potentially sensitive domain and user name data. You may also notice the references the product name before acquisition by EMC - Legato.
I ran the process three more times to get a recent differential and two transaction log backups onto the server so I could use those to capture the RESTORE process and screens.
Restores with the SSMS Plug-in
I'm going to wrap this up with a couple of more screen shots and some brief explanation of what the tool can do during database restore. Below is the General settings for the restore function. I only have one instance on this server so I will restore to same one as the source.
At the bottom of the screen shot above you can see all the backup history for this database. I going to pretend that I already tried to restore the Full backup from 09/28/2015 but there were issues and I couldn't bring the database online. So now I'm going to recover to an earlier point in time using the four backup objects from 9/27/2015 - one full, one differential and two transaction logs.
I'm going to use the timeline to show you one way to access this functionality. If the list of backups was very long and I wanted a specific point in time it would be more efficient. Here is what that UI looks like. The colored vertical bars on the timeline show when backup objects were created. There are lines for the full, differential and two log objects. I can see from this display that I want to recover to around 6:00PM on 9/27. NMM will go back from that point and find the first Full backup and start the recovery from that point. It will then pick up any differential and log backups that were taken after that full and before 6:00PM.
There are two more optional screens of setting to show. This screen below shows how to relocate files in when necessary. I will restore back to the original location (S:\MSSQL).
The next screen shows more settings to control the restore operation. I have left the Overwrite the existing database unchecked since I deleted the database that was suspect. I also have selected the RESTORE WITH RECOVERY since I don;t have any additional backup objects after 6:00PM that I want to restore. The NMM restore with recover each of the first three objects with NO RECOVERY and will then do RECOVERY after applying the last transaction log.
Here is the database back online and ready for client connections.
This has been a fairly long article and I appreciate that you've hung in this far. I just want to show you a quick script for doing the same thing that I just did with the GUI. I used the SSMS Plug-in to choose the options that I wanted and then hit the Script button in the top left part of the UI pane. This is an example of what I got:
nsrsqlrc.exe -s MyNetworkerServerl -c MySqlServer -t "09/28/2015 9:00:00" -S normal "MSSQL$S2014I01:DataGen"
I could keep this script around and if I needed to do quick Restore, I would look at the msdb backup and restore history to see what was available for recovery like this.
I see that the last object that I want to restore was created at 9/27/5:58:11 PM. I can change the date in the script that I captured above and I will get the same result as going through the GUI. There is a full set of backup and restore scripting in the Networker Module for Microsoft documentation.
- SQL Server has a robust set of tools for managing database backup and restore.
- In many enterprise environments, the cost, complexity, and chance for failed recovery of systems with multiple components indicates that a centralized data protection approach is preferred.
- EMC Networker is capable of protecting many types of IT assets including SQL Server.
- In Networker environments, the SQL DBA teams can have a robust set of tools that allow them to retain the ability to do on demand backups and restores to react to situations not covered by regularly scheduled jobs.
- The GUI tools and scripting options packaged in the Networker Module for Microsoft are sufficiently different from SQL native tools that the DBA team will need to invest in training to ensure high reliable and efficient recovery of data is feasible.
- The NMM tools parallel the SQL native capabilities so that there should be no loss of functionality after adoption.
- Request a meeting or demo from your EMC account team.
- Check-out the demos and educational videos on YouTube.
- Download documentation and white papers from EMC Support.
Thanks for reading
Phil Hummel - @GotDisk