MCITP : Business Intelligence Developer 2008

Passed the BI MCITP Exam today. Am very relieved as it was the toughest of all the exams.

Am now 3 x MCITP in SQL 2008 Administration, Development and Business Intelligence :)

MCTS : SQL Server 2008, Business Intelligence Development and Maintenance

Just a quick note to say I passed 70-448 : Microsoft SQL Server 2008, Business Intelligence Development and Maintenance.

Not looking forward to the MCITP mind, that will be tough..

r

SQL Server 2008 Editions and Downscaling

l'm having to look at downscaling the SQL infrastructure.

Not surprisingly, the cost of Enterprise edition is a large issue for a small company. Expecially when you factor number of processors and the need for multiple environments. Consolidation and downscaling are therefore the order of the day.

Cost aside, what FUNCTIONALITY will I stand to lose? (and miss, sob...)

1. Data & Index Compression
2. Backup Compression (although this is available in the STANDARD edition of SQL 2008 R2)
3. Table Partitioning
4. Online Index Rebuilds
5. Resource Governor

ref : Features Supported by the Editions of SQL Server 2008

Quindecillion

Am currently using a SHA-1 hash generating function to generate unique indexes over large text columns.
The NVARCHAR(1024) columns I am encoding could cost as much as 2048 bytes each to store before compression (as they’re UNICODE).
The idea is to reduce my storage and to be able to index large text columns using HASHBYTES. The function allows me to use VARBINARY(20) to hold the Hash calculation. 20 bytes being a lot more friendly than 2048.

Links :
1) SHA Hash functions
2) MSSQLTips.com : Unique constraints for large text columns (using hashbytes)

To quote that article,

The odds of a duplicate hash value being generated are 1 in 1,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,00

1 in 1,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 is ‘one in a quindecillion’.

Here’s how I found out this amazing fact...
Really Big Numbers

(you can all go back to sleep now...)

SQL 2008 : Adding DATE and TIME back to DATETIME

SQL 2008 has DATE and TIME as separate data types at last.
But what if you need to fit them back together as a DATETIME?

SELECT CAST(CAST(mydate AS DATE) AS DATETIME) + CAST(mytime AS TIME)
FROM  [dbo].[mytable]

I'm sure there are other ways, I'd be interested to hear them, and any performance differences they provide!

Ref :
http://stackoverflow.com/questions/700619/combine-the-date-in-one-field-with-time-of-another-field-ms-sql-server

Notes : Non-yielding IOCP Listener error

Following a colleague running a heavy query via a linked server and killing the process on the remote machine, we've had SQL becoming non reponsive and I had to restart the server (the services individually failed to restart).

The error is 'Non-yielding IOCP Listener error' and followed some memory issues.

I'm following recommendations in these links and am implementing the changing both the Min and Max SQL memory settings to the same (2GB less than the machine capacity).

http://blogs.msdn.com/grahamk/archive/2008/09/11/non-yielding-iocp-listener-stack-dump-analysis.aspx

http://204.9.76.233/Community/forums/p/24993/139079.aspx#139079

(Admittedly I should have done this years ago but with 32GB to play with on the box, I've never come close to investigating any memory problems.)

SQL 2008 : Management Studio Error : Saving Changes in not permitted.

The error >

Saving Changes in not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

How to resolve >

Link : SQLServerPedia

Enabling Data Compression in Scripts generated from Management Studio 2008

1) On the Management Studio menu bar, navigate to 'Tools > Options'

The following menu then appears >


2) Expand 'SQL Server Object Explorer' , click 'Scripting'
3) Scroll down to 'Object Scripting Options' and change 'Script Data Compression Options' to TRUE


Useful link :

SQL 2008 Management Studio - Scripting Improvements

Bookmark : SQL 2008 Diagnostic Information Queries

Glenn Berry has updated his SQL Server 2008 Diagnostic Information Queries

Well worth a look...

SQL Server 2008 Diagnostic Information Queries

SQL 2008 Management Datawarehouse : CU 5 Fix

I wasted a lot of time originally trying to get the SSIS data collectors functioning consistently in the MDW.

Therefore i'm happy to see this fix...

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/e1aa9f08-2239-4c0b-b902-b19b8693a799

MCITP : SQL 2008 Database Developer

Following passing my MCTS on Monday , I passed MCITP Database Developer 2008 (exam 70-451) this afternoon.

Very relieved to get my evenings back. :)
r

MCTS : SQL 2008, Database Development

In Septembar I gained my MCTS & MCITP in SQL Server Administration.

This afternoon I'm happy to say I got MCTS Database Development, exam 70-433.

r

Mega Patch Tuesday - 8 Critical Updates

Well I now know what I'll be doing next week...

SQL 2008 : Sparse Columns

If a column is sparsely (rarely) populated, you can benefit by declaring it as SPARSE.
By declaring columns where the majority of data is NULL as 'sparse' you can save storage space.

The Technet Explanation of Sparse column functionality.

As ever, Pinal Dave has investigated Sparse columns fully.

Minimising Data Compression Rebuild Time

ALTER TABLE REBUILD WITH(DATA COMPRESSION=PAGE,MAXDOP=8)

Implementing table compression use server MAXDOP (sp_configure 'max degree of parallelism') if you dont set MAXDOP in statement.

A good explanation of page compression and the MAXDOP setting is here >
http://sqlblog.com/blogs/linchi_shea/archive/2008/05/05/sql-server-2008-page-compression-using-multiple-processors.aspx

SQL 2008 : High Availability Whitepaper

Just out, a whitepaper on achieving High Availability in SQL 2008

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


Looking at the feature table, i'm glad I'm running Enterprise edition!

MCITP : Database Administrator 2008

Following passing my MCTS on Monday , I passed MCITP Database Administrator 2008 this morning.

Contemplating the development track (70-433 & 70-451) next month to bring me up to date.

r

MCTS : Database Administration 2008

Just a quick note to say I passed 70-432 : Microsoft SQL Server 2008, Implementation and Maintenance yesterday.

I used the MCTS Self-Paced Training Kit (Exam 70-432) if you want to do the same.

r

SQL 2008 : Using MultiServer Queries

In SQL 2008 you can run a query against multiple servers at the same time by opening a query window against a server group rather than an individual server.

To demonstrate, a query that fetches data about a server -

SELECT
  TOP 1 BACKUP_START_DATE AS LAST_BACKUP
, @@VERSION AS SQL_VERSION
, CREATE_DATE AS LAST_STARTUP
, DATEDIFF(D,CREATE_DATE,GETDATE()) AS DaysUptime
FROM MSDB.DBO.BACKUPSET (NOLOCK)
CROSS JOIN SYS.DATABASES (NOLOCK)
WHERE SYS.DATABASES.NAME = 'TEMPDB'
ORDER BY BACKUP_SET_ID DESC

By executing against the group we get it executed against each server, hence a row for each >

SQL 2008 : Apply Compression to all Tables and Indexes

Apply Compression to all Tables and Indexes
(example uses Page Compression)

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' FROM sys.objects where TYPE = 'u'
UNION
SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' FROM sys.objects where TYPE = 'u'