Database Installation / Configuration

Database Installation / Configuration Instructions.

BoincSpy has the ability to save all the results sent to Berkley to be saved locally to a database, this can be used to calculate the following:

Average CPU / elapsed time
# of Workunits completed
#of Workunits completed / day.
What are the best triplet / gaussians / pulses / spikes that the WU's have generated for the SETI project..
Display all WU's on the SkyMap

Download

DBSetup.zip - md5 checksum: c6e4e40184ca8e5b72625a6ef2d3e3dc *DBSetup.zip

Unzip the DBSetup.zip file: ( Unzip to a directory where the database server resides )

Installation steps.

Initial Installation
Upgrade procedures
Running the agent as a Service

Usage

BoincSpy 1.0 provides a SQL DB agent to update the database.

The executable is in the \DbAgent\cmdline directory.

Before running the program you need to run BoincSpy and set the General settings:

Enable the logging and select the MSSQLDatabase option:

Modify the Hostname\Instance Name, Username and Password fields that correspond to your SQL Server configuration. (Defined below )

Hit the OK button.

Running the BoincSpyAgent.

Run the BoincSpyAgent.exe from the command prompt or Windows explorer.

Notes/Issues:

If you are interested in viewing the raw data that I am collecting you can use osql or Microsoft's SQL Server Web Data Administrator program.
The BoincSpy agent program should be run on each computer that BOINC is running.
You need .NET FrameWork 2.0 installed, same pre-requisite as BoincSpy.
You may need to install MDAC 2.8 on your computer ( If you are using Windows ME / Windows 98 or Windows NT ).
There is a possibility that there may be missing data being saved, this should be a very small time slice, ~ 7 seconds of missing data at the end of the WU.
If the there was errors creating the database, it's possible the removing of the database from the control panel did not work. To drop the database manually:
- osql -Usa -PSomeSAPWD -S Servername\BoincSpy
1> Drop database BoincSpy

2>go

Database Upgrade procedures

Changes to BoincSpy Database - (Feb 6th, 2005 V0.7 )

Modified the algorithm for calculating the number of WU's / day processed. When Date switched into the new years caused problems.
For the skymap, only view SETI@Home projects. ( Do not display the alpha / beta project results ).

osql -Usa -PSomeSAPWD -S ServerName\BoincSpy -i DBUpgrade-V0.7.sql

Changes to BoincSpy Database - (Jan 8th, 2005 V0.6 )

Changed a couple of stored procedures to support additional skymap functionality.

osql -Usa -PSomeSAPWD -S ServerName\BoincSpy -i DBUpgrade-V0.6.sql

Changes to BoincSpy Database - ( Dec 19th, 2004 V0.4)

Created / Modified stored procedures to support displaying best scores for the Seti Project.

BoincSpy Database upgrade procedure: V0.3 -> V0.4

In the directory where you unzipped DBSetup.zip type in the following command:

osql -Usa -PSomeSAPWD -S ServerName\BoincSpy -i DBUpgrade-V0.4.sql

Changes to the BoincSpy Database - ( July 1st, 2004 V0.3 )

Modified the schema to support the predictor@home project.
Changed the statistics procedures to filter by project.

BoincSpy Database upgrade procedure: V0.2 -> V0.3

In the directory where you unzipped DBSetup.zip type in the following command:

osql -Usa -PSomeSAPWD -S ServerName\BoincSpy -i DBUpgrade-V0.3.sql

Changes to the BoincSpy Database - ( May 06, 2004 V0.2 )

The database installation program is no longer used. It was not providing enough flexibility ( IE install program did could not prompt for location of the database.)
No longer support the integrated security option. This has caused issues with installation.
Database schema has changed to support different projects, when they become available.

BoincSpy Database upgrade procedure: V0.1 -> V0.2

If you have installed the previous version do the following:

Goto control panel->Add Remove Programs and remove the BoincSpy Database Installation program. ( This will remove the database installed on the SQL Server, this will also remove the data as well. Sorry about that. After this release, if there are any database changes in the future. there will be upgrade scripts. )

Instructions to install the Database Server.

To gather these results, a database server is required, at this time SQL Server 2000 / SQL Server MSDE is supported.

SQL Server MSDE is a re-distributable product that is free from Microsoft. Click here to download.

If you have multiple workstations working on BOINC, pick one of your machines that has the most CPU / memory available and designate that for the database server.

Once downloaded and uncompressed, use the following command DOS command to install it: ( Uncompressing will usually create a c:\msde2000a directory ).

setup.exe SAPWD="SomeSAPWD" INSTANCENAME="BOINCSPY" SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0

The ReadmeMSDE2000A has other options that you may want to specify, IE directory name you want this to be installed on.

Configuring the Database:

Edit the create_db.sql script if you require the database files to be located different from the default location.
Change the 2 database 'FILENAME=' attributes. IE:

CREATE DATABASE [BoincSpy] ON (NAME = N'BoincSpy_Data.MDF',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$BOINCSPY\Data\BoincSpy_Data.MDF' , SIZE = 2,

FILEGROWTH = 10%) LOG ON (NAME = N'BoincSpy_Log.LDF',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$BOINCSPY\Data\BoincSpy_Log.LDF' , SIZE = 1,

FILEGROWTH = 10%)

Note: If you have 2 physical drives, it is recommended that you have one file located on one drive and the other file on the other drive.

Use the following command to create the database.

osql -Usa -PSomeSAPWD -S Servername\BoincSpy -i create_db.sql
Create the database schema.

osql -Usa -PSomeSAPWD -S ServerName\BoincSpy -i BoincSpy.sql

Creating a Database user.

Need to create a database user, to create a user you must log into the sa account using the osql command supplied by the SQL/MSDE Server install.

c:>osql -S Servername\BOINCSPY -Usa -PSomeSAPWD

Once logged in use the sp_addlogin command: As an example:

	osql>EXEC sp_addlogin 'some_user', 'some_password', 'BOINCSPY'

	osql> go

 Change the owner of the database to the user specified above:

	osql> use BOINCSPY

	osql> go

	osql>exec sp_changedbowner 'some_user'

	osql> go

Running the Agent as service:.

Prerequisites

The Service must run as LocalSystem account not as a Network Service account. You have to goto the services from Admin tools to change the logon type.

Please Note: Starting with 0.9.9 you need to change the Service to run under an Account that BoincSpy is running from. DO NOT USE Log on as Local System Account.

Only run under Windows 2000, Windows Pro/ XP Home and Windows 2003 Server.

Installation

Follow the instructions definned in the Usage section ( IE run BoincSPY General settings to set the logging attributes ).
Goto x:\BoincSpy\DBAgent\service directory.
In the \service directory, type the following. DBAgentService /install
The service does not start automatically, you have to goto the Control Panel->Administration->services and set BOincSpy - DBAgent to start automatically.

Notes

As this is a preliminary release, you should check that the service is running. To do this:

Check the DBAgentService.log in the service directory, all error messages will be displayed there.

If the following message is display, then the service has aborted, you will have to restart the service from services control panel.

startRefresh.finished @ x-xxx-xxxx xx:xx:xx

Note: You need Debugging turned on to verify functionality, this can be enable from the BoincSpy Setting/Options menu.

Last Updated: February 06, 2005 12:55