MSSQL Exchange names 2 databases

/ MSSQL Server /

Using the following Stored Procedure you can swap the names of two databases. For example, to bring a recently updated database online, after which the old database can be filled again. To exchange database names, a third name is required. This is the name of database2 with the addition ‘_OLD’.

/****** Object:  StoredProcedure [dbo].[DbNameExchange]    Script Date: 12/01/2012 09:15:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ==============================================================
-- Author:		Tim van Kooten Niekerk
-- Create date: 2012-12-01-A
-- Description:	Exchange database names between 2 databases...
-- ==============================================================

CREATE PROCEDURE [dbo].[DbNameExchange]
    @vDatabase1 nvarchar(256),
    @vDatabase2 nvarchar(256)
    
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @H1 AS int
  DECLARE @vDatabaseT AS nvarchar(256)
  DECLARE @vQuery1 AS nvarchar(512)

  -- T.b.v. wisselen van database namen is er een derde naam nodig...
  SET @vDatabaseT = @vDatabase2 + N'_OLD'

  -- Check rename action prefered state before rename action 1/3...
  IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
     AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
     AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 0
  BEGIN
    -- Geef de secundaire db een tijdelijke naam...
    BEGIN TRY
      SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase2 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;WAITFOR DELAY ''00:00:05'';ALTER DATABASE [' + @vDatabase2 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabase2 + '] MODIFY NAME = [' + @vDatabaseT + '];ALTER DATABASE [' + @vDatabaseT + '] SET MULTI_USER'
      EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
    END TRY
    BEGIN CATCH
    END CATCH
    -- Check rename action prefered state before rename action 2/3...
    IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
      AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 0
      AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 1
    BEGIN
      -- Geef de primaire db de naam van de secundaire db...
      BEGIN TRY
	    SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase1 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;WAITFOR DELAY ''00:00:05'';ALTER DATABASE [' + @vDatabase1 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabase1 + '] MODIFY NAME = [' + @vDatabase2 + '];ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
      END TRY
      BEGIN CATCH
      END CATCH
  	  -- Check rename action prefered state before rename action 3/3...
      IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 0
        AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
        AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 1
      BEGIN
        -- Geef de db met de tijdelijke naam de naam van de primaire db...
        BEGIN TRY
	      SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase1 + '];ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
          EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
        END TRY
	    BEGIN CATCH
        END CATCH
	    IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
          AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
          AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 0
	    BEGIN
	      PRINT 'INFO: Rollout actie correct uitgevoerd...'
	    END
	    ELSE BEGIN
          PRINT 'ERROR: Rollout actie is niet volledig uitgevoerd, probeer het nomaals...'
	      BEGIN TRY 
	        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase1 + '];ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
            EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
          END TRY
	      BEGIN CATCH
          END CATCH
          -- Niet gelukt, dan in ieder geval multi_user activeren...
		  SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET MULTI_USER'
          EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
	    END
	  END
    ELSE BEGIN
	    -- Something went wrong trying to rollback...
	    PRINT 'ERROR: Fout geconstateerd, probeer terug te draaien...'
        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase2 + '];ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
  	  END
    END
    ELSE BEGIN
    -- Something went wrong trying to rollback...
    PRINT 'ERROR: Fout geconstateerd, probeer terug te draaien...'
    SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
    EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
    END
    -- Unprepare statements...
    EXEC sp_unprepare @H1
  END
  ELSE BEGIN
    PRINT 'ERROR: Startsituatie niet correct...'
  END
END

GO

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>`""
}