Mat Stephen's SQL Server WebLog

Free SAP Customer Workshops "How Microsoft Runs SAP" Munich on February 7, 2006 and in Brussels on February 9, 2006.

Mat_Stephen — Wed, 04 Jan 2006 18:37:00 GMT

These courses rarely come to Europe - so if you're interested be quick!

From the blurb:-

We would like to invite you to attend the upcoming SAP Customer Workshop about How Microsoft Runs SAP. The same session will be held in Munich on February 7, 2006 and in Brussels on February 9, 2006.

The heart of Microsoft's worldwide operations runs on SAP. Please choose the most convenient location for you and join us for the day to hear from our SAP Services team experts about how they run and manage a global deployment of SAP across 89 countries and for 60,000 employees. This real world, non-marketing workshop is designed to give you insight into Microsoft's experiences choosing, implementing, rolling out, and maintaining our global SAP system. Both the technical aspects and the business/functional aspects of the implementation will be addressed.

For a preview of Microsoft's deployment of SAP, please read the case study at:
http://www.microsoft-sap.com/casestudies/Microsoft_Case_Study.doc

Enterprise customers considering SAP will want to send their business and IT decision-makers. SAP customers and partners implementing R/3 will benefit from Microsoft's technical, business and project management key learnings. The content of this seminar-style workshop will cater to both business and technical participants. In past workshops, customers have also found this to be a good opportunity to exchange experiences with other workshop participants.

   • Keynote: SAP, The Software That Drives Microsoft
   • Best Practices: Change Control & Leveraging Technology
   • Technical Architecture
   • SAP BW and Reporting Strategy
   • SAP .NET Extensions and Mendocino

February 7, 2006:

Kempinski Hotel Airport München
Terminalstrasse Mitte 20
85356 München

Tel. +49 89 97 82 36 40
Fax. +49 89 97 82 36 13

February 9, 2006:

Sheraton Brussels Hotel & Towers
Manhattan Center
3 Place Rogier
1210 Brussels, Belgium

Phone (32) (2) 2243111
Fax (32) (2) 2243456

Registration is limited to 150 people on a first-come first-serve basis, so please register early!

To register visit:

https://www.wellsregister.com/sap/customer/cityselect.asp

Phone: +1 206-706-3225
E-mail: SAP@WellsRegister.com

Business casual.

This is a free all-day workshop. Registration and cancellation are mandatory.

We look forward to seeing you in Munich or Brussels !

SQL 2005 Metadata Samples Toolkit and whitepaper now available

Mat_Stephen — Tue, 03 Jan 2006 20:14:00 GMT

Reposted 10/1/06 to include in 'free tools' category

I'm constantly asked "what's Microsoft doing about metadata and do we have a solution."

In the old days (SQl 2000 and 7), Microsoft provided Meta Data Services, a platform designed to help tools and applications manage metadata and information models more effectively. It included the Open Information Model (OIM). A core model of sharable and reusable type descriptions. Microsoft worked with the Meta Data Coalition to publish the Open Information Model as an industry metadata model standard. Initially it was warmly received. However, soon after, a second set of companies in the Object Management Group proposed the Common Warehouse Metamodel (CWM) as a second standard for metadata. Metadata was everywhere!

But the excitement over cross-industry metadata models eventually wore off. For a variety of reasons, ISVs started to move away from industry-wide models and focused on point-to-point mappings between products (primarily focusing on XML-based exchange). The Meta Data Coalition merged with the OMG and cross-industry metadata modeling efforts have been stalled for several years. The world discovered Enterprise repositories and cross-product metadata models are very hard to integrate into existing computing environments (lots of custom applications and tools) and require lots of care and feeding for effective deployment and maintenance (i.e. lots of consulting).

So, Meta Data Services was deprecated in SQL 2005.

With SQL Server 2005 XML is every where, its used to describe all the really useful objects in the product stack. Consequently the possibilities to satisfy many of the needs, that metadata repositories are touted to solve, can now be easily solved by reporting off the XML that describes so many of the key components used in (Microsoft SQL Server) data warehousing (ie used by SSIS, SSRS and SSAS). The important aspects are data lineage, data dictionaries, auditing and impact analysis.

We are very pleased to announce the first incarnation of the "SQL Server 2005 Business Intelligence Metadata Samples Toolkit" http://www.microsoft.com/downloads/details.aspx?FamilyId=11DAA4D1-196D-4F2A-B18F-891579C364F4&displaylang=en. Along with the associated whitepaper http://www.microsoft.com/downloads/details.aspx?FamilyId=182BD330-0189-450C-A2FE-DF5C132D9DA9&displaylang=en it should now be possible to satisfy many of your metadata needs - simply and essentially for free!

The whitepaper also describes the importance of the new Extended Properties that can be applied to objects within the database - these can be used to store all sorts of business information including the all important data lineage and data definition.

New Performance Troubleshooting Guide for SQL2005

Mat_Stephen — Tue, 03 Jan 2006 13:12:00 GMT

Many things have changed with SQL 2005, correspondingly we have to change the way we think about troubleshooting performance problems. The most significant change is the advent of the new Dynamic Management Views (DMV), these are going to take some getting used to and this article helps significantly. There’s some really good stuff around identifying blocking, something that plagues most of us at sometime or other – well worth a read if you’re responsible for day to day running of a SQL box.

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

New SQL 2005 code samples available

Mat_Stephen — Mon, 02 Jan 2006 20:15:00 GMT

I guess now the pressure is off to release the product we'll see more useful samples comming out of corp; here's a link to the latest December update:

http://msdn.microsoft.com/sql/downloads/samples/default.aspx

Feature Pack for Microsoft SQL Server 2005 - November 2005

Mat_Stephen — Mon, 28 Nov 2005 20:47:00 GMT

Overview

The Feature Pack is a collection of standalone install packages that provide additional value for SQL Server 2005. It includes:

Latest versions of redistributable components for SQL Server 2005
Latest versions of add-on providers for SQL Server 2005
Latest versions of backward compatibility components for SQL Server 2005

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Get the facts about deploying and using Microsoft® SQL Server 2005TM

Mat_Stephen — Fri, 25 Nov 2005 01:09:00 GMT

Our launch roadshow, here in the UK, was totally over subscribed. So here's another chance to gem up on the plethora of new capabilities.

Experts from Scalability, DevelopMentor, and Solid Quality Learning will present practical, real-world information in three tracks covering administration, development and business intelligence Register NOW www.sqlmag.com

You've got to be quick!

Slides from the SQL Server Launch in Birmingham UK

Mat_Stephen — Thu, 10 Nov 2005 13:32:00 GMT

During, what I hope was viewed as a successful day, many people asked where they could find the slides for the technical sessions.
You will be able to find them @ http://www.microsoft.com/uk/launchtour2005 - but you may have to wait a couple of days before they actually get loaded.

Hope those of you attended enjoyed the event. Wow - I found it incredibly hard work - three sessions on the trot - thought my brain was going come detached from my mouth.

TechNet Briefing: What's NEW in SQL 2005 - Analysis Services & Data Mining

Mat_Stephen — Tue, 01 Nov 2005 14:42:00 GMT

Here's a little self publicity for gig I'll be doing in December:

To register please go to https://msevents-eu.microsoft.com/cui/EventDetail.aspx?culture=en-GB&eventid=118761637

05 December 2005 18:30 - 05 December 2005 21:00 (GMT) GMT, London
Welcome Time: 18:00
Language: English

Microsoft Ltd

Chicago 1
Building 3
Microsoft Campus Thames Valley Park Reading Berkshire RG6 1WG
United Kingdom

General Event Information
Products: SQL Server.

Recommended Audience: Business Professional, IT Professional and Partner.

Session Abstract: Get Ready for SQL 2005! - This session will explain the benefits of the new Unified Dimension Model (UDM) that underpins the On-Line Analytical Processing (OLAP) component of SQL Server 2005 Analysis Services. The UDM represents a radically new approach to OLAP, seeking to combine the best aspects of OLAP with the best of aspects of On-Line Transaction Processing (OLTP). This session will compare and contrast the OLAP engine in SQL Server 2000 with the UDM and explore the new architectural possibilities it offers. Also covered will be an introduction to the new data mining algorithms which will show how they can be employed to gain competitive advantage.

Its not SQL but it is amuzing

Mat_Stephen — Mon, 24 Oct 2005 12:19:00 GMT

Some people around here (in the UK) just have too much time on their hands, check this out for a little light entertainment.

http://msexp.streamnavig.com/msexp/player.asp?e=E9999&s=9999_en_w&f=9999_en&uid=00011C7758813ADF&lng=en&cou=uk

Changing Job

Mat_Stephen — Thu, 06 Oct 2005 16:52:00 GMT

It is with sadness that I have to announce that I am changing jobs and moving back into my old role as a Product Specialist, working with our Enterprise customers in a pre-sales capacity.
Alas, I won’t have as much time to blog now, but I’ll do my best to share interesting SQL info as I come across it.

Microsoft .NET Data Provider for SAP

Mat_Stephen — Tue, 04 Oct 2005 15:07:00 GMT

After my recent SQL 2k5 BI session, here at TVP Reading, I had an enquiry asking for more info regarding the SAP provider I showed in the list of providers available to SQL Server Integration Services . The best public information I can find is @ http://msdn2.microsoft.com/en-us/library/ms216599. The provider isn't publicly available at the moment but when the product is released it will be made available for download, and will not be in the box. I don't know a lot about SAP R3 but I believe this provider will also allow access to BW - I'll check, if it doesn't I'll post an edit in the next few days.

UK SQL User Group Presents: "Concurrency in SQL Server 2005, Kalen Delaney Full Day Seminar: 18th Oct 2005."

Mat_Stephen — Wed, 28 Sep 2005 15:18:00 GMT

Tony Rogerson of the UK SQL User group has asked me to advertise this event. I think it will be of general interest but also of interest to people migrating or thinking of migrating from Oracle. And remember you've still got three days to enter the Oracle migration competition @http://www.sqlserverchopper.com/Rules.aspx.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Concurrency in SQL Server 2005, Kalen Delaney Full Day Seminar: 18th Oct 2005.

http://www.sqlserverfaq.com?eid=61

Failing to design an application with concurrency in mind, and failure to test an application with the maximum number of expected simultaneous users is one of the main causes of poor application performance.

In this seminar we will look at the concurrency enhancements made in SQL Server 2005, based on a technology called row-level versioning (RLV). RLV provides a new isolation level called Snapshot Isolation that allows readers of data to not be blocked by writers. The seminar will compare the new isolation level with the previous ones and provide guidelines as to how to determine which isolation level is appropriate for your needs, and what application changes might be necessary to achieve the level desired.

In addition to new locking behaviours, we will discuss the way that RLV increases the concurrency potential of other features of SQL Server 2005, including online index rebuilds and multiple active results sets.

In this seminar you will learn:

How and when to use the new Snapshot Isolation to achieve better locking behaviour and greater concurrency.

How Row Level Versioning improves the concurrency of other SQL Server operations.

How to tune your database to allow the maximum use of Row Level Versioning and how to monitor the resources used.

SQL Server 2005 Technical Overview blogcasts

Mat_Stephen — Tue, 27 Sep 2005 17:38:00 GMT

New SQL Server 2005 Technical Overview blogcasts from the States

Demo 1-1 Using Object Explorer    http://www.microsoft.com/winme/0509/25037/SQL-01_Demo1.asx

Demo 2-1 Using Queries and Scripts  http://www.microsoft.com/winme/0509/25037/SQL-01_Demo2-1.asx
Demo 2-2 Using Solution Explorer  http://www.microsoft.com/winme/0509/25037/SQL-01_Demo2-2.asx

Demo 3-1 Securing SQL Server with the Surface Area Configuration tool http://www.microsoft.com/winme/0509/25037/SQL-01_Demo3-1.asx
Demo 3-2 Using SAC.EXE to Export and Import Surface Area Configurations http://www.microsoft.com/winme/0509/25037/SQL-01_Demo3-2.asx

Demo 4-1 Understanding User-Schema Separation http://www.microsoft.com/winme/0509/25037/SQL-01_Demo4.asx

Demo 5-1 Using SQL Agent Proxies http://www.microsoft.com/winme/0509/25037/SQL-01_Demo5.asx

Demo 6-1 Configuring Database Mail http://www.microsoft.com/winme/0509/25037/SQL-01_Demo6-1.asx
Demo 6-2 Sending and Checking E-mail http://www.microsoft.com/winme/0509/25037/SQL-01_Demo6-2.asx

Demo 7-1 Using Mirrored Backups http://www.microsoft.com/winme/0509/25037/SQL-01_Demo7.asx

Demo 8-1 Using SQL Profiler   http://www.microsoft.com/winme/0509/25037/SQL-01_Demo8-1.asx
Demo 8-2 Generating a deadlock  http://www.microsoft.com/winme/0509/25037/SQL-01_Demo8-2.asx
Demo 8-3 Analyzing deadlock using SQL Profiler  http://www.microsoft.com/winme/0509/25037/SQL-01_Demo8-3.asx

OLAP, Darwin and Evolution

Mat_Stephen — Tue, 20 Sep 2005 02:59:00 GMT

Edited 21st Sept 05 to include picture of herbarium sheet.

In my article 'Business Intelligence (BI): The way it is without the blah blah', I mention that I was aware of some exiting research using the SQL Server OLAP engine, the results of which where hopefully about to hit Nature magazine. I couldn't say anymore at the time for fear of jeopardising the conclusions' debut in Nature (If Nature isn't first to publish, it's not in Nature). I claimed this research would demonstrate OLAP, as a technology, can be used as a useful tool in fields well beyond those fenced in by the concept of Business Intelligence. Well this research has indeed made it to the hallowed pages of this erudite organ; if you are prepared to pay the fee, you can find it here 'What Henslow taught Darwin' @ http://www.nature.com/nature/journal/v436/n7051/index.html#Feature.

The article doesn't single out SQL Server per se, but I can assure you, as someone who is in touch with one of the authors, Analysis Services OLAP engine made a significant contribution to the research mentioned. Hopefully follow up material from the authors will document how OLAP was used. Apparently there's so much interest in this story there's even talk of a film!

Let me give you an abstract to show how SQL Server Analysis Services OLAP has contributed to mankind's understanding of its own history and the study of biology therein.

Darwin was a student (1829-31) of Professor John S. Henslow of Cambridge. Henslow is well known for arranging Charles Darwin's berth on the good ship HMS Beagle, the ship that took Darwin to the Galapagos islands. The Galapagos islands are where Darwin discovered 'Darwin's Finches', birds that had 'evolved', by appearance and behaviour, to such an extent that Darwin had to question whether they were different varieties of the same species, or different species altogether. In pursuit of the answer to this question, Darwin formed his seminal concepts that became the backbone to On the Origin of Species.

In the shadow of the afore mentioned article, we must now understand that it was Henslow who had first recognised variation in species, and that it was this recognition and the desire to seek its verification that put Darwin on the Beagle. Furthermore, it was the fidelity of Darwin to fulfil this ambition, with its incumbent rigours in procedure, that ultimately lead him to break free of the creationist shackles that had hindered his mentor, and from which, sadly, his mentor would never be freed.

The research in this article covers 10,172 plants collected by Henslow. By creating an OLAP cube to analyse the sheets of paper holding these plant samples, the researchers show that Henslow organised his documentation by none other than the variation he observed to be within the limits of species variation. Nobody is known to have attempted this before. It is of the stuff that makes a good film that we can now visit his herbarium at Cambridge and see, with newly enlightened eyes, how the plants within it have been carefully arranged to illuminate this most potent observation; a place where, hitherto, this brilliant enlightenment has been so shrouded in the dark silence of ignorance.

Below, courtesy of the authors, is an image of one of Henslow's herbarium sheets: Phleum arenarium. Eight numbered individuals are arranged in order of increasing height. Plants 1-5 were collected 3rd June 1829 at Mildenhall, Suffolk by J.S. Henslow. Plants 6-8 were collected in June 1822 at Liverpool by W. Wilson.

Database Mirroring won't make RTM

Mat_Stephen — Fri, 16 Sep 2005 18:54:00 GMT

Edited 20/9/05 to correct typos

As Paul Flessner says in his letter http://www.microsoft.com/sql/2005/productinfo/letter.mspx Database Mirroring won't make it in the RTM (Release To Manufacturing) release for SQL 2005. It will be there for people to test using a trace flag, but Microsoft has not declared it tested enough for production.

We do expect to see this feature go live sometime in the first half of next year.

Also Express Manager is being revamped. Its going to get its own Visual Studio lite appearance - also to come in the first half of next year.

SQL Server Integration Services Samples - Including Fuzzy Logic

Mat_Stephen — Thu, 15 Sep 2005 19:27:00 GMT

At a recent Technet presentation, someone came up to me and asked me if I could post my Fuzzy Logic sample package to my Blog. I confess I haven't got round to wrapping it all up, but now it looks like I'm off the hook as one has been supplied with the samples that come with the product.

Here's the complete list of samples (as it is today)

Installing Sample Integration Services Packages - Provides instructions on installing sample packages from the stand-alone Microsoft Windows installer.

Calculated Columns Package Sample - Demonstrates how to use the Aggregate and Derived Column transformations to compute values and write the results to a file.

Capture Data Lineage Package Sample - Demonstrates how to get lineage information about data, add it to the data flow, and write the source data and the lineage information to a table.

Process XML Data Package Sample - Demonstrates how to extract data from an XML data file by using an XPath operation, convert the data to a flat-file format, and insert the data into a text file.

Data Cleaning Package Sample - Demonstrates how to clean data by applying exact and fuzzy matching of new customers to existing customers, and how to identify duplicate customers by using fuzzy grouping.

Execute SQL Statements in a Loop Package Sample - Demonstrates how to run SQL CREATE TABLE statements in a loop, evaluate and clean data values, and insert data into tables.

Execute Process Package Sample - Demonstrates how to use the Execute Process task to run an executable that expands a CAB file, read the expanded data, and insert the data into a table.

SMO Tables DBCC Package Sample - Demonstrates how to enumerate the user tables in the AdventureWorks database by using SMO, use a Script task to retrieve the schema and table names, put the names into two variables, and use an Execute SQL task that executes a DBCC CHECKCONSTRAINTS command using the two variables.

AWDataWarehouseRefresh Package Sample - Demonstrates how to refresh the AdventureWorksDW data warehouse from the AdventureWorks OLTP database.

Synchronizing Adventure Works DW Partitions Package Sample - Demonstrates how to automate the synchronization of partitions between relational and Analysis Services databases by using an Integration Services package. The sample package uses the Script task and the Analysis Services Execute DDL task, and illustrates the use of the ASSL and AMO technologies."

Bill Gates - A short chat with Microsoft's Chief Software Architect

Mat_Stephen — Tue, 13 Sep 2005 14:50:00 GMT

Its not really SQL Server, but this short 16min video comes from the guy who ultimately takes resposibility for SQL. Bill is asked a number of interesting questions that put his contimued relationship with Microsoft under the spotlight - like why does he bother working? If you haven't seen Bill speak much - this is a very good cameo.

http://channel9.msdn.com/Showpost.aspx?postid=111598

PS its also a good intro to Channel9

Getting to know SQL 2005 Service Broker

Mat_Stephen — Tue, 13 Sep 2005 00:27:00 GMT

Looks likes I've got to do a presentation on Service Broker for the SQL 2005 launch - so I've been doing some home work on the subject.

The best primer I've found is http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlsvcbroker.asp?frame=true this is refernenced in the webcast http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032263311&EventCategory=5&culture=en-us&CountryCode=US.

Once you've got your head round the basics you'll want to create your ow broker service and queues. Best I've found is "Setting Up a Service Broker Service and Queue" from SQL Server 2005 Books on line at ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9/html/f6f1c8b5-393b-48f2-a2b0-184fef683b5c.htm. Problem with this is there's a bug in the code. I can't remember the delta between what's printed and what I have working, but my suggestion is to use the code below to create the TargetStoredProcedure and then comment out the sp.Create(); line in the sample code with //.

Hope you enjoy this new technology - if I discover some new usefulties (a new word I've just invented) in this area, I'll post them in my new 'Service Broker' category

USE [AdventureWorks]
GO
/****** Object: StoredProcedure [dbo].[TargetStoredProcedure] Script Date: 09/12/2005 23:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure dbo.TargetStoredProcedure as
DECLARE @message_body varbinary(MAX);
DECLARE @message_type_name nvarchar(128);
DECLARE @conversation_handle uniqueidentifier;
DECLARE @message_iduniqueidentifier uniqueidentifier;
DECLARE @message nvarchar(max);
DECLARE @MessageType nvarchar(max);
Declare @message_id nvarchar(max);

WHILE (1 = 1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
WAITFOR (

RECEIVE top(1)
@message_type_name = message_type_name,
@message = message_body,
@conversation_handle = conversation_handle,
@message_id = message_id

FROM TargetQueue
), TIMEOUT 500

IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END;
select @message = N'<Hello>Pong</Hello>';
set @MessageType = '//microsoft.com/ssbdemo/PongMessageType';
SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE @MessageType
(@message);
END CONVERSATION @conversation_handle
COMMIT TRANSACTION
END TRY
BEGIN CATCH
print @@error;
ROLLBACK
CONTINUE
END CATCH
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF

SQL 2005: Making sense of the new Optimistic Concurrency Controls and the readers that block writers

Mat_Stephen — Tue, 06 Sep 2005 20:11:00 GMT

For a long time now Oracle enthusiasts have bashed SQL Server for not having Optimistic Concurrency Controls. They would demonstrate a SQL Server reader blocking a writer and possibly a writer blocking a reader. Thus, having appeared to demonstrate that SQL can't handle these two operations concurrently, they would therefore conclude that SQL can't scale - certainly not to enterprise level workloads; workloads that involve lots of people writing to a database at the same time as lots of people wanting to read from it.

This, of course, is a rather dirty trick; you only have to look at http://www.microsoft.com/sql/evaluation/compare/benchmarks.mspx to see SQL Server 2000 performance against various typical 3rd party LOB application workloads that involve reading and writing. So how did/do they perform these mischievous tricks - here's how:

Open connection 1

USE AdventureWorks

BEGIN TRANSACTION

UPDATE Production.Product

SET StandardCost = 5.0,

ListPrice = 11.50

WHERE Name LIKE '%sock%'

Open connection 2

USE AdventureWorks

SELECT Name,

StandardCost,

ListPrice

FROM Production.Product

WHERE Name LIKE '%Sock%'

You will now see that connection 2 will not return anything - instead it just sits there doing nothing. Here the writer on connection 1 blocks the reader on connection 2. This blocking will continue until connection 1 either commits or rollbacks the transaction it started with BEGIN TRANSACTION.

Well it would be very easy, at this stage, to get into a religious slanging match - and you know I don't do religion very easily. Luckily, with SQL 2005, I don't even have to think about get religious about this situation, because SQL Server 2005 now supports the necessary isolation levels to stop this happening.

At this point I'm reminded of an old joke told by Tommy Cooper: Bloke goes to see a Doctor, he lifts his arm in the air and says, "Doctor, every time I do this it hurts." And the Doctor says, "Well don't do it then."

I'm reminded of this joke because if you begin a transaction, update a value and then you find it causes other people problems - I would suggest you don't do it. Why begin a transaction, do some work and then walk off without committing it?

Okay - so you might have a 'really big update' that takes ages to run and it can't be batched up into smaller transactions and you need to run some very long running reports that have to have a transactionally consistant view of the data. Indeed; you might also be very unlucky and get struck by lightening!

In SQL 2000 there are various ways to obviate the encumbrance of this 'really big update' and nasty report – but I don't want to go down that road, like I said I want to avoid a religious debate. Now, in SQL 2005, we have exactly the same capability as Oracle to make such an operation very easy for the developer. Like Oracle, this capability employs the concept of 'versioning', which means each connection in the above scenario will work with its own version of the data, thus avoiding contention.

However as with Oracle, versioning takes up both processing and i/o at the server, resources that are potentially very expensive and not to be wasted if you don't have to. Does Oracle do any benchmarks with this feature turned on? I don't think so, and nor would SQL for that matter.

I will argue that versioning is frequently used as a way to make developers jobs easier and not because it’s actually necessary. By that I also mean it makes it easier for developers to write sloppy code - and by that you can happily infer I believe it makes it easier to employ cheaper developers.

So how do we go about ~~employing these cheaper developers~~, sorry, reducing contention on the database? To do this we now have two new isolation levels:

1. SNAPSHOT ISOLATION (Transaction-level Snapshot)

Administrators must set the new ALLOW_SNAPSHOT_ISOLATION database option to allow Snapshot Isolation.

In order to start a transaction that uses Snapshot Isolation a developer must SET TRANSACTION ISOLATION LEVEL SNAPSHOT

RESULT: Every statement within a Snapshot Isolation Transaction sees the same version of data comprised only of committed changes which occurred before the start

of the transaction. Other statements inside other transactions do not see the changes made inside this Snapshot Isolation Transaction.

2. READ COMMITTED WITH SNAPSHOT ISOLATION (Statement-level Snapshot)

Administrators must set the new READ_COMMITTED_SNAPSHOT database option to allow Read Committed Snapshot Isolation

No further application level changes are required to have statements use Read-Committed Isolation.

RESULT: Each statement sees a version of the data that was committed just before the statement began, instead of when the resource is read. This is merely a new implementation of read committed that is non-locking and non-blocking; the data is accurate only as at the start of the statement.

IT Forum - Barcelona 15th - 17th November

Mat_Stephen — Tue, 06 Sep 2005 12:31:00 GMT

The tag line for IT Forum is "3 days of learning one year of solutions" - I'm sure you can sell that to your boss. If you do the details are here http://www.mseventseurope.com/msitforum/05/pre/content/default.aspx

SQL 2005 - UPSERT: In nature but not by name; but at last!

Mat_Stephen — Wed, 31 Aug 2005 12:34:00 GMT

SQL Server has long been criticised for not having an UPSERT command, well now in SQL 2005 we have some good news, we have two new TSQL set operators that give us the complex insert/update logic that constitute an UPSERT. These are INTERSECT and EXCEPT

e.g.

-- UPDATE Destination FROM (Source INTERSECT Destination)

-- INSERT INTO Destination FROM (Source EXCEPT Destination)

These statements obviate the need to construct complex correlated sub-queries or WHERE EXISTS logic.

And what's more, these operators deliver high performance by exposing core engine functions.

Project REAL—Business Intelligence in Practice - great website!

Mat_Stephen — Tue, 30 Aug 2005 18:27:00 GMT

For sometime a group of Program Managers from the SQL BI team have been working with a customer, Barnes and Noble, to produce a SQL BI solution for their data warehouse and analysis needs using SQL 2005. The leasons learnt from all their work is being continually published to this website http://www.microsoft.com/sql/bi/projectreal/default.mspx in the form of webcasts and whitepapers. With so many new tools and functionality to play with in SQL 2005, I have found their work (what I've seen of it so far), to be invaluable.

64 Bit performance v 32 bit performance - some compelling evidence

Mat_Stephen — Thu, 25 Aug 2005 15:23:00 GMT

More and more ITPros are looking for help in deciding between 64 bit and 32 bit boxes, often they are looking for evidence that 64 bit is worth the investment and worth the risk (of adopting a new platform).

This link http://www.microsoft.com/sql/64bit/productinfo/winhecdemo.mspx takes you to a compelling demo, delivered at the Microsoft WinHEC 2005 conference, which compares (graphically) the performance of SQL Server 2005 running on the 32-bit version of Windows Server 2003 compared with SQL Server 2005 running on Windows Server 2003, x64 Edition.

Enjoy

Upgrading a SQL Replication Topology

Mat_Stephen — Tue, 23 Aug 2005 19:11:00 GMT

Jonathan Allen wrote to me asking what order one should upgrade servers in a replication topology - pointing out that this doesn't seem obviouis from Books On Line, which I agree.

The answer is essentially the same as for any previous upgrade: Distributor first (as it has to handle new and old log record formats), then the Publisher and finally the Subscribers.

Fame at last: Ten Useful Microsoft Blogs

Mat_Stephen — Fri, 19 Aug 2005 17:47:00 GMT

Redmondmag.com have published an article 'Ten Useful Microsoft Blogs' @ http://www.redmondmag.com/columns/article.asp?EditorialsID=1044

Guess who's number 2?

Also at number 10 is the UKs own Industry Insiders blog http://blogs.technet.com/industry%5Finsiders/. So as promised all of you who submitted articles are already gaining recognition. Alas only two articles have come form the database community. So come on, I know plenty of you who read this blog have interesting and valuable stories to tell - just send me your biography and a page or two of interesting material and I'll get it posted on the site. It should be a career enhancing experience, whilst adding to the richness of the SQL Server community.