Good Day.
I currently have a MS SQL 2005 Standard 32bit system setup on Windows XP 4GB RAM.
We have front-end software that accesses this database.
Is it possible to upgrade to a higher version of MS SQL (say 2008 64bit) and still maintain 100% compatibility with the current software front-end?
I answered pretty much the same here:
SQL Server is backwards-compatible for 2 versions, and sometimes 3. You can maintain a specific version compatibility using compatibility levels:
However, SQL Server 2005 compatibility is no longer supported on any existing supported SQL Server version (SQL 2019 and the soon-to-be-released SQL Server 2022). The only way to get actual 2005 compatibility is to move to an Azure SQL Managed instance. If you have the installation media, you can install SQL 2008 or 2008 R2, but I don't believe you can get a legal software license from Microsoft.
As mentioned in the other thread, the system and software versions you have are underspecified to support SQL Server, and if SQL Server 64 bit version even works on Windows XP 64 bit version, you do not want to use XP 64 bit, it was not a reliable OS.
My idea was to get SQL 2008 R2 on a modern 64bit OS not XP 64.
The front-end software itself runs on a separate Windows XP System. This sounds like it could work.
Any possible compatibility issues?
The oldest supported version of SQL Server is SQL 2014. You may as well just try SQL2019 and put the database in a Compatability Mode of 100 (SQL 2008) You will need to use up to date drivers on the client.
Lots! Things like old style outer joins, date/datetime compatability issues with ODBC drivers etc. You will need to do a lot of testing.