32- and 64-Bit Connectivity from the Same Machine
Author: Chuck Heinzelman
Reviewers: Steve Howard, Carl Rabeler, Shaun Tinline-Jones, Mike Weiner, Murshed Zaman
I was recently involved in a customer lab testing the Microsoft Business Intelligence stack. I noticed an interesting behavior when working on one of my test servers.
Here’s the environment that I was working with:
· Windows Server 2008 R2 (64-bit)
· SQL Server 2008 R2 (64-bit)
· SharePoint 2010 (64-bit)
· Excel 2010 with PowerPivot Add-In (64-bit)
· Visual Studio 2010 (32-bit)
See the problem yet? I had a mix of 32-bit and 64-bit applications on the same machine.
Many developers and database professionals are running 64-bit operating systems and tools these days. For the work that we do, the larger address space that 64-bit processing provides allows us to work with larger data sets and do our jobs better. To facilitate this, companies that produce DBMSs and other data sources are shipping 64-bit drivers and connectivity tools to connect to their systems.
In a mixed-mode environment like the one outlined above, connectivity can prove to be quite tricky. You will need to have both a 32-bit and a 64-bit driver loaded on your machine to make this work properly. In the software scenario outlined above, you would need:
· 32-bit driver to work with the data from within the Visual Studio environment
· 64-bit driver to work with the data from within PowerPivot and SharePoint
This problem is not limited to the applications listed above. I have seen it crop up in the following arenas as well:
· SSIS – When running in the BIDS designer (a 32-bit application), you need to have 32-bit drivers installed to use the design-time tools. SSIS packages can be run using either the 64-bit or the 32-bit SSIS engine (64-bit is default, but this can be changed by setting the Run64BitRuntime property to false). If you do not have 32-bit drivers installed on your machine, you will not be able to connect to your data sources at design-time. Also, if you deploy a solution where only 32-bit drivers exist to a 64-bit server, you will need to install the 32-bit drivers on the server and explicitly schedule the package to run using the 32-bit SSIS runtime to use the 32-bit drivers. More information can be found in the article 64 bit Considerations for Integration Services on MSDN (https://msdn.microsoft.com/en-us/library/ms141766.aspx).
· Report Builder – Report Builder is a 32-bit application, and the design-time experience will require 32-bit drivers. When you preview a report, your experience will vary depending on if you are connected to a report server or not. If you are connected to a report server, the preview will run in the context of that server and you will need to have the appropriate bit-level drivers installed on the server. If you are running disconnected, Report Builder itself will do the rendering using the 32-bit drivers.
If you are using SQL Server as your data source, the 64-bit SQL Native Client installer will install both the 64-bit and 32-bit drivers side-by-side. If you are extracting data from a non-Microsoft database platform, you will need to check with the database manufacturer to determine how to install their drivers side-by-side. You might also run into this issue when extracting data from data sources such as Microsoft Excel – which up until Microsoft Excel 2010 did not have a 64-bit version.
In some cases, you might not be able to install both the 32-bit and 64-bit drivers side-by-side. In this case, your options are fairly limited. The easiest way to get around this is to install all of your client tools at the same bit-level – which could mean running the 32-bit versions of all of your tools on your 64-bit machine.
Conclusion
Each data source provider is going to have different drivers and different configuration methods, so I cannot go into depth here into how to configure both the 32-bit and 64-bit drivers on the same machine for each provider. You might even run into scenarios where the installation procedures are different from one version to the next from the same provider. The easiest way to get around this problem is to try to run everything at the same bit-level, but that is not always possible. If you cannot run everything at the same bit-level, you will need to consult the manufacturer of your data source for their best practices on side-by-side driver installation.
Additional Resources
64 bit Considerations for Integration Services - https://msdn.microsoft.com/en-us/library/ms141766.aspx
Data Sources Supported by Reporting Services (SSRS) - https://msdn.microsoft.com/en-us/library/ms159219.aspx
Data Access Technologies Road Map - https://msdn.microsoft.com/en-us/library/ms810810.aspx
How to get a x64 version of Jet? - https://blogs.msdn.com/b/psssql/archive/2010/01/21/how-to-get-a-x64-version-of-jet.aspx
SSIS: 64-bit Story - https://social.technet.microsoft.com/wiki/contents/articles/ssis-64-bit-story.aspx