Migrating from 32-bit to a 64-bit SQL Server installation isn't a trivial operation. You have to contend with a number of factors when you move from one platform to the next. Some of the concerns specifically relate to 32/64-bit platform issues and, in this article, I'll cover three of the most important ones: data source providers, compiled user-defined functions and components and Data Transformation Services (DTS) packages.
Data source providers
Database products on Windows typically offer an OLE DB or ODBC data source provider -- a piece of middleware registered with the system that allows any application that supports OLE DB to talk to that data source. Most of us ought to be familiar with this mechanism, and both the 32-bit and 64-bit editions of SQL Server have their own data providers.
Keep a few things in mind when you're using the 64-bit edition of SQL Server and 64-bit ODBC drivers. First, 32-bit programs cannot see 64-bit ODBC drivers -- they can only see other 32-bit applications, including 32-bit ODBC drivers. The Jet database engine, for instance, does not have a 64-bit driver available; it will only run in 32-bit space and talk to 32-bit ODBC connectors.
You should, however, be able to use the 32-bit driver to talk to a 64-bit database application, for the same reason you'd be able to connect to a remote data server regardless of what it's running. If the actual connection to the database server is performed through a mechanism like TCP/IP or named pipes, those things aren't dependent on a specific architecture. Hence, they should work across 32- and 64-bit environments. (Note that there may be some issues with linking a 32-bit instance of SQL Server to a 64-bit instance via distributed queries, if you're trying to do that.)
If you're using 64-bit Windows and want to edit the configuration of the 32-bit ODBC drivers, you can do this by launching the program %SystemRoot%\SysWOW64\odbcad32.exe, which brings up the 32-bit ODBC console. The default ODBC interface, the one invoked from the Control Panel, is 64-bit only.
At one point, it was only possible to create user-defined functions, or UDFs, in SQL Server by writing them as bits of T-SQL. SQL Server 2005 made it possible to create user-defined functions using SQL Server's common language runtime (CLR). A UDF could be written in Visual Basic, Visual C++ or Visual C# and then deployed as a .DLL, resulting in much better performance than what you'd get through a T-SQL piece.
However, if you've written a UDF to be used with a 32-bit implementation of a database, it'll almost certainly need to be recompiled on the 64-bit platform to work correctly. If you create the UDF in Visual Basic 6 (which is scheduled to be unsupported as of March 2008), you'll need to port it to a more current platform. There it can be recompiled, since VB6 has no 64-bit edition. The same goes for any other components that have been written as 32-bit .DLLs to work with SQL Server -- they'll need to be recompiled as 64-bit code.
Get more on this topic:
I've written elsewhere about the fact that DTS is no longer available in the 64-bit
edition of SQL Server; it's been replaced with SQL Server Integration Services (SSIS). That doesn't mean you can't use DTS packages any more -- just that you can't run them directly on the target 64-bit system. They can be stored on a 64-bit system, just not run there.
One way to work around this is to set up a 32-bit system that could be used to run the DTS packages and then export the data to a 64-bit system. You could even run an older edition of SQL Server with DTS in a virtual machine that's hosted on the SQL Server itself (or running on another computer).
Migration barriers when moving from 32-bit to 64-bit aren't impossible to overcome -- you just have to pay attention and take care. As long as you have access to a 32-bit system, or can mock one up in a virtual machine, you ought to be able to build a bridge away from any existing 32-bit dependencies.
ABOUT THE AUTHOR:
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!