October 15, 2019 at 3:45 pm
This question is more for advice on next time. However, here was the scenario:
I was tasked with upgrading SQL Server 2016 to 2017 Developer edition. However, we had Standard edition installed, so the upgrade path wasn’t supported. I installed SQL Server 2017 Developer as a named instance. Then I needed to migrate databases, some of which were 12-20GB. My boss didn’t want to change connection strings so told me to change the new instance to the default port 1433, so I did (changed port of 2016 instance as well). All was ok until we were no longer able to access the 2016 instance, even after adding the port number in SSMS connection dialog box.
So, I ended up bringing all users over after switching ports back and then shutdown the 2016 instance. That left me to bring the databases over. Well to my surprise mdf, ndf and ldf files were in multiple locations. Most were in the MSSQL11 DATA and LOG folders while others were in the MSSQL13 folders. I made the decision to move everything to MSSQL14 DATA and LOG folders. This added addition time to hunt down the files bring over and attach databases. We need to uninstall 2012 and 2016 versions and I felt that leaving the files where they were wasn’t a good idea not to mention in organized. Well, I got some flack from Management because it took all day to do. Keep in mind IOPS sucked, I was only getting 3-5MB during transfer, even moving from one folder to the next on the same drive.
My questions are: 1. Was I wrong to go about migrating the databases the way I did with a detach and attach method? 2. What could I do next time to speed the process up?
October 15, 2019 at 5:43 pm
I assume this was a dev server of some sort?
Moving the files is good for organization, but it doesn't really matter here. I would likely have left them alone and just added security for the 2017 instance to access files in those locations. The xfer to move the files is going to take time, no good way to make this faster, but what you could do for the future is not use defaults and set a /sqldata and /sqllog folder. That way you can keep mdf flies there for all versions, anticipating a 2019 upgrade.
No idea why you couldn't access the 2016 instance. That's separate, however. I would work out why you can't, because changing ports is important, and this can certainly speed up a switch from one version to the next if you want to redirect clients.
October 15, 2019 at 6:03 pm
Is this a development environment? If it's not, you are likely out of compliance with MS licensing.
Two instances of SQL cannot share the same data and log files. If that's what you expected, then it was not going to work.
If it was me, I probably would have:
Since this would have been a fresh install, you would have needed to generate scripts to re-create the logins, jobs, and so forth to complete the process.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply