Setting Up Microsoft SQL Server for AGS Usage Database (optional)

The GeoSystems Monitor can be configured to store AGS Usage information in either a SQLite database or a Microsoft SQL Server / SQL Server Express database (ver. 2008 and higher).

For small clients with minimal AGS usage, or clients with one AGS server with a light load, the SQLite database option should be sufficient.

For all others, a full RDBMS solution is recommended, because of the amount of information that will be stored and reported on.

To use the RDBMS option, the following steps need to be performed prior to trying to implement AGS Usage activity:

Setup and configure a Microsoft SQL Server (MSSQL) DB (either SQL Server or SQL Server Express will work):

1. Create a new DB (can be on any server that is accessible to GeoSystems Monitor).
2. Add table structure to DB.
3. Add DB user for GeoSystems Monitor access.
4. Add an ODBC connection on the GeoSystems Monitor server (this is the connection to the DB Server).
5. Configure GeoSystems Monitor AGS Reporting / Charts application to use the ODBC connection. 
     a.  ONLY one Report DB is needed. Multiple AGS Sites can save to the Report DB.

Optional RDBMS Report Database setup for MS SQL server (or SQLExpress)

1.  Open up SQL Management Studio and add a new database on your SQL Server:

Name: GeoMonAGSStats

Initial size and file location can be your environments defaults. File will grow over time.

2.  Run SQL script (in SQL Mngt Studio) to add table structure to new database (3 tables). 

VESTRA_CreateTables_for_GeoMonAGSStats_MSSQL.sql located in the GeoSystems Monitor\Documentation\UsageDBs_RDBMS folder.

3.  Either add user to SQL server manually and give database write access to GeoMonAGSStats

Or run script provided to add user:  VESTRA_CreateUser_for_GeoMonAGSStats_MSSQL located in the GeoSystems Monitor\Documentation\UsageDBs_RDBMS folder.

4.  The next step needs to be done on the Web Server that the GeoSystems Monitor is running on.
Create a 32 bit ODBC connection to the database just created above.
To launch (on Win 2k8R2 Server): from the command line run: c:\windows\sysWOW64\odbcad32.exe

NOTE the sysWOW – this is for the *** 32 bit ODBC Connection ***

         * There is a shortcut in the GeoSystems Monitor\Documentation\UsageDBs_RDBMS folder

Go to the SYSTEM DSN tab and click [Add]

Select SQL Server and click [Finish].

Fill out the below information

Name:  The name of the connection that will be entered into the GSM points
Server: Is the Server where the Report DB was created above

Click [Next>]

Input the credentials to connect to the Report DB (created earlier)

Click [Next>]

Select Database for ODBC connection to connect to: Database created earlier

Click [Next>]

Create New Data Source:

Click [Finish]

Summary of ODBC Connection just created.

Click [Test Data Source]

You must have a Successful test to utilize the RDBMS option for reporting.

Click [OK]

The connection name created here is entered in Each Point configured for AGS Usage Statistics.