New Database Job – Understand What You Have

black server racks on a room
This entry is part 3 of 6 in the series New Job

In my previous post, I expounded on the first 30 days I had at four jobs in the last four years. and how to set up 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 groups 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 very 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:\temp\SQLInstances.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 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 a while, follow up blog posts will be written for dbachecks 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.

Series Navigation<< Tools I Use on My Jump BoxNew Database Job – The 90 Day Plan >>

Related Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.