By: Scott Shaw | Updated: 2011-02-02 | Comments (14) | Backup
I've configured my server to use SnapManager for SQL (SMSQL) and installed SQL Server, SnapDrive, and SMSQL. How should I configure backups?
This article assumes you've followed the basic steps to setup your server to use NetApp SnapDrive and SMSQL. If you haven't yet then refer to my previous article which provides a high-level overview of things to consider before configuring backups: Considerations for Implementing SnapManager for SQL Server. If you didn't quite get it right the first time, don't fret. When you go through the steps to configure your backups SMSQL will let you know whether or not all the puzzle pieces are in place. This article will not go through every step in the process. See the Next Steps section for documentation for step-by-step instructions.
Let's start with a couple of quick reminders. SQL must be installed prior to installing SMSQL! So, if you're like my company and installing SQL and installing the storage software are handled by different teams then you'll need to coordinate these steps and makes sure SMSQL is installed after you install SQL. You'll also need to make sure that SMSQL is running under an account with both Local Administrator and SA privileges. The SMSQL documentation recommends you run SMSQL with the SQL Server service account.
Now that's out of the way we can get down to the business of configuring our backups. The first thing you'll notice is all the backups are configured through the SMSQL console. The fact that there is no tie-in with SSMS will drive a DBA crazy. Keep in mind this is a storage application - not a SQL plug-in. Because of this you will need to install the software on a central management server. If you have more than one DBA in your shop, each DBA will need to log into the server via RDP or VNC to manage backups.
The console allows you to manage multiple instances of SQL and it does a good job at listing out the last backup, last restore, and last configuration for each instance. We currently manage about 30 instances in the console and performance isn't bad though certainly not stellar. By April we'll be managing hundreds so stay tuned. Just like SSMS you will first need to register the server. Once registered you'll need to run the configuration wizard. One purpose of configuration is to make sure the datafiles are mapped to the correct LUNs. Whether or not you move the datafiles yourself or have SMSQL move them for you makes no difference - you will still need to run the Configuration Wizard before setting up backups. Here are some quick points about configuration:
- You'll want to specify a verification server. If you choose to run DBCC CHECKDB commands or snapshot verification then this selection allows you off-load the IO to a dedicated server. Trust me, you do not want DBCC CHECKDB running on the server you're trying to snap.
- We use a single SnapInfo directory. We've found this reduces complexity. You may use different directories if you have different retention, archiving, or recovery policies for databases on the same server.
- The directory for your snapshots need to be on their own LUN. Do not put any other files on this LUN. If you do than you will potentially corrupt your snapshots and the directory will need to be recreated (we had a vendor put files on our snap directory because they just assumed it was a directory to put application files)!
- SnapManager likes to do a lot of things before migrating databases; DBCC before and after migration, deleting copies of migrated databases, and UPDATE STATS. If you are not migrating any databases then simply deselect all the options. If you are migrating, I tend to still deselect and manually do the verification and deletion. It's your call. Verifications can take a long time to complete for large databases and we have separate weekly jobs that do this task.
- Click on the Advanced button to get the Advanced Notification Settings. Be sure to check mark "Only send notification when operation fails". If you don't, then you'll get an email every time a snapshot completes which, depending on your requirements, could be every half hour. Of course you can select this option if you just feel like you don't get enough email during the day.
Backup ConfigurationConfiguring backups involves another wizard. When selecting your databases keep in mind a few points:
- The databases you select to backup will be part of a single SQL job. This means if you have different scheduling requirements for different database you'll need to group these separately. You'll need to run the wizard for each group.
- Pay attention to which databases are sharing LUNs. If you have a user database on the same LUN as system databases you'll need to separate these. If you have any database sharing a LUN with another database than your restores will be streaming (the word "streaming" and "slow" are interchangeable).
Take a look at this example:
There are 3 LUNs and the C drive. Notice that the SharePointWeb database and the DBA database are on the same LUN. The SharePointWeb database is 800 GB and the DBA database is 100 MB. When I restore the SharePointWeb database snapshot SMSQL will perform a streaming restore. This takes about 3 hours. If I were to move the database to its own LUN then the restore takes about 3 minutes. Moral of the story - do not share LUNs or volumes for large databases!
When configuring the backups I would first only check the system databases master, model, and msdb (note tempdb cannot be configured). This creates a job performing a streaming backup of just the system databases. I would then configure the DBA database and the SharePointWeb databases in a separate backup job.
You can only backup databases that are on NetApp storage (notice the VDISK designation). If any of the LUNs were local than they would be grayed out and could not be backed up using SMSQL.
Things to Consider
Here is a quick and dirty brain dump of things to consider when configuring SMSQL backups. Of course everything is based on individual needs and requirements. Your RPO (Recovery Point Objective) and RTO (Recovery Time Objective) may be vastly different from one system to another. Each requirement will mean a different policy.
SMSQL only allows for a total of 255 snapshots. This can be maximized with the following configuration:
- Take hourly snapshots and retain them for 7 days.
- Take daily snapshots and retain them for 14 days
- Take weekly snapshots and retain them for 364 days
- Test your backups. Also work with your Storage Administrator to determine proper sizing for your snap storage drive. Expand the storage if necessary to accommodate your retention policies.
- Backup errors will need to sometimes be handled by the DBA team and sometimes by the Storage team. Be sure to include both teams in the error notification.
- See my previous post on considerations prior to setting up backups: http://www.mssqltips.com/sqlservertip/2220/considerations-for-implementing-snapmanager-for-sql-server/
- Here's the best practice guide for SQL Server on NetApps Storage: http://media.netapp.com/documents/tr-3821.pdf
- This is the NetApp disaster recovery considerations for SQL Server: http://media.netapp.com/documents/tr-3604.pdf
- This document outlines storage layout considerations for SQL Server: http://media.netapp.com/documents/tr-3696.pdf
Last Updated: 2011-02-02
About the author
View all my tips