Richard Campbell Blogs Too

We've spent some time walking around SQL Server and its business intelligence services. For this show, Greg and I talked to Trey Johnson about the overall infrastructure that Microsoft provides for Business Intelligence. Not just SQL Server's features, but also Sharepoint and ProClarity, which was recently acquired by Microsoft.

As always, we love your comments, questions and suggestions at info@runasradio.com.

Greg and I discussed a lot of potential topics around SQL Server with Brad, but eventually settled on this one because it was so unique. What does it take to be a better DBA? I agree with Brad for the most part, the challenge of being a great DBA is that you have to do it yourself, there's rarely, if ever, any infrastructure in a company around the development of the DBA career.

This show represents another distinctive style of show we're trying on for RunAs Radio... focused on career development. Like it? Let us know at info@runasradio.com.

So Mark, Karen and I managed to put together something Mondays-like last night. Not exactly a Mondays without Carl, but close enough. Without any recording gear, its going to disappear into history. The SDC folks seemed to enjoy it, lots of laughs.

Just to make it more exciting, I participated in the DotNetNuke Futures Panel right before Mondays. We had originally intended to make the panel discussion into a .NET Rocks show, but without recording gear, that couldn't happen. There's going to be another panel like this at DevConnections in Las Vegas, we'll see if we can't record that one for .NET Rocks instead. If you haven't been paying attention, DotNetNuke is going through a major reorganization as it becomes one of the larger Open Source projects in the world. SDC is hosting the Open Force Europe conference, so folks here are learning what the reorganization means to them.

Today I was even busier - my famous SQL Tips & Tricks session first thing in the morning, then Load Testing with Kent Alstad (more great content generated by our work on Strangeloop) before lunch and then closing the conference in the last slot with Steve Forte doing a SQL Server Q&A session. We left the content of the Q&A session largely open, the attendees were very interested in SQL 2008. Fortunately, Steve and I disagree on a number of features, so it was, shall we say, an "animated conversation."

Tomorrow is the speaker's tour, which will have a number of new twists!

Donald Farmer talks to us about data mining on the IT side of the world, away from the traditional concepts of "people who liked this book also liked..." and into the idea that we could be data mining application and event logs to help us anticipate the needs of our information systems.

Data mining is a specific set of features of Analysis Services in SQL Server 2005 - and its included in the box! If you own a license of SQL Server 2005, you own Analysis Services and its data mining capabilities.

Like this type of show? Let us know at info@runasradio.com.

Blame Michele Leroux Bustamante for this one - she talked me into coming down to do a couple of presentations at the SoCal Code Camp.

I did my Querying Talk again, but also took The Scaling Habits of ASP.NET out for a spin for the first time since the Vancouver TechFest.

Scaling Habits is a fun talk for me because it really is a tour through the evolution of an ASP.NET application - from those early days where you're one guy with a clever idea for a web app, through to what it takes to run a large scale site with multiple servers and the related bureaucracy for operating it.

Along the way I talk about the elements of the evolving site - how much traffic is typical, the kinds of metrics that matter, and so on. And most importantly, what it takes to move to the next level of evolution for the application.

At the core of this whole concept is the idea of the Performance Equation.

A quick description of each factor in the performance equation:

Now I can't take credit for this equation, I did not invent it. The original one comes from the "Field Guide to Application Delivery Systems" by Peter Sevcik and Rebecca Wetzel from NetForecast. However, I did make one change to it - the original equation does not account for simultaneous downloading of resource files and the base overhead of the page file itself. That is represented by the separate addition of an RTT and dividing the rest of the AppTurns by the number of concurrent requests.

So all of these factors go into the time it takes for a web page to fully render on your web browser after you request it.

When I display the equation to an audience, I always ask the question: "What part do you work on?" When I'm talking to ASP.NET developers, invariably the answer is Cs - Compute time on the server. After all, that's the code you wrote. But if you don't know what Cs is in relation to all the other factors of the equation, how do you know if that's the right thing to work on?

Some other interesting issues I've run into once I started looking at web performance this way:

• In many cases bandwidth is just not the issue, we have lots. But when it *is* an issue, often we don't test with the same bandwidth that the customer has, so we don't realize when bandwidth is a problem.
• Round Trip Time is the ping time between the customer and the server. Again, since we often test with servers that are so close to us that the ping time is ultra-low, we don't have test conditions that match with our customers. Its amazing how huge a factor bad RTT can be for performance.
• AppTurns of course exacerbate RTT times, because its a multiplier - if you have a dozen JS files, a dozen CSS files and thirty images (which is remarkably common), you're talking about over 50 AppTurns, and even divided by Concurrent Requests, that expands response time by lots of seconds.
• Normally, with Internet Explorer and FireFox, the number of Concurrent Requests is four. It can be adjusted at the client computer, but its very rarely done. It is possible to do a trick with URI renaming where each resource appears to come from a separate server so that you can fool the web browsers into doing more than four concurrent requests.
• Compute time on the client becomes a significant issue when you get heavy with the Javascript, most often seen with AJAX-style pages. In my opinion, getting the browser more involved in generating a web page is a good idea, but you need to account for the cost involved. If you're only looking at server compute times, then of course AJAX looks like a brilliant solution - because you've hidden the cost.

Now that's not to say that Compute Time on the Server isn't important to the equation - it *might* be. But you should know for sure before you pour your time into improving it. Going through the exercise of breaking down where the total response time goes is a critical first step to making sure your effort is going to the right place.

Thanks again to all the folks at the SoCal Code Camp - I had a fantastic time, I'd love to come down again!

I dropped into the Calgary .NET User Group to do my famous SQL Query Tips & Techniques session. John Bristowe was nice enough to put me up for the night, but not before he got an interview out of me.

I've done the querying talk for a number of years now, and I update it regularly. I promised the folks there that I'd make the code available, so I've attached it with all the latest stuff, including my exploration of running totals.

Had a great time, a fun crowd to talk to... I'll have to bring the Strangeloop gear out there once we're ready for a road show and let 'em see what we've been up to.

Here's the sample code, including the script to build the database:

I'm in Montreal this week for DevTeach, the biggest little developers show in Canada.

Carl is here as well, along with many other of my favorite speakers.

On Wednesday I'll be doing my famous SQL Querying Tips & Tricks session, updated for 2007 (now with more Running Totals!).

But the biggest news came this morning: DevTeach is coming to Vancouver, November 26-30 2007!

I'm sure we'll pack the house in Vancouver, the number of speakers I've talked to over the years that have been waiting for a chance to come to Vancouver in the guise of a conference is amazing. I think its the best city in the world, but then I'm biased.

November is the rainy season for Vancouver, but if you like to ski, the end of November is right around the time the mountains open. There are three local mountains (Grouse, Seymour and Cypress) that you can take a local bus to. And then of course there's Whistler/Blackcomb, a couple of hours away. And there's another dozen ski mountains further away than that.

And besides, you're there to geek, and there's gonna be a lot of geekiness around at the end of November!

I'm about to do my last session at Tech Ed Boston, a repeat of my session on Monday, SQL Querying Tips & Techniques.

I figure I better just put the session sample here, so you can download it if you're so inclined.

sample.zip (5.49 KB)

The following files are inside the sample zip:

• AdvQuerying_Setup.sql contains a script to create the tables and data for the samples
• AdvQuerying.sql is the script with all the example queries I demoed in the session
• Error Handling Main.sql is the script for the deadlock error handling stored proc
• Error Handlng Secondary.sql is the script for setting up the deadlock demo

So to use these samples, create a database (I called it AdvQuerying) and then run the setup script. Then you're good to go!

I'm hanging out in the United Kingdom in Reading, just outside of London, attending the VBUG Conference.

Carl and Mark Miller are here as well.

Yesterday I did my famous advanced querying session, I've attached my demo files here for the folks who wanted them.

AdvQuerying_Setup.sql (3.48 KB)

AdvQuerying.sql (23.52 KB)

I'm also including my error handling queries, since I had enough time to quickly show off how SQL Server 2005 can actually handle an error: in this example, I handle a deadlock inside a stored procedure.

Error Handling Main.sql (1.48 KB)

Error Handling Second.sql (.26 KB)

Besides my session, Carl and I did a DNR Live, talking to the four fellows involved in a Code Off - the concept is that all four developers, working independently, build four different UIs over top of a common set of banking web services. The four clients included WinForms, Office, ASP.NET and mobility clients (PDA and Smartphone). They had about four hours to build the clients, and the results were impressive. It was an incredibly compelling demonstration of how capable Visual Studio 2005 is.

The conference ends today, then I'm off to London for the weekend before heading home.

I had every intention of blogging through Tech Ed, but it didn't happen.

One week ago today (Monday), I was walking from my hotel room to Tech Ed in a really foul mood. Being grouchy is rather unusual for me, but you can ask Steve Forte and Cathi Gero, they were there, and boy, was I grouchy.

I guess its been a few years since I've done back-to-back conferences, having done the SDC conference in the Netherlands the previous week, my body was trying to tell me I should be at home by then.

All of this changed when I got on stage with Steve to do our Advanced Querying session.

Y'know, speaking at conferences is really a money losing proposition for the majority of speakers, myself included. I would be making more money staying at home and working. But its really, really fun. Really. Engaging a big group of people (and there was about 800 people in the room) is a challenge, its exciting, and when it goes well, you're in orbit for the rest of the day. And I think it went pretty well - lots of laughter and ooh-aahs.

So to my audience at my first session: THANKS! You made my week.

Some folks have been emailing me, unable to find the samples for the session. I've attached two files here, the first is the setup file which creates the sample tables.

Setup.sql (3.57 KB)

The second is the demo script itself with all the queries Steve and I showed.

SS2k-YukonSamples.sql (24.42 KB)

We got a ton of response on this session, and some cool new ideas for a new version next year.

My second session on Tuesday was the Profiler session, with my special guest Vipul Shah who jumped in to show off the cool new features of Profiler in SQL Server 2005. Its a cool session, but I think with Profiler 2005 coming, I'm going to have to rewrite it to really dig into the new capabilities of the tool.

As I explained in the session, my real focus on the Profiler session was to let developers know that things can happen to your queries between your code and SQL Server, and Profiler is really the only way to know. The big example I show is ADO 2.5 messing with a SELECT statement and stored procedure, wrapping them in cursors. I haven't found the same behaviour in ADO.NET, so I think the demo is getting moot.

I'm thinking next year I'll revise the session to make it more of a “Using Profiler as a Diagnostic Tool” type session.

On Wednesday Carl Franklin and I did .NET Rocks! in front of a live audience. I think there were close to a thousand people in the room, which was at the far end of the conference center. And I do mean the far end - I figured by the time we got there we'd walked to Cuba. We interviewed the Team System guys, I think the show went really well, it was fun to dig into more of the story behind Team System... and even better to have a bunch of fans watching the show!

Alas, my version of the standard DNR disclaimer (normally Geoff's domain) didn't make the cut of the show, but either way, a good listen.

Tech Ed may be over, but the Tech Ed Charity Auction isn't. 23 Tech Ed speakers, including me, are donating an hour of consulting time via phone, email or IM. You can bid on EBay at http://cgi.ebay.com/ws/eBayISAPI.dll?ViewItem&item=5587400881. The auction ends on June 16, so bid soon, and bid often.

When we say Beer Night, we mean it!

Near the beginning of this evening session, Kent Alstad and Remi Caron conspired to bring Steve and I these huge two liter beers in Heineken labelled boots. I finished mine, Steve didn't. Not that he wasted it, he filled a dozen regular sized glasses from the boot.

The session itself was a melage of SQL Server 2005 topics, our slide deck and five slides: the title slide called the session “Estaban, 'splain dis Jukon to me!” It got less serious from there.

We had a fine discussion on the horror and fear around using the CLR inside of SQL Server, Ted Neward and Markus Egger got into it before Steve put the brakes on what was turning into an entirely too serious discussion.

I have to thank Kim Tripp for firmly planting all the details of Snapshot Isolation into my head, I think I was able to deliver a coherent explanation to everyone when the topic came up.

The beer continued to flow after the session, but we still knocked out some good talks the next morning. The conference was a ton of fun, its nice to see the SDGN group growing bigger and better still.

The day after the conference we went on the traditional Holland Tour, although this year it wasn't traditional at all... we actually got to sleep in a bit, rather than leaving at 8am as we have many years, we weren't loaded up and gone until 10! First stop was the Airborne Museum in Arnhem, which showed the history of the northern-most (and unsuccessful) part of operation Market-Garden.

Suitably subdued, our next stop after doing several spirals around the Netherlands was a paintball center!

The paintball matches were about 10 on 10, doing various missions: capture the flag, deliver your flag, and the Blackhawk down scenario, where you have a VIP stuck in the helicopter and have to go in and rescue them.

Our team won three of four matches, and I have to say that even our loss was pretty sketchy. I'd attribute our success to some great players, including the crazy fire teams of Steve Forte and Arnot, Ted Neward and Kent Alstad, Remi and Rob and the implacable Cathi Gero, who knows exactly what a VIP should do - stay alive and book it down the trail!

I'm sitting in the back of the main hall at the SDC 2005 Conference outside Arnhem, in the Netherlands.

There's a keynote going on... in Dutch.

I've just finished my first session, the Advanced Querying Tips & Tricks session that I'll do again next week at Tech Ed Orlando. The session went very well, I did it solo, in Orlando it'll be a duet with Stephen Forte.

After lunch Kent Alstad and I are up, talking about requirements. We've done the session before, its been updated, and its a lot of fun. We argue a fair bit, usually me causing trouble complaining about all this planning he wants to do.

Tonight, Steve and I are on again... in the schedule its called “Mid Evening Beer Session with Technical Content.“ In the past its been called a Geek Night and other silly things. Its really Steve and I talking about any old thing and generally behaving foolishly. As the beer flows, it gets more foolish. Looks like its going to be a packed house tonight...

Six weeks since my last entry... and its not that I don't have anything to say, but I've been so busy, by the time I get home, I just want to sleep.

Various highlights of the past six weeks:

• Hung out with Tim Huckaby and his family the weekend of April 16th, lots of fun!
• Kate Gregory and I did a duet deep dive at the end of April, talking about VSTO.
• All the Canadian RDs got together at Microsoft Canada in Mississauga, where we found out that Craig Flanagan, our intrepid leader, was moving on to bigger and more XBoxie things.
• Fellow RD Guy Barrette spent a week out here doing talks on Visual Studio 2005 and had a chance to visit my little toyland.
• I test ran my SQL Querying talk for Tech Ed at both the Victoria .NET User Group and VANTUG!

Which brings me up to current events... I leave this afternoon for the Netherlands to present at SDC 2005 at Papendal outside Arnhem. From there I'm headed to New London, Connecticut to spend some time with Carl and do a few shows (including something new!). After THAT, Carl and I are both headed down to Tech Ed in Orlando (same flights and everything).

I'm doing two sessions at Tech Ed, one is my Advanced Querying Techniques, Tips & Tricks session, which drills into various querying tricks I've collected over the years. This year I'm doing it with Steve Forte, and we're going to compare and contrast SQL Server 2000 and SQL Server 2005 to demonstrate how many of this slick querying techniques change with the latest and greatest.

The other session is a reprisal of my SQL Profiler for the Developer session that I did last year - there won't be any ice cream bars this year I'm afraid. However, I do have a special guest, Vipul Shah is going to show off some of the new goodies in SQL Server 2005 for Profiler junkies.

So finally, I'll stagger home around June 9th, all spring conferenced out.

Maybe then I'll get to fixing my monster machine... it burned up a week after I finished building it, and its sat there dead ever since. Did I mention I've been busy? There isn't going to be any easy fixes, everything worked perfect, but there's just not enough cooling in that little eight inch radiator.

Chalk another one up to those clever folks at Microsoft.

Want to take SQL Server 2005 out for a spin, but don't have a spare machine lying around to do it with?

Try SQL Server 2005 on a Virtual Lab running at Microsoft. They're using a web-based version of the Remote Desktop system. Depending on your Internet connection, its just like being there. I use remote desktop to administer my servers.

http://msdn.demoservers.com/login.aspx?group=sql2005

Had a great time last night at the VANTUG General Meeting. Excellent turn out too - almost every seat was filled.

I demonstrated Microsoft Operations Manager 2005. This is a very cool technology for those of us who are responsible for the care and feeding of a bunch of servers. MOM 2005 is part of Microsoft System Center, which also includes Systems Management Server. And Microsoft System Center is a key player in the Dynamic Systems Initiative (DSI), one of Microsoft's three major initiatives (along with Trustworthy Computing and the .NET Platform).

The Dynamic Systems Initiative focuses on making all aspects of software and hardware manageable. At the software level that means building applications with management in mind - the most obvious thing putting Windows Management Instrumentation (WMI) points throughout your application. Software like SQL Server and Exchange report a steady stream of WMI data points to anyone listening. Think of it as SNMP on steroids. And MOM is all about consuming WMI data.

You can instrument your own applications in .NET from the System.Management namespaces, creating your own custom events, performance counters, and so on. Its not a simple thing to do, but if you're serious about being an application that can be maintained and used in the long term, its worth the effort. Log files are not enough any more, you want to work and play in the DSI world.

Ultimately, doing DSI properly means spending less money and time on keeping servers operational. Its a worthy goal, but like all of Microsoft's major initiatives, its not going to happen overnight: its a combination of new software, new thinking and new work. A couple of years from now, we'll look back on the way we manage systems today the same way we look at email pre-Internet.

Besides having fun with MOM 2005, I showed off the new error handling abilities of SQL Server 2005, essentially doing an abbreviated version of my T-SQL Error Handling in SQL Server 2005 session from Tech Ed Malaysia.

After that, it was all about toys. Here's the goodies I showed off:

• Infrared thermometer (the ultimate in civilian hortosecond generation)
• Datastor Pro 7200rpm USB2 hard drive
• Pretec 1GB Micro USB Key
• Motorola MPx200 cell phone
• WiFi Detector
• And one other goodie that shall continue to remain nameless...

Unfortunately, we didn't get to talking about my home server rig, but I'm sure I'll get a chance in the next month or so to talk about the trials and tribulations of keeping a half dozen servers happy and healthy at home.

This session is one of my favorite “grab-bag” type sessions, and judging by the reaction of the attendees, one of their favorites too. I've never been mobbed with USB keys at the end of a session before. For those of you who didn't grab the code immediately off of me after the session, or from Tech Ed's COMM Net, I'm including it here as well.

In the session I explored essentially four areas of querying techniques. To some degree, they built on each other.

Initially I explored subquerying, an area lots of people dabble in, but haven't really hammered out all the details on. My personal favorite of the subquery examples is a duplicate detection query. In the real world, duplicate data gets entered, and we have to go clean it up. This query returns rows for every duplicate product, showing the oldest ID as the “real” one as well as the duplicate. You can adapt this query to clean up your database, transferring any foreign key rows to the oldest ID, etc.

SELECT Product_ID, Product_Name, Price,
 (SELECT MIN(Product_ID) FROM Products AS P1
  WHERE Products.Product_Name = P1.Product_Name) AS OldID
FROM Products WHERE Product_ID NOT IN
 (SELECT MIN(Product_ID) FROM Products AS P2
  WHERE Products.Product_Name = P2.Product_Name)

This version of the query show both a correlated subquery in the SELECT clause and the WHERE clause, as well as doing that one trick that subqueries are so good at - finding exceptions. The NOT IN statement says “give me only those rows that are not one of the minimum IDs for that product name.“

While I'm not going to go through every technique in the session, attached is the sample code for all of the techniques, including the very popular Rozenshtein cross-tab technique - both static and dynamic.

Advanced Querying.zip (42.34 KB)

This is one of those “I always wanted to write this” sessions about a bit of software I think not enough people know about - the SQL Server Profiler.

Profiler is your friend - it shows you what arrives at SQL Server from your application. And, believe it or not, what arrives there is not always what you sent. Sometimes middleware messes with your SQL.

Profiler can also help you trace down tricky things, like where deadlocks are happening. A deadlock occurs when two connections each hold one resource that the other needs to finish a transaction. Neither one will finish unless the other gives up its resource. When SQL Server detects a deadlock, it picks a loser, failing that transaction with a 1205 error. The other connection can then complete its transaction.

Obviously, the best way to avoid deadlocks is to not ever do that - always lock resources in the same order. I make a list of the sequence in which I'll modify tables and then always write my stored procedures to do updates in that sequence. That way, stored procedures can block each other, but not deadlock. However, this still doesn't get rid of all deadlocks.

And the problem is, the error you get for deadlocks is a bit vague - the error message returns what the competing SPID was, which would typically indicate the other computer who's transaction succeeded, but not what table was involved in the deadlock. In the session (and included in the attachment for this blog entry) is the sample code to show a deadlock in Profiler and then you can use the Object ID information around the deadlock to query sysobjects and find what table/resource was involved in the deadlock.

Also in the sample are some queries for testing out the Index Tuning Wizard, part of the Profiler and a tool that will look at the queries in a Profiler Trace file and decide if any new indexes would benefit performance. It'll also recommend removing indexes that aren't ever used.

Finally, there's my little VB6 sample app that's good for playing with ADO 2.x to see how OLE DB can mess with your SQL - in the session I demonstrated how OLE DB actually converts your SQL statement into a cursor to fulfill your request for a modifyable server-side recordset.

This is the same session I did at Tech Ed San Diego with my buddy Steve Forte... unfortunately, Steve had a problem with his flights that meant he had to leave before the session actually started, making my duet more of a solo...

Steve and I have been doing duets for years, so I dragged a couple of photos out that we had used in the past for duet sessions...

This picture was taken around 1998 at the Papendal facility near Arnhem in the Netherlands, Steve (looks awfully young, doesn't he?) and I are working on our first joint session called “XML for N-Tier”.

This photo was taken at a costume party on Halloween of 2000 in Phoenix, Steve and I are dressed as the three musketeers, our third (Tom Howe) is taking the picture.

The interoperability session focuses on how you can use SQL Server to interoperate with other databases, in this case its Oracle 10g running on Red Hat Linux Fedora Core 1. The idea was to run two Virtual PC sessions, one with Windows 2003 and SQL Server 2000, the other Red Hat and Oracle 10g.

I used Werner Puschitz's step-by-step instructions to Installing Oracle 10g on Red Hat. Even then, its tricky, lots of places to go wrong. And since I'm running Linux from a Virtual PC session, part of the challenge is getting Red Hat happy in a VPC environment. Networking is also tough - I didn't want to use fixed IPs on the VPCs, since that would cause other network problems when plugged into various conference networks. Each VPC session grabs a dynamic IP, but the way Oracle is configured, you have to alter a listener file to tell Oracle what IP you're now running under.

On the SQL Server side of things, you need to install the Oracle tools to get Net Manager, which provides communication to Oracle from the Windows 2003 environment. That's another point where you have to specify the IP address of the Oracle server.

In SQL Server itself, the key to making the connection is linked servers - and the easiest way is using sp_addlinkedserver and sp_addlinkedsrvlogin stored procedures to establish the connection. The parameters look like this:

sp_addlinkedserver @server = 'OrclDB', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'larryserv'

• @server is the name you want to refer to the linked server with.
• @srvproduct is the name of the database you're linking to. SQL Server checks this name carefully, if you misspelled Oracle, it won't work.
• @provider is the name of the OLE DB provider to use, in this case its the Microsoft Oracle provider.
• @datasrc is the name of the Oracle database from Net Manager.

sp_addlinkedsrvlogin @server = 'OrclDB', @useself = false, @locallogin = 'sa', @rmtuser = 'scott', @rmtpassword = 'tiger'

• @server is the name of the linked server as specified in sp_addlinkedserver.
• @useself is a flag to tell SQL Server to use the account currently logged into SQL Server on the remote server. Setting this to false means that the login will use the remote user and password supplied.
• @locallogin is the name of the account that this login can be used by. You can set it to NULL and it works with all accounts. The account can be a SQL login or a Windows login.
• @rmtuser is the user name to log into the remote server with.
• @rmtpassword is the password to log into the remote server with.

Once the linked server is established, you can do queries like this:

select * from OrclDB..SYSTEM.TBLCONTACTS where lastname = 'Campbell'

You can see the only unusual aspect of this query is the table notation, indicating the linked server and the schema that the table is associated with. With Oracle, table names are case sensitive, so you have to get the case right.

In the session we discuss not only how to interoperate, but also how to migrate. The key to migration is to move the data last, not first. Build a .NET application that calls SQL Server stored procedures, and use the stored procedures to make the calls through to Oracle. That way you can build your new client without turning off the old client, and the two clients can run side-by-side for as long as necessary. Eventually, everyone will be using the new client since its getting all the new features.

You can start adding tables to SQL Server to add new features, linked servers will allow you to do joining between the two databases if you like. Eventually, when no one is using the old client any more, you can shut it down and begin moving tables from Oracle to SQL Server. You don't have to do them all at once, just move the tables as you have time, altering the stored procedures to point to SQL tables, rather than the linked Oracle tables. Ultimately, all the tables will be moved and you can shut down the remote connection.

On the other hand, you might discover that there's stuff in Oracle you can't do without, and you can maintain this level of interoperability indefinitely.

The session went very well, unfortunately its tough to give out as a demo, since everything is in VPC images... the Oracle/Red Hat image is nearly six gigabytes!

My first session at Tech Ed Malaysia focused on the new error handling features of T-SQL in SQL Server 2005.

Although its a cool session, I have to wonder about the practicalities of doing error handling SQL Server, after all, as a server application, its usually our job to receive requests from clients and then report back on the results of the request, whether they're happy noises or error messages. And I see error handling as the ability to actually handle an error - as in, detect it, fix the problem and retry the previously error-causing SQL statement. So when would you ever do this?

The only logical place I can think of is deadlocking. Deadlocking is an artifact of SQL Server, not clients... okay, granted, a poorly written client can generate lots of deadlocks, but no client ever sends the request “can I have a deadlock please”... although for some clients its a pretty close thing.

Deadlocks, for those out there new to the concepts, are not blocking, which is where one SQL statement has to wait while another finishes its business. A deadlock occurs when one connection has a lock and requires another lock, and another connection has the other lock and requires the one held by the first connection. Its unresolvable, so SQL Server detects it, and then picks a loser, who's transaction fails... the winner goes on to complete their transaction with no awareness of the suffering spread.

With SQL Server 2005, we can actual handle deadlocks - recover from them transparently so that the client applications don't even know they're happening. Now that doesn't mean that we shouldn't continue to avoid deadlocks in the first place. The best way is to avoid the scenario I just described: make sure all clients lock resources in the same order, so that they block each other, rather than deadlock. Blocks will naturally resolve on their own (or else timeout). Deadlocks are uglier. Stored procedures can help by making sure that clients can't grab data themselves, they have to go through stored procedures. And then you have rules for writing stored procedures, so that the same tables are modified in the same order in every procedure. As long as you make certain you're locking in the same order, you'll block rather than deadlock.

But even with those efforts, you'll still get the occasional deadlock, as transactional velocities rise and queries take longer and longer to run. That's where this clever bit of code comes in:

CREATE PROCEDURE DeadLock_Test AS

SET NOCOUNT ON
SET XACT_ABORT ON
SET DEADLOCK_PRIORITY LOW

DECLARE @Err INTEGER
DECLARE @ErrMsg VARCHAR(200)

RETRY:
BEGIN TRY
  BEGIN TRANSACTION
  UPDATE tblContact SET LastName = 'SP_LastName_1' WHERE ContactID = 1
  UPDATE tblContact SET LastName = 'SP_LastName_2' WHERE ContactID = 2
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  SET @Err = @@ERROR
  IF @Err = 1205
    ROLLBACK TRANSACTION
    INSERT INTO ErrorLog (ErrID, ErrMsg) VALUES (@Err, 'Deadlock recovery attempt.')
    WAITFOR DELAY '00:00:10'
    GOTO RETRY
  IF @Err = 2627
    SET @ErrMsg = 'PK Violation.'
  IF @ErrMsg IS NULL
    SET @ErrMsg = 'Other Error.'
  INSERT INTO ErrorLog (ErrID, ErrMsg) VALUES (@Err, @ErrMsg)
END CATCH

So in this sample stored procedure, I use the new Try and Catch statements to provide error handling. The code in the Try block executes my intended update statements, in the example I'm just updating two contact rows, changing the last names. The two updates are wrapped in a transaction. If there's no problems, the transaction starts, the two rows are updated, the transaction is committed, and the stored procedure finishes.

Now, to introduce some fun, on a separate connection I do this:

BEGIN TRANSACTION
  UPDATE tblContact SET LastName = 'Direct_LastName_2' WHERE ContactID = 2

And then in another connection I execute the stored procedure. So the first connection is holding a lock on ContactID = 2, and the stored procedure has got a lock on ContactID = 1 and is waiting for the lock on ContactID = 2. Now I hop back to the first connection and execute the following:

UPDATE tblContact SET LastName = 'Direct_LastName_1' WHERE ContactID = 1

Now I've created a deadlock situation. The first connection has a lock on ContactID = 2, waiting for a lock on ContactID = 1, and the stored procedure connection has a lock on ContactID = 1 and is waiting for a lock on ContactID = 2. SQL Server detects the deadlock and chooses a loser. Since the stored procedure has its deadlock priority set low, it always loses.

When the deadlock error occurs in the stored procedure, the code in the Catch block executes. The first thing that happens is I retrieve the error code from @@ERROR and take a look at it. If its an error 1205, I know that's a deadlock, so I can do my deadlock recovery. The first step is to rollback the transaction so far. This releases the lock the stored procedure was holding on ContactID = 1, so the other connection finishes the transaction successfully. Since its finished, I can commit it, changing the last names to Direct_LastName_1 and Direct_LastName_2.

Meantime, back at the stored procedure, after rolling back the transaction, the stored procedure records the fact that it had a deadlock into an error logging table, then waits a few seconds to give the other side of the deadlock a chance to finish. Once the wait is over, the GOTO statement jumps execution back up to just above the BEGIN TRY block in the stored procedure, and the updates are attempted again. Since the other transaction is already finished, all is well, the transaction completes and the names are changed to SP_LastName_1 and SP_LastName_2. Who said being the deadlock loser sucked?

Next session - the SQL Server/Windows interop with Oracle/Linux session... supposed to be a duet with Steve Forte, but his flights got messed up and he has to leave early, so I'm doing this one solo again, just like in Montreal. Right after the interop session I'm reprising my SQL Server Profiler for the Developer session.

Wow, already half way into Tech Ed Malaysia, and I'm finally getting a blog entry done. We're having waaay too much fun.

After flying 13 hours Vancouver to Hong Kong, followed almost immediately by the three hour Hong Kong-Kuala Lumpur leg, I arrived in KL short a day... got on the plane late Thursday night, arrived the afternoon of Saturday. Those darn date lines.

It didn't take me long after arriving to find Tim Huckaby (who was good enough to leave me a message at the desk) and Goksin Bakir (he just called me on my cell phone)... we hung out at the pool and drank beer, complained about jet lag and soaked up the indominable KL heat. That night Brian Noyes join us as well for a trip across the lake to a nice Chinese dinner where they have one sneaky Peking Duck.

Sunday was the super tour day - from 7:30am to 9pm we were out and about with our guide Razali and the fabulous elephants of Kuala Gandah. Tim, Brian, Goksin, Kim and I were made volunteers of the sanctuary, rather than visitors, which gave us more access, but we had to do some work, too... here's some examples:

This is Cek Mek with our guide, Razali. One of our first stops at the sanctuary was to go out in 4x4s to feed Cek Mek... she spends most of her time away from the rest of the herd, hanging out in the jungle. She's one of the two “working” elephants, who are actually used to assist the team in moving wild elephants. The other working elephant is Cek Bunga, who doesn't like people all that much, so we didn't get anywhere near her.

Back at the sanctuary proper, we got into the main compound to visit with more elephants, some big:

...and not so big...

We also got to visit with Lasha, a 2.5 year old male who has been fighting intestinal parasites. He's very weak and thin, so he's being kept from the rest of the herd for his own safety. We went into an area behind the compound to visit with him and give him some goodies to eat.

Kim was especially fond of him...

Later, Kim and Brian brought Lasha out from the holding area in the back to the main area for feeding.

As volunteers, we helped visitors feed the elephants... there is a technique and some safety tips involved!

Later in the afternoon Razali took us on a hike through the jungle, to a little village... maybe a dozen people lived there total, the patriarch of the family is a master blow-darter. He put on a fine display firing a foot long dart into a banana tree a good fifty feet away... after a couple of tries, anyway.

Then Goksin took a shot at it... apparently Goksin is a blowdart shark, nailing the tree on the first shot!

After that, we weren't allowed to play with the blowgun any more. We hiked back to the village. Along the way Razali pointed out and offered up some food from the jungle, including heart of palm and water from a vine.

Between the five of us, we took about 400 pictures, I'll put together a full storyboard one of these days.

So that was Sunday - after returning to the hotel we headed down to the bar and consolidated pictures, plus Steve Forte showed up. We made plans for the next day, taking a tour of the Batu Caves and other religious sites. Kim had to do a pre-conference seminar the whole day, she's still annoyed with us for having fun while she had to work.

This is the entrance to the Batu Caves, all 272 steps of it.

Did I mention there were monkeys (long-tailed macaques, to be exact) everywhere? These are the rats of the monkey world, stealing off of anybody who is slow enough to snag. We saw monkeys running off with bags of peanuts, ice cream bars, you name it. One little bugger grabbed my water bottle, I shook him off, and as he prepared to give another go, I flipped the bottle around and gave him a face full. Yeah yeah, I'm fighting with a monkey, but hey, he started it!

The Batu Caves are ancient limestone, filled with Hindu shrines. And they're huge!

This is the view from the top of the stairs looking into the caves, you can see the first chamber, the roof is 250 feet up. Beyond is stairs into a second open air chamber.

After taking a look at the rock formations, the shrines and the macaques scattered throughout the caves, we headed back down the many stairs and into our taxi for our next stop on the religious tour... the museum of Islamic art. However, the cab driver was a bit confused and took us instead to the National Museum of Malaysian Art... its a nice museum, full of stories of the heritage of Malaysia. But it didn't fit with our theme of all Malaysian faiths, so after a quick walk through we headed back to the cab.

On the second try the cabbie did find the Museum of Islamic Art, and also found out it was closed on Mondays. Ah well. Third stop, a Buddhist Temple. This place was open, and fabulous.

Our cabbie came with us to give us basic instruction on how to get around the temple, including proper observations of lighting some incense, and getting your fortune.

Here Steve and Brian, shoes off, are getting instructions on using the luck sticks. You pick up the sticks in a bunch, then drop them back into the bin a couple of times. Then you pick one, and match its number with a little drawer in the bin. Inside the drawer is a bit of paper with your fortune on it. Those towers with the ladder beside them are wish towers, in a different ritual you put a wish on a bit of paper and the temple staff put the wish in with one of those lights on the towers.

That was enough fun for Monday, we headed back to the hotel.

On Tuesday Steve and I headed back to the Islamic Museum of Art, which was now open... only to discover that you weren't allowed to take photos inside. However, it is an amazing place, lots of old copies of the Quran and other artifacts from the history of Islam. There was a big screen tied to a computer in one room that would read the Quran to you in Arabic, showing simultaneous English translation. Very cool. And the favorite part of the museum was the model room, full of 1/100 scale models of the major mosques around the world, including Mecca and Medina. Incredible structures, temples with room for two million people!

Tuesday afternoon was my first bit of work, a SQL Server “Ask the Experts” panel I sat on with Kim and Steve, along with Rodney Fournier (the cluster god!) and Prakash Sundaresen... Joe Yong from the SQL team showed up to field all the “When is SQL Server 2005 shipping” questions.

Its now early Wednesday morning here in Kuala Lumpur, and the real work begins. I have a session every day til the end now, two on Thursday.

I'm just a few hours away from flying off to Kuala Lumpur for Tech Ed Malaysia.

I'm flying on Cathay Pacific, not my usual airline, but their routing kicks ass: Vancouver-Hong Kong-Kuala Lumpur. I leave at 3am Friday morning (that's late, late Thursday night) and arrive around 1pm on Saturday. Its about 24 hours travel, door-to-door, I did the same trip in March for a Business Intelligence workshop.

There's a significant contingent of RDs going... Adam Cogan, Goksin Bakir, Kim Tripp, Malek Kemmou, Steve Forte and Tim Huckaby. So expect some serious speaking, and serious partying.

A bunch of us are heading out with one of my favorite Malaysians, Razali of the Utan Bara Adventure Team. We're going to spend the day with Razali, exploring Malaysian jungle and hanging with the elephants of the Kuala Gandah Elephant Center.

And then there's the actual conference. I'm doing an all SQL Server set of sessions:

• SQL Server Profiler for the Developer
• Advanced SQL Querying Techniques
• T-SQL Error Handling in SQL Server 2005

Plus Steve and I are going to do our duet session on interoperability between SQL Server on Windows 2003 Server and Oracle on Red Hat Linux.

We're staying at a place called the Palace of the Golden Horses, apparently its an amazing resort, I guess we'll find out when I get there.

I'm about an hour away from delivering my last session in the last time block at DevTeach in Montreal.

Its a repeat session of my Introduction to OLAP, a session I've done a number of times and can have a lot of fun with. I love the last session of the conference, its a good time for humour and lots of silliness. I did this session in the first block in the first day... 8am Sunday morning is a tough time to present, much less to attend.

The conference has been lots of fun, many of the usual suspects were here (many are already leaving)...

• Guy Barrette (thanks for setting up the great dinner, Guy!)
• Jim Duffy (believes he's funnier than I am, and may even be right)
• Sylvain Duford (behind that enigmatic smile beats the heart of an evil bugger like the rest of us)
• Markus Egger (Austrian, and not afraid to tell you about it, over and over)
• Carl Franklin (pick a card, any card)
• Cathi Gero (way too nice to hang around with this evil crowd)
• Rob Howard (enjoying his new found freedom)
• Tom Howe (the indominable, the incomparable, and my dear friend)
• Don Kiely (from Alaska, and it shows)
• Kevin Kline (its all about SQL Server, dummy!)
• Nick Landry (hey, settle down, I'd like to come back to this place again some day, I live here y'know)
• Julie Lerman (have you seen my husband?)
• Ted Neward (I call him slash-boy, ask him about it if you get a chance)
• Rod Paddock (trying to keep Duffy under control, when he isn't hassling Markus)
• Marcie Robillard (the DataGird Girl herself!)
• Joel Semeniuk (take my clients - PLEASE!)
• Rick Strahl (the dude with the hair!)
• Christian Weyer (popped his sushi cherry and is never going back)

Missed my buddy Steve Forte, who was supposed to come along and do the Oracle/Linux to SQL Server/Windows Interop session with me. I made it work solo, but its not the same. I also presented a brand new session on Error Handling in SQL Server 2005 (Yukon). While I appreciate the sentiment of giving SQL Server real error handling, I'm still debating about its relevance... how many errors occur in SQL Server that don't have to be propogated back to the client anyway? I brought this line of discussion up during the session, and I think the general consensus was that deadlocks were pretty much the only error that we really want to handle on our own.

I was thinking along the same lines and had written a bunch of test code to try catching a deadlock in a stored procedure and recover automatically... to no avail. Blame it on the beta, I'll wait for Beta 2 and see how things behave then.

Marcie (DataGridGirl) Robillard crashed the conference to catch a couple of sessions and ended up presenting one! What's up with that!

Poor Cathi Gero - we pick on her endlessly. The problem is that Cathi is a genuinely nice person, while the rest of us are evil buggers. The number of times over the course of the past three days that I've seen Cathi with her hands over her mouth, turning pink with embarassment are almost beyond count. Here's an example:

Here, Ted Neward is feeding Cathi some odd thing from our dinner at this lovely Belgian restaurant that Guy Barrette arranged for us. Somehow the feeding of Cathi became highly amusing (you can see me laughing in the background) and Cathi was red all over again... I don't know why she puts up with all of us.

After this final session (only a few minutes before I have to go set up), I'm conference free for a couple of months - a relaxing summer polishing barbequing skills and enjoying being at home. Oh sure, I'll still be working, but that's the easy part.

Next conference - Tech Ed in September, Kuala Lumpur for sure, and Tokyo a maybe!

Well, Stephen Forte and I pulled off the Oracle/SQL Server Interoperate session today. The crowd wasn’t huge, but they were definitely into it – the questions were outstanding, everyone gets the idea that there are hard choices to make in interoperability. Running three operating systems (Window XP host, VPC of Windows 2003 Server and VPC of Red Hat Fedora) isn't all roses and sunshine, either!

We’ve been handing out lots of RD Bingo cards, and signing even more (you have to get an RD on the card to sign their picture, get a line and you win)… the loot is great. I’m astounded at the number of folks here, getting a cellphone connection (or WiFi connection) is a serious challenge.

Now I get to relax for a couple of days and soak up some sessions before my SQL Server Profiler for the Developer session on Friday.