Ms SQL Server 2000 administrator manual series-8. Managing Microsoft SQL Server services

8. Manage Microsoft SQL Server services
SQL Server Service Components
Use the SQL Server service administrator
Use Windows 2000 Service Control Administrator
Use SQL Server Enterprise Manager
Summary
After Microsoft SQL Server 2000 is installed, you can start execution. Before using Microsoft SQL Server 2000 to access data, you must first learn how to start the service components and their components of SQL Server, such as SQL Server Agent) distributed Transaction Coordinator and Microsoft search ). These components are part of the SQL Server service and are executed as independent jobs. This chapter discusses how to set and use various components and how to start and stop service components, and how to use three management tools (SQL Server service administrator, SQL Server Enterprise Manager, and Microsoft Windows 2000 Service administrator ).
________________________________________
Description
Although SQL Server 2000 can also be executed in Microsoft Windows NT 4, this chapter focuses on how SQL Server 2000 is executed in Microsoft Windows 2000. On the Microsoft Windows 98 job platform, SQL Server is like a normal execution file and does not support service components. Therefore, it is not covered in this Chapter.
________________________________________
Enterprise Manager is very important for managing SQL Server. Its service scope includes setting up databases and objects, setting server options, setting and managing the rewrite function, and managing backups. This chapter will only focus on the management functions of SQL Server and service components. Other services will be mentioned in subsequent chapters.
SQL Server Service Components
　
The "service" mentioned in SQL Server represents a program or program that can execute specific functions or support other programs. When you start using SQL Server, the SQL Server service starts in Windows NT or Windows 2000. The service project can manage database archives, process Transact-SQL (T-SQL) Statements, configure online user resources, and validate data consistency. As long as the SQL Server execution instance is installed, the service name of each execution instance is MSSQL $ InstanceName. the difference lies in setting the name of the execution instance to $ InstanceName during installation. Each SQL Server Agent corresponding to the execution individual is called SQLAgent $ InstanceName. In terms of Distributed Transaction coordinators and full-text search engines, even if more than one executor is installed, there is only one uniform service name.
These three service components are included in the copyright of your SQL Server. When installing SQL Server, they will appear in the options preset by the SQL Server Agent. If the Distributed Transaction Coordinator or full-text search engine service is not installed, you can run the SQL Server Installation program again.
The SQL Server Agent supports job scheduling, starting job execution, issuing alerts, notifications, and database repair plans. If service components are missing, it is difficult for managers to perform these tasks. The SQL Server Agent automates routine database maintenance. For example, you can set Automatic database backup at one o'clock every night, or automatic transaction record file backup every thirty minutes. If you want to continuously check the system's execution performance, you can also set the performance status. For example, when the CPU usage exceeds 90%, an alert is sent. The services mentioned above can be executed manually or set to automatically start service components when SQL Server is started. Setting Automatic Start ensures that the service components are actually executed. In Chapter 30th, we will learn how to create a database maintenance plan, and Chapter 31st will learn how to use the SQL Server Agent to set job schedules, start job execution, and issue alerts and notifications.
The Distributed Transaction Coordinator can coordinate remote database information from different sources and allow a transaction to be updated on several remote servers. If the transaction is approved, the transaction administrator ensures that all data sources are synchronized and permanently updated. If a transaction produces an error, the transaction administrator must reply to updates from all data sources. Chapter 1 of this book will discuss Distributed Transaction coordinators in more detail.
The full-text search engine service can start Microsoft seach in the SQL Server service administrator to serve as the full-text search index and search engine. Full-text search indexes allow complex searches in character strings. For example, you can use the search service to find similar words or phrases.
As mentioned earlier in this chapter, you can use several tools to start or stop SQL Server service components: SQL Server service administrator, SQL Server Enterprise Manager, and Windows 2000 Service administrator. First, let's look at the SQL Server service administrator. We can use it to control all service components, such as SQL Server Agents, distributed transaction coordinators, and full-text search engines.
Use the SQL Server service administrator
　
To use the SQL Server service administrator to start or stop the SQL Server service, follow these steps .)
1. Click Start/assembly, select Microsoft SQL Server, and then select the SQL Server service administrator to enter the application of the service administrator, as shown in 8-1.
　
 
Figure 8-1 SQL Server service administrator
2. the local server and service options are displayed in the drop-down list in the SQL Server service administrator dialog box. In the service drop-down list, select the service you want to manage. (Note! The service administrator can also manage other servers on the network ). The service components that can be managed in the service drop-down list include SQL Server, Distributed Transaction Coordinator, Microsoft search, and SQL Server Agent.
3. Select the corresponding button to start/continue, stop or pause the service. The dialog box contains a circle (with arrows inside) to display the current status of the selected service. If the SQL Server service is suspended, click Start/continue to restart the service. The reason for suspending SQL server without stopping SQL Server is that it prevents users from logging on, so that you have time to send messages to users connected to SQL Server and ask them to stop working, log out before stopping the server. If you stop SQL server without pausing it, all the server programs will be immediately interrupted.
4. The running service administrator updates the screen every five seconds. To change this setting, you can click the small icon in the upper left corner of the dialog box to display the system menu. Select Options to display the SQL Server service administrator dialog box, as shown in Figure 8-2.
　
 
Figure 8-2 "SQL Server service administrator options" dialog box
Enter a new time interval in the polling interval. If the service control action is selected, the service administrator will pop up a dialog box to verify each action to start, pause, or stop the service. Once you change these two settings, the interval between the four service components and the action of verifying the service control are both changed.
________________________________________
Description
If the automatic start is not set for the SQL Server service and the SQL Server Agent service, you must start it manually.
________________________________________
Use Windows 2000 Service Control Administrator
　
You can use the Windows 2000 Server Service Control administrator to start or stop the local or remote SQL Server service. Or set the Windows 2000 Service Control administrator to automatically start the SQL Server service when the system is enabled. The procedure is as follows:
1. Click Start/ASSEMBLY/system management tools and select services to enable the Service Control administrator, as shown in Figure 8-3.
2. In the service list, select Distributed Transaction Coordinator, Microsoft search, MSSQLServer, or SQLSERVERAGENT. Select the project to be started and press twice to go To the content screen, as shown in Figure 8-4.
　
 
Figure 8-3 "Windows 2000 Service Control administrator"

　
 
Figure 8-4 "content" Window of "SQL Server Agent"
3. In the drop-down list of the Startup type, you can select automatic, manual, or disabled. The automatic option can automatically start the service component after the system starts. The manual option is to manually select the service component. The disabled option is to disable the start of the service component. Click confirm storage settings.
4. There are three other tabs in the content window. The logon tab specifies the logon identity, that is, the account used to log on to the service component. The repair volume page allows you to set the computer response when a service component fails. The dependency page displays the dependency between the selected service component and other service components (dependency does not necessarily exist ). For example, the SQL Server Agent service is dependent on SQL server. If the SQL Server service is terminated, the SQL Server Agent cannot be started.
Use SQL Server Enterprise Manager
　
Enterprise Manager is a part of the Microsoft Management Console (MMC. MMC is a centralized system that manages applications and comprehensively manages the architecture on Windows 2000 Server. In Windows 2000 and later versions, MMC manages Microsoft BackOffice applications (such as Microsoft Exchange Server, Microsoft Proxy Server, Microsoft Site Server, Microsoft Systems Management Server, and Microsoft SNA server ), will play a crucial role.
Manage SQL Server
　
Enterprise Manager is the most complete tool for managing and setting SQL Server. The service administrator can only start, pause, or stop a service, but Enterprise Manager can not only start or stop the service, you can also perform the following tasks:
• Register servers
　
• Set local and remote servers
　
• Set and manage multi-Server Installation configurations
　
• Set login security, add users, system administrators, and operators
　
• Specify the SA Password
　
• Create and schedule jobs
　
• Create alerts and set SQL Server to contact the system administrator by email
　
• Set and manage databases, data tables, indexes, views, pre-stored programs, rules, triggers, default values, backup devices, and error logs
　
• Manage other SQL Server Service Projects
　
Enterprise Manager (8-5) is the first step to manage all SQL Server tasks. This chapter will learn the basics of using Enterprise Manager. Advanced usage will be learned in subsequent chapters of this book.

　
 
Figure 8-5 SQL Server Enterprise Manager
Etnerprise manager can be used to execute the following four jobs, which must be executed before the first installation using SQL Server. Each assignment will be explained in more detail later:
• The advantage of setting up a server group to create a server group is to restrict certain information to only allow access to specific groups. If some accounts have similar resource usage and demand types, they should be placed in the same group for convenient management.
　
• The registration server must register your server with MMC to manage the server with MMC.
　
• The Access Server can check and set some attributes after registering the server. If the network is in a multi-server environment, you can use Enterprise Manager to manage and set all servers.
　
• Change the default password of the system administrator. When SQL Server is installed, no password is set for the preset MANAGEMENT SYSTEM account. You must set a password before you start using SQL Server.
　
Create a server group
　
Enterprise Manager can be used to create server groups to help manage jobs. Server groups allow you to classify related servers into one type for easy access. You can think of a server group as a data folder, while a data folder contains related files. The advantage is that you only need to execute a command once to change the servers in the group. You do not need to execute the same command repeatedly on each server. According to the default value, an SQL Server group is created when SQL Server is installed. The group name is the SQL server group. Follow these steps to create a server group:
1. Click Start/ASSEMBLY/Microsoft SQL Server]/Enterprise Manager to enable the Enterprise Manager application.
2. The window on the left of enterpise manager shows that the SQL server group is a subdirectory under Microsoft SQL servers. The window on the right shows the server group. On Microsoft SQL Server, click the right button and select Add SQL Server group in the shortcut menu to add a group.
3. enter a new group name in the server group dialog box, as shown in Figure 8-6. If you select a sub-group, you can set the new server group to a sub-group under the existing group. If you select the top-level group, the new server group is the same level as the SQL server group. Click OK to save the new group.
　
 
Figure 8-6 "server group" dialog box
Registration Server
　
After creating an SQL Server group, you can register a local server or remote server as a member of the group. Follow these steps:
1. select a server group in the right pane of Enterprise Manager and press the right button (if the data folder under Microsoft SQL servers in the left pane has been expanded, you can also press the right button on the group name in the left pane) and select Add SQL Server registration in the shortcut menu.
2. Now you are welcome to use the register SQL Server genie screen. In the future, you will use enterprise manager for many routine management tasks. The Wizard allows you to easily complete many set programs. Now, follow the next step to register the program.
3. Select SQL Server, as shown in Figure 8-7. The available SQL server on the network is listed in the list under the available server, select the available server in the list, or manually enter the server name in the text box, click the Add button to register the server. You can also press the Remove button to remove the server that you do not want to register. Enter your options and follow the next step to register the program.
4. Go to the select Verification Mode screen. Select the Security Authentication Mode of SQL Server. The security of SQL Server is discussed in Chapter 34th. (If you select general installation during installation, SQL Server will be set to use Windows NT authentication mode ). Follow the next step to continue the registration process.
　
 
Figure 8-7 select Verification Mode
5. Select the SQL server group, as shown in Figure 8-8. You can add your servers to an existing group, or add a new SQL Server group at the top. If you want to add servers to an existing group, select the first option and select a group from the drop-down list. Select the second option and enter the new group name in the group name text box. Follow the next step to continue the registration process.
6. Go to the SQL Server wizard screen after completing registration. The registered servers are displayed on this screen. If there are other settings to be modified, click the previous step to return to the previous screen and reset the settings. After confirming that the settings are correct, follow the instructions to complete the registration process.
　
 
Figure 8-8 select an SQL Server Group
7. The register SQL Server dialog box appears. Check that the registration is successful, as shown in Figure 8-9. Press close and close to close this dialog box.
　
 
Figure 8-9 "register SQL Server Message" dialog box
Access Server
　
After a server is successfully registered with Enterprise Manager, all content, databases, and objects can be accessed. As long as you expand any server under the SQL server group in the left pane of Enterprise Manager, servers in all groups are listed and the newly registered server content and objects are viewed, as shown in Figure 8-10. The following sections will explain how to manage and set server content and attributes.

　
 
Figure 8-10 server content and objects
Change default password
　
All SQL server settings have a built-in management account, which is named SA (representing system administrator ). In a newly added SQL Server, SA does not set any password. To ensure database security, follow these steps to set the SA password.
1. First enter the server to which you want to change the password. The method for entering the server is shown in the previous section.
2. Expand the Security folder and click log on. the SQL Server user account will appear in the right pane of Enterprise Manager, as shown in Figure 8-11.
　
 
Figure 8-11 SQL Server user account
3. Press the right button for SA logon and select content from the shortcut menu to enter the SQL Server logon attribute window, as shown in Figure 8-12.
　
 
Figure 8-12 "SQL Server login properties" Window
Other settings can be made through the SQL Server login attribute window. These features are described in Chapter 34th of this book.
4. enter a new password in the password box, and click OK to enter the Confirm Password dialog box to confirm the password.
5. re-enter the password and click OK to complete the password setting.
________________________________________
Note:
If you forget the password you set in the future, you have to reinstall SQL Server. Therefore, remember the password you set to avoid any problems.
________________________________________
Manage other services
　
Enterprise manage R can be used to manage SQL Server Component services, such as SQL Server Agents, distributed transaction coordinators, and full-text search engines. Enterprise Manager is the only tool that can manage these service projects. Unlike service control administrators or SQL Server service administrators, Enterprise Manager can only start or stop these service projects.
SQL Server Agent
　
Enterprise Manager provides simple interfaces to manage SQL Server proxies. Follow these steps to access the content of the SQL Server proxy service.
1. Expand the server to be accessed in Enterprise Manager, and then expand the management data folder, as shown in Figure 8-13.
　
 
Figure 8-13 "manage" data folder in Enterprise Manager
2. in the right pane, press the right button on the SQL Server proxy program, or in the left pane, click the right button on the SQL Server proxy program icon to open a shortcut menu, you can choose to start or stop the agent service, display the Error Log File, set the server as the master server or target server, add a job, alert or operator, or view the content window. In Chapter 31st, we will discuss these options.
3. Select content from the shortcut menu to go to the SQL Server Agent Properties window, as shown in Figure 8-14.
　
 
Figure 8-14 SQL Server Agent Properties window
4. In this window, you can set different options for the SQL Server Agent on different volume labels. These tabs are: General, advanced, alert system, job system, and online. In the lower left corner of the window, there is a Description button to explain the options of each volume tab.
Microsoft Distributed Transaction coordinators
　
In Enterprise Manager, the only option to manage Microsoft Distributed Transaction coordinators is start or stop. Expand the server to be accessed, and then expand the support service data folder, as shown in Figure 8-15.

　
 
Figure 8-15 "Support Service" data folder
Click the right button on the Distributed Transaction Coordinator to stop or enable the Service in the shortcut menu.
Full-text search engine
　
You can also find the full-text search engine in the data folder of the support service. You can also call a shortcut menu to stop or enable the service, you can also clear full-text directories and view service properties.
Summary
　
The SQL Server service administrator and Enterprise Manager are two important tools used to manage SQL Server. This chapter describes the basic steps for managing and setting SQL Server and SQL Server services (advanced settings will be learned in the last chapters of this book ). After learning how to use these two management tools, you can create your own database and data tables. In the next two chapters, you will understand the importance of SQL Server Enterprise Manager in establishing and managing databases and data tables.