& Database Engine Essay, Research Paper
Introduction:
The purpose of this paper is to Define Microsoft Database and Jet Engine, explore its uses, and to a better understanding of the theories and principals related.
“Jet is the default database engine for Visual Basic as well as other Microsoft applications such as Microsoft Access, Microsoft Excel, Project, and Microsoft Foundation Classes (MFC). ” (Microsoft HOWTO: Identify, 1)
Microsoft Access and the Microsoft Jet Database Engine:
“Together, Microsoft Access and the Microsoft Jet database engine form a complete database management system (DBMS). Microsoft Access is responsible for the user interface and all the ways that users view, edit, and manipulate data through forms, queries, reports, and so forth. Microsoft Jet ? the data manager component of the DBMS ? retrieves data from and stores data in user and system databases.” (Microsoft, Access Architecture)
Microsoft Jet is a relational database engine that handles all database processing for Access. Microsoft Jet can also provide data to ODBC (Open Data Base Connectivity) client applications. “(Microsoft, Access Architecture, 23)
Microsoft Jet is made up of a set of dynamic-link libraries (DLLs):
*sum* Microsoft Jet DLL (Msjet35.dll)
“Msjet35.dll is the main program that evaluates and carries out requests for data. If the request is for native data ? data stored in the Microsoft Access Database (MDB) format ? Msjet35.dll also handles the reading and writing of the data. If the request involves non-native data, then Msjet35.dll makes calls to either the ODBC Driver Manager DLL (Odbc32.dll) or one of the external installable ISAM DLLs, as explained later in this section. ” (Microsoft, Access Architecture, 23)
*sum* Data Access Objects DLL (Dao350.dll)
“Dao350.dll is the Microsoft Jet component that provides a developer interface to Microsoft Jet. Data Access Objects (DAO) includes a rich, high-level set of objects that insulates developers from the physical details of reading and writing records. “(Microsoft, Access Architecture, 23)
*sum* Installable ISAM DLLs
“Microsoft Jet provides access to several external Indexed Sequential Access Method (ISAM) format files by using a series of installable DLL files referred to as installable ISAMs. Microsoft Jet supports the external ISAM formats shown in the following table.” (Microsoft, Access Architecture, 23)
ISAM format Supported by DLL
Xbase (dBASE and FoxPro) Msxbse35.dll
Paradox Mspdox35.dll
Lotus Msltus35.dll
Microsoft Excel Msexcl35.dll
Microsoft Exchange/Outlook Msexch35.dll
Text and HTML Mstext35.dll
“These DLLs handle the reading and writing of data stored in dBASE, FoxPro, Paradox, Lotus, Microsoft Excel, Microsoft Exchange, Microsoft Outlook, fixed-width text, delimited text, and HTML files.” (Microsoft, Access Architecture, 24)
Jet and Replication Objects:
“Microsoft Jet and Replication objects (JRO) allow you to add features to your application that are specific to the Microsoft Jet Database Engine. The Jet and Replication objects are based fundamentally on Microsoft Active Data Objects (ADO), because they are connected to an ADO Connection object. However, the Jet and Replication objects function only with Microsoft Jet databases.
With the Jet and Replication objects you can:” (Microsoft JRO, 1)
*sum* Create and synchronize database replica sets.
*sum* Compact a database, and specify options for the compacted database, such as passwords and encryption.
*sum* Refresh the memory cache by writing pending data changes to the database, and reading the most recent data from the database to memory.
The Jet Engine
“The Jet database engine is represented by Jet Engine objects. With Jet Engine objects, you can make a compacted copy of a Jet database and include options for encrypting data, changing database engine versions, passing user IDs and passwords, specifying the system database or registry information, setting or overriding locale-specific collating orders, removing relationships, and repairing damaged replicas. You can also force any pending changes to the data to be processed by completely flushing the cache of database operations.” (Microsoft JRO, 2)
Replicas
“Replicated databases are represented by Replica objects. With Replica objects, you create new replicated databases, define the options for a specific replica (full, partial, design master), synchronize the changes between two replicas, and determine conflicts that occur during synchronization.” (Microsoft JRO, 2)
Filters
“Partial replicas are created using Filter objects, which are collected in the Filter collection of a Replica object. Filter objects define criteria that limit the records that become replicated. Filters can be applied to tables or relationships in a partial replica.” (Microsoft JRO, 2)
Jet in WINS and DHCP:
“Microsoft Windows NT Server includes a utility, Jetpack.exe, that can be used to compact a Windows Internet Name Service (WINS) or Dynamic Host Configuration Protocol (DHCP) database. Microsoft recommends that you compact the WINS database whenever it approaches 30 MB. NOTE: Chapter 5 page 133 of the Microsoft Windows NT Server versions 3.5 and 3.51 TCP/IP manuals states that Compact.exe can be executed to compact the WINS database. This is not correct. The correct file name is Jetpack.exe.” (Microsoft How to Use, 1)
WINS and DHCP Preventive Maintenance:
“Take steps now to keep your NT 4.0 network running smoothly
If you are or ever were a user (i.e. victim) of Windows NT 3.5x’s Windows Internet Naming Service (WINS) and Dynamic Host Configuration Protocol (DHCP), you know that regular maintenance tasks such as periodic jetpacking of the database and checking of log files and event logs are crucial to keeping these important infrastructure services functioning. Even if your firm uses the best networking practices, NT 3.5x’s WINS and DHCP quirks probably periodically cause you stress.” (Mar-Elia, Darren, 1)
Jet Files:
“WINS and DHCP rely on Microsoft’s Jet database engine to store information about their services. WINS stores the records that clients register and the records it replicates from other WINS servers in a Jet database, which is usually in %systemroot%\system32\wins. WINS servers keep important configuration options that relate to their replication partners and their name registration behavior in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WINS registry subkey.” (Mar-Elia, Darren, 1-2)
“DHCP uses a Jet database to keep track of which IP addresses the server has leased. DHCP relies heavily on the Registry for storage of scope definitions (the ranges of IP addresses DHCP can distribute) and reservations (the address assignments DHCP hard-codes using machines’ media access control?MAC?addresses). DHCP stores its scope and configuration information in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\ Services\DHCP-Server registry key. The DHCP database is in %systemroot%\system32\dhcp. WINS and DHCP servers hold several files that relate to each service’s database.” (Mar-Elia, Darren, 3)
“Wins.mdb or dhcp.mdb. The wins.mdb file is the main database file that holds records for WINS. Dhcp.mdb is the main database file that holds DHCP records.
J*.log. Within the DHCP or WINS directory, you’ll find at least two J*.log files, which are transaction files for the database. When you change a WINS or DHCP database, the software writes your change to the current log file. You can verify this fact by viewing the WINS or DHCP database directory. The directory’s files with the most recent modification dates are the log files; the database files reflect the date and time when the WINS or DHCP service last terminated gracefully (i.e. via a shutdown or termination of the service).” (Mar-Elia, Darren, 3)
When you first install these services, you’ll probably see a J50.log file. J50.log is the current transaction log for the database. Each log holds a maximum of 1MB. When the current log fills, the DHCP or WINS service copies it to a backup log. The software usually calls these backup logs J50xxxxx.log, in which xxxxx is a sequential hexadecimal number. DHCP usually generates more frequent log file backups than WINS generates, because DHCP generates a new series of logs (and backups) every time it backs up the DHCP database. By default, DHCP backs up its database hourly. (Mar-Elia, Darren, 3)
J*.chk. You use J*.chk to checkpoint the log (i.e., to keep track of which J*.log transactions DHCP or WINS has written to the database). J*.chk is useful when the database needs to back out of a set of transactions during a recovery, because the file keeps track of which transactions the software has committed since it last wrote to the database. (Mar-Elia, Darren, 4)
Care of the Jet Databases:
“DHCP and WINS Jet databases can be sources of much grief for NT administrators. As DHCP and WINS databases grow, they become less efficient at servicing requests and more susceptible to corruption. If a database becomes corrupted, the WINS or DHCP server simply stops servicing requests. You can usually find evidence of database corruption within NT’s system event log, which you use Event Viewer to view. When a crucial event log error suggests that your database is corrupted, your choices for recovery are limited and probably painful.
WINS is especially problematic, because it is very active in medium to large environments. During business hours, WINS servers are always working with the database. If the servers aren’t registering or renewing client computers’ name registrations, they’re replying to name queries. And every time users log on to a machine that uses WINS, the system registers their username with that machine in WINS. In addition, large NT infrastructures might contain several WINS servers that replicate their databases to one another. As a result of all this activity, WINS databases are subject to fragmentation. As WINS writes and rewrites records to the database, wins.mdb often becomes fragmented and actually grows in size as a result of the fragmentation. Over time, the database can become quite large if you don’t care for it properly.” (Mar-Elia, Darren, 5)
DAO/Jet
DAO is the COM interface to the Jet database engine and with DAO 3.5, an interface to RDO 2.0. Basically, DAO/Jet is primarily designed to access ISAM (The Indexed Sequential Access Method ) databases as it supports most common ISAM data access techniques. Therefore DAO/Jet is the solution when it comes to accessing native Jet (.MDB) or ISAM data sources such as Btrieve, FoxPro, Paradox, and dBase. (Microsoft, Choosing, 7)
“Using the Jet API from Visual Basic is uncalled for (so to speak) as the DAO interface is far, far easier to use and much better suited for Visual Basic development. However, when working with remote data sources, the Jet database engine consumes more resources than the API interfaces or RDO. While DAO/Jet is capable of accessing ODBC data sources, it has limited stored procedure management and multiple result set functionality. It cannot build queries using server-side cursors or leverage much of the potential of these remote DBMS systems.” (Microsoft, Choosing, 7)
ISAM
“To use Data Access Objects (DAO) methods and objects with Visual Basic for Applications in Access to link or import data from Exchange or the Outlook desktop information manager, you must have the Microsoft Jet Exchange installable indexed sequential access method (ISAM) (Msexch35.dll) installed. The Jet Exchange installable ISAM is a driver that allows you to access data that is stored remotely on a Microsoft Exchange Server or that is stored locally in offline folder (.ost), personal folder (.pst), or personal address book (.pab) files.
Note: If you have set up Outlook to use an offline folder file, the offline folder file is a file stored on your hard disk that “mirrors” the folder structure and data stored on your Microsoft Exchange Server. Data from your Microsoft Exchange Server is copied into the offline folder file whenever you synchronize folders (manually or at a scheduled time). When you start Outlook, it gives you the option to connect to your server or work offline. If you choose to connect, you are viewing data directly from the server; if you choose to work offline, you are viewing data from the offline folder.” (Microsoft Accessing, 1)
“For this reason, how you are logged on to Outlook determines whether the Microsoft Jet Exchange installable ISAM reads data from the offline folder file or from the Microsoft Exchange Server. For example, if you initially define a link to your Inbox folder while you are working offline in Outlook, the Microsoft Jet Exchange installable ISAM reads the data from your offline folder file. However, if you open the same database later while Outlook is connected to your Microsoft Exchange Server, the data will be read from the Inbox folder on the server.
Using the Microsoft Jet Exchange installable ISAM to access Exchange and Outlook data is primarily useful for reading information from message folders, public folders, address books, and other Outlook items. You can’t modify existing items. And, though it is possible to write new items to Exchange and Outlook message stores, not all fields are available or up datable, which limits your ability to create some items. If you need more flexibility when working with Outlook objects from Microsoft Access, you should instead use automation interfaces to work with Outlook.” (Microsoft Accessing, 1-2)
Installing the Microsoft Jet Exchange Installable ISAM Driver
Before you can use DAO to gain access to Microsoft Exchange Client or Outlook data, you must install the Microsoft Jet Exchange installable ISAM. Although you can install the Microsoft Jet Exchange installable ISAM using the Dataacc.exe file that is included in the Microsoft Office 97 Value Pack, that version of the driver does not register itself and has certain limitations. To install the most current version of the driver, download and run Wzmapi80.exe to install the Microsoft Exchange and Outlook Wizard. This installs and registers the most current version of Msexch35.dll so you can use it from DAO, and also installs the Microsoft Exchange and Outlook Wizard so you can import and link Microsoft Exchange and Outlook data using the Microsoft Access user interface.
Note: You must have a copy of Microsoft Access 97 installed before you can install the Microsoft Exchange and Outlook Wizard. The Setup program does not provide a way to install just the Microsoft Jet Exchange driver; you must install the wizard components as well. (Microsoft Accessing, 2)
Installing MSDE
“Microsoft Database Engine (MSDE) doesn’t install with the basic setup for Office 2000 and Access 2000. However, you can install MSDE from Office 2000, CD 1. Run setupsql.exe from the \sql\x86\setup folder. Note that you can’t run SQL Server and MSDE on the same computer. MSDE operates as a database server for a local workgroup in a peer-to-peer network or on a Windows NT computer. On Windows NT computers, MSDE supports either Windows NT integrated security or SQL Server security. Applications for MSDE on a Windows 9x computer exclusively support SQL Server security.” (Dobson, 1-2)
“If you install MSDE on a Windows 9x computer and attempt to link to it from a Windows NT computer, your users might experience a network failure. One cause of this failure is that the NT computer attempts to link to the server from Named Pipes, whereas the MSDE installation procedure for a Windows 9x computer installs multiprotocol and TCP/IP settings. To fix this problem, use the Client Network Utility on the NT computer to revise network client settings to match those on the Windows 9x computer running MSDE.” (Dobson, 2)
“A valid reason to install MSDE even when you use Access projects exclusively with MS-SQL Servers is to fix a known problem in the operation of the Tools, Security, Database Security command. This command enables login and user ID creation and permission and role assignments from a GUI that mimics Enterprise Manager’s Action, New Login command. Unfortunately, MSDE won’t operate from Access 2000 unless the workstation that MSDE is running on has MSDE installed on it. Therefore, to set security, even for SQL Server databases from Access projects, you need to install MSDE.” (Dobson, 2)
“Although MSDE has a database technology similar to SQL Server 7.0, it has several distinct limitations. First, Microsoft optimized MSDE for a handful of users. Microsoft asserts that performance starts to degrade after five users. You’ll want to check your MSDE applications for performance in real situations to assess whether any performance degradation is within acceptable limits for your needs. Second, the maximum size of an MSDE database is 2GB, instead of the essentially unlimited size of a SQL Server database. Third, SQL Server has many features that Access doesn’t have. For example, a MSDE database can’t serve as a replication publisher for transactional replication. And, MSDE can’t act as an OLAP or English Query server.” (Dobson, 3)
Access Security and Passwords
MS Access features three independent types of passwords:
*sum* Database passwords
“Database passwords were introduced in the times of MS Access 95 as a new, easy to use security feature. Setting up a single password that controls who can open a database is both faster and easier compared to setting up a complicated user-level security. If there’s a database password set, all users must enter that password to open the database.” (Robinson, 1)