March 30, 2017 at 11:04 pm
Hi All,
i would like to hear from you advice regarding my approach on performing the upgrade/ migration of my existing mssql 2008r2
current spec:
- OS = 2008r2 x64 (VMware)
- MSSQL = 2008r2 x64
- integration service
- reporting service
- analytics service
- engine service
to be upgrade/ migrated to
- OS = 2008r2 x64 (VMware)
- MSSQL = 2012 x64
- integration service
- reporting service
- analytics service
- engine service
my approached will be to:
1. clone the VM DB server
2. perform in-place upgrade of the DB instance (via the mssql 2012 setup.exe wizard)
3. apps to test the upgraded db with all its components ( reporting, integration, analytics )
4. perform data cut over for the user database, reporting databases)
- will do it by backup and restore method ( from the current db to the upgraded db)
I got my concern on whether point 4 works for the reporting databases ( the reporting database and the reporting temp db). Is there anything i need to be aware when doing the migration of the reporting databases during the actual data-cut over day ? How about the analytics services and the integration services?
April 4, 2017 at 11:02 am
flaskvacuum - Thursday, March 30, 2017 11:04 PMHi All,
i would like to hear from you advice regarding my approach on performing the upgrade/ migration of my existing mssql 2008r2current spec:
- OS = 2008r2 x64 (VMware)
- MSSQL = 2008r2 x64
- integration service
- reporting service
- analytics service
- engine serviceto be upgrade/ migrated to
- OS = 2008r2 x64 (VMware)
- MSSQL = 2012 x64
- integration service
- reporting service
- analytics service
- engine servicemy approached will be to:
1. clone the VM DB server
2. perform in-place upgrade of the DB instance (via the mssql 2012 setup.exe wizard)
3. apps to test the upgraded db with all its components ( reporting, integration, analytics )
4. perform data cut over for the user database, reporting databases)
- will do it by backup and restore method ( from the current db to the upgraded db)I got my concern on whether point 4 works for the reporting databases ( the reporting database and the reporting temp db). Is there anything i need to be aware when doing the migration of the reporting databases during the actual data-cut over day ? How about the analytics services and the integration services?
Ask yourself this question: If you do an in-place upgrade to a clone of the original VM, then what database compatibility level will all your databases be at that point? Then, stop and think about what a restored database that comes from the old VM is going to be... SQL 2008 R2, and NOT SQL 2012.
I'd much rather see a clone of the original VM have SQL 2008 deinstalled and SQL 2012 installed fresh. Then migrating the databases using backup and restore starts you out at the lower 2008 compat-level, and you get to choose when that moves up to 2012.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 4, 2017 at 12:14 pm
Why stop at SQL Server 2012? Very soon, 2012 will be three generations behind. There are so many improvements from a stability and performance standpoint to SQL 2016, I don't see the logic in short-stopping yourself to a soon to be out of support product.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2017 at 12:18 pm
Another thing to watch for is SSRS doesn't like changes in server names. If you are running enterprise edition, it should work fine I think, but if it is standard then you will get complaints about clustering and you'll need to fiddle with the tables.
Also make sure to backup your SSRS encryption keys (from the GUI) from the original one as you'll likely have to restore those on the migrated one due to the machine name changing.
If you do the in-place upgrade for SSRS, you won't have to rebuild all of your SQL jobs. If you do a fresh install, the jobs will need to be re-created.
If you have the disk space for it, I'd spin up a "TEST UPGRADE" server where you can practice the upgrade and document any issues you ran into. After that, work on the live one.
And as a last thought, are you sure you want 2012 to be your end version? SQL Server 2012 hits end of mainstream support this year. And (in my opinion), SSRS 2016 is a lot nicer than SSRS 2012. If you are planning on using SSIS catalogs, then SSIS 2016 is also nicer than 2012. Although upgrading to 2016 would mean upgrading your OS as well.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 4, 2017 at 6:05 pm
For SSRS as already mentioned, definitely make sure you have a backup and move your encryption keys. You can restore SSRS databases pretty easily. Whenever the service starts up, it always checks if the jobs exist for the subscriptions. If they don't exist, it automatically recreates the jobs. So those would be fine.
Sue
April 4, 2017 at 10:15 pm
sgmunson - Tuesday, April 4, 2017 11:02 AMflaskvacuum - Thursday, March 30, 2017 11:04 PMHi All,
i would like to hear from you advice regarding my approach on performing the upgrade/ migration of my existing mssql 2008r2current spec:
- OS = 2008r2 x64 (VMware)
- MSSQL = 2008r2 x64
- integration service
- reporting service
- analytics service
- engine serviceto be upgrade/ migrated to
- OS = 2008r2 x64 (VMware)
- MSSQL = 2012 x64
- integration service
- reporting service
- analytics service
- engine servicemy approached will be to:
1. clone the VM DB server
2. perform in-place upgrade of the DB instance (via the mssql 2012 setup.exe wizard)
3. apps to test the upgraded db with all its components ( reporting, integration, analytics )
4. perform data cut over for the user database, reporting databases)
- will do it by backup and restore method ( from the current db to the upgraded db)I got my concern on whether point 4 works for the reporting databases ( the reporting database and the reporting temp db). Is there anything i need to be aware when doing the migration of the reporting databases during the actual data-cut over day ? How about the analytics services and the integration services?
Ask yourself this question: If you do an in-place upgrade to a clone of the original VM, then what database compatibility level will all your databases be at that point? Then, stop and think about what a restored database that comes from the old VM is going to be... SQL 2008 R2, and NOT SQL 2012.
>> doing an in - place upgrade will upgrade all my databases to 2012. This will allow time for my apps to perform testing on the upgrade version. And there will also be a pre-initial data cut over to test the cut over data of the ssrs and others user databases . Yes, i am aware they will be an 2008 version and to change the compatibilities later once restore over to the upgrade version is not that difficult. Do you see my point here?I'd much rather see a clone of the original VM have SQL 2008 deinstalled and SQL 2012 installed fresh. Then migrating the databases using backup and restore starts you out at the lower 2008 compat-level, and you get to choose when that moves up to 2012.
>> all my stuffs will be upgraded to 2012.
April 4, 2017 at 10:16 pm
SQLRNNR - Tuesday, April 4, 2017 12:14 PMWhy stop at SQL Server 2012? Very soon, 2012 will be three generations behind. There are so many improvements from a stability and performance standpoint to SQL 2016, I don't see the logic in short-stopping yourself to a soon to be out of support product.
>> this is coming from the management. I am aware of your concern too.
April 4, 2017 at 10:21 pm
bmg002 - Tuesday, April 4, 2017 12:18 PMAnother thing to watch for is SSRS doesn't like changes in server names. If you are running enterprise edition, it should work fine I think, but if it is standard then you will get complaints about clustering and you'll need to fiddle with the tables.Also make sure to backup your SSRS encryption keys (from the GUI) from the original one as you'll likely have to restore those on the migrated one due to the machine name changing.
If you do the in-place upgrade for SSRS, you won't have to rebuild all of your SQL jobs. If you do a fresh install, the jobs will need to be re-created.
If you have the disk space for it, I'd spin up a "TEST UPGRADE" server where you can practice the upgrade and document any issues you ran into. After that, work on the live one.
>> my test Upgrade is as mention, i will clone the current db server and will perform in-place upgrade on the clone vm db server. Anything goes wrong i still can clone again from the current vm db server and then try to upgrade it until all is done. Then pass it over to the apps team to do testing and verification. Then schedule a data cut over day to port over the databases to the tested clone upgrade db server.
My worries is on porting over the reportServers database (native mode). What else i need to look out for beside i need to backup the encryptions key.And as a last thought, are you sure you want 2012 to be your end version? SQL Server 2012 hits end of mainstream support this year. And (in my opinion), SSRS 2016 is a lot nicer than SSRS 2012. If you are planning on using SSIS catalogs, then SSIS 2016 is also nicer than 2012. Although upgrading to 2016 would mean upgrading your OS as well.
April 4, 2017 at 11:59 pm
flaskvacuum - Tuesday, April 4, 2017 10:16 PMSQLRNNR - Tuesday, April 4, 2017 12:14 PMWhy stop at SQL Server 2012? Very soon, 2012 will be three generations behind. There are so many improvements from a stability and performance standpoint to SQL 2016, I don't see the logic in short-stopping yourself to a soon to be out of support product.>> this is coming from the management. I am aware of your concern too.
Many sympathies.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply