Installing SQL Server 2008

Aviation Databases are using SQL Server as a data server in our latest programs.  This is done due to the advantages this gives us to improve our applications.  SQL Server is a more stable data platform and is faster when large files are processed by our programs.  There are various other advantages to using SQL server and it is simply time to move from the old dBase environment.

Users not familiar with SQL Server, find it difficult to know where to start and what is required.  SQL Server is a very powerfull server system, but to use our programs with SQL server will not require the users to interact with SQL server at all, as all communication with SQL Server is done from the program.  Afirst time installation is required on the Server or a PC dedicated for this task.

Note that these instructions are only relevant to the server PC and is not required for other station PC’s that use our programs.  These PC’s will simply link to the server, but does not require any installation of SQL Server components.

The minimum requirements does not require any SQL server management tools to be installed.  Management tools are handy, should maintenance be required in the SQL server.  The only other choice is to know if your server PC is a 32-bit or 64-bit system.  The next step is to download the latest installation programs and to install.  Please follow these simple steps to ensure a smooth installation.

We are using SQL Server 2008 Express as a minimum requirement.  SQL Server Express is a free application for the typical database size that we normally require and can be downloaded from the internet.  At the moment R2 is available and can be downloaded from these sites:

• Basic server installation
• Server with management tools installation

Experienced users can simply follow the installation dialog, but this might be challenging for new users.  It is therefore recommended that users download this small configuration file from our ftp site to enable installation from the command line with no user information required.

Older PC’s might require other updates, before you will be able to install SQL Server.  These requirements are defined on the web link and can also be downloaded from this page.  These are: Microsoft .Net Frameworek 3.5 SP1 and Windows Installer 4.5 Note that the Server with management tools requires an extra program: Windows PowerShell 1.0 to be installed on the server PC.

Once your Server PC is up to date, proceed to install SQL Server.  Copy the configuration file into the same directory as the SQL Server 2008 installation file.  Open the command prompt window (Start –> Run > cmd).  Change to the download directory and run the installation from the command prompt: (On a 32-bit installation)

SQLEXPR32_x86_enu.exe /ConfigurationFile=c:\sql\AvdataSQLConfig.INI

On a successful installation, it should be possible to see the server.  This can be checked from the command prompt, by entering the command: “sqlcmd –L”

Grant SQL user access for programs

Users must have sufficient access rights or roles on the SQL server to connect to the database and access the database files and procedures as stored in the SQL server.  This is applicable to all programs using a SQL server for the data, like Toolman and Status.

Installing Status and Toolman applications for the first time on a server machine, will grant the SQL user sufficient access to the database.  It is however necessary to allow other users access to the database.  This can be done from inside the program from the SQL connect window.

There are two different possibilities here, users can log on with Windows Authentication, where the program will use the access asigned to the “BUILTIN\Users” or specific users where Windows Authentication is not used.

From the waterfall menu select the SQL Connect window, connect to the server and select Save on the catalog selection.  The Manage Access button should be available if you have sufficient access to grant access to others.

Select the “Manage Access” button.  The window that will open shows all you access rights and has two options to grant rights to other users.

 

Selecting the top “Grant” button, will allow all “BUILTIN\Users” access to the selected catalog or database.  This will therefore work in a domain or environment where users will connect through Windows Authentication.

Alternatively, it is possible to create users and assign the applicable rights to this new user to access the database.  Note that some SQL server requires a strong password, otherwise this step might fail.

Once access is granted to other users, there should be no problems for them to connect and use the applicable database from the program.

---

Users experienced in SQL can use the Management Studio application to set this access.  Assign the database owner role to users.

Grant application user access

Users must have sufficient access rights or roles on the SQL server to connect to the database and access the database files and procedures as stored in the SQL server.  This is applicable to all programs using a SQL server for the data, like Toolman and Status.

Installing Status and Toolman applications for the first time on a server machine, will grant the SQL user sufficient access to the database.  It is however necessary to allow other users access to the database.  This can be done from inside the program from the SQL connect window.

There are two different possibilities here, users can log on with Windows Authentication, where the program will use the access asigned to the “BUILTIN\Users” or specific users where Windows Authentication is not used.

From the waterfall menu select the SQL Connect window, connect to the server and select Save on the catalog selection.  The Manage Access button should be available if you have sufficient access to grant access to others.

Select the “Manage Access” button.  The window that will open shows all you access rights and has two options to grant rights to other users.

 

Selecting the top “Grant” button, will allow all “BUILTIN\Users” access to the selected catalog or database.  This will therefore work in a domain or environment where users will connect through Windows Authentication.

Alternatively, it is possible to create users and assign the applicable rights to this new user to access the database.  Note that some SQL server requires a strong password, otherwise this step might fail.

Once access is granted to other users, there should be no problems for them to connect and use the applicable database from the program.

---

Users experienced in SQL can use the Management Studio application to set this access.  Assign the database owner role to users.

Installing Toolmanager software

Our toolmanager program is designed for the aviation industry to keep track of tools.  Tools can be issued to staff, jobs or sent for calibration.  Various reports assist users to keep track of tool movement.  For more information about the program, please link to our Toolmanager web site.

This article follows a step by step guide for fist time installations.  Please contact us from the link on the web site to arrange for the FTP details to download the evaluation program.   This installation assumes that users have a SQL server 2008 Express or better installed on the network.  The first installation must be done from the server machine to attach the database to the server.

Once the installation program (ToolSetup.exe) is downloaded, it is recommended that you copy this to a directory on the server machine where all users can access the exe for the first installation as well as for future upgrades.  This directory is remembered as a default location for future installations.

Run ToolSetup.exe

Select all options for a first installation, provided that you are on the SQL server machine where the empty table structure can be installed.

Note on the install summary the default paths where all the files will be installed.  The Toolman.exe will be installed in the default Program Files directory.  As this is a server machine, an empty database will be installed in the directory c:\ProgramData\Avdata\SQL  This is done to allow the program to attach this database to SQL.

Now run the program TM.exe

First time users will automatically be taken to the SQL connection window.  At this stage the SQL server must be running and you must have sufficient access to the server.  The preferred method to connect is by means of Windows authentication, but any SQL power user can be use to attach the database.

This window opens with the default values, but can be changed for each installation.  Select the “Open” button to test for a successful connection.  Once connected, the Catalog section will be available.  The “Create” button should be available if you have sufficient rightsa and if the installation section had the “Server” install option checked. Click on “Create” to attach the dabase as installed earlier.  A successful installtion should produce the screen below.  Note the important last line: “Database found in SQL server”.  If this is not shown, something went wrong and the database did could not be attached.

Now click “Save” in the Catalog section to store this information for future connections.  You will notice that the ODBC connection will automatically be created.  Make sure that the connection method, ie user and password or windows authentication is correct.  Select the “Test/Save” button. 

You can now exit from this window and the Toolmanager application should be configured for use on this machine.

 

---

On subsequent installations on pc’s linking to this server, follow the same steps, without selection of the server installation during installation.  Once you have connected to the SQL server, the “Create” button will not be available, but the drop down list will have the catalog adbTools which can be selected before the “Save” button is selected.