NetApp Technology Network: Message List - Another SMSQL question regarding "retainsnapofsnapinfo".

Re: Another SMSQL question regarding "retainsnapofsnapinfo".

community@netapp.com — Fri, 17 Jul 2009 14:49:41 GMT

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

Re: Another SMSQL question regarding "retainsnapofsnapinfo".

community@netapp.com — Fri, 17 Jul 2009 14:40:48 GMT

Full

Sorry did not re-read the thread. Wait one

Re: Another SMSQL question regarding "retainsnapofsnapinfo".

community@netapp.com — Fri, 17 Jul 2009 14:30:56 GMT

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

Re: Another SMSQL question regarding "retainsnapofsnapinfo".

community@netapp.com — Fri, 17 Jul 2009 12:22:02 GMT

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.

Re: Another SMSQL question regarding "retainsnapofsnapinfo".

community@netapp.com — Fri, 17 Jul 2009 12:16:37 GMT

Sorry forgot all about it. Wait one.

Re: Another SMSQL question regarding "retainsnapofsnapinfo".

community@netapp.com — Fri, 17 Jul 2009 11:59:40 GMT

Any further news on this Bren?

Re: Another SMSQL question regarding "retainsnapofsnapinfo".

community@netapp.com — Mon, 06 Jul 2009 21:14:31 GMT

Sorry

posted script from SQL2000 and SMSQL2.1

Will post correct version tomorrow.

Bren

Re: Another SMSQL question regarding "retainsnapofsnapinfo".

community@netapp.com — Mon, 06 Jul 2009 16:44:44 GMT

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.

Re: Another SMSQL question regarding "retainsnapofsnapinfo".

community@netapp.com — Fri, 03 Jul 2009 14:30:31 GMT

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

Re: Another SMSQL question regarding "retainsnapofsnapinfo".

community@netapp.com — Fri, 03 Jul 2009 11:08:43 GMT

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