We help IT Professionals succeed at work.

What is involved as far as migrating from a Windows 2003 (32-bit) SQL Server 2005 to a Windows 2003 (64-bit) SQL Server 2005?

vcomtech
vcomtech asked
on
Scenario:
Windows 2003 Enterprise Edition (32-bit) - SQL Server 2005
2 servers clustered Active/Passive.

We are looking to migrate the databases over to 2 new servers located at a data center.
Windows 2003 Enterprise Edition (64-bit) - SQL Server 2005
2 servers will be clustered Active/Passive.

I've never migrated an SQL database and I've done some research on the internet to help. I'm not fully in charge of the migration but I am involved in the QA portion of the migration. Being part of the QA team I'm sure knowing how the migration process works would help me come up with a near perfect checklist to ensure that things run smooth.

Any recommendations to resources on the internet would be greatly appreciated. Here are some of my main concerns in regards to the migration.
1. What's the best way to copy/transfer the database environment over to the new server? Over the VPN connection to our data center is not a feasible idea since the database is well over 30GB.
2. Would the database environment need to be copied to both servers or will replication take care of this? I'm assuming that the only one copy is needed on one of the clustered servers.
3. Are there any compatibility issues between the 32bit and 64bit operating systems?

Thanks In Advance!
Comment
Watch Question

ideas

export/import?? using teh dump files on a media then send teh media theer instead of using a conenction (30gb is a lot!!!!!)
Commented:
1. If your datacenter is near your database current location, the best way (fastest, the most secure) is to transfer your database on a physical media directyly to datacenter.
2. Because the database format is identical on x86 and x64 platforms you can just stop sql server, copy database files (with logs!) and attach them to the new machine (copy in the same place on disks if possible and start service). You can also backup databases and restore them on the new machine. If machine name is different than the old one, after attaching/restoring master database you will need to change local server name (with sp_dropserver and sp_addserver).
3. There are some compatybility issues when migrating to 64-bit:

A. A lack of OLEDB 64-bit providers. The Jet and ODBC is mostly the greatest problem. If you use them via SSIS or linked servers to Excel, for example, you may have a problem.
B. Third party extended stored procedures will stop working (as they are in 32-bit dll-s). Most of them must be rewritten in CLR.
waiting for your reply

Author

Commented:
Thanks for the help.

1. I think the best way for us to transfer the data over will probably be tape backup. Both datacenters have a tape drive and backup server. This should be easy enough.

2. Well the question here is, will the servers self-replicate since they will be set up in clustered mode? Or will the database(s) have to be copied on to both servers in the cluster (offline of course)?

3. This info is great to know. EE is always a great place to find answers.
are they a mirror servers? have you setuped them that way ?

Author

Commented:
I'm not the one that will be setting it up but from the info I've gathered, they will be setup as a cluster active/passive for failover. So yes they will be set up as mirrors. What's the usual sync/replication method when the cluster is set up for SQL; copies need to be made on both servers or just 1 and the other will sync across?

thanks
the one who will setup the remote server should make it automatic as they are a cluster. usually i prefer the export/import with databases. anyways , i would suggest a local copy backup system as the data size is huge and the link is not wide enough.

waiting for your feedback

Author

Commented:
sorry dont want to sound repetitive but what do you mean by automatic? does this mean that when the database has been moved to the primary server, it will automatically replicate to the secondary server? as far as moving the database, a full backup of the database will be performed while the database is stopped to ensure no new data being written. then a restore of the database on to the new server. ive also cited this link as a possible source or performing this move/migration.
http://support.microsoft.com/kb/224071

let me know what you think.
as you said , the guy ( or girl) responsible for creation of teh master db will make sure of that. if that what you are looking for now , we can go deeper into that. looks that you already familiar with the suitable ms links that had to be read. anyways, please let us be focused on what we need to solve :)

waiting for your reply

Author

Commented:
Thanks for the help!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.