Upgrading your Microsoft SQL Server database engine

Last reviewed on 2022-09-30

The PDXpert Server installer automatically downloads and installs a version of the Microsoft SQL Server Express Edition database engine.

To view the SQL Server database engine's version and edition, open the PDXpert Application Server console, and select the Information tab. For example: SQL Server 2008 R2 SP1 Express Edition (64-bit) (10.50.2500.0).

  • The version represents the technology era: 2008 (10.0), 2008 R2 (10.5), 2012 (11.0), 2014 (12.0), 2016 (13.0), 2017 (14.0), 2019 (15.0), 2022 (16.0), etc.

  • The edition identifies various database size, capability and performance limits, as well as cost: from Express (limited but free) through Enterprise (most capable and expensive).

Upgrade the installed SQL Server database instance when:

  • the installed version becomes unsupported or lacks important new features; or
  • your edition can't keep pace with the workload; for example, you add more users and product data.

Special note for SQL Server Express LocalDB§

Beginning with PDXpert 10.0, the PDXpert system setup installs SQL Server Express LocalDB. Express LocalDB provides a smaller download, faster installation, reduced application footprint, and tighter security (by allowing only PDXpert and other on-machine applications).

Although it has many advantages as the starting database for PDXpert, Express LocalDB is more difficult to upgrade and use with SQL Server Management Studio, ODBC drivers, and other applications. When an upgrade is needed, consider moving to a more advanced SQL Server edition (Express, Standard, Enterprise).

  • When upgrading SQL LocalDB, there are some extra steps needed to complete the process; search the web for upgrade sql localdb

  • If other applications require remote connections to the PDXpert system database or to simplify database development, install the most current instance of SQL Server Express (or higher). After installation, use the PDXpert Application Server's Change Database Server button to move the existing database into the new instance.

Upgrade preparation§

Before upgrading:

  • Review Microsoft's SQL Server in-place upgrade procedure (for example, Upgrade SQL Server).

  • Ensure that your current SQL Server version has the most recent service packs. Some upgrade paths require a minimum service pack (e.g., SQL Server 2008 SP2).

  • Confirm that your PDXpert system is compatible with the planned SQL Server instance. If your PDXpert system is no longer on a Maintenance, Support and Upgrade ("MSU") subscription, check with us to ensure it's compatible with the new SQL Server version you intend to install.

  • Confirm that you're upgrading to a SQL Server version that's compatible with (that is, at least equal to) your current SQL Server database instance. You cannot upgrade a 32-bit instance of SQL Server directly to native 64-bit. Refer to Microsoft's list of supported From/To upgrade paths.

    • Ensure that your server machine's hardware and software configuration meet the increased requirements for the SQL Server you will be using.

    • Windows Installer 4.5 is a prerequisite for SQL Server 2008 and higher. This may require a separate download for your operating system before running the SQL Server installer.
    • If you're not upgrading to a new version of Express, you must have the Microsoft installation media and product key for your new SQL Server edition.

  • All users should exit from their PDXpert client applications.

  • Disable any scheduled tasks (such as automated backups or materialized view scripts), ODBC clients, MRP transfers, and other external database connections.

  • Back up the current database. If you use the PDXpert Application Server to back up your database, you can use the Database without Library (*.bak) file option.

Upgrade process§

During an in-place upgrade, the SQL Server installer takes the PDXPERT instance off-line. Any active PDXpert clients will lose their connection, as well as any unsaved work.

Upgrade SQL Server Express version

If you're upgrading an older SQL Server Express — for instance, from 2012 Express to 2019 —, download the free Express installer. Select the correct architecture and language for your server.

You cannot use the SQL Server Express LocalDB (SqlLocalDB.msi) installer to upgrade an earlier SQL Server Express Edition.

Start the SQL Server installer, select Upgrade from SQL Server 20xx (or similar) and follow the instructions to upgrade the PDXPERT instance.

Upgrade SQL Server edition

If you're upgrading to a different edition of SQL Server (for example, from Express to Standard), refer to Microsoft's instructions for the edition you'll be installing.

Upgrade validation §

After SQL Server is upgraded, the database remains at the previous SQL Server compatibility level. For example, if the database instance was SQL 2014 (level 120) and upgraded to SQL 2019 (level 150), the database file remains at 120. Upgrading the compatibility level gives improved T-SQL language features. Always check the Microsoft release notes and test your Transforms and Views collection members. For details, see Microsoft's note: View or change the compatibility level of a database

After altering the database compatibility level, restart the PDXpert Server service (or restart Windows) to refresh the SQL Server connection.

After upgrading:

  • Open the PDXpert Server application, and confirm that the new SQL Server engine is identified on the Information tab. For example: SQL Server 2019 Standard Edition (64-bit) (15.0.2080.0) with PDXpertDB at Compatibility level 150 The information shown may be different based on PDXpert release and SQL Server edition/version.

    If SQL Server is not identified, restart the PDXpert Server service (or restart Windows) to start the SQL Server service. If SQL Server is often slow to start, delay the PDXpert Server service startup.

  • Enable any disabled tasks or database connections.

  • Validate that users can log into the PDXpert client, find and open item records, and view or save file attachments.

If your PDXpert system has an active Maintenance, Support and Upgrade ("MSU") subscription, upgrade to the current PDXpert release after upgrading SQL Server.