SQL Help with upgrade

[JOrdan01070]

I'm hoping somebody can help me with SQL upgrade for Facility. I need to upgrade the SQL server to a newer version when I try and do an in place upgrade it does not allow me to select the MSSQLSERVER instance, so I have backed up the Facility database as a 'copy only' can I just install the newer version of SQL server and restore the back up and run old and new instances of the database at the same time.

 

I have never installed or configured SQL So its a bit daunting.

Edited December 14, 2023 by JOrdan01070

[Bedders]

Assuming you have another Windows Server license, and a license for the latest version of Microsoft SQL Server, I would:

Set up a new Windows Server.

Install the latest MSSQL server on it.

Backup the database on the old server.

Copy it over, and Restore it on the new server.

Ensure any Users, Jobs etc. have gone over successfully.

Make sure nothing in the database has filepaths to files saved on the server. If it does, you'll need to copy the files over and either replicate the filepath, or (check this syntax carefully as I'm handballing it)

UPDATE tbl_TableName SET filePath = REPLACE(filePath, 'Old\Path', 'New\Path')

Fire up the application or website that connects to it, and compare the old version to the new version.

Upgrade the Compatibility Version of the database.

Again, fire up the application or website that connects to it, and compare the old version to the new version.

 

 

To answer your question - yes, you can restore the backed up database as a new database on the same server. I've done that before for testing things. But I've never tried having multiple versions of MS SQL on the same server, we've always spun up a new virtual machine for each version of MS SQL we're running.

Edited December 14, 2023 by Bedders

[Koldov]

we've always spun up a new virtual machine for each version of MS SQL we're running.

 

Not sure that's feasible for those of us without Data Centre licensing.

 

However, having said that, SQL sever seems not to need any activation and although it has a serial key, it comes preinstalled in any MVLSC download. Although I don't see it listed separately on our VLSC and I can't see any 'activations' for it...

 

I've recently had to install a 2nd SQL server version for Veeam on a server that used to host SIMS (and still has the original SQL 2014 installed but dormant).

 

Seems to work OK so far although the SIMS SQL has been 'turned off' so YMMV.

 

Also MS says it's fine from the skim read I did on this help page:

 

https://learn.microsoft.com/en-us/sql/sql-server/install/work-with-multiple-versions-and-instances-of-sql-server?view=sql-server-ver16

Edited December 15, 2023 by Koldov

[JOrdan01070]

Thank you both I managed to get it sorted.

[Bedders]

Not sure that's feasible for those of us without Data Centre licensing.

 

We tend to be migrating from one version of SQL to a newer one when we upgrade the server OS, so for us we're buying the Windows Server Standard OS anyway along with the latest SQL server version.

 

That said I've only done this twice in my time here