[ Main Index | IPSentry Help Index ]
 

The IPSentry Graphing Database SQL Upsizer is a utility used when you need to store a massive amount of statistical detail history beyond the limits of the default file system.   Currently, the database used for the detail statistics has a limit of 2GB which will store approximately 29 million data samples.

Access this utility by launching the IPSEntrySQLUpsize.EXE utility contained in the IPSentry application path.
(The default location is c:\program files\rge inc\ipsentryv6\...)

The IPSentry SQL Upsizer allows you to upsize IPSentry graphing statistics data from the MS Access MDB (ipsgdata.mdb) to Microsoft SQL Server.

Prerequisites
Create an empty database on your SQL Server e.g. (IPSGDataSQL).  Do not create any tables, indexes, etc...  You will need this database name, your SQL connection configuration, and security credentials (either Windows Auth, SQL, or Mixed) to complete the above form.

In the above example, we have SQL Server running on the local machine listening on TCP Port 127.0.0.1 port 1433 (default) and have created a table named IPSGDataSQL configured for SQL login credentials to be passed rather than windows authentication.

Before proceeding with the IPSentry SQL Upsize, you can test your connectivity to the SQL server by creating a Data Source which connects to this database through Control Panel, Administrative Tools,  Data Sources.  If you are unable to create a data source using these windows tools, it is unlikely that IPSentry SQL Upsizer will be able to connect either.

The following fields are available for input:

Upsize Ipsgdata.mdb from:
This field is the location in which the Ipsgdata.mdb file is currently installed.  The default location is under the IPSDataRoot folder in the IPSentry application path.

OLE DB Provider
This field should contain SQLOLEDB unless otherwise instructed.  At this time there are no other valid entries for this field. 

SQL Server or IP Address[,port]
Enter the name or IP address of the SQL server to which the database will be upsized.  This will be the location assigned during configuration of the SQL Server for the database you just created in the prerequisites outlined above.

Database Name
Enter the name of the empty database you created on the SQL Server in which to store the IPSentry graphing data. 

Database Owner
Depending on security policies and SQL configuration, you may need a different name other than the default of dbo.

Use Windows Authentication
If your SQL Server is configured to utilize Windows Authentication rather than SQL logon authentication, checkmark this option.  Otherwise leave this value unchecked.

SQL Logon / Password:
If you are not using Windows Authentication, enter the user logon name and password of the user on the SQL Server with rights to create, modify, delete, read and write in this database.  Use of sa is preferred at this point and may be modified after creation of the database table structures.

Use Custom Connect String
If you are familiar with database connection strings, you may prefer to utilize your own connection string parameters.  Use of this option requires advanced knowledge of database connection and configuration of data sources.

Prepare Bulk Import File
If you are upsizing a database with millions of records, the amount of time required to import the data may be excessively long. Instead, you may wish to create an import file to be used in conjunction with DTS (Data Transformation Services) on SQL Server.

Selecting this option will create a delimited text file for the DataValues table which will then need to be manually imported to the database using the DTS Wizard.

This data file named BulkDataValues.csv will be created in the IPSentry application folder and contain the following fields - Tab delimited with CRLF as end of record.
DataKey DataIDent iDate iValue iState

Note that "DataKey" is the record identifier.  When running the DTS wizard, you will need to allow import of the row identity column.

In our testing of 29 million+ records, the time to import using the standard upsize record insertion would be over 8 hours.  With the use of DTS and a bulk import file, the time for extraction and completion was just around 1 to 1.5 hours.

Validate Settings
Click this button to validate all of your configuration settings and have the IPSentry SQL Upsizer test to make sure it can access the Ipsgdata.mdb file and access the SQL Server and database specified.  You will not be able "Upsize Now" until the validation has passed.

Upsize Now
Once the validation is complete, click the Upsize Now button to start the database upsize process.  Depending on the amount of data contained in your current database, network speed, and other factors this could take anywhere from a few minutes to several hours.   If the upsize process is interrupted, it will have to be started from the beginning.

Update Config
Once the upsize is complete, click this button to update the IPSentryV6.Config data file with the settings you have entered.  IPSentry will use this configuration information to access the SQL Server and begin logging data to the SQL Database rather than the MS Access database.  Do not perform this option until either you have converted all of your existing data.

 

 

 



     If you require additional assistance, please visit our on-line support forum at http://forum.ipsentry.com.
 
©1997-2012 by RGE, Inc. - All Rights Reserved
IPSentry® is a registered trademark of RGE, Inc.

 
Support Forums: http://forum.ipsentry.com
Web Site: https://ipsentry.com
Support Email: support@ipsentry.com