Windows Cluster of SQL backup problem.

[astorius]

Hi folks,



I have W3K server (SP1) 64-bit with SQL 2000 on Intel Itanium-based machine. A machine is clustered by Windows Cluster. During TSM Scheduled SQL backup the SQL was failed and the cluster begun takeover procedure that was failed. TSM SQL Backup was ended, but with status "successful" (from activity log) despite he don't backed up nothing.



Do you have any ideas, how i can get real backup status?

 

[moon-buddy]

It is a little tricky to backup SQL in a Cluster scenario.



I am assuming that SQL is on the shared drive.



What you have to do is create three backup schedules: one for each node and one for the share drive or drives. The shared drive backup MUST be defined as a resource using the Windows Cluster manager. This way, when a failover happens, the backup of SQL still works whichever node has hold of the shared resource.



Read the Admin guide section on Windows clusters. It is quite detailed.

 

[moon-buddy]

If I may add, the previous posting I made defines how should the setup be. But I forgot to answer your specific question: How to find out if the backup really happened?



When you have defined the TDP for SQL environment properly - and again, a clustered environment needs more attention - TSM logs would have been setup. These logs will tell you the status of your backup. It records mostly what has been done during the backup.



A TDP for SQL log would look something like this:



01/16/2007 20:20:29 Request : FULL BACKUP

01/16/2007 20:20:29 Database Input List : *

01/16/2007 20:20:29 Group Input List : -

01/16/2007 20:20:29 File Input List : -

01/16/2007 20:20:29 Number of Buffers : 3

01/16/2007 20:20:29 Buffer Size : 1024

01/16/2007 20:20:29 Number of SQL Buffers : 0

01/16/2007 20:20:29 SQL Buffer Size : 1024

01/16/2007 20:20:29 Number of Stripes specified : 1

01/16/2007 20:20:29 Estimate : -

01/16/2007 20:20:29 Truncate Log? : -

01/16/2007 20:20:29 Wait for Tape Mounts? : Yes

01/16/2007 20:20:29 TSM Options File : C:\Progra~1\Tivoli\TSM\TDPSql\dsm.opt

01/16/2007 20:20:29 TSM Nodename Override : -

01/16/2007 20:20:29 Sqlserver : SQL1

01/16/2007 20:20:29

01/16/2007 20:30:47 Total SQL backups selected: 30

01/16/2007 20:30:47 Total SQL backups attempted: 30

01/16/2007 20:30:47 Total SQL backups completed: 30

01/16/2007 20:30:47 Total SQL backups excluded: 0

01/16/2007 20:30:47 Total SQL backups inactivated: 0

01/16/2007 20:30:47 Throughput rate: 5,016.35 Kb/Sec

01/16/2007 20:30:47 Total bytes transferred: 3,153,774,624

01/16/2007 20:30:47 Elapsed processing time: 613.96 Secs



The above is an actual snapshot of my TDP for SQL server backup.



There is a TSM GUI interface called TDPSQL.exe. If you have confidured your cluster environment properly and have defined the right OPT files for the shared resources, the TDPSQL.exe GUI interface should open. From here, you can see all backups that had been completed whether incremental or full. This is another way of finding out if the backup ran successfully.

 

[mohsinalighaffar]

which opt files i have to configure for sql in clustered windows environment?

dsm.opt( backup/archive cliet or TDPSQL )? clusterdsm.opt?
Which one of these i have to configure for SQL in clustered windows environment to work properly!!!

 

[djchopps0013]

You will use dsm.opt for baclient backups and dsm.opt in TDPsql dir for SQL backups. Usually if its a shared drive both cluster nodes will have access to both of these files. That way when nodes fail the SQL generic service will attempt to start TSM services on 2nd cluster node. So with that being said these files could be anywhere, but still on a shared resource and could be named anything your admin wanted to call them.