I am running SQL Server 2008 on a Windows 7 machine. I just use the databases locally; don't have external users or other programs connecting. I need to upgrade to Windows 10 before year end because Microsoft are stopping Windows 7 security updates from January.
I've been reluctant to do so because of the work involved, other time commitments. I haven't previously upgraded a Windows box running T-SQL databases and SSMS before.
I understand that my C: will get wiped during the upgrade. I had a few questions, would be grateful to hear from anyone who has done this previously:
Maybe it's all going to be easy and will just work, but if it isn't I'm hoping to identify any time sink landmines that I can avoid in advance of stepping on them during the upgrade process.
Step 1 is going to be to upgrade your SQL Server unfortunately.
SQL Server 2008 and 2008 R2 are not supported on Windows 10 or Windows Server 2016.
IF the SQL instance is being used for testing and/or development, you could migrate it over to a SQL Server 2017 Developer edition and migrate your databases onto that? If this is NOT for testing/development and the databases are under 10 GB in size, you could use Express. Either way, you will need to upgrade the SQL instance before or after you do the OS upgrade; and in my opinion, this will be a LOT easier to do before the OS upgrade... except SQL Server 2017 doesn't support Windows 7.
That being said, when I upgraded from 7 to 10 on my home machine, I didn't have any drives wiped; everything I had installed stayed installed.
So my steps for this "upgrade" would be:
PRELIMINARY STEP - determine which version of SQL Server you NEED. If you can get by with Express, download a copy of Express. If it is for testing/development only, grab a copy of Developer. If it is for production use, buy a license for 2017 at the level you require (standard/enterprise).
1 - do a full backup of ALL SQL databases (user and system) and store them in a good location
2 - run DBCC CHECKDB on ALL of the SQL databases (user and system) to make sure you have no corruption
3 - IF you have any errors or corruption, correct that and repeat steps 1 and 2 until you have no corruption or errors
4 - stop the SQL instance and set the SQL instance startup to be manual (this is just in case the drives don't get wiped)
5 - make sure your most recent backup is NOT on the C drive and, if possible, unplug that drive to prevent accidental deletion with windows upgrade/install
6 - Upgrade to Windows 10 and plug the drive with the backups back in (if you unplugged it)
7 - Install SQL 2017
8 - restore the backup of all system databases, with NEW names and NEW locations, which should result in them being upgraded. These will be moved later.
9 - Once ALL of the system databases are restored, restore the user databases overwriting the user databases you had on disk before.
10 - sop the SQL instance
11 - back up the current system databases to a safe location (you should not need them, but I like to have a roll back point to go to just in case)
12 - copy the system databases with the new names to the location of the current system databases and rename them to match what the system databases SHOULD be
13 - start the SQL instance and cross your fingers
If all went well, it should start up and automatically Mount the user databases. If things went sideways, begin troubleshooting. If the user databases didn't Mount, manually Mount them. If it fails to start, delete the current system databases and put the default ones back.
In the event you don't have many custom objects in the system databases (hopefully this is the case), it may be better and safer to just script out re-creating all user objects from the system databases and run that script after installing SQL 2017 and the restore should just work. You will need to map the database users to logins if you don't restore your system databases, but that is a simple alter user command.
Also, if everything comes up nicely, you will likely want to update statistics and possibly rebuild indexes and checkdb again as well as have a backup on 2017.
As for a checklist, this is mine:
1 - check if what I am doing is possible (ie SQL 2008 on Windows 10 is not supported. To me, not supported means not possible)
2 - create valid backups of PC/VM and SQL databases
3 - CHECKDB before and after
4 - upgrade
5 - update statistics with SQL upgrade
6 - new backups once everything completed successfully
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.
October 25, 2019 at 3:38 pm
I need to upgrade to Windows 10 before year end because Microsoft are stopping Windows 7 security updates from January
You don't once mention upgrading your version of SQL Server though. 2008 is already outside of support.
As far as I recall (I haven't checked) SQL Server 2008 does not run on Windows 10, so it seems like it's time to upgrade both.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 28, 2019 at 11:40 am
In the event you don't have many custom objects in the system databases (hopefully this is the case), it may be better and safer to just script out re-creating all user objects from the system databases and run that script after installing SQL 2017 and the restore should just work. You will need to map the database users to logins if you don't restore your system databases, but that is a simple alter user command.
bmg002 thanks again for your very helpful step by step process. I do not believe that I have added anything custom to the system databases. If that is the case, if I understand this quote correctly you're saying I could skip the system database backup steps and just modify the user database access after following the other steps to switch to SQL server 2017?
So if I understand correctly I will have to install SQL Server 2017 after upgrading to Win 10 (since it's not supported in Win 7). Given there's nothing custom in the system databases, I can avoid migrating those and post OS migration, in SQL Server 2017 I would essentially just restore the SQL Server 2008 user databases which I had backed up prior to the OS switch and then configure the logins to reinstate user access, right?
October 28, 2019 at 3:13 pm
Things to check that you may not realize are stored in the "system" databases:
SELECT 'EXEC sys.sp_addsrvrolemember '''+pr.name+''', '''+pm.name+''''
FROM sys.server_role_members AS rm
INNER JOIN sys.server_principals AS pm ON rm.role_principal_id=pm.principal_id
INNER JOIN sys.server_principals AS pr ON rm.member_principal_id=pr.principal_id
WHERE pr.name <> 'sa'
AND pr.name NOT LIKE 'NT SERVICE%'
AND pr.name NOT LIKE 'NT AUTHORITY%'
?
SELECT 'ALTER ROLE [' + r.name + '] ADD MEMBER [' + m.name + '];' AS sqlcmd
FROM msdb.sys.database_role_members rm
INNER JOIN msdb.sys.database_principals r ON rm.role_principal_id = r.principal_id
INNER JOIN msdb.sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE r.name IN ('DatabaseMailUserRole','SQLAgentUserRole','SQLAgentReaderRole','SQLAgentOperatorRole')
AND m.name NOT IN ('dc_operator','MS_DataCollectorInternalUser','PolicyAdministratorRole','SQLAgentReaderRole','SQLAgentOperatorRole')
https://www.mssqltips.com/sqlservertip/5805/script-out-sql-server-credentials-and-proxies/
https://thomaslarock.com/2014/08/sql-server-configuration-check/
https://gallery.technet.microsoft.com/scriptcenter/Script-to-Scipt-out-14a19eda
hope this helps
October 28, 2019 at 4:38 pm
I almost forgot, check if you have any SQL Agent jobs on the instance. You can script those from the Object Explorer Detail window in SSMS.
October 29, 2019 at 10:55 am
<li style="list-style-type: none;">
- script out credentials for the instance:
https://www.mssqltips.com/sqlservertip/5805/script-out-sql-server-credentials-and-proxies/
<li style="list-style-type: none;">
When I execute the script to get the proxies from sp_help_proxy in SQL Server 2008, SSMS returns this message:
Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'sp_help_proxy'.
I suspect this is because that stored procedure was not available in SQL Server 2008. If that is the case, is there another way of achieving the same end goal?
October 29, 2019 at 4:24 pm
Sorry I missed that point, you can find them and script them out in the Object Explorer Detail view in SSMS. I believe the info is in the msdb.dbo tables:
sysproxies, sysproxylogin, sysproxysubsystem
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply