May 16, 2011 at 9:34 am
I have a 64-bit SQL Server 2008 R2 instance running in a virtual environment that the business has outgrown, and we have purchased physical hardware to migrate to. Originally, I had planned to do a straightforward detach-reattach style migration. However, the business has introduced a requirement where the physical server must have the same name and IP address as the virtual server.
The databases live on a SAN volume that has been mapped with drive letter E:. After discussing a number of options, the path that seems to us to hold the least amount of risk would be to detach all the databases, including the system databases, detach the SAN volume from the virtual machine, attach the SAN volume to the physical server, then attach the system databases and the user databases.
The questions I have are:
What are the potential pitfalls of doing it this way?
Would it be more advantageous to do this using a backup/restore method instead?
Is there another, better way of doing this that I'm not thinking of?
I'm just beginning my planning, and I've never done a migration like this before, so any advice would be helpful.
May 16, 2011 at 10:36 am
It is a lot faster to detach/attach then it is to backup the Database.
I would still have cold backups before I do the move.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 17, 2011 at 12:28 am
1-Take Full backup and then restore on the physical server with no recovery(no downtime)
2-Down your application (Down time started)
3-Take differential backup (differential backup time depends on the change in database)
4-Restore it on the Phsical Server with recovery
5-Route the Application and start it
whats the size of the database ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 17, 2011 at 3:38 am
you can't just detach and attach system databases - you'll need to find the relevent kb article.
The quickest way to move mdfs or backups when you have a san is to move the luns from one server to another. I used to do this some years ago, for reasons which are out of scope here I used to have to backup and restore just over a TB of databases from one server to another - we did this by backing up to a san volume attached to the source server and then attaching the volume to the target server to restore ( the move a lun takes just seconds ) - you can do the same thing with the mdf and ldf files of course then you only copy them locally - or your old lun becomes your new lun - or whatever - just try to avoid copying across the network.
Generally once databases get large the detach attach is far too slow compared to compressed backups or ordinary backups come to that - you'd never really want to copy a 10TB mdf across a network unless you had no other options. If your database is tiny, maybe 100GB then it doesn't make any difference one way or another.
I wouldn't migrate system dbs with a new build btw. Just the user dbs.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 17, 2011 at 3:42 am
sorry didn't fully answer your question - there are no real pitfalls to moving san volumes about - BUT - I'd make a backup somewhere else first just in case. I used this technique on a regular basis for nearly two years without issue.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 18, 2011 at 6:45 am
My approach would be to do a standard side by side build and test of the new server.
When it comes time to put the new server live, go into DNS and delete the Cname record pointing to the old server. Then create an alias record for the new server so that the old server name and IP address map to the new server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 18, 2011 at 6:59 am
Thanks to all for the helpful advice. I've learned a lot from planning this out.
I think the approach we're going to take is to do a backup and restore of the system databases, followed by a detach and reattach of the user databases. Fortunately, I have the luxury of being able to test the restores of the system DBs because the physical box isn't yet in production, and I can restore and then examine the instance to see what I've got when I do this.
I think I'm going to try and accomplish this without mucking about with the SAN volumes. The databases are relatively small, so copying them to a nice, new, clean SAN volume is preferable to me to reattaching a SAN volume with binaries and other flotsam and jetsam on it.
May 18, 2011 at 3:33 pm
I'm a SQL Server DBA and Compellent SAN administrator. For what's it's worth:
I appreciate your caution about this migration.
I don't know how your SAN is configured, nor its administrative interface, but I can tell you that using Compellent SAN management software it is super easy to release a mapping for a LUN from one server and to then map that LUN to another server. Doing more than that is just busy work. Think of it as attach and detach for LUNs, much like attach and detach for databases in SQL Server Management Studio.
If your SAN interface can be utilized in a similar fashion, it would be the quickest, easiest, and a very safe path for migration of your application databases. It will also prevent duplicated use of potentially precious space on your SAN (you had said you intended to "copy" the databases to new SAN volumes).
LC
May 18, 2011 at 4:11 pm
Depends on downtime. Why do you want to relocate the system databases?
If master comes up from a restore and your server machine name is not the same as where it came from, you may have some trouble...
For relocating logins, dbo.sp_help_revlogin, dbo.sp_hexadecimal stored procedures from Microsoft can allow you script out the logins and transfer them with passwords and SIDs intact. Before you apply the login scripts, create empty databases with the same name as the databases to be relocated, this will preserve the default database setting for the transferred logins (assuming 2005 or higher). Replication, Jobs and linked servers can all be scripted out and transferred. If you change the recovery models for your user databases to Full (if they are not using that recovery model already), take a full backup and restore with nerecovery to the new server, then apply the transaction log backups with norecovery, when it comes to the cutover time, take the last transaction log backups and apply with recovery. This will give you downtime for the databases measured in seconds, and no stuffing around with system databases.
Once your user databases are recovered, shutdown the virtual server. Then change the new SQL Server's name, first in SQL Server. Use sp_dropserver 'YourTempServerName';, then run
sp_addserver 'YourServerNameYouWant', local;
SELECT @@servername to verify the change. Now change the windows server name and IP address. Reboot and you should be all good.
Ideally you could set up DNS aliases for your applications to use instead of the server name or heaven forbid IP address. When it comes to cutover, set the Time to Live of the aliases to 1 minute, let that propagate and at cutover change the aliases to point to the new server. Set the Time to Live for the DNS aliases back to the corporate standard once you have verified all is well. If it all goes wrong you have the virtual server as your rollback plan.
May 19, 2011 at 11:13 am
I want to say thanks again to everyone for all of your suggestions. There are many more ways of getting from point A to point B than I even realized, and I've definitely learned a few things I didn't know from reading your input.
Jason's post caused me to rethink my approach entirely. The server I'm migrating is actually extremely modest in terms of the number of server-level objects/jobs/etc. it hosts. I was working from the assumption that the best way to migrate would be to restore the system DBs, but it may actually reduce the risk of the project hitting a snag on go-live day if we just script across what we do have after a thorough inventory of the environment in its current state. The fact that we are planning to rebuild SSAS and SSIS assets from scratch anyway probably simplifies this greatly as well.
Regarding logins, we currently only have a small number of logins based on AD groups and accounts. Only the sa account is a SQL Server login. Is it OK to just create those manually, or am I going to miss critical information that lives in the system DBs on the source server if I do it that way?
May 19, 2011 at 1:47 pm
Hi devin,
I wouldn't script out sa, it's the only account I wouldn't script out, only because I've never tried it. Regarding the AD accounts, I would script them out and transfer that way, faster than manually doing it, but do create empty user databases of the same name as on your prod server so that the Default database setting is preserved. The part about scripting out the SIDs and passwords only applies to SQL Server logins, the Microsoft procs script out the logins appropriately, but be aware that they will not script out server role membership. If sa is your only SQL Server based login, it could be an opportunity to change authentication to Windows Only, that's if that suits your environment.
Don't forget to setup the memory settings, default data/log locations and any other non default server level settings on the new server. You could do a dry run before the cut over day, without changing the server name to the production name to see how it all goes. It's always good to test an implementation plan before the big day. Having the virtual server to roll back to takes the pressure off. We've just gone through a SAN replacement/hardware upgrade and where possible the approach I outlined is the way we did it. The added bonus of documenting your environment is all good. Good luck and I hope it all goes well.
Jason
May 19, 2011 at 4:35 pm
EdVassie (5/18/2011)
My approach would be to do a standard side by side build and test of the new server.When it comes time to put the new server live, go into DNS and delete the Cname record pointing to the old server. Then create an alias record for the new server so that the old server name and IP address map to the new server.
this would be my route but i wouldnt mess with DNS. Create the new server with new name, get it set up and operational then at last minute when switching the servers, change IP, rename the physical box and run sp_dropserver then sp_addserver. Couple of reboots required but should be no great shakes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 24, 2014 at 7:09 am
HI,
I have to migrate 15 MSSql server 2008 R2 database residing on VM to Physical machine of same version of SQL Server..
I have prepared a procedre..
1. Backup of both system and User defined databases.
2. Backup of logins by executing a script.
3. restore/attach to physical machine, the one detached database(system+user) from VM.
4. Execute the login query(Script) on physical VM
If I detach and attach triggers and procedures will all exist.
Can you please explain me if any steps are missing..
Thanks
Praneeth
January 24, 2014 at 7:46 am
praneethydba (1/24/2014)
HI,I have to migrate 15 MSSql server 2008 R2 database residing on VM to Physical machine of same version of SQL Server..
I have prepared a procedre..
1. Backup of both system and User defined databases.
2. Backup of logins by executing a script.
3. restore/attach to physical machine, the one detached database(system+user) from VM.
4. Execute the login query(Script) on physical VM
If I detach and attach triggers and procedures will all exist.
Can you please explain me if any steps are missing..
Thanks
Praneeth
Create a new thread and put full detail in there and it will be answered for you
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply