Hardware Migration on MSSQL 2005!!!

[Former Member]

Dear friends,

We are planning to do H/W migration of our ECC Production system on same win + mssql version.

According to note no 151603, instead of attach / detach we can simply copy the data files after SQL server start and stop.

As this is  a production system, I wanted to be sure whether this would  not cause any inconsistency in the target DB.

Can you please let me know the correct approch?

Thanks,

Vishal

[Former Member · Accepted solution]

While it would work, the recommended approach is to do a backup/restore process - backup the databases from the old system and restore them on the new system. This makes sure that you have a backup in case something happens in the new system. It also makes sure that you do not get the database files corrupted during the copy process. When you stop the SQL Server service, the lock on the database files is released, freeing it up for access from other processes such as anti virus and backup applications, etc. This file locking mechanism may introduce corruption in the database files and may appear after attaching them back to SQL Server after restarting the service. This is the reason why I don't recommend the detach/attach process for migrating/upgrading.moving SQL Server databases

[Former Member · Accepted solution]

Thanks Edwin for helpful answer!!!

So,  in this case I would prefer setting up log shipping on the target H/W (DR setup) and after the cut off , shutdown the original system --> start target DB --> perform the system copy.

It will save my time and ensure the integrity of the database.

How about this approach?

Regards,

Vishal

[Former Member · Accepted solution]

If this is a SQL Server 2005 instance, database mirroring would be more appropriate as you can configure synchronous replication between the new instance. This will definitely save you more time than log shipping since failing over the database mirroring session will automatically bring the databases on the new system online while bringing the ones on the old system in recovery mode. No need to shutdown the original system. After you have gone live to production, you can consider your old system as standby system for HA/DR purposes.

One more thing to add is to make sure that you transfer your SQL Server logins as well so you don't have orphaned database users. Use this Microsoft KB article for this process.