January 17, 2017 at 11:45 pm
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
January 18, 2017 at 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.
January 18, 2017 at 2:50 am
Br. Kenneth Igiri - Tuesday, January 17, 2017 11:45 PMComments 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")
January 18, 2017 at 2:55 am
andrew.whettam - Wednesday, January 18, 2017 2:50 AMBr. Kenneth Igiri - Tuesday, January 17, 2017 11:45 PMComments posted to this topic are about the item Five Useful SQL Server Management PracticesThanks 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
January 18, 2017 at 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.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 18, 2017 at 2:57 am
christoffersen366 - Wednesday, January 18, 2017 2:36 AMGreat 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
January 18, 2017 at 3:13 am
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.
January 18, 2017 at 3:33 am
Thom A - Wednesday, January 18, 2017 2:56 AMHelpful 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
January 18, 2017 at 3:51 am
John Mitchell-245523 - Wednesday, January 18, 2017 3:33 AMThom A - Wednesday, January 18, 2017 2:56 AMHelpful 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
January 18, 2017 at 4:05 am
Thom A - Wednesday, January 18, 2017 3:51 AMThanks, 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
January 18, 2017 at 4:18 am
John Mitchell-245523 - Wednesday, January 18, 2017 4:05 AMI 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
January 18, 2017 at 6:58 am
The pictures are too small to read, and the text is too blurry when blown up.<
-Greg Joiner
January 18, 2017 at 9:22 am
Br. Kenneth Igiri - Tuesday, January 17, 2017 11:45 PMComments 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'
January 18, 2017 at 9:23 am
Can you elaborate on some of the risks of backing up to the same server?
January 18, 2017 at 9:30 am
matthew.sharkey - Wednesday, January 18, 2017 9:23 AMCan 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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply