Five Useful SQL Server Management Practices

Question

Five Useful SQL Server Management Practices

Kenneth Igiri

SSCertifiable

Points: 5001
More actions
January 17, 2017 at 11:45 pm

#334287

Comments posted to this topic are about the item Five Useful SQL Server Management Practices
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

christoffersen366 Old Hand Points: 373 More actions · Answer 1

Great list and I agree with all of it except for 5. Alerts might work well enough but I've heard it has issues so I think there are smarter ways, that isn't limited to the small selection of the Operators and wouldn't require as much for so little.

Also, there's also plenty of documentation of the unreliability of sp_MSforeachdb.

andrew.whettam SSC Enthusiast Points: 194 More actions · Answer 2

Br. Kenneth Igiri - Tuesday, January 17, 2017 11:45 PM
Comments posted to this topic are about the item Five Useful SQL Server Management Practices

Thanks for this Kenneth - very useful! I have an observation on backing up to share vs local disk - there are in fact pros and cons either way. Certainly, with a local only backup, there is a risk that a local disk failure could take out both your backups and your data files if you have them both on the same disk. However, disk failure isn't the only risk and I'm quite keen on the extra speed that a local backup can give, combined with the ease and speed of restore. Here are my thoughts, for what they're worth:
!. Backup! Have a thought-out, well planned backup and restore regime - know your Recovery Point and Recovery Time Objectives, have your storage set up right, think through all the scenarios and implications
2. Perform a test a restore regularly so that you know how to do it, you know it works, you know you have enough free space, you know how long it takes etc.
3. Backup both to a local drive AND to a network share if at all possible (there are various ways to do this: 3rd party software, network copies etc.)
4. On the local server, place data, log and backup files on separate sets of drive spindles with appropriate RAID if using DAS - discuss it with your storage manager if using a SAN (but don't accept "it's a SAN so it's fine")

Kenneth Igiri SSCertifiable Points: 5001 More actions · Answer 3

andrew.whettam - Wednesday, January 18, 2017 2:50 AM
Br. Kenneth Igiri - Tuesday, January 17, 2017 11:45 PM
Comments posted to this topic are about the item Five Useful SQL Server Management Practices
Thanks for this Kenneth - very useful! I have an observation on backing up to share vs local disk - there are in fact pros and cons either way. Certainly, with a local only backup, there is a risk that a local disk failure could take out both your backups and your data files if you have them both on the same disk. However, disk failure isn't the only risk and I'm quite keen on the extra speed that a local backup can give, combined with the ease and speed of restore. Here are my thoughts, for what they're worth:
!. Backup! Have a thought-out, well planned backup and restore regime - know your Recovery Point and Recovery Time Objectives, have your storage set up right, think through all the scenarios and implications
2. Perform a test a restore regularly so that you know how to do it, you know it works, you know you have enough free space, you know how long it takes etc.
3. Backup both to a local drive AND to a network share if at all possible (there are various ways to do this: 3rd party software, network copies etc.)
4. On the local server, place data, log and backup files on separate sets of drive spindles with appropriate RAID if using DAS - discuss it with your storage manager if using a SAN (but don't accept "it's a SAN so it's fine")

Thanks Andrew. Thanks so much.

Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising

Thom A SSC Guru Points: 99127 More actions · Answer 4

Helpful article. I had thought about using a share to back up before, but this article has made me start looking into it finally (while I have the time as well!). I'm interested to see how it affects us. The network guy's eyes lit up though, it seems he doesn't enjoy backing up the files on the SQL Server to tape, they'd much more prefer it if they were somewhere else (naming their Back up server). Hopefully this'll make things a little cleaner for all of us.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Kenneth Igiri SSCertifiable Points: 5001 More actions · Answer 5

christoffersen366 - Wednesday, January 18, 2017 2:36 AM
Great list and I agree with all of it except for 5. Alerts might work well enough but I've heard it has issues so I think there are smarter ways, that isn't limited to the small selection of the Operators and wouldn't require as much for so little.
Also, there's also plenty of documentation of the unreliability of sp_MSforeachdb.

Definitely. We used Foglight for a while and we are now moving to Oracle Enterprise Manager at my shop. Howoever this was written mostly for smaller firms who may not be able to afford enterprise grade tools. I would like to learn other options though. Thanks Chris.

Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising

ben.taylor.devops Mr or Mrs. 500 Points: 502 More actions · Answer 6

A great article.

To add to the section about using Windows authentication, I find it really useful when your company runs .NET websites, as it makes the management so much easier. When you change a password, you only need to change it on a single application pool rather than however many times it's been replicated in various config files.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Thom A - Wednesday, January 18, 2017 2:56 AM
Helpful article. I had thought about using a share to back up before, but this article has made me start looking into it finally (while I have the time as well!). I'm interested to see how it affects us. The network guy's eyes lit up though, it seems he doesn't enjoy backing up the files on the SQL Server to tape, they'd much more prefer it if they were somewhere else (naming their Back up server). Hopefully this'll make things a little cleaner for all of us.

Yes, I also recommend this approach. But if you're moving to it from a model where all servers have their databases backed up locally, make sure you stagger the backups as much as possible, or it'll all end in tears when all backups kick off at the same time! That includes transaction logs as well: if, for example, you back up every 15 minutes, schedule some jobs for one minute past, some for two minutes past, and so on.

John

Thom A SSC Guru Points: 99127 More actions · Answer 8

John Mitchell-245523 - Wednesday, January 18, 2017 3:33 AM
Thom A - Wednesday, January 18, 2017 2:56 AM
Helpful article. I had thought about using a share to back up before, but this article has made me start looking into it finally (while I have the time as well!). I'm interested to see how it affects us. The network guy's eyes lit up though, it seems he doesn't enjoy backing up the files on the SQL Server to tape, they'd much more prefer it if they were somewhere else (naming their Back up server). Hopefully this'll make things a little cleaner for all of us.
Yes, I also recommend this approach. But if you're moving to it from a model where all servers have their databases backed up locally, make sure you stagger the backups as much as possible, or it'll all end in tears when all backups kick off at the same time! That includes transaction logs as well: if, for example, you back up every 15 minutes, schedule some jobs for one minute past, some for two minutes past, and so on.
John

Thanks, I'll keep this in mind. I believe that the back ups, and transaction logs, are completed one after the other already, rather than all trying at the same time, but I set the process up some years ago so best to make sure 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Thom A - Wednesday, January 18, 2017 3:51 AM
Thanks, I'll keep this in mind. I believe that the back ups, and transaction logs, are completed one after the other already, rather than all trying at the same time, but I set the process up some years ago so best to make sure 🙂

I was thinking more about staggering backup jobs on different servers, rather than databases on the same server. Of course, if you only have one server, it's not going to be a problem!

John

Thom A SSC Guru Points: 99127 More actions · Answer 10

John Mitchell-245523 - Wednesday, January 18, 2017 4:05 AM
I was thinking more about staggering backup jobs on different servers, rather than databases on the same server. Of course, if you only have one server, it's not going to be a problem!
John

Only the one SQL server at the moment (we're hoping to get a second for BI up sometime this year). All the other backups going to the backup server (File server, Domain Controllers, etc) are staggered already.

Will definitely keep it in mind to stagger them if we do get the sign off. Not sure if we will though, as we're asking for 2016 Enterprise this time (and we all know how deep your pockets need to be for that).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

joinerg Grasshopper Points: 24 More actions · Answer 11

The pictures are too small to read, and the text is too blurry when blown up.<

-Greg Joiner

bill_twomey SSCrazy Points: 2302 More actions · Answer 12

Br. Kenneth Igiri - Tuesday, January 17, 2017 11:45 PM
Comments posted to this topic are about the item Five Useful SQL Server Management Practices

The IF NOT EXISTS check in section 4 on backups seems to be a report on recent backups. You do not need all those columns in a If Exists check.
If you need a report on recent backups, you probably want to filter for full database backups. Also, the column [backup_time (secs)] returns negative values for backups started before midnight and completed the next day. Try using datediff.

select bus.database_name, bus.backup_start_date, bus.backup_finish_date,

datediff(ss, bus.backup_start_date, bus.backup_finish_date) [backup_time (secs)],

bus.backup_size,

bmf.physical_device_name

from msdb..backupset bus

join msdb..backupmediafamily bmf on bus.media_set_id=bmf.media_set_id

where bus.backup_start_date >= (getdate() - 1)

and bus.type='D'

matthew.sharkey Newbie Points: 9 More actions · Answer 13

Can you elaborate on some of the risks of backing up to the same server?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 14

matthew.sharkey - Wednesday, January 18, 2017 9:23 AM
Can you elaborate on some of the risks of backing up to the same server?

If you lose the server, you lose your backups as well. Back up your databases to a server in a different building (different town, even) - that's the safest way. That's provided your network can handle it, of course - having half your backups fail due to dropped packets, timeouts or other networking black holes turns safe back into rather unsafe. See my comment earlier about staggering backup times.

John