Another SMSQL question regarding "retainsnapofsnapinfo".

[marcconeley]

A quick question which I hope someone can help me with.

I using SMSQL & running daily FULL backups at 00:00 every day and 18x hourly tlog only backups.

I want to keep backups going back 4 days.

Therefore I have 2 separate SMSQL jobs scheduled: 1 for the FULL backups (which include Tlogs also) and 1 for the hourly tlog only backups.

The SQL job params look like this:

TLogs params:          -RetainBackupDays  4 -lgbkonly -bksif -RetainSnapofSnapInfoDays 4 -trlog  -noutm  –mgmt standard

Full backup params: -RetainBackupDays  4 -lb -bksif -RetainSnapofSnapInfoDays 4 -trlog  -noutm  –mgmt daily

Now:

- By default a backup also forces a "snapshot of the snapinfo LUN" - this means that after every backup (whether it be a tlog or full) the vol holding the snapinfo LUN is snapshot! Is there a better way to configure this? Maybe only do volume level snapinfo snapshots once per day i.e. when my full backup runs & not after every tlog backup?

My problem is that the SnapIno LUN always has plenty of space and can hold 4 days of SQL backups no problems - but the volume snapshots didn't seem to get deleted when I was using the default "-RetainSnapofSnapInfo 8" command (it wasn't keeping only the last 8 - it was keeping more than 10days worth which eventually filled my volume). When this happens SMSQL doesn't generate an error as the backup worked - only the snapshot of the vol failed. Instead I get a Filer volume warning telling me my volume is 100% full.

The only option in SMSQL is to either turn "create snapshot of snapinfo drive after backup" on or off - which means after every hourly transaction log backup it also snapshots the snapinfo drive. Is this not overkill? I have 4 days of streamed backups on the LUN, then can I simply keep 1 day of snapinfo snapshots?? Why are the snapinfo snaps so important if we have the backups available on the LUN anyway?

I've read a great post about FR and SMSQL, but I can't find much regarding this "-RetainSnapofSnapInfo" and how it should be used when performing regular SMSQL backups.

Thanks,

Marc

[BrendonHiggins]

Hi

I have my data and TLs on different LUNs.  I do a full backup 3 times a day but also snapmirror the TLs every 15 minutes.  This does not have the snapinfo issue.

Bren

[marcconeley]

Hi Bren,

Thats exactly how I want my configuration - and I only have about 10Gb combined of databases and logs - and my databases and logs are also on different LUNs & volumes: 1 LUN for databases, 1 for logs, 1 for system dbs and 1 for snapinfo.

Can I ask a few questions:

- What version of SMSQL are you using? (I am using SQL2005 and SMSQL v5).

-  How long are you keeping your backups for?

- When you perform the tlog only backup every 15mins, does it not also make a snapshot of your snapinfo LUN?

     - if not, then how often do you make a snap of your snapinfo LUN, and how long do you keep them for?

(when I look at my filer, I can see hourly snapshots of my snapinfo vol because its snapped after every tlog backup - this is why I can't lower the tlog backup frequency to 15mins - I would simply eat up even more space on my filer).

- In SnapManager, under "Backup Settings|Transaction log backup" theres an option "Create Snapshot of snapinfo drive after after backup" - this is obviously my problem. I was told not to disable this though as it could affect my ability to restore!? How have you configured this option?

- Would it be possible for you to post your SQL job command strings here so I can compare them to mine - for both your tlog & full backup jobs?

Sorry for all the questions but I really appreciate the help nailing this. All the documentation but very few real-life examples on how to configure backups etc. We've been using SMSQL for a long time now, but it was originally configured by my NetApp consultants to only run a full backup at 00:00 every day - now I have a business critical database running on it   I needed to reduce potential data loss by running more frequent backups - which is when I hit all these issues. The easy answer is to oversize my volume to keep all these snapshots - but for 10Gb of data I am already taking up more than 160Gb of space on my filer for the various LUNs/vols mentioned above.

Cheers,

Marc

[BrendonHiggins]

1). SQL2005, SMSQL5 & Snapdrive 6.02 - We also have SQL2000, SMSQL 2.1

and snapdrive 4.2.1

2). Between 2 and 4 weeks. We also have 2 years of snapvaults

3). Yes it does go into the snapinfo and take filer storage

We use the SQL Agent to pass the command

C:\Program Files\NetApp\SnapManager for SQL Server\SmSqlBi.exe -H

"DMSQV01" -D 30 -L -J -K 30 -S "DMSQV01" -C 0 -N -UN -UM

every 15 minutes

Hope it helps

Bren

[marcconeley]

Hi Bren,

Maybe I am being stupid here, but I don't understand this. On my SQL2005 server agent, SMSQL has passed the following string to our SQL agent:

tlog job: C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe new-backup  –svr 'BNK-DB02'  -d  'BNK-DB02', '10', 'BAX40Production', 'BAX40test', 'BAX40Validation', 'INVOICES55', 'master', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB', 'Servicedesk' -ver  –verInst 'BNK-DB02' -mp  –mpdir 'C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint' -RetainBackupDays  4 -lgbkonly -bksif -RetainSnapofSnapInfoDays 2 -trlog  -noutm  –mgmt standard

full job: C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe new-backup  –svr 'BNK-DB02'  -d  'BNK-DB02', '10', 'BAX40Production', 'BAX40test', 'BAX40Validation', 'INVOICES55', 'master', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB', 'Servicedesk' -ver  –verInst 'BNK-DB02' -mp  –mpdir 'C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint' -RetainBackupDays  4 -lb -bksif -RetainSnapofSnapInfoDays 2 -trlog  -noutm  –mgmt daily

Its called "SmsqlJobLauncher.exe" in my jobs. Whats this "SmSqlBi.exe" used in yours? (different version of SQL server perhaps?).

From the above you can see that I keep 4 days of backups, and 2 days of Snapinfo snaps during this "testing period" that I am going through.

I am aiming for daily full backups, with at least hourly tlog backups throughout the day - but at the moment we are simply eating up too much space on our Filer to do this (4.5Gb of logs = 70Gb vol). And I only need to keep 4 days of backups and we have fairly small data change - so I have something misconfigured somewhere.

[BrendonHiggins]

Sorry

posted script from SQL2000 and SMSQL2.1

Will post correct version tomorrow.

Bren

[marcconeley]

Any further news on this Bren?

[BrendonHiggins]

Sorry forgot all about it.  Wait one.

[BrendonHiggins]

C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe new-backup  –svr 'S1SQV01'  -d  'S1SQV01', '19', 'master', 'model', 'msdb', 'SharedServices_DB', 'SharedServices_KC_DB', 'SharedServices_KC_Search_DB', 'SharedServices_Search_DB', 'SharePoint_AdminContent', 'SharePoint_AdminContent_5', 'SharePoint_Config', 'SharePoint_KC_Config', 'WSS_Content', 'WSS_Content_Fusion', 'WSS_Content_mySites', 'WSS_KC_Content_KMCentre', 'WSS_KC_Content_mySites', 'WSS_KC_Content_SSP1', 'WSS_KC_Search_SPKC01', 'WSS_Search_SPMOS01' -ver  –verInst 'S1SQN01' -mp  –mpdir 'C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint' -RetainBackups  7 -lb -bksif -RetainSnapofSnapInfo 0 -trlog  -updmir  -verdest  –mgmt daily

From a Windows 2003 server running SQL 2005, snapdrive 6.02 and smsql 5.

[marcconeley]

Hi Bren,

Is this your tlog backup, or your full-backup?

I am a little confused as you said that you were doing 15mins tlog backups (including snapshots of snapinfo), and 2x daily full backups - but these params look like you're not doing any snaps of your snapinfo LUN when you perform the backup!?!?

-RetainBackups  7 -lb -bksif -RetainSnapofSnapInfo 0 -trlog  -updmir  -verdest  –mgmt daily

Or am I misunderstanding something here?

Marc

[BrendonHiggins]

Full

Sorry did not re-read the thread.  Wait one

[BrendonHiggins]

Sorry I only have the scripts for TL on the older SMSQL versions.  I guess this is a new issue I will also have to resolve, as we are going to upgrade some more servers next month.

Good luck and please post back when you have the answer.  As I will need it.  😉

Bren