MSDE instructions (complete)

1. MSDE Introduction
MSDE 2000 (SQL Server Desktop Engine, ms SQL Server Desktop Engine) is a royalty-free, redistributable database engine provided by Microsoft that is fully compatible with Microsoft SQL Server 2000.
Msde2000 is small and has low system requirements. It supports most functions of SQL Server 2000 in other versions, but does not include graphical management tools (Enterprise Manager, query analyzer, and so on ).
MSDE is an enabling technology that provides local data storage and is fully compatible with Microsoft SQL Server & reg; version 7.0 code. This technology transforms access from a simple File Server database application to a highly scalable customer/Server solution suitable for organizations of any size.

MSDE 2000 system requirements:

1. Operating System
MSDE 2000 is designed to run on the following operating systems:
Microsoft Windows 98
Microsoft Windows NT Workstation 4.0 Service Pack 5 (SP5) or later
? Microsoft Windows NT Server 4.0 Service Pack 5 (SP5) or later
? Microsoft Windows NT 4.0 Server Enterprise Edition Service Pack 5 (SP5) or later
? Microsoft Windows 2000 Professional
? Microsoft Windows 2000 Server
? Microsoft Windows 2000 Advanced Server
? Microsoft Windows 2000 datacenter Server
? Microsoft Windows XP
? Microsoft Windows Server 2003

CPU: A computer that uses a Pentium processor with a minimum of 166 MHz.
Memory: a minimum of 32 mb ram.
Note: Microsoft recommends that you use 64 MB or larger memory. As required by the operating system, more memory may be required.
Hard Disk: The minimum available disk space is 44 MB.
Others: Microsoft Internet Explorer 5.0 or later.

Note: before installing Microsoft Internet Explorer 5.0, you must install Microsoft Windows NT 4.0 Service Pack 5 or later.

2. Performance Comparison Between MSDE and Microsoft Access

? Requirement? MSDE? Microsoft Access (JET)
? Scalability
? ? ? Recommended up to 5 concurrent users (easily scales to SQL Server to obtain unlimited number of concurrent users)
2 GB of data
Transaction logging recommended for up to about 20 total users
2 GB of data
No transaction Logging
? Login in upgrading to SQL Server seamless since based on the same technology: simply detach the database via access/MSDE and then re-attach from within the SQL Server Enterprise Manager; preserves all stored procedures, objects, views.
Example Using pubs database:
1) sp_detach_db 'pubs'
2) sp_attach_db 'pubs', 'c:/MSSQL7/data/pubs. MDF 'Access Upsizing Wizard: converts access/Jet Table objects to SQL Server. makes no modifications to forms, reports, queries, macros, or security. may require some modification upon conversion.
Data Transformation services: imports data only.
? Business Critical 7x24 support and QFE
Point-in-time recovery
Guaranteed transaction integrity
Built-in Fault Tolerance
Security integrated with Windows NT No 7x24 support or QFE
Recoverable to last backup
No transaction Logging
No integrated security with Windows NT
? Rapid application prototyping access is UI for both engines and offers WYSIWIG database tools and built-in forms generation.

Jet and MSDE Usage Analysis
? There are four key usage criteria to consider when choosing your database engine. Let's review them in priority order.
?
Simplicity. JET 4.0 has the highest compatibility with Access 97 and earlier versions. if you have existing applications developed for access, jet is your easiest and probably best option given its compatibility with Access 97 and earlier.
Jet is easier to use and administer than MSDE, making it a good choice for new and relatively simple database applications that do not have compatibility concerns with SQL Server. it has low resources for memory and disk and requires nearly zero administration. jet is also the default database option for Access 2000. A database created using the jet engine can always be upsized later to SQL Server Using The Upsizing Wizard although additional modification may be required.
?
? (A) advantage-Jet
?
Data Integrity. MSDE incorporates technology from SQL Server 7. SQL Server delivers a single code base, which scales from a PC running Windows 95 to multiprocessor clusters running Windows NT Server, Enterprise Edition, offering 100% application compatibility. jet does not have this type of scalability as it is confined to the access product.
MSDE is a client/server data engine; Jet is a file/Server Data Engine. the big advantage that MSDE has over jet is that MSDE is a process that runs queries and logs transactions. if anything shocould go wrong during a write to the database, such as disk error, network failure or power failure, MSDE can recover as it logs transactions. after the system comes back up, MSDE will revert back to the last consistent state. this gives MSDE greater reliability than jet. if the system were to go down with jet, the database cocould be upt and you may be need to revert back to your last backup copy.
? MSDE is the right engine for systems that involve important transactions, such as financial applications, or for mission critical applications that need to be up 24 hours per day, seven days a week, such as the Internet. the more important the database, the more likely you are to choose MSDE.
?
? (B) advantage-MSDE
?
Number of simultaneous users (performance). SQL Server 7.0, the basis for MSDE technology, can handle a very large number of simultaneous users. Jet and MSDE are optimized for individual or small workgroup solutions.
MSDE also has a performance advantage over jet for large sets of data and parallel simultaneous users. because Jet is a file-server system, the query processing must happen on the client. this involves moving a lot of data over the network for large databases. MSDE runs that same query on the server. this loads the server more, but can reduce network traffic substantially, especially if the users are selecting a small subset of the data.
If you are creating a new application for a small group of users, MSDE or SQL server will help your application scale in the future.
?
? (C) advantage-MSDE and SQL Server for scalability.
?
Amount of data. Jet can handle up to 2 GB of data per MDB file. MSDE also supports 2 GB of data per database.

Note:
1. The database processed by MSDE requires that the total data volume of each database be less than 2 GB.
2. A maximum of five online users are supported. There is no limit on the number of Session connections for each user. (This restriction does not seem to be found in the test)
Ii. Installation instructions
MSDE is released along with sqlserver2000. It can be found on the sqlserver2000 installation disc (not SP4) or downloaded from the Microsoft official website:
Http://www.microsoft.com/downloads/details.aspx? Familyid = 8e2dfc8d-c20e-4446-99a9-b7f0109f8bc5 & displaylang = EN (SQL2000.MSDE-KB884525-SP4-x86-ENU.EXE at the bottom of the page)

There is a setup. ini file in the MSDE folder, which is the configuration before the installation program. The common format is as follows:

[Options]
Targetdir = "D:/Setup/MSDE/binn" // program installation directory
Datadir = "D:/Setup/MSDE/Data" // data file storage directory
InstanceName = "zanb" // Instance name to avoid conflict with the default Instance name
Securitymode = "SQL" // The Authentication mode is mixed. The default mode is Windows authentication.
Sapwd = "sa" // the user's Sa password, which seems to be used by SP4.
Usedefaultsapwd // The SA password is empty by default.
Disablenetworkprotocols = 0 // network connection is not allowed. Set 0 to allow

Double-click setup to install the SQL Server to the specified directory. After installation, run sqlmangr.exe of C:/program files/Microsoft SQL Server/80/tools/binnto start SQL Server.

Another installation method is to use the merge module under the MSM in MSDE to seamlessly integrate it into your own MSI installer.
The procedure is as follows:
Note: All Visual Studio. NET development products provide Windows Installer software.
1. Start Visual Studio. NET.
2. Create an installation project.
A. On the File menu, point to new and click Project. The "New Project" window appears.
B. In the "project type" tree, click Install and deploy the project node, and then click Install project in the "template" window in the right pane.

3. type the name and location of the project, and click OK.

Note: Make sure Solution Explorer is on in the Microsoft development environment. To open it, click solution resource manager in the View menu.
4. Set the search path of the project to the folder where the MSDE merge module is located:
A. Select the installation project in Solution Explorer.
B. In the "properties" window, find the searchpath item.
Note: If the "properties" window is not opened, click the Properties window on the View menu.
C. Enter the complete path of the position of the MSDE merge module. Generally, the path is X:/MSDE/MSM and X:/MSDE/MSM/1033, where X is the drive letter of the disc drive.

5. Add the MSDE 2000 merging module to the project: Right-click the project name in Solution Explorer, click Add, and then click Merge Module options.
6. Locate the location of the merge module File and select the merge module to be added to the project.

Note: dependencies should be taken into account, which is important. Some merging modules require other merging modules (these modules are called dependencies) to correctly generate the installation package in this project.
7. After adding all required merging modules, you can generate the Windows installer package:
A. Click the project in Solution Explorer.
B. Click Generate in the Microsoft development environment. This will generate and create an MSDE 4th Windows installer package at the location specified in step 1.

Note:
1. In Step 4, the property window is in the menu view-other Windows-Properties window, instead of right-clicking the property page (or view-property page)

3. Manage MSDE
MSDE does not have a visual enterprise manager like sqlserver, but it can be managed through osql. EXE. (Of course, you can use the Enterprise Manager and analyzer that comes with sqlserver2000. For example, if server a is installed with sqlserver2000 and server B uses msdesp4, you can use the Enterprise Manager and analyzer to connect to msdesp4, you can also download the green enterprise manager and analyzer from the internet. I have collected and integrated the analyzer. If you are interested, you can give me the information)
If it is used for the first time after installation, You need to perform the following settings:
1. After the installation is complete, the SA password for the management account is blank by default (no matter whether or not the password is set in setup. INI), you need to set the password.
A. Use osql. EXE to log on. In the command prompt, enter:
Osql-U sa-s servername/InstanceName (Press ENTER)
B. the following message is displayed: Password: (Press ENTER)
C. Type: sp_password @ old = NULL, @ new = 'sa ', @ loginame = 'sa' (Press ENTER)
D. Type: Go (Press ENTER)

Returns the success message.

2. The Enterprise Edition program will automatically add users and initialize databases. If you need to add users, you can use the sp_addlogin stored procedure to create a new Logon account that uses SQL Server Authentication To establish a connection to SQL Server. The following example shows how to create an SQL Server logon password "zhianbang" for a user named "zanb": Execute:
Type: exec sp_addlogin 'zanb', 'zhianbang '(Press ENTER)
Type: Go (Press ENTER)

Use SA to create a database.
Create Database cmsdb
Go
Authorize the database to zanb
Use cmsdb
Sp_changedbowner zanb
Go
Log out and use zanb to log on
Osql-s lizp-u zanb-P zhianbang
You can perform operations on the cmsdb database.

Osql commands comply with the transact-SQL specifications. For specific commands, see the help documentation provided by sqlserver2000.

3. Change the Authentication Mode
If you cannot determine how to verify the Authentication Mode installed by MSDE, you can view the corresponding registry key. By default, for Windows authentication, the value of the Windows loginmode registry subitem is set to 1. If mixed-mode authentication is enabled, this value is 2.
The location of the loginmode subitem depends on whether you install MSDE as the default MSDE instance or a named instance. If MSDE is installed as the default instance, The loginmode subitem is located in the following registry subitem: HKLM/software/Microsoft/MSSQLServer/loginmode. If MSDE is installed as a named instance, the loginmode subitem is located in the following registry subitem:
HKLM/software/Microsoft SQL Server/% InstanceName %/MSSQLServer/loginmode
Note: before switching the Authentication mode, you must set the SA password to avoid exposing potential security vulnerabilities.

4. Use MSDE
Because MSDE and sqlserver2000 are fully compatible, that is, MSDE and sqlserver2000 are exactly the same for programmers to connect to databases.

By rainfish http://blog.csdn.net/bat603