December 10, 2015 at 12:36 pm
Hi all,
I am looking for a few pointers with moving and upgrading a 2008 two node cluster to new hardware and upgrading to 2014, which will also include moving all data to a new SAN.
The current cluster has 150+ small sized user databases which will need to be migrated. I am not so concerned with this part, as I intend to backup them up on the existing cluster and copy the backup files to the new cluster and restore them. Then perform any required additional tasks afterwards like correcting orphaned users. However, if anyone can suggest any scripts that will perform the backup and restore on mass would be appreciated.
The part I am not that clear on is how (if possible) I can preserve the backup chain information to the new 2014 cluster as I hold a months worth of point in time backups. I would like to be able to copy all existing backup files to the new cluster and still have the ability to use SSMS GUI to restore a backup if required (which due to the agile nature of the applications that the databases support is a frequent task). Also the reason why I require the ability to use the GUI is for ease of use for other staff members who are not SQL literate that follow restore procedures to perform this task. Will a restore of the 2008 msdb to the 2014 instance work in this way?
If the above is possible I will also need to change the backup file path locations stored against the backup set in the msdb database. The current cluster uses a location that is specific to SQL 2008 (\MSSQL10\backup), and this will need to be changed to the 2014 equivalent for the restores to be able to locate the backup files.
Many thanks for any assistance anyone can provide.
Adam
December 15, 2015 at 5:22 am
Hiya,
I believe the official line is that system databases can not be restored to different versions of SQL Server:
https://msdn.microsoft.com/en-us/library/ms190190.aspx#Anchor_0
That being said, I can't think of a reason why you shouldn't try to copy the backup data over and this article may help you understand the scope of the data you require. Unfortunately, this isn't something I have ever done myself.
December 15, 2015 at 5:45 am
Test, test, test!
I have seen three upgrades this year to SQL 2014, two had severe performance problems afterwards. With the cardinality estimator changing that in itself is enough to warrant pre and post performance tests. Add new hardware in as well and you need to test workload against the new server, compare to the old and make sure that you fix any identified performance regressions before the new server becomes the only server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2015 at 8:55 am
Thanks for the article link Ginga, I will continue to look around as well and update this post if I find anything.
I guess my safest option would be to leave the existing cluster as is until the 1 month backup retention has expired alongside the new cluster with all databases copied across. Then if I do have to perform a restore I can do it on the old cluster and copy the individual database across again.
Adam
December 16, 2015 at 9:02 am
acrutchley (12/10/2015)
Hi all,I am looking for a few pointers with moving and upgrading a 2008 two node cluster to new hardware and upgrading to 2014, which will also include moving all data to a new SAN.
The current cluster has 150+ small sized user databases which will need to be migrated. I am not so concerned with this part, as I intend to backup them up on the existing cluster and copy the backup files to the new cluster and restore them. Then perform any required additional tasks afterwards like correcting orphaned users. However, if anyone can suggest any scripts that will perform the backup and restore on mass would be appreciated.
The part I am not that clear on is how (if possible) I can preserve the backup chain information to the new 2014 cluster as I hold a months worth of point in time backups. I would like to be able to copy all existing backup files to the new cluster and still have the ability to use SSMS GUI to restore a backup if required (which due to the agile nature of the applications that the databases support is a frequent task). Also the reason why I require the ability to use the GUI is for ease of use for other staff members who are not SQL literate that follow restore procedures to perform this task. Will a restore of the 2008 msdb to the 2014 instance work in this way?
If the above is possible I will also need to change the backup file path locations stored against the backup set in the msdb database. The current cluster uses a location that is specific to SQL 2008 (\MSSQL10\backup), and this will need to be changed to the 2014 equivalent for the restores to be able to locate the backup files.
Many thanks for any assistance anyone can provide.
Adam
do not migrate the system databases especially MSDB, there's metadata in this database that is pinned to the instance where it came from.
I've never done this before but set yourself up a test system and enable updates to the system tables, then export the backup set info and reimport into your target system
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 20, 2015 at 4:10 am
How long is your backup retention time? If your backup retention time is expired than it's no longer an issue. So it's only temporary a problem. How long do you need to migrate all the databases? Hours, days, weeks, months?
During this period you could restore an older version of a database on the old cluster and then migrate it again to new cluster. This might take up to four times longer, but should be no big deal for small databases.
You could also query the msdb database on the old cluster for backup file locations and use that to restore the database on the new cluster. Again this might be a bit more work than the solution above but it will be a lot faster. If you're good with SQL queries you can generate the restore statement with your query. Someone who's good at scripting or programming and understands how this works can create something that fully automates this.
If you need to decommission the old cluster before the backup retention time is over, you could export the backup history from the msdb on the old cluster and keep it in a handy place.
If you are adventurous you could try to insert this data inside the msdb database on the new cluster. I won't recommend this, but i could work if know what you're doing.
December 22, 2015 at 5:36 am
I would also suggest heavy testing. The new cardinality estimator could pose big problems as stated earlier. Make sure you either rebuild indexes after you migrate the databases or at minimum run update statistics.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply