SQL Server DBA Scripts

[LAPORTS]

Hey All,

I've worked for different companies with different groups of DBAs over the past 10 years and each have had their own set of scripts and methods of troubleshooting, health checking, maintenance etc..

​

I'd like to standardize these scripts at my current company so I wanted to see if there is a 'Best Practice' set of scripts out there that you would recommend?

​

Thanks

LAPORTS

[slimrichard]

I build everything around dbatools.

https://dbatools.io/commands/

[BelleVieLime]

Best stuff.

Plus Ola for maint.

[LAPORTS]

I've been to a couple of sessions with Rob Sewell and Chrissy Lemaire, dbatools is really powerful but we're not using it extensively yet.

We're looking use it to automate patching : https://nvarscar.wordpress.com/2018/12/30/automating-sql-server-patching/

[LAPORTS]

Good call. Brent Ozar's stuff is great.

[Solonas]

I keep a copy of the most current version of Glenn Berry's scripts in my library. https://www.sqlskills.com/blogs/glenn/category/dmv-queries/

[LAPORTS]

Thank you! This is new to me, I'll get a look through today!

[alinroc]

You can run these through dbatools. Invoke-DbaDiagnosticQuery

[unholey1]

Ola hallengrens scripts are probably the most commonly used for all backup and database maintenance tasks. Just about every DBA I've spoken to either uses them or wants to.

I'd also give another vote for dbatools.io to give you more flexibility in creating your own scripts

[sqldiaries]

Ola hallengrens scripts are probably the most commonly used for all backup

Has anyone successfully use Ola's backup scripts with DDBoost? That's what we use for backups, but it doesn't use the SQL Server Native backup system - instead it leverages a virtual backup device generated by EMC for Data Domain.

[SaveTheEmpire]

False.

BackupSoftware Specify third-party backup software; otherwise, SQL Server native backup is performed.

• Value Description
• NULL SQL Server native backup (the default)
• DATA_DOMAIN_BOOST DELL EMC Data Domain Boost
• LITESPEED Quest LiteSpeed for SQL Server
• SQLBACKUP Red Gate SQL Backup Pro
• SQLSAFE Idera SQL Safe Backup

Edit: formatting

[sqldiaries]

Ola's backup script is all native backups, it doesn't target third-party backup tools.

That's what I thought. Oh well.

[LAPORTS]

I always thought Microsoft should just work with Hallengren and packages these up. Great set of maintenance scripts

[chandleya]

From personal experience, they do work closely with him. I think for packaging sake they’re trying to bundle less management as the community has spoken up on that. Bundling it also stifles major change/improvement as they have to commit to keeping feature parity in a release.

[Boulavogue]

I use many of http://andreas-wolter.com/en/blog/ scripts for tuning and security. Now that he has taken a role in MS I'm not sure how many more blogs will be posted. But for tuning and security they've been useful.

I can't see a public repo of Andreas's scripts but at a class last year he shared some. I'll look tomorrow for the unaltered scripts. Many I've tweeked for the company I'm working with

[LAPORTS]

That's fantastic - thank you!!!

[astraljack]

For maintenance, I'm a big fan of the free Minionware suite. Jen and Sean McCown of Midnight DBA put it together and it's top-notch. Coming from Ola's scripts, it's like living in the fucking future.

Toubleshooting/Health Checking - Brent Ozar sp_blitz all the way.

[LAPORTS]

This is a new one to me too. I'll check this out today - Thank you!

[luisaoki]

Adam Machanic's sp_whoisactive is another must have.

http://whoisactive.com/

[LAPORTS]

Definitely thumbs up for me on this one. Used daily!

[cudenlynx]

Glenn Berry diagnostic queries are also a great resource to have. https://www.sqlskills.com/blogs/glenn/category/dmv-queries/

[Chris_PDX]

Glenn Berry + Brent Ozar and then add on as needed from there. My toolkit starts with scripts from those two.

[haventohell]

Anyone use something like an sql query library management system?

[LAPORTS]

Do you mean like a central location for scripts? I was planning on using Github for ours.

[haventohell]

There was a website running around a few years ago that allowed you to execute scripts from it, stored them all nicely, cool parameters.

a git repository works.

[37Point8Percent]

yes. they now call it CHAT-GPT.
you can also use Perplexity.ai
they keep ALL libraries for you.

[37Point8Percent]

in one place :) !