A small PowerShell function for unzipping a file.
function fUnzip([string]$sZIPFileName, [string]$sExtractToDir) {
$oShell = new-object -com shell.application
$oZIPFile = $oShell.namespace("$sZIPFileName")
$oExtractTo = $oShell.namespace("$sExtractToDir")
$oExtractTo.Copyhere($oZIPFile.items())
}
Server STUNNEL config…
;debug = 7
;output = stunnel.log
cert = HU-TEST-SERVER.pem
verify = 3
CAfile = HU-TEST-KEYSTORE.pem
[test]
accept = 7080
connect = fq.host.name:7080
The PowerShell function example below can be used to transmit information using the TCP protocol. For example, to perform an HTTP GET command on a website.
function fnTCPSock([string]$sHostName, [string]$sPort, [string]$sScript) {
# Converteer script string to byte for write stream...
[byte[]]$byteScript = [System.Text.Encoding]::ASCII.GetBytes($($sScript))
# Open 4096 Bytes buffer for read stream...
[byte[]]$byteBuffer = New-Object System.Byte[] 16384
# Connect to host...
$oTCPSock = New-Object System.Net.Sockets.TcpClient($sHostName, $sPort)
$oStream = $oTCPSock.GetStream()
# Feed script(byte) tot write stream...
$oStream.Write($byteScript, 0, $byteScript.Length)
# Return read buffer converted to string...
[string]$sCount = $oStream.Read($byteBuffer, 0, 16384)
return [System.Text.Encoding]::ASCII.GetString($byteBuffer, 0, $sCount)
# Close objects...
$oResult.Close()
$oSockTCP.Close()
}
[string]$sScript1 = "GET / HTTP/1.1`r`nAccept: text/html`r`nHost: [virtualhostname]`r`nReferer: http://host.domain.name/site/`r`n`r`n`r`n" [string]$sResult1 = fnTCPSock "hostname" "80" $($sScript1)
MSSQL table switch IN, switch OUT examples…
-- Switch in/out procedure examples...
-- Tim van Kooten Niekerk
-- Partition function en schema maken...
--DROP PARTITION FUNCTION pfTable_1
--DROP PARTITION SCHEME psTable_1
CREATE PARTITION FUNCTION pfTable_1 (int) AS RANGE LEFT FOR VALUES (2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)
CREATE PARTITION SCHEME psTable_1 AS PARTITION pfTable_1 ALL TO ([PRIMARY])
-- Make table with a partition funciton on column jaar (year)...
CREATE TABLE [dbo].[Table_3](
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [nchar](10) NULL,
[jaar] [int] NOT NULL
) ON [psTable_1]([jaar])
-- Check partitioned table...
SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Table_3') ORDER BY partition_number
-- Make Switch OUT and truncate table so you can reload 2015...
-- Make Switch OUT table without constraints...
CREATE TABLE [dbo].[Table_3_OUT](
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [nchar](10) NULL,
[jaar] [int] NOT NULL
)
ALTER TABLE Table_3 SWITCH PARTITION 5 TO Table_3_OUT
TRUNCATE TABLE [dbo].[Table_3_OUT]
-- Now you can reload (INSERT) new values for 2015 in the original table...
-- Or... Make Switch IN with using a switch IN table for 2015...
-- Switch IN table with constraint on jaar = 2015...
CREATE TABLE [dbo].[Table_3_IN](
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [nchar](10) NULL,
[jaar] [int] CHECK (jaar = 2015) NOT NULL
)
-- INSERT new values in switch IN table and make switch...
ALTER TABLE Table_3_IN SWITCH TO Table_3 PARTITION 5
-- Extend partition function with a new year...
ALTER PARTITION SCHEME psTable_1 NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION pfTable_1 () SPLIT RANGE (2021)
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
A number of useful stored procedures related to users and roles within databases.
EXEC sp_HelpSrvRoleMember EXEC sp_HelpLogins EXEC sp_HelpUser EXEC sp_HelProtect EXEC sp_HelpRoleMember
Create a login (QuickRef).
CREATE LOGIN [(DOMAIN\)User] (FROM WINDOWS) WITH (PASSWORD = 'A34esdjdawki^tW4', ) DEFAULT_DATABASE = [DatabaseName]
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
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 | | +------------+------------------+------+-----+---------+----------------+
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
A prepare and execute example. It prepares a statement with one variable (@P1). The returned handle (@H1) is executed.
-- Prepare statement... DECLARE @H1 int EXEC sp_prepare @H1 OUTPUT, N'@P1 nvarchar(25)', N'SELECT @P1 AS StmtExecute', 1 --SELECT @H1 -- Execute handle... EXEC sp_execute @H1, N'Succes' -- Unprepare statement... EXEC sp_unprepare @H1