SQL 2008 : Backup Compression

A little adventure in testing SQL 2008 Backup Compression.

Firstly, backing up a 10GB database normally -

BACKUP DATABASE [Track] TO  DISK = N'd:\backuptemp\TrackBAK'
WITH NOFORMAT, NOINIT,  NAME = N'Track-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

It took 3m 25s >

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 1276080 pages for database 'Track', file 'Track' on file 1.
100 percent processed.
Processed 1 pages for database 'Track', file 'Track_log' on file 1.
BACKUP DATABASE successfully processed 1276081 pages in 205.729 seconds (48.458 MB/sec).

Now, I'll backup the same database, but this time WITH COMPRESSION -

BACKUP DATABASE [Track] TO  DISK = N'd:\backuptemp\TrackCompressed.BAK'
WITH NOFORMAT, NOINIT,  NAME = N'Track-Compressed Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

It took 1m 34s >

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 1276080 pages for database 'Track', file 'Track' on file 1.
100 percent processed.
Processed 1 pages for database 'Track', file 'Track_log' on file 1.
BACKUP DATABASE successfully processed 1276081 pages in 94.380 seconds (105.630 MB/sec).


Overall SQL 2008 achieved compressing the original to 29% of the full backup's size.
Full Backup : 10,209,369 KB
Compressed backup : 2,977,141 KB

WHILE Loop to break up a larger update/delete job

Using a WHILE loop and TOP predicate to break an UPDATE job into smaller steps.

I use this all the time for DELETEs and UPDATEs so I thought it about time I put it here...

This approach avoids table locks by breaking down the task into trasactional parts...

DECLARE @Counter bigint
DECLARE @CounterText varchar (100)
SET @Counter = 0

WHILE  (SELECT COUNT(*) FROM dbo.tableToUpdate (NOLOCK) WHERE [_processdatetime] IS NULL) > 0 
BEGIN

SET @Counter= @Counter + 1
SET @CounterText = + CONVERT (CHAR (20), @counter, 8)
RAISERROR (@CounterText , 10, 1) WITH NOWAIT 

CHECKPOINT

UPDATE TOP (1000) dbo.tableToUpdate
  SET [_processdatetime] =  GETDATE()
    FROM dbo.tableToUpdate WHERE [_processdatetime] IS NULL
END
DECLARE @Counter bigint
DECLARE @CounterText varchar (100)
SET @Counter = 0

WHILE(1=1)
BEGIN

SET @Counter= @Counter + 1
SET @CounterText = + CONVERT (CHAR (20), @counter, 8)
RAISERROR (@CounterText , 10, 1) WITH NOWAIT 
CHECKPOINT
DELETE TOP(5000) FROM myTable WHERE dateField < ’20100318′
IF @@ROWCOUNT < 1 BREAK
END

Link : SQL Tips - Quick Delete for huge tables

SQL Server 2008 : Script for MySql 5.1 Linked Server

I blogged a few days ago about creating a linked server to MySql from MS Sql 2008.
Further to that post, here is a script that does the steps for you.

Script for Configuring MySql ODBC link from SQL 2008 -

EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL', @srvproduct=N'mysql', @provider=N'MSDASQL', @datasrc=N'mysqldsn', @provstr=N'DRIVER={MySQL ODBC 5.1 Driver};DSN=mysqldsn;SERVER=172.16.0.9;DATABASE=MySqlDatabaseName;UID=remote_user;password=remote_password;'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYSQL',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYSQL',@useself=N'False',@locallogin=N'mysql_link',@rmtuser=N'remote_user',@rmtpassword='remote_password'

GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

SSIS Error : type_e_libnotregistered

Attempting to view SSIS packages on a local sql server I was presented with this error after applying SQL 2005 SP3 >

" Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED)) (Microsoft.SqlServer.DTSRuntimeWrap) "

Run these 2 commands to re-register SSIS DLLs...

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\dts.dll

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\MsDtsSrvrUtil.dll

Windows Activation Error (0x8004FE33)

Failed to activate with error 0x8004FE33
Failed to acquire a Confirmation ID with error 0x8004FE33

Basically our proxy requires authentication and was preventing activation.

These urls had to be excluded from unauthentication on the the proxy server (ISA) so that the certificate revocation lists could be accessed. (We allowed both HTTP & HTTPS).

crl.microsoft.com/pki/crl/products/MicrosoftRootAuthority.crl

crl.microsoft.com/pki/crl/products/MicrosoftProductSecureCommunications.crl

microsoft.com/pki/crl/products/MicrosoftProductSecureCommunications.crl

crl.microsoft.com/pki/crl/products/MicrosoftProductSecureServer.crl

microsoft.com/pki/crl/products/MicrosoftProductSecureServer.crl

activation.sls.microsoft.com:443

Developer Permissions TSQL

to view activity monitor >
USE MASTER; GRANT VIEW SERVER STATE TO [username]

to run profiler >
USE MASTER; GRANT ALTER TRACE TO [username]

to view execution plans >
USE DBNAME; GRANT SHOWPLAN TO [username]

BI Explained

with a little (american) humour...

http://blogs.msdn.com/bi/archive/2009/03/22/history-of-business-intelligence.aspx