Netapp without SnapManager for SQL Server

[SQLDEEBEEAY]

Hi Guys,

Our organisation is in the process of rolling out NetApp storage across our systems and the plan was to use SnapManager for SQL as a data protection/backup/restore tool on top of this.

Upon sandboxing and testing SnapManager we have decided that we do not wish to use this piece of software.

I'm wondering if there are any issues with using NetApp as a data protection tool in conjuction with standard NetApp data protection.

Ideally we want to use NetApp to snapshot the backup files (transation logs, full and differential backups) and generate these using SQL Server's inbuilt tools, such as Backup/Restores.

Is there any major issue with this?

Thanks

[lafoucrier]

Hi Darrel,

My Orgnanisation actually use the kind of method you want to implement for SQL backup.

From my point of view (admin/Architect Netapp) this solution is not really optimal in perspective of space efficiency and duration of SQL backup (by the way SQL backup is hot backup), and in efficientcy of disaster recovery plan.

Advantage is for SQL admins is that they used to work with this kind of backup (dump) and my goal will be to prove them the easier way with Snapmanager SQL.

So we will study in 2012, SQL Snapmanager implementation on our SQL virtual Infrastructure (based on Hyper-V), but i do not exlude other methods and tools based on Snapshot/Snapvault. My goal is also to facilitate restore operation, standardize backup and restore methods, and in the same time be able to directly reused SQL Backup for Disaster Recovery Plan on my teriary storage in few minutes.

My first plan is to implement Snapmanager for SQL with Snapvault and inline compression on secondary Storage.

Can you give me few words about your experience with Snapmanager SQL, and why it was rejected in your context?

Thanks in advance,

bye,

Yannick

[steve_strong]

Hello Darren,

We did have a Virtual 2003 R2 x64 Enterprise Windows server running SQL 2005 x64 Enterprise with snap drives mapped to LUNs on the NetApp, we also trialed the Snap Manager for SQL but decided to just stay with the snap drives (space is reported correctly on the NetApp). We are now migrating to a new virtual Windows server with just VMDK disks, data deduplication is enabled, this server has its own datastore of 2TB, with six thin provisioned VMDK disks ranging in size from 1TB to 5GB, deduplication is saving nearly 100GB of space, the server is running a 550GB database, I then use the NetApp virtual storage console to create a snapshot for replication using snapmirror.

Regards

Steve

[HARAMAMBARU]

Hi,

May I ask what was the reason for your decision of not using SnapManager? We are in the process of deciding if we want SnapManager to back up our SQL databases. I haven't run any tests yet, but I am going to once the admin guys install it for me. Being a SQL DBA for more than 10 years, I am quite comfortable with SQL Server backups, but don't know much about SnapManager. Because our database is relatively big I was planning to run Full backups once a week, Diff backups once a day and Log backups every half an hour. Can I do the same with SnapManager? From what I know so far it can't run differential database backups, right?

Thanks

Tatiana

[storageguy]

In my opinion as a Storage admin. If you are going to rely on snaps then SMSQL a must. It gives you the consistancy snap. Easy to restore and easy for data migration ( combined with snapmirror). We do 2 hourly full backups and 1/2 tran backups. I have seen it restore 2tb database in 15 min. That is bacause of snapshot but with SMSQL it was a consitancy snap. If uou use SQL native dump then you have take a snap at volume level. You set the SMSQL to update snapvault and snapmirror after backup completed on primary. You use snapdrive to do all the above but then you need to shut SQL to take a clean snap. SMSQL in other hand pauses SQL via VDI and take the snap ( so you have a clean snap).

[HARAMAMBARU]

What size are your full backups that you run every 2 hours? Are they about 2 TB each? The question is when SMSQL runs full database backup is it always equivalent to the size of the database or does it take full initial snap and incremental subsequent snaps?

[bsti]

We have NetApp storage as well and our core is SQL Server and we've made a similar decision.  Our main issue was with the database quiescing that any snap product introduces to the SQL environment (this is a Microsoft VSS issue in the end, NOT a NetApp issue).

In these cases, it quiesces all of the databases, serially, before taking a backup.  This causes our databases to "hold" writes for several seconds every time we take a backup.  In our testing, the "holding" starts when it quiesces  the first database on the SQL instance, then continues to hold writes until the final database on that instances is quiesced.  We have over 30 databases on some servers, and it takes 1 - 7 seconds to queisce a database.  This may not pose a problem if your database servers house more "warehouse" like data, that is only queried periodically.  However, as in our case, we're more OLTP, where our databases are serving up 1000s of transactions per second 24x7. 

We decided to house and maintain the SQL Server native Full/Differential/Log backups.  We do NOT snapshot them at this time though for the same reasons mentioned above.  Every night we do a full, with a diff every few hours, and frequent transaction log backups.  This amounts to huge amounts of change, which makes snapping those volumes unmanageable.