‘Sexy’ Extensibility Design and Patterns

“Programming to the interface” is important when addressing extensibility.

Provider Pattern – If you are familiar with asp.net then you have been beat over the head with this pattern enough that I won’t spent much time on it. If your not familiar with asp.net, I’ll give you 50,000ft view…… it allows for abstraction of data and behavior by using an interface that the client is aware of and an external class library that contains the actual implementation of whatever you are trying to do.

Plug-Ins – This is pretty straight-forward. Plug-ins are implemented in a way similar to modules below. The real difference is that you have to explicitly iterate through a list either from a configuration file or a pre-defined directory.

Modules – This was the most interesting pattern and basically defines multiple parts of extensibility in one class. Related patterns are Chain of Responsibility, Loosely Coupled Events and Interception Filter. This methodology is event-driven and in an extremely flexible manner allows modules to hook into the events. The modules are identified to the client or web app through a configuration file and are loaded using reflection.

Man I am so behind on getting my notes and thoughts typed up...I've been to over ten sessions and I only have two typed up. I should be able to catch up this evening. Tuesday was rather uneventful as I hit a string of so-so sessions. Without further ado here is my notes for the session on Spatial Support in SQL Server 2008.....

Spatial Support in SQL Server 2008

- Spatial support is vector based and includes planar (Geometry) and geodetic (Geography)

- SQL Server was expanded to allow large UDTs

o In 2005, UDTs were limited to 5K

o In 2008, UDTs are limited only by LOB size (around 2 gigs)

- Geometry

o Adheres to OGC/ISO standards

o Can store points, lines, polygons and collections of the previously mentioned

o Methods include relationship (intersects, disjoint), construction (intersection, union) and metrics (distance, area)

o Sample:

DECLARE @g GEOMETRY

SET @g = GEOMETRY:PARSE(‘POINT(5.5 8.8)’)

- Geography

o Ellipsoidal data-type

o Does not adhere to OGC/ISO standards

o Same methods as Geometry class

- Spatial Indexes: Uses multi-level grids to map multi-dimensional data into a 2-dimension index

I am out at DevConnections this week and so far I have been impressed with the improvements and new features that are coming in the 2008 product line. I will post recaps of the sessions I attended as time allows...

- Data Warehousing in SQL Server 2008 by Christian Kleinerman – this session focused primarily on the new features and performance enhancements that were made in the SQL engine.

ETL Enhancements:

1. The new MERGE statement – this powerful new statement combines DML statements into one to allow for “UPserts” and “Deletes” between a specified source and destination.

MERGE tblDestination t

USING tblSources s

ON t.ID=s.ID

WHEN MATCHED

UPDATE SET t.Name=s.Name

WHEN NOT MATCHED

INSERT VALUES(s.Name)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

2. Logging enhancements were made to allow for minimal logging. This level of logging keeps only what is needed to successfully rollback. According to there test significant performance gains where seen with this change.

3. Insert over DML – you know have the ability to do an insert off a DML statement with the OUTPUT clause.

INSERT INTO tbl

SELECT FROM (

<>

) CHANGES

WHERE Action=’UPDATE’

4. New tabled-valued functions were attended to track and query changed data.

5. The data flow thread allocation was changed from static to dynamic assignment for operators. This improves parallelism performance and adds thread starvation prevention.

6. A new ETL lookup is being added to SSIS. The new lookup has an unrestricted cache size and the ability to persist cache.

7. Data Compression to shrink table sizes and improve query performance is being added. The compression can be configured at the table or index level and they have seen performance gains anywhere between 1.5x and 7x improvements. During this topic they Mr. Kleinerman discussed the tradeoff between memory and CPU usage when using compression. He also briefly covered how compression was implemented (I will go through later).

8. Enhancements were made to the designer to build ‘best practices’ into the tools suite.

Query and Analysis:

1. Partitioned Table Parallelism was improved through dynamic thread allocation.

2. SQL Server 2008 can support multiple bitmap filters to improve star join query processing.

3. GROUPING SETS where added to the sql toolbox

GROUP BY GROUPING SET (date, (state, city))

4. Changes were made to the MDX query processing for subspace computation. Since cubes are sparsely populated changes were made to minimize what is processed during computation.

5. The Reporting Services engine was completely rewritten for 2008. I won’t go into all the changes but there were a ton of them discussed (i.e. Enhanced visualizations, Word Rendering, etc).

Admin

1. Backups can not be run with compression

2. The entire AS backup storage system has been replaced

3. The Resource Governor basically allows workload to be differentiated and per-request limits to be set ( I plan on elaborating on this is a separate post).