June 12, 2010 at 6:27 pm
Hi,
We have 3 node a/a/p setup for sql server 2005 and I'm taking the backups to the disk on both the active nodes. But what happens if the node crashes? My backups will be gone right?
So I'm planning to backup the databases to a network share (on other separate server) but I read in forums that do NOT take the backups to Network share
Please advice what is the best practice to backup the databases?
thanks
June 13, 2010 at 8:55 am
make the backup drive a share then make that share a cluster resource. Then if a node crashes the backup drive will failover as well.
You should also copy the backups off somewhere else, a network share or virtual tape.
---------------------------------------------------------------------
June 13, 2010 at 11:54 am
Fundamental backup principle - Do not store backups on the same physical drive as the data/logs. Use a separate drive. In terms of clustering, as George said, get a second drive as a clustered resource and use that.
You can script a copy to the network.
If you can't get another drive, just use a job to script a copy of the files to a network share after they are complete.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 13, 2010 at 1:18 pm
make the backup drive a share then make that share a cluster resource. Then if a node crashes the backup drive will failover as well.
You should also copy the backups off somewhere else, a network share or virtual tape.
you mean, we need to create the share on the same node & make it cluster resource?
I want to give you more details about the Cluster groups & Instances we have:
On Node1 (Active): Has one instance
SQLGroup1-->for ins1
BackupGroup1-->has one drive to keep the backups of ins1 and also created a cluster file share to keep the log shipping backups
Node2 (Active):: has 4 instances:
SQLGroup2 --> for ins2
SQLGroup3--> for ins3
SQLGroup4-->for ins4
SQLGroup5-->for ins5
BackupGroup2--> has one drive to keep the backups of all the 4 instances and also created a cluster file share to keep the Log shipping backups.
Node3 (Passive)
Please advice me what is the best practice to keep the backups for above configuration?
Is the one we have currently good backup strategy?
Do I need to create the a network share on a separate Server instead on the drive in Backupgroup on node1 & similarly on node2?
and also create a network share on separate server for Log shipping backups?
or just the configuration I have is good go?
Please advice..I'm really in a confused state and I'm NOT getting proper solution for this from many days?
Thanks
June 13, 2010 at 1:22 pm
If you can't get another drive, just use a job to script a copy of the files to a network share after they are complete
Steve,
I have a full backup job which takes backups of 30 databases of total size of 100 GB and takes 90 mins to complete. So you mean that I need to call a copy job once the full backup completes to copy the backups to a network share right?
Could you please give me sample script to copy the backups to a network share once the full backup completes?
Many thanks
June 13, 2010 at 2:56 pm
The key is for your backup drives to be a clustered resource. They should be visible from both nodes, just like the data files. However the physical drives should be separate.
For scripts, here are a couple:
http://qa.sqlservercentral.com/scripts/67173/
http://qa.sqlservercentral.com/scripts/31932/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 13, 2010 at 6:47 pm
The key is for your backup drives to be a clustered resource. They should be visible from both nodes, just like the data files. However the physical drives should be separate.
Thanks Steve,
So here my backup drive in Backupgroup1 on node1 and the backup drive in Backupgroup2 are clustered resources and that means I'm with best practices right?
or do I need to completely send the database backups to a Separate Network share on a separate server?
Please clarify me
Thanks
June 15, 2010 at 12:23 am
Could you please advice me...
thanks
June 15, 2010 at 4:17 am
passivebyz (6/13/2010)
The key is for your backup drives to be a clustered resource. They should be visible from both nodes, just like the data files. However the physical drives should be separate.
Thanks Steve,
So here my backup drive in Backupgroup1 on node1 and the backup drive in Backupgroup2 are clustered resources and that means I'm with best practices right?
you are ok, the backup drives are cluster resources so will failover to the passive node should you lose a node.
or do I need to completely send the database backups to a Separate Network share on a separate server?
its not an OR, its and AND, you should copy your backups off somewhere else AS WELL, in case of total loss of the drive,
---------------------------------------------------------------------
June 15, 2010 at 9:30 am
If they are clustered resources, then as George mentioned, they'll fail over. Is the backup drive 1 from Node 1 visible on Node2? It should be.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 29, 2010 at 3:45 pm
If they are clustered resources, then as George mentioned, they'll fail over. Is the backup drive 1 from Node 1 visible on Node2? It should be
In a 3 node a/a/p cluster, I can see the backup drive from both the active nodes but NO drives are visible from Passive node.
In a 2 node a/p cluster, No drives are visible from passive node.
So is my cluster set up correct or need any attention?
Thanks
June 29, 2010 at 3:48 pm
Its a fundamental principal of clusters that the drives are only visible to the active node so yes that sounds correct.
If you were to failover the cluster resources you would then see the drives on the other node.
---------------------------------------------------------------------
June 30, 2010 at 6:18 am
User should always use the diffrent drive to take backup and if want to take back up on network path then there will be these ways
1. share the network drive
2. generate script or programme with uses network path
by default using node if we take backup without describe the nework path the backup will store in server drivr
June 30, 2010 at 8:50 am
That's not true. You can have shared drives on the cluster that are visible to the active node only, but will failover if the cluster fails over.
Backing up to the network is problematic at times. It can fail with network traffic, and then you are left without backups. If you want to store it on the network, back up locally and copy it.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 1, 2010 at 8:45 am
thanks
but now i want to know that how can i know that the particular backup has fail
and how to make network cluster
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply