Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Netapp snap for files, log shipping for database? Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 12:14 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
One of our top challenges is to create a disaster recovery site so we can recover from an outage in under 30 minutes. Our database is now about 2 TB but has a dependency on a file share containing over 2 TB of scanned images. A full backup with native sql is running about 3.5 hours and that will grow. We also do nightly differentials and every 15 minute log backups.
One suggestion was to institute log shipping ( or mirroring ) for the database and Netapp Snap technology for the images since our disk back end is Netapp.
Also under consideration is training DBAs to handle Netapp's Snap Manager for SQL server, but the training suggested is long and costly, requiring DBAs to learn quite a bit of storage engineer information.
The advantage of staying with "native sql" techniques for the DBAs is that we have several experienced ones and this keeps DR within their skill sets as well as within the skill set of any new ones that might be hired.
Our Systems guys are learning how to handle the storage side of Netapp and they do not want to learn anything about databases.

The other, original, proposal was for a couple of DBAs learn the Netapp approach to backups, restores etc. This leaves you spending quite a bit for a handful of DBAs to get training, and exposure if they leave the company. It seems you either "buy in" to a particular storage technology 100% for the long term, or not.



Post #1345443
Posted Wednesday, August 15, 2012 2:22 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
Instead of using Netapp and Snap - you want to implement replication across to the DR site for your image files. This way, all image files are immediately written to the DR site. You would also set it up as bi-directional replication so that any files written on the DR site are replicated across to your production site.

For the databases, the options I would pursue are SQL Server 2012 Always On clustering. If that is not available and you cannot upgrade yet, then straight database mirroring. If you have to rely on Netapp (or any SAN technology), then you should be looking at the mirroring capabilities on the SAN. With Netapp, you can setup the LUNs supporting the databases with a mirror that can be replicated to your DR site (note: you still have to copy that data across the first time - and copying a compressed backup file for database mirroring will take less time).

Netapp's (or any SAN's) snap technology is not going to be frequent enough to prevent data loss. Additionally, you have to be concerned about the timing of the database backups and the snap clone - or you could end up with the database having entries referring to image files that do not exist, or image files that do not have an entry in the database.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1345532
Posted Wednesday, August 15, 2012 2:37 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
We're currently on a sql 2005 active/passive cluster and will probably be upgrading straight to 2012 eventually ( next year ). I don't fully understand Netapp snap technology yet, but what I'm hoping management will adopt is a combination of snap for non-database files and something more inherently Sql Server ( dba world ) for the database.
The flex clone idea where you can create a copy of the production database on a dev box with no disk footprint is appealing, but due to bandwidth limitations from office ( DR location ) to server facility I'm not sure that would work now. Of course that has implications for log shipping too but IO for that should be light by comparison.
I think next year the DR mirror site will be in another state so the bandwidth for that shouldn't be an issue then.



Post #1345541
Posted Wednesday, August 15, 2012 2:43 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
My impression was that backups done via Netapp's snap manager for sql could be done very often, very fast and that for databases in full recovery, its an either or proposition -- you can't back these databases up with native sql because you'll break the netapp log chain.
One thing we haven't tested is the IO freeze time. One of our data files in in line to be split up, but currently that one is 1.1TB. You also have to make sure backups run with sql snap manager don't step on eachother -- can't have a log backup overlap a full backup etc.
Add in the possibility that the database might be migrated off of Netapp in a year or two and its quite an investment to go that route now.



Post #1345545
Posted Wednesday, August 15, 2012 5:03 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
Indianrock (8/15/2012)
My impression was that backups done via Netapp's snap manager for sql could be done very often, very fast and that for databases in full recovery, its an either or proposition -- you can't back these databases up with native sql because you'll break the netapp log chain.
One thing we haven't tested is the IO freeze time. One of our data files in in line to be split up, but currently that one is 1.1TB. You also have to make sure backups run with sql snap manager don't step on eachother -- can't have a log backup overlap a full backup etc.
Add in the possibility that the database might be migrated off of Netapp in a year or two and its quite an investment to go that route now.


I don't understand how this relates to your original question. However, you can use native backups with no problem as long as you use the COPY_ONLY parameter.

The reason Snap Manager backups are so fast, is because they are not 'backing' up the database. They are taking a snapshot of the LUN where the database files are located. This is why you cannot put the system databases on the same LUN as the data files - and why they also recommend having a separate LUN for the data file.

If you have multiple data files on a single LUN - then all of those databases are 'backed' up at the same time. This causes issues if you want to be able to restore one of the databases - because then you cannot restore the LUN to the snapshot and instead have to present the LUN as a new LUN and use native restore on the single database.

How this all relates to using the Snap backups for DR is simple. You cannot do that without copying the full database across the wire - every time. You cannot copy the snapshot - because the snapshot has to be able to read the data from the source system.



Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1345609
Posted Thursday, August 16, 2012 3:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 2,111, Visits: 3,709
Unless SnapManager's got much more complicated in the last 2 years, the training really shouldn't be that long. Or costly.
The front end was god-awful, but the technology's ace.

YMMV, but 700GB used to back up in well under a minute. As Jeffrey says - it's quick because it's not really backing up as such, just writing meta data.
The log backups are more or less just native SQL log backups, although IIRC they're not compatible with native SQL backups.

Having said all that, I'll echo Jeffrey - SnapManager's great for on-site backup/restore. Off-site DR, not so much.
SAN replication/mirroring and/or SQL's own HA solutions are much more suitable.
Post #1345773
Posted Thursday, August 16, 2012 6:51 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
What exactly did you mean by
"Having said all that, I'll echo Jeffrey - SnapManager's great for on-site backup/restore. Off-site DR, not so much. ". ?



Post #1345907
Posted Thursday, August 16, 2012 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 6,742, Visits: 14,378
for the images you may be better off with Windows Distributed File System. This is much improved under Windows 2008

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1345975
Posted Thursday, August 16, 2012 9:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 2,111, Visits: 3,709
Indianrock (8/16/2012)
What exactly did you mean by
"Having said all that, I'll echo Jeffrey - SnapManager's great for on-site backup/restore. Off-site DR, not so much. ". ?


Just that, as SnapManager for SQL doesn't create actual backup files for the data files, you can't move them to another site for DR. Another technology is required for that.
May have conflated the two issues (DR site & SQL backups) from your posts.
Post #1346037
Posted Friday, August 17, 2012 7:22 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
Well I'm trying to get enough information about the best approach to backups, restores and disaster recovery without having to take $10,000 in courses to make the decision. Also trying to get to the truth behind lots of marketing hype.
I did read that the Netapp approach makes getting copies ( "backups" "clones" whatever you want to call it ) of the database available on development servers with flex cloning. And supposedly the mirror/data files/snap remains on the Netapp with the database on the DEV sql server using almost no disk space on the DEV server. That would be helpful and save lots of time copying big sql backups around the network.

But from what's been said in this thread it sounds like for keeping a DR hot standby current we might be better off with log shipping, sql server mirroring or, once on sql 2012, availability groups. I never got the impression from Netapp reps that a DR copy couldn't be kept very current with the snap manager approach -- taking "snap" backups every 15 minutes if necessary. They did caution that you can't have two such "backups" happening at the same time -- no overlap between "log" and "database" backups.
Obviously its tough for a DBA without much storage knowledge to get his/her head around this.

I had come to the conclusion that once a database reaches a certain size, "native" sql stuff just wouldn't hack it. I will look into the Windows Distributed File System approach.
thanks
Randy



Post #1346536
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse