SQL Server 2000 installation and basic framework

Server We always put the word SQL Server on the tip of our tongue, and perhaps very few people think about what SQL Server is. The question is difficult to make clear in a few words. The reason we have to discuss SQL Server in a serialized way is to address this issue from different angles.
One, server components

SQL Server 2000 consists of two parts: Server components and client tools.

1. Content

The server components of SQL Server are run as Windows Services (Windows service). SQL Server is generally considered to contain four kinds of Windows services (we focus on OLTP, and do not consider OLAP for the time being), respectively: MSSQLSERVER, dtcdistributed Transaction Coordinator, SQLServerAgent, Search Service.

MSSQLServer is the most commonly used service, the general database functions are provided by it, such as file management, query processing, data storage, etc. DTC is a distributed Transaction Coordinator that supports update operations across two or more servers to ensure transactional integrity ; SQLServerAgent is responsible for automating SQL Server, which is needed if SQL Server executes a stored procedure at a specified time; Search service is full-text query services, responsible for Full-text search work.

2. One of the reasons for dividing four

Now we know that the core of SQL Server is four server components that run in the form of Windows services. Smart readers may ponder the question: Why do server components run as Windows services? Why do I need to split the entire system into four server components?

The SQL Server server component is used as a Windows service program, primarily because the Windows service program is available to users who are not logged in.

The main reason for dividing SQL Server's core functionality into four Windows service programs is to consider the following factors: These Windows service programs each bear the task functionally divisible, which is a prerequisite; not every application needs to use all of the functionality provided by four services. Let the user have the choice to close some services, will undoubtedly save the system resources, can also save the user spent on the management of energy; Finally, separate into multiple service programs, you can set the operating system-level security policy for each service.

Second, the Client tool

The core of SQL Server 2000 is the server components discussed above, but the user is not directly exposed to them (although they are the real work), but the client tools. The server component is the engine, the client tool is the user interface, and the two are complementary.

Let's look at what the client Tools for SQL Server 2000 are-Enterprise Manager, Query Analyzer, Profiler, Service Manager, client Network Utility, server Network Utility, import and Export data (DTS), and so on.

The server components and the client tools are functional, and the client tools need to express the most abundant functionality of the server components in the simplest form; The server components and the client tools are physically discrete, saying vernacular: they are not the same program! The client tools are connected to the server component, You need some dynamic link libraries for communication, and the SQL Server 2000 Communication Library supports a variety of network protocols, such as TCP/IP, named pipes, and so on.

The question is clear: as long as the client tools and server components are compatible with functionality, they can be connected through a certain protocol, so we can connect to SQL Server servers all over the world by attaching a set of client tools on our own machines, which requires the other side to open enough permissions.

Tip: Installing SQL Server 2000 is really about installing server components and client tools. Of course, we can choose to install both server components and client tools at the same time, or install only one of them, or even choose to install fewer things.

Iii. Version Issues

There are many versions of SQL Server 2000: Enterprise, development, Standard, and personal. Each version contains a client tool that is essentially the same, and the server component may be a little different. So before you install SQL Server, you must refer to the operating system and SQL Server version compatibility table (Figure 1) and select the appropriate version of SQL Server based on the operating system.

Figure 1

Iv. on examples

Before you start installing SQL Server, there is another concept that needs to be explained, that is, the SQL Server instance. We mentioned that the SQL Server server component is made up of four Windows service programs, in which we may install all the server components or only a subset of them, but we all need a unified concept to flag a set of SQL Server services, which is the concept of SQL Server instance.

Tip: You can understand that installing the SQL Server server component is creating a new instance of SQL Server (or, of course, adding or subtracting service components from an existing instance). SQL Server 2000 allows multiple instances to be created in the same operating system, although we would not normally do so.

If you install only one instance of SQL Server, you do not need to specify the instance name at SQL Server installation, and the default name is used automatically. The name of the computer in the Windows domain is the name of the SQL Server instance, and you can use an IP address to represent a SQL Server 2000 instance when you connect to an instance of SQL Server using the TCP/IP protocol.

If you have more than one instance of SQL Server 2000 installed in an operating system, you need to specify the instance name when you install SQL Server.
You can use the form "computer name \ Instance name" to flag SQL Server 2000 instances in a Windows domain, and you can use IP address \ Instance name to represent a SQL Server 2000 instance when you connect to an instance of SQL Server using the TCP/IP protocol.
V. Installation of actual combat

The SQL Server 2000 installer is very intelligent, and basically the user simply has to follow the prompts and select the default. The following I select some need to pay attention to the settings screen to explain.

Select "Create a new instance of SQL Server ...", as shown in Figure 2:

Select Install server and client tools, as shown in Figure 3:

Set the startup mode for the service, which has two services, namely SQL Server and SQL Server Agent. This is the MSSQLServer and SQLServerAgent mentioned above. Here our operating system launches them as "Local System Account", as shown in Figure 4:

Select mixed mode for authentication, as shown in Figure 5:

Six or one peep

Installation is complete, you certainly can't wait to appreciate its style. Don't worry, let's first make sure the database server is started. We mentioned that the SQL Server 2000 core is made up of four server components, where the database service is MSSQLSERVER, and now we just want to make sure it's started.

1. Service Manager

Select the name of the native SQL server instance in the server column;

Select SQL Server in the Services column (MSSQLServer above);

Press the "Start/Continue" button to start the MSSQLServer service;

Normal Startup:

2. Enterprise Manager

Open Enterprise Manager, expand the database project for your database native SQL Server instance node, and you can find that SQL Server has installed six databases automatically.

The master database records all system-level information for the SQL Server system.

The tempdb database holds all temporary tables and temporary stored procedures.

The model database is a template provided for creating a database for users.

The msdb database is for SQL Server agents to schedule alerts and jobs and to log various operations.

Northwind and pubs are sample databases, and we'll use them often to demonstrate later.

In addition to Northwind and pubs, the other four databases are maintained by SQL Server and do not typically require user intervention.