April 8, 2005 at 2:31 pm
I am porting a production SQL Server box to a bigger and better machine. I want to see what are the precuations/backups I have to take. I don't want to miss anything. Please point me to some good articles and also post your ideas.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
April 8, 2005 at 3:20 pm
There are several variables that have to be taken into consideration here. Are you retiring the old server (meaning specifically the SQL Server instance(s) on it) completely or will it remain in service. If you are retiring it do you want to use the same instance name for the new SQL Server instance? Doing so means that you won't have to reconfigure any of your existing applications, but it also means that you have to move everything in one shot.
If you are going to have both servers on-line simultaneously then the move is mostly a matter of moving databases from one to the other and "pointing" the various applications at the new instance. We used this method when we upgraded a server that had more than 40 databases on it. This allowed us to break the move up over several days, but several application support folks got their noses out of joint because they were forced to reconfigure their applications and in some cases it isn't that easy...
The process went something like this:
1. Restore the database(s) to the new server from the most recent full backup using the WITH NORECOVERY option. Script and move any relavent logins to new server.
2. Restore transaction logs to the target server as they are available.
3. At the appointed time, bring the database(s) into single user mode to prevent further modifications by users.
4. Make one last transaction log dump of databases
5. Restore the last logs to target server WITH RECOVERY.
6. Reconfigure applications
7. Test.
Obviously, if you have small databases you can skip the whole restore tran log thing and just do a full backup and restore. I also restored msdb to preserve backup and job histories.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 8, 2005 at 3:36 pm
If you move the databases to the new server, rather than the complete "image", you will need to move the SQL loginids complete with their passwords and sids, or you will need to fix the "orphaned logins".
See sp_addlogin and sp_change_users_login in Books online.
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply