32-bit to 64-bit SQL Server migrations

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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Migration Strategies and Planning eZines and eBooks for SQL Server professionals New SQL Server 2008 R2 CTP set for November PASS Summit 2009 Preview Are data warehouses made for the cloud? Q&A: Moving forward with SQL Server in the cloud SQL Server Mailbag: Migrating down to Standard Edition Microsoft releases SQL Server 2008 R2 CTP A first look at Microsoft SQL Server 2008 R2 Using Microsoft Hyper-V for SQL Server consolidation Migrating to SQL Server 2008 and leveraging new features Database Management and Administration Password cracking tools for SQL Server Using traces in SQL Server Profiler Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

>%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.

UDFs

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.

DTS

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).

Conclusion

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!
Copyright 2007 TechTarget

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.