Hi all,
Does anybody has any practical experience in restoring a SQL Server 2005 database created in 32 bit (with data) into SQL Server 2005 64-bit and run natively? I'm exploring the possibility (and feasibility) of doing so here, and may actually do it.
I read from some SQL Server migration articles, all of them mentioned that the migration is straight forward. There isn't even need to change the application code that connect to the database. Then from googling and live-ing, i found several more important notes:
1. We'll need to re-create & re-map the database logins, and re-create user objects on the SQL Server x64. (Well, this is pretty obvious) [Read here for why, and here for how]
2. Jobs will need to be migrated manually. (Or did I miss anything here?)
3. This kb article seems to be contradicting the migration article above, in regards to application code changes. There could be some OLE DB providers and some ADO.NET providers are not available in 64-bit versions (luckily, I'm only interested in SQL Native Client).
4. In the same article (#3 above), "cannot run a SSIS package that contains the Execute DTS 2000 Package task in a 64-bit environment. Simply because there is no DTS runtime in 64-bit version". (Expected! Now I'll have to re-write the DTS package into SSIS. )
I wonder if there is any other critical issue that I'll need to look into? Specifically, in regards to relational database, DTS & SSIS Package, SQL Agent, and perhaps collation (does it matter?). Pointers will be much appreciated. Thanks in advance.