Monday, 8 September 2008

Migrating IIS web application from 32 bit to 64 bit server.

Changing Servers from a 32 to 64 bit environment

By Strictly-Software

I recently had to go through the process of moving a website from a 32 bit windows to 64 bit windows server.

It was one of those tasks that I presumed would be pretty simple and not involve much work but turned into a proper nightmare.

I thought there might have been some sort of “gotcha” guide out on the web somewhere but if there was at the time I couldn’t find it!

I could either find bits and bobs about some individual problems or nothing at all. So I promised myself that on completion I would create an article myself detailing all of the major problems I had to overcome and the solutions I used. So here it is!

The original system was a Windows 2003 server hosting IIS 6.0 and running ASP classic sites.

We had got to the limits in terms of memory allocation and had boosted performance as much as we could in the application (see top 10 tips for boosting ASP classic site performance) therefore we wanted to try moving to a new 64 bit server before considering any large scale application rewrite to .NET.

1. COM objects and compiled dll libraries.

We found that most of our 3rd party COM objects needed upgrading to 64 bit.

You should check each object in turn to see if you need new installations or licences. Objects that we used that needed upgrading included:
  • ASPEmail
  • ASPJPEG
  • ASPUpload
  • DTSearchEngine
  • ISAPI_Rewrite

2. Running scripts.

Scheduled jobs (.bat, .cmd, .vbs) all need to be able to run in a 64 bit environment.

A 64 bit script cannot run a 32 bit process so if your script tries to instantiate 32 bit COM objects
you will get errors unless you upgrade those COM objects to 64 bit (see above).

Another option is to run the script in the c:\windows\sysWOW64\ directory with
cscript which enables you to run 32 bit scripts in a 64 bit environment.

3. ISAPI Rewrite

The name of the rewrite configuration file changes from httpd.ini to .htaccess and if you compare
the format of the files they differ in syntax e.g the flag [I] for "Ignore Case" becomes [NC] "No Case" and a 301 permanent redirect flag of [RP] becomes [R=301].

Also for Rewrite Rules the 64 bit version which is similar to that used on Apache servers requires the first rule to be wrapped in starting and closing tags e.g

# 32 BIT RULE

RewriteRule /some-old-page.htm http://www.newsite.com/newpage.htm [I,O,RP,L]

Becomes

#64 BIT RULE

RewriteRule ^/some-old-page.htm$ http://www.newsite.com/newpage.htm [NC,R=301,L]


There is a helpful conversion tool within IIS that appears in a new tab under each site to convert
your current files to the new format if you don't want to do it by hand.

4. ADO Connection Strings

If you are using an MDAC connection string to connect to an MS SQL database such as:

DRIVER={SQL Server}; SERVER=server_name_or_address;
DATABASE=database_name; UID=username; PWD=password;

You will need to change it to SQLOLEDB as MDAC is not supported on 64 bit windows
systems.

PROVIDER=SQLOLEDB; SERVER=server_name_or_address;
DATABASE=database_name; UID=username; PWD=password;


5. Issues related to changing to an SQLOLEDB connection string.

Changing to the SQLOLEDB provider will mean that you may have problems with SQL that returns multiple recordsets, either client side or server side SQL or stored procedures.

For example with the MDAC connection string, a client side SQL statement like the one below in an example piece of ASP code would work fine.

NOTE the two SELECT statements in the SQL and the objRS.NextRecordset statement against the resulting recordset.


strSQL = "DECLARE @vals varchar(2000); " &_
  "SELECT @vals = COALESCE(@vals + '''', '''', '''''''') + CAST(a.CategoryFK AS varchar(10)) " &_
  "FROM DATA_CATEGORIES_VALUES as a " &_
  "WHERE a.IDFK = 3556 AND " &_
  "a.DataTypeFK = 'JEB';" &_
  "SELECT @vals as val;"

Set objRS = objConnection.Execute(strSQL)

'* move to the next recordset as the first one just builds the string,
'* but it's the 2nd one that returns the values
Set objRS = objRS.NextRecordset

If Not(objRS.BOF AND objRS.EOF) Then
 val = objRS("val")
End If

However using the SQLOLEDB provider will result in an error such as

“Operation is not allowed when the object is closed”

when it tries to move to the next recordset.

I believe that this is caused because within the temporary stored procedures that are created to run client side SQL don't include a “SET NOCOUNT ON” statement which is the first recordset but this line is automatically included by the SQLOLEDB provider and therefore there is no need to move to the next recordset as only one is returned anyway.

Set objRS = objRS.NextRecordset

Using “SET NOCOUNT ON” means that the first recordset implicitly returned by SQL server containing the number of rows affected by the first SELECT statement is not returned.

This would be the case for any SELECT statement that does not itself return a dataset.

6. Stored Procedures and SQLOLEDB

Stored procedures which do not have the SQL statement “SET NOCOUNT ON” at the top of it's code block may also now cause problems if they return multiple recordsets with data and you may get “Operation is not allowed” or “Item does not exist with this name or ordinal” errors when trying to reference the recordset object in your client side code.

Including “SET NOCOUNT ON” at the top of stored procedures is good practise anyway and since SQL 2005 it's part of the default stored procedure template. Therefore any procs that don't include it should be updated to include it to prevent these sort of issues.

7. Returning BLOBS (varchar(max), nvarchar(max), text, ntext etc)

A benefit to changing to the SQLOLEDB provider is that you no longer have to put your BLOBS at the end of your SELECT statements (e.g nvarchar(max), varchar(max), ntext, text).

Previously you had to put these columns at the end of the SELECT statement after any non BLOB columns to prevent them from being returned empty e.g:


SELECT CandidateID, Name, DOB, Town, County, Blob1, Blob2, Blob3


With SQLOLEDB you do not have to do this as it seems to handle them better than MDAC so the following will return values for the BLOB columns even though they are not at the end of the SELECT.


SELECT CandID, Name, Blob1, Blob2, Blob3,  DOB, Town, County

These are the main configuration issues I found when moving to a 64 bit environment but please post any comments if there are other issues related to migrating IIS web applications from 32 to 64 bit platforms.

I am sure there are plenty more!

By Strictly-Software


© 2008 Strictly-Software

5 comments:

Marc said...

Very informative. Thanks. My developers tell me that running ASP Classic on 64 bit windows only works if the IIS is set to 32 bit mode, prohibiting that other 64 bit apps can work on the same server.
What is the bottom line? Can I run standard ASP code concurrently with 64 bit apps on the same IIS, as long as I follow the steps you've outlined.
I want to migrate my app to .NET, but not get railroaded into it by the technology.

R Reid said...

It is true that IIS can run in 32 or 64 bit mode on a 64 bit machine but you do not have to run it in 32 bit mode for ASP classic sites.
I have a large number of ASP classic sites running on a 64 bit server with IIS in 64 bit mode hence the article.

Mahesh Chavda said...

Two things:
1) You can use third party 32 bit COM components on 64 bit IIS by creating COM+ applications that will work as a wrapper over 32 bit components.

http://msdn.microsoft.com/en-us/library/ms679567(VS.85).aspx

2) Use do not need to use SQLOLEDB for 64 bit. 64-bit OLEDB Provider for ODBC (MSDASQL) Is Now Available For Windows Server 2003.

So your query string may look like:
Provider={MSDASQL};Driver={SQL Server} ;.........

Thanks,
Mahesh
mahesh.chavda@gmail.com

(0.o) said...

Great article. Summarise my pain for the past few days! Any tips on excel file import? lack of 64-bit excel driver is forcing me up the wall... =p any help is appreciated. hah!

R Reid said...

Yes I feel your pain regarding the lack of XLS and Text drivers on 64 bit boxes as I also have an admin system that relies on those drivers to upload files.

I haven't actually solved the problem yet due to the fact I have some 32bit servers that connect to the same system so I am using those as a workaround but some of the ideas I have had are:

-Creating my own file parser to handle CSV files. It would be pretty easy to rewrite some of the freely available .NET classes people have created for this issue in ASP classic.

-Using a .NET proxy page/ web service to handle the task.

-Creating a DTS/SSIS package to handle file transfers into a temp table. Save the file on the webserver then execute the package to import the data into a table before returning the recordset if you need to.

-Hope and pray someone at MS creates a suitable driver for 64 bit. I am not 100% positive on this but I vaguely remember reading somewhere that in windows 2008 there would be a driver for this issue.

If you find another solution please post the details :)