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

SERVERPROPERTY

Shows SQL version properties...

SELECT SERVERPROPERTY ('productversion')
      ,SERVERPROPERTY ('productlevel')
      ,SERVERPROPERTY ('edition')

10.0.1779.0 RTM Enterprise Edition (64-bit)

MAXDOP - Maximum Degree of Parallelism

Symptoms : CXPACKET and PAGEIOLATCH_SH wait types in Activity Monitor.

Solution : Adjust MAXDOP (the Maximum Degree of Parallelism) at a server level (see below) or use the OPTION (MAXDOP) query hint.

Reason : CXPACKET means processors are waiting for each other to finish tasks that are running in parallel. Setting MAXDOP to 3 on a 4 processor box means parallel processes cannot consume all resources in this way i.e. one processor is left to serve other queries.





Links :

Sql-server-Pro : Max degree of parallelism - When to change itJonathan Kehayias : Tuning the cost threshold of parellism

From the comments on Jonathan's post - Paul White’s suggestions for OLTP scenario...

Something that can work well in many environments, assuming a primarily OLTP-type workload:


1. Server-wide setting to MAXDOP = 1


2. Cost threshold set to zero


3. Use the MAXDOP = N query hint on code that benefits from parallelism (even if it's just MAXDOP 2 to allow bitmap operators).

TSQL : Modifying Computed Column

You can't! Sorry... (as of sql 2005, 2008)

Drop the column and add it again, like this >

USE Adventureworks
go
ALTER TABLE Person.Contact
DROP COLUMN [RSVersion] 
go

ALTER TABLE Person.Contact
ADD RSVersion  AS 
(CHECKSUM(ContactID
,NameStyle
,Title
,FirstName
,MiddleName
,LastName
,Suffix
,EmailAddress
,EmailPromotion
,Phone
,PasswordHash
,PasswordSalt) ) PERSISTED NOT NULL

go

Links :
CHECKSUM as a computed column
MSDN Forums

Table Size

Reminder to self... How large is that table?

EXEC sp_spaceused N'SchemaName.TableName'

Here's a previous script to show All table sizes

SQL Server 2008 : MySql 5.1 Linked Server

1 ) Download & Install the ODBC driver.
It needs to be installed on the instance where SQL Server is installed.

http://dev.mysql.com/downloads/connector/odbc/5.1.html

2) Create a system DSN (data source name).
i. Start > Control Panel > Administrative Tools > Data Sources (ODBC)



ii. Click Add, select mySQL ODBC driver, click Finish >


iii. Enter server details and connection parameters >


iv. Click OK, and the DSN has been created >



v. Click Configure, and adjust the settings on the 'Flags 1' tab >



vi. Click the 'Flags 2' tab and select the following >



vii. Click the 'Flags 3' tab and select the following >



viii. Click the 'Test' button to verify connectivity.


3) Create a linked server in SQL Server.

Update 30/03/2008 - Steps are given below, or if you want to use a script, theres a template here.

else, the steps are >

i. Expand Linked Servers (under ServerName > Server Objects)



ii. Right click 'Linked Servers' to add a new one and enter details to match the DSN you created. >



iii. Provide an appropriate connection string on this screen too >
Driver={MySQL ODBC 5.1 Driver};Server=172.16.0.9;Database=dbname;User=username;Password=password;


iv. Provide Security details on the security tab.
Map a local sql user (previously created) to the remote user.



v. Change provider properties for compatibilty.
It is called MSDAsql and is located here >



vi. Set the provider properties >


4) Enable OpenRowset / OpenDataSource

Either using the interface > Enabling OpenRowset in SQL 2005
or via commands >

sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
go

5) Fetch the data!

SELECT * FROM OPENQUERY(mysql, 'SELECT * from tablename limit 10;')

Copy only today's backups

Batch file to achieve this >

set dd=%date:~0,2%
set mm=%date:~3,2%
set yy=%date:~6,4%

set today=%mm%-%dd%-%yy%

md x:\sqlbackup


del x:\*.* /s/q
echo %date% - %time% - copying database backups >> x:\Backuplog.txt
echo %date% - %time% - copying database backups
xcopy e:\sqlbackup\*.* x:\sqlbackup /D:%today% /r/v/y/s

This was helpful >
String Manipulation in Dos Batch files

Tweet SQL

Tweeting programatically from SQL Stored Procedures...

http://www.tweet-sql.com/

New Server Consolidation White Paper

March 2009 : Technical White Paper

SQL Server Consolidation at Microsoft

http://technet.microsoft.com/en-us/library/dd557540.aspx

Improving SQL Performance

Excellent document on MSDN on Improving SQL Performance...

http://msdn.microsoft.com/en-us/library/ms998577.aspx


Found it via Jose Barreto's Blog (full of excellent SQL content btw) ...

http://blogs.technet.com/josebda/archive/2009/03/19/book-chapter-on-improving-sql-server-performance.aspx

Tool : HD_Speed

Simple tool that is free (we like free)


http://www.steelbytes.com/?mid=20

http://lifehacker.com/5166662/hd_speed-benchmarks-your-disks-and-drives

SQL 2008 : Deprecated Features

Does your sql code use deprecated features?

SQL 2008 can let you know...

select * from sys.dm_os_performance_counters
where object_name like '%Deprecated Features%'

Reporting Requirements

Herding Cats - Dealing with open ended reporting requirements

Excellent article. I can just see this coming when exposing a reports model to SQL Report Builder >
http://sqlserverpedia.com/blog/sql-server-bloggers/herding-cats-dealing-with-open-ended-reporting-requirements/

SSIS 2008 : 1TB in 30 minutes

MSDN Publishes details of last year's ETL World record using SSIS 2008.

Visio 2007 : Reverse Engineering a SQL Server Database

1) Create Database Diagram >


2) Reverse Engineer >


3) Select the database >



4) Select type of objects to represent in the diagram >


5) Select the objects themselves >


6) Create diagram automatically now?


7) Review selections >


8) Screenshot of the result >



9) The diagram >

.