ONTAP Discussions

SnapManger for SQL

smedlytonker
3,258 Views

Background

We started the planning stages of creating a DR site about 1.5 years ago. As part of that project we need to replicate several MS SQL databases to our DR site. To accomplish this we purchased SnapManager for SQL, SnapMirror, SnapDrive, etc. Since starting that project we have switched IT personnel on the project. The new IT person(s) have come into the project cold, without much NetApp training and the benefit of conversations with NetApp engineers that helped put together a solution.

Question

The IT person in charge of trying to replicate database to the DR site it trying to accomplish this by snapping a single volume where all of the databases are located with SnapMirror. I wanted to confirm that this would not work because SnapMirror by itself would not quiesce or make the data consistent. That is why I told him he needs to use the SnapManager for SQL we purchased or else he risks corrupted database at the DR site.

Would these be accurate statements to tell the IT person  about using ‘SnapManger for SQL’ for database replication because:

1.       It quiesce the data whereas SnapManger by itself will not

2.       That replication will be faster because SnapManger only backups what is data (not really sure on this point)

3.       That is simplifies backup and restore operations

What other advantages does ‘SnapManager for SQL’ have with regard to replicating MS SQL databases?















1 ACCEPTED SOLUTION

chriskranz
3,258 Views

Yes, I can confirm that is the case.

SnapMirror works at the storage level and simply transfers all snapshots from the primary to the secondary locations.

Using SnapManager for SQL you are creating a consistent snapshot of the SQL data, and then SnapManager will trigger a SnapMirror update, which will include the consistent snapshot of the SQL data.

View solution in original post

4 REPLIES 4

chriskranz
3,258 Views

There's a lot of good technical information on the NOW site, but I'll stick to your questions.

1) SnapManager for SQL uses the Microsoft VSS providers to talk directly to SQL to quiesce it and register a proper backup within SQL. This also allows you to truncate the logs database when required.

2) The data transfer will be more efficient because the logs database will be truncated, and not necessarily because the data was properly quiesced. You also have better granularity and control of what data you are replicating as you see this from a SQL type interface.

3) Definitely simplifies the process. It also allows you to do full DBCC based verification on the databases that you backup, this process can even be done on the remote DR site on totally separate hardware (both server and filer). If you set log replication, you can have the ability to get a very low RPO by replaying logs you need. Another added advantage is being able to easily clone databases to other servers with no extra storage requirement. You are also managing and scheduling the backups from a familiar interface, you schedule and control the jobs using SQL Job Agent, and the SnapManager interface is designed to be familiar to SQL administrators. Once you have been through the wizards once or twice you'll wonder how you ever did without it!

There are a lot of other benefits, but just wanted to cover your main questions first (with a little padding). Feel free to ask further questions if you want any more clarify in any area.

Cheers!

smedlytonker
3,258 Views

Thank you very much for your replies. I was wondering If you could confirm that using SnapManager on volume with SQL data would not work because SnapManager by itself would not quiesce or make the data consistent. To create consistent SQL snap shots you would need SnapManager for SQL.

chriskranz
3,259 Views

Yes, I can confirm that is the case.

SnapMirror works at the storage level and simply transfers all snapshots from the primary to the secondary locations.

Using SnapManager for SQL you are creating a consistent snapshot of the SQL data, and then SnapManager will trigger a SnapMirror update, which will include the consistent snapshot of the SQL data.

chriskranz
3,258 Views

Just to clarify also, watch the terms you use. SnapMirror is the replication that works on the storage level, and SnapManager is the application integration that creates the consistent data snapshots. I only just noticed but in your last post you refer to SnapManager and SnapManager for SQL. I guess in the first case you actually just mean SnapMirror?

Public