Five Useful SQL Server Management Practices

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4521

    Comments posted to this topic are about the item Five Useful SQL Server Management Practices

    Br. Kenneth Igiri
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • christoffersen366

    Old Hand

    Points: 373

    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

    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")

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4521

    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
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • Thom A

    SSC Guru

    Points: 98333

    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.

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4521

    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
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • ben.taylor.devops

    Mr or Mrs. 500

    Points: 502

    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: 148319

    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: 98333

    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.

  • John Mitchell-245523

    SSC Guru

    Points: 148319

    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: 98333

    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.

  • joinerg

    Grasshopper

    Points: 24

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

  • bill_twomey

    SSCrazy

    Points: 2301

    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

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

  • John Mitchell-245523

    SSC Guru

    Points: 148319

    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

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

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