Configuring SQL Server as the Auditing Database

Microsoft SQL Server 2000 Desktop Engine (MSDE) does not have its own user interface. EFT Server interacts with MSDE in the background. The OSQL utility allows you to enter Transact-SQL statements, system procedures, and script files for maintaining an MSDE 2000 database. During installation, EFT Server places a database schema file (ARM_DBScript_1_1.sql) in the Reports directory that can be used with OSQL to set up the Microsoft SQL Server 2000 Desktop Engine (MSDE) to work with ARM. For additional information on the OSQL utility, including common script samples, refer to article Q325003 on the Microsoft support pages, How to Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility.

An MSDE database has the following limitations:

  • MSDE is not supported on the Microsoft Vista operating system.

  • An MSDE database can be no larger than 2 GB (this includes MDF and NDF files only; log sizes are not included in the size limit).

  • Performance throttling occurs when there are more than five (5) concurrent workload batches in progress.

  • MSDE does not offer OLAP / data warehousing capabilities.

If you install EFT with ARM without the provided MSDE database and want to use your own SQL database, you will need to add the appropriate tables to your database, as described in Configuring the ARM Database in SQL Server, below.

The SQL Server 2000 Workload Governor

According the Microsoft MSDN Library articles for SQL Server, http://msdn2.microsoft.com/en-us/library/Aa224554(SQL.80).aspx, the Microsoft® SQL Server™ 2000 workload governor (also known as the throttle) is designed to limit the performance of an instance - an instance of the SQL Server 2000 database engine is one copy of the database softwarethat operates as an operating system service of the database engine. The cumulative number of logical reads and writes of data pages by all the active connections determines how much the database engine will slow down. The workload governor starts slowing down the database engine when more than eight operations are actively running at the same time. The application event log for SQL Server 3629 messages or the DBCC CONCURRENCYVIOLATION statement indicate how often the workload governor is activated. For information about how to use the DBCC CONCURRENCYVIOLATION statement, refer to the MSDN article at http://msdn2.microsoft.com/en-us/library/aa258279(SQL.80).aspx.

Configuring the ARM Database in SQL Server

The steps below describe how to configure the ARM database in SQL 2005. The process is similar for earlier versions of SQL Server.

To use SQL Server as EFT Server's Auditing Database

  1. Install EFT Server without the MSDE component, unless you want to perform auditing on the local system for testing purposes.

  2. Point EFT Server to the SQL Server of your choice.

SQL Server configuration

  1. On SQL Server, launch Microsoft SQL Server Management Studio or equivalent, and provide your administrator login credentials when prompted.

  2. In the left pane, expand the Security node, right-click Logins, then click New Login. The New Login dialog box appears.

  3. Create a new user called eftuser and click SQL server Authentication.

    		4.

    If SQL server Authentication is not available as a choice, verify that the SQL Server has been set up to support mixed mode.

  4. In the Password and Confirm password boxes, provide a complex password consisting of an alphanumeric and symbol mix of at least 8 characters.

  5. Select the Enforce Password Policy check box.

  6. Leave the Default database as master.

  7. In Default language, click the down arrow to select your language, if other than English, then click OK.

  8. In the left pane, right-click Databases, then click New Database.

  9. In the dialog box that appears, name the database eftdb.

  10. In the Owner field, provide the login name you just created (eftuser).

  11. In the Database files table, change the Initial size value to 10 MB for the eftdb logical name (first row). Leave the eftdb_log row as is and click OK.

  12. In the left pane under Databases, click the newly created eftdb database, then on the Query menu, click New Query. A blank screen appears in the right pane in which you can type in a SQL query.

  13. Go to the topic called SQL Script, copy the script, then paste it into the Query text box. Make sure the query begins with the words if exists and ends with a parenthesis. Include everything between the sections labeled begin SQL query and end SQL query.

  14. To run the query you just entered, click Execute on the toolbar. A message appears indicating whether the query was able to complete successfully.

  15. Expand Databases, then eftdb, then Tables. Verify that the database has populated correctly. (The tables defined in the script should have been created.)

Test your connection

  1. Create a test connection with your FTP client to EFT Server and upload and download a few files.

  2. Switch back to SQL Server and select the dbo.tbl_ProtocolCommands table under the eftdb database icon. It should return several rows with the commands issued by your client from the test connection.

  3. You can now pull reports directly from EFT Server against data audited to SQL Server.

If you are running EFT Administrator, you must have an entry in that system's DNS for the name of the SQL (or MSDE) server, otherwise EFT Administrator will not be able to connect to the SQL Server when attempting to pull reports.