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

The GeoSystems Monitor can be configured to store SDE 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 SDE content, or clients with one SDE instance under 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 SDE Usage monitoring (detailed steps included):

To 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 SDE Usage Charts application to use the ODBC connection.
   1. ONLY one Report DB is needed.  Multiple SDE Instances can save to the Report DB.

A detailed description of these steps follows:

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

Name: GeoMonSDEUsage

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 (4 tables).

VESTRA_CreateTables_for_GeoMonSDEUsage_MSSQL.sql located in the GeoSystemsMonitor\Documentation\UsageDBs_RDBMS folder.

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

Or run script provided to add user:  VESTRA_CreateUser_for_GeoMonSDEUsage_MSSQL.sql located in the GeoSystemsMonitor\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 fill out the information below

Name:  The name of the connection that will be entered into the GeoSystems Monitor 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 SDE Usage.  So, this name must be known by whoever will be responsible for creating the SDE usage monitoring points in the GeoSystems Monitor.