DevelopMENTAL Madness

Friday, June 30, 2006

SQL Server 2005 Unattended Install

As always I try and post about my experiences which cause my biggest headaches. So today's topic is about setting up an unattended install for SQL Server 2005. I have spent the entire day trying to create an unattended install to a Virtual Server installation.

Error: Invalid INI file. Make sure the file exists, have access and has the correct entries.

This one still does not make sense to me. First, I had the INI file in the same directory (Desktop) as my setup .bat file. And I had the command window open to the same directory as well. So I figured it required a fully qualified path. So I figured it was the spaces in the path "C:\Documents and Settings\Mark\Desktop\setupSql05.ini" and I put quotes around it. Still no go. So finally, I just dumped the file in the root and updated my .bat file as follows:

D:\Servers\Setup.exe /settings "C:\setupSQL05.ini" /qb

And that did the trick!

Error: SQL Server Setup could not validate the service accounts

I'm sure there are a number of reasons why you might see this error. But in my case it was stupidity brought on by fatigue. I created 3 domain accounts: {domain}\SqlServer, {domain}\SqlAgent, and {domain}\SqlBrowserAgent. I set the password to never expire, except on SqlBrowserAgent. It turns out I forgot to change this when I created the account. The nice thing was that I was able to determine the account by checking the setup log file "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetupXXXX.cab\{hostname}_SQL.log". I opened it in notepad, did a find for my domain name ( "{domain}\" ) and found the error in the log. Once I opened up the settings for the account in question it was obvious.

Sometimes it just doesn't pay to get out of bed!

Error: The feature(s) specified are not valid for any SQL Server products

Again, the log file came in for a save here. Silly me, I used the documentation to create my INI file! According to BooksOnline for the ADDLOCAL parameter, Notification_Services has a 3 child features: NS_Engine,NS_Client,NS_Rules. So because I wanted to install Notification Services I passed all three to the argument. However, when I got the above error and verified that I had spelled everything correctly (including case) and had no spaces I was stumped. So I checked the error log ({hostname}_SQL_Core(Local).log) and found this:

Failed to find feature:NS_Rules : 70002

So I opened the template.ini file that comes with the installation disk and found that in truth, NS_Rules does not exist. Greatful for install logs I removed the feature from the list and ran install again. Only for the same error to strike again! This time the culprit was SQL_Profiler90, also in the BOL docs I might add.

Failed to find feature:SQL_Profiler90 : 70002

So make sure you don't fall for the same trap as I did!

Success at last!

Here is my successfull settings INI file (don't forget to add the [PIDKEY] option):


[Options]



USERNAME=Admin

COMPANYNAME="MaxPreps, Inc."



ADDLOCAL=SQL_Engine,SQL_Data_Files,SQL_Replication,SQL_FullText,Notification_Services,NS_Engine,NS_Client,Client_Components,Connectivity,SQL_Tools90,SQLXML


INSTANCENAME=MSSQLSERVER



SQLBROWSERACCOUNT="{domain}\SqlBrowserAgent"

SQLBROWSERPASSWORD=***********



SQLACCOUNT="{domain}\SqlServer"

SQLPASSWORD=***********



AGTACCOUNT="{domain}\SqlAgent"

AGTPASSWORD=***********



SQLBROWSERAUTOSTART=1

SQLAUTOSTART=1

AGTAUTOSTART=1



SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS



ERRORREPORTING=0






I hope this helps anyone out there who's struggling. And can I just say, thank goodness for Virtual Server!

Labels: ,

Saturday, June 24, 2006

BUG: ASP.NET 2.0 HttpApplication cannot find IHttpHandlerFactory when HttpContext.RewritePath(string) includes PathInfo

This week we've been testing our migration configuration for our upgrade from the .Net 1.1 Framework to 2.0. Last summer I wrote an HttpModule which masks our urls. Internally our url structure resembles "http://domain/path/page.aspx/pathinfo?querystring". Where Page.aspx is always the same and PathInfo is the path to a template file. But in order to optimize our urls for search engines the HttpModule I wrote changes that format to "http://domain/path/pathinfo.mxp/querystring. Where querystring was State=CA&SSID={GUID} it is now California/Boys_Varsity_Football_Fall_05-06. We use a series of RegEx objects to determine which url format was used. If a UserAgent requests a page using the first url we send back a 301 redirect. The purpose of this is to get search engines to update their indexes with the new path. If the second url format is used we reformat the url to match the first and use the HttpContext.RewritePath(string) method to forward the actual url to our web application. Like this:

HttpContext.Current.RewritePath("/path/page.aspx/pathinfo?querystring");

When we installed the .NET 2.0 Framework and configured our development server with ASP.NET 2.0 the masking stopped working. Instead we were getting errors. We found that our pages were being caught in an infinate loop. The HttpApplication.OnBeginRequest event was being raised over and over, alternating between the urls. Our module would recieve the request, reformat it, call HttpContext.RewritePath. Then it would get called again, this time it would see the url in the internal format and send a redirect. Which would then trigger the entire process again. The Page classes would never get called.

I created a sample project which duplicates our process but is much simpler. I found that the problem was with the PathInfo when we reformatted the url to the internal format. In order to fix the problem we had to split the url into parts and use the HttpContext.RewritePath(string, string, string) method. So when this url comes in :

http://domain/folder/template.mxp/california

We reformat it as:

/folder/page.aspx/template.mxp?State=CA

Then we break it into 3 parts (path, pathinfo, querystring) and pass it as:

HttpContext.Current.RewritePath("/folder/page.aspx", "template", "State=CA");

Once I figured this out and fixed it, I was curious to know where the problem was caused. So I opened up Lutz Roeder's .Net Reflector and dove into the HttpContext.RewritePath code. I couldn't anything that would cause what we were seeing. So then I decided to add a bunch of empty methods to a Global.asax class and put a break point in each to see where things went wrong. I added all the per request application events and added a break point in Page.Init() and Page.Load() as well to see if the request ever got to the page class.

When there was no PathInfo in the url it hit every break point. But when PathInfo was included it would hit every application request method, but the Page.Init() and Page.Load() methods were never called. So I created a class which inherited from System.Web.UI.PageHanderFactory, overrode the GetHandler method and put a breakpoint in it. This is called by the Application.OnResolveRequestCache event. But when PathInfo was included in the url, it was never called. This explains why the Page class was never called - because it couldn't find an IHttpHandler for the page, so it just redirected the user. (I didn't verify this by checking IIS logs, but I'm simply describing what seems to happen). This is what caused the infinate loop.

The code below is the sample project I created. Which does not excatly duplicate our process, but it does duplicate the bug. I hope this helps anyone out there with a similar problem and I will be submitting this to Microsoft along with the work around.

IIS Configuration

Add *.mxp to application mappings:

  1. Open IIS.mmc
  2. Right-click website - select properties
  3. Open Home Directory tab
  4. Click Configure
  5. On the Mappings tab click Add.. :

Executable: C:\windows\Microsoft.NET\Framework\v2.0.50727\aspnet_isapi.dll
Extension: .mxp
Verbs: All verbs
Verify file exists: no

  1. Click "ok" on all open dialogs

Web.config

<?xml version="1.0"?>
<configuration>
<system.web>
<compilation debug="true"/>
<httpHandlers>
<add verb="*" path="*.aspx" type="UrlRewrite.myPageFactory"/>
<add verb="*" path="*.mpx" type="UrlRewrite.myPageFactory"/>
</httpHandlers>
</system.web>
</configuration>

myPageFactory.cs


using System;
using System.Web;
using System.Web.UI;

namespace UrlRewrite
{
public class myPageFactory : PageHandlerFactory
{
public override IHttpHandler GetHandler(HttpContext context, string requestType, string virtualPath, string path)
{
return base.GetHandler(context, requestType, virtualPath, path);
}
}
}

Global.asax (remove comments to fix bug)

<%@ Application Language="C#" %>

<script runat="server">

void Application_OnBeginRequest(object sender, EventArgs e)
{
HttpApplication application = (HttpApplication)sender;

string url = application.Request.Url.AbsolutePath;
if (url.Contains(".mxp"))
{
url = url.Replace(".mxp", ".aspx");
/*
if (url.IndexOf(".aspx/") != -1)
{
//if there is pathInfo in the url it can't be sent as a single url, it has to be separated or we endup in a endless loop
string info = string.Empty;
int i = url.IndexOf(".aspx/") + 6;
info = url.Substring(i);
url = url.Substring(0, i - 1);
HttpContext.Current.RewritePath(url, info, string.Empty);
}
else
*/
HttpContext.Current.RewritePath(url);
}
else if (url.Contains(".aspx"))
{
url = url.Replace(".aspx", ".mxp");
application.Response.Redirect(url);
}
}

</script>

Labels: , , ,

Thursday, June 22, 2006

SQL Profiler - Replaying Traces

SQL Profiler has been aptly referred to as the "poor man's load tester".

3 months ago I captured trace logs spanning 1 week. My intent was over the summer (our companies "off-season") to use the trace logs to stress test the database to determine the results of server and database configuration changes. Now I'm spending most of my morning trying to replay the trace logs I created using the system stored procedures 3 months ago.

Well, here's what I've discovered:

  • The database ID in the trace log has to match the database id of the database on the server where you are replaying the trace.

  • The login name has to match the login name you are using to connect to the server where you are replaying the trace.

  • The login you are using must have their default database set to the database you are replaying the trace against.

  • The login you are using to replay the trace must have permissions to replay the commands you've captured in your trace.

  • The trace captured by the SQL 2000 stored procedures (by default) will not replay with SQL Profiler 2005.

I'm sure someone will tell me that I haven't captured all the correct events. And that may be so. But I'm still pretty new (relatively) to DBA responsibilities. I'm planning on using our SQL Server upgrade this summer as a chance to learn to do DBA stuff the right way and maybe get certified while I'm at it.

Wish me luck.

Labels: , , ,

Monday, June 19, 2006

Hello World!

As all good developers, my first words are the well-known greeting which states, "Hey it works!". I have no idea who will be interested in my comments, but I wanted a place were I could share my discoveries with whoever was interested.

I have found so much helpful information on the web which has enabled me to do what I love and do it well. I have wanted to return the favor for sometime, but I just don't have to time to sit down and write a well-structured coherant article. But when I learn something, I want to put it out there for anyone who's having the same problem. Hopefully they'll find the answer in what I will write.

If they don't, I recommend www.codeproject.com, my favorite source code site and community of developers.