Implementing Site Server Search Database Catalogs

Editor's Note: Each month, this column discusses various aspects of the advanced administration of e-commerce sites. This month's column examines Microsoft Site Server ODBC Database Catalog Search, which lets you build search catalogs by crawling through ODBC databases. You can use the resulting search catalogs to search databases.

In last month's issue, I showed you how you can use Site Server Search to easily implement a content search on your Web site. If you're familiar with Site Server 3.0, you know that the product sometimes delivers power at the expense of easy-to-use technology. Many features this enormous product contains are unleashed only when you write code.

This month, I show you an easy and powerful technology—Site Server ODBC Database Catalog Search. With this tool, you can build search catalogs to let your site visitors search for information in your Microsoft Access or Microsoft SQL Server databases. Site Server displays the results of successful database searches on a search results page, just like a content search does. (For information about content searching, see "Implementing Site Server Search on Your E-Commerce Site," June 2000.) From this search results page, when a site visitor clicks a link, a page appears with information from the database record. ODBC Database Catalog Search is wizard driven and easy to configure and implement.

You can use either wizards or custom configuration to create Site Server Search index catalogs to search your database (in this article, I discuss only the wizard method). Creating the Active Server Pages (ASP) to perform the searches is also automatic and completely wizard driven. Behind the scenes of the ASP, a Site Server Search Catalog retrieves a reference to what the user is searching for, and ActiveX Data Objects (ADO) uses that reference to retrieve the search results.

You can create the Search Catalog only from the Site Server Service Admin (HTML) tool in Site Server 3.0: The feature isn't available in the Site Server Service Admin (Microsoft Management Console—MMC) tool. In the articles that I've written for IIS Administrator about Site Server 3.0 technologies, I've shown you how to implement administrative technologies in both Admin tools. Having functionality in one administrative tool and not in the other isn't uncommon in Site Server. The Site Server Service Admin (HTML) tool contains functionality that the Site Server Service Admin (MMC) tool doesn't have, and vice versa. This inconsistency in functionality can create serious confusion when you're looking for the tool that you need.

So, you can create a Site Server database search only from the Site Server Service Admin (HTML) tool. When you've created your database search, you can manage the search catalog from either administrative tool. Creating a Site Server 3.0 database search is simple. The Catalog Definition Wizard facilitates the entire process.

Creating a Database Search Catalog and Search Pages
Let's say you want to create a search of the products table for the database that you use on your e-commerce site. First, access Site Server Service Admin (HTML) by choosing Start, Programs, Microsoft Site Server, Administration, Site Server Service Admin (HTML) on your Site Server machine. Click Search on the Site Server Service Admin (HTML) home page, which Figure 1, page 9, shows, to access the Web-based Administration for Site Server 3.0 Search home page, which Figure 2 shows.

Click Catalog Build Definitions from the left pane of this page to access the Catalog Build Definitions page, which Figure 3 shows. The Catalog Build Server creates and maintains the index catalogs, which keep references to all the resources (including databases) on your Web site that you choose to be available to Site Server Search. So, in addition to this searchable database, you need to create a System ODBC data source to your Access or SQL Server database.

Click Create at the bottom of the Catalog Build Definitions page to start the wizard that creates a new database search catalog. Under Specify the source of the documents you will catalog on the page that appears, click A Database, then click Next. When the wizard prompts you to specify a Catalog Name, give your search catalog the name of the table you're searching or a description of the data it contains (e.g., eCommerceProducts). Site Server Search restricts catalog names to 39 alphanumeric characters with no spaces. Enter appropriate information for your database and catalog, as you see in Figure 4, then click Next. Specify the table in the database that you want to be searchable, and click Next.

On the next page, choose your Content, Primary Key, and Hyperlink columns. The Content column is the main column containing text in your database records—it's the column that visitors search by default when they perform search queries. This column is equivalent to the body of a text document; in this example, the Content column is the description field of the product being sold on the e-commerce site. Site Server uses this column to generate the description or summary of the database record. Click Next.

Now, you must specify whether the columns you just entered are searchable or retrievable, as Figure 5 shows. If you mark a column as searchable, visitors can search its value after Site Server has indexed it. If you mark a column as retrievable, site visitors can retrieve and display its value in search scripts. To save space and improve search performance, mark as retrievable only those columns that you need to be retrievable.

In this example, let's say you've chosen to include additional search functionality on the column sku. You've also chosen to retrieve the columns list_price and quantity_in_stock and make them available to the ASP that displays the results of a search. You've chosen to retrieve the three columns relating to a sale (i.e., sale_price, sale_start, and sale_end). You can add ASP code later to conditionally display and use sale prices, if applicable. Finally, you've chosen to retrieve the image_file, image_width, and image_height columns to the ASP that displays the results. Later, you can prevent these fields from appearing on the page but use the values with an HTML Image tag to display a picture of the product. Click Next. On the page that appears, select the Build the database catalog now check box. Click Finish to start the build of the database catalog.

The Create New Catalog Wizard creates a catalog (e.g., eCommerce-Products) with a custom schema definition. The wizard also creates the database-indexing ASP files dir.asp and row.asp in microsoft site serversiteserverknowledgesearchdatabaseindexecommerceproducts. Finally, it creates the database-search files search.htm, results.asp, and view.asp in microsoft site serversiteserverknowledgesearchdatabasesearchecommerceproducts.

Executing the Search
Everybody with access to the Web site can execute the ASP that Site Server uses to index and display database content. If you want to control access to database content, use Internet Service Manager (ISM) to change the security on the directories in the /siteserver/knowledge/search/database virtual root. End users can access the search page by clicking the http://servername/siteserver/knowledge/search/database/search/ecommerceproducts/search.htm hyperlink (where servername is the name of the server on which the application resides), which appears on the Create New Catalog Definition Wizard summary page that Figure 6 shows. Click this hyperlink to access the Database Search page and search the database table. In this example, the fictional e-commerce site sells Internet Server API (ISAPI) filters, so search on the word ISAPI and click Search. At the bottom of the Database Search page, which Figure 7 shows, you can also use a slightly different syntax to search on the other fields that you've cataloged as searchable. In this example, you've also chosen to index on SKU, so to search on SKU, type @sku 01-1016.

The ISAPI search yields three results, as Figure 8 shows. You can choose one of the search results by clicking its hyperlink. Click ISAPI Filter for Web Security (component), which is a summary in HTML table format of the database record. The resulting page, which Figure 9 shows, isn't attractive and needs an Add to basket button, but Site Server Search has performed 95 percent of the work—the hard stuff—automatically. (See the Site Server 3.0 Search documentation for tips about customizing the search scripts or moving the ASP into your site.)

Keeping the Search Catalog Up-to-Date
Now that you've created the Search pages, you can manage the database catalog in either Site Server Service Admin (HTML) or Site Server Service Admin (MMC). Like much of the content on your Web site, Search catalogs are static. This concept is hard to envision, but although the content in the database you're searching can be dynamic, the search catalog you just built against the database is static. As the contents in your database change, you have to refresh the Database Search catalogs to keep them up-to-date. Fortunately, you can easily schedule an automated refresh process from Site Server Service Admin (MMC). You can't schedule the builds of the Database Search catalogs in Site Server Service Admin (HTML). Database catalogs don't support incremental builds, which refresh only those files that have changed in your content Search catalog: If you schedule an incremental build of your Database Search catalog, a full catalog build runs anyway. Full builds can be burdensome to your server and take a long time to complete, depending on the amount of data cataloged and the speed of your server.

To access Site Server Service Admin (MMC), choose Start, Programs, Microsoft Site Server, Administration, Site Server Service Admin (MMC). Navigate to the Search catalog that you just built. Right-click the Search catalog, then select Properties. Click the Schedule Builds tab. Select the Incremental builds check box to access the Incremental Builds Properties dialog box. On the Schedule tab, choose the frequency and start time of the scheduled incremental builds. Obviously, databases that you frequently modify require more frequent incremental builds of the Search catalog than databases that rarely change. I usually schedule the Search catalog builds at 2:00 a.m., with a frequency of every night. You'll most likely want to time the length of your builds (which the MMC facilitates in a simple, manual process) to make sure that they start and finish when usage is lowest on your Web site. When you've configured the time and frequency of the incremental builds of your Search catalog, Windows automatically creates a task to do the work. Click OK when you're finished, and Windows will automatically schedule your catalog builds.

Database Search vs. ADO or T-SQL
Now you've seen how easy it is to create a powerful database search application and host it on your Web site. With this knowledge and the foundation that last month's article gave you, you can think about bigger and better search applications. You can create complex Search catalogs hosted in powerful Knowledge Management (KM) applications that include content not only from your file systems and Web sites but also from other Web sites on the Internet, from Microsoft Exchange Server public folders, and now from databases.

Developers might think, "Why wouldn't you just do this in T-SQL or ADO?" You could, of course, and you might choose to. But the power of this type of database search is that it's

Although the ASP that Site Server ODBC Database Catalog Search generates eventually uses ADO to retrieve records from the database, you avoid complex where clauses that T-SQL would have to search within table columns; the search catalog itself handles the where clauses. These complex where clauses in T-SQL are costly in processing time: That's why a Search Catalog would most likely be faster in most cases (depending on hardware, amount of data, and so on). The main weakness of the Site Server database search is that you're limited to searching only one table in the database. However, the Site Server online documentation covers many tricks for overcoming this drawback.