Shulou(Shulou.com)06/01 Report--

In the daily management and maintenance of SQL Server, we need to pay attention to a lot of things, and then sometimes do things out of order, now on the basic principles and ideas to solve common problems, here to share with you.

First of all, introduce the basic operation principle of SQL Server.

As a Windows service, each instance of SQL Server is a sqlserve.exe process. After starting, this process reads the startup configuration of the SQL Server instance, detects the hardware, configuration memory and CPU, initializes some SQL Server system configuration, starts the database, and connects to the network. Then the database starts normally, and then the client can access it normally.

This is the basic startup process, and under what circumstances will it fail to start normally? of course, we will introduce the basic situations that we can solve, such as execution file corruption, database corruption, and these problems that cannot be solved immediately. I'll talk about it later.

The 1.SQL Server instance service process failed to start normally

When the sqlservr.exe process starts, it needs to read some key values of the registry and read some files, and then act accordingly according to these configurations.

What are the registry key values that need to be read, and what is the general purpose of them:

1) under HLM\ SPFTWARE\ Microsoft\ Microsoft SQLServer\ MSSQL11.MSSQLSERVER\ MSSQLServer

Audit Level: sets whether SQL Server records user login information, or you can set the server properties in Management Studio.

Login Mode: sets whether SQL Server receives only Windows account logins or both WIndows and SQL server accounts.

2) under the startup parameters HLM\ SPFTWARE\ Microsoft\ Microsoft SQLServer\ MSSQL11.MSSQLSERVER\ MSSQLServer\ Parameters

SQL Server can have a lot of startup parameters, and some things provide analysis of the problem.

Here are a few fixed parameters:

-d records the physical location of the data file of the master database

-l records the physical location of the master log file

-e records the location of the SQL Server error log file

3) under the network configuration information HLM\ SPFTWARE\ Microsoft\ Microsoft SQLServer\ MSSQL11.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib

Which network protocols will SQL Server listen on, and the configuration of each network protocol, is also placed in the registry.

There are two problems that may be encountered in reading SQL Server startup information from the registry:

1) permission to activate account

If the startup account does not have permission to read the registry or call some SQL Server resource files, then the SQL Server process may not be created, and the errorlog files may not be generated.

This problem needs to be solved by giving permission to start the account.

2) Log file and folder access problems

For SQL Server to start successfully, you must be able to create the log file errorlog correctly. If the log file cannot be created successfully, then SQL Server cannot be started.

Sometimes the path to the log is incorrect, that is, the location specified by the-e parameter is incorrect, or a process locks the errorlog file.

two。 The system database cannot be started properly

After the SQL Server service is enabled, the first thing is to start the system database. If the system database cannot be opened normally, SQL Server will not start normally.

3. Network configuration failed

To receive a client connection, SQL Server must configure a network protocol, usually TCP/IP,Named Pipe,LPC, and if the protocol is not configured correctly, the client cannot connect to the SQL Server properly.

Among the protocols to be activated by SQL Server, there are

Shared Memory is a connection designed for local connections through LPC (Local Procedure Call) technologies like SQL Server.

Named Pipe means that customers need to interact with SQL Server through the name of pipe. Sometimes, they need to make sure that typing the pipe name in errorlog is what the client expects.

TCP/IP is the IP address and port number that the SQL Server instance listens on.

4. User data cannot start normally

The last step of SQL Server startup is to open the user database. If the database file is corrupted, or if the database is not closed properly the last time SQL Server is closed, you may have trouble opening the user database.

5. SQL Server resources cannot be kept online in a cluster environment

SQL Server installed in a cluster environment is monitored by the Windows cluster service to see if the SQL Server is working properly. If the cluster service is not connected to the SQL Server, a switchover may occur. Management needs to monitor and analyze why SQL Server resources can't stay online, and what is the reason for the last switch.

Tip: the Errorlog log records all serious problems encountered by SQL Server during startup or running, so when SQL Server encounters an abnormal problem, check the log file first. This file is generally saved 7, in the order of time from near to far, and restarting the SQL Server service will generate a new errorlog file.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.