SMSQL - detailed implementation questions which don't seem to be covered in the documentation...

[tguntrip]

Hi,

IHAC who's asked four questions; I'm (sadly) not an SMSQL or SQL Server expert (by any stretch of the imagination) and I wondered whether anybod know the answers to the three questions I couldn't answer definitively from the documentation:

1.       Does SQL know about the backup being done (does the backup list inside SQL show the backups and if they were successful).

3.       How is the SQL DB quiesced ?

4.       Is there any interruption to the DB when the quiesce or snapshot happens ? (ie. Will SAP notice ? – we know the SAP app will notice even with a 1 second delay....)

If anybody knows any of these answers I'd be very grateful indeed. I'm travelling all week and can't connect up and reserach as often as I'd like...

Thanks

Tony

[radek_kubka · Accepted solution]

Hi,

According to my best knowledge:

1.       Does SQL know about the backup being done (does the backup list inside SQL show the backups and if they were successful).

It doesn't - in that sense that you cannot recover a database via SQL Management Studio pointing to NetApp snapshot. SQL tools understand one format - .bak file created by SQL tools only.

Looking from a different angle, SMSQL backup is fully integrated with the app & transaction logs get truncated (that's typical function of any backup agent / application). So from this perspective, SQL 'knows' it got backed up.

   How is the SQL DB quiesced ?

In SQL 2008 via VSS integration. In earlier versions of SQL, which aren't fully VSS-aware, the thing called SQL virtual drive (VDD if memory serves right?) is leveraged.

Is there any interruption to the DB when the quiesce or snapshot happens ? (ie. Will SAP notice ? – we know the SAP app will notice even with a 1 second delay....)

Nope - under normal circumstances it is a fraction of a second only & from DB user perspective it's business as usual all the time.

Hope it helps!

Regards,

Radek

[radek_kubka]

Nope - under normal circumstances it is a fraction of a second only & from DB user perspective it's business as usual all the time. 

One more thing.

This is all fine for taking snapshots, yet your verification process may slow down things significantly (it basically scans all database pages, i.e. read everything from disk). And sometimes it takes quite long time to complete (see some scary numbers over here: http://communities.netapp.com/message/19678#19678)

The best thing to do is to offload verification to a non-production SQL server & ideally to SnapMirror destination volumes.

Regards,
Radek

[sourav]

Good answers Radek. Just a few more points: -

1. The backup verification has no impact or relation with the "database quiescing" time. As Radek mentioned, the verification server must always be a non-production standalone server and backup verifications must be done in a controlled window outside of peak hours. The reason for this is to control the total time taken for the backup job to complete of which the "database quiescing" time is a small fraction.

2. Entries for the "FULL" and "TLOG" backups taken by SMSQL are present in the "BackupHistory" table of MSDB. However, due to the fact that the backups themselves are based on NetApp snapshots (the actual value add), SQL Server cannot read and hence restore from these backups using the standard RESTORE DATABASE command.

3. The DB quiescing is done by SQL Server itself once it recieves the request from SMSQL via the VDI interface. Hence, SMSQL itself plays no role here except sending the request which is then handled by SQL Server.

Regards,

Sourav.

[tguntrip]

Thanks Sourav - very useful, much appreciated!

Tony