Author name: Tim van Kooten Niekerk

MSSQL Snapshot Demo

/ MSSQL Server /

Snapshot demo script…

/* ##### Maak een test database... ###### */
USE [master]
GO
CREATE DATABASE [TEST1] ON  PRIMARY 
( NAME = N'TEST1', FILENAME = N'D:MSSQLSERVERTEST1.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TEST1_log', FILENAME = N'D:MSSQLSERVERTEST1_log.ldf' , SIZE = 20480KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

/* ##### Maak een tabel in de database... ##### */
USE [TEST1]
GO
CREATE TABLE [dbo].[Table_1](
	[COL1] [int] IDENTITY(1,1) NOT NULL,
	[COL2] [nchar](10) NULL
) ON [PRIMARY]
GO

/* ##### Vul de tabel met waarden... ##### */
USE [TEST1]
GO
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Oemupa3l')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Wa4ookei')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Einguix0')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('eeSh8pae')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('The1thoh')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Ba3eih6x')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Ohw8aeYi')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('ZeeC7al0')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('so8Mie3C')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('xu4ohNgo')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('aev3Iepi')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('phe1Eejo')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('aeTiu7ai')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Vee7Sux7')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('ia3aShu6')
GO

/* ##### Controleer of de tabel is gevuld */
USE [TEST1]
GO
SELECT * FROM [TEST1].[dbo].[Table_1]
GO

/* ##### Maak een snapshot van de test database... ###### */
USE [master]
GO
CREATE DATABASE TEST1_Snap ON
( NAME = TEST1, FILENAME =
'D:MSSQLSERVERTEST1_SNAP.mdf' )
AS SNAPSHOT OF [TEST1];

/* ##### Selecteer waarden uit de snapshot... ###### */
USE [TEST1_Snap]
GO
SELECT * FROM [TEST1_Snap].[dbo].[Table_1]
GO

/* ### Verwijder waarden uit de tabel... ##### */
USE [TEST1]
GO
DELETE FROM [TEST1].[dbo].[Table_1]
      WHERE COL1 = 10
GO

/* ##### Controleer of de waardes zijn verdwenen... */
USE [TEST1]
GO
SELECT * FROM [TEST1].[dbo].[Table_1]
GO

/* ##### Selecteer waarden uit de snapshot... ###### */
USE [TEST1_Snap]
GO
SELECT * FROM [TEST1_Snap].[dbo].[Table_1]
GO

/* ##### Als je zeker weet dat er geen gerelateerde gegevens zijn verdwenen ##### */
/* Set Identity insert ON */
USE [TEST1]
GO
SET IDENTITY_INSERT [TEST1].[dbo].[Table_1] ON
GO

/* Zet verwijderde record terug */
USE [TEST1]
GO
INSERT INTO [TEST1].[dbo].[Table_1] (COL1, COL2)
SELECT * FROM [TEST1_Snap].[dbo].[Table_1] 
WHERE COL1 = 10

/* ##### Controleer of de tabel is gevuld ##### */
USE [TEST1]
GO
SELECT * FROM [TEST1].[dbo].[Table_1]
GO

/* ##### Restore volledige database van snapshot... ##### */
USE [master]
GO
RESTORE DATABASE [TEST1] FROM DATABASE_SNAPSHOT = 'TEST1_Snap'
GO

MySQL Table Cleanup Procedure

/ MySQL Server /

With the following procedure, it is possible to move data from a primary table to a secondary table for archival purposes. This specific example moves data on the basis of a date column in the primary table. The number of days (related to  the current date/time) to remain in the primary table is passed to the procedure. Start procedure with ‘CALL pCleanUPTable (35)’.

DELIMITER //

CREATE PROCEDURE pCleanUpTable(IN iDays INT)

  BEGIN
    START TRANSACTION;
    SELECT (now() - interval iDays day) INTO @dtDeleteFrom;
    SAVEPOINT PreCleanUP;
    -- Copy old records to new archive/secundary  table...
    INSERT INTO sectab (primtab_id, value1, value2, value3, datum)
    SELECT id, value1, value2, value3, datum from primtab where datum < @dtDeleteFrom;
    SELECT ROW_COUNT() INTO @iRowsCopy;

    -- Remove records from primary table...
    DELETE FROM primtab WHERE datum < @dtDeleteFrom;
    SELECT ROW_COUNT() INTO @iRowsDelete;

    -- Check if rows copied -eq rows deleted...
    IF (@iRowsCopy = @iRowsDelete) THEN
      COMMIT;
      SELECT 'Success - Committed!' AS Result, @iRowsCopy AS RowsCopy, @iRowsDelete AS RowsDelete;
    ELSE
      ROLLBACK TO PreCleanUp;
      SELECT 'Failure - ROLLBACK!' AS Result, @iRowsCopy AS RowsCopy, @iRowsDelete AS RowsDelete;
    END IF;
  END //

DELIMITER ;

In bovenstaande voorbeeld is gebruik gemaakt van de volgende InnoDB tabellen.

[primtab]
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| value1 | varchar(75)      | YES  |     | NULL    |                |
| value2 | varchar(75)      | YES  |     | NULL    |                |
| value3 | varchar(75)      | YES  |     | NULL    |                |
| datum  | datetime         | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+

[sectab]
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| primtab_id | int(10)          | YES  |     | NULL    |                |
| value1     | varchar(75)      | YES  |     | NULL    |                |
| value2     | varchar(75)      | YES  |     | NULL    |                |
| value3     | varchar(75)      | YES  |     | NULL    |                |
| datum      | datetime         | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

MSSQL Query frequently used counters

/ MSSQL Server /

The query below returns a few frequently used counters. Page life expectancy, Lazy writes/sec, Target Server Memory, Total Server Memory and Processor Utilizaton. If exist PAGEIOLATCH waiting tasks are returned.

SET QUOTED_IDENTIFIER ON

-- New temp table...
CREATE TABLE #tPrefCounters ([Name] nvarchar(40),[Value] int,[Info] nvarchar(50) )
GO

-- Prepare statements...
DECLARE @H1 int
EXEC sp_prepare @H1 OUTPUT, N'@P1 nvarchar(40), @P2 nvarchar(50)', 
     N'SELECT TOP(1) @P1 AS Name, cntr_value AS Value, @P2 AS Info FROM [master].[sys].[dm_os_performance_counters] WHERE counter_name = @P1', 1

-- Execute handles and fill #tPrefCounters table...
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Page life expectancy', N'Should be -gt 300 sec per 4GB mem'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Lazy writes/sec', N'(Sample #1)'
WAITFOR DELAY '00:00:01'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Lazy writes/sec', N'(Sample #2) Should not climb much (low or zero)'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Target Server Memory (KB)', N'Optimal memory under current load'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Total Server Memory (KB)', N'Memory used by instance, should be close to target'

-- Add CPU Utilization...
INSERT INTO #tPrefCounters
SELECT TOP(1) 'Processor Utilization' AS Name, 
              x.xrecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS Value, 
			  '% CPU Utilization' AS Info
FROM ( SELECT [timestamp], CAST(record AS XML) AS xrecord FROM sys.dm_os_ring_buffers 
       WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' ) AS x
ORDER by x.[timestamp] DESC

-- Add PAGEIOLATCH wait stats...
INSERT INTO #tPrefCounters
SELECT top(5) wait_type AS Name, wait_duration_ms AS Value, 'Long waits may indicate disk io problems'
FROM [master].[sys].[dm_os_waiting_tasks] WHERE wait_type LIKE 'PAGEIOLATCH%' ORDER BY wait_duration_ms DESC

-- Unprepare statements...
EXEC sp_unprepare @H1

-- Display prefered counters...
SELECT * FROM #tPrefCounters
GO

-- Remove old temp table...
DROP TABLE #tPrefCounters
GO

 

MSSQL Query CPU Utilization

/ MSSQL Server /

Met de onderstaande oplossing kun je snel de CPU Utilization van de laatste 5 minuten ophalen (per minuut weergegeven).

SET QUOTED_IDENTIFIER ON
SELECT TOP(5) x.xrecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS CPUUTIL
FROM ( SELECT [timestamp], CAST(record AS XML) AS xrecord FROM sys.dm_os_ring_buffers 
       WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' ) AS x
ORDER by x.[timestamp] DESC

MSSQL Query SQL Server Logs

/ MSSQL Server /

De volgende uitwerking maakt gebruik van extended stored procedure xp_readerrorlog om in de SQL Server Log records te zoeken naar specifieke term(en). Alleen records van de laatste 3 dagen worden getoond (indien aanwezig in het logbestand).

DECLARE @iLogArchive int, @iLogType int 
DECLARE @vSearchString1 nvarchar(50), @vSearchString2 nvarchar(50) 
DECLARE @vSortOrder nvarchar(4) 
DECLARE @dtFrom datetime, @dtTo datetime 
SET @dtFrom = (GETDATE() -3) 
SET @dtTo = (GETDATE()) 
SET @iLogArchive	= 0		-- 0=Current, 1=Archive #1, enz.. 
SET @iLogType		= 1		-- 1=SQL Server, 2=SQL Agent 
SET @vSortOrder		= N'asc'	-- asc=Ascending, desc=Descending  
-- Set searchstrings and start procedure... 
SET @vSearchString1 	= N'Fail' 
SET @vSearchString2 	= N'' 
EXEC master.sys.xp_readerrorlog @iLogArchive, @iLogType, @vSearchString1, @vSearchString2, @dtFrom, @dtTo, @vSortOrder

KERBEROS short manual

/ MS Windows Server /

Om KERBEROS authenticatie mogelijk te maken, moet er een Service Principle Name (SPN) worden gedefinieerd. Een SPN bestaat uit een aantal componenten, namelijk: een service class (bijvoorbeeld: HTTP, MSSQLSvc of MSCRMSandboxService), een hostnaam en een poortnummer.

Het authenticatie proces verloopt als volgt. Een client vraagt een service ticket aan de Key Distribution Centre (KDC) van de domain controller. Deze zoekt  dan de SPN op in de directory. Als de SPN wordt gevonden maakt de KDC een session key aan en versleuteld deze met het wachtwoord van het service account. Vervolgens verstuurd de KDC de service ticket met daarin de session key naar de client. De client verstuurd de service ticket naar de service. De service, die onder het service account draait waaraan de SPN is gekoppeld, decodeert de session key met haar eigen wachtwoord. Zodra het decoderen succesvol is verlopen is de authenticatie gelukt.

 

Met het volgende commando registreer je een SQL Service Principle Name in Active Directory.

SetSPN -A MSSQLSvc/HOSTNAME:1433 DOMAINservice-account
SetSPN -A MSSQLSvc/HOSTNAME.fqdn:1433 DOMAINservice-account

 

Met het onderstaande commando kun je de geregistreerde SPN opvragen.

SetSPN -L DOMAINservice-account

 

Check if SQL-connection is authenticated with KERBEROS.

SELECT auth_scheme FROM sys.dm_exec_connections where session_id=@@spid

 

PowerShell Grep Recent Logfiles

/ PowerShell Scripting /

I’ve written this PowerShell script to search for specific txt in recent logfiles (changed within last 24 hours). The output is returned to standard out.

# *****************************************************************************
# Scriptnaam: CHECKLOGFILES.PS1
# Written_by: Tim van Kooten Niekerk
# Versie: 20120522B
# Info: PowerShell script to search for specific text in files modified in the last 24 hours.
# *****************************************************************************

# Functions en Procedures...
function fGetFileModTime([string]$sFileName) {
  $vFileInfo = get-childitem $sFileName
  return $vFileInfo.lastwritetime 
}

function fSendMessage([string]$sNode, [string]$sApplication, [string]$sMessage) {
  $sMessage
  # And send result to operations...
  #return Start-Process...
}

# Start Main script...
if (($args[0]) -and ($args[1]) -and ($args[2]) -and ($args[3])) {
  # Check if path exists...
  if (Test-Path $args[0]) {
    # Check for searchterm in files modified in the last 24 hours...
    [boolean]$bResultFound = $false
    [string]$sSearchTerm = $args[1]
    Get-ChildItem $($args[0]) | where {$_.LastWriteTime -gt (Get-Date).AddHours(-24)} |
      % {
	    $oResult1 = select-string -path $_.fullname -pattern $sSearchTerm -context 0 
	    if (($oResult1.Linenumber -gt 0) -or ($oResult1.Count -gt 0)) { 
	      $sResult1 = $oResult1 | format-list -property line,path,linenumber | Out-String
	      [string]$sResultMerge = [string]$sResultMerge + $sResult1
	      $bResultFound = $true
	    }
	  }
    # $sResult1 = select-string -path $($args[0]) -pattern $($args[1]) -context 0 
    # Send message when searchterm is found...
    if ($bResultFound -eq $true) {
      $sMessage1 = "Searchterm [" + $args[1] + "] found in recent logfiles... `r`n`r`nRESULT:"
      fSendMessage $args[2] $args[3] $sMessage1 
    }
  } else {
    $sMessage1 = "Error executing ... `r`n`r`nERROR: Pad niet gevonden."
    fSendMessage $args[2] $args[3] $sMessage1  
  }
} else {
  "Error executing script... `r`n`r`nERROR: Arguments missing."
  "SYNTAX: checklogfiles.ps1 `"<c:pathtodir*.log>`" `"<searchterm>`" `"<hostname>`" `"<service>`""
}

 

MSSQL List Active DBFiles

/ MSSQL Server /

Met behulp van de volgende query kun je een overzicht opvragen van alle actieve bestanden eventueel kun je een WHERE clause opnemen (WHERE type_desc = ‘LOG|ROWS’) om alleen de logbestanden of alleen de data bestanden weer te geven.

SELECT smf.physical_name, sdb.name as database_name
FROM sys.master_files smf
LEFT JOIN sys.databases sdb on sdb.database_id = smf.database_id
--WHERE type_desc = 'LOG'
ORDER BY smf.physical_name

PowerShell Check Database-backup

/ PowerShell Scripting /

This next script can be used to check if Full and Transaction Log backups are made within the last two days. Databases are read from a CSV file. I use a slightly modified version which reads databases from a registration database.

# ********************************************************************
# Scriptnaam: CHECKBACKUP.PS1
# Geschreven_door: Tim van Kooten Niekerk
# Versie: 20110811A
# Info: Check if backup is made within last two days...
# ********************************************************************

# Global config settings...
[string]$sServerInstanceFile = ".ServerInstance.csv"
[string]$sSMTPServer1 = "smtp-server-name"
[string]$sToAddress1 = "email-adsress"

# Global objects...
$oNETIP = [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()

# Register Snapins...
$oPSSnapinRegResult = PSSnapin SqlServerCmdletSnapin* -registered
if ($oPSSnapinRegResult) {
  $oPSSnapinResult = PSSnapin $oPSSnapinRegResult.Name
  if (-not $oPSSnapinResult) { 
    Add-PSSnapin $oPSSnapinRegResult.Name
  }
} else {
  break
}
  
# Functions and Procedures...
function fSendMail([string]$sSMTPSrvrName, [string]$sFrom, [string]$sTo, [string]$sSubject, [string]$sBody, [string]$sAttFileName) {
  if (($sSMTPSrvrName) -and ($sFrom) -and ($sTo) -and $sSubject) {
  
    # Declare mail objects...
    $oMessage = new-object Net.Mail.MailMessage
    $oSMTP = new-object Net.Mail.SmtpClient($sSMTPSrvrName)

    # Pass values to object...
    $oMessage.From = $sFrom
    $oMessage.To.Add($sTo)
    $oMessage.Subject = $sSubject
    if ($sBody) { $oMessage.Body = $sBody }

    # Add attachment...
    if ($sAttFileName) {
      $oAttachment = new-object Net.Mail.Attachment($sAttFileName)
      $oMessage.Attachments.Add($oAttachment)
    }

    $oSMTP.Send($oMessage)
    if ($? -eq "True") {
      return "INFO: Email has been sent."
    } else {
      return "ERROR: Error sending e-mail."
    }

  } else {
    return "ERROR: Argument(s) missing. [fSendMail `"SMTPServerName`" `"From`" `"To`" `"Subject`" (`"Body`") (`"AttachFileName`")]"
  }
}

function fSQLCmd([string]$sInstance, [string]$sQuery, [string]$sDatabase) {
  if ($sDatabase) {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -Database " + $sDatabase + " -QueryTimeout 30 -Query `"" `
                               + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  } else {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; `
                               if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  }
  return Invoke-Expression $sQuery
}

# Start Main script...
[int]$iCounter = 0
Import-Csv $($sServerInstanceFile) -header ("instance", "checkfull", "checktl") | foreach {
  # Skip header...
  if (($iCounter -ne 0) -and ($_.instance)) {
    $oResult1 = $null
    $oResult2 = $null
    # Run Queries on servers...
    if ($_.checkfull -eq 1) {
      [string]$sQuery1 = "/* ##### Begin T-SQL Query... ##### */ `
      SELECT sys.databases.name AS NoRecentFullBackup `
      FROM sys.databases `
      WHERE sys.databases.name NOT IN `
      (SELECT DISTINCT sys.databases.name from sys.databases `
      INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name `
      WHERE ((msdb..backupset.type = 'D' OR msdb..backupset.type = 'I') `
      AND msdb..backupset.is_copy_only = 0 `
      AND msdb..backupset.backup_start_date > (GetDate() -2))) `
      AND sys.databases.state = 0 `
      AND sys.databases.name  'tempdb' `
      /* ##### Einde T-SQL Query... ##### */"
      # Forceer in een object zodat we altijd kunnen tellen...
      [Object[]]($oResult1) = fSQLCmd $($_.instance) $sQuery1 "master"
    }


    if ($_.checktl -eq 1) {
      [string]$sQuery2 = "/* ##### Begin T-SQL Query... ##### */ `
      SELECT sys.databases.name AS NoRecentTLBackup `
      FROM sys.databases `
      WHERE sys.databases.name NOT IN `
      (SELECT DISTINCT sys.databases.name from sys.databases `
      INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name `
      WHERE (msdb..backupset.type = 'L' AND msdb..backupset.backup_start_date > (GetDate() -2))) `
      AND sys.databases.recovery_model != 3 and sys.databases.state = 0 `
      /* ##### Einde T-SQL Query... ##### */"
      # Forceer in een object zodat we altijd kunnen tellen...
      [Object[]]($oResult2) = fSQLCmd $($_.instance) $sQuery2 "master"
    }

    # Zodra er rows worden geteld een e-mail samenstellen...
    if (($oResult1.Count -gt 0) -or ($oResult2.Count -gt 0)) {
      $oResult1 = $oResult1 | Out-String
      $oResult2 = $oResult2 | Out-String
      [string]$sFromAddress1 = "noreply@" + $oNETIP.HostName + "." + $oNETIP.DomainName
      [string]$sBody1 = $oResult1 + $oResult2
      [string]$sSubject1 = $($_.instance.Replace("", "_")) + ":Database backup older than 2 days..."
      fSendMail $sSMTPServer1 $sFromAddress1 $sToAddress1 $sSubject1 $sBody1
    }
  }
  $iCounter++
}
# ********************************************************************

CSV File format:

instance,checkfull,checktl
SERVERA,1,1
SERVERBINS1,1,1
SERVERC,1,1