The Export Configuration dialog allows you to configure KeyServer to export its internal data to an external database, such as Oracle, MySQL, SQL Server, etc.
What exporting is NOT:
There are various reasons you might want to use exporting:
Both external and internal reports in KeyConfigure can be used to access either exported or internal data. External reports (Crystal, MSAccess, FileMaker, etc.) always use ODBC with a correctly configured DSN to specify the target database. Internal reports target the KeyServer's internal database by default unless the Options button in the Configure Reports dialog is used to specify a DSN targeting an exported database.
If there are firewall rules controlling traffic between the KeyServer host computer and the external database server, you may need to set up appropriated firewall rules. Consult the database server documentation.
Depending on the platform hosting the KeyServer process, specific export modules are available:
Note: newer versions of KeyServer may include additional fields in various tables (e.g. the Computer table has had additional fields added in many of the major releases of KeyServer). In order to include new or additional fields into a previously exported table, consult the tables documentation.
Three additional export modules listed below are included for use in transforming KeyServer data to XML, comma delimited, or tab delimited text. These methods do not use the network to pass data directly to a remote database server and cannot be performed incrementally (e.g. complete database files are exported every time). They can be used for static export/import of K2 data into another database or analysis tool. Usually, exporting to a database of some sort is a better choice than exporting to any of the text formats.
Note: every time one of the static export methods (XML, CSV, or Text) is run, the complete database files are exported into the "Export" folder within the KeyServer Data Folder replacing any previously exported file of the same name. For these three static methods, scheduled exports are allowed at most once per day. Also, since a complete export of large databases can be slow, you might want to disable export of Audits and Usage when using a text based method.
OPEN Export Configuration dialog
By default, the export module is Disabled, meaning that the internal databases will not be exported.
All tables except for Audits and Usage are always exported. These two check boxes allow you to configure whether the Audits and Usage tables are exported or not, since these two tables are by far the largest. By default, they are both exported, so that the external database will contain all the data that the internal database does.
Configuring this module allows KeyServer to use ODBC to export the internal databases to an SQL database through a DSN (defined with system access permissions on the computer which is running KeyServer). Before configuring the ODBC module, you must first set up the ODBC Data Source that KeyServer will use. Instructions on setting up an ODBC Data Source should be included with your database software, as the details are specific to each database system.
Note that if you are using a 64-bit version of KeyServer, you will need to use a 64-bit ODBC driver. Likewise, 32-bit KeyServer requires a 32-bit ODBC driver. This is one of the few reasons to prefer one bitness over the other when given a choice on a 64-bit OS. In addition, you must use the correct ODBC Administrator which matches the bitness of the KeyServer executable. Specifically, if you are using 32-bit KeyServer on a 64-bit OS, you will need to manually locate and launch the 32-bit ODBC Administrator, which is at C:\Windows\SysWow64\odbcad32.exe, and use that version of ODBC Administrator when defining the DSN which will be used in the KeyServer export configuration.
You will also need to create a database into which KeyServer will insert data. You can use an existing database if you choose. There must also be an account and password that is granted read and write (SELECT, INSERT, UPDATE, CREATE TABLE) access to the database.
Once you have set up an ODBC Data Source (DSN) and the corresponding database (and access account), you can configure KeyServer to export its data to this Data Source. Choose the Exporting command from the Config Menu in KeyConfigure, and then select ODBC from the pop-up menu. In the Configuration section of the dialog box, enter the name of the Data Source you just set up, along with the account name and password (don't leave the password field blank).
There are various options that you can set to control the SQL commands used for creating new tables, records, and fields.
The basic queries for adding new rows and modifying existing rows can be done in one of four ways:
There are three options for text encoding - the correct choice might depend on what type of RDBMS you are using but also how the specific database is configured:
Time fields can be exported in one of four different formats:
In the lower left, in the Schedule area, you can configure how often KeyConfigure will perform an export. Note that the export will be incremental, so there will not be a huge performance penalty if you choose a short interval. For a small KeyServer you might export as often as every hour. For a larger KeyServer you should probably export no more frequently than once a day.
Once these settings have been made, KeyServer begins to export its databases according to the schedule you have configured. You can force an initial export by clicking Export Now. The first thing that KeyServer does during an export is check if the tables are already present in the database. If not, it attempts to create these databases using the appropriate “CREATE TABLE” command. If for some reason the tables cannot be created, you can create these tables yourself (FileMaker Pro for example does not accept this command). See KeyServer Tables Description for the details of what is in each table.
The following list gives examples of how to configure the ODBC export module to work with your particular ODBC driver. If you are using a different database and driver, we would be interested to hear how to successfully configure it.
Microsoft (Access, SQL server, etc.) - Use Microsoft-compatible CREATE TABLE, Use SQLBindParameter optimization, Use SQLBindParameter with multiple rows, Use MERGE semantics, Use Standard DATETIME
MySQL - Use REPLACE semantics, Use Standard DATETIME
Oracle - Use Microsoft-compatible CREATE TABLE, Use SQLBindParameter optimization, Use SQLBindParameter with multiple rows, Use MERGE semantics, Use Oracle-compatible DATETIME
PostgreSQL - Use PL/pgSQL stored procedure, Use UTF-8 Encoding for text fields, Use Text Times (Escape Backslashes in text fields only for old versions of postgresql)
Every computing environment is different, and with KeyServer's ODBC exporting you are using software from several different sources (KeyServer, ODBC, the ODBC drivers, the database, etc.) These factors increase the potential for problems, so you should be sure to verify that your configuration works properly.
One good strategy is to test your configuration at each stage. First set up your database and make sure you can get the proper access to it using the database's native tools. Next, set up the ODBC Data Source and verify that it works correctly using a simple ODBC-based query tool like Crystal Reports, MS Access, MS Query, or any tools that come with your database system. It is important at all steps to verify that you have granted the appropriate permissions to the account that KeyServer will be using to connect to your database.
Configure KeyServer's ODBC export module and click Export Now. Check that the tables were created properly. As a simple confirmation, you could use a query tool to run the SQL command "select count(*) from KSComputers" - this should return a number which matches the computer count at the bottom of KeyConfigure's Computer Window.
If you run into problems after configuring Exporting, check the Status Details to see what error is displayed and whether it contains enough information to understand the problem. If you can't see an error, or if it does not contain enough data, you can also configure Diagnostic logging to include more information about exporting. If you need to call Sassafras technical support for help in setting up ODBC exporting, we will most likely need to look at the diagnostic log.
In order for the linux version of this module to work, you will need to install unixODBC, which can be downloaded from http://www.unixodbc.org/.
The MySQL module can only be used to export to MySQL databases. It is available to KeyServers running on OS X or linux, and requires libmysqlclient version 18. If you want to export to a MySQL database from a Windows KeyServer, you can download the ODBC driver for MySQL and use the ODBC exporting module.
This module looks very similar to the ODBC module, but it has fewer options, and there are a few slight differences in how it should be configured.
The Data Source field usually contains both the host name and the Database name separated by a slash. The syntax is:
If the hostname is missing, then localhost is used. For example, typing "/ksdb" will use the MySQL server on localhost, and the database named "ksdb". Typing "192.168.0.4/ksdb" will use a MySQL server on 192.168.0.4, and the database named "ksdb".
In order for the linux version of this module to work, you will need to install the MySQL "Shared compatibility libraries" that MySQL provides. These can be downloaded from http://mysql.org/downloads/mysql/5.0.html (search for "Shared compatibility libraries" in the various Linux flavors).
The PostgreSQL module can only be used to export to PostgreSQL databases. It is available to KeyServers running on OS X or linux. If you want to export to a PostgreSQL database from a Windows KeyServer, you can download the ODBC driver for PostgreSQL and use the ODBC exporting module.
This module looks almost identical to the MySQL module.
The Data Source field usually contains both the host name and the Database name separated by a slash. The syntax is:
If the hostname is missing, then localhost is used. For example, typing "/ksdb" will use the MySQL server on localhost, and the database named "ksdb". Typing "192.168.0.4/ksdb" will use a PostgreSQL server on 192.168.0.4, and the database named "ksdb".
In order for the linux version of this module to work, you will need to have libpq.so.3 installed, which can be downloaded from postgresql.org.
The SQLite module can only be used to export to SQLite database files. It is available to KeyServers running on OS X. If you want to export to a SQLite database from a Windows KeyServer, you can download the ODBC driver for SQLite and use the ODBC exporting module.
The configuration for this module is very simple. All that is needed is to specify a file name in the Database field. This export will create this file in the Export Files directory (or Export Modules directory in older versions).
This module exports all databases in tab separated field format into the “Export Files” directory (within the "KeyServer Data Folder"). Unlike the ODBC or MySQL methods, the exported data is not immediately accessible to reporting tools until it has been explicitly imported into some database or analysis tool. As noted above, exporting is not incremental and can be scheduled at most once per day.
This module exports all databases as XML files into the “Export Files” directory (within the "KeyServer Data Folder"). Unlike the ODBC or MySQL methods, the exported data is not immediately accessible to reporting tools until it has been explicitly imported into some database or analysis tool. As noted above, exporting is not incremental and can be scheduled at most once per day.
After successfully configuring exporting, you may notice that all tables have been successfully exported, but all term tables are missing. If this is the case, try the following series of steps:
The reason that the KeyServer may need to be restarted in order to export the Term tables is that the Term tables, unlike all the other tables, very seldom change. Therefore, KeyServer only attempts to export them the first time it does an export after starting up. So if you have made changes to the export configuration, or manually deleted any tables from the external database server, you may be stuck in a situation where KeyServer won't try to export the Term tables again until you restart.