0

I'm afraid I don't know much about SAN's so please forgive my lack of detail or technical terms.

As a developer I've just completed and put on an existing production system a new application but it would appear to have tipped the scales regarding the performance of the backups being taken from the SAN. As I understand it there's a mirror of the SAN being taken usually constantly at the block-level. However, there seem to be so many new writes to the disk that the SAN mirroring/backup process can no longer keep up. I believe I've narrowed this down to SQL Servers tempdb which exists on a drive that contributes the largest portion of the problem! In fact I think tempdb has be contributing the largest portion of the issues all along regardless of my application!

My question therefore is whether the tempdb should ever be mirrored or backed on the SAN and whether anyone else has gone through this sort of pain already? I'm wondering whether it's a best practise to make sure that tempdb is never mirrored on a SAN simply because any writes to it don't need to be saved. This also raises a slightly connected question - is it better to rely on SQL Servers built-in database backups tools (DB in full-recovery mode with full/differential and transaction log backups) or, as is the case with our application, SQL server is in simple recovery mode and never backed up since the SAN is mirrored and backed up?

Many thanks

Zoredache
  • 130,897
  • 41
  • 276
  • 420

3 Answers3

1

You don't need to be backing up/mirroring tempdb at all since it's recreated upon re-start of the SQL Server anyway.

On the other point, even in cases where I've had mirroring/SAN replication available I've still maintained a cycle of SQL backups as a belt and braces recovery point - but that's generally because the SAN backups have been managed by others and I like to have some bak files that I can easily work with to do test restores etc.

Chris W
  • 2,670
  • 1
  • 23
  • 32
  • Completely agree - I'm no SAN engineer but it doesn't feel safe to me to rely on block-level backups when I'd much rather use transaction-ensured SQL Server backups. Problem is I'm not in control of how the SAN is configured but of course all fingers will point to me since it's my (partly) app that's causing the issue! –  May 11 '10 at 12:26
1

All that said, iff tempdb has a lot of writes you should try to eliminate them. tempdb TRIES to not write to disc, it merly does so when it needs more pages than memory available. VERY often that is a sign of crappy SQL, like surplus DISTINCT clauses (forcing to keep all records in tempdb to eliminate doubles). Not saying always, but if you get a lot of tempdb writes, try to find out whether you really need them.

TomTom
  • 51,649
  • 7
  • 54
  • 136
  • Got to agree with TomTom here. SQL is always going to eat up SAN resources by its nature but you still need to verify that the problems being seen aren't actually caused by poor SQL/lack of performance tuning & optimisation. – Chris W May 11 '10 at 08:11
  • I agree but what I'm worried about at the moment is currently we don't have a performance problem we simply have a problem related to the backup of a database on a SAN that doesn't need to be backed up in the first place!! –  May 11 '10 at 12:22
0

tempdb should not be mirrored.

tony roth
  • 3,884
  • 18
  • 14