Day: October 24, 2012

Encrypting Sql Server Backups using 7-Zip & Powershell

If like me, you’re moving off using litespeed compression to using native Sql Server 2012 compression, there is one feature that you’re likely to miss; encryption.  Here is how you can use 7-zip via powershell to implement encryption (the default encryption for 7-zip is AES-256).

if (-not (test-path "$env:ProgramFiles\7-Zip\7z.exe")) {throw "$env:ProgramFiles\7-Zip\7z.exe needed"}
set-alias sz "$env:ProgramFiles\7-Zip\7z.exe"

$path = "C:\PathToBackupFolder"
$password = "TestPassword"

$files = gci -Path $path | where{$_.Extension -eq ".bak"}
foreach($file in $files)
{
	$NewFile = "$path\$file" -replace ".bak",".bak.7z"
	$cmdOutput = sz a -tzip "$NewFile" $file.FullName -p$password
	$cmdOutput[8]
	if($cmdOutput[8] -eq "Everything is Ok")
	{
		Remove-Item -Path $file.FullName
	}
	else
	{
		Send-MailMessage -To "whomever@wherever.com" `
		-Subject "Error in compressing file on ServerName" `
		-Body "There was an error in compressing the file <b>$file</b> on ServerName.  Please look into it." `
		-SmtpServer "mail.wherever.com" `
		-From "server@reedbusiness.com" -BodyAsHtml
	}
}

Identity Bug in Sql Server 2012

Apparently this bug has been hanging around since April 2012, but I only heard about it this morning.

IF OBJECT_ID('TestTable') IS NOT NULL
BEGIN
	DROP TABLE dbo.TestTable
END
GO

CREATE TABLE dbo.TestTable(
	ID		INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	Value	VARCHAR(1000) NOT NULL
)
GO

INSERT INTO dbo.TestTable(Value)
SELECT 'Test'
GO 101

checkpoint
--RESTART SQL INSTANCE

INSERT INTO dbo.TestTable(Value)
SELECT 'Test'
GO 101

SELECT * FROM dbo.TestTable

Now look at the IDENTITY values for the rows:

clip_image002[5]

Skips upwards by 1000.  Scary.

There is a connect item for this located here.  Please vote!  The connect item states that it is related to AlwaysOn.  This is not true.  This bug occurs on any restart of the sql service.

A work-around for this is to make sure to checkpoint the database before a restart, but obviously it’s not the best work-around as you don’t always control the failover (HA).  All that being said, if you checkpoint before a restart, you’ll not see the identity bug:

clip_image002[7]

 

Also, I’ve just installed Sql Server 2012 SP1 CTP and the bug still exists.