Sql-server – Migrating SQL database to a new SQL server

migrationsql server

Thanks for viewing my question. I currently have a database on SQL Server 2005, which is being migrated to SQL Server 2008. Each SQL Server is named differently in SSMS. The old is named GARDB1, and the new GARDB2008. GARDB2008 is in production with everything running smoothly besides this is the last database I need to move over. Now, obviously both servers have different IP addresses, but I can add the old IP to the new server after migration.

My problem is in connecting too MANY different reports we have connected to the database after migration. The connection strings do not include the IP address, only the name GARDB1. There is also a website attached to this database.

How can I migrate this database to the new server while keeping everything connected? It is okay to have downtime while doing this. I tried creating a new alias in SQL configuration manager, but this did not help. What am I missing?

Thanks for any help!


Best Answer

You can use CNAME in DNS. When you're done with GARDB1, rename it to something else (GARDB1_OLD). Create a CNAME in the DNS server called GARDB1 which should point to GARDB2008. It should solve your problem because GARDB1 will resolve to GARDB2008 now. Ideally what you need to have is a generic CNAME that you can just re-point to a new DB server each time you are migrating. Perhaps next time you plan to migrate, you can implement it. Or you could go with what @SeanGallardy suggested.