Automating like an Enterprise DBA

TSQL Tuesday

The second Tuesday of the month comes to us a little early this month. That means it is time again for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

Automation

The theme as chosen by Garry Bargsley (b | t) is about automation. Specifically, Garry has provided two requirements about automation for this month. As is always, there is leeway in a post that participates in TSQL Tuesday.

One of the things that should seem very commonplace to a data professional is the effort to become a lazy DBA. A lazy DBA is not a bad thing. It just means the DBA works hard to automate the repetitive mundane tasks that may be tedious and/or time consuming. Time can always be better spent somewhere else, right?

If you are lacking in any ideas for what can be automated, here are a few TSQL Tuesday roundups from when we have talked about automation previously (yes it is a hot topic – ALWAYS!).

1. August 2010 – Beach Time – what do you automate to earn beach time?
2. February 2011 – Automation in SQL Server – Give your best tricks for making your life easier through automation.
3. January 2014 – Automation – How much of it is the same?
4. September 2015 – The Enterprise – How does one manage an enterprise of databases?
5. September 2017 – PowerShell Automation – Find something and automate it.

In the past, I have written about automation a few times. Some of my favorites are automated restores, automation in the cloud, and my poor mans automated audit.

I automate many processes and have automated loads of tasks over the years. You see, automation means I can spend more time doing other tasks that require more time, more thought/concentration, more effort, and frankly more interest. So what have I automated recently that may be different from what I have previously written? This time, I have something that may seem utterly trivial but in the end it is rather tedious and time consuming to manually check over and over and over.

PowerShell

When I automate a task, I generally will try to use the tool that seems the most appropriate for the task: windows scheduler, SQL Agent, TSQL, SSIS, VB, C#  and now I am trying to add PoSh to that list. I don’t believe there is a one size fits all automation tool. Sometimes, one has to be flexible enough to adapt other technologies into the tool-belt.

I have been working with a client to check their servers for SQL Server version, SSMS version, PoSH version and so on. All of this to try and get the appropriate updates installed on the server. Believe it or not, many of their servers were still running PoSH v2 and didn’t have any Service Packs installed for their database servers. OUCH!

Touching every single server (even if it is only 10 servers) is far too tedious and error prone. So, I spent a little time klooging together with my neanderthal level PoSH skills and found a way to retrieve various pieces of information from the servers and then store those data points in a database so I could report on the entire environment easily with TSQL. In addition, I could show change history and find approximately (at worst) when an update was installed.

Of all of the things I scripted to start tracking, the one I want to share this time can also be used to audit security on each of the database servers. I use the following script to audit the localadmins on each of the database servers in the enterprise. In order to trap each local admin on the server, I also recurse through domain groups to find all users of a group to find everybody that may have access. Here is a version of the script that is similar to what I use now.

Param (
[string]$TypeName = $null #"myserver_type"
,[string]$svrname = "" #"myserver"
)
import-module sqlserver
import-module activedirectory
$c = Get-Credential -Credential mydomainmylogin
$dtime = Get-Date -Format "yyyy-MM-dd HH:mm:ss.fff"
$ScriptProcess = "Get-AdminsV2"
$Result = [PSCustomObject] @()
IF($svrname){
$SQLServerList = [ordered]@{
    ServerName = $svrname}
}
else {
if($TypeName){
$SQLServerList = Invoke-Sqlcmd -serverinstance myserver -database "DBA" -query "select distinct ServerName From Mgmt.ServerList sl inner join [Mgmt].[ServerType] st on sl.ServerTypeID = st.TypeID where st.TypeName = '$TypeName'"
}
else{
$SQLServerList = Invoke-Sqlcmd -serverinstance myserver -database "DBA" -query "select distinct ServerName From Mgmt.ServerList sl inner join [Mgmt].[ServerType] st on sl.ServerTypeID = st.TypeID where st.TypeID <> 7;"
}
}
foreach ($server in $SQLServerList)
{
$svrname = $server.ServerName
If  (Test-Connection -ComputerName  $svrname -Count  1 -Quiet ) {
try {
$dtime = get-date
$skipchecks = New-PSSessionOption -SkipCACheck -SkipCNCheck -SkipRevocationCheck
 Try  { 
$admins = Invoke-Command -Computername $svrname -ErrorAction Stop -SessionOption $skipchecks -Credential $c -ScriptBlock {  Get-LocalGroupMember -Group "Administrators" }
<#$admins = $admins |? {$_.groupcomponent –like '*"Administrators"'}  
  
$admins |% {  
$_.partcomponent –match “.+Domain=(.+),Name=(.+)$” > $nul  
$admin = $matches[1].trim('"') + “” + $matches[2].trim('"') 
#>
}
Catch {
  $EObject = [ordered]@{
  ScriptErrorId = ''
  Computername = $svrname
  ScriptError = $_.ToString()
  ScriptProcess = $ScriptProcess
  PollDate = $dtime
  }
  $EResult = (New-Object -TypeName PSCustomObject -Property $EObject)
  # $EResult
  Write-SqlTableData -ServerInstance "myserver" -DatabaseName "DBA" -SchemaName "Monitor" -TableName "ScriptErrors" -Force -InputData $EResult
   # Continue 
  }
  #$admins
 Foreach ($admin in $admins) {
  
    $Adm = $admin.Name.ToString()
    $ObjClass = $admin.objectclass.ToString()
<#
    $admin
    $Adm
    $Admin.Name
    $Admin.ObjectClass
    $ObjClass
    #>
  If ($ObjClass -match 'Group') {
  #"Group Matched"
  $domain,$group = $adm.split('')
  #$domain
  #$group
    $members = Get-ADGroupMember -Identity "$group" -Recursive
    $grouphierarchy = $Adm
    function Get-GroupHierarchy ($group)
        {
        import-module activedirectory
        $groupMember = get-adgroupmember $group | sort-object objectClass -descending
           foreach ($member in $groupMember)
            {
            if ($member.ObjectClass -match "group")
                {
                $grouphierarchy += "^$($domain)$($member.name)"
                $grouphierarchy
                Get-GroupHierarchy $member.name}}
        }
 
    $grouphierarchy += Get-GroupHierarchy $group
    #$grouphiho.ToString()
    #$grouphierarchy.tostring()
  $Object = [ordered]@{
  AdminId = ''
  Computername = $svrname
  Admin = $Adm
  ScriptProcess = $ScriptProcess
  PollDate = $dtime
}
  $Result = (New-Object -TypeName PSCustomObject -Property $Object)
  #"Result before write to sql attempt"
  Write-SqlTableData -ServerInstance "myserver" -DatabaseName "DBA" -SchemaName "Monitor" -TableName "LocalAdmins" -Force -InputData $Result
  $Result.Clear

    foreach ($member in $members) {
 
 #$member.name
    $Object = [ordered]@{
    AdminId = ''
    Computername = $svrname
    # Admin = "$($domain)$($group)^$($domain)$($member.name)"
    Admin = "$($grouphierarchy)^$($domain)$($member.name)"
    ScriptProcess = $ScriptProcess
    PollDate = $dtime
    }
  
  $Result = (New-Object -TypeName PSCustomObject -Property $Object)
  Write-SqlTableData -ServerInstance "myserver" -DatabaseName "DBA" -SchemaName "Monitor" -TableName "LocalAdmins" -Force -InputData $Result
  $Result.Clear
  }
 }
  ELSE {
  #"else reached"
    $Object = [ordered]@{
    AdminId = ''
    Computername = $svrname
    Admin = $Adm
    ScriptProcess = $ScriptProcess
    PollDate = $dtime
    }
  
  $Result = (New-Object -TypeName PSCustomObject -Property $Object)
  Write-SqlTableData -ServerInstance "myserver" -DatabaseName "DBA" -SchemaName "Monitor" -TableName "LocalAdmins" -Force -InputData $Result
  $Result.Clear
 
 }
}
 #$Result |FT -AutoSize
}
catch{}
}
}

Could I improve on the efficiency of this script? Most definitely I believe there is room for improvement. Remember, I am very novice at my PoSH skills. Scripting issues aside, it works and basically fetches a list of servers from a database, then iterates through each of those servers to fetch the complete list of local admins on each of the servers. Then the script writes out the complete list of admins for each server back to my database so I can generate a history of changes to the admins or report on who has admin access on the server.

For anybody that has admin access to a database server, the permission path (nested group path) is recorded in hierarchical form separated by the carrot character (^). Using this script, I have been able to provide a report to domain admins to clean out various unwanted individuals from access that was not intended or necessary.

Wrapping it Up

Automation is an essential tool for every data professional. Wait, no, that’s not accurate. Automation is an essential tool in all facets of IT. Automation is a definitive method to work more efficiently and offload some of the mundane repetitive tasks that consume too much time.

Even if the task is not trivial but needs to be repeated and done so without error, the best tool is automation. Performing tasks over and over naturally leads to higher risk of error. The way to minimize that risk is to perform the task via some automation script or routine.