Part IV: Post-Installation
Chapter 10: Post-Installation Steps on the Server
10.6 Setup Full-Text Searching on MS SQL Server
To emulate the Progress database engine on Microsoft SQL Server, the need for keyword searches on certain fields is required.
However, SQL Server does not allow the same, less featured, quicker searches that Progress natively allows. In order to fulfill this need, you are required to use the more fully featured and resource-intensive Microsoft Search Service.
The Microsoft Search Service uses Full-Text Catalogs to perform word searches on specific fields in the database. The Full-Text Catalogs are separate files of proprietary data structures, established for the purpose of evaluating
Contains and FreeText queries.
These files are not automatically updated along with the rest of the database. Full-Text Catalogs must be refreshed on a periodic basis for them to reflect current data. The following information reviews the approach to keeping these Catalogs up-to-date while minimizing the impact of the refresh process on the overall server performance.
10.6.1 Review the Process - Building Full-Text Catalogs
Populating Full-Text Catalogs requires a great deal of processing power and substantial writes to the Disk subsystem on the server.
Due to this, you benefit greatly from moving the catalogs to separate disks to avoid contention for the disk when writing the catalogs. More than one Catalog being built onto a disk results in Catalogs competing for space. The server's resources will dictate how well you can avoid this bottleneck.
Another variable in the creation of the Catalogs is the Noise files used. Noise files contain any words or characters to be skipped in the creation of the Catalogs. Since our description fields largely do not contain actual text, the noise files are blank since this allows searching on most everything. The noise file feature is targeted more at the evaluation of text where there are many filler words that are unimportant for searching.
SQL Server allows two Refresh options: Full Population which refreshes the entire catalog from the metadata in the database; and Incremental Population which refreshes only the parts that have been changed. Results vary based on the placement of the catalogs on the disks, server resources, and the number of changes. However, Incremental populations take on average about one-third the time of Full populations.
Since populating (refreshing) the Catalogs is done on a regular basis and since the process can be intensive, optimizing performance is an important part of optimizing the overall database server.Starting the MS SQL Search Service
10.6.2 Starting the MS SQL Search Service
1. To verify that MS Search Service is running on the server, select Start > Control Panel > Administration Tools > Services.
2. Confirm that the following Services are set:
• SQLServer > Started > Automatic • SQLServerAgent > Started > Automatic
• SQLServer FullText Search > Started > Automatic
For any services that are not started, start the service and change the startup configuration if needed.
3. You may need to register a new SQL Server. To do this, open SQL Server Management Studio. Register
the server by selecting SQL Server Group. Right-click and choose New SQL Server Registration. Follow the prompts to set this up.
10.6.3 Enabling Full-Text Search for Your Database
After starting the MS Search Service, you need to enable the full-text searching on the specific database.
1. In SQL Server Management Studio, right-click on the database that you want to enable full text searching
and select Properties.
2. In the Select a page section on the left side, select the Files option. Select the Use full-text indexing
check box. Click OK.
10.6.4 Defining the Catalogs
After enabling full-text search on the database, you need to you need to enable the full-text searching on the specific database, tables, and columns. To do this, you need to define the Catalogs.
The Catalogs are separate files, so you need to determine where to place the files. For performance reasons, locating the Catalogs on separate drives avoids contention for disk resources during the regular populations of the Catalogs. Consider this, along with the times that you will run the jobs. If you choose to change the locations, you need to perform step 6 in the following steps to drop the catalogs then you can re-edit the scripts and jobs to reflect the new strategy.
Note The MSSQL Full Text scripts are available in the following folder:C:\:Program Files\Epicor Software\Epicor905\server\sql.
1. At the server, use Windows Explorer to navigate to the batch file CatalogDirCreate.bat. This file is located
in C:\Program Files\Epicor Software\Epicor905\server\sql. All SQL scripts are located in this path.
Note By default, the CatalogDirCreate.bat file is configured to use Drive E:. If you want to change
the location from E:, right-click on the file, and edit it in a text editor to specify a different drive. For example, on the first line of execution code, change the E: to D: to specify the D: drive. Save and close the file.
2. Double-click to run the file. This batch file places the Catalogs on the E: drive and in the single folder called FullTextCatalogs. If you prefer to place some Catalogs on a different drive to improve performance, edit
this batch file or create the folders manually.
3. Open Query Analyzer and connect to the MSSQL Server running the Epicor905 database. Open the
following script file: FullTextCatalogCreate.sql.
4. Review each catalog create statement for each Catalog to verify the drive path. For example, in the custxprt
catalog, the create statement looks similar to the following:
sp_fulltext_catalog
'custxprt', 'create','e:\fulltextcatalogs\custxprt'
The above statement locates the Catalog on the E: drive in the fulltextcatalogs\custxprt folder. If you changed the drive path in the CatalogDirCreate.bat file, you must edit this statement to reflect the physical path on the server to the folders created when the bat file was run. Catalogs can only be created on hard drives on the server, not on tapes or network drives.
After editing the FullTextCatalogCreate.sql script, save the changes and keep for future needs.
5. Run the script from the Query Analyzer, the results window should read Query batch completed at the
bottom of the window when completed. Confirm that the catalogs are created by opening SQL Server Management Studio then your server, Databases. Choose the Epicor905 Database, then drill down to
Full-Text Catalogs. All 32 Catalogs should appear in this list. You may need to refresh the display or open
and close SQL Server Management Studio to get it to reflect the new Catalogs.
6. This is an optional step to change location. To drop the existing metadata and Catalogs, run the
FullTextCatalogDrop.sql script in the Query Analyzer. After this step, you will need to perform the above
steps in the previous Starting the MS Search Service section again to re-setup the Catalogs.
10.6.5 Changing Database Schema
In order to run the Incremental population, the tables with the column used in a Full-Text population must contain a "timestamp' column.
The SQL Server can use this column to determine whether columns have been changed since the last Refresh.
1. Back up your MSSQL database before proceeding.
2. In the Query Analyzer, from the File menu, select Open > File. Select and run the
FullTextSchemaChange.sql script. This script adds the Timestamp field to each of the tables to allow
MSSQL Server to know whether a record has changed since the last population.
Note If the timestamp column already exists in a table, due to a previous script run, the message
Column name in each table must be unique is displayed in the Results pane. This message lets you know that the timestamp field has already been successfully added to the table.
10.6.6 Initial Population of Catalogs
At this point, the MSSQL Server database is prepared and the Catalogs are created. You now need to add data. You do this by initially fully populating them.
1. Return to the Query Analyzer. From the File menu, select Open > File. Select and run the
to several hours depending on the size of your database and the speed of your server. This step needs to be run only once.
When running the this initial population of the catalogs, you can use the Windows Task Manager to help you determine when the populations have completed. To do this, open the Windows Task Manager and click on the Performance tab. Review your CPU usage. Once your CPU usage returns to a less utilitized status, then the Catalog Initial Population should be complete.
2. To test that the Catalogs have been populated, log onto the Epicor application. Check that the keyword
searches function is based on the data currently in your database. The preferred testing method is to use the Where Part Description Contains field for the Part Search component.
If your search returns appropriate data, then your Full-Text configuration is complete.
10.6.7 Verifying that the MSSQL Server Agent is Running
The MSSQL Server Agent is an optional service that must be started and set to Auto-start with the MSSQL Server engine service.
1. Verify that in the above section Starting the MS Search Service you confirmed that certain services were
running and configured, including MSSQLServerAgent.
2. Navigate to Start > Control Panel > Administration Tools > Services. Verify the following services are
started:
• MSSQL Server • MSSQL Server Agent
3. Close the Services program.
10.6.8 Setting Up the Catalogs to Update Incrementally
yAfter you have finished the initial population of your full-text catalogs, you may want to set your catalogs to be updated incrementally so they reflect the most current information.
Each Full-Text enabled column has been split into its own separate Catalog to allow the flexibility of being run independently. Certain Catalogs will not be changed often enough to warrant running the refresh jobs on a daily basis (such as langorg). Use the following steps to configure an incremental schedule.
1. Log into Microsoft SQL Server Management Studio. Expand the SQL Server Agent. 2. Right-click the Jobs folder and select New Job.
3. On the General page, enter a name for the schedule. For the category, select Full-Text. Enter a brief
description of the schedule task.
4. Select the Steps page. Click the New button. Enter the name for the step. Verify that Transact-SQL script (TSQL) type is selected. Select the database on which you want this job to run. Click Open.
5. Select the FullTextIncremental.sql script. Click Open. You should see the contents of the script displayed
in the window. Verify that the USE statement contains the name of your database. Click OK.
7. Enter the desired days and times you want the schedule to run. Click OK. Click OK on the Job Properties
window.
You have now configured your catalogs to be incremented automatically on a schedule.
10.6.9 Testing Server Impact for each Job
After building the full-text catalogs and setting up the schedule, each Administrator should review the overall impact of the process and determine a strategy to balance timeliness and server performance.
To do this:
1. At the server, go to Start > Control Panel > Administrative Tools and open the Performance program.
Right-click on the Counter column and select Add Counters. For Performance Object: Processor, add %
Processor Time. For Performance Object: Memory, add Pages/sec. 2. Run each job individually.
3. In the Performance monitor, review the Processor and Memory values and verify that they are low enough
to see the effect of the populations, such as Processor Time: 25% or under and Pages/sec: 10 or under. Note that this process may need to be the only activity running against the server in order to view the real effect.
4. You can also confirm the current status of the Catalog using Enterprise Manager > Databases > Manufacturing > Full-Text Catalogs. The listing of the Catalogs with their Status and Last Population are
included. Note that this screen does not refresh easily.
You can also run sp_help_Fulltext_Catalogs in the Query Analyzer to determine the current status. See the BOL (SQL Server books online) for detail.