New Database Job – Understand What You Have

In my previous post, I expounded on my first 30 days I had at four jobs in the last four years. and how to setup your jobs box. I commented and got quoted on the fact that if it’s documented I don’t support it. So, these are methods of getting things documented, some including just having to have meetings, others running code.

One I believe in having a Central Management Server (CMS) where you can register your servers. Put them in as many groups as you desire but have core group such as Dev, Test, QA, UAT, Prod, Prod Sec, etc. The rest could be by application name if needed. I always have a set of names that are for the DBAs to use to do our work, other teams can have theirs for their work, i.e., deploying code.

If you aren’t using dbatools yet you should be. While not every shop can use to manage everything it is works every well for most tasks and that includes scanning the network for SQL Instances. Because unless you could into a well oiled machine there will be instances they don’t know about and one day someone will come knocking asking to fix it. Warn your security team before you run this.

Find-DbaInstance -DiscoveryType All | Export c:tempSQLInstances.csv

Now that you have that create a spreadsheet, us I know we are DBAs, but business folks aren’t and get them to fill in some data such as:

• Environment (Test/Dev/QA/UAT/Prod)
• Description (What is this used for?)
• Business Users (What teams used it?) Get Distribution lists of people contact.
• Developers – Get distribution lists of people to contact
• PlanB – What to do if the sever goes belly up?
• What is your SLA (Service Level Agreement)? RPO/RTO, better yet work on identifying Severvce Level Objectives.
• Data center it is located in
• This is just a started place, and you will import this data into a database at some point.

If you have Azure you will need to inventory that separately, as basic script I got from GitHub can be found here.

Now it’s time to take a look at at least the on prem servers and see what we have gotten ourselves into. The easiest way to do this is to run sp_blitz out of Brent Ozar’s First Responder Kit. To this we will create a DBATools database on each server to house the scripts and later an exceptions table and need an CMS Server to register out instances we have in and of course dbatools.

# this has been adatped from Garry Barsgley's post from here https://garrybargsley.com/2020/07/14/sp_blitz-for-all-servers/
try {
    $CMSServer = "CMSServer"
    $SQLInstances = Get-DbaRegServer -SQLInstance $CMSServer -ExcludeGroup Decom -EnableException
    $servers = @($CMSServer)
    $ToolsDB = "DBATools"
    $Query = "exec dbo.sp_blitz @CheckProcedureCache = 1, @CheckServerInfo = 1, @BringThePain = 1, @SkipChecksDatabase = '$($ToolsDB)', @SkipChecksSchema = 'dbo', @SkipChecksTable = 'BlitzChecksToSkip';"
    ForEach ($s in $SQLInstances) {
        $connection = Test-DbaConnection -SQLInstance $s.name
        
        if ($connection.ConnectSuccess -and $s.name -notin $servers) {
            $servers += $s.name
        } else {
            Write-Error "Unable to connect to SQLInstance: $($s.name)"
        } 
    }
    
    $servers | Install-DbaFirstResponderKit
    try {
        $servers | Invoke-DbaQuery -Database "master" -Query $Query -AppendServerInstance -EnableException | Write-DbaDbTableData -SqlInstance $CMSServer -Database $ToolsDB -Table 'BlitzOutput' -EnableException -AutoCreateTable
    } catch {
        Write-Error "Error running sp_blitz on SQLInstance: $($s)"
    }
    
    $Query = "IF NOT EXISTS (SELECT 1 FROM sys.columns c INNER JOIN sys.tables t on t.object_id = c.object_id WHERE c.name = 'CreatedDate')
         ALTER TABLE BlitzOutput ADD CreatedDate Date
         UPDATE dbo.BlitzOuput SET CreatedDate = GETDATE() WhHERE CreatedDate IS NULL"
    Invoke-DbaQuery -SQLInstance $CMSServer -Database $ToolsDB -Query $Query -EnableException
} catch {
    Write-Error "Error connecting to CMSserver: $($CMSServer)"
}

Then after a month of fixing the low hanging fruit found above, rerun it and see who much progress you made. I will do a follow post how to use the skip tables as they come in handy on AGs especially.

This mostly like will be enough to keep us busy for awhile, follow up blog posts will be written for dbchecks and Azure SQL Tips. Next week I’ll go into more community based scripts I used and I’m using to collect info on my servers.

Please leave comments if you have any tips of your own. Thanks.

The post New Database Job – Understand What You Have first appeared on Tracy Boggiano's Blog.