2012-03-04 05:18 PM
We had high expectations that the new Federated backups would solve our backup problems but alas no.
We have a SQL Cluster with 8 instances hosting a total of approx 250 databases (and growing). We need to be able to back them all up with SMSQL at least every 30 minutes. This is not possible as one can not run multiple SMSQL backups concurrently. Running the backups serially exceeds the 30 minute window that the business has dictated.
We need a solution where one doesn't have to maintain scripts. We want to be able to schedule a single job that will simply backup ALL databases in ALL instances within 30 minutes (These backups are needed for our BC/DR solution). If a new DB is created or one is dropped, we don't want to have to go in and edit a job.
Currently, we have scripted generating the backup job for each instance by querying the DBs it contains. But these jobs must be run sequentially.
The new federated backup doesn't really help, we are going to have to be a bit cleverer in building the backup job ( which can be done ), but it doesn't look as though there is any concurrency so we will again exceed the 30 minutes.
2012-03-05 06:32 AM
Hi Glen ,
Federated backup in SMSQL 5.2 provides the new federation feature so that users can specify databases not only from different SQL instances on the same host, but also from remote hosts on different or the same storage controllers. Users can connect to the remote servers, select databases, add them to the backup list, and create Snapshot copies on remote servers as well as local backups at the same time.
Thank you for your comment. I beleive what you are looking for in your environment is a high availability group where databases in a single AG is backed up simultaneously even though they may be spread . This is something we have in our mind for future release.
Is it possible to set up a conference with you or through your Account or PS representative to understand your scenario better.
2012-03-05 12:18 PM
The AG idea does seem to be a better fit for what we are trying to achieve.
Given our current environment of two MS-SQL hardware clusters both backed by filers separated by 600+ miles, our latest cluster failover test (on Monday after the install of SD6.4) succeeded in bring 250+ databases online at the BC/DR site in 40 minutes. The only issue being the age of some of the databases due to the SMSQL constraint of not being able to backup multiple instances concurrently. Our solution uses MS Powershell, the Ontap module, SnapDrive and SMSQL/SnapMirror. We are pleased with the solution so far but really need to get the concurrency issue sorted to meet the business RPO.
2012-03-05 11:18 PM
Yes you are correct that SMSQL has the limitation with concurrent backup.I would like to also state that if the clustered instances run on different nodes then SMSQL should have no problems backing them up concurrently because SMSQL is not cluster aware.
However with AG also the backups will be sequential due to the limitation.