Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JaybeeSQL
Posting Yak Master
112 Posts |
 Posted - 2014-02-05 : 06:59:43
|
| Hi all,Background - we are migrating across both to a different version, and to a virtual server.1) Our databases total 200gb across 28db's ...should we backup across the LAN, or backup locally, zip the .bak files and move them with a utility (We have no image/blob data)?2) Some of our user DB's vary in Recovery Model, either Full or Simple. Is there anything to consider for the migration?3) We want to run the the servers in parallel for a few weeks prior to cutover. Generally, can applications write to/read from two databases instead of just one? Or is there a better way to ensure seamless transitions?4) I foresee a significant delay between initial restore/testing and final cutover (while we upgrade our DTS packages...manually!!) However, currently the DB's are backed up fully on a regular basis - any recommendations on the best way to synch them (bearing in mind Recovery Models above?)5) Is it best to script the maintenance jobs at the original and restore on the target server, or just recreate from scratch?Thanks,JB |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-02-05 : 12:06:35
|
| $.021) The average DB size is pretty small (7GB'ish) so restoring or copying the files across the LAN should not be an issue. Either way works.2) Simple recovery does not have any log restore. When you actually cut over, consider putting the original DB into Read-only mode, performing a backup and restoring at the destination. This would prevent data loss.3) The application can do anything it wants but you'd need to make the application perform the second write.4) There are many options and probably someone will come up with a better one than mine. After you are convinced that the parallel systems and the DTS packages are correct, perform a backup and restore (See #2)5) I'd script the jobs out. Bear in mind that you need to script the Operators, Schedules, etc. as well as the actual jobs.===============================================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961) |
 |
|
|
JaybeeSQL
Posting Yak Master
112 Posts |
Posted - 2014-02-05 : 14:20:55
|
| Hi Busta (yes that is a 'Kool' name, how did you think it up??)1) Great!2) Can do, but that means overwriting the target database...would that also overwrite the newly created SSIS packages?3) Great, will check!4) Waiting... ;5) Sure, I think they're included by default but I'll check. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2014-02-12 : 11:54:55
|
| 1. I suggest Local. I probably would not zip it unless the time required to copy the backup is greater than several hours.2. If you have down time in between there shouldn't be an issue. Just backup, copy restore. But if your down time is critical I think you want to restore it a head of time and then apply your log when you cut-over (this applies to #3 and #4).3. It is unlikely your Applications can write to 2 Databases at once.5. I had trouble when we did this. I believe your MSDB Database has most of that. But you can only restore if the SQL Server Versions have the exact same build, I believe. |
|
|
|
JaybeeSQL
Posting Yak Master
112 Posts |
Posted - 2014-02-12 : 17:29:13
|
| We're not planning to send MSDB across, just script out logins/jobs, restore them at the target, and rebuild the DTS packages from scratch.Ok, does this preserve the continuity of the LSN's and synch up ? ....Friday 8pm Place all DB's in Full recovery, kick off Backups (full);8.01 Disable t/log backup job;10pm Backups complete;10.01 Commence zip/copy of .bak files;10.02 Enable t/log backup job;10.03 Run batch process to copy all subsequent log backups to new server;10.04 Go to the bar, chug down a few beers;Monday 9am Restore scripted logins onto target;10am Restore full backups onto target;3pm Restore scripted jobs onto target;Tu/We/ThRebuild DTS packages in SSISFriday5pm Reset all DB's to Read only;5.01 Restore all t/log backups (dozens, possibly hundreds by this point) at target |
|
|
|
|
|
|
|
|
Migrating from 2K to 2K8, some general questions..