Changing Servers from a 32 to 64 bit environment

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 bit of a nightmare. I thought there might have been some sort of “gotcha” guide out on the web somewhere but couldn’t find anything more than details about individual problems or nothing at all. So I promised myself that on completion I would compile one myself of problems and solutions I found on the way.

The 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) and we wanted to try a move to a new 64 bit server before considering any 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) 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.
The other options is to run the script in the c:\windows\sysWOW64\ directory with
cscript that enables you to run 32 bit scripts in a 64 bit environment.

3. ISAPI Rewrite
The name of the configuration file changes from httpd.ini to .htaccess and if you compare
the format of the files they differ in syntax.
There is a conversion tool within IIS that appears in a new tab under each site to convert
your current files to the new format.

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 (client side SQL/stored procedures)

For example with the MDAC connection string, a client side SQL
statement like the one below 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)
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 such as this a “SET NOCOUNT ON” must be
automatically included by the SQLOLEDB provider which is not present in
MDAC and therefore there is no need for the

Set objRS = objRS.NextRecordset

Using “SET NOCOUNT ON” means that the first recordset implicitly returned
by SQL server containing the number of rows effected 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 is included as part of the default template for new stored procedures in
SQL 2005. Any procedures that do not currently have this statement should be
edited to include it.

7. Returning BLOBS (varchar(max), nvarchar(max), text, ntext etc)
A benefit to changing to SQLOLEDB I have found is when referencing multiple
BLOBS in SQL (nvarchar(max), text etc) whereas previously you had
to put these columns at the end of the SELECT statement after any non BLOB
columns to prevent them being returned empty eg

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.

Tweet