Basic concepts of SQL Server

Address: http://blog.csdn.net/zjcxc/archive/2005/07/06/415711.aspx Author: zhujian
1. How to understand SQL Server instances

In the project implementation process, many users need to develop a new SQL Server-based system. After the system is accepted, it should be merged with an original SQL Server system, A single server is shared, so a separate server cannot be provided for the new system (a separate server cannot be provided for development ). However, to ensure the development of the new system without affecting the normal operation of the old system, the new system must use an independent SQL Server service. In versions earlier than SQL Server 2000, such requirements cannot be met, but in SQL Server 2000, on the basis of the original SQL Server instance, add another instance to solve the problem.
The so-called "instance" is an SQL Server database engine. SQL Server 2000 supports running multiple SQL Server database engine instances on the same computer at the same time. Each SQL Server database engine instance has a set of systems and user databases that are not shared by other instances. Applications connect to SQL Server database engine instances on the same computer in the same way as those running on other computers. Because each instance has a set of systems and user databases that are not shared by other instances, the operation of each instance is independent, and the operation of one instance is not affected by the operation of other instances, it does not affect the operation of other instances. Installing Multiple SQL Server instances on a computer is equivalent to simulating this computer into multiple database servers, and these simulated database servers run independently and simultaneously.
There are two types of instances: Default instance and named instance. A computer can have only one default instance or no default instance. The default Instance name is the same as the computer name, changing the computer name will modify the default Instance name synchronously (SQL Server 7.0 can only be installed as the default instance. After changing the computer name, the SQL Server service cannot be started, you must execute the SQL Server Installation Program for automatic repair to solve the startup problem). When the client connects to the default instance, the computer name that installs the SQL Server instance will be used.
When installing SQL Server 7.0 and SQL Server 2000 on the same computer, since SQL Server 7.0 can only be installed as the default instance, you should first Install SQL Server 7.0, install SQL Server 2000 as a named instance. Alternatively, when installing SQL Server 2000, specify the installation as a named instance.

Multiple named instances can be installed on one computer. When a client connects to a named instance, the format of the following computer name and Instance name combination must be used:
Computer_name \ instance_name

 2. How the client interacts with SQL Server

The complete process of sending a Transact-SQL statement from the client to the SQL Server, processing the result, and returning the result to the client is as follows:
(1) TDS data packets are generated by Microsoft ole db provider, SQL Server ODBC driver, or DB-Library DLL of SQL Server.
(2) TDS data packets are transmitted to the SQL Server Client. Net-Library.
(3) The client Net-Library encapsulates the TDS data packets as network protocol data packets.
(4) on the server, the server Net-Library receives network protocol packets. ODS (Open Data Services) is responsible for extracting the TDS Data packets and passing the results to the relational database server.
(5) The Relational Database Server is responsible for processing the content of the TDS data packets.
(6) If you need to return the result to the client, ODS will generate the processing result and pass it to the Server Net-Library.
(7) The Server Net-Library encapsulates the TDS data packets as network protocol data packets and then transmits them back to the client. Net-Library.
(8) The client Net-Library restores the network protocol data to the TDS data packet and transfers it to the upper-layer database interface, for example, Microsoft ole db provider, SQL Server ODBC driver, or DB-Library DLL.
(9) Microsoft ole db provider, SQL Server ODBC driver, or DB-Library DLL restores the TDS data packet to a "result set" that is acceptable to the application ".
No matter whether the application that accesses SQL Server is on the same single machine as the SQL Server instance, the communication between the application and the SQL Server instance goes through the above process.

  3. TDS, NET-Library, and network protocols

TDS (table format data stream Protocol ):SQL Server uses an application-level protocol called table format data stream (TDS) to communicate between client applications and SQL Server. TDS packets are encapsulated in the packets generated by the protocol stack used by the Net-Library.
For example, if a TCP/IP socket Net-Library is used, the TDS data packet is encapsulated in the TCP/IP data packet of the basic protocol. The content of the data packet sent back from the result set to the application depends on whether for xml is specified in the Transact-SQL statement passed to the database engine:
If for xml is not specified, the database engine sends the relational result set back to the application. The TDS data package contains the result row set. Each row contains one or more columns, as specified in the SELECT statement selection list.
If for xml is specified, the database engine returns the XML document to the application in the form of a stream. In the TDS data packet, the XML file is formatted as a single, long Unicode value. The size of each data packet is about 4 KB.
You can configure the data packet size of SQL Server, that is, the data packet size of TDS. The size of the TDS data packet on most clients is 4 KB by default (the default size of the DB-Library application is 512 bytes). Tests have proved that in almost all solutions, this is the best data packet size for TDS. The size of the TDS data packet can be larger than that of the data packet in the basic protocol. In this case, the protocol stack on the computer that sends the data packet Automatically splits the TDS data packet into units suitable for the protocol data packet size, the protocol stack on the client computer reassembles the TDS data packets on the receiving computer.

Net-Library and network protocol:SQL Server uses a dynamic link Library (DLL) called Net-Library to communicate with a specific network protocol. Matching Net-Library pairs must be active on the client and server computers to support the required network protocols.
For example, to allow a client application to communicate with a specific SQL Server instance through TCP/IP, the client TCP/IP socket Net-Library (DBNETLIB. dll) is configured to connect to the server, while the server TCP/IP socket Net-Library (SSNETLIB. dll) must be listened on the server computer.
For a pair of Net-libraries, the client/server connection is not supported. Both the client and server must also run the protocol stack that supports Net-Library.
For example, if the server TCP/IP socket Net-Library listens to the server computer and the client TCP/IP socket Net-Library on the client computer is configured to connect to the server, then, the client can connect to the server only when the TCP/IP protocol stack is installed on both computers.
Net-Library is installed by the SQL Server installer, and the network protocol is installed by the operating system installer.

 4. Server ID

When an application needs to access an SQL Server instance, it must be aware of the unique identifier of the Server. This is similar to finding a person. You must know the name of the person you are looking for, or other signs that can uniquely identify the person within the search range, such as the ID card number.
The following examples illustrate the common identity that can be used to access the SQL Server:
1. The default instance connecting to the SQL Server on the local machine has a valid Server ID which can be:
The computer name, (local), localhost, 127.0.0.1,., server name and local IP address are not specified.
2. The name of the SQL Server instance connected to the local machine has a valid Server ID that can be:
Computer Name \ Instance name,. \ Instance name, local IP address \ Instance name
3. The default instance connecting to SQL Server on other computers has a valid Server ID which can be:
Server computer name, Server IP Address
4. The name of the SQL Server instance connected to other computers has a valid Server ID that can be:
Server computer name \ Instance name, Server IP address \ Instance name

Other Instructions:
1. You can add tcp:, np:, lpc:, or rpc: at the beginning of the SQL Server instance name to specify the protocol in your connection string, for example: tcp: jack
2. You can add a port number to the connection string (for example, MyServer \ MyInstance, 1433) to specify the specific port on which the SQL Server instance is listening.
3. You can use the client network utility of SQL Server to specify an alias for the SQL Server to be connected, so that you can use a brief and friendly name to access SQL Server.

 5. logon ID and authentication

A logon ID is an account identifier used to control access to any SQL Server 2000 system. SQL Server 2000 completes the connection only when the specified logon ID is verified to be valid. This type of logon authentication is called authentication.
The default database is used in the recording attribute. When a user logs on to the SQL Server, the default database becomes the current database of the connection, unless the connection request specifies another database as the current database.
The account used to log on to SQL Server is created in SQL Server (using SQL Server Authentication, you can also create and be granted logon permissions in Windows NT 4.0 or Windows 2000 (using Windows Authentication ).
The SQL Server instance must verify that the login ID provided by each connection request has the permission to access the instance. This process is called authentication. SQL Server 2000 uses two types of authentication: Windows Authentication and SQL Server Authentication. Each authentication type has different logon IDs.
Windows Authentication
The authentication process for Windows is as follows:
(1) Members of the SQL Server 2000 sysadmin fixed Server role must first specify to SQL Server 2000 all Windows NT or Windows 2000 accounts or groups that are allowed to connect to SQL Server 2000. When using Windows authentication, you do not need to specify a logon ID or password when connecting to SQL Server 2000. The user's access permissions to SQL Server 2000 are controlled by Windows NT or Windows 2000 accounts or groups. authentication is required when you log on to the Windows operating system on the client.
(2) After the client successfully logs on to Windows using a valid Windows Account, Windows opens the trusted connection. Trusted connection attributes include Windows NT and Windows 2000 groups and user accounts of the client that opens the connection.
(3) when the client is connected to SQL Server 2000, the SQL Server 2000 client sends a Windows trusted connection request to SQL Server 2000. SQL Server 2000 obtains the user's account information from the trusted connection attribute, and match them with Windows accounts that are defined as valid SQL Server 2000 logon. If SQL Server 2000 finds a match, accept the connection. Otherwise, the connection is not accepted.
When you use Windows 2000 authentication to connect to SQL Server 2000, the Windows NT, Windows 2000 group, or user account is the Login User ID (Login ID ).
SQL Server Authentication
The SQL Server authentication process is as follows:
(1) sysadmin fixed Server role members first specify all valid SQL Server 2000 logon accounts and passwords to SQL Server 2000. These logon accounts and passwords are irrelevant to your Windows Account or network account.
(2) When connecting to SQL Server 2000, you must provide the SQL Server 2000 Logon account and password. The system will identify the user in SQL Server 2000 using the user's SQL Server 2000 login account.

Note:
SQL Server installed on Windows 98 and Windows me systems only supports SQL Server users.

To add a logon ID for Windows authentication, follow these steps:
1. Add a Windows User
Management tools -- Computer Management -- users and groups -- Right-click users -- create a user logging on to windows
2. Grant the logon permission to SQL Server
Enterprise Manager -- SQL instance -- Security -- Right-click logon -- New Logon
-- [General], click the "..." button after "name", and select the user created above
-- "Authentication" select "windows Authentication"
-- "Domain": select the user's domain
-- Select "Allow access" for "Secure Access"
-- OK
3. log on
Log out of windows and log on to the operating system with the user you created.
Then, query the analyzer and log on to the SQL server. Select "windows Authentication" for authentication during connection"
The login user is the new user.

To add a logon ID for SQL Server Authentication, follow these steps:
Enterprise Manager -- SQL instance -- Security -- Right-click logon -- New Logon
-- [General], enter the user name in "name"
-- Select "SQL server Authentication" for "authentication" and enter the user's password
-- OK. Enter the password again.

 6. Authentication Mode

SQL Server supports two authentication modes:
(1) Windows only 
In this way, only valid Windows accounts with logon permissions are allowed to access SQL Server. SQL Server users are not allowed to log on.
(2) SQL Server and Windows
In this way, the valid Windows Account and SQL Server users with logon permissions are granted to access SQL Server.

Note:
Because Windows 98 and Windows me systems only support SQL Server Authentication, you can only use the "SQL Server and Windows2000" Authentication mode.

You can set the SQL Server Authentication mode as follows:
You can set the SQL Server Authentication mode when installing SQL Server. After installing SQL Server, you can modify the SQL Server Authentication mode using the following methods:
Enterprise Manager -- Right-click SQL Server instance -- Properties
-- In [security], select "Windows only" or "SQL Server and Windows2000"
-- OK
-- Restart the SQL Server service to make the modification take effect.

 

7. solve common connection problems

 

1. "SQL Server does not exist or access is denied"

This is the most complex. There are many causes of errors and there are many aspects to check. Generally, there are several possibilities ::
1. incorrect spelling of the SQL Server name or IP address
2. Incorrect server network configuration
3. Client Network Configuration Error
To solve this problem, we generally need to follow the steps below to find out the cause of the error step by step.

============== First, check the network physical connection ================

Ping <Server IP Address/Server Name>
If the ping <Server IP address> fails, the physical connection is faulty. In this case, Check hardware devices, such as NICs, hubs, and routers.
Another possibility is that a firewall software is installed between the client and the Server. For example, the ISA Server. firewall software may block the ping and telnet responses.
Therefore, when checking for connection problems, we must temporarily disable the firewall software or open all closed ports.

If the ping <Server IP address> succeeds, the ping <Server Name> fails.
It indicates that there is a problem with name resolution. At this time, check whether the DNS service is normal.
Sometimes the client and server are not in the same LAN. At this time, the server name may not be used to identify the server. At this time, we can use the HOSTS file for name resolution,
The specific method is:
1. Use notepad to open the HOSTS file (usually in C: \ WINNT \ system32 \ drivers \ etc ).
Add a record corresponding to the IP address and server name, for example, 172.1610.24 myserver
2. configure it in the client network utility of SQL Server, which will be described in detail later.

============ Second, use the telnet command to check the running status of the SQL Server ==============

Telnet <Server IP addresses> 1433
If the command is successfully executed, you can see that the cursor keeps flashing in the upper left corner after the screen flashes. This indicates that the SQL Server is working normally and is listening for TCP/IP connection at port 1433.
If the command returns an error message "unable to open the connection", it indicates that the Server has not started the SQL Server service,
The TCP/IP protocol may not be enabled on the Server, or the Server does not listen on the default port 1433 of SQL Server.

Then, we need to check the network configuration on the server, whether the named pipe is enabled, whether the TCP/IP protocol is enabled, and so on.

SQL Server's built-in Server network tools can be used for inspection.
Click: program -- Microsoft SQL Server -- Server network usage Tool
After you open the tool, you can see which protocols are enabled on the server in "General.
In general, we enable Named Pipes and TCP/IP protocols.
Click "properties" in the TCP/IP protocol to check the default port settings of the SQK Server service.
Generally, we use the default port 1433 of SQL Server. if you select "Hide server", it means that the client cannot see this server by enumerating the server. This protects the server, but does not affect the connection.

============== Next we will go to the client to check the network configuration of the client ================

We can also use the client network tools provided by SQL Server to perform checks,
The difference is that this tool is run on the client this time.
Click: program -- Microsoft SQL Server -- client network usage Tool
After the tool is enabled, You can see which protocols are enabled on the client in the "General" item.
In general, we also need to enable the named pipe and TCP/IP protocol.
Click TCP/IP protocol and select "properties" to check the default connection port settings of the client. The port must be consistent with the server.
Click the "alias" tab and configure the alias for the server. The server alias is the name used for connection,
The server in the connection parameter is the real server name. The two can be the same or different. The alias settings are similar to those in the HOSTS file.
Through the above checks, the first error can be basically ruled out.

2. "unable to connect to the server, user xxx Login Failed"

This error occurs because SQL Server uses the "Windows only" authentication method, so you cannot use the Logon account of SQL Server (such as sa) to connect. the solution is as follows ::
1. Use the enterprise manager on the Server side and select "use Windows Authentication" to connect to SQL Server
Procedure:
In Enterprise Manager
-- Right-click your server instance (the one with the green icon)
-- Edit SQL Server Registration attributes
-- Select "use windows Authentication"
-- Select "use SQL Server Authentication"
-- Enter sa in the login name and sa in the password
-- OK
2. Set to allow SQL Server login
Procedure:
In Enterprise Manager
-- Expand "SQL Server group", right-click the name of the SQL Server
-- Select "attribute"
-- Select the "Security" tab.
-- Under "authentication", select "SQL Server and Windows ".
-- OK, and restart the SQL Server service.
In the preceding solution, if "use Windows Authentication" fails to connect to SQL Server in step 1, modify the Registry to solve the problem:
1. Click "start"-"run", enter regedit, and press enter to enter the Registry Editor.
2. Expand the registry key in sequence and browse to the following registry key:
[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer]
3. Find the name "LoginMode" on the right of the screen and double-click to edit the double-byte value.
4. Change the original value from 1 to 2 and click "OK"
5. Close Registry Editor
6. Restart the SQL Server service.
In this case, you can successfully use sa to create an SQL Server registration in the Enterprise Manager,
However, you still cannot connect to SQL Server in Windows Authentication mode.
This is because there are two default logon accounts in SQL Server:
BUILTIN \ Administrators
<Machine Name> \ Administrator deleted.
To restore these two accounts, you can use the following methods:
1. Open the Enterprise Manager, expand the server group, and then expand the server
2. Expand security, right-click logon, and click New logon"
3. In the Name box, enter BUILTIN \ Administrators
4. On the "server role" tab, select "System Administrators"
5. Click "OK" to exit
6. Use the same method to add <machine Name> \ Administrator to log on.

Note: The following registry keys ::
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ LoginMode
The value determines the Authentication mode that SQL Server will adopt.
1. indicates that the "Windows Authentication" mode is used.
2. hybrid mode (Windows Authentication and SQL Server Authentication ).

3. Prompt connection timeout
If a third error occurs, the client has found the server and can connect to the server. However, the connection time is later than the allowed time, causing an error.
This usually occurs when a user runs the enterprise manager on the Internet to register another server that is also on the Internet and has a slow connection, the above timeout error may occur. in some cases, the LAN may cause such errors.
To solve this problem, you can modify the connection timeout settings of the client.
By default, the timeout setting for registering another SQL Server through the Enterprise Manager is 4 seconds, the query analyzer is 15 seconds (this is also the reason why there is a high possibility of errors in the Enterprise Manager ).
The procedure is as follows:
Settings in Enterprise Manager:
1. In Enterprise Manager, select "Tools" from the menu, and then select "options"
2. In the displayed "SQL Server Enterprise Manager properties" window, click the "advanced" tab.
3. Enter a large number in the "Login timeout (seconds)" box on the right under "connection settings", for example, 20.
Query settings in Analyzer:
Tool -- option -- connection -- set logon timeout to a large number

8. Service

After the SQL Server is installed, many service applications are added to the system, and they work together to complete various data processing and understand the role of these services, it is conducive to better use and management of SQL Server. The specific descriptions are as follows.
Various services and functions of SQL Server 2000

Service name	File Name	Server description
MSSQLSERVER	Sqlserver.exe	The most important service in SQL Server, as long as it is started, can complete most of the database processing. It is responsible for data access, security configuration, and transaction management.
SQLSERVERAGENT	Sqlagent.exe	Schedules regularly executed activities (such as database maintenance, backup, and replication), and notifies the system administrator of problems on the server. If you do not need to do this, you can stop this service.
Microsoft Search	Mssearch.exe	The full-text retrieval function in the database allows you to query the content of data fields in full-text retrieval mode, rather than Like keyword filtering provided by general SQL syntax. This is more efficient than Like When retrieving certain keywords in fields of a large number of texts. Note that full-text retrieval configuration is required for full-text retrieval. If you do not need full-text retrieval, you can stop this service.
Distributed Transaction Coordinator (MSDTC)	Msdtc.exe	Is a Transaction Manager that allows client applications to contain multiple different data sources in a transaction. MSDTC coordinates distributed transactions committed between all servers that have been registered in the transaction to ensure that all updates on all servers are permanent, or delete all updates when an error occurs. To put it simply, if you only execute transactions in one SQL Server instance, the SQL Server can handle the tasks by itself. If you need to span other programs, it is even the execution instance of another SQL Server, all transactions must be completed through MSDTC. If the transaction is not processed across other programs, you can stop this service.