SQL SERVER 基础概念集

SQL Server 基础概念集

 

 

1、如何理解SQL Server的实例

 在项目实施过程中，不少用户会有这样的需求：要求开发一套基于SQL Server的新系统，这套系统验收通过后，要和一个原有的SQL Server系统合并，共用一个服务器，所以不能为新系统提供单独的服务器（资金上不允许为开发提供单独的服务器）。但是要保障新系统的开发而又不影响旧系统的正常运行，所以要求新系统使用单独的SQL Server服务。在SQL Server 2000以前的版本中，这样的要求是无法满足的，但在SQL Server 2000中，就可以在原有SQL Server实例的基础上，再添加一个实例来解决问题。

 所谓的“实例”，就是一个SQL Server数据库引擎。SQL Server 2000支持在同一台计算机上同时运行多个SQL Server数据库引擎实例。每个SQL Server数据库引擎实例各有一套不为其他实例共享的系统及用户数据库。应用程序连接同一台计算机上的SQL Server数据库引擎实例的方式与连接其他计算机上运行的SQL Server数据库引擎的方式基本相同。由于实例各有一套不为其他实例共享的系统及用户数据库，所以各实例的运行是独立的，一个实例的运行不会受其他实例运行的影响，也不会影响其他实例的运行。在一台计算机上安装多个SQL Server实例，就相当于把这台计算机模拟成多个数据库服务器，而且这些模拟的数据库服务器是独立且同时运行的。

 实例包括默认实例和命名实例两种。一台计算机上最多只有一个默认实例，也可以没有默认实例，默认实例名与计算机名相同，修改计算机名会同步修改默认实例名（SQL Server 7。0只能被安装为默认实例，在修改计算机名后，会导致SQL Server服务无法启动，需要执行SQL Server安装程序进行自动修复才能解决启动问题），客户端连接默认实例时，将使用安装SQL Server实例的计算机名。

 在同一台计算机上安装SQL Server 7。0和SQL Server 2000时，由于SQL Server 7。0只能安装为默认实例，所以应该先安装SQL Server 7。0，将SQL Server 2000安装为命名实例。或者在安装SQL Server 2000的时候，指定安装为命名实例。

 

 一台计算机上可以安装多个命名实例，客户端连接命名实例时，必须使用以下计算机名称与命名实例的实例名组合的格式：

computer_name/instance_name

 

2、客户端如何与SQL Server交互

 从客户端发送Transact-SQL语句到SQL Server服务器，到服务器处理完成并将结果返回给客户端的完整过程如下：

（1）用于SQL Server的Microsoft OLE DB提供程序、SQL Server ODBC驱动程序或DB-Library DLL生成TDS数据包。

（2）TDS数据包被传递给SQL Server客户端Net-Library。

（3）客户端Net-Library将TDS数据包封装为网络协议数据包。

（4）在服务器上，服务器Net-Library接收网络协议数据包。并且由ODS（Open Data Services）负责析取TDS数据包，并将结果传递给关系数据库服务器。

（5）关系数据库服务器负责处理TDS数据包的内容。

（6）如果需要将结果返回给客户端时，则由ODS将处理结果生成TDS数据包，并传递给服务器Net-Library。

（7）服务器Net-Library负责TDS数据包封装为网络协议数据包，然后传递回客户端Net-Library。

（8）客户端Net-Library将网络协议数据还原成TDS数据包，并转给上层的数据库接口，例如Microsoft OLE DB提供程序、SQL Server ODBC驱动程序或DB-Library DLL等。

（9）Microsoft OLE DB提供程序、SQL Server ODBC驱动程序或DB-Library DLL将TDS数据包还原为应用程序可以接受的“结果集”。

无论存取SQL Server的应用程序是否与SQL Server实例在同一台单机上，应用程序与SQL Server实例通信都会经过上述过程。

 

3、TDS、NET-Library和网络协议

 TDS（表格格式数据流协议）：SQL Server使用一个称为表格格式数据流 (TDS) 的应用程序级协议进行客户端应用程序与 SQL Server 之间的通讯。TDS 数据包封装在 Net-Library 所使用的协议栈生成的数据包中。

 例如，如果使用的是 TCP/IP 套接字 Net-Library，TDS 数据包就封装在基础协议的 TCP/IP 数据包中。将结果集发送回应用程序的数据包的内容取决于是否在传递给数据库引擎的 Transact-SQL 语句中指定了 FOR XML：

 如果没有指定 FOR XML，数据库引擎将关系结果集发送回应用程序。TDS 数据包中包含结果行集，每行包括一列或多列，就像在 SELECT 语句的选择列表中指定的那样。

 如果指定了 FOR XML，数据库引擎将 XML 文档以流的形式返回给应用程序。在 TDS 数据包中，XML 文档被格式化为单个的、长 Unicode 值，每个数据包的大小约为 4 KB。

 可以配置 SQL Server 数据包大小，即 TDS 数据包大小。大多数客户端上的 TDS 数据包大小默认为 4 KB（DB-Library 应用程序默认为 512 字节），经过测试证明，在几乎所有的方案中，这是最佳的 TDS 数据包大小。TDS 数据包大小可以比基础协议中的数据包大。如果是这种情况，发送数据包的计算机上的协议栈自动将 TDS 数据包分解成适合协议数据包大小的单元，客户端计算机上的协议栈则在接收计算机上将 TDS 数据包重新组合在一起。

 Net-Library和网络协议： SQL Server使用一个称为 Net-Library 的动态链接库 (DLL) 与特定的网络协议通讯。匹配的 Net-Library 对在客户端和服务器计算机上必须是活动的，才能支持所需的网络协议。

 例如，若要使客户端应用程序能通过 TCP/IP 与特定的 SQL Server 实例通讯，必须在客户计算机上将客户端 TCP/IP 套接字 Net-Library (DBNETLIB.dll) 配置为与那台服务器连接，同时服务器 TCP/IP 套接字 Net-Library (SSNETLIB.dll) 必须在服务器计算机上监听。

 对于一对 Net-Library，本身并不支持客户端/服务器连接。客户端和服务器也必须都运行支持 Net-Library 的协议栈。

 例如，如果服务器 TCP/IP 套接字 Net-Library 在服务器计算机上监听，并且客户计算机上的客户端 TCP/IP 套接字 Net-Library 被配置成与那台服务器连接，那么，客户端只有在两台计算机上都安装了 TCP/IP 协议栈时才能连接到服务器上。

 Net-Library由SQL Server安装程序安装，而网络协议由操作系统安装程序安装。

 

4、服务器标识

 当应用程序需要访问某个SQL Server实例的时候，必须要知道被服务器的唯一标识。这就类似于找一个人，您必须知道要找的人叫什么名字，或者其他一些能在你查找的范围内，能唯一标识这个人的标志，比如身份证编号。

 下面通过一些例子来说明常见的可用于访问 SQL Server 服务器标识：

1、 连接本机上的 SQL Server 的默认实例有效服务器标识可以是：

 本机的计算机名称、(local)、localhost、127。0。0。1、。、不指定服务器名、本机IP地址

2、 连接本机上的 SQL Server 的命名实例有效服务器标识可以是：

 本机的计算机名称/实例名、。/实例名、本机IP地址/实例名

3、 连接其他电脑上的 SQL Server 的默认实例有效服务器标识可以是：

 服务器的计算机名称、服务器的IP地址

4、 连接其他电脑上的 SQL Server 的命名实例有效服务器标识可以是：

 服务器的计算机名称/实例名、服务器的IP地址/实例名

 其他说明：

1、   可以通过在 SQL Server 实例名称的开始处添加 tcp：、np：、lpc：、或 rpc： 来指定您的连接字符串中的协议，例如：tcp：jack

2、   可以将一个端口号添加到连接字符串（例如 MyServer/MyInstance，1433），用于指定 SQL Server 实例正在侦听的特定端口

3、   可以通过 SQL Server 的客户端网络实用工具，为要连接的 SQL Server 指定别名，以便使用简短且友好的名称来访问 SQL Server。

 

5、登录ID与身份验证

 登录ID是账户标识符，用来控制对任何SQL Server 2000系统的访问权限。SQL Server 2000只有在验证了指定的登录ID有效后，才完成连接。这种登录验证称为身份验证。

 录属性中，有一个是默认数据库。当一个登录连接到SQL Server时，这个默认数据库就变成该连接的当前数据库，除非该连接请求指定另一个数据库作为当前数据库。

 用于登录SQL Server的账户在SQL Server中（使用SQL Server身份验证）创建，或者在Windows NT 4。0或Windows® 2000中创建并被授予登录权限（使用Windows身份验证）。

 SQL Server实例必须验证每个连接请求所提供的登录ID是否具有访问该实例的权限。这一过程称为身份验证。SQL Server 2000使用两类身份验证：Windows身份验证和SQL Server身份验证。每一种身份验证都有不同类别的登录ID。

 Windows 身份验证

 Windows身份验证的过程如下：

（1）SQL Server 2000 sysadmin固定服务器角色成员必须首先向SQL Server 2000指定所有允许连接到SQL Server 2000的Windows NT或Windows 2000账户或组。当使用Windows身份验证时，在连接到SQL Server 2000时不必指定登录ID或密码。用户对SQL Server 2000的访问权限由Windows NT或Windows 2000账户或组控制，当登录到客户端上的Windows操作系统时需接受身份验证。

（2）客户端使用有效的Windows账户成功登录Windows后，Windows打开信任连接。信任连接的属性包括打开连接的客户端的Windows NT和Windows 2000组及用户账户。

（3）当客户端连接到SQL Server 2000时，SQL Server 2000客户端软件向SQL Server 2000请求Windows信任连接，SQL Server 2000从信任连接属性中得到用户的账户信息，并将它们与定义为有效SQL Server 2000登录的Windows账户相匹配。如果SQL Server 2000找到匹配的项，则接受这个连接，否则不接受这个连接。

 当使用Windows 2000身份验证连接到SQL Server 2000时，Windows NT或Windows 2000组或用户账户就是登录的用户标识（登录ID）。

 SQL Server身份验证

 SQL Server身份验证的过程如下：

（1）sysadmin固定服务器角色成员首先向SQL Server 2000指定所有有效的SQL Server 2000登录账户和密码。这些登录账户和密码与用户的Windows账户或网络账户无关。

（2）当连接到SQL Server 2000时，用户必须提供SQL Server 2000登录账户和密码。系统将通过用户的SQL Server 2000登录账户在SQL Server 2000中标识用户。

 注意：

 Windows 98、Windows me系统中安装的SQL Server仅支持SQL Server用户。

 添加用于Windows身份验证登录ID的方法如下：

1、 加Windows用户

    管理工具--计算机管理--用户和组--右键用户--新建用户--建立一个登陆windows的用户

2、 在SQL Server中授予其登录的权限

企业管理器--SQL实例--安全性--右键登录--新建登录

--[常规]项，点"名称"后的"。。。"按钮，选择上面建立的用户

--"身份验证"选择"windows身份验证"

--"域"选择用户所在的域

--"安全性访问"选择"允许访问"

--确定

3、登录

注销windows，用你新建的用户登陆操作系统。

然后查询分析器登陆SQL，连接时的身份验证选择"windows身份验证"

这时登陆的用户就是新建的用户了

    添加用于SQL Server身份验证登录ID的方法如下：

企业管理器--SQL实例--安全性--右键登录--新建登录

--[常规]项，"名称"中输入用户名

--"身份验证"选择"sql server身份验证"，输入该用户的密码

--确定，再次输入密码

 

6、身份验证模式

    SQL Server支持两种身份验证模式：

（1） 仅Windows

    该种方式仅允许授予登录权限的合法Windows账户访问SQL Server，SQL Server用户将被禁止登录。

（2）SQL Server 和 Windows

    该种方式授予登录权限的合法Windows账户和SQL Server用户访问SQL Server。

    注意：

    由于Windows 98、Windows me系统仅支持SQL Server身份验证，因此只能使用“SQL Server 和 Windows2000 ”的身份验证模式

    设置SQL Server身份验证模式的方法如下：

    可以在安装SQL Server时，设置SQL Server身份验证模式，在SQL Server安装完成后，可以通过下面的方法修改SQL Server身份验证模式：

企业管理器--右键SQL Server实例--属性

--[安全性]项中，选择“仅Windows”或者“SQL Server 和 Windows2000”

--确定

--重新启动SQL Server服务，使修改生效

 

7、解决常见的连接问题

一、"SQL Server 不存在或访问被拒绝"

    这个是最复杂的，错误发生的原因比较多，需要检查的方面也比较多，一般说来，有以下几种可能性：

1、SQL Server名称或IP地址拼写有误

2、服务器端网络配置有误

3、客户端网络配置有误

    要解决这个问题，我们一般要遵循以下的步骤来一步步找出导致错误的原因：

 

 ============= 首先，检查网络物理连接 =============

ping <服务器IP地址/服务器名称>

    如果 ping <服务器IP地址> 不成功，说明物理连接有问题，这时候要检查硬件设备，如网卡，HUB，路由器等。还有一种可能是由于客户端和服务器之间安装有防火墙软件造成的，比如 ISA Server。防火墙软件可能会屏蔽对ping，telnet 等的响应。因此在检查连接问题的时候，我们要先把防火墙软件暂时关闭，或者打开所有被封闭的端口。

    如果ping <服务器IP地址> 成功而ping <服务器名称> 失败，则说明名字解析有问题，这时候要检查 DNS 服务是否正常。

    有时候客户端和服务器不在同一个局域网里面，这时候很可能无法直接使用服务器名称来标识该服务器，这时候我们可以使用HOSTS文件来进行名字解析，具体的方法是：

1、使用记事本打开HOSTS文件（一般情况下位于C：/WINNT/system32/drivers/etc）。

    添加一条IP地址与服务器名称的对应记录，如：172。168。10。24 myserver

2、或在 SQL Server 的客户端网络实用工具里面进行配置，后面会有详细说明。

 

============= 其次，使用 telnet 命令检查SQL Server服务器工作状态 =============

telnet <服务器IP地址> 1433

    如果命令执行成功，可以看到屏幕一闪之后光标在左上角不停闪动，这说明 SQL Server 服务器工作正常，并且正在监听1433端口的 TCP/IP 连接

    如果命令返回"无法打开连接"的错误信息，则说明服务器端没有启动 SQL Server 服务，也可能服务器端没启用TCP/IP 协议，或者服务器端没有在 SQL Server 默认的端口1433上监听。

    ==接着，我们要到服务器上检查服务器端的网络配置，检查是否启用了命名管道。是否启用了 TCP/IP 协议等等==

    可以利用 SQL Server 自带的服务器网络使用工具来进行检查。

点击：

程序 -- Microsoft SQL Server -- 服务器网络使用工具

打开该工具后，在"常规"中可以看到服务器启用了哪些协议。

一般而言，我们启用命名管道以及 TCP/IP 协议。

点中 TCP/IP 协议，选择"属性"，我们可以来检查 SQK Server 服务默认端口的设置

    一般而言，我们使用 SQL Server 默认的1433端口。如果选中"隐藏服务器"，则意味着客户端无法通过枚举服务器来看到这台服务器，起到了保护的作用，但不影响连接。

 

============= 接下来我们要到客户端检查客户端的网络配置 =============

    我们同样可以利用 SQL Server 自带的客户端网络使用工具来进行检查，所不同的是这次是在客户端来运行这个工具。

点击：

程序 -- Microsoft SQL Server -- 客户端网络使用工具

打开该工具后，在"常规"项中，可以看到客户端启用了哪些协议。

一般而言，我们同样需要启用命名管道以及 TCP/IP 协议。

    点击 TCP/IP 协议，选择"属性"，可以检查客户端默认连接端口的设置，该端口必须与服务器一致。

    单击"别名"选项卡，还可以为服务器配置别名。服务器的别名是用来连接的名称，连接参数中的服务器是真正的服务器名称，两者可以相同或不同。别名的设置与使用HOSTS文件有相似之处。

    通过以上几个方面的检查，基本上可以排除第一种错误

 

二、"无法连接到服务器，用户xxx登陆失败"

    该错误产生的原因是由于SQL Server使用了"仅 Windows"的身份验证方式， 因此用户无法使用SQL Server的登录帐户（如 sa ）进行连接。解决方法如下所示：

1、在服务器端使用企业管理器，并且选择"使用 Windows 身份验证"连接上 SQL Server

操作步骤：

在企业管理器中

--右键你的服务器实例(就是那个有绿色图标的)

--编辑SQL Server注册属性

--选择"使用windows身份验证"

--选择"使用SQL Server身份验证"

--登录名输入：sa，密码输入sa的密码

--确定

2、设置允许SQL Server身份登录

操作步骤：

在企业管理器中

--展开"SQL Server组"，鼠标右键点击SQL Server服务器的名称

--选择"属性"

--再选择"安全性"选项卡

--在"身份验证"下，选择"SQL Server和 Windows "。

--确定，并重新启动SQL Server服务。

    在以上解决方法中，如果在第 1 步中使用"使用 Windows 身份验证"连接 SQL Server 失败，那就通过修改注册表来解决此问题：

1、点击"开始"-"运行"，输入regedit，回车进入注册表编辑器

2、依次展开注册表项，浏览到以下注册表键：

[HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/MSSQLServer/MSSQLServer]

3、在屏幕右方找到名称"LoginMode"，双击编辑双字节值

4、将原值从1改为2，点击"确定"

5、关闭注册表编辑器

6、重新启动SQL Server服务。

    此时，用户可以成功地使用sa在企业管理器中新建SQL Server注册，但是仍然无法使用Windows身份验证模式来连接SQL Server。这是因为在 SQL Server 中有两个缺省的登录帐户：

BUILTIN/Administrators

<机器名>/Administrator 被删除。

要恢复这两个帐户，可以使用以下的方法：

1、   打开企业管理器，展开服务器组，然后展开服务器

2、   展开"安全性"，右击"登录"，然后单击"新建登录"

3、   在"名称"框中，输入 BUILTIN/Administrators

4、   在"服务器角色"选项卡中，选择"System Administrators"

5、   点击"确定"退出

6、   使用同样方法添加 <机器名>/Administrator 登录

说明：以下注册表键：

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/MSSQLServer/MSSQLServer/LoginMode 的值决定了SQL Server将采取何种身份验证模式。

1、表示使用"Windows 身份验证"模式

2、表示使用混合模式（Windows 身份验证和 SQL Server 身份验证）

 

三、提示连接超时

    如果遇到第三个错误，一般而言表示客户端已经找到了这台服务器，并且可以进行连接，不过是由于连接的时间大于允许的时间而导致出错。

    这种情况一般会发生在当用户在Internet上运行企业管理器来注册另外一台同样在Internet上的服务器，并且是慢速连接时，有可能会导致以上的超时错误。有些情况下，由于局域网的网络问题，也会导致这样的错误。

    要解决这样的错误，可以修改客户端的连接超时设置。

    默认情况下，通过企业管理器注册另外一台SQL Server的超时设置是 4 秒，而查询分析器是 15 秒（这也是为什么在企业管理器里发生错误的可能性比较大的原因）。

具体步骤为：

企业管理器中的设置：

1、   在企业管理器中，选择菜单上的"工具"，再选择"选项"

2、   在弹出的"SQL Server企业管理器属性"窗口中，点击"高级"选项卡

3、   在"连接设置"下的"登录超时（秒）"右边的框中输入一个比较大的数字，如 20。

查询分析器中的设置：

工具 -- 选项 -- 连接 -- 将登录超时设置为一个较大的数字

 

8、服务

    当安装好SQL Server后，其实是在系统中加入了许多服务应用程序，彼此分工合作，配合完成各种数据处理，了解这些服务的作用，会有利于更好的使用和管理SQL Server。具体的说明如下表所示。

 

SQL Server 2000的各种服务和功能说明

服 务 名 称	文  件  名	服 务 说 明
MSSQLSERVER	Sqlserver.exe	SQL Server中最重要的服务，只要启动了它，就可以完成大部分的数据库处理。它负责数据存取、安全配置、事务管理等
SQLSERVERAGENT	Sqlagent.exe	负责调度定期执行的活动（如数据库维护、备份、复制等），以及通知系统管理员服务器所发生的问题。如果不需要做这些处理工作，可以停止此服务
Microsoft Search	Mssearch.exe	提供数据库内全文检索的功能，让用户可以针对数据字段的内容以全文检索的方式查询，而非一般SQL语法提供的Like关键字过滤。这对于在大量文本的字段中检索某些关键字的情况下，比Like更有效率。需要注意的是，要进行全文检索的话，需要进行全文检索配置。如果没有全文检索的需要，可以停止此服务
Distributed Transaction Coordinator（MSDTC）	Msdtc.exe	是一个事务管理器，它允许客户端应用程序在一个事务中包含多个不同的数据源。MSDTC协调在所有已在事务中登记的服务器间提交的分布式事务，以确保所有服务器上的全部更新为永久性的，或在发生错误时删除所有更新。简单地说，如果只在一个SQL Server实例内执行有事务的工作，则SQL Server自己可以处理，如果需要跨到其他的程序，就算是另一个SQL Server的执行实例，都需要通过MSDTC来协调完成事务。如果没有跨到其他程序的事务处理，可以停止此服务