SQL Server and the CML

Microsoft® SQL Server™ is the transactional database for the Corporate Media Library (CML) application. Library information is stored in tables, data integrity is maintained by triggers and referential integrity, and business logic is executed by stored procedures.

Key Features and Technologies of SQL Server

• Tables where the all the Fitch & Mather library data is stored.
• Indexes that provide quick access to CML data and can enforce uniqueness on the rows in a table.
• Triggers. Though the CML database does not make extensive use of them, triggers remain one of the strongest preservers of data integrity. The Triggers topic describes the use of CML triggers. SQL Server allows multiple triggers per table change and direct recursion of triggers.
• Stored procedures. The CML application makes extensive use of stored procedures. For example, xp_sendmail is an extended stored procedure that sends overdue notices to specified borrowers of library materials.
• Full-text catalogs enable full-text search. SQL Server provides the ability to make textual queries on data in the SQL Server database and other file systems. The full-text search of the CML application only accesses the SQL Server database. The search could be extended to documents stored in Microsoft Exchange Server public folders, Microsoft Excel spreadsheets, Microsoft Word documents, and so on.
• Performance. Design of the database and features of SQL Server 7.0 contribute to the performance goals of the CML application.
• Online operations deliver continuous availability of the CML application.
• Manageability. One requirement of the CML application is low cost of management. SQL Server 7.0 supports auto-configuration and self-tuning. An example is dynamic space management, which allows a database to automatically grow and shrink within configurable limits, minimizing the need for administrator intervention.
• Row-level locking improves concurrency without tuning. Concurrency is not a major issue for the CML development team. The predominant data access is read-only because CML borrowers search library materials and display results in the browser. However, borrowers can add and delete requests to check out library titles, so concurrency of transactions on the request table is a concern. See the CML Data-Services Tier section for discussion of table, index, and query design.
• Scalability. SQL Server 7.0 scales from very small to very large databases. The CML database is not huge (the expected row count in the largest tables is in the range of 15,000 to 20,000). The expected number of concurrent users of the CML application is 40 to 50.