ONTAP Discussions

Scheduling snapshots for multiple SQL instances in a Windows cluster?

gunthermontez
3,311 Views

I found an older question in this forum covering the same subject, but it was never answered, figured I'd bring it up again...

Our company's setup has 8 SQL instances running in an active-active Windows cluster with 3 nodes, the normal configuration has 1 instance on the first node, 3 instances on the second node, and 4 instances on the third node (based on the size and usage of the databases in each instance). Once I discovered that only one SnapManager operation could be active per machine, I setup a staggered backup schedule for the agent jobs on all the instances. However, there are occasions where one backup job may take longer than normal to complete and this causes another backup job on a different instance to fail because there's already an active SnapManager operation. Not to mention that if we ever have an instance not running on its preferred node, there will be even more overlaps in the backup job schedules. Trying to schedule all the jobs on all instances so that there's no overlap is not an option, the frequency of backups per instance would not be high enough, not to mention the occassional long runnning job could still cause an overlap. Each instance also has one backup job a night that also verifies the backup in a non-clustered standalone instance, I believe those jobs need to avoid overlaps as well. And, I'm not sure if this matters, but all the nodes are running Windows Server 2008 R2 Ent and all the SQL instances are SQL 2008 R2 Ent.

The way I have the backup jobs setup right now is working, but is very fragile. I'm sure there are other folks out there that have a similar setup, I'm curious as to how others have approached these issues, hopefully there's a better solution than what I've been trying.

1 ACCEPTED SOLUTION

abhisek
3,311 Views

Hi ,

We have addressed this in our new upcoming SMSQL 5.2 and added a federated backup and restore option .You can take simultaneous backup of db’s on different instances spread out on on multiple servers. The new product is tentatively planned for a release at the end of November.

Regards,

Abhishek Basu ,

Database Architect -Microsoft Solutions Engineering-Sql Server

<http://my.netapp.com/>

Email : Abhisek.Basu@netapp.com

Blog : http://communities.netapp.com/blogs/abhishek

View solution in original post

3 REPLIES 3

gunthermontez
3,311 Views

Even if it's not in a cluster, is there anyone out there that has to deal with scheduling snapshots for multiple instances on a single machine?

abhisek
3,312 Views

Hi ,

We have addressed this in our new upcoming SMSQL 5.2 and added a federated backup and restore option .You can take simultaneous backup of db’s on different instances spread out on on multiple servers. The new product is tentatively planned for a release at the end of November.

Regards,

Abhishek Basu ,

Database Architect -Microsoft Solutions Engineering-Sql Server

<http://my.netapp.com/>

Email : Abhisek.Basu@netapp.com

Blog : http://communities.netapp.com/blogs/abhishek

gunthermontez
3,311 Views

This is excellent news, this addresses the biggest pain point of using SnapManager in our environment!

Public