February 28, 2017 at 5:05 pm
Hi.
Need Gurus to give me some steps to upgrade about 600 Databases from old Windows Server 2008 32 bit hardware to state of art SQL Server 2014 64 bit.
At the source I will use maintenance plans and take a FULL Backup of ALL DB's after the traffic is shutdown. Will those FULL Backups be good enough or do I need to take a log backup as well?
Then the backup files will be moved to the destination and at that point I will run restore scripts WITH RECOVERY ( since its only full backup) , transfer logins, msdb jobs, rebuild indexes, update stats and update compatibility level? Any other ideas?
Business also wants to get a case when say Primary will be in use and how to do it?
At that point I was thinking I will add a differential backup ( from last full backup ) and use Tlog backups taken every 30 minutes.
Thank you,
March 1, 2017 at 4:06 am
I would probably be inclined to offline the databases on the source and copy the database files to the target and then attach the databases at the target
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 1, 2017 at 6:24 am
What I'd suggest is a slight change in the order you plan to do things. I'm presuming you've got the new hardware already, so what I would do is prior to your database migration, get all the logins, jobs (set them to disabled on the NEW server during the migration), SSIS packages migrated. That shaves some time off and can be done during normal work hours without impacting anyone.
Then, when your migration window arrives, migrate the actual databases, either using Perrys' method of "offline on source->copy DB files->attach to destination" or your method of backup / restore (which is what I use.) But, if you're going to do the backup/restore method, I would suggest adding a step to the process. Once the backup of a DB is complete, set it to offline. It prevents anyone from accidently connecting to it later, makes it a little easier to keep track of which have and have not been done, and gives you a very easy and quick bail out in case there's a problem (just online the DB(s) you need)
And yes, all you should need if all traffic is stopped to the DBs is the full backup, it would be transactionally consistent to the time that the backup completes (and is one of the reasons I set the original DB to offline, then I *know* there won't be changes)
March 8, 2017 at 9:28 pm
I just migrated hundreds of databases to new servers and SQL 2016 SP1. I used log shipping with PowerShell that I created to start the process of migration early and then at the time I am migrating, I did a tail of the log backup to ensure that I got all the transactions and then did a final restore of the log on the shipped copy and then I renamed the old server and renamed the new server to be the old name so that all the applications did not have to be changed.
I would recommend at least doing a tail of the log backup after fulls to ensure that you are not missing data no matter how idle the server is.
I moved all the logins over prior to the move so that I could do things while both servers were up and the down time would be smaller.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply