August 18, 2015 at 10:54 am
Hello all,
I have a "lateral" migration to do. By lateral I mean only the box is being replaced, but both my OS and SQL versions remain the same (2008R2, both OS and SQL). The server holds SQL, Reporting Services, Integration Services and Analysis Services in place. The final server name is to be kept the same as my current server, same as with folder structure.
The migration plan I intend to follow is simply bringing SQL to the same patch level as the current server and attaching all databases to the new server, this should take care of all data, jobs and logins on the SQL side and all of my reporting services data. My only concern at this point is the replacement of the master db (pending reading on this).
For reporting services I'd expect only had left dealing with the encryption key (if any).
For Integration Services I'd be pretty much done as all of them are within MSDB.
For Analysis Services I'd simply attach the cubes files, similar to what I intend to do on the databases.
Overall it appears to be an "easy" process but I want to ask you guys if you advice taking care of things I might be overlooking at this point. I'm doing my RTFM process over the web now, but most I find is about major version migrations.
Any help is appreciated.
Thanks
August 18, 2015 at 1:39 pm
I'm reminded of a scenario I used to work on with restoring a server on "dissimilar hardware" as part of a DR exercise. We had to prove that we could restore the entire box on hardware that is NOT identical to the original. The only real difficulty in doing so is getting the necessary drivers installed, but it did work. If you have the test box handy and a test network handy, you can install enough OS to allow you do the restore of the C: drive only (OS), and then fix the drivers, and then restore any other drive letters after the OS is operational. It's a long process, but then, so is pretty much ANY lateral migration. Even if you do a fresh install of the new hardware, you could still just restore all the databases, I would think... but I'm no database backup expert, so someone else will have to chime in on that topic.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 18, 2015 at 3:06 pm
You could either restore the master db --or-- bring down SQL, copy the original master files over the new master files, and start SQL normally. You'll see a lot of errors for "missing" user dbs, but you can ignore those until you get those dbs attached.
Alter the file paths for model and msdb dbs to what they need to be on the new server. Then, you can either restore them or copy over them as above.
For the user dbs, attach them as you planned to.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 27, 2015 at 10:06 am
As for the future guys reading this thread... The process was fairly smoothly, as originally expected.
Preparation in advance was to taking servers to the same SQL patch level and installing any additional OLEDB drivers and pre-requisites. Also remember to backup the encryption key for reporting services.
After that, all you need to do is stopping SQL services, rename servers, move system and user DBs to the very same path structure of the old server and restart services. At that point everything should be running exactly as expected. Just a few extras:
Analysis Services. I went the backup/restore path and manually added the cubes.
Reporting Services. This caught me a little off guard as a Kerberos SPN error was logged on production that wasn't happening on my exercises on the test server; I had to reconnect the service to the ReportServer DB trough the RS configuring tool. After that was just matter of restore the encryption key so all passwords and such are back working on the new place.
SSIS. Nothing to add, the SQL part took care of it (my packages are stored on MSDB), yours might be on the filesystem so you'd have to take care of that.
Have fun!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply