Phil Scott

Launching the ASP.NET Configuration web site outside of Visual Studio (using Cassini)

2006-07-04T01:46:00Z

Some of you .NET heads out there will probably ask: "So Phil, why would I care to launch the config tool without Visual Studio? I live and breath inside Visual Studio." Well, the answer is, that you might not want to launch it, but your non-programming buddy the System Administrator who has the priviledge and the burden of maintaining user security is very likely to want to do this.

I'll keep this post short and sweet. Here's an example batch file I wrote (that must be run on the web server hosting the ASP.NET 2.0 application to be configured).

@echo off
SET FrameWorkFolder=C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
SET VFolder=Asp.NetWebAdminFiles
SET Port=8099
SET App=/Portal
SET AppPath=C:\Projects\SIS\Portal\

explorer "http://localhost:%Port%/%VFolder%/default.aspx?applicationPhysicalPath=%AppPath%&applicationUrl=%App%"

%FrameWorkFolder%\WebDev.WebServer.EXE /port:%Port% /path:%FrameWorkFolder%\%VFolder% /vpath:/%VFolder%

Here are the basic ideas exemplified above:

Be considerate to those (other than you) who will be supporting your ASP.NET 2.0 web site. Give them a way to maintain site security.
Since the Cassini web server is built into the .NET 2.0 framework (as WebDev.WebServer.EXE), we can auto-launch any disk folder as a web site.
Since the ASP.NET Configuration web site is stored in the C:\Windows\Microsoft.NET\Framework\v*\Asp.NetWebAdminFiles\ folder, we can simply launch a local web site through Cassini to host it for us.
The ASP.NET Configuration web site's default web page requires two parameters:
1. applicationPhysicalPath (which should point to your ASP.NET 2.0 web site root folder on the hard disk)
2. applicationUrl (which should be your appname as found in your aspnet database's aspnet_applications table)
Since the command to launch Cassini is a blocking process, the WebDev.WebServer command must be last in the batch file. This means that we must launch the browser prior to bringing up the Cassini site. It all seems to work out fine for me. Perhaps on other people's machines the timing will not work out so conveniently.

Cheers.

Legacy integration with SQL 2005 CLR Table Value Functions

2006-03-10T00:10:00Z

Here's the deal. I'm working on a web project that needs to access legacy data (real time) through a service layer up to a series of web components. The legacy system happens to be an Alpha, running OpenVMS with an old database format called RMS. We have an database driver package for this system (called ConnX), and it runs pretty well.

The issue is that I'd like to encapsulate the details of the legacy integration from the service layer. You see, the legacy system will be going away sometime in the near future, and over the course of time, we'll be migrating the legacy data onto the MS SQL platform. So, our team decided that we want to minimize the number of components that touch the legacy system, thus minimizing the effort of the future migration.

We decided (since we're using MS SQL 2005) to see about writing some C# code to run in the SQL CLR engine to perform the job of encapsulating queries to the legacy database. We thought we'd create some .NET 2.0 assemblies, hook them up to SQL 2005 stored procedures and user defined functions, and use them like any other stored SQL code. We've found this to be a very successful strategy. Now, we have user defined table value functions that are encapsulating calls through an OLEDB driver to the legacy database, and the better news is: we can perform SQL JOINs between native MS SQL tables and legacy table data in real time!!!

So that's the back story. Here's how to actually do it:

(In summary, create a .NET Assembly that contains one or more SQLFunction-compatible methods, and then register the assembly and the individual methods with the SQL 2005 server.)

*** Step 1: Create a SQL Database Project ***

In Visual Studio 2005, create a new project (Visual C# -> Database -> SQL Server Project).

Choose, or create a database connection reference. (This can be your local development SQL Express server for development purposes. Or, you can point it to your integration or team database. The automatic-deployment feature only seems to work well for me when I point it to my local SQL server on my development PC.)

In the Solution Explorer, Add a User-Defined Function. (Add -> User-Defined Function) and name the class (I called mine "LegacySQLFunctions.cs", and I renamed my project to "LegacySQLFunctions").

It will generate some code like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class LegacySQLFunctions
{
   [Microsoft.SqlServer.Server.SqlFunction]
   public static SqlString LegacySQLFunctions()
   {
      // Put your code here
      return new SqlString("Hello");
   }
};

*** Step 2: Program the C# method for the SQL Table Valued Function***

I think of this kind of like the ASP.NET concept of a code-behind module. This assembly is the .NET code behind for the SQL function I need.

To create a SQL Function that returns table values rather than scalar values, you have to do a few extra things:

Edit the method attributes to specify the DataAccess, TableDefinition, and FillRowMethodName.
Change the method to return an IEnumerable type.
Write a separate method to parse the individual IEnumerable members into the defined output table columns.

The idea behind the IEnumerable return type and the FillRowMethodName attribute is that the SQL CLR engine is going to call the main method for the SQL Function, expect to receive an enumerable object, will then walk the enumeration calling the method specified in the FillRowMethodName attribute to parse each record into the expected columns specified for output. Here's how mine is structured:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.OleDb;

namespace ACME.TheApp.MyPortal
{

   public partial class LegacySQLFunctions
   {
      //--- Define a main entry point for a SQL 2005 User-Defined-Function that returns a TABLE value type ---
      [SqlFunction(
         FillRowMethodName = "ParseUserDetailRow",
         TableDefinition = "UserAccount NVARCHAR(10), FirstName NVARCHAR(14), LastName NVARCHAR(14), DateOfBirth DATETIME",
         DataAccess = DataAccessKind.Read
      )]
      public static IEnumerable GetUserDetails( string userAccountLike )
      {
         // Put your legacy integration code here. It should query your legacy database and construct any sort
         // of IEnumerable object for return. In my case, I used the Rows property of DataTable as the return value.

         // By the way, the legacy connection string, legacy user and legacy password should be saved
         // in a configuration table somewhere else on the SQL server, retrieved and cached.   But for simplicity of
         //

         string legacyUser = "imNotTellingYou";
         string legacyPwd = "StillNotDumbEnoughToShowThisInABlog";
         string legacyConnectString = string.Format(
            @"provider=connx ole db provider; Data Source=C:\Connx32\UTILS\legacy.CDD;User ID={0};Password={1};",
            legacyUser, legacyPwd);

         string queryString = "SELECT USRACCT, FNAME, LNAME, DOB "
            + "FROM SYSUSR "
            + "WHERE USRACCT LIKE '" + userAccountLike + "'";

         using (OleDbConnection connection = new OleDbConnection(legacyConnectString))
         using (OleDbCommand cmd = new OleDbCommand(queryString, connection))
         using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
         {
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt.Rows;   // the Rows collection implements IEnumerable, so I am safe to return this to SQL.
         }
      }

      //--- Define the call-back function that will parse each row in the enumeration ---
      public static void ParseUserDetailRow( object item, out string userAccount, out string firstName,
            out string lastName, out DateTime dateOfBirth )
      {
         DataRow row = (DataRow)item;

         userAccount = row["USRACCT"].ToString();
         firstName = row["FNAME"].ToString();
         lastName = row["LNAME"].ToString();
         dateOfBirth = Convert.ToDateTime( row["DOB"]);
      }

   };
}

*** Step 3: Register the assembly and function with SQL ***

Now, I copy the DLL I've built over to my SQL 2005 server. (In my case, I created a folder on my SQL server named C:\Components\SQLCLR\ into which my .NET assemblies go.) I copy the bin\Debug\LegacySQLFunctions.dll file to the server in C:\Components\SQLCLR\.

Next, I open up SQL 2005 Server Management Studio, connect to the database I'm going to keep the Legacy functions in, and open a new query window.

I write and execute the following T-SQL commands:

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'LegacySQLFunctions')
DROP ASSEMBLY LegacySQLFunctions;
GO

-- If you're going to use CLR code that connects to unmanaged code (e.g. C++ OLEDB drivers)
-- you'll need to enable TRUSTWORTHY attribute on the database, and use the UNSAFE
-- permission set on the assembly. Otherwise, skip this command, and use PERMISSION_SET=Safe
ALTER DATABASE MyPortal SET TRUSTWORTHY ON
GO

-- Add the assembly and CLR integration based stored procedure
CREATE ASSEMBLY LegacySQLFunctions
FROM 'C:\components\SQLCLR\LegacySQLFunctions.dll'
WITH PERMISSION_SET = UnSafe;
GO

-- Now, bind the SQL function to the code-behind method in the assembly
CREATE FUNCTION dbo.fnGetUserDetails( @UserAccountLike NVARCHAR(20) )
RETURNS TABLE
(
UserAccount NVARCHAR(10), FirstName NVARCHAR(14), LastName NVARCHAR(14), DateOfBirth DATETIME
)
AS EXTERNAL NAME LegacySQLFunctions.[ACME.TheApp.MyPortal.LegacySQLFunctions].GetUserDetails;

After running the above SQL query, the function should be callable from anywhere within SQL code. Here are a few fictitious examples of how I can use this function now that it's been created:

-- Example 1: returns all user detail records from the legacy database where the legacy
-- user account name begins with JWHITE
SELECT * FROM dbo.fnGetUserDetails( 'JWHITE%' )

-- Example 2: Retrieves recently logged in Portal users, with a join to legacy user details.
SELECT PortalUser.LastLoginDate, ud.FirstName, ud.LastName, ud.DateOfBirth, PortalUser.Email
FROM PortalUser
INNER JOIN dbo.fnGetUserDetails( '%' ) AS ud
ON PortalUser.LegacyAccountName = ud.UserAccount
WHERE PortalUser.LastLoginDate > DateAdd(dd, -1, getdate())
ORDER BY PortalUser.LastLoginDate DESC

The CLR assembly runs very fast. The above code runs sub-second.

Conclusion

SQL 2005 User Defined Functions coupled with the .NET CLR is a beautiful thing!!! Certainly great for legacy integration and encapsulation.

Waterfall is back with a vengeance!

2006-03-10T00:08:00Z

Ok. Short post. But it's worth it. Take a look at http://www.waterfall2006.com/

I'm rolling on the floor laughing! Thanks Curt for the link!

MSF Agile - what's it all mean?

2005-12-16T22:48:00Z

With the looming public release of Microsoft Visual Studio 2005 Team System, we are all hearing about "MSF Agile". Do we know what MSF Agile means to our projects?

Some of us in the software development industry already know what Agile Software Development is, while others have been so focussed on the PMI groundswell that the word "agile" never reached their ears.

What frightens me is that MSF Agile is likely to be the first introduction to Agile Software Development for many software developers and project managers in 2006 and 2007. Why is that a problem? Is there something wrong with MSF Agile? The issue is that, like MSF, MSF Agile is not a prescriptive methodology. It never claimed to be. It is a framework, with which one may develop or compare methodologies; and it contains many excellent resources to help practitioners study patterns and practices in software development. It is akin to a set of principals and ideas without a detailed application guide or set of practical examples. As such, developers and project managers who might not be otherwise educated about Agile Software Development methodologies and practices are likely to be lulled into a belief that by using Visual Studio 2005 Team System, they are practicing Agile Software Development!

Don't get me wrong. I believe that MSF Agile will be a beneficial guideline for certain audiences. I just don't think that the average guy on the street, writing C# 2.0 code or crafting a project gantt chart is going to benefit directly from studying MSF Agile. These folks (in my humble opinion) might be better served to enter the agile community with something like SCRUM or Crystal Clear. Once they've applied the specific practices to live projects, then MSF Agile might hold more meaning to them.

What I'd like to see out of Team System is a set of process guidance templates for each of the major Agile Software Development practices. While I don't think it would be appropriate for Microsoft to take on this responsibility, I would hope to see more TFS templates coming out of the agile community for SCRUM, XP, Crystal Clear, Lean and the like. I definitely like what Howard van Rooijen is doing with SCRUM work items for TFS. If TFS had a gallery of these templates from the agile community (perhaps through MSDN), then developers and PMs using TFS would have more visibility into the fact that there really are well defined methodologies for Agile Software Development.

All of that said, I love what I see coming out of Visual Studio Team System as it relates to Agile Software Development. Microsoft has incorporated many tools, reports and views that will make feature driven development practitioners and Scrum Masters very happy. Work item backlog integrated with change history, defect and velocity reports, unplanned work analysis... all of these things should get project leaders in the agile community very excited.

Suggestions for the uninitiated:

Read the very succinct wiki on Agile Software Development
Listen to the Agile Toolkit Podcast (many of the Agile Manifesto signatories are interviewed here)
Pick one of the Agile methodologies to study, buy a book, read articles on-line about it.
(Extreme Programming, Scrum, Adapdive Software Development, Crystal Clear, DSDM, Feature Driven Development, Lean Software Development)
If you're still interested, then get into a project with an experienced Agile Software Development practitioner or team.

Interesting links related to Agile Software Development and MSF Agile:

Agile Manifesto (the unifying beliefs)
http://www.agilemanifesto.org/

Short introduction to Agile Software Development
http://www.agilealliance.com/intro

MSF for Agile Software Devleopment
http://msdn.microsoft.com/vstudio/teamsystem/msf/msfagile/default.aspx

An early blog by Scott Bellware, somewhat critical of MSF Agile
http://geekswithblogs.net/sbellware/archive/2005/02/03/21964.aspx

Microsoft VSTS
http://lab.msdn.microsoft.com/teamsystem/

Microsoft's David Anderson gives a webcast about Lean Thinking in MSF v4.0
http://haloscan.com/tb/agileman/Lean_Thinking_in_MSF_v4_0_Webcast

SQL Query Performance - Do you feel dirty? (Dirty Reads)

2005-12-05T16:38:00Z

Ok, so I'm feeling guilty that I was in such a rush to get my blog, and then I failed to posting anything for almost a week. Well, I thought that rather than let another week go by I ought to post something of use. But what to post? It just so happens that I have been working on a document to share some of my experience with optimizing TSQL queries (MS SQL 2000 that is). I know that in the spirit of SQL 2005, it may seem blasphemy to share knowledge about something that was so yesterday as SQL 2000. But, that's what I have. And there you are.

Today's installment will be a tidbit about optimizing queries specifically using locking hints. Depending on the shape and use of the database, I have used this technique to reduce 30-second queries to 2 seconds. Other times, it has made no noticable difference at all. It all depends on the kind of volume and types of transactions hitting the tables being queried.

I'll follow this post up later with more of the same subject. Here you go:

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

Turn off record locking in SELECT

Microsoft SQL has several levels of lock granularity. The server does its best to “pessimistically” control concurrency with locks. This means that the server assumes the worst when it comes to possible concurrency collisions, and protects you from yourself.

Locks can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases. SQL Server dynamically determines the appropriate level at which to place locks for each Transact-SQL statement. The level at which locks are acquired can vary for different objects referenced by the same query; for example one table may be very small and have a table lock applied, while another, larger table may have row locks applied. The level at which locks are applied does not have to be specified by users and needs no configuration by administrators. Each instance of SQL Server ensures that locks granted at one level of granularity respect locks granted at another level. [SQL 2000 online help]

While the server assumes the worst when trying to protect us from ourselves, it also delivers less performance while doing so, and in some circumstances may have dire performance consequences.

Remember Optimization Axiom #2: “Locking is not your friend.” (when optimizing queries)

Example 1:

Imagine that your program runs the following summary query to display revenue for 2005:

SELECT DivCode, DeptCode, SUM(Revenue) AS RevenueSubTotal
FROM AccountsJournal
WHERE ClosedDate BETWEEN ‘2005-01-01’ AND ‘2006-01-01’
GROUP BY DivCode, DeptCode

Let’s say that this AccountsJournal table contains 1,500,000 rows of data, and already has an index for DivCode+DeptCode as well as a separate index for ClosedDate.

Any problems with this? Indexes are in order. The query is optimized. But there is a problem. A big one.

This had better not be running on the main production database during business hours. It might run in less than 3 minutes, but during that time, the SQL server’s locking traffic cop is deciding the fate of every production user of the AccountsJournal table, and whether or not they’ll be allowed to post their updates in a timely manner. Almost as bad, is the fact that this query will also be forced to wait while other update queries finish running. Do you feel lucky?

What is a dirty read?

Simply put, a dirty read is where a query reads data from the database without lock protection. Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running.

SQL Server assumes that you need to be protected from concurrency issues even when you’re running a SELECT statement. What you need to decide is whether or not you agree with it’s assessment of the danger. Ask yourself these questions:

While my SELECT query is running, does it matter whether or not someone else makes a change to a record or two? Will it affect my outcome in a meaningful way?
Are any of the tables in my query in danger of being affected by a mass-update while the query is in the process of running? If so, would it be bad if my query only saw part of the mass-update?

If the answer is, “No, my query doesn’t mind,” then you need to turn off the locking for your query. This is called a “dirty read”. You’re willing to accept less than a pristine and untouched snapshot of data.

When should a dirty read be used?

A better question is, “when shouldn’t a dirty read be used?” It is the author’s opinion and experience that the best practice for database developers is to use dirty reads as the rule, rather than the exception. Let the hate mail begin...

In fact, at two telecommunications companies in the author’s past, it was a serious offense for developers to write any query without enabling dirty reads (without strong justification). At In Touch Communications, in 2001, the development staff had no SQL standards. The MS SQL 2000 database that powered their Siebel CRM system was deadlocking often, and the Customer Service and Sales departments would crash in Siebel at least twice per week. In 90% of the cases, the issue turned out to be long-running queries that had been hand written by developers for various purposes. Some were queries for reports. Others were custom scheduled SQL jobs. All of them used the default locking of SQL server.

Once the In Touch Communications I.T. staff migrated all appropriate stored procedures (99%) to use dirty-reads, the deadlocks went away. Siebel stopped crashing. Later, I.T. afforded a separate SQL server to run their reports, which further increased system performance (also a best practice).

It is rare that a SELECT statement is negatively impacted by using a dirty read. The only cases the author has run into have been in stored procedures that run one or more SELECT statements to gather information just prior to a mass update or delete. In these rare cases, a qualified DBA should consider the data dependencies, and decide whether or not dirty reads are safe for that stored procedure. If locking is allowed on a long-running query (15+ seconds), then the DBA must consider how this query will affect the rest of the system.

How to use dirty reads

Best practice rules:

All SELECT queries should use the WITH (NOLOCK) directive for every table involved in the query.
If it is not practical to use the WITH (NOLOCK) directive on every query, then upon opening a database connection, enable dirty reads as the default behavior for the entire database connection using:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Example 2:

To change our previous revenue query to use dirty reads:

SELECT DivCode, DeptCode, SUM(Revenue) AS RevenueSubTotal
FROM AccountsJournal WITH (NOLOCK)
WHERE ClosedDate BETWEEN ‘2005-01-01’ AND ‘2006-01-01’
GROUP BY DivCode, DeptCode

Example 3:

Here’s an example of WITH (NOLOCK) on a more complex query:

SELECT DISTINCT
      P.Handle, SM.SeatDescription
      , PS.PreFlopSeen, PS.FlopSeen
      , PP.Invested, PP.Won, PP.MonetaryProfit

FROM tblPlayer P WITH (NOLOCK)

INNER JOIN tblPlayerStats PS WITH (NOLOCK)
ON P.PlayerID = PS.PlayerID

INNER JOIN tblPlayerHand PH WITH (NOLOCK)
ON P.PlayerID = PH.PlayerID

INNER JOIN dbo.tfn_Hand_PlayerCount() HPC
ON PH.HandID = HPC.HandID

LEFT JOIN (

            SELECT SM.PlayerCount, SM.SeatIndex
                  , SM.SeatID, ST.SeatDescription
            FROM tblSeatMap SM WITH (NOLOCK)
                  INNER JOIN tblSeat ST WITH (NOLOCK)
                        ON SM.SeatID = ST.SeatID
            WHERE ST.StreetID = 2

      ) SM
            ON SM.PlayerCount = HPC.PlayerCount
                  AND SM.SeatIndex = PH.PreFlopSeatIndex

LEFT JOIN dbo.tfn_PlayerProfit_Summary() PP
ON P.PlayerID = PP.PlayerID

WHERE NOT SM.SeatID IS NULL

Notice that both User Defined Functions in Example 3 do not have a WITH(NOLOCK) directive. More on that later.

Until next time...

Phil Scott
www.neudesic.com
phil.scott@neudesic.com