3 Best Practices for Non-Production

When I set up a new UAT/DEV environment there are some things I tend to do:

Change the DB Owner to the SA account or equivalent

I do this in Production too, obviously but if you are regularly refreshing UAT databases, this can sometimes be overlooked.

Change Recovery Models to Simple

I don’t require Point In Time recovery for my UAT/DEV databases. I don’t want backups at all, to be perfectly honest (unless I’m specifically asked by a developer to do so) as I’ve got enough to worry about with my production backups. Space is also important to me and I’m saving space by not taking any backups.

I generally don’t have a separate Log drive my non-production environments because again, I want to save space but also performance isn’t of the utmost importance on these servers.

Change Job Owners to SA account or equivalent

This is the same as the first one really, I do this in production too but can quite often be overlooked.

As part  of our routine, I normally ask my other DBAs to check these settings as part of a UAT refresh but also as a monthly check. After painfully watching another DBA right-clicking his way to RSI Hell through SSMS, I thought i’d be kind a show them a better way of doing this via PowerShell.

Whilst we are at it, I’d also recommend shrinking those log files down after a UAT refresh and checking for orphaned users.

The following script will check through multiple servers for the above settings and produce an text file of all the databases and jobs that don’t meet these best practices.

It utilizes the get-DBADatabase and get-DBAAgentJob cmdlets from DBATools.io to do this.

You could  even amend this so that it updated the owners and  models automatically but i’ve left this as a report for  now. I have separate cmdlets that I run to change Job and  DB Owners as well as one for changing to simple models, just in case as there could be a reason why your UAT environment isn’t following best practice so it’s always best to check first and update later!

I also have a similar script where I check for my best practices across my production environment which I’ll blog about another time.

Function Test-BestPracticeUAT

<#
  .SYNOPSIS
    This function checks for some UAT best Practices across an SQL Estate. Primarliy it checks for the database owner, recovery model (should be simple)
    and also the current job owners.
    

    .EXAMPLE
    Test-BestPracticeUAT -ComputerName ServerName1, ServerName2 -NewOwner 'SA' 

    This will return a list of all databases where the owners are not SA, where the  recovery model is set to Full and also where the job owners are not set to 'SA'

    .PARAMETERS
    -Computer
    This is the server/instance name
    -NewOwner
    This is the login you wish to check is the owner of your databases and Agent Jobs. This is not mandatory and can be set to whatever you want. I default mine to 'SA'
 

#>

{

[CmdletBinding()]
  param
  (
    [Parameter(Mandatory)]
    [string[]]$ComputerName,
        [Parameter(Mandatory=$false)]
        [String]$Owner='C4rb0n'

    )
    
    Process

    {

    $obj = @()
    $obj2=@()
    $obj3=@()
    
    $logFile = "C:\Temp\UATBestPractice.txt"
        IF (!(Test-Path $logFile))
        {
        new-item $logFile
        }
        else
        {
        Clear-Content -Path $logFile
        }
    
    
    Try 
    {

    foreach ($comp in $computerName)
    {
     
    $owners = get-dbadatabase -ServerInstance $Comp | where-object {$_.owner -ne $Owner}
    $obj += $owners

    

    $model = get-dbadatabase -ServerInstance $comp -RecoveryModel full
    $obj2 += $model   


    #Test for Incorrect Job Owners

    $jobowners = get-dbaagentJob -SQLInstance $comp | where-object {$_.OwnerLoginName -ne $Owner}
    $obj3 = $jobowners
 
    }

    }


    Catch
    {
            $ErrorMessage = $_.Exception.Message
            Write-Error -Message $ErrorMessage
            $ErrorMessage| out-file $logFile -append
    }
    finally
    {

     "Databases where owner is not $Owner :"   | out-file $logFile -append
     $obj | format-table -Property Name, owner, ComputerName | out-file $logFile -append

     "Databases where recovery model is set to FULL"  | out-file $logFile -append

     $obj2 | format-table -Property Name, RecoveryModel, ComputerName | out-file $logfile -append

     "SQL Jobs where owner is not $Owner :" | out-file $logFile -append

     $obj3 | format-table -Property Name, OwnerLoginName, sqlinstance | out-file $logfile -append
    }
   } 
    
}