FAQ - Server Install

Question
Updating SQL Server versions and or moving servers
Answer

We have performed dozens of upgrades of earlier versions of MS SQL Server to SQL 2008 without issue. The process is straight forward, even if it contains a significant number of steps, largely covered by install / upgrade wizards and the occasion misleading Microsoft error dialog.

The upgrade requirements are the same as the requirements for the installed version of MS SQL Server and a disk space for each database of between 2 and 10GB (if 4 GB if not SQL 2008 R2 Express).

The backup folder should be on a physically separate drive. As a significant percentage of catastrophic Server failures are hard drives failing in an unreported manner: cross linked segments even with SMART technology. It is strongly recommended that full backups are taken to a physically separate drive, and checked for restorability on a regular basis.

When upgrading, irrespective of the current version of  MS SQL (2000, 2005, 2008) we recommend the target version should be MS SQL 2008 R2. The express R2 version is freely downloadable from Microsoft.

Upgrades can be inline - in which case the process is likely to take a hour or two. We have not experienced any difficulty with inline upgrades.

SQL Server upgrade

The first step is to backup the (Ava) databases.

If the decision is to migrate to another server then the following applies:

There is a requirement for two shared folders with full rights to be mapped to Ava users: one for document templates, one for update files.

  • Install the new SQL instance.
  • The backup(s) should be restored to the new server instance.
  • Copy the contents of the current update and template shares to the new shares.
  • Grant permissions for access to the new server databases for the appropriate Window user group.
  • Check the port number of the new instance, if the port for the connection ip is using dynamic settings, make the port fixed on the previously dynamically selected port.
  • Enable the server firewall for the ip range and ports TCP 1433, UTP 1434 and TCP (now fixed) port.
  • Configure the *.udl files (UDL stands for Universal Data Link and these are simply script files which contain the connection strings to the appropriate databases) in the update folders to connect to the new instances.
  • Copy one of these *.udl files to a client and check that it connects.
  • Edit all the bat files in the update folder so they reflect the new server settings.
  • On each client go to the program files / Ava pa / set-up auto update.

SQL 2008 server move 1

Select find avapa.exe to update from.

SQL 2008 server move 2

Search for the new update shared folder.

SQL 2008 server move 3

 

The applet will show files being copied.

(This process will also copy the new connection udl files and bat files across to the client.)

  • Run Ava and test (you may want to test/ do a dry run on one client first to check permissions).
  • If Ava connects then it will work fully. If it does not connect, open the appropriate UDL file and click "test". It has always been the case that if the UDL test works Ava works and vice versa. The UDL applet simple exercises the connection string so either its configuration is incorrect, there is a firewall blocking the SQL ports, network permissions are incorrect or the server instance has not been correctly configured.

Web:

Ava web pages run on any IIS that supports net 3.5 or 4.0.

Once the update is successful, change the compatibility of the databases to SQL 10 (2008) and then request an Ava update to the latest version.

Related links:

Google's disk failure experience with statistics

Google's disk failure experience BBC article

Download SQL 2008 R2 Express from Microsoft

If you found this information useful, please share it!


This FAQ was last updated on Wednesday, September 30, 2015

Contact Information

To find out more about Ava solutions you can contact us in a number of ways:
Follow Us...