Just another Select of Ms SQL DBA site
There are reasons you need to resolve SID. You can do it by powershell:
|
1 2 3 |
$SID = New-Object System.Security.Principal.SecurityIdentifier("S-1-5-21-9534051626-2041855937-2114229915-98301") $objUser = $SID.Translate([System.Security.Principal.NTAccount]) $objUser.Value |
If you get the error:
Get-DbaDatabase] Failure | The certificate chain was issued by an authority that is not trusted.
You can set in dbatools:
|
1 |
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register |
or
|
1 2 3 4 5 |
# Set these defaults for all future sessions on this machine Set-DbatoolsInsecureConnection # Set defaults just for this session Set-DbatoolsInsecureConnection -SessionOnly |
Some usefull docker command in powershell windows :
Prerequisite: Visual Studio Code Remote Development Extension Pack
|
1 2 3 4 5 6 7 8 |
# 1. Build the infrastructur in github directory: docker-compose build # 2. Run the infrastructure docker-compose up # 3.When done, clean up the environement by running docker-compose down |
There is a database in AG and you need to drop it and restore from backup (which is sometimes a transition from developer to prod )
You don’t need any prerequisite for cluster nodes and AG names.
The script find the instance in local windows server and find the db AG nodes and listener.
So finally you need the database name and the location of the backup folder which can be an Ola backup solution folder.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
$db="AdventureWorks" $BackupDirectory= "\\backupserver01\mssql_bkp\SQLSERVER15\AdventureWorks" $SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" } #find AG: $AGGroup =Get-DbaAgDatabase -SqlInstance $SqlServersOnline[0] | Where-Object {$_.Name -eq $db} $SQLListener = Get-DbaAgListener -SqlInstance $SqlServersOnline[0] | Where-Object {$_.AvailabilityGroup -eq $AGGroup.AvailabilityGroup} # connect to listener to get primary and secondary $availabilityGroupReplicas = Get-DbaAgReplica -SqlInstance $SQLlistener.Name -AvailabilityGroup $AGGroup.AvailabilityGroup $PrimaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Primary').Name $SecondaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Secondary').Name $AvailabilityGroup = $AGGroup.AvailabilityGroup # remove db from AG-s Remove-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db Remove-DbaDatabase -SqlInstance $secondaryReplica -Database $db -Confirm:$false # check restore script with -OutputScriptOnly # Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace -MaintenanceSolution -OutputScriptOnly # usefull paratmeters if needed: -RestoreTime '2022-01-26 07:15:00' -WithReplace -OutputScriptOnly -MaintenanceSolution Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace -MaintenanceSolution # -OutputScriptOnly # check and set recovery model if needed if ( (Get-DbaDbRecoveryModel -SqlInstance $PrimaryReplica -Database $db).recoverymodel -eq "Simple") ` { Set-DbaDbRecoveryModel -SqlInstance $PrimaryReplica -Database $db -RecoveryModel Full ; Backup-DbaDatabase -SqlInstance $PrimaryReplica -Database $db -FilePath NUL } # add database to primary AG Add-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db -SeedingMode Automatic |
Do you need an alert for CPU, for network?
Here is an example for one network card.
You can put in a ps1 file and run it from task scheduler.
You should set the email and SMTP addresses:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
######## task scheduler ####################### # # prereq for powershel by task scheduler : # !! - Set-ExecutionPolicy Unrestricted - !! # # user: SYSTEM # # run whereher user is logged on or not # run with highest privileges # # action: start a program # program/script powershell # add arguments (optional) : -file D:\Budapest_DBA\MonitoringDiskSpaceReporting\volumespace-monitoring-smtp-mail.ps1 # Start in (optional): D:\Budapest_DBA\MonitoringDiskSpaceReporting\ ################################################ $perfvalue= (Get-Counter -counter "\\server0056\network interface(hp nc375i integrated quad port multifunction gigabit server adapter)\bytes total/sec" -MaxSamples 3 -SampleInterval 5 | select -expand CounterSamples | Measure-Object -average cookedvalue).Average Write-Output $perfvalue if( $perfvalue -ge 5000){ $body ="Automata monitoring alert email. Network Traffic is high, maybe take a look:<br> \Network Interface(*)\Bytes Total/sec value:<b> $perfvalue </b><br> Gábor" Send-MailMessage -Body $body -BodyAsHtml -From "sqlserver@company.com" -SmtpServer "smtp.company.com" -Subject "performance alert $env:computername Network Traffic" -To "dbateam@company.com" -Encoding utf8 } |
|
1 2 3 4 5 6 |
Get-Cluster Get-ClusterNode Get-ClusterGroup Get-ClusterResource Get-ClusterResource | Select-Object * | Sort-Object cluster,ownernode,name | Out-GridView Get-ClusterResource | Select-Object cluster,ownernode,name, ownergroup,resourcetype, state,characteristics | Sort-Object cluster,ownernode,name | Out-GridView |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# Invoke-DbaAgFailover dbatools powershell command to failover # Switch the all cluster group to the another node in verbose: # do NOT do in production environment: Get-ClusterGroup | Move-ClusterGroup -verbose # Switch the cluster to the particular Node: # do NOT do in production environment: # Get-ClusterGroup | Move-ClusterGroup -Node ideanodeneve # move one group example: Move-ClusterGroup "Available Storage" Move-ClusterGroup "Cluster Group" |
DBATools makes your life happy, you can find the SQL server instances and you can failover them.
To find all of the SQL instances in a cluster server . To Check AG before and after the failover, and failover the SQL Server AO AG :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# cluster:::::: # (Get-ClusterNode).name | Find-DbaInstance # find SQL server instances in the cluster nodes $SqlServers = (Get-ClusterNode).name | Find-DbaInstance # remote cluster server example: # $SqlServers = (Get-ClusterNode -cluster wvvzrz0020cl).name | Find-DbaInstance $SqlServers $SqlServersOnline = $SqlServers | Where-Object {$_.Availability -eq "Available" } $SqlServersOnline if ($SqlServers.Count -ne $SqlServersOnline.Count) { Write-Host "There are unavailable instances!!!!!" -ForegroundColor Red -BackgroundColor Yellow} # failover GUI Get-DbaAvailabilityGroup -SqlInstance $SqlServersOnline | Out-GridView -Passthru | Invoke-DbaAgFailover # -Confirm:$false -Whatif # instance ellenorzes Get-DbaAgDatabase -SqlInstance $SqlServersOnline | Out-GridView |
if you have to wait a lot for synchronization you can check how many servers are synchronized.
|
1 2 3 4 5 6 7 8 9 |
$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" } do { Write-Host; Write-Host "*********************************************************************************************";` $Databases= Get-DbaAgDatabase -SqlInstance $SqlServersOnline; Start-Sleep -Seconds 1.0 ; ` Start-Sleep -Seconds 0.5 ; ` Write-Host -NoNewline (Get-Cluster).Name "has" $Databases.Count " AG databases. " ; ` Write-Host ($Databases | Where-Object {$_.SynchronizationState -eq "Synchronized" }).count "databases are synchronized at" (Get-Date).ToString(); ` Write-Host -NoNewline "Out of Synchron:" ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }) | Select-Object Name ; ` } ` while ( ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }).count -ne 0 ); |
You can use this single command in Windows PowerShell to install PowerShell 7.
|
1 |
iex "& { $(irm https://aka.ms/install-powershell.ps1) } -UseMSI" |
List the subfolder sizes in a folder
|
1 2 3 4 5 |
Get-ChildItem -force '\\servername\sql_backup\BACKUP_OLA' -ErrorAction SilentlyContinue | Where-Object { $_ -is [io.directoryinfo] } | % { $len = 0 Get-ChildItem -recurse -force $_.fullname -ErrorAction SilentlyContinue | % { $len += $_.length } $_.fullname, '{0:N2} GB' -f ($len / 1Gb) } |
Result sample:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
/# result \\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AI 15.61 GB \\servername\sql_backup\BACKUP_OLA\SQL102318e002b 718.37 GB \\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BI 22.81 GB \\servername\sql_backup\BACKUP_OLA\SQL102318e001b 94.21 GB \\servername\sql_backup\BACKUP_OLA\SQL102184_STSS50E 97.33 GB \\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AP 17.52 GB \\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BP 36.99 GB \\servername\sql_backup\BACKUP_OLA\SQL102318e003c 355.39 GB \\servername\sql_backup\BACKUP_OLA\SQL102318e004b 3312.04 GB \\servername\sql_backup\BACKUP_OLA\SQL102249_STS05AP 1.85 GB \\servername\sql_backup\BACKUP_OLA\SQL102250_STS05BP 2.39 GB \\servername\sql_backup\BACKUP_OLA\AGSQL102318E005 0.90 GB \\servername\sql_backup\BACKUP_OLA\SQL102185_STSS50P 0.07 GB #/ |
Find old files recursively with powershell script:
|
1 |
Get-ChildItem -path \\servername\fileshare\folder -Recurse -Force -File | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-30)} |
Find the oldest file:
|
1 |
Get-ChildItem -path \\servername\share\folder -Recurse -Force -File | Sort-Object CreationTime | Select-Object -First 1 |
Gyakori eset, hogy a laptop hurcolásával más és más ip címet kapnak a virtuális szervereink. Host gépen az alábbi powershell paranccsal tudjuk kiíratni a guest gépek ip címeit:
|
1 |
Get-VM -ComputerName "mycomputername" | Get-VMNetworkAdapter |