[Note: This post is based upon an old blog post that I'm migrating for reference purposes, so some of the content might be a bit out of date. Still, hopefully it might help someone sometime...]
Some recent technical pain points...
Visual Studio 2008 Web Application Projects & Profile Properties
To cut a long story short, VS2008 Web Application Projects don't natively support ASP.NET Profile Properties. In VS2008 Web Site Projects (which, in case you hadn't heard me ranting previously, I loathe), the "Profile" class is dynamically generated behind the scenes when the Profile is set up in the config file. However, the same doesn't occur in Web Application Projects. You can utilise this tool [http://code.msdn.microsoft.com/WebProfileBuilder] to enable support for building the Profile class as part of the build process. Think hard about using Profile Properties though: should these properties really be "first-class citizens" of your underlying database schema, rather than "tacked-on"?
Using .NET TransactionScope with SQL Server 2000
Another goodie. TransactionScope, introduced in .NET 2.0, makes transactional .NET code a breeze! It uses a lightweight transaction in most cases, until the transaction requires escalation to a distributed transaction. Unfortunately, one of the cases where it doesn't use a lightweight transaction is when you're working against a SQL Server 2000 database. Yes, even if you're only accessing a single database on the SQL Server 2000 instance, you still start out with a distributed transaction: which means MS DTC becomes involved, and must be suitably configured on both the web server and database server.
MS DTC Authentication between a Computer in a Domain and a Computer not in a Domain
Following on from the previous item...
Of course, in our situation, we were deploying to an environment where we had a database server that was a member of the corporate domain, and a web server that was in the DMZ, and not a member of the domain. There are 3 options for authentication between DTCs: Mutual Authentication (preferred), Incoming Caller Authentication, and No Authentication. Because in our scenario there's not common point of reference for authentication between the DTCs (and no trust can be established), we had to go with No Authentication. We then had to do what we could to further secure DTC communication between the web and database servers through firewall rules restricting ports and IPs.
SQL Server Collation Differences between the Server and the Database
SQL Server has a default collation for the server instance. By default, when you create a new database, it uses this collation. However, it's also possible to specify a different collation for the database (it's also possible to specify a different collation again on a column-by-column basis inside tables within the database, but that's just an aside).
Normally this isn't a problem (other than it's nice to decide on a collation and stick with it unless you really need a different collation). However, when you have stored procedures or functions in your database that create and use temporary tables, a difference in collation between the database and the server instance can be a problem, particularly if you're trying to join between tables in your database and the temporary tables you've created. In this case, you'll get a collation mismatch error.
The workaround is to specify the COLLATE DATABASE_DEFAULT statement for collation-aware columns when creating temporary tables in tempdb. This will ensure that the temporary table column collation matches that used in the database. Then you'll only have a problem if for some reason you've used yet another collation for the specific columns in your database you're joining on... Yay for collation!