Microsoft Data Engine MSDE

Data
Microsoft Data Engine MSDE

Overview
Developers who create applications face the choice of which technology should be selected among the many database options. There are a number of factors that developers need to consider before choosing a database technology. For example, how many users does the application need to support? Do all users have access to the database system at the same time? What costs are involved in the development of this application? If the requirements of the application change over time, does the technology allow expansion of the database's functionality and size?
Until recently, Microsoft has launched two types of database management scenarios: Microsoft Jet (the Microsoft Access data Engine, which is published with Access and Microsoft Visual Studio) and Microsoft SQL Server. Since each technology solution has its own advantages and some limitations, it is generally difficult to ask a developer to choose a database technology that meets the needs of both the desktop and the data center, while the database application adapts to both long-term and short-term requirements. Because the jet based solution has the advantage of ease of use and low cost of development, it is a good choice for desktops or shared applications. However, developers have discovered that as application systems scale up, they need to make a lot of changes to the program code before they migrate the jet application to the platform of SQL Server. It's worth the developer's pleasure: With the release of a SQL Server-compatible data engine-microsoft data Engine (MSDE), developers can choose a new way to create database applications based on desktops and shared environments. With MSDE, developers can create freely distributed desktops and shared database solutions. Database applications that use this solution can be easily ported to SQL Server when the size of the system needs to be expanded.
What technologies are involved in the data engine
In essence, a "data Engine" in the running state is a core process. A database management system, such as Access or SQL Server, will use this core process to store and manage the data in the database. In this sense, we can often think of a data engine as a database management system without corresponding graphical management tools. The data engine has two main functions: one for storing data and the other for handling requests submitted by other objects for stored information. We usually refer to requests for retrieving or updating data as database queries.
Typically, the data engine responds to a database request submitted by a client application, along with a custom database application. Custom database applications are created with some kind of development tool, such as Microsoft Visual Studio. In this case, since the custom database application already contains built-in tools to manage the underlying database, there is no need to include graphics tools to manage the database in the data engine. In addition, because the data engine is easily distributed, developers can include the appropriate data engine in a custom application, and the user can install it into the system by selecting the engine component during the installation of the application.
Microsoft Data Engine
Microsoft Data Engine (MSDE) is fully compatible with SQL Server and can be used to process databases in a desktop and shared database solution created by Visual Studio 6.0 or Access 2000 for development tools. For Visual Studio 6.0 or Access 2000 developers, they can publish database applications created using MSDE without registering MSDE separately. MSDE uses some of the techniques provided by SQL Server and is fully compatible with SQL Server 7.0. In Access 2000, MSDE is an ideal alternative to Jet (the default engine used by Access 2000 databases). In addition, for Visual Studio 6.0 developers, if they want to create custom database applications and want to use SQL-compatible databases in these custom applications, they can do so by using MSDE as the data engine. MSDE compatibility with SQL means that developers can migrate their existing database application code with MSDE as data engines, desktops, and shares into SQL Server without making any changes.
MSDE runs in the background in a way that provides a service. Here, the service refers to an application that runs in a background process. A service differs from the way other applications are run. For example, as long as the user chooses the "Start" option from the Start menu, the corresponding application can be put into operation. But for services, such as MSDE, this is not the case. Their startup and shutdown is usually done by the operating system. Desktop and shared database applications that use MSDE as the data engine often have the following limitations: The MSDE service should be running while the database application is alive. In general, the objects that use the service are other applications, not end users. Typically, a service runs in the background, and when it receives a processing request submitted to it by the client application, it goes to the appropriate request. For MSDE, these requests are some of the operations that will be performed on the database. Because the object that uses the service is the client application, not the end user, the service does not need a user interface. A project created in Access 2000 or a Visual Basic application can interact directly with a service. For MSDE, these client applications act as a user interface.
Features of MSDE:
* is fully compatible with Microsoft SQL Server 7.0 and previous versions.
* For Visual Studio 6.0 or Microsoft Office 2000 developers, they can publish MSDE-based database applications to end users free of charge.
* Can be run on Windows, Windows, Windows NT, and Windows on Intel and Alpha platforms.
* Has a client/server architecture (MSDE is operating on the database server, not on the client).
* Run in the background in a service-provided manner.
* During the development of the database, developers do not need to create the appropriate user interface for the engine. Other applications, such as custom applications developed with Access 2000 or Visual Basic, need to provide the appropriate user interface for the database.
Jet 4.0
Jet 4.0 is the default data engine that is used in Microsoft Access 2000. This engine is also included in the Visual Studio 6.0 Development Kit. Jet is a file-based database management system. This type of database management system has the following characteristics: The database in the system is contained in a separate database file, and the read and write operation of the database is controlled by the client. Jet can be used in a multiuser environment, and developers can expand database applications that use Jet as an engine into desktop and shared database applications. Microsoft Access provides an easy to use interface for the Jet data engine. Developers can also use Visual Basic or other development tools to create custom front-end applications that include a database with Jet as the engine.
SQL Server
SQL Server is a database management system based on client/server mode. Unlike the engines used in file-based databases, in a database management system based on client/server mode, the data engine is responsible for managing the read and write operations of the database. Because of this feature, database management systems based on client/server mode, such as SQL Server, can greatly improve the number of concurrent users and the amount of data that the system can handle. SQL Server is designed to meet this urgent need, as the number of concurrent users and the amount of data processed by a large database application used in the enterprise is increasing. These database applications include enterprise management systems and decision support systems that have been implemented today and need further improvement in the future. SQL Server has a variety of distributions to meet different requirements for the amount of data processed, concurrent users, and hardware in different environments.
database compatibility with SQL
Database compatibility with SQL refers to the extent to which the database is consistent with the ANSI Structured Query Language (ANSI-SQL) industry standard used for database communication. Both MSDE and SQL Server are compliant with the language specification defined in Transact-SQL (T-SQL), and Jet does not use this specification. In contrast, Jet defines and implements its own set of query languages. In this query language, Jet can invoke a Function procedure in a Visual Basic application (VBA). Transact-SQL is a well-defined set of languages through which developers can communicate between database applications and database servers and be able to program the database server. Transact-SQL is a superset of ANSI-SQL, with Transact-SQL, which enables developers to perform some custom transaction logic with the server programmatically. Users can do this by invoking the transaction logic, which is driven by events or messages, on the client, and when they are actually executed, the logical processes are done on the server. The consistency of MSDE with Transact-SQL language specifications means that objects in MSDE can run in SQL Server without modification. So, at this point, MSDE is superior to jet.
How to select a data engine
If the functionality and scale of your database application are no longer meeting the requirements of your database system, you may be prepared to create an application that is based on a powerful data engine, or you may want to migrate existing database applications by using a new data engine instead of the original engine. Whichever way you use it, it is important to have a certain understanding of the existing conditions and the characteristics of each data engine that you plan to use. So, what kind of data engine should you choose? If you want to create a desktop and shared database application, you might consider using MSDE instead of jet. In this section, we discuss the requirements that many database applications put on the system during development, and we also offer suggestions to tell developers how to choose the right data engine to meet the engineering requirements they will create to the fullest extent possible.
Requirements for database applications
Table 1 lists the requirements for database applications running in an enterprise environment, and describes the extent to which each data engine meets these requirements. If your application is currently (or will) have any requirements in the leftmost column of table 1, you should consider using MSDE instead of jet as the engine of the database.
Under what circumstances should you choose MSDE as the engine for your database system
In the following two cases, we can use MSDE as the engine for the database application. One is to use MSDE instead of jet to migrate existing database applications to improve its scalability. The second is to use MSDE as the engine to create desktops and shared database applications, which can be easily ported to SQL Server when the scale of the system expands.
compatibility with SQL Server for MSDE
The MSDE data Engine is fully compatible with SQL Server. Therefore, tables, stored procedures, triggers, and other database objects created in MSDE can be run in the SQL Server database without any modifications. However, objects created in jet, such as query objects, cannot be migrated to the SQL Server database without any modifications. If you want to migrate your currently created database applications to SQL Server, or if your existing database applications have already or will exceed the limits of the jet engine in some other way, you should select MSDE as your application's data engine. With this solution, you can easily migrate your database applications to SQL Server one day in the future without modifying any code.
Server-based processing
Unlike jet, MSDE is not a file-based data engine, so database applications that use MSDE as an engine are not limited by the amount of data processed by file-based databases. Because the operations on the database are performed on the client computer in a file-based database, this type of database affects the performance of the application. In server-based database systems, such as SQL Server and MSDE, the processing of requests submitted to client applications is done on the server, and after the request is processed, the server returns the corresponding result to the client application submitting the request. This approach greatly reduces the load on the network and enables users to access the database at an acceptable speed without a higher configuration terminal.
When multiple users use a file-based database at the same time, each user must open the same file to read and write information from. Since each user has only his turn to perform the appropriate operation on the database file, the file-based database becomes a bottleneck affecting system performance. The Server-based database management system is designed specifically to solve the problem that the client application encounters when accessing the database management system, so it can improve the performance of the system.
When client applications are distributed across different machines on the network, the way in which data is processed by the client makes the performance of the File-based database system further degraded. When a file-based shared database is placed on a file server in the network, if the client application submits a query request to the file server to perform the appropriate action on the shared database, even if the result of executing the request produces only one record, The file server also needs to pass all the source data contained in the request over the network to the client application submitting the request. In a server-based database, the data engine executes all processing requests submitted by the client application on the server, and only returns the processed results to the client after processing is completed. In this mode, the application will still be able to access the database on the server with better performance on a network connection with a narrower bandwidth.
If your database applications are distributed across the network, multiple users need to access your database at the same time, or users can access only through a narrower network connection, you should use MSDE as the engine for your database application as long as you have any of these.
Security
Although both MSDE and jet allow the necessary protection for separate database objects such as tables or stored procedures, MSDE databases running on Windows NT or Windows 2000 platforms can take advantage of the security mechanisms provided by Windows 2000 to protect database objects. However, the Jet database does not have this feature.
If you want to take advantage of this security mechanism provided in Windows 2000 in your database application, you should choose MSDE as your data engine.
Authorized
Any customer who has registered an Office 2000 Professional Edition or a learning version, or any user who has registered a tool component that is included in Visual Studio 6.0 Enterprise or Professional Edition, can get MSDE for free, including:
* Visual Studio 6.0, Professional Edition and Enterprise Edition
* Visual Basic 6.0, Professional Edition and Enterprise Edition
* Visual C + + (r) 6.0, Professional Edition and Enterprise Edition
* Visual InterDev (TM) 6.0 Professional Edition
* Visual J + + (r) 6.0, Professional Edition
* Visual FoxPro (r) 6.0 Professional Edition
For developers who use MSDE as the data engine to create desktop and shared database solutions, they are authorized to publish their MSDE solutions free of charge as long as they register the software below. The software products that need to be registered include:
* Microsoft Office 2000 Development Edition
* Related tool components in Microsoft Visual Studio 6.0 Professional Edition or Enterprise Edition (these tool components are listed earlier in this section)
For those developers who use Microsoft Office or Visual Studio as a development tool, they are also authorized to publish these databases for free if they include a database with Jet as their engine in their applications. However, desktop and shared database applications that use Jet as an engine are not easily ported to SQL Server. If development costs are an important factor in creating a database application, it is your best bet to use MSDE as the engine for your database.
MSDE Summary
If developers want to create desktops or shared database applications, and plan to expand the functionality and scale of these database applications in the future, using MSDE as a data engine will have the following advantages: In the near term, the development costs of the system can be reduced, and in the long run, also provides the most convenient way to migrate a database application that is currently created. When the size of your organization expands to provide new requirements for the functionality and performance of your database system, you can completely migrate it to a SQL Server based platform without modifying any of the code in your existing database application. In addition, because MSDE is fully compatible with SQL, developers can use the same data access language to seamlessly transform between relatively small MSDE database systems and larger SQL Server database systems.
Under what circumstances do you choose Jet
For some special database systems, it is also a good choice to use Jet 4.0 as the engine. In this section, we will discuss under what circumstances jet should be selected as the database engine.
Distribution of
In a database system that uses Jet as an engine, it is easy to back up and publish a database because the database is contained in a separate file. While the database system is running, other files access the Jet database files through applications such as visual Basic or Microsoft access, but because developers have a good definition of the dependencies between these files, Accessing database files does not take up a large amount of storage space on the system or use large amounts of processor resources. MSDE databases are more difficult to publish and install than the Jet database. Therefore, if you want to simplify the process of publishing and installing the database, it is a good choice to choose jet as the engine for the database.
Development costs
Developers can publish a database based on Jet 4.0 with a database application developed with the following tools:
* Microsoft Office 2000
* Microsoft Access 2000
* Related tool components in any version of Visual Studio 6.0 (these components are listed in the previous section of the article)
If development costs are an important factor to consider when creating a database application, it is a good choice to choose jet as the data engine.
Scalability
Both MSDE and Jet databases can be run in the desktop and in shared database applications. For some database applications, the amount of data they handle may never exceed 2GB, and the number of users supported will not exceed 20. If you have a Jet database application, and even as the system grows, it does not handle much of the data and needs to support enough users, then it is still a good choice to use jet as the engine of an existing database.
Requirements for system resources
Because the Jet database is compressed, it is appropriate to choose jet as the engine for the database on those platforms with limited resources. Of the three data engines discussed in this article, the database with Jet as engine consumes the least amount of storage space and system memory consumed. If you use a machine that has very limited disk space or system memory, it would be wise to choose jet as the engine.
Compatibility with previous versions
Jet 4.0 provides maximum compatibility for those databases that are based on previous versions. You can run multiple versions of the existing Jet database application in the same system. As long as you don't add SQL Server or MSDE to the database system, your existing system will still be a database system based on the jet/access solution. If you call a function defined in a previous version of Jet in one of your existing Jet database applications, or if you call a function that requires a lot of processing to convert the function to a SQL-compatible database, Using Jet as a database engine will be more advantageous than using MSDE or SQL Server 7.0.
Jet Summary
For those that are currently required to deal with a small amount of data, there are not many concurrent users to support, and in the future there will be no significant growth in the database management system, based on the Jet database system has low development costs, easy maintenance advantages, and with the previous version of the Jet database system has the greatest compatibility.
Under what circumstances should you choose
SQL Server
Looking at the title of this section, you might want to ask: Why not use MSDE or jet as the engine to develop all the database systems? The answer to this question is that the database system based on jet and even MSDE is in some cases unable to meet the actual needs. Below, we describe these situations.
The capacity of the database
A database based on MSDE can store up to 2GB of data size. Each file in a jet-based database can store no more than 2GB of data. Because Jet database files can increase storage capacity by linking to each other, the amount of data that is ultimately stored is limited by the size of the disk space. However, managing multiple data files will be a very difficult task. Therefore, if you have an existing database that is larger than 2GB, or will break through this limit in the future, choosing SQL Server as the engine for your database system will be the best choice.
Number of concurrent users supported
When the database system needs to support no more than 5 concurrent users, the engine with MSDE as the database will get the best system performance. Microsoft strongly recommends that the system support more than 5 concurrent users in order for the MSDE database system to perform superior performance.
From a technical and licensing point of view, the Jet based database system to support the number of users can reach 255, but in practice, the system supports the best number of users not more than 20. The reason for this is that the Jet database system uses a file-based approach to store data, so that as the number of connected users increases, so does the number of users who need to read and write to the file, resulting in poor system performance.
Because SQL Server can support thousands of concurrent users, it can meet the performance requirements of enterprise-class database systems to the greatest extent possible. Therefore, if a database application needs to support more than 5 users, choosing SQL Server would be the most sensible choice.
SQL Server Summary
If a user requires a separate database to process more than 2GB of data, and users require good performance when accessing the database system, SQL Server should be used as the engine for the database system. In all data engines, SQL Server delivers the most superior performance and the best scalability.
Explanation of MSDE License
Any user who has registered for Office 2000 Professional Edition or a learning version, or any user who has registered a tool component that is included in Visual Studio 6.0 Enterprise or Professional Edition, can get MSDE for free, including:
* Visual Studio 6.0, Professional Edition and Enterprise Edition
* Visual Basic 6.0, Professional Edition and Enterprise Edition
* Visual C + + (r) 6.0, Professional Edition and Enterprise Edition
* Visual InterDev (TM) 6.0 Professional Edition
* Visual J + + (r) 6.0, Professional Edition
* Visual FoxPro (r) 6.0 Professional Edition
License required to publish the MSDE solution
If Visual Studio 6.0 or Office 2000 developers use MSDE in the solution they create, they can publish their solution to their customers or end users as long as they adhere to the end-user License agreement for other software products that are used during the development process, Microsoft does not charge a corresponding royalty fee for developers who use MSDE.
Licensing required when an MSDE solution is interoperable with SQL Server
If a developer publishes an MSDE-based database application that needs to interact with SQL Server while it is running, they need to obtain a Microsoft Client Access License (CAL) for each copy of the database application that the developer installs on the client. For a central SQL Server database, developers also need to obtain a license for each copy of the installation. If you want more detailed information about SQL Server 7.0 quotes and licensing permissions, visit the relevant content in the SQL Server Web site:
Http://msdn.microsoft.com/isapi/gomscom.asp?Target=/sql/70/gen/pricing.htm..