August 7, 2005 at 2:56 pm
We have a number of apps that use SQL Server databases. We are migrating these apps to a new machine. I'm pretty much a SQL Server newbie in terms of this type of thing. (I've installed and done basic configuration and even created some DBs and done my owne queries.) Unfortunately, I can't migrate these apps all at the same time and move their DBs to the machine at the same time so I'm going to have to migrate them gradually, one-by-one. I'd like some expert advice on this. I first want to get existing copies of the DBs over to the new server and running. I then was thinking I'd used replication on the original DBs to keep the new in sync with the old, then reverse the replication/synchronization as each app is migrated so the old can become a hot spare. I'd like to know if this strategy sounds good and, if so, what would be the best way to get copies of the databases over to the new machine. If this isn't a good strategy, I'd like suggestions as to how it can be done.
Yes, we have some backups, but right now they are not dependable (unfortunately).
August 8, 2005 at 5:11 am
Hi Richard,
The best way for you to do this is to utilise your backups and restore each DB on the new server, I know this means a little extra work because you need to restore transaction logs and the like but I definately wouldn't be considering replication to do it.
I'm more than a little worried that you say your backups are not dependable as that's one of the main things you need to be on top of, a DBA is nothing without a valid backup to recover from, and less than nothing if they have backups but never test restoring them.
You don't want to put yourself in a position of loosing everything because you dont have one and/or haven't made sure you can do a restore when needed.
Sorry to get on my soapbox about this but it's one of the main things you need to sort ASAP.
August 8, 2005 at 6:39 am
I understand about the backups. I'm very worried about it as well.
First, I'm not a DBA, but simply a sysadmin who has been tasked with this. Second, I'm new to the company and just becoming familiar with what we do and do not have, etc. Third, the backups for this machine are "problemmatic" in that the system has apparently been unstable for some time with various backup problems that cause the backups to fail. Case in point: even though installed, at backup time Backup Exec seemed to think the OFO wasn't there. So, when I uninstalled this and the remote agent (so I could reinstall them) and went to reboot afterward, I came up with an "inacessible_boot_drive" BSOD. (Fortunately a repair got the system back online.)
So, this is the existing situation I'm facing here.
Many thanks, though!
August 8, 2005 at 6:43 am
It sounds like you're in a bind with that server, for the migration to the new one i'd leave backup exec alone and use SQL backups to disks to do the job, that way you're not introducing any 3rd party problems into the mix.
If it's as bad as you say then do your backup to disk and get it transferred over to the new Server asap.
August 8, 2005 at 7:15 am
That's what I'm going to do!
After accomplishing that, should I then set up replication for the needed databases until the transition is complete? Unfortunately, at least 2 apps aren't going to be migrated for a couple of weeks due to issues I'd rather not discuss.
Again, many thanks for the help!
August 8, 2005 at 7:25 am
If it were me i'd start by doing the immediate full backup to disk, then supliment this with 4 hourly transaction log backups (for peace of mind) and nightly differential backups.
When you want to migrate you restore the latest differential and then any transaction log backups that were taken after that point (use the no-recovery option on all of the restores).
Finally, switch off the app, make sure there's no-one using the DB and take the last transaction log backup, restore that with recovery and you should be able to point the app at the new DB (good luck). Take the old DB offline when you're done, that way you'll know if the move has worked as it wont be able to connect to the old DB if it's offline.
Hope this helps, if you need anything else feel free to ask.
August 8, 2005 at 8:20 am
Actually, maybe you can help me with another issue. I want to direct all logs to another drive reserved just for those logs (and other sequential access type files). I just tried to do that for tempdb in the new installation and couldn't. How do I do this?
August 8, 2005 at 10:18 am
To quickly setup backups, use the Maint Plan wizard to generate regular DB and Log backups. I think the Maint Plan won't let you specify a drive on another server, but you can modify the backup command after the fact to point to another machine. You don't need Tempdb to go to another server, just the logs from your production databases.
Here's the modified command from my trans log backup. I point to my other server SQLTEST-01 .
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 778B38E9-B9CE-4B18-828E-941DDB699033 -Rpt "E:\Log\Job_Logs\AdvDbPrd Daily6.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "\\SQLTEST-01\LogBackups\AdvDbPrd" -DelBkUps 3DAYS -BkExt "TRN"'
August 8, 2005 at 2:24 pm
Trying to restore master by forcing into single mode with sqlservr -c -m at the command line I'm getting some kind of initerrlog error message that there is a OS and that errorlog is in use. What did I do wrong?
August 8, 2005 at 10:28 pm
Not quite clear ... Your trying to restore master from where to where ?
You probably don't neet to restore Master to your new machine. If it's a new installation of SQL, it will already have Master, MSDB & MODEL. Restore your production (and others as needed) to your "new" server from backups done on your "old" server . You may want to restore msdb if you have DTS packages on your current system.
To paraphrase someone elses tag line ..... "Of course, I may be wrong"
August 9, 2005 at 1:55 am
I agree with not restoring the master database, the new install will have one and unless you're on the same service pack and have the files installed in the same locations you will probably encounter problems.
I know that this means you end up recreating logins and databases (to restore into) but it's probably better doing it this way than trying to troubleshoot what's gone wrong after a failed restore.
August 9, 2005 at 7:04 am
I can't thank you enough for your help!!!! It has been extremely valuable! I've been able to move production databases with ease using the import/export model. (I found that easiest for me to use.) Between this site (and your help) and MS KB 314546, I've pretty much gotten things under control.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply