PowerShell: 1-Liner to Discover SQL Servers on the Network

Run this command on server with SQL CLI installed (this will only detect SQL servers that has the SQL Service Discovery enabled)

PS C:\Windows\system32> [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

ServerName InstanceName IsClustered Version
---------- ------------ ----------- -------
TESTSQL01 SQLEXPRESS No 14.0.1000.169
TESTSQL02 SQLEXPRESS No 12.0.2000.8
TESTSQL02 SQLEXPRESS01 No 14.0.1000.169
TESTSQL02 SWPDM No 12.0.4100.1

Microsoft SQL Server Storage Capacity Planning

This would be a recommended mounted volumes for most environment:

  1. 80GB RAID10 C:\ Operating System
  2. 50GB RAID10 D:\ SQL Application (BIN) & System Databases
  3. 100GB RAID10 E:\ User Defined Databases (including related Indexes)
  4. 50GB RAID10 L:\ Transaction Logs
  5. 100GB RAID6 G:\ Backups, Batch Processing, Full Text Catalogs
  6. 50GB RAID6 T:\ TempDB

Reasonings:

  • Each volume would be expandable to support growth
  • User Defined databases should be on a separate volume from the System Databases to simplify backups and restores of those non-system files
  • Although some sources recommended that H:\ be used as the Backup volume, it should be avoided as many environments would map this as the “Home” folder for users
  • RAID10 were known for fast READ I/O with fairly performant WRITE I/O
  • RAID6 READ&WRITE I/O would be slower, yet this would be an effective use of available raw disks capacity while allowing 2 simultaneous disk failures with risking data loss.

MS SQL: Admin_Report_Notification

/* Admin_Report_Notification */
DECLARE @tab char(1)
SET @tab = CHAR(9)

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Report DBMail Profile',
@recipients = 'admin.dude@kimconnect.com',
@subject = 'dude, email report',
@body = 'Please see the attached query result for:

Use kimconnect
select * from table_a a (nolock)
join table_b b
on a.userid = b.userid
where a.status <> 4
and a.recorddeleted = 0',
@execute_query_database = 'kimconnect',
@query = N'SET NOCOUNT ON
select
LoginName,
FirstName,
MiddleName,
LastName,
Status = CASE
WHEN Status = 1 THEN ''ACTIVE''
WHEN Status = 2 THEN ''LOCKED''
WHEN Status = 3 THEN ''DISABLED''
ELSE ''DELETED''
END,
convert(varchar, PasswordExpirationDateUTC,120) as PasswordExpirationDateUTC,
convert(varchar, LastLoginDateUTC,120) as LastLoginDateUTC,
convert(varchar, LastLoginFailureDateUTC,120) as LastLoginFailureDateUTC,
ConsecutiveLoginFailures,
ChangePasswordRequired,
RecordDeleted,
ChangeLoginNameCount,
convert(varchar, UpdateDateUTC,120) as UpdateDateUTC,
convert(varchar, LastPasswordChangeDateTimeUTC,120) as LastPasswordChangeDateTimeUTC,
Role
from dbo.table_c a
join dbo.table_d b
on a.userid = b.userid
where a.status <> 4
and a.recorddeleted = 0;',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Admin Report.csv',
@query_result_separator=@tab,
@query_result_no_padding=1

MS SQL: Archive and Shrink Database

--Archive Database: with maximum allowed runtime and a set number of records
declare @EndDate datetime, @Recs INT, @maxrunTimeInMinutes INT
SET @maxrunTimeInMinutes = 60
SET @Records = 20000
set @EndDate = dateadd(dd,1,cast(convert(char(10),(select min(logdate) FROM KIMCONNECT.dbo.au_commontable),121) as datetime))
execute [dbo].[pOrccArchive] @maxrunTimeInMinutes = @maxrunTimeInMinutes, @Records = @Records, @EndDate = @EndDate

--Shrink Database:
EXEC pShrinkDatabase 1, 'KIMCONNECT'

PowerShell: Microsoft SQL Administration

Script to Install Microsoft Clustering Service

$proxy="http://proxy:8080"
$exclusionList="localhost;*.kimconnect.com"

function checkProxy{
try{
$connectionTest=iwr download.microsoft.com
#$connectionSucceeds=Test-NetConnection -Computername download.microsoft.com -Port 443 -InformationLevel Quiet
if ($connectionTest){
return $True;
}
}
catch{
return $False
}
}

function fixProxy{
# Check if proxy is enabled on the system and fix it
$proxyKey=(Get-ItemProperty -Path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings")
if ($proxyKey.ProxyEnable){
# Set http proxy for browsers
Set-Itemproperty -path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings" -Name 'ProxyServer' -value $proxy

# Set winhttp proxy for PowerShell
netsh winhttp set proxy $proxy $exclusionList

[system.net.webrequest]::defaultwebproxy = New-Object system.net.webproxy($proxy)
[system.net.webrequest]::defaultwebproxy.credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
[system.net.webrequest]::defaultwebproxy.BypassProxyOnLocal = $true
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
}

if (checkProxy){
"Proxy is now good to go..."
}
else{
"Proxy problems..."
break;
}
}

if(!(checkProxy)){"Internet issues detected. Fixing now..."; fixProxy;}

# Adding Microsoft Cluster
if (!(get-module -Name "FailoverClusters") ){
Install-WindowsFeature Failover-Clustering | out-null;
Install-WindowsFeature RSAT-Clustering-MGMT | out-null;
Install-WindowsFeature RSAT-Clustering-PowerShell | out-null;
Install-Module SqlServer -Force -Confirm:$False
Import-Module SqlServer
Import-Module FailoverClusters | out-null;
}


Script to Create New Microsoft Cluster

# Variables for New Cluster
$clusterName="SQLCLUSTER007"
$nodes="SQLSHERVER01","SQLSHERVER02"
$clusterIPs="x.x.x.x" # There should be a listener IP on each subnet that a node resides
$clusterQuorum="\\FILESERVER01\CLUSTER02"

Other SQL AlwaysOn High Availability Preparation Steps:
# Add Nodes into Permissions List of Quorum directory
# Create AD Group with the named node computer accounts
# Move nodes into same OU and add the newly created AD group with read-write access to that container
# Connect to each node to install SqlServer and/or SQLPS modules
# Ensure that clustering services are started on each node

# Build Cluster
New-Cluster -Name $clusterName -Node $nodes -StaticAddress $clusterIPs
# Set Quorum
if ($clusterQuorum -ne ""){
Set-ClusterQuorum -NodeAndFileShareMajority $clusterQuorum
}
# Connect to AD to sync DNS records
#repadmin /syncall on DC2
# Update DNS with the current Live IP of AlwaysOn Listener
#Get-ClusterResource $listenerAlias | Update-ClusterNetworkNameResource
# Check DNS Record to verify the correct Active IP
#(Resolve-DnsName $listener).IPAddress
# Force DNS Replication (SysAdmin)
#repadmin /syncall /Aed

Script to Create Cluster (Duplicate)

$proxy="http://proxy:8080"
$exclusionList="localhost;*.kimconnect.com"

function checkProxy{
try{
$connectionTest=iwr download.microsoft.com
#$connectionSucceeds=Test-NetConnection -Computername download.microsoft.com -Port 443 -InformationLevel Quiet
if ($connectionTest){
return $True;
}
}
catch{
return $False
}
}

function fixProxy{
# Check if proxy is enabled on the system and fix it
$proxyKey=(Get-ItemProperty -Path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings")
if ($proxyKey.ProxyEnable){
# Set http proxy for browsers
Set-Itemproperty -path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings" -Name 'ProxyServer' -value $proxy

# Set winhttp proxy for PowerShell
netsh winhttp set proxy $proxy $exclusionList

[system.net.webrequest]::defaultwebproxy = New-Object system.net.webproxy($proxy)
[system.net.webrequest]::defaultwebproxy.credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
[system.net.webrequest]::defaultwebproxy.BypassProxyOnLocal = $true
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
}

if (checkProxy){
"Proxy is now good to go..."
}
else{
"Proxy problems..."
break;
}
}

if(!(checkProxy)){"Internet issues detected. Fixing now..."; fixProxy;}

# Adding Microsoft Cluster
if (!(get-module -Name "FailoverClusters") ){
Install-WindowsFeature Failover-Clustering | out-null;
Install-WindowsFeature RSAT-Clustering-MGMT | out-null;
Install-WindowsFeature RSAT-Clustering-PowerShell | out-null;
Install-Module SqlServer -Force -Confirm:$False
Import-Module SqlServer
Import-Module FailoverClusters | out-null;
}


# Variables for New Cluster
$clusterName="SQLCLUSTER007"
$nodes="SQLSERVER01","SQLSERVER02"
$clusterIPs="x.x.x.x" # There should be a listener IP on each subnet that a node resides
$fileShareWitness=""
$domainExpression='dc=kimconnect,dc=com'

# Preparation Steps
# Add Nodes into Permissions List of Quorum directory
# Create AD Group with the named node computer accounts
# Move nodes into same OU and add the newly created AD group with read-write access to that container
# Connect to each node to install SqlServer and/or SQLPS modules
# Ensure that clustering services are started on each node

# Build Cluster
New-Cluster -Name $clusterName -Node $nodes -StaticAddress $clusterIPs

# Set Quorum
if ($fileShareWitness -ne ""){
Set-ClusterQuorum -NodeAndFileShareMajority $fileShareWitness
}

# Connect to AD to sync DNS records
#repadmin /syncall on DC2

# Other commands...

# Update DNS with the current Live IP of AlwaysOn Listener
#Get-ClusterResource $listenerAlias | Update-ClusterNetworkNameResource

# Check DNS Record to verify the correct Active IP
#(Resolve-DnsName $listener).IPAddress

# Force DNS Replication (SysAdmin)
# repadmin /syncall /Aed
# repadmin /syncall %dc% /d /e /a /A /P
# repadmin /syncall dst-dc01 dc=kinecta,dc=org /d /e /a
# Repadmin /replicate $Destination $Source $domainExpression
# Adding Active Directory Module
if (!(get-module -name "ActiveDirectory") ){
Add-WindowsFeature RSAT-AD-PowerShell | out-null;
import-module -name "ActiveDirectory" -DisableNameChecking | out-null;
}
$dcs=(Get-ADDomainController -Filter *).Name
$domainName=(get-addomain).DNSRoot
function Replicate-AllDomainController {
$dcs | Foreach-Object {repadmin /syncall $_ (Get-ADDomain).DistinguishedName /e /A | Out-Null};
Start-Sleep 10;
# Check last replication success of each target server
Get-ADReplicationPartnerMetadata -Target $domainName -Scope Domain | Select-Object Server, LastReplicationSuccess;
}

Script to Administer SQL Clusters

<#  Sypnosis
What it does:
- Collects all FailOver Clusters in the current domain
- Lists all nodes of a selected cluster
- Asks user whether to change Primary node in a particular cluster
- Changes Primary node basing on user input

Current Limitations:
- Cannot run outside of a server with FailOver Clusters being set
- Dependent on WinRM being available on remote machines
- Does not build cluster - that's a function in future development
#>

$proxy="http://proxy:8080"
$exclusionList="localhost;*.kimconnect.com"

# Dynamic Credential
$who = whoami
if ($who.substring($who.length-2, 2) -eq "-admin"){$username=$who;}
else {$username=$who+"-admin";}
$password = Read-Host -Prompt "Input the password for account $username" -AsSecureString
#$password=convertto-securestring "PASSWORD" -AsPlainText -Force
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username,$password

# Show cluster resources and owners
#get-clustergroup

# Initialize variables
$clusters=""
$nodes=""
$list=""
$pickIndex=""
$pickedItem=""
$pickedCluster=""

# Ensure that script is ran in the context of an Administrator
$myWindowsID=[System.Security.Principal.WindowsIdentity]::GetCurrent()
$myWindowsPrincipal=new-object System.Security.Principal.WindowsPrincipal($myWindowsID)

# Get the security principal for the Administrator role
$adminRole=[System.Security.Principal.WindowsBuiltInRole]::Administrator

# Check to see if we are currently running "as Administrator"
if ($myWindowsPrincipal.IsInRole($adminRole))
{
# We are running "as Administrator" - so change the title and background color to indicate this
$Host.UI.RawUI.WindowTitle = $myInvocation.MyCommand.Definition + "(Elevated)"
$Host.UI.RawUI.BackgroundColor = "Black"
clear-host
}
else
{
# We are not running "as Administrator" - so relaunch as administrator

# Create a new process object that starts PowerShell
$newProcess = new-object System.Diagnostics.ProcessStartInfo "PowerShell";

# Specify the current script path and name as a parameter
$newProcess.Arguments = $myInvocation.MyCommand.Definition;

# Indicate that the process should be elevated
$newProcess.Verb = "runas";

# Start the new process
[System.Diagnostics.Process]::Start($newProcess);

# Exit from the current, unelevated, process
exit
}

function checkProxy{
try{
$connectionTest=iwr download.microsoft.com
#$connectionSucceeds=Test-NetConnection -Computername download.microsoft.com -Port 443 -InformationLevel Quiet
if ($connectionTest){
return $True;
}
}
catch{
return $False
}
}

function fixProxy{
# Check if proxy is enabled on the system and fix it
$proxyKey=(Get-ItemProperty -Path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings")
if ($proxyKey.ProxyEnable){
# Set http proxy for browsers
Set-Itemproperty -path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings" -Name 'ProxyServer' -value $proxy

# Set winhttp proxy for PowerShell
netsh winhttp set proxy $proxy $exclusionList

[system.net.webrequest]::defaultwebproxy = New-Object system.net.webproxy($proxy)
[system.net.webrequest]::defaultwebproxy.credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
[system.net.webrequest]::defaultwebproxy.BypassProxyOnLocal = $true
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
}

if (checkProxy){
"Proxy is now good to go..."
}
else{
"Proxy problems..."
break;
}
}

if(!(checkProxy)){"Internet issues detected. Fixing now..."; fixProxy;}

# Set PowerShell Gallery as Trusted to bypass prompts
$trustPSGallery=(Get-psrepository -Name 'PSGallery').InstallationPolicy
If($trustPSGallery -ne 'Trusted'){
Set-PSRepository -Name 'PSGallery' -InstallationPolicy Trusted
}

# PowerShell Execution Policy
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Confirm:$false -Force

# Install PowerShellGet
#Install-Module PowerShellGet -Force

# Adding Prerequisite Active Directory Module
if (!(get-module -name "ActiveDirectory") ){
Add-WindowsFeature RSAT-AD-PowerShell | out-null;
import-module -name "ActiveDirectory" -DisableNameChecking | out-null;
}
$domain=get-addomain
cls

# Adding Microsoft Cluster
if (!(get-module -Name "FailoverClusters") ){
Install-WindowsFeature Failover-Clustering | out-null;
Install-WindowsFeature RSAT-Clustering-MGMT | out-null;
Install-WindowsFeature RSAT-Clustering-PowerShell | out-null;
Install-Module SqlServer -Force -Confirm:$False
Import-Module SqlServer
Import-Module FailoverClusters | out-null;
}

function listClusters{
$domainName=($domain.DNSRoot).toUpper()
"`n`nListing all Clusters in $domainName...`n"
$global:clusters=get-cluster -domain $domain
#$global:headLine="Available clusters in $domain`:"
}

function listNodes{
"`n`nListing all Nodes in this Cluster...`n"
$global:nodes=(Get-ClusterNode -Cluster $pickedItem).Name
$global:pickedCluster=$pickedItem
#$global:headLine="Available nodes in $pickedItem`:"
}

function show{
Param($callFunction)
invoke-command (Get-Item "function:$callFunction").ScriptBlock

if ($nodes){$count=$nodes.count; $global:list=$nodes}
else {$count=$clusters.count; $global:list=$clusters; $global:nodes=""}

$show="`n--------------------------------------------------------`nThere are $count items on this list:`n--------------------------------------------------------`n"
#$show="`n--------------------------------------------------------`n$headLine`n--------------------------------------------------------`n"
for ($row=0;$row -le $count-1;$row++){
$server=$list[$row]
$show += "$row" + ": " + "$server" + "`n"
}
return $show
}

function pickItem{
do {
try {
$flag = $true
[int]$pick=Read-Host -Prompt "`n--------------------------------------------------------`nPlease type the number corresponding to the desired item`n--------------------------------------------------------"
} # end try
catch {$flag = $false}
} # end do
until ($pick -lt $list.count -and $flag)

$global:pickIndex=$pick
$global:pickedItem=$list[$pick]
$clusters=""
$nodes=""
"$pick`: $pickedItem is picked"
}

function getPrimary{

# Pick Cluster
do {
try{
$flag1=$true
show -callFunction listClusters
pickItem
}
catch{$flag1=$fase}
}
until ($flag1)

# Pick Node
do {
try{
$flag2=$true
show -callFunction listNodes
pickItem
}
catch{$flag2=$fase}
}
until ($flag2)
}

# return true or false as determinant on whether program proceeds
function allNodesReachable{
$winRMPort=5985
$allNodesReachable=$True
$nodes | foreach {
if (!(Test-NetConnection -ComputerName $_ -Port $winRMPort).TcpTestSucceeded){$allNodesReachable = $False;break;}
}
return $allNodesReachable
}

function sqlClusterDiscovery{
$global:targetNode=$nodes[$pickIndex]
#$global:targetNode=$target

# Connect to the target node to retrieve SQL Clustering Information
$global:sqlClusterNameStatus=$False
$global:sqlClusterName=""
$global:sqlClusterOwner=""
$global:sqlClusterListener=""
$global:sqlClusterListenerAlias=""
$global:sqlClusterTargetSynchronized=$False
$global:victim='None'

$sqlClusterDiscovery=Invoke-Command -Credential $cred -ComputerName $targetNode -ScriptBlock{
$invoke = @{ComputerName=$env:COMPUTERNAME}
$noError=$False
$theTarget=$Args

try{
if ( !(get-module -name "FailoverClusters" ) ) {Import-Module FailoverClusters -DisableNameChecking | out-null;}
$clusterName=(get-clusterresource).Name[3]
#$clusterName=(ls SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups).Name #Alternative method
$owner=(get-clusterresource | Select -ExpandProperty OwnerNode).Name[3]
#owner=(ls SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups).PrimaryReplicaServerName #Alternative method
$listener=(Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq "name"})[1].Value
$listenerAlias=(Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq "name"})[1].ClusterObject.Name

# Check for Synch status
if ( !(get-module -name "SQLPS" ) ) {import-module -name "SQLPS" -DisableNameChecking | out-null;}
$nodeObject=Get-Item SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$clusterName\AvailabilityReplicas\$theTarget
$targetNodeSynchronized=$nodeObject.RollupSynchronizationState -like 'Synchronized'
$noError=$True
}
catch{
$noError=$False
}

# Object is to be returned from remote to local PowerShell session
if ($noError){
$invoke.Add('sqlClusterStatus',$True)
$invoke.Add('sqlClusterName',$clusterName)
$invoke.Add('sqlClusterOwner',$owner)
$invoke.Add('sqlClusterListener',$listener)
$invoke.Add('sqlClusterListenerAlias',$listenerAlias)
$invoke.Add('sqlClusterTargetSynchronized',$targetNodeSynchronized)
}
else{
$invoke.Add('sqlClusterStatus',$False)
$invoke.Add('sqlClusterName',$clusterName)
$invoke.Add('sqlClusterOwner',$owner)
$invoke.Add('sqlClusterListener',$listener)
$invoke.Add('sqlClusterListenerAlias',$listenerAlias)
$invoke.Add('sqlClusterTargetSynchronized',$targetNodeSynchronized)
}
New-Object -Type PSObject -Prop $invoke
} -Args $targetNode #End sqlClusterDiscovery Invoke-Command

$global:sqlClusterNameStatus = $sqlClusterDiscovery.sqlClusterStatus
$global:sqlClusterName = $sqlClusterDiscovery.sqlClusterName
$global:sqlClusterOwner = $sqlClusterDiscovery.sqlClusterOwner
$global:sqlClusterListener = $sqlClusterDiscovery.sqlClusterListener
$global:sqlClusterListenerAlias = $sqlClusterDiscovery.sqlClusterListenerAlias
$global:sqlClusterTargetSynchronized = $sqlClusterDiscovery.sqlClusterTargetSynchronized

# Pick a Potential Victim: a Synchronized Node that is not a Cluster Owner
if ($sqlClusterTargetSynchronized -eq $False){
$nodes | foreach {
if ($_ -ne $sqlClusterOwner -and $_ -ne $targetNode){
$potentialVictim = Invoke-Command -Credential $cred -ComputerName $_ -ScriptBlock{
$thisNode=$env:COMPUTERNAME
$invoke = @{ComputerName=$env:COMPUTERNAME}
"Checking $thisNode..."

if ( !(get-module -name "SQLPS" ) ) {import-module -name "SQLPS" -DisableNameChecking | out-null;}
$clusterName=(ls SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups).Name
$nodeObject=Get-Item SQLSERVER:\Sql\$thisNode\default\AvailabilityGroups\$clusterName\AvailabilityReplicas\$thisNode
$thisNodeSynchronized=$nodeObject.RollupSynchronizationState -like 'Synchronized'

if ($thisNodeSynchronized){
"Victim shall be $thisNode";
$invoke.Add('Victim',$thisNode);
}
New-Object -Type PSObject -Prop $invoke
} # End potentialVictim Invoke-command

$global:victim=$potentialVictim.Victim
#if ($victim -ne 'None'){continue;}
#else{"`nThe chosen node $targetNode's SQL databases are NOT Synchronized, but no other node has been found to transfer this ability to $targetNode. Failover can only proceed with a potential loss of data.`n";}

} #Endif nodes loop
} #End foreach
} #End if not synchronized

Write-host "`nCluster $pickedCluster Discovery Results:`n---------------------------------------------------------------------------------------`n"
Write-host "targetNode: $targetNode"
Write-host "sqlClusterTargetSynchronized: $sqlClusterTargetSynchronized"
Write-host "sqlClusterName: $sqlClusterName"
Write-host "sqlClusterNameStatus: $sqlClusterNameStatus"
Write-host "sqlClusterOwner: $sqlClusterOwner"
Write-host "sqlClusterListener: $sqlClusterListener"
Write-host "sqlClusterListenerAlias: $sqlClusterListenerAlias"
Write-host "victim (if any): $victim"
}

function seizeSyncMode{
<#
This function ensures that the target node can be set with Asynchronous Commit mode; otherwise, program cannot safely executes without potential data loss.
- list all nodes
- connect to each node to query whether it's primary or secondary and whether it's set with synchronous commit mode
- if target node is a secondary with asynchronous commit mode, connect to another secondary and change that to asynchronous mode so that
the target node can be set with synchronous commit mode
#>

try{
# Step 1: connect to cluster owner switch synch modes of secondary replicas
Invoke-Command -Credential $cred -ComputerName $sqlClusterOwner -ScriptBlock{
"`nConnected to $env:computername...`n"
$theCluster=$Args[0]
$theVictim=$Args[1]
$theChosen=$Args[2]
$thisComputer=$env:computername

if ( !(get-module -name "SQLPS" ) ) {"SQLPS not in shell environment. Now importing..."; import-module -name "SQLPS" -DisableNameChecking | out-null }

"`nNow setting $theVictim replica with Asynchronous Manual Mode...`n"
Set-SqlAvailabilityReplica -AvailabilityMode "ASynchronousCommit" -FailoverMode "Manual" -Path SQLSERVER:\Sql\$thisComputer\default\AvailabilityGroups\$theCluster\AvailabilityReplicas\$theVictim

"`nNow setting $theChosen replica with Synchronous Commit mode...`n"
Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -Path SQLSERVER:\Sql\$thisComputer\default\AvailabilityGroups\$theCluster\AvailabilityReplicas\$theChosen

<#
# Check replica synchronization
$newSync = Get-Item "SQLSERVER:\Sql\$thisComputer\default\AvailabilityGroups\$theCluster\AvailabilityReplicas\$theChosen"
$synchronized=$newSync.RollupSynchronizationState -like 'Synchronized'
"`nReplica $theChosen is currently synchronized: $synchronized`n"

if (!($synchronized)){
Write-Host -NoNewline "Waiting for synchronization to complete..."
$dots=50
$timeout=300 #5 minutes
while (!($synchronized)) {
$dots-=1;
$timeout-=2;
if($timeout -lt 0){"$timeout seconds have passed. Skip this waiting."; continue;}
if ($dots -eq 0){Write-Host ".";$dots=92;}
else {Write-Host -NoNewline "."}
Start-Sleep -s 2
$newSync.Refresh()
}
}
#>

"`nExiting $env:computername...`n"
} -Args $sqlClusterName,$victim,$targetNode
return $True;
}
catch{return $False;}
}

function removeReplica($item){

# Connect to cluster owner switch synch modes of secondary replicas
Invoke-Command -Credential $cred -ComputerName $sqlClusterOwner -ScriptBlock{
"`nConnected to $env:computername..."
$theCluster=$Args[0]
$theReplica=$Args[1]
$thisComputer=$env:computername

if ( !(get-module -name "SQLPS" ) ) {"SQLPS not in shell environment. Now importing..."; import-module -name "SQLPS" -DisableNameChecking | out-null }

# Removing replica membership
Remove-SqlAvailabilityReplica -Path SQLSERVER:\SQL\$thisComputer\default\AvailabilityGroups\$theCluster\AvailabilityReplicas\$theReplica
write-host "$theReplica has now been removed from $theCluster."

} -Args $sqlClusterName,$item
}

function addReplica($item,$synchMode){
# synchMode value shall either equal "1":Synchronous or "2":ASynchronous

# Step 1: Connect to Cluster Owner to Add Replica
Invoke-Command -Credential $cred -ComputerName $sqlClusterOwner -ScriptBlock{
"`nConnected to $env:computername..."
$theCluster=$Args[0]
$theReplica=$Args[1]
$thisComputer=$env:computername
$synchType=$Args[2]

if ( !(get-module -name "SQLPS" ) ) {"SQLPS module not in shell environment. Now importing..."; import-module -name "SQLPS" -DisableNameChecking | out-null }
if ( !(get-module -name "ActiveDirectory") ) {"ActiveDirectory module not in shell environment. Now importing..."; Add-WindowsFeature RSAT-AD-PowerShell; import-module -name "ActiveDirectory" -DisableNameChecking | out-null }

# Add secondary replica into cluster
$domain=(get-addomain).DNSRoot
$agPath = "SQLSERVER:\SQL\$thisComputer\default\AvailabilityGroups\$theCluster"
$endpointURL = "TCP://$theReplica.$domain`:5022"
$availabilityMode = if($synchType -eq 1){"SynchronousCommit"}else{"ASynchronousCommit"}
if ($availabilityMode -eq "SynchronousCommit"){$failoverMode = "Automatic"}else{$failoverMode = "Manual"}
$secondaryReadMode = "AllowAllConnections"
New-SqlAvailabilityReplica -Name $theReplica -EndpointUrl $endpointURL -FailoverMode $failoverMode -AvailabilityMode $availabilityMode -ConnectionModeInSecondaryRole $secondaryReadMode -Path $agPath

#$databases=Get-ChildItem SQLSERVER:\SQL\$env:computername\default\AvailabilityGroups\$theCluster\AvailabilityDatabases
#$databases | foreach {Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$env:computername\default\AvailabilityGroups\$theCluster" -Database $_}

} -Args $sqlClusterName,$item,$synchMode

# Step 2: Connect to new secondary replica to (a) join it to Availability Group (b) Join Databases to Availability Group
Invoke-Command -Credential $cred -ComputerName $item -ScriptBlock{
"`nConnected to $env:computername..."
$theCluster=$Args[0]
$theReplica=$Args[1]
$thisComputer=$env:computername

if ( !(get-module -name "SQLPS" ) ) {"SQLPS module not in shell environment. Now importing..."; import-module -name "SQLPS" -DisableNameChecking | out-null }

# Part (a): join node into availability group
"Joining $theReplica to cluster $theCluster..."
Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\$thisComputer\default -Name $theCluster

# Part (b): join databases into availability group
"Joining databases to cluster $theCluster..."
$databases=Get-ChildItem SQLSERVER:\SQL\$theReplica\default\AvailabilityGroups\$theCluster\AvailabilityDatabases
$databases | foreach {Add-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$theReplica\default\AvailabilityGroups\$theCluster\AvailabilityDatabases -Database $_}

# Resume Data Movement
$databases= Get-ChildItem SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$theCluster\AvailabilityDatabases
$databases | Resume-SqlAvailabilityDatabase

<#
# Check Synchronization state
$newSync = Get-Item SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$theCluster\AvailabilityReplicas\$env:computername
$notSynchronized=$newSync.RollupSynchronizationState -notlike 'Synchronized'
"`nDatabases are currently synchronized: $notSynchronized`n"
if ($notSynchronized){
Write-Host -NoNewline "Waiting for synchronization to complete..."
$dots=50
while ($notSynchronized) {
$dots-=1;
if ($dots -eq 0){Write-Host ".";$dots=92;}
else {Write-Host -NoNewline "."}
Start-Sleep -s 2
$newSync.Refresh()
}
#>

} -Args $sqlClusterName,$item

}

function setPrimary($index){
$tries=3;

# Check Point (rudimentary method)
"`n`n`n`n{0} function is invoked!" -f $MyInvocation.MyCommand
"`nPlease confirm that you want to set node $targetNode as Primary for cluster $pickedCluster"
do {
try {
[string]$confirm=Read-Host -Prompt "`nType in 'YES' to confirm! you have $tries attempt(s) left. Press Ctrl + C to abort!"
$tries -= 1;
} # end try
catch {$confirm = $false}
} # end do
until ($tries -eq 0 -OR $confirm -eq "YES")

if ($confirm){
"`nConfirmation received!`n"

"Please Wait while the program executes..."

$primaryInvoke = Invoke-Command -Credential $cred -ComputerName $targetNode -ScriptBlock {
$invoke = @{ComputerName=$env:COMPUTERNAME}
$noError=$True
$hostname=hostname
"Now setting $hostname as Primary Failover Node..."

$theTarget=$Args[0]

if ($hostname -eq $theTarget){
if ( !(get-module -name "SQLPS" ) ) {"SQLPS Module is currently not installed on $hostname. Importing..."; import-module -name "SQLPS" -DisableNameChecking | out-null;}
$cluster=(get-clusterresource).Name[3]

# Checking synchronization state with the primary replica before proceeding to Manual Failover and avoiding data loss
Write-Host -NoNewline "Checking Synchronization status..."
$newPrimary = Get-Item SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityReplicas\$theTarget
$notSynchronized=$newPrimary.RollupSynchronizationState -notlike 'Synchronized'
#$notHealthy=(Test-SqlAvailabilityReplica -Path SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityReplicas\$theTarget).HealthState -notlike 'Healthy';

if ($notSynchronized) {
try{
"Attempting to switch this replica, $theTarget, to 'Synchronous Commit' mode..."
# Set availability mode as Synchronous commit
Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" -Path SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityReplicas\$theTarget
Write-Host -NoNewline "Waiting for synchronization to complete..."
while ($notSynchronized) {
Write-Host -NoNewline "."
Start-Sleep -s 2
$NewPrimary.Refresh()
}
}
catch{
Write-Host "This node cannot become synchronized with the Primary Node.`nFailover cannot proceed without data loss."
$noError=$False
return $_ # Break out of remote shell session
}
} # End if Not Synchronized
if (!($notSynchronized)) { # Execute only if Replica has been synchronized!
try{

# Move cluster group primary role to a specific node
if ( !(get-module -name "FailoverClusters" ) ) {"FailoverClusters Module is currently not installed on $hostname. Importing..."; Import-Module FailoverClusters -DisableNameChecking | out-null;}
Move-ClusterGroup “Cluster Group” -node $theTarget

# Setting Target Node as Primary Node
$databases= Get-ChildItem "SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityDatabases"
$databases | Suspend-SqlAvailabilityDatabase
Switch-SqlAvailabilityGroup -Path SQLSERVER:\sql\$env:computername\default\availabilitygroups\$cluster #-AllowDataLoss

# These lines will only run on the SQL AlwaysOn HA Primary Node
# "Switching this Primary Node $theTarget to 'Synchronous Commit' mode..."
Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" -Path SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityReplicas\$theTarget
if ($notSynchronized) {
Write-Host -NoNewline "Waiting for node status to reach full synchronization..."
while ($notSynchronized) {
Write-Host -NoNewline "."
Start-Sleep -s 2
$NewPrimary.Refresh()
}
"$theTarget node has been synchronized."
} # end if not Synchronized
else{
"$theTarget node is already synchronized."
}

$databases | Resume-SqlAvailabilityDatabase

"Updating listener IP Address with DNS..."
$listener=(Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq "name"})[1].Value
$listenerAlias=(Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq "name"})[1].ClusterObject.Name
Get-ClusterResource $listenerAlias | Update-ClusterNetworkNameResource
# Resolve-DnsName $listener
} # end try
catch{
"Error has occured in the Setting Target Node as Primary Node."
$noError=$False
}
} # End if synchronized

} # End if hostname = theTarget
else{
"There appears to be a mismatch between this node's hostname and the selected target.`nProgram shall not execute!"
$noError=$False
}

# This variable determines whether program would continue to set other nodes to resume database movements
# Object is to be returned from remote to local PowerShell session
if ($noError){
$invoke.Add('Status',$True)
}
else{
$invoke.Add('Status',$False)
}
New-Object -Type PSObject -Prop $invoke

} -Args $targetNode,$pickedCluster


"Now setting the other Nodes to resume Database movements..."
if ($primaryInvoke.Status){ $nodes | foreach {
if ($_ -ne $targetNode){
Invoke-Command -Credential $cred -ComputerName $_ -ScriptBlock{
hostname
if ( !(get-module -name "SQLPS" ) ) { import-module -name "SQLPS" -DisableNameChecking | out-null }
$cluster=(get-clusterresource).Name[3]
$databases= Get-ChildItem "SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityDatabases"
$databases | Resume-SqlAvailabilityDatabase
# Join-SqlAvailabilityGroup -Path SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups -Name $cluster
}
} -Args $targetNode,$pickedCluster
}
}
# "Done!`n`nProcess is completed to set $targetNode as the Primary Node."
} # endif confirm
#>
}

getPrimary
if (allNodesReachable){sqlClusterDiscovery}
if($victim -ne 'None' -and $victim -ne $null){if(seizeSyncMode){setPrimary $pickIndex}}

MS SQL: Using Profiler to Trace Failed Logins

Story:

There has been an issue with a service account triggering login errors at the frequency of once every 5 minutes. This has alerted Information Security as failed logins are aggregated and reviewed regularly. Thus, the task is to investigate and resolve this problem on the targeted node, a SQL server.

Symptoms:

Repeated error message in text format:

Login failed for user 'KIMCONNECT\sql_readonly'. Reason: Failed to open the explicitly specified database. [CLIENT: <localmachine>]
Error: 18456, Severity: 14, State: 38

Initial Attempt at Addressing Issue:

First, grant SELECT permissions to all database objects

SELECT 'GRANT SELECT ON "' + TABLE_SCHEMA + '"."' + TABLE_NAME + '" TO "KIMCONNECT\sql_readonly"' FROM information_schema.tables

Output

GRANT SELECT ON "dbo"."spt_fallback_db" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."spt_fallback_dev" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."AU_CommonTable_View" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."spt_fallback_usg" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."RPT_Common" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."spt_monitor" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."spt_values" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."MSreplication_options" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."_DBNames" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."_DBStatistics" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."database_size_free_space" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."disk_size" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."AU_XFR_EntityChangeSetProperty_View" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."AU_XFR_EntityChangeSet_View" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."AU_XFR_ScheduledHostTransfer_View" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."AU_XFR_HostTransferTransaction_View" TO "KIMCONNECT\sql_readonly"

Second, grant SELECT permissions toward a specified database

GRANT SELECT ON "TESTDB" TO "KIMCONNECT\sql_readonly"

Alternative

GRANT SELECT ON "dbo"."TESTDB" TO "KIMCONNECT\sql_readonly"

Output if the granter doesn’t have permissions

Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'TESTDB', because it does not exist or you do not have permission.

Expected outcome if grantor has the appropriate permissions

Command(s) completed successfully
Further Issues:

After those SQL statements have been issued, the problem still persists. Further investigation shows that the targeted databases have been in recovery pending statuses.

New Error messages:

[298] SQL Server Error: 40060. Cannot open database 'TESTDB" requested by the login. [SQLSTAGE 42000]
[298] SQL Server Error: 18456, Login failed for user 'KIMCONNECT\sql_readonly'.[SQLSTATE 28000]
Result of Attempts:

It appears that service accounts automatically log into databases to perform routine maintenance tasks, such as optimizing indexes and querying performance stats. If the databases are in pending recovery mode, these login attempts will fail. The fix is to kick the problematic databases out of recovery mode or set them as offline and to add “public” access to all databases of the service account. Afterward, we can verify that 5-minute interval errors have ceased… NOT.

Next, Add User Mapping:

Connect to SQL Server using SSMS > navigate to the Security > Logins > right-click on the account > Properties > select the “User Mapping” tab > put a check mark to each database that this service account should have access > ensure that there is a check market next to “public” access option > OK

Next, Run Profiler:

Start > All Programs > Microsoft SQL server > Performance Tools > SQL Server Profiler > New Trace > Connect to SQL Server Instance > Give the trace a name such as Failed Logins

Uncheck everything > Put check marks next to Show all events & Show all columns > Click on Security Audit category > put a check mark next to Audit Login Failed > Optional: select only Application Name & Text Data columns > click Run

Observe the first match(es)

Right-click an Event > Pause Trace > Expand Application Name, Database Name & Text Data

According the screen-shot above, one can interpret that the database name “Master” is having a a login issue by the readonly-sql service account. The application is “SQLAgent – TSQL JobStep (Job 0x262EBA2E6B57.. : Step 1)”. Once this is identified, it’s would be necessary to modify permissions of the database named “master” using one of these methods:

To grant read permissions, add the default db_datareader role (read permission on all tables) to that account.

USE master
GO
EXEC sp_addrolemember N'db_datareader', N"KIMCONNECT\sql_readonly"
GO

There’s also a db_datawriter role – which gives your user all WRITE permissions (INSERT, UPDATE, DELETE) on all tables:

USE master
GO
EXEC sp_addrolemember N'db_datareader', N"KIMCONNECT\sql_readwrite"
EXEC sp_addrolemember N'db_datawriter', N"KIMCONNECT\sql_readwrite"
GO

One Liner version

GRANT CONNECT, SELECT, INSERT, UPDATE, CONTROL, EXECUTE, ALTER, ALL ON DATABASE::master TO "KIMCONNECT\sql_readwrite"
GO
Solution:

The attempts above have only assisted us at getting closer to the fix. It appears that issue has to do with a SQL job as indicated in the “SQLAgent – TSQL JobStep (Job 0x2628EBA2E6B57A409BB4D3689BE03287 : Step 1)” of the ApplicationName error being produced by the SQL Server Profiler.

Run this command to convert Job ID to Job Name

SELECT name FROM msdb.dbo.sysjobs WHERE CONVERT(binary(16), job_id)=0x2628EBA2E6B57A409BB4D3689BE03287

Once the culprit has been identified, pausing that job correlates to the ceasing of the failed login events.

SQL Language (Condensed)

SELECT [DISTINCT | MIN | MAX] column1 [AS alias]
FROM table_name
WHERE condition [IS NULL|IS NOT NULL] <== WHERE is a command to declare that a filtering condition shall follow
GROUP BY column_name(s)
HAVING [NOT] condition1 AND|OR condition2 <== performs aggregate functions
ORDER BY column1,column2 [BETWEEN value1 AND value2] [IN (value1,value2)] [LIKE {pattern}] [ASC|DESC];

SELECT [MIN|MAX|COUNT|AVG|SUM](column_name) FROM tablename;

SELF JOIN: table joins with itself
INNER JOIN: both tables
LEFT JOIN: matched values of LEFT table
RIGHT JOIN: matched values of RIGHT table
FULL JOIN: returns all records matching both tables (large resulting data sets)

SELECT Cars.Toyota, Owners.Name
FROM ParkingLot;
INNER JOIN Owners ON Cars.OwnerID=Owners.OwnerID;

UNION is used to selects only distinct records between two SELECTs. To also return non-distinct, use UNION ALL

SELECT column1 FROM table1
UNION [ALL]
SELECT column1 FROM table2;

EXISTS: returns a boolean value of TRUE or FALSE of any matches
SELECT * FROM Cars WHERE EXISTS (SELECT Model from Cars WHERE Model='Camry');

SELECT INTO: copies data from one table into a new table
SELECT *
INTO newHouse [IN city2]
FROM oldHouse
WHERE movable=TRUE;

SQL INSERT INTO SELECT Statement is similar to INSERT INTO with the addition of requiring data source and target tables match
INSERT INTO newHouse.LivingRoom
SELECT * FROM oldHouse.LivingRoom
WHERE movable=TRUE;

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

DELETE FROM table_name WHERE condition;

CASE Syntax:
CASE
WHEN condition THEN result
ELSE otherResult
END;

Comparison operators:
=,!=,<=,>=

Supplement operators:
ANY :returns TRUE or FALSE if subquery meets the condition
ISNULL() :returns true or false from the evaluative condition
IFNULL() :will execute when null condition is met
COALESCE() :is same is IFNULL()

Wilcards:
% :Represents any character
_ :underscore represents 1 character
[] :character inside brackets
^ :NOT
- :range (e.g. [a-z]}

Other Special Keywords:
ROWNUM :matches the row number

Stored Procedures:

1. Create:
CREATE PROCEDURE backup_db
AS
BACKUP DATABASE oldHouse
TO DISK = 'C:\oldHouse_db.BAK'
GO;

2. Execute
EXEC backup_db;

Some Old SQL Snippets

--Visibar_Test

SELECT STK_ROOM, BIN, INV_CODE, LOC_DESC
FROM LOCATION_DESCRIPTION
WHERE (STK_ROOM IN (@Stock)) AND (BIN IN (@Bins))
-- Query to fix POUID

select cast(POUID as binary(7)), POUID, VCPOUID
from Mfg_DFSFNSF
where substring(POUID,6,1) = char(0)
and
substring(POUID,1,1) != char(0)


update Mfg_DFSFNSF
set POUID = left(POUID, 5) + char(32) + char(32)
where substring(POUID,6,1) = char(0)
and
substring(POUID,1,1) != char(0)
--Sales_2004

SELECT TOP (100) PERCENT dbo.Z_SAAM_CustomerClassValues_D_I.CustomerClassValue, dbo.Z_SAAM_GroupDesc.GroupDesc, SUM(slt.ShippedQuantity)
AS ShippedQuantity, SUM(slt.ShippedQuantity * cl.ItemLocalNetUnitPrice) AS ShippedAmount, ISNULL(SUM(slt.StandardTotalShippedCost), 0.0)
- ISNULL(SUM(ia.StandardTotalAdjustmentCost), 0.0) AS StandardTotalGLAmount, ISNULL(SUM(slt.SATotalShippedCost), 0.0)
- ISNULL(SUM(ia.SATotalAdjustmentCost), 0.0) AS SATotalGLAmount, i.ItemReference4, fp.FiscalYear, fp.FiscalPeriod, i.ItemNumber
FROM dbo.SA_ShipmentCOLineTransaction AS slt INNER JOIN
dbo.SA_ShipmentCOLine AS sh ON sh.ShipmentCOLineKey = slt.ShipmentCOLineKey INNER JOIN
dbo.SA_COLine AS cl ON cl.COLineKey = sh.COLineKey INNER JOIN
dbo.SA_Item AS i ON i.ItemKey = sh.ItemKey INNER JOIN
dbo.SA_COHeader AS ch ON ch.COHeaderKey = cl.COHeaderKey LEFT OUTER JOIN
dbo.SA_CommissionCode AS cc ON cc.CommissionCodeKey = ch.CommissionCodeKey LEFT OUTER JOIN
dbo.SA_Region AS r ON r.RegionKey = ch.RegionKey INNER JOIN
dbo.SA_Customer AS c ON c.CustomerKey = ch.ShipToCustomerKey INNER JOIN
dbo.SA_ShipToDeliveryLocation AS st ON st.ShipToDeliveryLocationKey = ch.ShipToDeliveryLocationKey INNER JOIN
dbo.SA_FiscalPeriod AS fp ON slt.ShippedDate BETWEEN fp.PeriodStartDate AND fp.PeriodEndDate INNER JOIN
dbo.Z_SAAM_CustomerClassValues_D_I ON
c.CustomerClassDefinition1Key = dbo.Z_SAAM_CustomerClassValues_D_I.CustomerClassDefinitionKey RIGHT OUTER JOIN
dbo.Z_SAAM_GroupDesc ON i.ItemReference4 = dbo.Z_SAAM_GroupDesc.GroupCode LEFT OUTER JOIN
dbo.SA_InventoryAdjustment AS ia ON ia.ItemKey = i.ItemKey AND ia.CustomerKey = c.CustomerKey AND
ia.ShipToDeliveryLocationKey = st.ShipToDeliveryLocationKey AND ia.RegionKey = r.RegionKey AND
ia.CommissionCodeKey = cc.CommissionCodeKey AND ia.FiscalPeriodKey = fp.FiscalPeriodKey AND ia.IsApplied = 1
GROUP BY fp.FiscalYear, fp.FiscalPeriod, dbo.Z_SAAM_GroupDesc.GroupDesc, dbo.Z_SAAM_CustomerClassValues_D_I.CustomerClassValue, i.ItemReference4,
i.ItemNumber
HAVING (fp.FiscalYear = 2007) AND (NOT (i.ItemNumber = '21465P') AND NOT (i.ItemNumber = '20390P') AND NOT (i.ItemNumber = '20441') AND
NOT (i.ItemNumber = '21266P') AND NOT (i.ItemNumber = '21444-1P') AND NOT (i.ItemNumber = '21464P') AND NOT (i.ItemNumber = '21465P') AND
NOT (i.ItemNumber = '21487P') AND NOT (i.ItemNumber = '21500') AND NOT (i.ItemNumber = '21530') AND NOT (i.ItemNumber = '74025') AND
NOT (i.ItemNumber = '74029') AND NOT (i.ItemNumber = '74030') AND NOT (i.ItemNumber = '74039') AND NOT (i.ItemNumber = '74050') AND
NOT (i.ItemNumber = 'CREDIT-OVERHAUL') AND NOT (i.ItemNumber = 'HB800P') AND NOT (i.ItemNumber = 'HB900') AND
NOT (i.ItemNumber = 'HB900-01') AND NOT (i.ItemNumber = 'HB900-02'))
ORDER BY fp.FiscalPeriod, i.ItemReference4

MongoDB: Backup and Restore

Manual Methods:

Using mongodump and mongorestore

Making a backup using mongodump
- Permissions required: grant find action, backup role
- Outputs BSON file formats
- Connects to mongod or mongos instance (on port 27017 if default)
- Creates ./dump in current directory
- mongodump 2.2+ will not work with older mongod
- Command: mongodump [--host mongodb01.kimconnect.com] [--port 27017] [--collection --myCollection --db kimconnect] [--username kimconnect --password "PASSWORD"] [--oplog (point-in-time snapshot)] [--out /opt/backup/kimconnect-YYYY-MM-DD]

Restoring a backup using mongorestore
- Permissions required:
-- MongoDB 3.2.10 and earlier, requires anyAction on anyResource grants
-- MongoDB 3.2.11 and newer, restore role with --oplogReplay
- By default, ./dump is in the environmental paths
- Command: mongorestore [--host mongodb01.kimconnect.com] [--port 27017] [--username kimconnect --password 'PASSWORD'] [/opt/backup/kimconnect-YYYY-MM-DD/] [--oplogReplay (restore snapshot)] [--objcheck (checks integrity prior to commit)]

Using mongoexport & mongoimport

Using mongoexport
- Permissions required: read role
- Commands:
-- mongoexport [--db contacts --collection crm --out /opt/backups/contacts.json] [--username kimconnect --password 'PASSWORD'] [--host mongodb01.kimconnect.com --port 27017]

Using mongoimport
- Permissions required: readWrite role
- Commands:
-- mongoimport [--file /opt/backups/contacts.json] [--db contacts --collection crm] [--username kimconnect --password 'PASSWORD'] [--host mongodb01.kimconnect.com --port 27017]

Some MySQL Admin Stuff

make sure it’s running
ps -ef | grep mysql

Creating MySQL DB

HOWTO on creating MySQL DBs
mysql -u root -p
# Enter your root password
Now, inside MySQL shell
mysql>create database cafecenter;
 
Query OK, 1 row affected (0.00 sec)
mysql>show databases;
 
+———-+
| Database |
+———-+
| database |
| mysql |
| cafecenter|
| test |
+———-+
4 rows in set (0.01 sec)
 
mysql>
Create a username/password that has at least the following privileges for the database: SELECT, INSERT, UPDATE, DELETE”
mysql> use mysql
mysql> GRANT ALL ON database_name TO manager@localhost IDENTIFIED BY “Contract$$$”;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit
 
To work with a single database:
mysql> use DATABASENAME;
mysql> show tables;
mysql> select * from TABLENAME;
 
————————————-
 
Import a dump file into database after uploaded it onto the server
 
mysql -p -h localhost cafecenter < /var/www/html/install/rayzz_new.sql
 
————————————-
Useful Commands:
$ mysqldump db_name my_table
$ mysqldump db_name my_table > output.sql
$ mysqldump –no-data db_name my_table > dump_table_name.sql
$ mysqldump –add-drop-table db_name my_table > dump_table_name.sql
$ mysqladmin create db_name
$ mysqladmin drop db_name
$ mysqladmin flush-privileges
$ mysqladmin ping
$ mysqladmin reload
$ mysqladmin kill ps_id,ps_id…
$ mysqladmin –user=root shutdown
$ mysqladmin variables
$ mysqlimport db_name file_name …
$ safe_mysqld
$ myisamchk table_name.MYI

Install phpMyAdmin in Centos 5.3

# Prerequisite: must install RPMForge Repo prior
 
yum install httpd php php-mysql php-gd php-mbstring php-mcrypt mysql mysql-server phpmyadmin
chkconfig httpd on
chkconfig mysqld on
service httpd start
service mysqld start
—-
vim /usr/share/phpmyadmin/config.inc.php
—-
$cfg[‘blowfish_secret’] = ‘[whatever]’; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
—-
 
—- configure port 50000 open on firewall —
vim /etc/httpd/conf/httpd.conf
—- Add virtual website —-
Add:
Listen 50000
 
<VirtualHost *:50000>
        # Basic setup
        ServerAdmin admin@kimconnect.com
        ServerName localhost
        ServerAlias 192.168.1.80
        DocumentRoot /usr/share/phpmyadmin
    # HTML documents, with indexing.
        <Directory />
        Options +Includes
        </Directory>
</VirtualHost>
 
vim /etc/httpd/conf.d/phpmyadmin.conf
—- To allow overrides —-
AllowOverride All  //inside the brackets
Alias /phpmyadmin /usr/share/phpmyadmin
Alias /phpMyAdmin /usr/share/phpmyadmin
Alias /mysqladmin /usr/share/phpmyadmin
 
Create user root:
mysqladmin -u root password PASSWORD (mySQL ‘root’ is not the same as system root)

MySQL on Localhost

Modern versions of MySQL is memory intensive; thus, it is a pre-requisite that an adequate amount of RAM is present on a MySQL server prior to its installation. However, slimmer instances of VPS or AWS/VMWare may not have sufficient memory to run MySQL. Hence, swap files must be created to augment the modest RAM allocation in these types of machines. Below is a procedure to add swap memory on a CentOS image:
 
# Check memory & swap file
free -m
 
# Check available disk space
df -h
 
# Allocate swapfile, set appropriate permissions, create swapfile
sudo dd if=/dev/zero of=/swapfile count=4096 bs=1MiB    #allocate
chmod 600 /swapfile        #secure the directory
mkswap /swapfile        #make swapfile in the /swapfile directory
swapon /swapfile        #configure system to use /swapfile
 
# Check swapfile settings
swapon -s
 
# Make permanent
vim /etc/fstab
# add this line
/swapfile none swap defaults 0 0
 

 
Run MySQL on Localhost:
 
# Reinstall MySQL
yum remove mysql mysql-server
mv /var/lib/mysql /var/lib/mysql_old_backup
# or just delete: rm -rf /var/lib/mysql
yum install mysql mysql-server
 
# Set default password format – this must be done prior to creating any new users!
vim /etc/my.cnf
# add this line
default_authentication_plugin=mysql_native_password
 
# Set mysql to autostart
systemctl enable mysqld
systemctl start mysqld
 
# Obtain the initial password
grep ‘temporary password’ /var/log/mysqld.log
 
# Secure mysql
mysql_secure_installation
# set password
# R3sponsibility2nd5ecurity!
 
# Send command from bash terminal
mysql -uroot -ppassword -e “COMMAND1;COMMAND2;”
 
# Example
mysql -uroot -ppassword -e “ALTER USER kimconnect IDENTIFIED BY ‘5ecret0fM@na\!’;”
 
# Create database, user & grant access
mysql -uroot -ppassword  -e “CREATE DATABASE kimconnect;CREATE USER kimconnect@’%’ IDENTIFIED BY ‘password’;GRANT ALL PRIVILEGES ON kimconnect.* TO ‘kimconnect’@’%’;flush privileges;”
 
 
# Individual commands:
 
# Set password
SET PASSWORD FOR ‘kimconnect’@’%’ = PASSWORD(‘password’);
 
# Update password
ALTER USER kimconnect IDENTIFIED BY ‘password’;
 
# Grant access
mysql -uroot -ppassword -e “GRANT USAGE ON kimconnect.* TO ‘kimconnect’@’%’ IDENTIFIED BY ‘password’;”
 
# Remove user
DROP USER kimconnect;
mysql -uroot -ppassword -e “DROP USER kimconnect;”
 
# Reload accesses
flush privileges;
 
# Grant remote accesses
mysql -uroot -ppassword-e “GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ WITH GRANT OPTION;FLUSH PRIVILEGES;”
mysql -uroot -ppassword -e “GRANT ALL PRIVILEGES ON kimconnect.* TO ‘kimconnect’@’%’ WITH GRANT OPTION;FLUSH PRIVILEGES;”
 
# Check connection
mysql -ukimconnect-ppassword -e “show databases;” -h 172.17.0.x
 

Troubleshooting
 
# connect to mysqld
mysql -uroot -p
# input password
# ERROR 2002 (HY000): ‘Can’t connect to local MySQL server through socket ‘/var/lib/mysqld/mysqld.sock’ (111)’
# This could be caused by an incorrect password. If necessary, restart mysqld
systemctl restart mysqld
 
# Check password policy
SHOW VARIABLES LIKE ‘validate_password%’;
# Change any global variable:
# SET GLOBAL validate_password_length = 12;
# SET GLOBAL validate_password_number_count = 3;
# SET GLOBAL validate_password_mixed_case_count = 1;
# SET GLOBAL validate_password_special_char_count = 1;
# SET GLOBAL validate_password_policy = HIGH;
 
# Look at all the sockets
find / -type s
# Located socket at: /run/mysqld/mysqlx.sock
pkill mysqld
 
Out of memory error:
InnoDB: Fatal error: cannot allocate memory for the buffer pool
# Resolution: increase RAM or add a swap file onto the host
 
ERROR 2059 (HY000): Plugin caching_sha2_password could not be loaded: Error loading shared library /usr/lib/mariadb/plugin/caching_sha2_password.so
# Resolution: 
vim /etc/my.cnf
# add this line
default_authentication_plugin=mysql_native_password

MySQL Docker Container

################### Dockerfile Build Method ######################
mkdir /var/lib/docker/mysql && cd /var/lib/docker/mysql
vim Dockerfile

################## Dockerfile contents #####################
# Derived from official mysql image (our base image)
FROM mysql/mysql-server:latest

# Add a database
#ENV MYSQL_DATABASE kimconnect

# Set default password format - this must be done prior to creating any new users!
RUN echo "default_authentication_plugin=mysql_native_password" >> /etc/my.cnf

# Add the content of the sql-scripts/ directory to your image
# All scripts in docker-entrypoint-initdb.d/ are automatically
# executed during container startup
COPY ./scripts/* /docker-entrypoint-initdb.d/
########################################################

mkdir scripts
vim /var/lib/docker/mysql/scripts/initialize.sql
############### Insert Contents #############################
# This is the method to allow kimconnect user to connect remotely from anywhere
CREATE USER 'kimconnect'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON kimconnect.* TO 'kimconnect'@'%';
#############################################################

# Cool trick: create directory from within vim (if parent directory doesn't exist), then save
:!mkdir -p %:h
:wq

# Create Docker Image from Dockerfile and then run it
cd /var/lib/docker/mysql
docker build -t mysql .
docker run --privileged --restart always -d -p 3306:3306 --name mysql-server -e MYSQL_ROOT_PASSWORD='password' mysql

# Check logs
docker logs --tail 50 --follow --timestamps mysql-server

# Connect to Bash inside container
docker exec -ti mysql-server /bin/bash
mysql -kimconnect -ppassword

MySQL as Container in Docker

# Run MySQL Server container
mkdir /var/lib/docker/volumes/mysql_data
docker run -d --privileged -p 3306:3306 --name=mysql -v /var/lib/docker/volumes/mysql_data:/var/lib/mysql mysql/mysql-server:latest

# Run mysql-client
docker exec -it mysql_container mysql -uroot -p

# Check logs of container using Portainer GUI to find the auto-generated password

# Reset root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

# Reset accesses

FLUSH PRIVILEGES;

# This is the method to allow kimconnect user to connect remotely from anywhere
CREATE USER 'kimconnect'@'%' IDENTIFIED WITH mysql_native_password BY 'password' ;
GRANT ALL PRIVILEGES ON * . * TO 'kimconnect'@'%';

# Check MySQL logs
docker logs mysql_container


# The Oracle version of MySQL-Server
docker run --privileged --restart always -d -p 3306:3306 --name=mysql-server -v /root/mysql/scripts:/docker-entrypoint-initdb.d/ -e MYSQL_ROOT_PASSWORD='password' -e MYSQL_DATABASE=kimconnect mysql/mysql-server:latest

docker run --privileged --restart always -d -p 3306:3306 --name=mysql-server -e MYSQL_ROOT_PASSWORD='password' -e MYSQL_DATABASE=kimconnect mysql/mysql-server:latest

# Different version of mysql server
docker run --privileged --restart always
-d -p 3306:3306 --name mysql \
-v /root/mysql/scripts:/docker-entrypoint-initdb.d/ \
-e MYSQL_ROOT_PASSWORD='password' \
-e MYSQL_DATABASE=kimconnect \
mysql:latest

SQL: Exists Condition

The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:

SELECT columns
FROM tables
WHERE EXISTS ( subquery );
The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.

Example #1
Let's take a look at a simple example. The following is an SQL statement that uses the EXISTS condition:

SELECT *
FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);
This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.

Example #2 - NOT EXISTS
The EXISTS condition can also be combined with the NOT operator.
For example,

SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
This will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

Example #3 - DELETE Statement
The following is an example of a delete statement that utilizes the EXISTS condition:

DELETE FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);

Example #4 - UPDATE Statement
The following is an example of an update statement that utilizes the EXISTS condition:

UPDATE suppliers

SET supplier_name = ( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS
( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);

Example #5 - INSERT Statement
The following is an example of an insert statement that utilizes the EXISTS condition:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

SQL: DELETE FROM Statement

DELETE FROM `Profiles` WHERE Picture = '0'
--------------------------------------------------
Change password to "sexcenter"
UPDATE `Profiles`
SET Password = '5863294bde549649adab89940c3e09ea'
WHERE Password = 'pass123word'
-------------------------------

DELETE FROM `Profiles` WHERE Email = ''

-----------------------------

DELETE * FROM `Profiles`
WHERE Email != '%@%'

Gallery2 Random Highlight Mod

There's a SQL technical limitation where one may not update a table based on its nested View. Thus, the work around for that would be creating an alias table with the returns of a Select statement. Then, it would be possible to run an update on the original table based on the new alias. A demonstration is as follows:
CREATE TABLE a_random
SELECT g2_Entity.g_id, g2_Entity.g_onLoadHandlers
FROM g2_Derivative
INNER JOIN g2_ChildEntity ON g2_Derivative.g_id = g2_ChildEntity.g_id
INNER JOIN g2_Entity ON g2_Derivative.g_id = g2_Entity.g_id
WHERE
g2_ChildEntity.g_parentId IN
(SELECT g_id FROM g2_Entity WHERE g_entityType = 'GalleryAlbumItem')
AND g2_Derivative.g_derivativeType = 1;
---------------------------------------------------------------------------------------------------------------
UPDATE g2_Entity a, a_random c
SET a.g_onLoadHandlers='|RandomHighlight|'
WHERE a.g_id = c.g_id
The example above is an actual SQL statement ran against a mySQL database. The exact application is Gallery2 RandomHighlight module's recursive update on nested folders.

================================================================================================

How to do a Search and Replace in mySQL:
update `_vldmembers` set username = replace(username, '%', '_')

MySQL Update Statement

UPDATE _vldmembers_data_members
SET data_gender2=3
WHERE data_gender1=1;

UPDATE _vldmembers_data_members
SET data_state=469
WHERE data_city LIKE 'Kansas%';

UPDATE _vldmembers_data_members
SET data_state=457
WHERE data_city LIKE 'colorado%';

UPDATE _vldmembers_data_members
SET data_state=498
WHERE data_city='Houston';

UPDATE _vldmembers_data_members
SET data_state=498
WHERE data_city='Austin'

UPDATE _vldmembers_data_members
SET data_state=461
WHERE data_city='Miami';

UPDATE _vldmembers_data_members
SET data_state=461
WHERE data_city='Tampa';

UPDATE _vldmembers_data_members
SET data_state=456
WHERE data_city='Orange County';

UPDATE _vldmembers_data_members
SET data_state=456
WHERE data_city='Los Angeles';

UPDATE _vldmembers_data_members
SET data_state=456
WHERE data_city='Sacramento';

UPDATE _vldmembers_data_members
SET data_state=456
WHERE data_city='San Diego';

UPDATE _vldmembers_data_members
SET data_state=485
WHERE data_city='New York';

UPDATE _vldmembers_data_members
SET data_state=485
WHERE data_city='New York City';

UPDATE _vldmembers_data_members
SET data_country=210
WHERE data_city='London';

UPDATE _vldmembers_data_members
SET data_country=210
WHERE data_city='nottingham';

UPDATE _vldmembers_data_members
SET data_country=210
WHERE data_city='Birmingham';

UPDATE _vldmembers_data_members
SET data_country=210
WHERE data_city='Brighton';

UPDATE _vldmembers_data_members
SET data_country=210
WHERE data_city LIKE '%yorkshire';

UPDATE _vldmembers_data_members
SET data_state=464
WHERE data_city = 'honolulu';

UPDATE _vldmembers_data_members
SET data_state=468
WHERE data_city LIKE 'Iowa%';

UPDATE _vldmembers_data_members
SET data_state=456
WHERE data_city = 'San Francisco';

UPDATE _vldmembers_data_members
SET data_gender2=3
WHERE data_aboutme LIKE '%lesbian%';

MySQL: update with Join Tables

Let’s say for example you have a product table which stores information about products and a productPrice table which has pricing information and you want to update the prices based on when the product was created (in the examples below you want to discount all your older stuff to 80% of the current price).
 
In MySQL you can do this in one of two ways. The first is do do a join using commas, like so:
UPDATE product p, productPrice pp
SET pp.price = pp.price * 0.8
WHERE p.productId = pp.productId
AND p.dateCreated < ‘2004-01-01’
The second way is to use inner join syntax as shown below. This syntax is slightly more flexible as it means you can use left and right joins as well as inner joins, whereas the previous example is an implicit inner join.
UPDATE product p
INNER JOIN productPrice pp
ON p.productId = pp.productId
SET pp.price = pp.price * 0.8
WHERE p.dateCreated < ‘2004-01-01’
Another example of doing this in MySQL but using a left join to flag all the products that do not have assocated entries in the productPrice table is as follows:
UPDATE product p
LEFT JOIN productPrice pp
ON p.productId = pp.productId
SET p.deleted = 1
WHERE pp.productId IS null
 
This isn’t necessarily a very realistic example as you would be more likely to want to straight out delete the non-matching entries or use a select query instead to display them in a list for editing but it illustrates how it can be done. The article about how to delete records with MySQL using a cross join uses these examples to delete instead of update.
Note that it’s also possible in MySQL to update values in all the tables that are joined in the query. In this example we’re doing the same query as in the first two examples but also updating the “dateUpdated” value in the product table.
 
UPDATE product p
INNER JOIN productPrice pp
ON p.productId = pp.productId
SET pp.price = pp.price * 0.8,
p.dateUpdate = CURDATE()
WHERE p.dateCreated < ‘2004-01-01’
(CURDATE() is the MySQL function to return the current system date. For other date and time functions in MySQL refer to the MySQL date and time functions manual page).
 
UPDATE _vldmembers_data_members d
INNER JOIN _vldmembers members
ON d.data_id = members.member_id
INNER JOIN badrecords bad
ON members.username = bad.username
SET d.data_ethnicity = 275
WHERE members.username = bad.username;
 
SELECT * FROM _vldmembers_data_members d
INNER JOIN _vldmembers members
ON d.data_id = members.member_id
INNER JOIN asians
ON members.username = asians.data_ethnicity
 
UPDATE _vldmembers_data_members d
INNER JOIN _vldmembers members
ON d.data_id = members.member_id
INNER JOIN asians
ON members.username = asians.data_ethnicity
SET d.data_ethnicity = 274
WHERE members.username = asians.data_ethnicity;
 
UPDATE _vldmembers_data_members d
INNER JOIN _vldmembers members
ON d.data_id = members.member_id
SET d.data_ethnicity = 274
WHERE members.username LIKE ‘%asian’
 
‘asian%’
‘%azn’
‘azn%’
‘%azn%’
‘%asian%’
 
UPDATE _vldmembers_data_members d
INNER JOIN _vldmembers members
ON d.data_id = members.member_id
SET d.data_ethnicity = 274
WHERE members.username LIKE ‘%azn%’;

SQL replace character in string

update g2_Item set g_title = replace(g_title, ‘_’, ‘ ‘);
update g2_Item set g_title = replace(g_title, ‘-‘, ‘ ‘);
update g2_Item set g_title = replace(g_title, ‘.jpg’, ”);
update g2_Item set g_title = replace(g_title, ‘.JPG’, ”);
 
update _vldmembers set username = replace(username, ‘:’, ”);