September 17, 2015 at 12:45 pm
I am migrating a server instance from one server to another with much better hardware, while keeping the database files on the same lun.
I will be installing SQL2008R2 on the new server, and the plan was to detach and attach the databases.
My biggest concern is how to deal with all the other objects such as logins etc. my thought was to do it one of two ways.
1. after detaching the databases. turn off the sql services, copy over the system databases to the new server, start it up, then attach the databases.
2. create scripts ot create the database objects needed in on the new server, attach the databases to the new server, then run the scripts to create the objects that are not copied over.
Any thoughts to which method would be better? or if there is a better method?
September 17, 2015 at 9:30 pm
Hi,
The best way to decide would is to test in DEV environment.
as per MSDN article both methods should be fine. https://msdn.microsoft.com/en-us/library/ms345408.aspx#master
also, you can rename the new drive as the old drive paths..
thanks.
September 17, 2015 at 9:53 pm
lyle.schow 28602 (9/17/2015)
create scripts ot create the database objects needed in on the new server, attach the databases to the new server, then run the scripts to create the objects that are not copied over.
Any thoughts to which method would be better? or if there is a better method?
Just to let you know, Logins are not directly scripted via SSMS. Need to use sp_help_revlogin to script out. Secondly, if you have linked servers, they are not scripted either. Backup and restore of master, msdb databases are much better way to ensure system objects are seamlessly transferred. In case if you are using encryption, please ensure to backup service master key, database master keys of old server as well.
Regards,
Raj
September 18, 2015 at 3:51 am
lyle.schow 28602 (9/17/2015)
I am migrating a server instance from one server to another with much better hardware, while keeping the database files on the same lun.I will be installing SQL2008R2 on the new server, and the plan was to detach and attach the databases.
My biggest concern is how to deal with all the other objects such as logins etc. my thought was to do it one of two ways.
1. after detaching the databases. turn off the sql services, copy over the system databases to the new server, start it up, then attach the databases.
2. create scripts ot create the database objects needed in on the new server, attach the databases to the new server, then run the scripts to create the objects that are not copied over.
Any thoughts to which method would be better? or if there is a better method?
Do not copy system databases, it generally provides more problems than you'll want to encounter.
Presumably your new server has a different computer name?
Unless you install the new system with the exact same configuration (computername, instancename, disk paths, etc) you'll not even be anywhere near close.
migrate all objects out of the system database and apply to the new server, switching LUNs is also a bad idea generally, I've had to fix issues with systems such as these where a patch couldn't be later applied because it thought I was already installed (yes they had copied the system databases) and server encryption was severely affected.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 18, 2015 at 3:56 am
September 18, 2015 at 9:27 am
Copy Database Wizard is typically how I have done it in the past. The detaching the same databases to the lun and then attaching the lun to the new server is something our System Admin has been pushing for. I have indicated that there is much more to it than that, but I am not getting through very well. I may push to use the Copy Database Wizard again since it is a proven method of migrating the databases.
September 18, 2015 at 9:29 am
Thank you! You have confirmed my biggest fear using this method, which is copying over configurations that will not fit with the new server.
September 18, 2015 at 9:32 am
The plan was to set up the new server by pointing the domain name from the old server to the new. I sense that there is more to it that that in your response. My fear is that simply pointing the domain name will not be enough when copying over the System Databases.
September 20, 2015 at 1:54 am
DO NOT CHANGE THE DOMAIN NAME in order to perform your migration, SQL Server stores machine name internally so just changing the domain name to the old value will not automatically make SQL think its still the same old server. If you are really intent on trying this use "Alias" instead. I Come across many cases during trainings where one system is prepared and images and the image applied on participant systems, next thing I know we all share the same hostname but different IP address and depending on how they are connecting TCP/IP with hostname or IP they end up connecting to each others machines instead of localhost
September 21, 2015 at 10:32 am
Why on earth are you using copy database wizard to migrate databases, just use backup\restore or detach\attach
Jayanth_Kurup (9/20/2015)
DO NOT CHANGE THE DOMAIN NAME in order to perform your migration, SQL Server stores machine name internally so just changing the domain name to the old value will not automatically make SQL think its still the same old server. If you are really intent on trying this use "Alias" instead. I Come across many cases during trainings where one system is prepared and images and the image applied on participant systems, next thing I know we all share the same hostname but different IP address and depending on how they are connecting TCP/IP with hostname or IP they end up connecting to each others machines instead of localhost
Domain name has ne real affect except where a service account is used, this could cause the instance to fail when starting
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply