Subscribe

SQL iDA with SnapProtect

Hi all,

I just wanted to clarify something regarding setting up the SQLSnapProtect backups.

Details: I have Virtual SQL servers in vSphere 5.1 in SnapProtect I have configured the VSA for backing up the VM's and also installed the SQL iDA agent for constant SQL backups.

I was reading the Back to Basics: SnapProtect by Chris Blackwood and he mentioned that you only require the VSA to consistently backup Exchange and SQL if the applications is installed on VMDK's. Nothing in the SnapProtect VMware or SQL section mention just using the VSA for consistent SQL backups. If you can use the VSA how are log backups handled?

http://support.netapp.com/NOW/knowledge/docs/snapprotect/relsnap_protect100sp2b/215-07763_A0_books_online_100sp2b/books_online_1/english_us/prod_info/...

http://support.netapp.com/NOW/knowledge/docs/snapprotect/relsnap_protect100sp2b/215-07763_A0_books_online_100sp2b/books_online_1/english_us/prod_info/...

Something I did notice in the documentation is the SQL iDA mentions the need to install the Media Agent role on the SQL server....

Step 6.

Expand Database and click MSSQL Server.

Expand Storage Resources and click MediaAgent.

Expand File System and verify that VSS Provider is selected

Do you really need to have the Media Agent role installed to take consistent backups of SQL. I have configured the SQL agent to use a Proxy that is a Media Agent and also has the SQL iDA installed.

At this stage the SQL backups using the SQL iDA are failing. I have included a snippet of the logs of the failed backup if anyone can see the reason why it failed.

On the SQL server I have installed the VSS Provider and MSSQL Server iDA and configured a Proxy

Any help would be greatly appreciated

Thanks,

Mike

Log File:

162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS master].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [master] to BCD

4124  162c  07/03 14:52:54 17  CsVolumeList::Add() - Added [C:\] to volume list

4124  162c  07/03 14:52:54 17  CsSnapRequestor::IsWriterIDInSnappedWriterList() - No writers engaged in this shadow.

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponent() - Adding writer [SqlServerWriter, a65faa63-5ea8-4ebc-9dbd-a0c4db26912a] to included writer list.

4124  162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS model].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [model] to BCD

4124  162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS msdb].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [msdb] to BCD

4124  162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS CBRPMS].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [CBRPMS] to BCD

4124  162c  07/03 14:52:54 17  CsVolumeList::Add() - Added [E:\] to volume list

4124  162c  07/03 14:52:54 17  CsVolumeList::Add() - Added [F:\] to volume list

4124  162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS DARPMS].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [DARPMS] to BCD

4124  162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS EXHPMS].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [EXHPMS] to BCD

4124  162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS CHTPMS].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [CHTPMS] to BCD

4124  162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS TBSPMS].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [TBSPMS] to BCD

4124  162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS PMSNET].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [PMSNET] to BCD

4124  162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS KENPMS].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [KENPMS] to BCD

4124  162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS ADLPMS].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [ADLPMS] to BCD

4124  162c  07/03 14:52:54 17  CVSSClientShadow::SubmitMetadataSelection() - Adding writer comp [SqlServerWriter -> COLO-LEGACYPMS SBKPMS].

4124  162c  07/03 14:52:54 17  CsSnapRequestor::AddComponentToVolumeList() - Added [SBKPMS] to BCD

4124  162c  07/03 14:52:54 17  CsSynchronization::MutexSyncWait() - Mutex signaled complete after [5] seconds.

4124  162c  07/03 14:52:54 17  CsVssBackupComponents::SetBackupState() - Committing backup state: SelectComponents: [1], BootableSystemState: [0], BackupType: [1 - VSS_BT_FULL], PartialFileSuppport: [0]

4124  162c  07/03 14:52:54 17  CsSnapRequestor::Initialize() - Will gather the writer metadata

4124  162c  07/03 14:52:54 17  CsSnapRequestor::Initialize() - Shadow Provider=a4b3467a-2462-442b-8699-bbad76968c76

4124  162c  07/03 14:52:54 17  CsSnapRequestor::StartSnapshotSet() - Created shadow set 2564ff67-9326-4477-b5ac-1b5bcb4b9ba3

4124  162c  07/03 14:52:54 17  CsVolumeList::Add() - Added [C:\] to volume list

4124  162c  07/03 14:52:54 17  CsVolumeList::Add() - Added [E:\] to volume list

4124  162c  07/03 14:52:54 17  CsVolumeList::Add() - Added [F:\] to volume list

4124  162c  07/03 14:52:54 17  CVArchive::Construct() - Constructing CVArchive Object

4124  162c  07/03 14:52:54 17  CVArchive::Construct() - Enable CVArchive Big Buffer [true]

4124  162c  07/03 14:52:54 17  CVArchive::LookupPipelineInfo() - Enter LookupPipelineInfo

4124  162c  07/03 14:52:56 17  CVArchive::AllocateArchiveFileId() - Sending allocate afileId request with copy = 3 ***

4124  162c  07/03 14:52:57 17  CSQLBackup::reserveArchFileForVolume() - Archive File ID is : [22]

4124  162c  07/03 14:52:57 17  CVArchive::LookupPipelineInfo() - Enter LookupPipelineInfo

4124  162c  07/03 14:52:58 17  CVArchive::AllocateArchiveFileId() - Sending allocate afileId request with copy = 3 ***

4124  162c  07/03 14:52:59 17  CSQLBackup::reserveArchFileForVolume() - Archive File ID is : [23]

4124  162c  07/03 14:52:59 17  CVArchive::LookupPipelineInfo() - Enter LookupPipelineInfo

4124  162c  07/03 14:53:00 17  CVArchive::AllocateArchiveFileId() - Sending allocate afileId request with copy = 3 ***

4124  162c  07/03 14:53:01 17  CSQLBackup::reserveArchFileForVolume() - Archive File ID is : [24]

4124  162c  07/03 14:53:02 17  CVSnapClientAPI::runPrePostSnapTask() - Running Pre-Post Snap Tasks.

4124  162c  07/03 14:53:02 17  CVSnapClientAPI::runPrePostSnapTask() - Params: SubClientId [14] PrePostSnap [1] SnapStatus [0] BackupLevel [1].

4124  162c  07/03 14:53:02 17  CVSnapHandlerAPI::shouldSnapCmdBeRunOnProxy() - Snap PrePost Option - [0]

4124  162c  07/03 14:53:02 17  CVSnapHandlerAPI::getDetailsForSubclientId() - Empty Command Found.

4124  162c  07/03 14:53:02 17  CVSnapClientAPI::runPrePostSnapTask() - Done Running Pre-Post Snap Tasks. Return Code - [0]

4124  162c  07/03 14:53:02 17  CsSnapRequestor::AddVolumesToSnapshotSet() - Successfully added volume [C:\] to shadow set.

4124  162c  07/03 14:53:02 17  CsSnapRequestor::AddVolumesToSnapshotSet() - Successfully added volume [E:\] to shadow set.

4124  162c  07/03 14:53:02 17  CsSnapRequestor::AddVolumesToSnapshotSet() - Successfully added volume [F:\] to shadow set.

4124  162c  07/03 14:53:02 17  CsVssAsync::WaitUntilDone() - Async status returned final code = 0x0004230a, Description = VSS_S_ASYNC_FINISHED.

4124  162c  07/03 14:53:02 17  CsSnapRequestor::GatherWriterStatus() - Gathering writer status After PrepareForBackup...

4124  162c  07/03 14:53:02 17  CsSnapRequestor::GatherWriterStatus() - 10 writers responded

4124  162c  07/03 14:53:02 17  CsSnapRequestor::CreateFolderMetadata() - Not attempting to create folder metadata

4124  162c  07/03 14:53:08 17  CsVssAsync::WaitUntilDone() - Async QueryStatus failed - Code = 0x80042306, Description = Unknown status.

4124  162c  07/03 14:53:08 17  Entering [CVSSClientShadow::AbortShadow]

4124  162c  07/03 14:53:08 17  CsSnapRequestor:Smiley Very HappyeleteSnapshot() - Call m_vss->DeleteSnapshots(m_snapshotSetId, type, bForceDelete, &lDeletedSnapshots, &NondeletedSnapshotID)  [FAILED, throwing CV exception] - Code = 0x80042308, Description = VSS_E_OBJECT_NOT_FOUND

4124  162c  07/03 14:53:08 17  CsSnapRequestor:Smiley Very HappyeleteSnapshot() - Error deleting 2564ff67-9326-4477-b5ac-1b5bcb4b9ba3 shadow set - VSS_E_OBJECT_NOT_FOUND

4124  162c  07/03 14:53:08 17  CsSnapRequestor::BackupComplete() - Call DeleteSnapshot()  [FAILED, throwing CV exception] - Code = 0x80042308, Description = VSS_E_OBJECT_NOT_FOUND

4124  162c  07/03 14:53:08 17  CsSnapRequestor::BackupComplete() - Failed to delete the snapshot set VSS_E_OBJECT_NOT_FOUND

4124  162c  07/03 14:53:08 17  CsSnapRequestor::BackupComplete() - Aborting backup!

4124  162c  07/03 14:53:08 17  CsVssBackupComponents::FreeWriterMetadata() - Free'd all writer metadata

4124  162c  07/03 14:53:08 17  CSQLBackup:Smiley Very HappyeleteVolumeSnapshots() - No internal snapshot records to act on.

4124  162c  07/03 14:53:08 17  CVSnapClientAPI::runPrePostSnapTask() - Running Pre-Post Snap Tasks.

4124  162c  07/03 14:53:08 17  CVSnapClientAPI::runPrePostSnapTask() - Params: SubClientId [14] PrePostSnap [2] SnapStatus [1] BackupLevel [1].

4124  162c  07/03 14:53:08 17  CVSnapHandlerAPI::shouldSnapCmdBeRunOnProxy() - Snap PrePost Option - [0]

4124  162c  07/03 14:53:08 17  CVSnapHandlerAPI::getDetailsForSubclientId() - Empty Command Found.

4124  162c  07/03 14:53:08 17  CVSnapClientAPI::runPrePostSnapTask() - Done Running Pre-Post Snap Tasks. Return Code - [0]

4124  162c  07/03 14:53:08 17  CSQLBackup::createSnapShot() - Error finalizing shadow for the backup.

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [SBKPMS] into backup.out

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [ADLPMS] into backup.out

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [CBRPMS] into backup.out

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [CHTPMS] into backup.out

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [DARPMS] into backup.out

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [EXHPMS] into backup.out

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [KENPMS] into backup.out

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [master] into backup.out

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [model] into backup.out

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [msdb] into backup.out

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [PMSNET] into backup.out

4124  162c  07/03 14:53:08 17  CSQLCommon::logDatabaseBackupStatus() - Successfully logged the database [TBSPMS] into backup.out

4124  162c  07/03 14:53:08 17  CSQLBackup::doDbSubclientBackup() - Pipeline is not required. Closepipeline is not called

4124  162c  07/03 14:53:08 17  CSQLBackup::startBackup() - Failedcount = [24] Successcount = [0] ConvertedDBcount = [0]

4124  162c  07/03 14:53:08 17  CSQLBackup::startBackup() - Set reference time [1372827188]

4124  162c  07/03 14:53:08 17  CSQLBackup::startBackup() - Set concatPhaseParams [-r 1372827188]

4124  162c  07/03 14:53:08 17  BKP CALLED COMPLETE (PHASE Status::FAIL), 17. Token [17:5:1]

Re: SQL iDA with SnapProtect

I'm not a hands-on expert with this (so perhaps unlikely to solve the specific problem), but I think the option to use a media agent is around what level of recovery you want.

If you do NOT use the agent, then the backup will be consistant at that point in time: so no log recovery, but a point to restore to.

If you want PIT recovery (with associated log handling), then yes, you would need to use the iDA.

Hope this helps a bit!

cheers