How to upgrade the SQL server running the current NAV.

Jacob1227

Hi All,

Nav 2009R2 is running with SQL 2008R2. Now we are going to upgrade from SQL 2008R2 to SQL 2014.

Can you please assist me with the below questions

So, 1) How to upgrade the SQL server running the current NAV?

2)Could this be an in-place upgrade or would a full back up and restore on a fresh install be needed?

Thanks in Advance,
Jacob A.

Slawek_Guzek

There are no special requirements when it comes to upgrading the SQL Server on which your database lives

You can do both - in place upgrade, or by backing up the database, restoring it onto new: SQ server instance, and repointing the NST configuration to the new SQL Server / database.

The in-place is the easiest and quickest way, but a tad bit riskier because if something goes wrong you will end up with no server and/or no database. Chances are really slim, yet non-zero.

Upgrading by SQL backup/restore will allow you to have the original environment intact, do some testing on the new server. The disadvantage is that you would need to take care of transferring all user logins to the new server. If you use Windows authentication only you can just script out the logins from the old server and recreate them in new - before restoring the backup. If you use database authentication then you would need to script user database logins with their passwords, re-create them on the new server, and still you might possibly encounter an 'orphan users' problem -a situation when the restored database includes user accounts but they do not link correctly with newly created SQL Server database logins, and in consequence users cannot log in.

While upgrading by backup/restore sounds potentially more problematic the advantage is that any potential problem will eventually surface in a new, fresh and separate test environment, not affecting production, and giving you plenty of time to investigate and solve them.

You can also create a backup of your entire SQL 2008R2 server installation (including the master database), then restore it in a separate place/box. Once the copy is up and running upgrade it in-place to the newer SQL version. The advantage is no hassle with user accounts, and still having a separate playground environment. The only trouble with this approach is that restoring the master database may be a bit tricky for an unseasoned SQL admin

Slawek_Guzek

There are no special requirements when it comes to upgrading the SQL Server on which your database lives

You can do both - in place upgrade, or by backing up the database, restoring it onto new: SQ server instance, and repointing the NST configuration to the new SQL Server / database.

The in-place is the easiest and quickest way, but a tad bit riskier because if something goes wrong you will end up with no server and/or no database. Chances are really slim, yet non-zero.

Upgrading by SQL backup/restore will allow you to have the original environment intact, do some testing on the new server. The disadvantage is that you would need to take care of transferring all user logins to the new server. If you use Windows authentication only you can just script out the logins from the old server and recreate them in new - before restoring the backup. If you use database authentication then you would need to script user database logins with their passwords, re-create them on the new server, and still you might possibly encounter an 'orphan users' problem -a situation when the restored database includes user accounts but they do not link correctly with newly created SQL Server database logins, and in consequence users cannot log in.

While upgrading by backup/restore sounds potentially more problematic the advantage is that any potential problem will eventually surface in a new, fresh and separate test environment, not affecting production, and giving you plenty of time to investigate and solve them.

You can also create a backup of your entire SQL 2008R2 server installation (including the master database), then restore it in a separate place/box. Once the copy is up and running upgrade it in-place to the newer SQL version. The advantage is no hassle with user accounts, and still having a separate playground environment. The only trouble with this approach is that restoring the master database may be a bit tricky for an unseasoned SQL admin