July 22, 2008 at 4:00 pm
Hello SQL Server 2005 Pros:
I am migrating an SQL Server 2005 database to some new servers, and I have gone through a testing process where I have performed a full backup from the old production server and restored it to the new server. I went thru the excercise of moving all jobs, alerts, operators, linked servers, saved ssis (dts) packages, users, re-set up SQL Server Mail on the new server, and the like. Essentially I successfully carried out the process outlined at http://support.microsoft.com/kb/314546/en-us.
Because of my relative inexperience in SQL server migration, the whole process took me a couple of days. During that time, naturally, the DB on the old production server has been updated. Now I have observed that if I just do another backup and restore it over top of the existing database in my new server, if I understand correctly, I would have to re-set up all my jobs, alerts, operators, linked servers, ssis (dts) packages and the like.
I'm imagining a better scenario where I could somehow grab just the transactions that have occurred since a couple of days ago, and somehow add them to the database on new server which is "frozen in time" from just a few days ago.
I've been reading about things like differential backups, the transaction log, snapshots, replication, or potentially using integration services to move all table structures and data from instance to the other, but I still don't know whether what I'm trying to accomplish is even possible, and if so - is it worth the trouble? - i.e. is this a standard administrative tactic? If not, what is the preferred method?
Or should I just tell my users not to use the db while after 5pm I perform another full backup, overwrite the old database, and reconfigure everything else manually as I have just done after business hours and have it ready for the next morning? (I think it wouldn't take me too long on the second go around)
I think just need a little insight and practical advice from you all, and then hopefully I can carry on.
July 22, 2008 at 8:43 pm
Now that the new server is up and running, all you should have to do is:
1) Shut down the current production application
a) You don't want the users accessing the old system anymore
2) Backup the current databases
3) Restore the databases to the new server
4) Verify the users are synched up on the new server
SSIS/DTS packages are stored in MSDB - so, you shouldn't need to do anything there.
Linked servers are setup at the server level, so again - nothing to setup again.
After the databases have been restored - you can verify the system. Once verified, reset the connection information for the application(s) and test to make sure everything is working. If everything is working - release the system to the users.
This is a general overview and not meant to be detailed, so make sure you have your checklist ready to go for each step. The main point is that you already have done most of the hard work - and that work doesn't need to be done again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 22, 2008 at 9:19 pm
Probably want to pause the current server so no new users come on and get the other ones to disconnect. Or set to single user mode.
Then do the backup.
Once you restore, the only thing you might need to do is sync the logins with users. (sp_change_users_login)
July 23, 2008 at 7:33 am
SSIS/DTS packages are stored in MSDB - so, you shouldn't need to do anything there.
Linked servers are setup at the server level, so again - nothing to setup again.
that's odd, I noticed that when I did the initial test backup and restore, I lost all of those things and had to recreate using scripts - and also the ms support article I referenced above seems to indicate in steps 4 and 5 and under "more information" that these need to be recreated by design - what am i misinterpreting?
July 23, 2008 at 10:25 am
Are you planning to migrated all databases on to new server
If so you can give a shot by following the given below steps( this is what I tried at my place).
You have to come up with a migration plan for this as you are migrating entire stuff to different box.
Before you start migration, put all databaes in readonly take backup.
Before restoring, take the backup of master of old server and restore it(Before Restoring take backup of master database of new server).
Once upon restoration see if sql server comes up or not, if sql server doesn't come up, check the error log and fix it.
Once SQL Server comes up you can restore all user created databases which were backed up on old server.
Bring down SQL Agent on old sql box.
Take msdb database backup from old sql box, restore msdb on new box.
You have to rename you new sql server, so that applications which are accessing will not be affected.
Test applications accessing databases migrated to new server.
When applciations\users are testing, bring down old sql server.
If you are planning just to migrate couple of database\databases to new server.
Then you have to adapt the following process.
Put all the database/databases that are going to migrate into readonly mode.
take backup of databases and then restore it on new server.
Script out all Logins which are users of this database\databases.
Script out users having access with priviliges.
Apply the script.
Script out jobs which runs processes on this migrated databases and apply it on new server.
Modify the applications connection string so that applications connect to newly migrated server(Coordinate with your App and Dev Managers) and finally test it.
Script
July 23, 2008 at 12:24 pm
ok, so my high-level question is this:
murthykalyani has recommended a procedure that should help me migrate involving restoring the master and msdb databases, yet Steve and Jeffrey both seem to indicate that all my other settings should already be included in a full backup (by full backup I refer to rightclicking the database in ssms > tasks > backup (to disk).
provided I didn't overlook anything in my test run (and that's a big IF admittedly) my own experience is that when I do this, the other settings are lost.
the posts seem to contradict each other somewhat, and so I still need to sort things out further here. I'd be grateful if someone could help me clarify - thanks!
July 23, 2008 at 1:14 pm
Let's walk through each one of your concerns:
jobs, alerts, operators, linked servers, saved ssis (dts) packages, users, re-set up SQL Server Mail on the new server
Jobs - stored in MSDB database, will not be lost when you restore a user database
Alerts - are defined at the system level, will not be lost when you restore a user database
Operators - are defined at the system level, will not be lost when you restore a user database
Linked Servers - are defined at the system level
SSIS Packages - can be stored in MSDB or file system
Database Mail - defined at the system level
Users - users are stored in each database and are tied to a system login. The system login will not be lost when you restore a user database, however - the users may not synch up correctly. If you run sp_change_users_login 'Report' in the newly restored database, this will generate a report of users that are orphaned. Lookup this procedure in BOL and it will show you how to fix those orphaned users.
If you used 'sp_revlogin' procedure to build the logins in the first place, then you should not have any issues with orphaned users (unless, new logins/users have been created since you used the procedure). This is because that procedure creates the logins using the same SID which will synch up with the user record in the database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 23, 2008 at 1:18 pm
ok. that's quite clear. thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply