March 3, 2009 at 9:46 am
I need to migrate a SQL 2005 to SQL 2005. Sounds simple enought correct? It is just being transferred to new hardware.
1. Should I Install the exact service pack and hotfixes on the destination server then copy over
* master
* msdb
* model
* mssqlresourcedb
to the new server? Hence, overlaying the installed system databases I just installed with the ones copied from my old server? I am trying to get the easiest most consistent way to do this. I know I can get the stored procedure from MS to script logins and their SIDS (ie the sp_rev logins). This new server will be on the same domain.
How would any of you all do this. Consider that Great Plains is on this SQL Server and it must be migrated.
Current service pack is Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Thanks for any insight you can give...
Matt
March 3, 2009 at 9:56 am
The way you list will work but there are few things you need to know before you commence:
* will the new hardware be renamed to the old server name?
* do you have downtime to perform this?
* how big are your user databases and are you able to copy them over to the new hardware with both servers on the network?
* will there be any parallel running?
* will the database files be in the same location on the new server
* how do you plan on re-pointing your apps to the new server?
Once you have all this info then you can begin.
1) build the new server to the same edition and SP level.
2) restore master database backup from old server - you will need to start SQL in single user mode to do this
3) restore msdb
4) restore or re-attach your user databases
5) once older server offline you may need to rename the SQL servers
you should be good to go
thanks
SQL_EXPAT
March 3, 2009 at 10:21 am
I am assuming I should copy mssqlresourcedb.mdf and .ldf as well?
Thanks for your input. 😀
Matt
March 4, 2009 at 3:22 am
No, not necessary. ResourceDB is used for upgrading and rolling back service packs. You new install on the new hardware will create one of these for you. AFAIK you dont need to concern yourself withe ResourceDB at all.
thanks
SQL_EXPAT
March 4, 2009 at 9:32 am
i've done this and it depends on the server
for one machine i just restored the db's, copied the jobs over and recreated logins manually. then when it was done i renamed the old machine and renamed the new machine to the original name
for some critical servers that we didn't want to take a chance on we backed everything up, detached the db's, uninstalled SQL, had the storage admin do his thing on the backend to map the HBA's to the correct disks in the EMC, install SQL on new cluster, install SP's, attach db's, create logins and create jobs from script and then recreate replication from scripts.
we did this so the new machines keep the same name and IP, just in case
March 4, 2009 at 10:06 am
Thank You for your input folks!!! much appreciated! 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply