Moving data between 32-bit and 64-bit SQL Server instances

    Yes, you can move SQL Server data back and forth between x64, x86, and IA64 architectures. The data and log files themselves do not store anything that indicates the architecture and work the same on either 32-bit or 64-bit. The same applies to the backup files. Given those facts it becomes clear that we can easily move data between architectures. You can backup on x86 and restore to x64. Detach/attach works fine. Log shipping works because it is basically backup/restore with some scheduling. Mirroring and transactional replication take data from the transaction log and push the data to another system so again they work across architectures. Merge replication is basically just another application sitting on top of SQL Server, it moves data by reading tables in one location and modifying data in another location. Again, this can all be done across architectures.

   Hopefully you are not installing new x86 boxes, 64-bit handles memory so much better. If you have legacy x86 boxes you can easily do a backup or detach from that old system and restore or attach on the new x64 instance. You can also reverse the process and copy data from x64 back to x86. The same logic applies to the other technologies listed above.

Per BOL (I used the SQL 2008 R2 version):

·         The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, a database mirroring session can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.

·         Because the SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments, a replication topology can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.

·         The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, a log shipping configuration can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.

If you're doing SAN level replication you'll need to talk to your SAN vendor about their support across platforms.

Read more: Cindy Gross - Troubleshooting, tips, and general advice about SQL Server

Posted via email from jasper22's posterous