How to do the Disaster Recovery Test with the SnapManager for SQL Server

[ye_htut_aung]

Currently, I am using SnapManager for SQL Server to replicate (snapmirror) hourly full database backup plus transaction logs every 10 minutes from California to New York.

How do I test mount my databases in New York? Do I need SnapManger for SQL Server in New York? I am not too clear on how to bring up my databases in New York. I would like to understand the procedure and document for DR purporses.

Thank you

[rkaramchedu1]

Currently, I am using SnapManager for SQL Server to replicate (snapmirror) hourly full database backup plus transaction logs every 10 minutes from California to New York.

How do I test mount my databases in New York? Do I need SnapManger for SQL Server in New York? I am not too clear on how to bring up my databases in New York. I would like to understand the procedure and document for DR purporse

If I can re-state it, you are using SMSQL to take SQL consistent backups and then using SnapMirror to replicate those volumes over to NY - right?

On the NY storage system, you'll have those volumes in a snapmirrored state. I believe you can use SnapDrive to mount the LUNs in those volumes to a test server. If you have flexclone license, you may also think about taking a clone of that and working off of that clone volume. The sql command line can be used to disconnect and connect the databases, if you are looking to script it.

I have some scripts that I've put together that can be modified to suit your purpose, most likely. You can PM me.

[ye_htut_aung]

Hi rkaramchedu1,

Thank you so much for your message.

I tried using SnapDrive to mount the LUNs in those volumes. What I get is the full database dump from over one hour ago. But if there is a way to take advantage of transaction logs, I can recover more recent data. However, I am unable to figure out how to mount the LUNs from the most recent transaction log possible.

Is it possible to recover to the latest transaction from New York storage?

Thank you,

- Ye

ye.aung@nacm.com

[BrendonHiggins]

When we do this we break the mirror, mount the database and then use SMSQL to play the TL back into the database to bring it forward to the correct point in time.

[ye_htut_aung]

Thank you Brendon.

I am just testing the DR procedure and documenting it.

Do I need to break the mirror when I test this? Can I just mount the LUNs using SnapDrive? You are saying that after mounting the LUNs using the SnapDrive, I can use SMSQL to restore the TL?

[BrendonHiggins]

If you mount flexclones in the 2nd site you can see your data at the

time of the last backup, job done.

If you want a full test you need to brake snapmirrors and recover every

piece of data possible via the last good TL snap, as this is what would

happen if you failed over between sites.

So it depends on what you are testing.

[rkaramchedu1]

*shameless plug* - but it is in hopes that this will help someone.

The perl script I talked about earlier uses Manage ONTAP SDK, SD CLI and SQL commands to perform a refresh of the secondary SQL server from SnapMirror destination. Of course SnapDrive makes it all possible. If we need to schedule these set of tasks, then this script might be of use.

http://rajeev.name/2010/03/04/refreshing-test-dev-sql-environments-using-netapp-technologies/

[BrendonHiggins]

Hi, welcome to the community.

We have two sites the same setup as you and every 6 months our risk team makes us change the production systems from one site to the other to show that it all works.  For a simple test to show that it works we do what "rkaramchedu1" has described with flexclones.  Works great.

Hope this helps

Bren

[gtadimalla]

Is it possible to restore a db thats backed up by snap manger for sqlserver to another server that is not part of netapp ...just local disks.

Whats the best way to refresh a test env with prod env backup .The prod db is backed up by snap manger for sql and i need to restore it

to a server that only have one local hard disk .

Thank you

[ye_htut_aung]

Hi gtadimalla,

I am new to NetApp myself and figuring things out. I am in the middle of the Disaster Recovery test and I am learning several things. Once I have figured out and documented the steps, I will share with you.

It is my personal experience that NetApp Tech Support is weak in database backup and recovery support.

- Ye

[ye_htut_aung]

Please refer to this newly published document from NetApp regarding disaster recovery using NetApp for SQL Server.

http://media.netapp.com/documents/tr-3604.pdf

- Ye

[ye_htut_aung]

Yes, it is possible.

(1) Take a snapshot at the source SQL Server using SnapManager for SQL Server.

(2) Using SnapDrive, you can mount that snapshot (LUN) from step(1) in the second server.

(3) Use "sp_attach_db" command to restore the database(s).

Note: Depending on disk availability on the volume, you may have to release (unmount) those LUNs soon.

[radek_kubka]

(2) Using SnapDrive, you can mount that snapshot (LUN) from step(1) in the second server.
(3) Use "sp_attach_db" command to restore the database(s).

I understand the question was about mounting db to a server not connected to NetApp. If that's the case, then additional step is needed:

(2a) Copy via host / network share a db in question to a server with internal drives only

Regards,
Radek

[gtadimalla]

Radek and Ye thank you.