November 23, 2015 at 2:32 pm
Greetings,
I've been trying to find out on my own before turning here, but either I'm not asking the right question and thus my Google-fu is failing, or the answer isn't out there (which I doubt).
We back up our production SQL Servers and SSRS Instances using Symantec NetBackup 7.5/7.6, and recently while reviewing the suggested index results from a query built around sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats I found suggestions for both msdb.dbo.backupset and msdb.dbo.backupmediafamily, which I found to be a bit odd as I thought that the system databases would already be indexed appropriately.
Before doing anything about it, I wanted to dig a bit deeper and determine why indexes were being suggested, and that's when I discovered that apparently NetBackup, either intentionally or unintentionally, doesn't maintain the backup history in msdb.
Since I have no control over how the backup jobs are configured on the NetBackup server, and before asking my IT group for assistance and potentially opening a huge can of worms, I thought I would ask if anyone has run into this before and, if you have, what you did about it? Is there a setting somewhere that will make NetBackup maintain the backupset history?
November 23, 2015 at 11:37 pm
WRT the missing index DMVs, they are a good start, but they are not always the sharpest tool in the shed. For example, you will probably see indexes being suggested upon the same keys, but with different includes clauses. You might be better served by creating just one index (with all of the different INCLUDE columns included). Treat its results with a little skepticism ("trust, but verify").
WRT the missing index DMV suggesting msdb indexes, that is actually somewhat common. Many of the job tables in msdb are not really "system" tables. If curious, you can verify my statement by using a test SQL Server to attempt creating an index upon a system table (it will fail) and then attempt to do the same on a job or backup history table (it can succeed). However, adding indexes to msdb is not supported by Microsoft, which can put you in a pickle. If Microsoft needed to patch msdb, and if a user-defined index caused that patch to fail, Microsoft would have no choice but to say "that index cannot be supported - please remove it." If you do decide to create an index (I recommend you don't, unless the pain is unbe3arbale), remember to remove the index before applying a SQL Server hotfix, service pack, cumulative update, or version upgrade.
I suspect NetBackup actually is storing its backup history in msdb (hence why indexes are being suggested). I do not know whether NetBackup offers an interface to delete backup history. If it doesn't offer an interface, use sp_delete_backuphistory @oldest_date = 'oldest_date' (recommended per https://msdn.microsoft.com/en-us/library/ms188328.aspx) or investigate by running a selects on msdb.dbo.backupset and msdb.dbo.backupmediafamily. After sp_delete_backuphistory has been run I suspect the missing index suggestions will no longer be raised (after SQL Server is next restarted, whenever that may be).
November 24, 2015 at 7:27 am
I suspect NetBackup actually is storing its backup history in msdb
In our environment we use SQL Server Agent jobs to perform the SQL Server native backups to disk and then NetBackup to backup the disks. We have a few SQL Servers that use the NetBackup plugin to perform the SQL Server backup. We are not currently using the NetBackup plugin on our 2012 and 2014 instances but on one of our SQL server 2008 instances, in the msdb backupmediafamily table the physical_device_name column has a Windows file path and the NetBackup enters a unique ID. Also the device_type is 2 for backup to disk and it is 7 for the NetBackup plugin.
November 24, 2015 at 9:59 am
WilburBud (11/24/2015)
I suspect NetBackup actually is storing its backup history in msdb
In our environment we use SQL Server Agent jobs to perform the SQL Server native backups to disk and then NetBackup to backup the disks. We have a few SQL Servers that use the NetBackup plugin to perform the SQL Server backup. We are not currently using the NetBackup plugin on our 2012 and 2014 instances but on one of our SQL server 2008 instances, in the msdb backupmediafamily table the physical_device_name column has a Windows file path and the NetBackup enters a unique ID. Also the device_type is 2 for backup to disk and it is 7 for the NetBackup plugin.
My IT Organization is forcing us to use the NetBackup plugin, so the device name is a 'virtual block device' - apparently NetBackup creates it at runtime and streams the data through it and to the storage farm.
On the whole I don't actually have a problem with NetBackup - it works, and it works far better than BackupExec which we used previously. I don't like that the service account that it uses has to have sysadmin rights, however, but that's a Symantec issue.
My concern with deleting the backup history is that I don't know if I'm potentially creating an issue if/when NetBackup tries to validate backup history, and suddenly isn't able to because the history is missing from the msdb table(s).
In contrast, I back up our Oracle databases using RMAN and pointing it to a CIFS share on our Data Domain device which lets us retain full control over our backups and backup history as RMAN automatically maintains it.
November 25, 2015 at 3:54 am
NBU definitely uses msdb. Here is an nice article
http://www.sqlhammer.com/blog/discovery-symantec-netbackup/
_________________________________________________________________
"The problem with internet quotes is that you cant always depend on their accuracy" -Abraham Lincoln, 1864
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply