April 9, 2009 at 12:27 am
Ok, here it goes...
We have a netapp shop and have cif shares set up to dump backups. When dumping sql backups to the cif shares the storage team is saying the replication for this filer is exceeding the SLA to remote location for DR. They indicate that 150GB of my backups should be snapshot from the hardware level on the server and not backed up to the filer so they can reduce space. Well in sql server snap shots while sql server services are running are no good you would have to shut the services down and take a snapshot, however we can not do this since we are 24x7.
We have 3 user databases on this filer all in full recovery mode taking full and transactional database dumps. The other piece to this is the total sum is 75GB and not 150. Well in netapp when you append a backup the blocks are apparently offset and causes the backup dump that happens every night to look like there are changes in the bitmaps. Hence the double of the size of the backups. I thought if I dump a backup using init this would resolve the issue, however during our testing my storage guy still sees it being used as double. Oracle did not have this issue and sql server does.
Any ideas why or how the hardware is seeing the changes eventhough it is being backed up once by overwriting the existing backup?
April 9, 2009 at 5:39 pm
Are you backing up to the same file every night? Perhaps you can share the code you're using?
Typically we recommend backups to a new file every night, and then removing old files after a day or two.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 9, 2009 at 5:57 pm
Netapp has a utility call Snap Manager for SQL Server. This utility uses the VDI to freeze SQL Server and creates a snapshot of the LUN. There are quite a few issues with using Snap Manager, so make sure you understand all of the criteria before setting it up.
Some of the things to know:
1) System databases must be on a separate LUN, with a separate snap info folder created. If not, all backups will be streamed, defeating the purpose of using Snap Manager.
2) Only a single instance of Snap Manager can be running at any given time. So, if you are verifying your backups (recommended), you will not be able to backup other LUN's or transaction logs until the backup has been verified. To avoid this, you would need to verify backups on another system.
3) Verification is not available on a cluster because the verification process creates a virtual LUN, mounts the LUN and attaches the database to perform the verification. This process cannot be done in a cluster.
4) Transaction Log backups are not snapped, but Snap Manager must be used to run them. If not, Snap Manager will not know anything about the files and cannot clean them up.
5) Be careful on how you setup transaction log backups. You can set it up to keep all related transaction log backups with the associated backup, or delete them - and it is not intuitive which setting is which (I don't remember the actual setting because we don't use Snap Manager any more).
There are other issues - but those are the ones I remember the most.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 14, 2009 at 4:33 pm
Steve/Jeffrey:
Thank you very much for your reply. Steve: To answer your question I am using a mainteance plan so the backup actually dumps first and then perges after so I am not sure if I have control over that. As I mentioned the only way I thought at the time to re-use the same space is to use INIT but apparently I was wrong.
Jeffrey: I will run the snap shot manager by my storage team , however I am inquiring to figure out if there is a way to reset the block and re-use the existing space so netapp does not see it as duplicated change.
ReshadIT
April 14, 2009 at 4:51 pm
Sponge (4/14/2009)
Steve/Jeffrey:Thank you very much for your reply. Steve: To answer your question I am using a mainteance plan so the backup actually dumps first and then perges after so I am not sure if I have control over that. As I mentioned the only way I thought at the time to re-use the same space is to use INIT but apparently I was wrong.
Jeffrey: I will run the snap shot manager by my storage team , however I am inquiring to figure out if there is a way to reset the block and re-use the existing space so netapp does not see it as duplicated change.
ReshadIT
I don't think you can reset anything at that level - that is how Netapp manages the storage and changes to the files. From what I understand, if you have a file on Netapp storage - say, a backup file called backup.bak - and you make changes to that file, Netapp records those changes as block changes, keeping the original blocks and storing the new information in new blocks.
This is great for versioning, but the more you reuse a single file the more space that is going to be used every time you make changes to the file.
For backups, what you could do is make sure your backups are going to new files every time. That way, the files never change and you don't get any recorded block changes. When you delete the previous backup file - the storage is reusable.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 16, 2009 at 6:00 am
May be temporal data of your databases. You can try changing the recovery mode of your backups
April 17, 2009 at 1:12 am
Jeffrey:
I am glad you said it! I just wanted to make sure others were thinking of the same conclusion. I guess it is just how the hardware manages the changes.
Thanks,
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply