mcdba2000学习笔记（70－228）sql server 2000 概述（1）
yy.net
Chapter 1 Overview of SQL Server 2000
Lesson 1: What Is SQL Server 2000?
The SQL Server 2000 Environment
1、 SQL Server 2000是传统的C/S的RDBMS，包括瘦客户端和服务器部分
2、 一般的访问模型
a) 客户使用客户端应用程序访问SQL Server
b) 客户通过客户端应用程序访问IIS，IIS再访问SQL Server。
SQL Server 2000 Components
1、 SQL Server 2000 Relational Database Engine
a) 实现数据的存储和管理，其核心是一些关系表，管理包括保证数据的可靠性，数据安全，冗余等等。
b) 设计的目标是为了OLTP(在线事务处理)，OLAP（在线事务分析）
2、 SQL Server 2000 Analysis Services
a) 作用是分析存储在数据仓库/商店中的数据
b) 某些OLTP可能需要很长时间，这时可以让SQL Server周期性摘要，存储信息（存储的信息称作数据仓库（warehouse），子集称作数据商店（ware mart））；用分析服务对数据仓库/商店进行分析，生成多维立方体，加快商业决策过程。
Application Support
可以使用多种方法开发应用程序访问SQL Server
1、 使用SQL/Transact SQL语句访问SQL Server，返回数据集表
2、 使用Transact SQL或Xpath访问SQL Server，返回XML
3、 使用Windows通用数据接口ADO，OLE-DB，ODBC发送T-SQL or Xpath；HTTP->T-SQL or Xpath
4、 MDX-> Analysis Services
5、 使用以下API对SQL Server进行管理，维护；数据库，数据仓库的创建等等
a) SQL Distributed Management Objects (SQL-DMO)―一组COM组件，封装了数据库的管理
b) Decision Support Objects (DSO) －一组COM组件，封装了数据分析的管理
c) Windows Management Instrumentation (WMI)—提供对OS，设备，服务的管理
Additional Components（其他组件，通常DBA使用上面两个组件，程序开发者使用下面两个组件）
1、 SQL Server 2000 Data Transformation Services (DTS)：
a) 提供数据转换功能，从一个数据源接收数据，执行转换，并存储到另一个数据源
b) 支持多种数据源：SQL Server, Oracle, Informix, DB2 and Microsoft Access databases, Microsoft Excel spreadsheets, and SQL Server multidimensional cubes
c) 使用DTS可以方便，自动的创建数据仓库，也可以为OLTP创建初始数据库
2、 SQL Server 2000 Replication
a) 保持数据同步或合并数据库
3、 SQL Server 2000 English Query
a) 提供一种以自然语言查询的方式
b) 自动内部转换自然语言到T-SQL
4、 Meta Data Services
a) 一组服务，提供了Meta Data的存储和管理
b) Meta Data是描述数据表属性或数据仓库对象的一些参数的集合
c) 一些工具可以交换Meta Data信息
SQL Server 2000 Editions
1、 SQL Server 2000 Enterprise Edition
2、 SQL Server 2000 Standard Edition
a) 主要缺少对大型数据库的支持，如集群等
3、 SQL Server 2000 Personal Edition
a) 不支持事务复制，全文检索（98，me）
b) 5进程限制
4、 SQL Server 2000 Windows CE Edition
a) 通过一组DLL作为OLE-DB Provider的方式运作
b) 可以通过Remote Data Access (RDA)和桌面版SQL Server实现数据交换
5、 SQL Server 2000 Developer Edition
a) 开发版，图形工具不支持语言设置，仅限于开发使用
6、 SQL Server 2000 Enterprise Evaluation Edition
7、 SQL Server 2000 Desktop Engine
a) 随应用程序发布，无管理工具，如果要管理则需要使用APIs
b) 相当于个人版的简化
yy.net
Installing SQL Server Editions on Various Windows Operating Systems
Table 1.1 Operating Systems Supported by SQL Server Editions
|
Operating System |
Enterprise |
Standard |
Personal |
Enterprise Evaluation |
Desktop Engine |
Developer |
Windows CE |
|
Windows 2000 Data Center |
Supported |
Supported |
Supported |
Supported |
Supported |
Supported |
N/A |
|
Windows 2000 Advanced Server |
Supported |
Supported |
Supported |
Supported |
Supported |
Supported |
N/A |
|
Windows 2000 Server |
Supported |
Supported |
Supported |
Supported |
Supported |
Supported |
N/A |
|
Windows 2000 Professional |
N/A |
N/A |
Supported |
Supported |
Supported |
Supported |
N/A |
|
Windows NT 4.0 Server, Enterprise Edition |
Supported |
Supported |
Supported |
Supported |
Supported |
Supported |
N/A |
|
Windows NT 4.0 Server |
Supported |
Supported |
Supported |
Supported |
Supported |
Supported |
N/A |
|
Windows NT 4.0 Workstation |
N/A |
N/A |
Supported |
Supported |
Supported |
Supported |
N/A |
|
Windows, Millennium Edition |
N/A |
N/A |
Supported |
N/A |
Supported |
N/A |
N/A |
|
Windows 98 |
N/A |
N/A |
Supported |
N/A |
Supported |
N/A |
N/A |
|
Windows CE |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
Supported |
Integration with Windows 2000 and Windows NT 4.0
Windows Authentication
1、可以用OS的用户认证，代替SQL Server的用户验证，增强安全性
Memory Management
1、 动态申请/释放内存
2、 使用Address Windowing Extensions (AWE) API技术，支持大内存（64G，DC；8G，AD）
Active Directory
1、 可以在AD中发布SQL Server的信息（SQL Server的名字，位置；数据库的名字，位置，大小；数据仓库/商店）
2、 安全账号的功能：即访问多个SQL Server时，使用同一个账号
3、 支持SSL加密数据传输
Failover Clustering
Microsoft Distributed Transaction Coordinator（DTC）
1、 一个事务同时在多个SQL Server实例上运行
SMP
Event Logs
System Monitor Counters
yy.net
Lesson 2: What Are the SQL Server 2000 Components?
Server Components
Table 1.2 Server Components and Their Functions
|
Server Component |
Description |
|
SQL Server service |
MSSQLServer service implements the SQL Server 2000 database engine. There is one service for each instance of SQL Server 2000. |
|
Microsoft SQL Server 2000 Analysis Services service |
MSSQLServerOLAPService implements SQL Server 2000 Analysis Services. There is only one service, regardless of the number of instances of SQL Server 2000. |
|
SQL Server Agent service |
SQLServerAgent service implements the agent that runs scheduled SQL Server 2000 administrative tasks. There is one service for each instance of SQL Server 2000. |
|
Microsoft Search service |
Microsoft Search implements the full-text search engine. There is only one service, regardless of the number of instances of SQL Server 2000. |
|
Microsoft (MS DTC) service |
Distributed Transaction Coordinator manages distributed transactions between instances of SQL Server 2000. There is only one service, regardless of the number of instances of SQL Server 2000. |
Client-Based Administration Tools and Utilities
Graphical Tools
Table 1.3 Graphical Tools in SQL Server 2000
|
Graphical Tool |
Description |
|
SQL Server Enterprise Manager |
The primary server and database administration tool, it provides a Microsoft Management Console (MMC) snap-in user interface. |
|
SQL Query Analyzer |
Used for creating and managing database objects and testing Transact-SQL statements, batches, and scripts interactively. |
|
SQL Profiler |
Used to monitor and capture selected SQL Server 2000 events for analysis and replay. Supports C2 security-level auditing. |
|
SQL Server Service Manager |
A taskbar application used to start, stop, pause, or modify SQL Server 2000 services. |
|
Client Network Utility |
Used to manage the client Net-Libraries and define server aliases containing custom server connection parameters, if needed. |
|
Server Network Utility |
Used to manage the server Net-Libraries, including enabling SSL encryption. |
Command-Prompt Utilities
Table 1.4 Command-Prompt Utilities in SQL Server 2000
|
Command-Prompt Utility |
Description |
|
Osql |
This utility allows you to query an instance of SQL Server 2000 interactively using Transact-SQL statements, system procedures, and script files. This utility replaces Isql, which was used with editions of SQL Server before SQL Server 7.0. |
|
Scm |
This utility (Service Control Manager) is used to start, stop, pause, install, delete or modify SQL Server 2000 services. It also can start, stop, or pause SQL Server running as an application. |
|
Sqldiag |
This utility gathers and stores diagnostic information to expedite and simplify information gathering by Microsoft Product Support Services. |
|
Bcp |
This utility copies data between an instance of SQL Server 2000 and a data file in a user-specified format. |
|
Dtsrun |
This utility executes packages created using DTS. |
|
Sqlmaint |
This utility performs a specified set of maintenance operations on one or more databases. These include performing DBCC consistency checks, backing up data and transaction log files, updating distribution statistics, and rebuilding indexes. |
yy.net
Relational Database Application Programming Interfaces
主要包括OLE-DB和ODBC
Net-Libraries
1、 OLE-DB或ODBC通过客户端的Net-Libraries访问服务器端的Net-Libraries
2、 Net-Libraries封装访问请求到底层网络协议，可以是本机，也可以是LAN，还可以使用SSL加密通信
Table 1.5 Net-Libraries Employable with SQL Server 2000
|
Net-Library |
Description |
|
Shared memory |
Used to connect to SQL Server 2000 on the same computer using a segment of memory. This is one of the default protocols for SQL Server 2000. |
|
Named pipes |
Used to connect to SQL Server 2000 using named pipes. A pipe is a file-system mechanism used for communication between processes. This is one of the default protocols for SQL Server 2000. |
|
TCP/IP Sockets |
Used to connect to SQL Server 2000 using TCP/IP. This is one of the default protocols for SQL Server 2000. |
|
NWLink IPX/SPX |
Used in the Novell network environment, primarily legacy Novell environments that do not support TCP/IP. |
|
VIA GigaNet SAN |
Used to support the new, high-speed SAN technology on GigaNet's cLAN server farm network. |
|
Multiprotocol |
Supports any available communication method between servers using Windows NT RPCs over any available network protocol. In earlier versions of SQL Server, this Net-Library was required to enable encryption and support Windows authentication. Today, it is mainly used for backward compatibility. |
|
AppleTalk ADSP |
Used in the Macintosh and Apple network environment. ADSP enables Apple Macintosh clients to connect to SQL Server 2000 using native AppleTalk. |
|
Banyan VINES |
Used in the Banyan VINES network environment. This protocol runs at the SQL Server 7.0 level of functionality for clients and servers running Windows NT 4.0 and will not be further enhanced. |
Open Data Services
1、Server Net-Libraries通过Open Data Services访问数据库引擎
Client-Server Communication
Internet Applications
SQL Server Books Online
Lesson 3: What Is the Relational Database Architecture?
1、 从物理上看数据库包括一个或多个文件存储在一个或多个磁盘上，它只对DBA可视，对用户而言是透明的。DBA负责数据库的物理优化。
2、 从逻辑上看，数据库是一些组件，如表，视图，存储过程，数据库设计者负责数据库的逻辑优化。
System and User Databases
Table 1.6 System Databases in SQL Server 2000
|
System Database |
Description |
|
master |
Records all of the system-level information for a SQL Server 2000 system, including all other databases, login accounts, and system configuration settings. |
|
tempdb |
Stores all temporary tables and stored procedures created by users, as well as temporary worktables used by the relational database engine itself. |
|
model |
Serves as the template that is used whenever a new database is created. |
|
msdb |
SQL Server Agent uses this system database for scheduling alerts and jobs, and recording operators. |
Physical Structure of a Database
Extents and Pages
1、 Extents（区域）是基本的数据分配单元（64k），每个Extents包括8个连续的8k的Pages组成；有两种Extents：唯一Extents，只属于一个对象；共享Extents，由多个（up to 8）对象共享。
2、 Pages（页）是基本的数据存储单位，大小是8k，数据以行的方式存储，一行最多8060字节，起始96字节存储系统信息（如页剩余空间）
Transaction Log Files
1、 和数据库文件关联的若干文件
Logical Structure of a Database
Table 1.7 Database Objects in SQL Server 2000
|
Database Object |
Description |
|
Tables |
A table generally consists of columns and rows of data in a format similar to that of a spreadsheet. Each row in the table represents a unique record, and each column represents a field within the record. A data type specifies what type of data can be stored in a column. |
|
Views |
Views can restrict the rows or the columns of a table that are visible, or can combine data from multiple tables to appear like a single table. A view can also aggregate columns. |
|
Indexes |
An index is a structure associated with a table or view that speeds retrieval of rows from the table or view. Table indexes are either clustered or nonclustered. Clustering means the data is physically ordered based on the index key. |
|
Keys |
A key is a column or group of columns that uniquely identifies a row (PRIMARY KEY), defines the relationship between two tables (FOREIGN KEY), or is used to build an index. |
|
User-defined data types |
A user-defined data type is a custom data type, based on a predefined SQL Server 2000 data type. It is used to make a table structure more meaningful to programmers and help ensure that columns holding similar classes of data have the same base data type. |
|
Stored procedures |
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. The procedure is used for performance optimization and to control access. |
|
Constraints |
Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing data integrity. |
|
Defaults |
A default specifies what values are used in a column in the event that you do not specify a value for the column when you are inserting a row. |
|
Triggers |
A trigger is a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. |
|
User-defined functions |
A user-defined function is a subroutine made up of one or more Transact-SQL statements used to encapsulate code for reuse. A function can have a maximum of 1024 input parameters. User-defined functions can be used in place of views and stored procedures. |
Optimizing Logical Database Design
1、 核心是设计表及表之间的关系
2、 优化设计的过程是一个正常化（normalized）的过程，目标是要减少数据的冗余
Lesson 4: What Is the Security Architecture?
1、 SQL Server使用两级安全措施
a) 验证用户身份，决定能否连接到SQL Server
i. Windows 验证：使用Windows系统账户，有更高的安全性
ii. SQL验证：使用SQL Server内部账户，适用于98，me等无Windows验证的情况
b) 安全授权，决定用户连接到SQL Server后能执行的动作
Authentication
Windows Authentication
SQL Server Authentication
Authentication Modes
Authorization
User Accounts
1、用户登录到SQL Server后，还需要用户账户才可以访问数据库
Guest User Account
1、如果用户没有指派用户账户，则视作Guest用户，Guest用户在每个数据库上独立创建
Roles
1、角色，类似于组，用来收敛用户，委派权利yy.net
Lesson 3: Running the SQL Server 2000 Setup Program
Running the SQL Server 2000 Setup Program
Understanding Installation Types
1、 连接工具
2、 客户端工具：客户端管理工具＋连接工具＋BOOK Online等
3、 服务端＋客户端：服务端，客户端工具，连接工具，其他
Selecting a Setup Type（选C＋S会有mini，Typical，Custom的选择）
1、 选择mini和Typical会问以下问题
a) Services Accounts
b) Authentication Mode
c) Choose Licensing Mode
2、选Custom安装可以设置其他选项
a) 添加其他网络库
b) 设置TCP/IP的侦听端口（默认是1433）
c) 定义代理服务器地址
d) 安装其他开发工具
e) 安装代码例子
f) 修改collation
g) 改变默认的命名管道（默认是\\.\pipe\sql\query）
Lesson 4: Using Default, Named, and Multiple Instances of SQL Server 2000
Installing Multiple Instances of SQL Server 2000
1、 SQL 2000允许在一台主机上安装多个实例，包括两类，默认实例和命名实例，其中默认实例只能有一个，命名实例可以有多个。
2、 默认实例可以是6.5，7.0，2000，命名实例只能是2000
3、 如果是新装SQL 2000，可以在安装时选择是安装成默认实例，还是命名实例
4、 如果是升级SQL，根据默认实例的版本不同有不同选择
a) 如果默认实例是2000，可以直接安装命名实例
b) 如果默认实例是7.0，可以选择升级默认实例到2000，或安装命名实例
c) 如果默认实例是6.5，可以安装2000的默认实例，或安装命名实例；6.5和2000用Vswitch切换，且6.5必须安装sp5及以上patch
5、所谓命名实例，就是对实例定义了一个名字，并用该名字访问之
Using Multiple Instances of SQL Server 2000 Effectively and Appropriately
1、 使用多个SQL实例的主要目的是为了调试的需要，或不同客户需要对实例有完整的控制
2、 使用多个SQL实例会增加管理负担，同时需要更多的计算能力，更多的资源
Understanding Shared Components Between Instances
Table 2.6 Shared Components in Multiple Instances of SQL Server
|
Component |
Description |
|
Client management tools and utilities (and their associated registry keys) |
All instances of SQL Server on a computer share the same version of all client management tools and utilities (and their associated registry keys). These tools and utilities work with all instances. The version of the tools and utilities will be the version from the first SQL Server 2000 version installed on the computer. If SQL Server 6.5 or 7.0 is already installed on the computer that you install SQL Server 2000 on, the SQL Server 6.5 or 7.0 client management tools and utilities are upgraded to SQL Server 2000 client management tools and utilities. |
|
Books Online |
All instances of SQL Server on a computer share the same version of SQL Server Books Online and that will be the one from the first version of SQL Server 2000 installed. If instances from multiple languages are installed, Books Online will be in the language of the first SQL Server 2000 version installed. If any earlier version of SQL Server was on the computer, that version of Books Online will be upgraded. |
|
Microsoft Search service |
There is only one instance of the Microsoft Search service. |
|
Distributed Transaction Coordinator |
There is only one instance of the Distributed Transaction Coordinator service. |
Understanding Unique Components Between Instances
1、 不同实例的系统、用户数据库是完全独立的
2、 每个实例有独立的SQL SERVER和SQL SERVER AGENT服务，在默认实例上分别称作MSSQLSERVER和SQLSERVERAGENT，在命名实例中分别称作MSSQL$INSTANCE和SQLAGENT$INSTANCE
Working with Default and Named Instances of SQL Server 2000
1、 SQL 2000的默认实例和早期版本侦听相同的一组网络地址，所以早期的客户端连接组件无需升级就可以连接到SQL 2000的默认实例
如果需要连接到2000的命名实例，则早期的客户端连接组件需要升级到2000的客户端连接组件，只需要提供的信息是：SQL的计算机名，实例名yy.net
Chapter 2 Installing SQL Server 2000
Lesson 1: Planning to Install SQL Server 2000
What Is the Minimum Hardware Required?
1、 大部分实际运作情况都超过了最小配置，最小配置主要的目的是建立一个衡量的基线。
2、 考虑配置包括两个方面：使用的OS和SQL的版本
3、 其次要考虑的主要是CPU,RAM,HARD DISK
Exceeding Minimum Computer Hardware Requirements
1、 四种硬件可以显著提高SQL的执行效率，它们是RAM,CPU,HARD DISK,NET CARD
Lesson 2: Deciding SQL Server 2000 Setup Configuration Options
Determining the Appropriate User Account for the SQL Server and SQL Server Agent Services
1、 确定用户账号的类型，是本地管理员账号还是域用户账号
2、 本地管理员账号适用于单机测试环境，不能访问网络资源
3、 域用户账号可以访问网络资源，如其他的SQL SERVER,EXCHANGE等
4、 SQL会为指定的账号指派特定的特权，如可以读写特定注册表键值，读写SQL系统文件夹
Choosing an Authentication Mode
1、 包括WINDOWS认证和SQL认证
2、 WINDOWS认证即，只要用户可以登录到系统中，也就可以登录到SQL中，即所谓信任连接。
3、 SQL认证，即使用SQL内置的检查功能检查用户的名字，密码
4、 推荐使用WINDOWS认证，安全性更高，简化了账户管理
5、 98，me不支持信任连接API，必须使用SQL认证
Determining Collation Settings
1、 Collation（检查）的设置定义
a) 非Unicode的数据如何存储
b) SQL如何比较和排序Unicode和非Unicode数据
2、 通常情况下SQL的安装程序检查本地WINDOWS的本地化设置，来决定自身的设置，除非
a) SQL和WINDOWS的语言版本不一样
b) SQL参与与其他SQL Schema的复制，且其他SQL的语言版本和当前安装的不一致
3、 其次，设置Collation是为了和以前版本的SQL兼容（以前版本的SQL使用了三个属性描述该内容：代码页，字符排序，Unicode Collation）
4、 Collation描述三方面的属性：
a) 非Unicode的代码页
b) 非Unicode的分类排序
c) Unicode的分类排序
5、 Collation的设置可以在不同级别设置
a) 系统级：所有的数据库/对象都会受该设置影响
b) 数据库/对象级：设置的数据库/对象受影响
Selecting Network Libraries
1、 SQL使用Network Libraries在网间通讯，包括客户端Network Libraries和服务器端Network Libraries。
2、 Network Libraries是一些DLL，使用的IPC（进程通讯）的方式（如共享内存，命名管道等）
3、 Network Libraries包括多种网络底层协议的Network Libraries，例如TCP/IP，IPX等
4、 SQL的服务器端监控所有经配置的Network Libraries，侦测是否有客户端请求访问；默认情况下SQL服务器端安装并配置了TCP/IP和命名管道的Network Libraries。其他Network Libraries安装但未配置，要配置其他Network Libraries，可以使用服务器网络配置工具
5、 SQL客户端可以使用多个Network Libraries访问服务器端，默认使用TCP/IP作为主Network Libraries，命名管道作为第二Network Libraries，可以使用客户端网络工具修改顺序
6、 可以配置服务器端网络工具配置SSL加密（针对所有Network Libraries，数据通讯加密）
Deciding on a Client Licensing Mode
1、 包括Per-Process和Per-Site许可模式
2、 Per-Process安装于服务器端（基于每个处理器），适用于用户数较多的情况
3、 Per-Site，在需要访问SQL的客户端安装许可证
yy.net
Lesson 5: Performing Unattended and Remote Installations of SQL Server 2000
Performing an Unattended Installation of SQL Server 2000
1、 一般语法：
Start /Wait D:\X86\Setup\Setupsql.exe k=SMS -s -m -SMS -f1 "C:\Setup.iss"
2、 setup.iss是安装初始文件，包括安装SQL所要回答的问题
Creating Setup Initialization Files for SQL Server 2000
1、 创建安装初始文件有三种方式
a) 使用SQL的安装程序，在安装过程中选择“高级选项”，其中有记录.iss文件的选项，选中该选项，选择需要的安装选项，执行普通的交互式安装（SQL并不会真实安装），记录的文件放在\WINNT中
b) 使用SQL自带的三个.iss文件
|
Setup Initialization File Name |
Calling Batch File Name |
Type of Installation |
|
Sqlins.iss |
Sqlins.bat |
Typical installation of SQL Server 2000 |
|
Sqlcli.iss |
Sqlcli.bat |
Installation of client tools only |
|
Sqlcst.iss |
Sqlcst.bat |
Custom installation of SQL Server 2000 including all components |
c） 修改现有的setup.iss文件，添加[SdFinish-0]小节，执行普通的交互安装，SQL会自动记录安装选项到该文件，并存放到\WINNT下
Performing a Remote Installation of SQL Server 2000
1、 就是把SQL安装到远程主机上
2、 基本过程和在本机无人值守安装类似，无非需要提供访问远程主机的合适权限，安装的位置，和源文件的位置。
3、 通过SQL的安装程序记录一个.iss文件即可。
Lesson 6: Troubleshooting a SQL Server 2000 Installation
Reviewing SQL Server 2000 Setup Program Log Files
1、 安装程序生成Sqlstp.log, Setup.log, SearchSetup.log三个日志文件，其中Sqlstp.log存放在\WINNT下，记录安装中的错误信息；Setup.log也存放在\WINNT下，记录错误信息和其他信息；SearchSetup.log记录Search Service的错误信息，存放在\WINNT\TEMP下
Accessing SQL Server 2000 Troubleshooting Information Online
1、 提供在线支持，URL是：
http://support.microsoft.com/Support/SQL/Tshooter.asp
Reviewing the SQL Server Error Log and the Windows Application Log
1、 SQL的错误日志文件：\Program Files\Microsoft SQL Server\Mssql\Log\Errorlog
2、 包括最近的六个日志
3、 命名实例的日志文件：Program Files\Microsoft SQL Server\Mssql$InstanceName\Log\Errorlog
yy.net
Lesson 2: Starting, Stopping, Pausing, and Modifying SQL Server 2000 Services
What Is the Default Configuration for Each SQL Server Service?
1、 考察SQL安装后默认服务启动及使用账号的情况
2、 SQL安装后SQL SERVER SEVICE自动启动，用户身份是安装时使用的域用户账号
3、 SQL安装后SQL AGENT SEVICE未启动，用户身份是安装时使用的域用户账号
4、 MS DTC，MS SEARCH服务自动启动，使用身份是本地系统账号
5、 MSSQLServerAdHelper和AD通讯有关，在12章讨论
Starting, Stopping, and Pausing SQL Server 2000 Services
1、 有多种工具可以对SQL SERVER等服务进行控制，包括启动，暂停，停止，是否开机启动服务等
2、 工具包括：
a) SQL SERVER SERVICE MANAGER
b) SQL Query Analyzer and SQL Profiler(如果服务停止，则在连接时启动)
c) SQL Server Enterprise Manager
d) Services MMC
e) NET command
Changing the SQL Server or SQL Server Agent Service Account after Setup
1、 可能是处于安全性的考虑，建议在安装完SQL后，建议修改服务账号
2、 只能用SQL Server Enterprise Manager修改，否则会有权限问题
3、 SQL Server Enterprise Manager可以修改SQL系统文件夹，注册表某些键值的权限，以及设置账户的某些特权（如以服务登录，页面锁定，信任委派等）。
4、 原来的服务账号仍然有权利，需要自行禁用或删除
Lesson 3: Working with Osql, SQL Query Analyzer, and SQL Server Enterprise Manager
Working with Osql
1、 Osql是主要的命令行工具，主要可以使用T-SQL，系统过程，脚本交互式查询SQL，或提交批处理和任务（jobs）
2、 Osql使用ODBC APIs，替代早期的Isql（7.0里的Osql不能访问命名实例）
3、 主要参数(区分大小写)：
a) EXIT，QUIT：退出Osql
b) –E：使用Windows认证方式
c) –U, –P：使用SQL认证方式
d) –S：要访问的SERVER\INSTANCENAME
e) –L：列出本机及LAN中所有SQL实例
f) GO：执行查询
Working with SQL Query Analyzer
1、SQL Query Analyzer提供了对象创建，T-SQL交互查询，脚本调试等功能
Working with SQL Server Enterprise Manageryy.net
Chapter 4 Upgrading to SQL Server 2000
Lesson 1: Preparing to Upgrade
Working with Multiple Versions of SQL Server on the Same Computer
1、 SQL2000可以使用两种方式支持多版本SQL在同一主机运行
a) 使用Vswitch工具
b) 使用多实例
2、 可以支持的版本包括6.5，7.0，2000，但同时只能有两个版本，且其中一个是2000
Version Switching
1、 使用Vswitch的方式可以在6.5和7.0/2000之间切换，切换为默认实例，某一时刻只能有一个默认实例，不允许多个版本或实例同时运行。
2、 Vswitch只适用于6.5和7.0/2000之间切换，不能在7.0和2000之间切换。
Named Instances
1、 可以把2000安装为命名实例，这样可以保证6.5/7.0可以正常运行，而不受影响
2、 2000以命名实例安装后，如果原来有7.0，则7.0的客户工具被升级到2000
3、 只有2000支持命名实例安装。
Choosing the Appropriate Upgrade Process and Method
SQL Server 6.5
1、 升级6.5要使用升级向导，6.5要安装sp5
2、 升级过程会升级6.5的系统配置，数据库，自动建立升级恢复
3、 可以仅升级数据库，这时使用DTS的BCP工具
4、 升级可以升级到本地硬盘，磁带机，升级到其他安装2000的主机
5、 无论何种升级，总是升级到默认实例，而不能升级到命名实例
6、 升级结束后，实际上存在两个SQL，6.5和2000，6.5不能运行了（可以用版本切换，切换到6.5）
7、 要让6.5升级后能继续运行，选择升级到其他运行2000的主机的方式。
SQL Server 7.0
1、 有两种方式升级7.0
a) 运行升级向导升级整个7.0到2000，可以升级系统文件，数据库，保留设置，此过程需要离线运作
b) 仅升级数据库，使用数据库复制向导在线升级数据库及Meta数据，支持远程复制和计划复制
Replication Issues
1、 大意是使用数据库的分发，订阅机制更新数据库
Determining Hardware and Software Requirements
SQL Server 6.5
1、 如果是6.5，NT4，本机升级：NT4需要SP5，IE5，6.5需要SP5
2、 如果是异机升级，需要6.5需要SP3
3、 升级使用默认命名管道方式（\\.\pipe\sql\query）
4、 需要用户数据库1.5倍的空间
SQL Server 7.0
1、 如果是7.0，NT4，版本升级：NT4需要SP5，IE5，7.0无需SP
2、 本机升级需要使用默认命名管道：（\\.\pipe\sql\query）
3、 数据库升级使用网络库方式，无需额外的空间
Preparing for the Actual Upgrade
升级准备工作
1、 终止用户活动，取得数据库所有文件的独占权限
2、 备份所有系统，用户数据库
3、 运行Database Console Commands (DBCC)保证数据库的一致性
4、 6.5，设置tempdb至少10M（建议25M）
5、 6.5，保证master至少有3M剩余空间
6、 6.5，验证master包括所有用户的登录信息
7、 6.5，禁用所有起始存储过程
8、 禁用所有工作（jobs）
9、 关闭数据库相关应用程序
10、停止复制，保证复制日志为空
11、有足够的剩余空间执行升级
12、保证有依从关系的数据库全部同时升级
yy.net
Chapter 3 Preparing to Use SQL Server 2000
Lesson 1: Reviewing the Results of Installation
What Files and Folders Were Added?
1、 SQL默认安装在\Program Files\Microsoft SQL Server，其中包括两个文件夹。
2、 一个是80，其中包括客户端管理工具，在线帮助等等，多个 SQL实例共享，其位置固定，不可更改。
3、 一个是\Program Files\Microsoft SQL Server，其中默认实例名字为MSSQL，命名实例的名字为MSSQL$INSTANCENAME，其位置可以更改
a) \Data存储系统和例子数据库，新建用户数据库的默认存储位置
b) \Log系统日志所在位置，包括一个当前日志，和6个最近的日志；还包括Agent的日志
c) \Bin包括当前SQL实例唯一性的应用程序和资源
What Permissions Were Set in the NTFS File System
1、 安装程序在安装过程中对MSSQL及其子文件夹设置了特殊的安全权限，只有域用户账号和本地管理员组对该文件夹有FC的权限，其他用户无任何权利。
2、 80文件夹无此设置，即默认的，普通用户有“读和执行的权限”
What Registry Keys Were Added
1、 SQL安装程序添加到注册表的键值
a) 共享文件和服务部分：
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServerADHelper
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client
b) 默认实例部分
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer
c) 命名实例部分
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$Instancename
What Permissions Were Set on Registry Keys
1、 默认只有域域用户账号和本地管理员组对下列键值有FC的权限
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Replication
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Tracking
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer
2、对于命名实例，可以参考修改名字即可
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ Microsoft SQL Server\InstanceName
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$InstanceName
2、 安装程序还会设置域管理账户在下列键值有读写权利
HKEY_LOCAL_MACHINE\SOFTWARE\Clients\Mail
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib
What Programs Were Added to the Start Menuyy.net
Lesson 2: Performing a Version Upgrade from SQL Server 7.0
Performing a Version Upgrade
1. 如果主机原来安装过SQL 7.0，2000的安装程序会侦测到，并提供升级，移除，添加组件等选项。
2. 选择升级，完成升级过程，其中会更新客户端工具，注册表信息，系统数据库等。
Performing Post-Upgrade Tasks
1、升级完毕后还有一些任务需要完成
a）出于排错的目的，可以察看错误日志，Sqlstp.log
b）重新注入全文检索目录（主要是太耗时，需要另行处理）
c）更新统计信息（主要是太耗时，需要另行处理）
d）注册服务器：7.0在新的企业管理器中没有自动注册
Manually Upgrading Meta Data Services Tables and the Repository Database
1、需要手动升级Meat Data Services Information Model――DTS升级的一部分
2、该升级修改Meta Data Services表结构，以支持新的功能，并将原来存储的数据存储到新的结构中
3、Meta Data Services自己使用的存储数据库也需要手动升级，以使用新的3.0存储引擎
Lesson 3: Performing an Online Database Upgrade from SQL Server 7.0
Performing an Online Database Upgrade
1、使用数据库复制向导完成数据库复制（升级）任务
2、启用数据库复制向导，选择源服务器，适当的账号，目标服务器，账号
3、目标服务器，可以是本地，远程，但不能是源服务器
4、选择要复制/移动的数据库，重名的数据库不能复制/移动；系统数据库不能移动
5、选择数据库存储的位置，名字
6、选择是否复制连接到数据库的对象（登录，存储过程，自定义消息等）
7、配置DTS，确定是否立即复制，计划复制
Performing Post-Upgrade Tasks
1、升级完毕后还有一些任务需要完成
a）重新注入全文检索目录（主要是太耗时，需要另行处理）
b）更新统计信息（主要是太耗时，需要另行处理）
c）删除不需要的数据库和日志文件
Lesson 4: Performing a Version Upgrade from SQL Server 6.5
Performing a Version Upgrade
1、使用SQL 2000的升级向导完成6.5的升级过程，2000必须安装为默认实例
2、执行升级向导，进入“数据和对象传输页”，选择是否进行校验，包括数据对象的校验和数据的校验。
3、进入登录页面，输入导入服务器名，导出服务器名，及相应的身份信息。（升级6.5，需要使用SQL 身份验证方式）
4、输入验证信息后，会重启相关SQL服务器（包括导入，导出服务器），以使用刚才提供的身份信息进行验证，并提示选择代码页
5、选择要升级/复制的数据库（只有model和用户数据库可以复制）
6、数据库创建页，设置数据库创建参数，创建的数据库文件保持紧缩大小，日志文件保持和6.5一致
7、系统配置页，设置要复制的系统配置，包括服务器配置，复制配置等
Troubleshooting a SQL Server 6.5 Upgrade
可能出错的原因：
1、Text is missing from the syscomments table.
2、Objects were renamed using sp_rename (the syscomments entry is not updated when the object is renamed).
3、Stored procedures were embedded within other stored procedures (no entry exists in syscomments for these stored procedures).
4、Table and views have NULL column names (the wizard cannot script these objects).
Tables were created on behalf of a user that does not have CREATE permissions.
5、A stored procedure modifies a system table or references a system table that does not exist in SQL Server 2000
6、还有一个可能是计算机名和@@SERVERNAME返回的服务器名不一致，可以使用sp_dropserver 和 sp_addserver同步两个名字
Specifying a Backward Compatibility Level for Upgraded Databases
1、6.5升级后，某些应用程序可能有问题，SQL2000可以设置一个向下兼容的层次，保证程序的正常运行。
2、运行sp_dbcmptlevel系统过程，或使用企业管理器（数据库，属性，选项，兼容级别）
3、可以兼容6.0－8.0yy.net
Lesson 2: Understanding the Transaction Log Architecture
Introducing Transaction Log Files
1、 每个数据库至少有一个事务日志文件（可以是多个），它是独立的系统文件，且仅被一个数据库所使用（通常的后缀是.LDF）。
2、 日志文件包括逻辑名（T-SQL）和物理名（OS），属性包括文件ID，初始大小，增长性，最大大小等；日志文件由一序列日志文件记录组成，log sequence number (LSN)日志顺序号标识每个日志文件记录；即使可能有多个日志文件，SQL当做一个连续的日志文件对待。
3、 SQL逻辑的把每个物理日志文件划分为多个virtual log files (VLFs)－虚拟日志文件；VLFs的大小和多少动态由物理日志文件的大小决定，但一个物理日志文件至少包括两个VLFs；每次日志文件增大时，会产生为一个独立的物理日志文件（包括自己的VLFs）；VLFs的大小和数量不能设置，SQL会尽量维护较少数量的VLFs。
4、 建议创建一个适当大小且合理增长大小的日志文件，以免频繁增长大小，创建多个VLFs，减慢恢复过程。
5、 日志文件主要跟踪数据库操作，实现UNDO，REDO，备份，恢复。
How the Transaction Log Works
1、 SQL使用buffer cache的方式存储检索的结果（数据页的集合），对buffer cache中的page进行修改（此时buffer中的数据页称作dirty page），SQL保证在修改后的数据页写回硬盘之前，日志文件会记录所作的修改。写回的过程称作flushing the page。
2、 日志文件包含足够的信息可以保证数据库可以roll back or roll forward。
3、 正是因为日志文件的roll back功能，可以实现当某个数据库文件损坏时，可以重建数据库，也因此日志文件和数据库文件需要放在不同磁盘中。
4、 SQL会周期性的把dirty pages写入磁盘，它发生在checkpoint过程和SQL的系统进程（独立工作线程或lazywriter线程），清除buffer的数据，接收新的数据；日志文件可以保证未完成的事务重新roll back回来（使用rollback命令或服务器重启）
Checkpoint Process
1、 检查点进程通过减少未写回磁盘的buffer页面数量，实现的目的是减少服务器失败恢复的时间。
2、 检查点发生的时机：
a) 一个检查点语句调用
b) ALTER DATABASE语句调用
c) 一个SQL2000实例正常终止
d) 自动检查点发生（基于日志中活动记录的数量，而不是和上次检查发生的时间间隔）
3、 检查点进程要记录一个最小的可以正常roll back的LSN，称作minimum LSN (MinLSN)。定位MinLSN：
a) 基于LSN最小的最老活动日志
b) 最老的还未复制的事务
c) 检查进程的起始点
4、 从MinLSN到最近的事务日志称作活动事务日志（可roll back的部分）
5、 SQL启动时（无论是正常还是非正常其中）会在每个数据库上执行恢复过程，会根据事务日志决定roll forward或roll back。
6、 日志记录中LSN小于MinLSN的部分是非活动区域，SQL确保该部分已经写入磁盘，为了重用磁盘空间，需要对这部分进行裁剪，裁剪的最小单位是VLF ，如果VLF包括活动记录则不能删除；在简单恢复模式下，简单删除VLF即可，在完全恢复和块-日志恢复模式下，需先备份要裁剪的非活动部分。（具体看8，9章）
7、 检查点进程可以清理可用空间，当逻辑日志文件到达物理日志文件的末端可以重用空间，当逻辑文件到达最后一个物理文件末端时，逻辑文件转移到第一的物理文件的第一个虚拟文件，如果第一个VLF没有被裁剪，且没有设置自动增长，则SQL停止工作。
Operating System Threads
1、 SQL使用individual worker threads和lazywriter thread周期性扫描buffer，计划dirty pages的写入；individual worker threads来自其他的SQL进程，它执行前会先等待主要任务的执行；lazywriter thread周期性扫描“可用buffer列表”，释放可用空间。
2、 SQL使用individual worker threads，lazywriter thread，检查点进程保障数据可靠写入磁盘；individual worker threads和lazywriter thread工作在检查点进程的两个周期之间，它们处理大部分dirty pages的写回，和检查点进程的区别是，检查点进程写回dirty pages，并清空之，但不更新可用buffer列表。
Introducing Recovery Models
1、 SQL的数据库可以有三种恢复方式：简单，完全，块日志
Full Recovery Model
1、 完整记录数据库的操作（包括大的变动，如索引建立，批量载入数据），可以恢复到故障点或特定时间。需要经常备份日志文件。
Bulk-Logged Recovery Model
1、 块日志备份大部分操作，少量大变动操作
Simple Recovery Model
1、 记录所有操作，包括大变动操作。好处是大部分情况可以保证日志文件不被添满，坏处是记录频繁，容易造成数据丢失，要恢复数据库只能是上次备份的数据库。
tempdb总是使用简单恢复模式，pubs，northwind默认使用简单恢复，可修改。yy.net
Chapter 5 Understanding System and User Databases
Lesson 1: Understanding the Database Architecture
Introducing Data Files
一个数据库（数据库数据文件部分）包括一个主数据文件（.MDF）和若干第二数据文件(.NDF)，主数据文件主要包括数据表，索引，数据库起始信息，系统数据表等。第二数据文件主要用于数据库的分盘存储。
数据文件的定位：SQL在两个地方记录数据文件的位置：系统主数据库文件（Master），主数据库文件（Primary）。大部分情况在Master中查找数据文件的位置，一些例外包括，从7.0升级到2000；恢复Master数据库，使用sp_attach_db连接数据库。
数据文件包括逻辑名和物理名：逻辑名适用于T-SQL中，要符合SQL的命名规范，且在数据库中唯一；物理名适用于OS。
其他的文件属性还包括：
a) file ID（文件编号）：唯一标识数据文件
b) initial file size
c) file growth increment
d) maximum file size
5、所有文件属性存储在File Header（文件头）页面，File Header是数据文件的第一页，SQL用文件号，页面号唯一标识页面，页面在数据文件中以0开始的数字序列编号。
Allocating Space for Tables and Indexes
1、 SQL 使用Extent给对象（表，索引）分配可用空间；Extent的大小是64K，包括8个连续的页，每个页面8K；有两种类型的Extent：混合Extent，唯一Extent；SQL为少量数据分配混合Extent(不足64K的数据)。如果数据>64K，则分配唯一Extent。
2、 对象创建之初，SQL首先定位一个混合且有剩余页面的空间Extent的页面分配给该对象，（一个页面只能存放一个对象）如果需要额外空间，则继续分配此种页面给它，直到对象空间满8个页面，分配唯一Extent给该对象。
3、 SQL使用Secondary Global Allocation Map (SGAM)的表（包括64,000 extents）跟踪带剩余页面的Extent，每个Extent使用一个bit位，为0代表该Extent为带剩余页面的Extent，否则为混合Extent且页面已满，或为唯一Extent。
4、 SQL使用Global Allocation Map (GAM)的表（包括64,000 extents）跟踪Extent的分配情况，每个Extent使用一个bit位，为1代表还未分配，为0代表已分配。
5、 GAMs and SGAMs表存储在数据文件的第一个Extent的第3，4页，可以快速检索到。
6、 SQL使用Index Allocation Map (IAM)表跟踪对象空间的分配情况（分配给对象的带剩余页面的Extent，唯一Extent的链接表）；IAM在数据文件的随机位置。
7、 SQL使用Page Free Space (PFS)统计分配页面的使用情况(empty, 1–50 percent full, 51–80 percent full, 81–95 percent full, or more than 95 percent full)。
8、 Page Free Space (PFS)存储在数据文件的第一个Extent的第2页，每个Page Free Space (PFS)记录8,000个连续的页，每隔8000页包括一个PFS。
Storing Index and Data Pages
1、 在没有集簇索引的情况下，表中的数据无规则的分布在分配的页中（这些数据称作堆（HEAP）），使用IAM表检索记录，大型表中会很慢。
2、 为了加快检索过程，可以创建索引，索引是一种B树结构，包括一个根节点，可能的中间层，叶节点；每个B树物理的基于索引值存储，每个索引页链接临近的索引页。
3、 SQL包括两种索引：clustered（集簇）索引和nonclustered（非集簇）索引
4、 集簇索引强制数据物理的按索引排列（页层为数据）
非集簇索引，数据逻辑上按索引排列（页层为指针）yy.net
Lesson 3: Understanding and Querying System and Database Catalogs
Introducing System Tables
1、 系统表包括两类（sys前缀）：
a) 系统分类（system catalog）
b) 数据库分类（database catalog）
System Catalog
1、 系统分类是一组系统表，记录了Meta数据（关于用户和系统数据库）和系统配置信息（SQL的配置），它们只存在于Master数据库中。
Table 5.1 Frequently queried system tables in the system catalog
|
System Table |
Description |
|
Sysaltfiles |
Contains a row of information for each file in the database, including the file ID, database ID (dbid), physical and logical filenames, location, size, and growth characteristics. |
|
Sysconfigures |
Contains a row of information for each server option set by an administrator before SQL Server 2000 started, plus dynamic configuration options set since startup. |
|
Sysdatabases |
Contains a row of information for each database, including the dbid, security identifier (SID) of the database owner, creation date, database compatibility level, location of the primary file, and database configuration options that have been set. |
|
Sysdevices |
Contains a row of information for each permanent backup device that has been created, including the physical and logical filenames, file size, and controller type for the device (such as disk or tape). |
|
Syslockinfo |
Contains a row of information for each waiting, converting, and granted lock request, including the ID of the user or process requesting the lock and the object being locked. |
|
Syslogins |
Contains a row of information for each login account, including the sid , login ID, encrypted password (may be NULL), default database, and server role. |
|
Sysmessages |
Contains a row of information for each system error or warning that SQL Server 2000 can return, including the error number, severity level, and description. |
|
Sysperfinfo |
Contains a row of information for each SQL Server performance counter, including the object name, counter name, and counter value. These counters are used in Windows System Monitor (or Windows 2000 Performance Monitor) and performance condition alerts. |
Database Catalog
1、 数据库分类包括管理特定数据库的一组系统表，每个数据库都有；包括足够的信息用于连接/断开数据库到SQL实例。
Table 5.2 Frequently queried system tables in the database catalog
|
System Table |
Description |
|
Syscomments |
Contains a row of information for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The text column contains the original Transact-SQL definition statement used to create the object. None of the entries in this table should be modified or removed. You can hide stored procedure definitions by using the ENCRYPTION keyword when the stored procedure is created. |
|
Sysindexes |
Contains a row of information for each index and table in the database, including the index ID (indid), type, original fill factor, and index name. |
|
Sysobjects |
Contains a row of information for each object in a database, including object name, object ID, user ID (uid) of the object owner, and creation date. |
|
Sysusers |
Contains a row of information for each Windows user, Windows group, SQL Server user, and SQL Server role in the database, including the user ID, username, group ID (gid), and creation date. |
Retrieving System Information
1、 可以使用多种方法检索系统表（不建议直接使用脚本察看）
a) 系统存储过程
b) 系统函数
c) 信息架构察看（information schema views）
2、 常用系统存储过程
Table 5.3 System stored procedures used for querying system tables
|
System Stored Procedure |
Description |
|
Sp_configure ['name', 'value'] |
Reports (or changes) configuration settings for a SQL Server 2000 instance. |
|
Sp_dboption ['database', 'option', 'value'] |
Reports (or changes) database options for a particular database. |
|
Sp_help ['object'] |
Reports information about a particular database object or data type. |
|
Sp_depends ['object'] |
Reports information about dependencies of database objects, such as views or procedures that depend on a particular table. |
|
Sp_helpdb ['database'] |
Reports information about a particular database (or all databases if no database is specified). |
|
Sp_helpfile ['name'] |
Reports the physical names and attributes of files associated with the current database. |
|
Sp_lock ['spid1', 'spid2'] |
Reports information about current locks. |
|
Sp_monitor |
Reports information about how busy SQL Server 2000 has been since it started and since sp_monitor was last run. |
|
Sp_spaceused ['object', 'updateusage' ] |
Reports information about the number of rows, disk space reserved, disk space used by a table or database, and whether the DBCC UPDATEUSAGE command should be run. |
|
Sp_statistics ['table_name', 'owner', 'qualifier', 'index_name', 'is_unique', 'accuracy'] |
Reports information about all indexes and statistics on a table or view. |
|
Sp_who ['login'] |
Reports information about current SQL Server 2000 users and processes, and can be filtered using the ACTIVE keyword to display only processes that are not idle. |
3、 常用系统函数
Table 5.4 System functions used to query system tables
|
System Function |
Description |
|
DATABASEPROPERTYEX ('database', 'property') |
Returns a value regarding a database option or property (such as Recovery). |
|
DB_ID ('database') |
Returns the ID number of a database. |
|
DB_NAME (database_id) |
Returns the name of a database. |
|
FILE_ID ('file_name') |
Returns the ID number of a logical filename. |
|
FILE_NAME (file_ID) |
Returns the logical file name of a file. |
|
FILEPROPERTY ('file_name', 'property') |
Returns a value regarding a file property (such as SpaceUsed). |
|
GET DATE () |
Returns the current system date and time in the SQL Server 2000 format for datetime values. |
|
HOST_NAME () |
Returns the name of the host computer. |
|
STATS_DATE (table_id, index_id) |
Returns the date that statistics for an index were updated. |
|
USER_ID ('user_name') |
Returns the database ID of a user. |
|
USER_NAME (id) |
Returns the database name of a user (such as dbo). |
4、 信息架构察看（information schema views）：基于ANSI-92标准，察看系统，数据库表，与实现的厂家无关。
常用information schema viewsyy.net
Chapter 6 Creating and Configuring User Databases
Lesson 1: Creating a User Database
Creating a User Database
1、 创建用户数据库，唯一必要的参数是数据库名称，数据库名称必须遵循SQL的标识规范：
a) 首字符必须是字母，下划线（_），@（代表本地变量或参数），#（代表临时表或过程）
b) 后继字符可以是数字，$
c) 不允许包括空格，特殊字符
d) 不允许包括SQL的保留关键字
e) 注意：如果需要在标识中包括空格，需要用“”或[]隔离标识
2、 其他属性如果不指定，SQL会用一组默认值（下表假定数据库名为SelfPaced）
Table 6.1 Example Default Database Properties for Database Name SelfPaced
|
Database Property |
Value |
|
Logical primary data filename |
SelfPaced_Data |
|
Physical primary data filename |
SelfPaced_Data.mdf |
|
Physical primary data file location |
C:\Program Files\Microsoft SQL Server\Mssql\Data |
|
Physical size of the primary data file |
The actual size of the model data file (640 KB by default) if created with a Transact-SQL script or the actual size of the model data file rounded up to the nearest whole number (1 MB) if created with SQL Server Enterprise Manager |
|
Physical primary data file growth properties |
Autogrowth enabled, with a growth increment of 10%, and no maximum file growth size |
|
Logical transaction log filename |
SelfPaced_Log |
|
Physical transaction log filename |
SelfPaced_Log.ldf |
|
Physical transaction log file location |
C:\Program Files\Microsoft SQL Server\Mssql\Data |
|
Physical size of the transaction logfile |
The actual size of the model transaction log file (512 KB by default) if created with a Transact-SQL script or the actual size of the model transaction log file rounded up to the nearest whole number (1 MB) if created with SQL Server Enterprise Manager |
|
Physical transaction log file growth properties |
Autogrowth enabled, with a growth increment of 10%, and no maximum file growth size |
yy.net
1、 SQL创建用户数据库包括两个步骤：
a) 从model数据库复制到主数据库文件初始化用户数据库和它的Meta数据，对象复制包括系统数据库对象和用户自定义数据库对象（model类置对象）
b) SQL用空的页面清空所有数据库文件（不包括系统表）
2、 新建的数据库除了基层model的数据库对象，还继承了model的配置（option），可以修改model的配置，以影响所有新建数据库的配置。
3、 创建的用户数据库还继承model的collation的设置。
Using SQL Server Enterprise Manager to Create a User Database
Using the Create Database Wizard
Using SQL Server Enterprise Manager Directly
Using the CREATE DATABASE Transact-SQL Statement to Create a User Database
Scripting Databases and Database Objects Using SQL Server Enterprise Manager
Lesson 2: Setting Database Options
Introducing Database Options
1、 数据库的设置从model继承而来，包括5方面的设置：
a) 某些自动化任务（自动创建统计，自动更新统计，自动缩减数据库等）
b) 光标行为和范围（本地，全局）
c) 恢复设置（完全，块日志，简单）
d) ANSI适应性（ANSI空值，引用标识）
e) 状态设置（只读，仅dbo访问）
Viewing Database Option Settings
Modifying Database Options
Lesson 3: Managing User Database Size
Using Automatic File Growth Appropriately
1、 SQL在默认情况下会自动增长数据库及日志文件大小，然而，这仅适用于小型系统中，不合适的自动增长会影响到性能（多个数据库/日志文件，文件碎片），因此，在专业环境中需要DBA监视数据库/日志的大小变化，手动调整数据库/日志的大小。
2、 如果要使用自动增长，设置一个较大的增长值，确保自动增长不会频繁发生；总是设置一个文件最大值，保证不会耗尽磁盘空间；可以使用警报监控文件的增长及剩余空间的多少（13章）。
3、 可以使用企业管理器，ALERT DATABASE语句修改自动增长的配置
4、 举例（修改TSQLDB的数据文件tsqldb_data，禁用自动增长）：
ALTER DATABASE TSQLDB
MODIFY FILE ( NAME = 'tsqldb_data' , FILEGROWTH = 0 )
Using Automatic File Shrinkage Appropriately
1、 可以在数据库设置中配置“自动紧缩”，自动紧缩可以自动压缩数据库/日志大小；同样，自动紧缩仅适用于小型，无管理环境。
2、 可以使用企业管理器，DBCC语句修改自动紧缩的配置
Controlling Data File Size Manually
1、增长数据库大小举例：
ALTER DATABASE TSQLDB
MODIFY FILE ( NAME = 'tsqldb_data' , SIZE = 15 )
2、压缩数据库举例：
USE TSQLDB
DBCC SHRINKFILE ( 'tsqldb_data' , 7 )
3、 DBCC SHRINKFILE只应用到当前数据库，压缩从数据库文件的末端开始
a) 所有使用的页面移动到数据库的开始，然后压缩文件到期望的大小（默认）
b) 移动最末端的Extent（区块），而不是页
c) 移动页面，而不压缩
4、 可以在使用数据库的同时压缩数据库文件，但是不能在备份时
5、 可以使用DBCC SHRINKDATABASE压缩整个数据库（包括所有数据库，日志文件）
Controlling Transaction Log File Size Manually
1、 日志文件的大小控制和数据库文件类似
2、 压缩日志文件的逻辑流程：
a) 压缩的单位是VLF，即假如600M日志，6个VLF，每个100M，期望压缩到350M，实际会压缩到400M，原因是压缩的单位是VLF，会自动向上取整。
b) 压缩从日志文件的末端开始
c) 如果期望的大小大于活动逻辑日志的位置，则直接去掉后面的VLFs
d) 如果期望的大小小于活动逻辑日志的位置，则用空记录把最后一个VLF填满，然后强制复制到第一个VLF，设置逻辑日志起始位置标记为第一个VLF，同时发出消息，通知压缩无法达成，再次执行DBCC时，释放最后面的VLF，进而释放空间。
Creating Additional Data and Transaction Log Files
1、 可以使用企业管理器，ALERT DATABASE语句增长额外的数据库，日志文件
2、 增加数据文件举例：
ALTER DATABASE TSQLDB
ADD FILE
(
NAME = 'TSQLDB2_DATA' ,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TSQLDB2.ndf' ,
SIZE = 10 ,
MAXSIZE = 25 ,
FILEGROWTH = 5
)
yy.net
Lesson 4: Placing Database Files on Multiple Disks
Introducing RAID
1、 RAID系统可以包括硬件，软件RAID
2、 SQL通常支持的RAID的类型是0，1，3，0＋1
3、 显然使用RAID，可以提高性能，错误冗余，增强可维护性
Introducing Filegroups
1、 SQL包括三种类型的文件组：主（primary），用户定义（user－defined），默认－（default）
2、 每个SQL数据库最多可以包括256个文件组，且至少包括一个文件组－主文件组。
3、 一个文件组可以包括多个数据库文件，分别于多个磁盘。
4、 主文件组包括主数据库文件
5、 当添加第二个数据库文件时，默认加入到主文件组
6、 当在数据库中添加对象，创建数据时，SQL按比例平均写数据到各个文件组的各个数据库文件，而不是从主文件组，从主数据库文件开始，顺序写入数据。这样有助于提高读写效率。
7、 日志文件没有文件组概念，因此也不属于任何文件组。
8、 用户创建的组就是用户定义组
9、 新建的数据库如果没有指定加入的组，则自动加入默认组（默认是primary组），可以用ALERT DATABASE修改。
Configuring Your SQL Server 2000 Disk Subsystem for Performance, Fault Tolerance, and Recoverability
Transaction Log
1、 明确日志的作用和读写特点：
a) 作用：数据恢复，回滚
b) 读写特点：
i. 读：仅在备份，回滚，启动时的数据恢复时进行
ii. 写：顺序，连续，同步
2、 显然，应该把数据库文件和日志文件放在不同磁盘上，以防止磁盘故障时，无法恢复数据
3、 每个日志文件使用独立的磁盘存放
4、 使用RAID1
Data Files
1、 使用专用硬盘，日志，数据库分离
2、 使用多个磁盘，控制器，提高I/O性能
3、 大型数据库中同时使用文件组，RAID提高性能，及便于维护
4、 如果使用RAID，RAID5是首选，可以提供快的读性能和一般的写性能。RAID0，最快，可靠性也最差；RAID0+1，昂贵，但性能，可靠性，数据的可恢复性最佳。
Tempdb
1、 tempdb负责存放临时数据，需要高的读，写性能，不需要恢复，可以考虑单独放置在一个磁盘，使用专用磁盘，使用RAID0。
Moving Data and Transaction Log Files
1、 可以使用分离（detach）和连接（attach）数据库和日志文件，方便它们在不同SQL，或磁盘间移动。
Detaching and Attaching Databases Using SQL Server Enterprise Manager
1、 分离：在企业管理器，数据库容器，选中要分离的数据库，右键，所有任务，分离数据库（可选参数：清除－当有用户连接到该数据库时，断开连接；更新统计－适用于移动数据库到移动媒体前）
2、 连接：在企业管理器，数据库容器，右键，所有任务，连接数据库，浏览到数据库的主文件，SQL会自动列出相关其他数据库文件，日志，如果路径不对会有提示。
Detaching and Attaching Databases Using Transact-SQL
1、 分离：使用sp_detach_db存储过程，举例：
Sp_detach_db TSQLDB, TRUE
2、连接：使用sp_attach_db存储过程，举例：
Sp_attach_db TSQLDB ,
@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TSQLDB.mdf'yy.net
Chapter 7 Populating a Database
Lesson 1: Transferring and Transforming Data
Importing Data
1、 创建好用户数据库后，常用的方法是从外部数据源导入数据。
2、 导入数据前要根据外部数据源的情况，评估数据导入过程，需要考虑的问题包括：
a) 数据的一致性，即相同数据在不同情况下是否表述一致。
b) 是否需要添加额外的列，数据可能存在内在的一致性，但没有表现出来。
c) 是否需要转换数据的格式，更清晰地反映事务的本质。
d) 是否需要调整数据列（分离，合并）
e) 数据导入是一次性的还是周期性的
f) 确定访问现存数据的方式，是否可以直接访问到，有否足够的权限，是否需要转化格式。
DTS Data Transformations
1、 经过输入数据评估过程后，进入实际导入流程。
2、 对于需要修改数据源的情况，很少能直接在数据源上直接修改，所以，可以考虑在数据导入过程或导入到SQL以后对数据进行净化和清理（转换）。
3、 在数据导入过程中对数据进行转换的过程称作－DTS。
4、 对于需要修改的数据类型和程度，以及是否需要周期性执行，决定了要使用的数据转换工具。
Introducing the Data Transfer Tools
Table 7.1 Data Transfer Tools and Their Functions
|
Tool |
Description |
|
DTS |
DTS is a graphical tool used to import, export, and transform data. DTS can work directly with a wide variety of data sources. DTS creates packages that can be scheduled. DTS can also import and export database objects schema (meta data) between SQL Server instances. |
|
Bcp |
Bcp is a command-prompt utility used to copy data from a text file to a SQL Server 2000 table or view (or from a SQL Server 2000 table or view to a text file) using ODBC. The transformation capabilities of Bcp are limited and require cryptic format files. Working with Microsoft or third-party databases is a two-step process. |
|
BULK INSERT Transact-SQL statement |
BULK INSERT is a Transact-SQL command used to copy data from an ASCII text file to a SQL Server 2000 table or view (but not from SQL Server 2000 to a text file) using OLE DB. The BULK INSERT statement provides the same functionality of Bcp (and has the same limitations) in a Transact-SQL statement and can be embedded in a DTS package. |
yy.net
1、 可用的转换方式：
Table 7.4 Available Types of Transformations
|
Transformation Type |
Description |
|
Copy Column |
Copies data from a single column to a single column (or multiple column to multiple column). By default, allows all possible data type conversions and automatically truncates text (when necessary) without error notification. |
|
ActiveX Script |
Uses an ActiveX script to transform (and truncate) data between one or more source and destination columns on a row-by-row basis. |
|
Date Time String |
Converts a date or time value in a source column to a different format in the destination column. Both columns must be a string data type. |
|
Lowercase String |
Converts string data to lowercase characters and to the destination data type (if necessary) from a source column to the destination column, truncating data as necessary. Both columns must be a string data type. |
|
Uppercase String |
Converts string data to uppercase characters and to the destination data type (if necessary) from a source column to the destination column, truncating data as necessary. Both columns must be a string data type. |
|
Middle of String |
Extracts a substring of string data from a source column and copies it to a destination column, truncating data if necessary. Can also perform case conversion. Both columns must be a string data type. |
|
Trim String |
Removes leading, trailing, and embedded white space from string data in a source column and copies it to a destination column, truncating data if necessary. Can also perform case conversion. Both columns must be a string data type. |
|
Read File |
Opens and copies the contents of a file specified in the source column to a destination column. The source column must be a string data type. The destination column must be a string or binary data type. |
|
Write File |
Copies the contents of a source column to a file specified in the destination column. The source column must be a string or binary data type. |
DTS Package Workflow
1、 DTS使用“优先约束”，定义DTS包执行的顺序，一个任务可以包括多个约束，没有约束的任务并行执行
2、 约束的类型：
Table 7.5 Precedence Constraints and Their Functions
|
Precedence Constraint |
Description |
|
Unconditional |
If Task 2 is linked to Task 1 by an Unconditional constraint, Task 2 will wait until Task 1 completes and then will execute, regardless of the success or failure of Task 1. |
|
On Success |
If Task 3 is linked to Task 2 by an On Success constraint, Task 3 will wait until Task 2 completes, and then will only execute if Task 2 completed successfully. |
|
On Failure |
If Task 4 is linked to Task 2 by an On Failure constraint, Task 4 will wait until Task 2 completes and then will only execute if Task 2 failed to complete successfully. |
DTS Package Storage
1、 可以存储DTS包到：
a) SQL Server 2000
b) SQL Server 2000 Meta Data Services
c) a Microsoft Visual Basic file
d) a structured storage file.
Table 7.6 DTS Storage Options
|
Storage Location |
Description |
|
SQL Server 2000 |
Stored as a table in the msdb database on any instance of SQL Server 2000. This is the default save option. Multiple packages and multiple versions can be stored. When you save a package to SQL Server 2000, you have the option of securing the packages with one or more passwords. |
|
Meta Data Services |
Stored in the repository database in Meta Data Services on your computer. Allows tracking of columns and tables used in the source and destination, including the lineage (ancestry or original source) of data in a particular row. You can secure a package saved to Meta Data Services by using its own security. |
|
Visual Basic file |
Stored in Visual Basic code that you can later open and modify using Visual Basic or Visual C++. You can secure packages saved to a Visual Basic file using a system such as Microsoft Visual SourceSafe. |
|
Structured storage file |
Stored in an operating system file. Allows storage and transfer of a DTS package independent of any SQL Server database. Multiple packages and multiple versions can be stored in a single file. When you save a package to a structured storage file, you have the option of securing the packages with one or more passwords. |
yy.net
Lesson 2: Introducing Microsoft Data Transformation Services (DTS)
1、 DTS包定义了一系列任务：连接数据库，执行数据转换，存储转换数据到数据源，遵循优先约束的原则，根据前面任务执行成功或失败，决定后继任务的动作。
2、 可以创建DTS包的工具：DTS导入/导出向导，DTS设计器，编程。
DTS Connections
Table 7.2 Connection Types
|
Connection Type |
Description |
|
Data source connection |
A connection to a standard database (such as Microsoft SQL Server 2000, Microsoft Access 2000, Oracle, dBase, or Paradox), an OLE DB connection to an ODBC data source, a Microsoft Excel 2000 spreadsheet, an HTML source, or any other OLE DB provider. The properties of the data source connection specify the necessary connection parameters. |
|
File connection |
A connection to a text file (normally delimited). The properties of the file connection specify the format of the text file. There are multiple formats you can use. For example, the text file format can be either a delimited or a fixed field format. |
|
Data link connection |
A connection to an intermediate file (.UDL file) that stores a connection string to create an OLE DB connection that is resolved at run time. The data link connection allows you to encapsulate the connection properties into a separate .UDL data link file. You can then edit the connection string in the data link file (from one data source to another) without changing the SQL Server 2000 DTS package. |
DTS Tasks
1、 DTS可以执行多种任务，包括并行任务，顺序任务（优先约束）。
2、 DTS任务包括两个方面：数据的移动，数据的转换。
3、 DTS中数据的转换使用的是称作DTS数据泵的架构（一种OLEDB的数据提供者）
4、 DTS设计器可以实现大部分DTS任务（除了并行数据泵任务，需要编程完成）。
Table 7.3 Tasks Available through DTS Designer
|
Category |
Task |
Description |
|
Tasks that copy and manage data and meta data |
Bulk Insert task |
Runs the BULK INSERT Transact-SQL statement from within a DTS package. This task provides the fastest way to copy information into a table or view, but it does not log error-causing rows. If you need to capture error-causing rows to an exception file, use the Transform Data task instead. |
|
|
Execute SQL task |
Runs Transact-SQL statements during package execution. You can perform a number of operations with Execute SQL task, which include dropping a table and running stored procedures. |
|
|
Copy SQL Server Objects task |
Copies SQL Server database objects (meta data) from one SQL Server instance to another. This task can transfer objects from one instance of SQL Server 7.0 to another; from an instance of SQL Server 7.0 to SQL Server 2000; or from one instance of SQL Server 2000 to another instance of SQL Server 2000. |
|
|
Transfer Database Objects tasks |
A collection of tasks that copy server-wide information (the Copy SQL Server Objects task copies only database-specific information) from one SQL Server instance to another. These tasks include the Transfer Database task, the Transfer Error Messages task, the Transfer Logins task, the Transfer Jobs task, and the Transfer Master Stored Procedures task. These tasks are used by the Copy Database Wizard. |
|
Tasks that transform data |
Transform Data task |
Copies, transforms, and inserts data from a data source to a data destination. This task is the most basic implementation of the data pump engine in DTS. |
|
|
Data Driven Query task |
Selects, customizes, and executes one of several Transact-SQL operations (such as an update or a delete) on a row based on the data in the row. Use this task if the Transform Data task and the Bulk Insert task do not meet the requirements of your application. |
|
Tasks that function as jobs |
ActiveX Script task |
Runs an ActiveX script. You can use this task to write code to perform functions that are not available in DTS Designer. |
|
|
Dynamic Properties task |
Retrieves data from an outside source and assigns values retrieved to selected package properties. External sources can be an .INI file, data file, query, global variable, environmental variable, or a constant. |
|
|
Execute Package task |
Runs other DTS packages as part of a workflow. Do not use this task recursively because it could generate a stack overflow, which could result in MMC shutting down. |
|
|
Execute Process task |
Runs an executable program or batch file. This task can be used to open any standard application, such as Microsoft Excel, but it is used primarily to run batch files or business applications that work against a data source. |
|
|
File Transfer |
Downloads data from a remote server Protocol task or an Internet location using FTP. The FTP task and Ftp.exe use the same connection method. |
|
|
Send Mail task |
Sends an e-mail message as a task. For example, notification can be sent to an administrator about the success or failure of a backup operation. In order to use this task, you need to install a MAPI client on the instance of SQL Server you are running. |
yy.net
Lesson 3: Transferring and Transforming Data with DTS Graphical Tools
Using the DTS Import/Export Wizard
Copying Entire Tables and Views
Querying to Specify the Data
Copying Objects and Data Between SQL Server Databases
Saving and Scheduling Packages
Using DTS Designer
Extending DTS Package Functionality
Transaction Support
Message Queue Task
Send Mail Task
Programming Templates
Lesson 4: Working with DTS Packages
Understanding DTS Package Storage Options
SQL Server 2000
1、 DTS包以SQL 2000的形式存储（存储在msdb的sysdtspackages数据表中），可以清晰的看到一个DTS包的清单；同时存储了DTS包的版本记录，可以回溯编辑之。
2、 存储在SQL中的DTS包，可以设置两种密码保护：所有者密码，用户密码；有用户密码可以执行DTS，但不能编辑。
Meta Data Services
1、 存储于Meta Data Services的DTS可以跟踪版本信息，Meta数据，数据血统，可以察看历史信息，并编辑之；DTS使用DTS信息模版的方式存储Meta转换信息。
2、 存储于Meta Data Services的DTS可以用两种方式跟踪数据的血统：行级别，列级别。
3、 行级别，跟踪数据转换前后的变化，需要添加一列作标识
4、 列级别，提供了包使用的版本，数据表
5、 Meta Data Services的DTS无包级别的安全性。
Structured Storage File
1、 以文件形式存储（.DTS）,也可以包括多个版本，历史记录；可以用命令行DTS工具执行；也可以包括所有者，用户密码。
Visual Basic File
1、 可以使用VB,VC编辑之
Using DTS Package Execution Utilities
1、 可以用命令行工具执行DTS包，包括Structured Storage File（需提供文件名），SQL 2000的（连接信息）；如果要密码，则需要提供密码。
DTS Run Utility
Dtsrun
Using DTS Package Logs and Exception Files
1、 DTS执行过程中会记录下日志（包括起始，结束事件；成功，失败；未执行等；限2k下执行）
2、 DTS还可以使用“排除”文件，记录错误信息（未复制的数据，失败的行，列）
Performing Disconnected Edits
1、 DTS设计器提供了“脱机编辑”的功能
yy.net
Lesson 5: Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement
Copying Data in Bulk Using Text Files
1、 除了使用DTS，还可以使用BULK INSERT Transact-SQL，Bcp导入数据（大量，文本数据，高速，较少转换格式）。
2、 Bcp是一种老的数据导入工具，支持脚本。
3、 要导入的文件通常有一定的格式（带分隔符的文本文件，如制表符，逗号）；格式信息可以作为Bcp的参数或BULK INSERT的条件语句，也可以放在一个可重用的文本文件中。
4、 要导入的文件通常由数据库系统，电子表格生成，可以是文本，也可以是二进制文件。
Using Bcp
1、 Bcp命令行对于参数要求比较严格，区分大小写
2、 参数列表：
Table 7.7 Commonly Used Parameters for Bcp
|
Argument |
Description |
|
Database_name |
The database into which the data is being inserted. If not specified, the default database for the specified user is used. |
|
Table_name |
The name of the table into which the data is being inserted or from which the data is copied. |
|
"Query" |
The query used to filter the data being copied out of SQL Server. |
|
In | Out |
The direction of the bulk copy operation. |
|
Format |
Used to create a format file. |
|
Data_file |
The data file used as the source or the destination of the bulk copy operation. |
|
-m |
The maximum number of errors that can occur before the bulk copy operation is cancelled. Default is 10. |
|
-f |
Specifies the full path of the format file. This parameter is optional. |
|
-e |
Specifies the full path of the error file used to record all rows Bcp is unable to transfer to the database. If this option is not used, no error file is created. |
|
-b |
Specifies the number of rows per batch of data copied. Each batch is copied to the SQL Server 2000 instance as a single transaction. |
|
-c |
Specifies the bulk copy operation using a character data type. |
|
-t |
Specifies the field terminator. The default is tab. |
|
-r |
Specifies the row terminator. The default is new line. |
|
-S server_name [/instance_name] |
Specifies the server name (and instance name if applicable) to which Bcp will connect. Default instance on the local server is the default. |
|
-U |
Specifies the login ID. |
|
-P |
Specifies the password for the login ID. NULL is the default. |
|
-T |
Specifies the use of a trusted connection, using the security credentials of the current user. |
|
-h "hint" |
Hints such as TABLOCK, ROWS_PER_BATCH=nn and ORDER ASC | DESC. These hints tell SQL Server how to process the imported data most efficiently. |
Using the BULK INSERT Transact-SQL Statement
1、 BULK INSERT Transact-SQL使用举例：
BULK INSERT NewDatabase..NewData
FROM 'C:\SelfPacedSQL\CH_7\NewData.txt'
WITH
(
BATCHSIZE = 250 ,
DATAFILETYPE = 'char' ,
FIELDTERMINATOR = ',' ,
ROWTERMINATOR = '\n',
MAXERRORS = 50 ,
TABLOCK
)
Optimizing Bulk Copy Operations
1、 当使用Bcp导入数据时，如果数据库设置为完全备份，则日志记录所有细节，导致速度下降，因此，建议设置数据库恢复模式为块备份模式，日志仅记录少量数据。
2、 其他优化性能的方法：
a) 如果从单一用户导入大量数据到空表，建议使用TABLOCK Hints
b) 使用大的批块时，使用ROWS_PER_BATCH hint
如果数据带有索引，集簇索引通常会比较快。yy.net
Chapter 8 Developing a Data Restoration Strategy
Lesson 1: Understanding Data Restoration Issues
What Are the Goals of a Data Restoration Strategy?
Provide Fault Tolerance
1、 考虑提供错误冗余，包括磁盘子系统，服务器级别，集群
Monitor Your Database
1、持续监控数据库（DBCC，SQL性能警报通知，Agent定期执行任务）
Plan for All Forms of Failure and Disaster
1、 为各种灾难事件制定恢复计划
Determine Acceptable Data Restoration Times
1、确定可以接受的数据恢复时间（通常由n种因素决定，包括冗余类型，数据库的大小，使用的频度）
Be Able to Quickly Verify Proper Database Functionality
1、确定数据恢复后的正确性（通常用T-SQL脚本查询数据库）
What Types of Backups Are Available?
Table 8.1 Types of Backups
|
Backup Type |
Description |
|
Full database backup |
Full copy of the database. |
|
Differential database backup |
Copy of all modified data pages since the last full database backup. |
|
Filegroup backup |
Full copy of all files in a filegroup. |
|
Differential filegroup backup |
Copy of all modified data pages since the last full filegroup backup. |
|
File backup |
Full copy of a data file. |
|
Differential file backup |
Copy of all modified data pages in a data file since the last full file backup. |
|
Transaction log backup |
Copies the active portion of the transaction log (which) also truncates the log). |
|
Snapshot backup and restore |
Full database copy in a very short time (measured in seconds) using third-party hardware and/or software vendors. Can be used with conventional differential and transaction log backups. |
yy.net
Lesson 3: Understanding the Restoration Process
Understanding the Recovery Process
Automatic Recovery Process
1、 SQL启动时会自动执行自动恢复过程保证数据库逻辑上的一致性。（检查每个数据库的事务日志，决定roll forward或roll back）
2、 恢复顺序：Master，model，msdb，user db；更新记录可以在SQL的错误日志中看到。
3、 SQL的自动恢复过程是自动的，不能直接控制，可以设置一个SQL自动恢复过程需要的时间（默认为0，即由SQL自动决定）
Manual Recovery Process
1、 手动恢复过程包括应用一个或多个数据库备份，完整恢复或恢复到某个点。
2、 当应用一个数据库备份（备份的一部分）时，可选把数据库标记为“no recovery”，含义是还需要应用其它的数据库备份，当最终的恢复数据库应用后，数据库标记为“recovery”，数据库在根据日志应用roll forward，roll back。
3、 通常如果数据库没有完整恢复（no recovery）时，也不可以使用，此时，可以把数据库恢复为后备（只读）模式，观察当前的数据状态，决定是否应用后续数据库备份，还是执行恢复动作（执行恢复后，后继备份不能再恢复）。此方式适用于恢复数据库到特定点。
Understanding Manual Recovery Options
Restoring Databases
1、 手动恢复的一般流程：
a) 恢复完整数据库备份（可以恢复到其它SQL实例）
b) 恢复差异数据库备份（可选）
c) 恢复比上述数据库新的事务日志备份
d) 手动恢复（在恢复最后一个日志后，手动执行）
2、 如果完整，差异数据库备份损坏，仍然可以通过较早的日志备份恢复数据库。维护一个完整链接的日志备份，提供额外的数据冗余。
Restoring Files and Filegroups
1、 恢复文件，文件组流程：
a) 恢复完整数据库备份，或文件，文件组备份
b) 恢复文件，文件组差异备份
c) 恢复比上述数据库新的事务日志备份
d) 手动恢复（在恢复最后一个日志后，手动执行）
Restoring and Recovering to an Earlier Point in Time
1、 有时可能需要恢复数据库到某个特定点，SQL有两种方式：
a) 恢复到特定时间点（基于日志）
b) 恢复到特定命名标记（基于日志）
2、 恢复到时间流程：
a) 完整，差异，需要恢复的日志，恢复
b) 不支持恢复到standby模式（即不能恢复到更近的时间点）
c) 可以通过日志的头部信息或msdb的backupset表确定日志的时间
3、 恢复到特定命名标记是先在msdb的logmarkhistory表建立了标记（列）
4、 恢复到时间，标记不支持的几种情况：
a) 最后要恢复的日志包括使用块恢复模式记录的块记录日志。
b) 使用文件，文件组时需要首先恢复完整的数据库
Recovery from Several Different Disaster Scenarios
User Data Disk Failure
1、 恢复及挂机时间基于是否有RAID
a) RAID5：挂机，替换磁盘，重建数据
b) 热交换：替换磁盘，重建数据
c) RAID0或无RAID：备份活动日志（选不裁剪日志），完整，差异，日志，最后一个日志选“恢复”。
User Transaction Log Disk Failure
Master Database Disk Failureyy.net
What Types of Data Restorations Are Available?
Table 8.2 Types of Data Restorations
|
Restoration Option |
Description |
|
Full database restore |
A complete restoration of an entire database using a full database backup, the most recent differential database backup (if any), and all transaction log backups in sequence since the most recent full or differential database backup. |
|
File or filegroup restore with full recovery |
A complete restoration of a file or filegroup using a file or filegroup backup, the most recent differential file or filegroup backup (if any), and all transaction log backups in sequence since the most recent file or differential file or filegroup backup. |
|
Recovery to a point in time |
A recovery of an entire database to a specified earlier point in time using fully logged transactions in transaction log backups, along with database, file, or filegroup backups. |
|
Recovery to a named transaction |
A restoration of an entire database to a specified named mark (such as immediately before or after a specific transaction) using fully logged transactions in transaction log backups, along with database, file, or filegroup backups. |
Back Up All Necessary Databases
1、除了要备份用户数据库，还需要备份系统数据库，包括Master，Msdb。
Develop and Implement a Data Restoration Plan
1、 制定数据恢复计划
a) SQL的版本，SP，OS的版本，SP，数据库/日志的位置，名字，访问账号，使用的网络库。
b) 记录那些数据库需要备份，频度，类型，原因。
c) 自动备份的程度（使用agent自动备份）
d) 确定谁负责备份，谁验证备份结果。
e) 确定备份的质量
f) 确定备份的介质
g) 确定备份的服务器硬件
Lesson 2: Understanding the Types of Database Backups
Understanding Full Database Backups
1、 完整备份包括所有数据库文件（用户数据，系统表，数据库对象，索引，用户定义表等），可以在线备份。
2、 通常，只要导入了数据或数据有了大的变化，就应该执行完整备份
3、 可以考虑定期完整备份数据库
Understanding Differential Database Backups
1、 差异备份记录自上次完整以来所有改变的数据，数据库对象（包括当前活动的变化，记录的是最后的变化结果）
2、 为了加快差异备份恢复时间，SQL使用Differential Changed Map (DCM) page跟踪自上次完整备份以来所有变化的extent（0－未变化，1－变化）。
3、 差异备份适用于在两次完整备份之间，加快数据库恢复过程（与日志备份相比优势在于，在少量数据变化比较频繁时，记录的数据量比日志备份小）
Understanding File and Filegroup Backups
1、 仅备份数据库的某个数据文件或某个文件组，通常适用于VLDBs，加快备份，恢复过程。
2、 可以设计某个文件组包括频繁变化的数据，其它文件组包括不常变化的数据，针对不通文件组使用不同的备份频率。
3、 使用文件组备份/恢复时，要注意同时备份/恢复相关文件，索引；对日志的备份也是必须的；使用文件组备份时可以提高备份效率（同时对多设备读写），但是由于增加了管理工作，建议仅使用于VLDBs。
Understanding Differential File and Differential Filegroup Backups
1、 类似数据库差异备份
Understanding Transaction Log Backups
1、 事务日志备份，备份了自上次日志备份以来的事务日志，使用事务日志备份可以恢复数据库到特定时间点（适用于块日志，完全模式）
2、 当数据库使用块日志恢复模式时，块操作对数据库作的修改记录在Bulk Changed Map (BCM)页中，日志备份时，通过扫描BCM页确定自上次日志备份以来修改过的Extents（最后的变化）。
3、 完整恢复模式不使用BCM页。
4、 日志的备份很大程度上取决于数据库事务繁忙的程度，恢复模式的选择，块操作的数量。
5、 SQL备份日志时会裁减不活动的VLFs，释放并重用日志空间。
yy.net
Chapter 9 Backing Up and Restoring SQL Server
Lesson 1: Understanding Backup Terms, Media, and Devices
Defining Terms
Table 9.1 Backup Terminology
|
Term |
Description |
|
Backup |
A full or partial copy of a database, transaction log, file, or filegroup forming a backup set. The backup set is recorded on backup media (either tape or disk) using a backup device (a tape drive name or physical filename). |
|
Backup device |
The physical file (such as C:\SQLBackups\Full.bak) or specific tape drive (such as \\.\Tape0) that you use to record a backup onto backup media. |
|
Backup file |
A file that stores a backup set. |
|
Backup media |
The actual physical media (either disk or tape) used to store a backup set using a backup file. Backup media can store multiple backup sets (such as from multiple SQL Server 2000 backups and from Windows 2000 backups). |
|
Backup set |
The backup from a single backup operation that resides on backup media. The backup set may reside on a single backup media, a media family, or a media set. |
|
Media family |
All media (physical files or tapes) in a media set written by a single backup device for a single backup set. |
|
Media header |
Provides information about the contents of the backup media. A media header must be written before a backup set can be recorded on the backup media (this is also called initializing the backup media). Usually, the media header is written one time and remains on the media for the life of the media. |
|
Media set |
All media involved in a backup operation. Examples of media sets are: a single tape, a single disk file, one backup device writing a set of tapes, or a set of tapes written by more than one backup device. |
Tape
Disk
Creating Permanent Backup Devices
1、 可以考虑创建一个或多个永久备份设备，创建永久备份设备的好处在于方便重用，可以为永久备份设备指定一个逻辑名称，备份和恢复时指定该名称即可。永久备份设备在Master的sysdevices表中有记录。
2、 也可以每次备份时创建备份文件，这样需要每次指定物理路径，名称，比较麻烦。
SQL Server Enterprise Manager
1、 使用企业管理器创建新的备份设备：
a) 企业管理器－管理－备份－新设备，输入设备的逻辑名，并指定磁带机或文件名称即可。
2、 删除备份设备：
a) 找到要删除的设备，删除
Transact-SQL
1、 使用T-SQL添加备份设备主要使用sp_addupmdevice存储过程
2、 举例：
a) Sp_addumpdevice 'disk' , 'FullBackupDevice' , 'E:\SQLBackups\Full.bak'
b) Sp_addumpdevice 'tape' , 'TLogTapeBackupDevice' , '\\.\Tape0'
c) Sp_addumpdevice 'disk','TLogBackupDevice' , '\\NetSrv\SQLBak\TLog.bak'
3、 察看当前有的备份设备：sp_helpdevice
4、 卸载一个备份设备：sp_dropdevice ‘设备逻辑名’
5、 卸载并删除：sp_dropdevice ‘设备逻辑名’，‘delfile’
yy.net
Lesson 4: Restoring and Rebuilding System Databases
Restoring the Master Database
1、 切换到单用户模式：sqlservr –m
2、 执行常规的数据库恢复
Rebuilding the System Databases
1、 如果Mster数据库无法正常工作，可以考虑重建系统数据库
2、 使用Rebuildm工具（C:\Program Files\Microsoft SQL Server\80\Tools\Binn），执行后提示指定SQL源文件的位置（需要Data文件夹），按提示完成。
3、 按上述恢复Master的步骤执行恢复任务。
按需要连接用户数据库。yy.net
Lesson 2: Backing Up Databases, Files, Filegroups, and Transaction Logs
Perform Backups Using SQL Server Enterprise Manager
Using the Create Database Backup Wizard
Using SQL Server Enterprise Manager Directly
Perform Backups Using Transact-SQL
1、 可以使用T-SQL实现数据库的备份（BACKUP DATABASE，BACKUP LOG）
2、 T-SQL可以实现对备份集，媒体集设置密码，及恢复中断的备份。
Performing a Full Database Backup
1、 备份举例：
a) BACKUP DATABASE Northwind TO DISK = 'C:\SQLBackups\Temp.bak'
b) 完整备份Northwind到C:\SQLBackups\Temp.bak
c) BACKUP DATABASE Northwind TO NorthwindFullBackup RESTORE VERIFYONLY FROM NorthwindFullBackup
d) 完整备份Northwind到设备NorthwindFullBackup，并验证
BACKUP DATABASE Northwind TO NorthwindFullBackup
WITH FORMAT ,
MEDIANAME = 'NorthwindBackups' ,
MEDIADESCRIPTION = 'Media for Northwind Database Backups' ,
NAME = 'Full Northwind Database Backup #1' ,
DESCRIPTION = 'BackupSet #1' ,
STATS = 25
说明：完整备份Northwind到设备NorthwindFullBackup，格式化媒体，并命名媒体集为NorthwindBackups，媒体集描述为Media for Northwind Database Backups；设置备份集名称为Full Northwind Database Backup #1，描述为BackupSet #1，备份进度增量为25％。
BACKUP DATABASE Northwind TO NWindDevice1, NWindDevice2
WITH MEDIANAME = 'Media Set for Northwind Database Backups'
说明：并行备份Northwind到设备NWindDevice1和 NWindDevice2上的名为'Media Set for Northwind Database Backups'的媒体集。
Performing a Differential Database Backup
1、 执行差异备份，简单在完整备份后面加上with differential参数
BACKUP DATABASE Northwind TO NorthwindDiffBackup WITH DIFFERENTIAL
说明：差异备份Northwind到设备NorthwindDiffBackup
Performing a File or Filegroup Backup
1、执行文件，文件组备份，添加file＝logical_filename，或filegroup＝logical_filegroup_ name
BACKUP DATABASE Northwind FILEGROUP = 'SECOND_FG' TO NorthwindFGBackup
说明：备份数据库Northwind的文件组'SECOND_FG'到设备NorthwindFGBackup
Performing a Transaction Log Backup
1、 备份日志，使用BACKUP LOG，语法类似于BACKUP DATABASE
BACKUP LOG NORTHWIND TO NORTHWINDLOG
备份NORTHWIND的日志到设备NORTHWINDLOG
BACKUP LOG NORTHWIND TO NORTHWINDLOG WITH NO_TRUNCATE
备份NORTHWIND的日志到设备NORTHWINDLOG，且不裁剪日志
yy.net
Lesson 3: Restoring a User Database
Determining the Data Restoration Sequence
1、 在开始恢复数据库之前，需要确定使用什么备份，以何种顺序实现还原过程。
2、 msdb数据库记录了用户数据库备份，还原的详细信息，可以根据msdb的内容决定还原过程。
3、 如果msdb损坏，可能需要从备份恢复，如果没有最近的备份，可以通过备份文件中记录的信息，重新生成备份历史记录到msdb。
4、 确定恢复顺序后，下一步要确定需要用到的备份媒体（标记或察看）。
5、 使用T-SQL察看的方式
Table 9.2 Information Retrieval Commands in Transact-SQL
|
Transact-SQL Command |
Description |
|
RESTORE LABELONLY |
Retrieves backup media header information, including the media set name and description. |
|
RESTORE HEADERONLY |
Retrieves backup set information, including the backup set name and description for every backup set on a particular backup device. This includes internal information regarding LSNs. SQL Server uses this information to determine what backup files it needs to apply and in what order. |
|
RESTORE FILELISTONLY |
Retrieves a list of each data and log file backed up within a particular backup set. |
Performing Restorations Using SQL Server Enterprise Manager
Restoration of an Entire Database from the Failure of a Data Disk
1、 使用企业管理器恢复一个损坏的数据库
a) 损坏的数据库在企业管理器中标示为suspect（可疑的）。
b) 首先应当备份事务日志，且不裁剪非活动日志
c) 在企业管理器中选中损坏的数据库，执行恢复数据库任务，SQL会自动根据msdb中的信息，生成一个恢复列表。并且可以根据用户选择，生成新的恢复序列。
d) 可以选中某个备份集察看属性，更改所在位置。
e) 在恢复的“选项”页可以选择恢复的位置（默认为原始位置），以及恢复完成状态。
2、 恢复选项：
a) 默认是应用所有选择的备份集，并在最后一个备份集应用后设置数据库为“恢复”状态。
b) 如果打算不应用所有的备份集，并打算应用额外的事务日志则需要选择其他选项。
i. 选择“leave the database nonoperational but able to restore additional transaction logs”，等同于T-SQL的“no-recovery”选项。
ii. 选择“leave the database read-only and able to restore additional transaction log files”，等同于T-SQL的“standby mode”选项，此恢复过程中会生成一个UNDO_ DatabaseName.DAT文件（在备份文件夹），此文件保证在后备模式下保证数据库的逻辑一致性。（记录了一个回滚记录，如果应用其他事务日志，则先回归修改，再应用日志）
Restoring a Data File or Filegroup
1、 在恢复数据库常规页选“文件，文件组”进入文件，文件组恢复模式
2、 选择要恢复的文件，文件组，同时,SQL强制选中对应的事务日志以保持数据库的一致性。
3、 可以选择“Select A Subset Of Backup Sets”，对选择的文件，文件组进行过滤，包括按存储位置，存储时间，所属文件，文件组。
Restoring a Database to a Different SQL Server 2000 Instance
1、 某些时候可能需要恢复数据库到其他SQL实例，比如，恢复某些数据，而不希望恢复整个数据库。
2、 要使用企业管理器恢复到第二SQL实例，需要先在其上创建相应的数据库，最好是数据库名称，路径一致。
3、 创建好相应数据库后，再其上执行恢复数据库任务，在常规页上选“从设备恢复”（因为在SQL中无相应的备份记录，备份信息需要通过备份文件获得。）
4、 接着选择“选择设备”，由于SQL没有待还原数据的备份信息，所以，在添加设备对话框选择添加设备，为SQL指定待还原数据库设备。
5、 选择好要还原的设备以后，可以察看设备中的备份集，还原某个备份集，添加备份信息到msdb等。
Recovery of a Database to a Point in Time
1、 如果需要选择恢复到某个时间点，通常恢复某些备份集，并选择“no recovery”或“standby”模式。
2、 standby模式可以使你在每次恢复玩毕，恢复日志之前，观察数据库的状态。
3、 当你确定需要的时间点后，在恢复数据库常规页种选中“point－to－time”设置时间点
4、 不能选择比选择的最早的日志更早的时间，选中恢复到时间点后也不能选择，使数据库不可运行，但可恢复其他日志的选项。
Performing Restorations Using Transact-SQL
Restoring a Complete Database
1、 RESTORE DATABASE Northwind FROM DISK = 'C:\SQLBackups\Temp.bak'
2、
RESTORE DATABASE Northwind FROM NorthwindFullBackup WITH NORECOVERY
RESTORE DATABASE Northwind FROM NorthwindDiffBackup WITH FILE = 2,
NORECOVERY
RESTORE LOG Northwind FROM NorthwindTLogBackup WITH FILE = 4,
NORECOVERY
RESTORE LOG Northwind FROM NorthwindTLogBackup2 WITH FILE = 5
说明：其中的数字指的是备份集在备份设备中的顺序。
Restoring a Data File or Filegroup
RESTORE DATABASE Northwind FILE = 'Second_Data_File'
FROM File_Backup WITH RESTRICTED_USER, NORECOVERY, STATS = 25
RESTORE LOG Northwind FROM NorthwindTLogBackup WITH FILE = 2
Restoration Using the Move Option
RESTORE DATABASE Northwind FROM NorthwindFullBackup
WITH NORECOVERY ,
MEDIANAME = 'NorthwindBackups' ,
MEDIAPASSWORD = 'my_password' ,
MOVE = 'Northwind' TO 'D:\SQLDATA\NwindNew.mdf' ,
MOVE = 'NorthwindLog' TO 'E:\SQLLogs\NwindNewLog.ldf' ,
RESTORE LOG Northwind FROM NorthwindTLogBackup
Restoring to Standby Mode
RESTORE DATABASE Northwind FROM NorthwindFullBackup WITH NORECOVERY
RESTORE LOG Northwind FROM
NorthwindTLogBackup WITH FILE = 4 ,
NORECOVERY
RESTORE LOG Northwind FROM NorthwindTLogBackup WITH FILE = 5 ,
STANDBY = TO 'D:\SQL\UNDO.tmp'
Restoring to a Specified Point in Time
RESTORE DATABASE Northwind FROM NorthwindFullBackup WITH NORECOVERY
RESTORE LOG Northwind FROM NorthwindTLogBackup WITH FILE = 4 ,
NORECOVERY
RESTORE LOG Northwind FROM NorthwindTLogBackup WITH FILE = 5 ,
RECOVERY,
STOPAT = 'Oct 2, 2000 5:08:32 PM'
yy.net
Chapter 10 Managing Access to SQL Server 2000
Lesson 1: Understanding the Authentication Process
Understanding the SQL Server 2000 Authentication Process
1、 一个用户要想访问SQL，首先必须要有访问账号（登录账号），这个账号可以是WINDOWS系统账号，或SQL内部安全账号。
Windows Authentication
1、 如果用户被windows域所验证，则用户可以使用获得的身份信息（Kerberos ticket or access token）要求SQL进行验证，SQL把相关身份信息和内部账户信息进行比较，以决定允许or拒绝访问。（称作信任连接）
SQL Server Authentication
1、 如果用户没有被windows域所验证，但是希望使用SQL安全账户访问SQL（SQL已配置为SQL验证方式），SQL在sysxlogins表中检索用户名，如果找到，加密提交的密码，并和表中加密的密码进行比较，决定允许or拒绝访问。
Comparing Windows Authentication and SQL Server Authentication
Table 10.1 Security Capabilities of Windows Authentication Versus SQL Server Authentication
|
Windows Authentication |
SQL Server Authentication |
|
When a user logs into the Windows domain, the user name and password are encrypted before being passed to the Windows domain controller. |
The Windows operating system never authenticates the user. |
|
When an authenticated user presents authentication credentials to a SQL Server 2000 instance, the Kerberos ticket or access token submitted is encrypted. |
When a user presents authentication credentials to a SQL Server 2000 instance, the user name and password submitted are not encrypted (unless SSL is enabled for the entire session). |
|
Windows 2000 and Windows NT 4.0 support password policies (such as enforcing complex passwords and password expirations). |
SQL Server 2000 supports no password policies (passwords can be of any length or complexity, and they never expire). |
|
Windows 2000 and Windows NT 4.0 supports account lockout policies (such as for multiple attempts using an incorrect password). |
SQL Server 2000 supports no account lockout policy (a user can try an unlimited number of times until a valid name and password allow access). |
Client Net-Libraries and Authentication
1、 客户端通过客户端网络库连接到服务器端，两端要求使用相同的网络协议。
2、 默认，客户端安装了TCP/IP，命名管道协议，服务器端按该顺序尝试。
3、 命名管道，多协议网络库，需要首先建立windows的信任连接，才能连接到SQL，而TCP/IP无此限制。也即，使用TCP/IP协议（包括其他非上述两个协议）可以无需经过windows验证而连接到SQL上。
Selecting a SQL Server 2000 Authentication Mode
1、 在98，me上使用SQL，只能支持SQL验证方式
Windows Authentication Mode
1、 windows认证模式使用windows安全架构，提供更高的安全性
Mixed Mode
1、 使用第三方OS，连接SQL时，通常使用混合模式（先以windows模式验证，无法通过，则使用SQL模式）
Switching Authentication Modes
1、 SQL实例，属性，安全，切换认证模式
Understanding Security Account Delegation
1、 SQL可以使用windows的“账户信任委派”功能，即安全信任状可以跨越服务器。
2、 开启此功能需要，windows2000，AD，Kerberos，用户账号开启此功能，计算机账号开启此功能，有Service Principal Name (SPN)号。
3、 要获得SPN号，可以使用reskit中的Setspn工具
a) 可以为SQL用户，端口设置一个永久SPN号，需要使用TCP/IP网络库，多个实例，多个端口需要不同的SPN。
b) 举例：Setspn -a MSSQLSvc/SelfPacedCPU.SelfPacedSQL.msft:1433 sqlservice
c) 当SQL使用一个本地系统账号时，会自动创建一个临时SPN号，SQL服务停止时，SPN自动过期。
4、使用“账户信任委派”的好处是可以跨SQL服务并行处理事务yy.net
Lesson 2: Understanding the Authorization Process
Understanding Server-Wide Permissions
1、 SQL定义了一组服务器角色（server roles）及其权限，其作用范围是整个实例，它们不能删除，修改权限，要为用户指定权限，添加用户到该角色即可。
2、 当使用T-SQL添加用户到服务器角色时，无须首先创建登录（SQL自动完成）。
Table 10.2 Server Roles in SQL Server 2000
|
Server Role |
Members of This Server Role Can ... |
|
sysadmin |
Perform any task within a SQL Server 2000 instance and within any database. By default, all members of the Windows built-in Administrators group, as well as the sa SQL Server security account, belong to this server role. |
|
serveradmin |
Configure SQL Server 2000 using the sp_configure system stored procedure and can shut down the SQL Server service. Members of the Windows built-in Server Operators group map well to this server role. |
|
setupadmin |
Install and configure linked servers, remote servers, and replication. Can also mark a stored procedure for execution at startup, such as sp_serveroption. Members of the Windows built-in Server Operators group map well to this server role. |
|
securityadmin |
Perform all security-related operations within SQL Server 2000, including managing CREATE DATABASE statement permissions, controlling server logins, and reading the SQL Server error log. Help desk personnel and members of the Windows built-in Server Operators group map well to this server role. |
|
processadmin |
Manage processes running in an instance of SQL Server. Can kill user processes, such as runaway queries. Help desk personnel map well to this server role. |
|
dbcreator |
Can create, modify, and delete databases. Senior database administrators who are not members of the sysadmin server role map well to this server role. |
|
diskadmin |
Can manage disk files and backup devices. Generally only used for backward compatibility with SQL Server 6.x. |
|
bulkadmin |
Can execute BULK INSERT statements. Allows members of the sysadmin server role to delegate BULK INSERT tasks without granting sysadmin rights. Use carefully because members must also have read access to any data being inserted and INSERT permission on any table into which data will be inserted. |
Understanding Database-Specific Permissions
1、 在数据库上实质包括两种类型的权利信息
a) 角色：即什么用户属于该角色（该角色已预置了一种权限）
b) 权限：某些特定的权限，可以直接指派给用户的（权限包括三种状态允许，拒绝，撤销）
Table 10.3 Database-Specific Permissions
|
Permission |
Description |
|
Database owner |
A user can be specified as the owner of the database, and can perform any activity with respect to the database. |
|
DBO role |
All members of the sysadmin server role are automatically members of the dbo role within each database, and can perform any activity with respect to the database. |
|
User |
Specified users and groups can be granted user access to a database via their Windows 2000 or SQL Server 2000 security account. A permitted database user is then granted permissions within the database through a database role, the public role, and specific grants of statement and object permissions. |
|
Guest user |
An authenticated user who has access to an instance of SQL Server 2000 (but who does not have a user account to access a particular database) can be permitted to access a database as a guest user. The guest account can be granted specific permissions within the database (generally to read certain data). By default, a database does not have a guest user account. |
|
Public role |
All users permitted to access a database become members of the public role within each database. The public role can be granted specific permissions (generally permissions needed by all users of the database). |
|
Fixed database role |
Permitted users can be added to fixed database roles within a database. Fixed database roles contain predefined rights within a database to perform database-wide activities. |
|
User-defined database role |
Permitted users can be added to user-defined database roles within a database. These roles can be created by an administrator and granted specifically delineated rights and permission within the database. |
|
Statement permissions |
Permission to execute administrative statements (such as CREATE PROCEDURE) can be granted, revoked, or denied to users, groups, and roles. |
|
Object permissions |
Permission to access database objects (such as a table or view) can be granted, revoked, or denied to users, groups, and roles. |
|
Application role |
Permission to perform specific activities within a database can be granted to an application, rather than granted to a user. An application connects to a database and activates the application role. Users accessing a database through this connection gain the permissions associated with the application role for the duration of the connection. Permissions assigned to a particular user are irrelevant when the user is accessing the database through an application role. |
Fixed Database Roles
1、 对于每个数据库，SQL预定义了9中固定数据库角色，在其上定义了执行特定任务的权限，不能删除该角色，也不能修改它预定义的权限。
2、 如果固定数据库角色不能满足需要，可以自定义用户角色，指定权限。
Table 10.4 Fixed Database Roles in SQL Server 2000
|
Database Role |
Members of This Database Role Can ... |
|
db_owner |
Perform any task within a SQL Server 2000 database. Members of this role have the same rights as the database owner and the members of the dbo role. |
|
db_accessadmin |
Add or remove Windows 2000 or Windows NT 4.0 users and groups and SQL Server users in a database (using the sp_grantdbaccess system stored procedure). |
|
db_securityadmin |
Manage all permissions, roles, role membership, and object ownership in a database (using the GRANT, REVOKE, and DENY statements). |
|
db_ddladmin |
Add, modify, or drop objects in the database (using the CREATE, ALTER, and DROP statements). |
|
db_backupoperator |
Run DBCC commands, issue checkpoints, and back up the database (using the DBCC, CHECKPOINT, and BACKUP Transact-SQL statements). |
|
db_datareader |
Read data from any user table or view in the database (you have SELECT permission on all tables and views). |
|
db_datawriter |
Modify or delete data from any user table or view in the database (you have INSERT, UPDATE, and DELETE permissions on all tables and views). |
|
db_denydatareader |
Not read data from any user table in the database (you do not have SELECT permission on any objects). Can be used with the db_ddladmin role to allow an administrator to create database objects owned by the dbo role, but not be able to read sensitive data contained in those objects. |
|
db_denydatawriter |
Not modify or delete data from any user table in the database (you do not have INSERT, UPDATE, and DELETE permissions on any object). |
yy.net
Lesson 3: Creating and Managing Logins
Granting Access Using SQL Server Enterprise Manager
1、 有两种方式使用企业管理器创建登录用户
a) 直接使用企业管理器创建登录用户
b) 使用创建登录向导创建登录用户
2、 使用企业管理器一次只能创建（连接）一个用户
Using the Create Login Wizard
1、 使用向导基本流程：
a) 认证模式：
i. windows
ii. SQL
b) 创建或连接用户
i. windows－连接域用户/组（域名\账号，允许访问or禁止）
ii. SQL－创建用户（用户名，密码）
c) 选择服务器角色
d) 选择要访问的数据库
Using SQL Server Enterprise Manager Directly to Create a Login
1、 位置：安全性－登录
2、 可以设置默认登录数据库，语言，浏览域用户，指定数据库权限等。
Creating a User-Defined Database Role Using SQL Server Enterprise Manager
1、 可以自行创建“用户自定义数据库角色”，该角色是基于角色所在数据库的。
2、 可以创建的类型有标准类型，应用程序类型，可以添加用户到其中，创建完毕可以设置访问权限。
3、 创建位置：数据库－角色
Granting Access Using Transact-SQL Statements
1、 使用系统存储过程可以创建用户登录，添加用户到服务器角色，也可以创建用户自定义角色，并添加用户。
2、 针对不同认证类型，包括windows和SQL存储过程
Windows Logins
1、 只有sysadmin和sercurityadmin服务器角色才能执行下述存储过程
Table 10.5 System Stored Procedures for Administering User or Group Logins
|
System Stored Procedure |
Description |
|
Sp_grantlogin 'login' |
Creates a login for a Windows 2000 (or Windows NT 4.0) user or group. |
|
Sp_revokelogin 'login' |
Revokes the login entries from SQL Server for a Windows 2000 (or Windows NT 4.0) user or group. Does not explicitly prevent a revoked user or group from accessing SQL Server 2000, however. For example, if a revoked user is a member of a Windows 2000 or Windows NT 4.0 group that has been granted access to SQL Server 2000, that user can still connect to SQL Server. |
|
Sp_denylogin 'login' |
Prevents a Windows 2000 (or Windows NT 4.0) user or members of a Windows group from connecting to SQL Server 2000. Prevents the denied user or group from accessing SQL Server 2000 through another login linked to a Windows user or group. |
|
Sp_defaultdb 'login' , 'database' |
Changes the default database for a login. |
|
Sp_defaultlanguage 'login', 'language' |
Changes the default language for a login. |
SQL Server Logins
1、 只有sysadmin和sercurityadmin服务器角色才能执行下述存储过程
Table 10.6 System Stored Procedures for Administering Security Account Logins
|
System Stored Procedure |
Description |
|
Sp_addlogin 'login' , ['password' , 'database' , 'language', 'sid', encryption_option'] |
Creates a new SQL Server login. Password is NULL if not specified. The default database is master if not specified. The default language is the current server language if not specified. By default, the password is in the master database. |
|
Sp_droplogin 'login' |
Drops a SQL Server login. |
|
Sp_password 'old_password', 'new_password', 'login' |
Adds or changes a password for a SQL Server login. |
|
Sp_defaultdb 'login', 'database' |
Changes the default database for a login. |
|
Sp_defaultlanguage 'login', 'language' |
Changes the default language for a login. |
Server Roles
1、 服务器角色存储过程负责添加，移除用户到服务器角色
2、 sysadmin可以处理任何服务器角色的添加，移除，其他特定服务器角色成员负责处理自身成员的添加，移除。
Table 10.7 System Stored Procedures for Adding or Dropping a Login to a Server Role
|
System Stored Procedure |
Description |
|
Sp_ addsrvrolemember 'login' , 'role' |
Adds a login as a member of a server role. |
|
Sp_dropsrvrolemember 'login' , 'role' |
Drops a login as a member of a server role. |
Database Access
1、 下表的存储过程可以指定某用户（组）为访问当前数据库的许可用户
2、 需要是sysadmin server role，db_accessadmin，db_owner fixed database roles成员。
3、 使用存储过程时，可以不需要先显示的建立连接。
Table 10.8 System Stored Procedures for Adding or Dropping Logins, Users, or Groups as Permitted Users
|
System Stored Procedure |
Description |
|
Sp_grantdbaccess 'login' , 'name_in_db' |
Adds a login as a user in the current database. Although the user name in the database can be different from the login name, this practice is not recommended (generally too confusing). |
|
Sp_revokedbaccess 'name' |
Removes a login as a user in the current database. |
Database Roles
1、下表显示的存储过程可以改变数据库的所有者，数据库的安全账号等。
Table 10.9 System Stored Procedures for Changing a Database Owner, Adding or Dropping a Security Account, or Creating a User-Defined Database Role
|
System Stored Procedure |
Description |
|
Sp_changedbowner 'login', remap_alias_flag |
Changes the owner of a user database. Only members of the sysadmin server role or the current database owner can change a database owner. |
|
Sp_addrolemember 'role' , 'security_account' |
Adds a security account to a database role in the current database. You can add a user-defined database role to a fixed or user-defined database role. Only members of the sysadmin server role and the db_owner and db_security fixed database roles can add members to any database role. Members of a database role can add members to that database role. |
|
Sp_droprolemember 'role' , 'security_account' |
Drops a security account from a database role in the current database. Only members of the sysadmin server role and the db_owner and db_security fixed dababase roles can remove members from any database role. Members of a database role can remove members from that database role. |
|
Sp_addrole 'role' , 'owner' |
Adds a new user-defined database role in the current database. Although you can specify an owner of the role, using the default of dbo is recommended. Members of the sysadmin server role and the db_securityadmin and db_owner fixed database roles can create user-defined database roles. |
|
Sp_droprole 'role' |
Drops a user-defined database role in the current database. Members of the sysadmin server role db_securityadmin and the db_owner fixed database roles can create user-defined database roles. |
Viewing Access Information
Using SQL Server Enterprise Manager
1、 察看的范围包括
a) 可以登录的用户
b) 服务器角色的成员
c) 特定数据库的用户
d) 特定数据库的角色
Using Transact-SQL
1、 只有sysadmin，securityadmin可以访问sp_helplogins，其他sp，任何用户可以访问。
Table 10.10 System Stored Procedures That Return Access Information
|
System or Extended Stored Procedure |
Description |
|
Sp_helplogins [ 'login' ] |
Returns information regarding all logins or a specified login, including the databases to which a login has access and database roles of which the login is a member. |
|
Sp_helpsrvrolemember [ 'role' ] |
Returns information regarding all server roles and their members or all members in a specified server role. |
|
Sp_helpuser [ 'security_account' ] |
Returns information regarding all users or a specified user in the current database, including all database role memberships. |
|
Sp_helprolemember [ 'role' ] |
Returns information regarding all database roles or all memberships in a specified database role within the current database. |
|
Sp_helpntgroup [ 'name' ] |
Returns information regarding all Windows 2000 (or Windows NT 4.0) groups or a specified group within the current database. |
yy.net
Lesson 3: Designing an Access and Permissions Strategy
Permitting Administrator Access
1、 首先确定那些用户需要有完全访问权限。
2、 默认本地管理员组拥有完全访问权限，还可以通过以下途径获得完全访问权限：
a) 添加用户到本地管理员组
b) 添加用户到SQL，指定sysadmin角色
c) 创建global组，并加入到本地管理员组
d) 创建global组，并加入到sysadmin组
3、 随着SQL用户的增长，可以考虑创建专门的SQL管理员组，用户的增删仅仅维护该组即可。
4、 如果不是所有本地管理员组需要完全访问，可以考虑移除本地管理员组（事先保证有其他管理员组能完全访问。）
Using Windows Groups and SQL Server 2000 Server Roles
1、 如果用户不需要完全访问权限，可以使用“服务器角色”。尽量把windows用户/组添加到服务器角色中，保持一定的映射关系，简化管理
Providing SQL Server 2000 Access
1、 如果需要使用SQL认证登录，尽量把用户归入角色，指派较少的权限，用SSL加密连接过程。
2、 windows登录，考虑创建两个组，一个是需要访问SQL的，一个禁止访问SQL。
Providing Database Access
1、 首先指派数据库的所有者，其次为要访问该数据库的用户建立不同权限的组。
Using Fixed Database Roles for Administrative Access
1、 可以考虑把执行特定任务的用户加入到数据库固定角色中。
2、 如果所有新建数据库都有此需求，考虑修改model组
Providing Data Access
1、 考虑是否需要建立guest组
2、 考虑把所有用户需要的权限指派给public
3、 根据需求设置不同访问角色，权限
yy.net
Lesson 2: Using Application Roles
Understanding Application Roles
1、 可以使用应用程序角色，限制用户通过程序访问SQL中的数据（就是在应用程序角色上设置权限控制）
2、 应用程序角色不包括任何用户，且处于非激活状态，需要用程序的方式提交密码激活之。
3、 用户通过程序访问SQL，权限以应用程序角色定义的权限为准，忽略用户在SQL上的权限。
4、 应用程序角色是基于数据库的，任何超过此范围的数据库，以guest用户处理。
Creating Application Roles
1、 使用企业管理器创建应用程序角色：数据库－角色－新角色－应用程序角色，输入名字，密码，指派权限即可像普通角色一样对待。
2、 使用T-SQL创建：
sp_addapprole 'AccountingAppRole' , 'AppPassword'
创建应用程序角色AccountingAppRole，设置密码为AppPassword
Activating and Using Application Roles
1、 激活应用程序角色使用sp_setapprole存储过程
EXEC sp_setapprole 'AccountingAppRole' , {Encrypt N 'AppPassword'} , 'odbc'
激活应用程序角色AccountingAppRole，使用密码AppPassword，使用odbc加密方式
2、 应用程序角色只有在激活的连接中生效，可以有效控制用户访问权限。
yy.net
Chapter 11 Managing SQL Server Permissions
Lesson 1: Granting Database-Specific Permissions
Implementing Permissions
1、 SQL中获得数据库的访问权限可以有多种途径：
a) 从sysadmin继承
b) 一个数据库的所有者
c) 通过数据库对象，Windows2000组获得的某个数据库对象的所有者
d) 固定数据库对象成员
e) 通过数据库对象，Windows2000组获得的特定权限
f) 从public组继承的权限
g) 从guest组继承的权限
Inherited Permissions
1、 很多系统内置角色预置了一组权限，执行特定任务。比如，securityadmin固定数据库角色负责数据库安全相关任务（可以执行GRANT，REVOKE，DENY语句）。
2、 对象的所有者拥有对象包括的所有权限，并可以指派权限给其他用户。
3、 sysadmin，securityadmin，ddladmin可以修改数据库中任何对象的所有者。
Permission Actions and Conflicts
1、 权限累加，DENY优先
Managing Statement Permissions
Table 11.1 Statement Permissions
|
Transact-SQL Statement |
Permission to Execute the Transact-SQL Statement |
|
CREATE DATABASE |
Inherited by members of the sysadmin and dbcreator server roles. Although the sysadmin and securityadmin server roles can grant permission directly to security accounts to run this statement, generally the security accounts use the dbcreator server role if the system administrator delegates permission. This permission exists only in the master database. |
|
BACKUP DATABASE BACKUP LOG |
Inherited by members of the sysadmin server role and the db_owner and db_backupoperator fixed database roles. Although you can grant permission to run these statements directly to security accounts, generally you will use the db_backupoperator fixed database role. |
|
CREATE
TABLE |
Inherited by members of the sysadmin server role and the db_owner and db_ddladmin fixed database roles. Permission to create these objects is sometimes granted directly to programmers (or to a programmers group or role) during development. By default, objects are owned by the creator of the object (although objects created by members of the sysadmin server role are owned by the dbo role). Members of the db_owner or db_ddladmin fixed database roles can designate the dbo role as the owner of an object they create. In addition, members of the sysadmin server role or the db_owner or db_ddladmin fixed database role can designate any user as the owner of an object they create. However, users who are not members of one of these roles cannot designate another user or the dbo role as the owner of an object they create. |
|
CREATE TRIGGER |
Inherited by the table owner on which the trigger is defined, members of the sysadmin server role, and the db_owner and db_ddladmin fixed database roles. These members cannot grant permission to run this statement to other security accounts. |
Creating Objects and Chain of Ownership Issues
1、 通常，数据库对象的创建者就是数据库对象的所有者，这在开发时有用，但是在投入使用中可能会有问题，建议把所有数据库对象的所有者指定为dbo角色。
2、 一个对象在脚本中执行的时候，可以指定对象的所有者，如果没有指定，SQL会检索该对象所有者是否是执行语句用户或dbo，如果都不是，就会报错。
3、 视图和过程可以从表中创建，如果一个用户访问视图或过程，SQL需要检查视图（过程）的所有者，同时还要检查底层表的所有者（链）。
4、 所以，在创建对象时，最好指定dbo为对象的所有者。（默认只有sysadmin，db_ddladmin，db_owner可以创建dbo所有者的对象）
Changing Object Ownership
1、 有时需要改变一个已有对象的所有者到dbo，可以使用如下语句：
sp_changeobjectowner 'SelfPacedSQL\Bill.Customer' , 'dbo'
Using SQL Server Enterprise Manager to Grant, Deny, or Revoke Statement Permissions
1、 使用企业管理器：数据库－属性－权限
2、 可以设置：GRANT，REVOKE，DENY
Using Transact-SQL to Grant, Deny, or Revoke Statement Permissions
1、 T-SQL语法：
2、 关键字：GRANT，REVOKE，DENY
3、 待处理的语句：CREATE TABLE，CREATE VIEW等，ALL代表所有语句
4、 TO 〔user〕，〔role〕
5、 举例：
a) GRANT CREATE TABLE TO Joe
b) DENY CREATE TABLE TO Joe
c) REVOKE ALL FROM Joe
Viewing Statement Permissions Using Transact-SQL
1、 用T-SQL察看当前语句的权限：
EXEC sp_helprotect NULL, NULL, NULL, 's'
Managing Object Permissions
1、 对象权限指的是在数据库对象上可以执行的动作（语句），包括表，视图，存储过程，函数等。
Table 11.2 Types of Object Permissions Associated with Database Objects
|
Database Object Permission |
Permission on the Database Object |
|
SELECT |
Permission to view information in a table, view, column, or certain user-defined functions. Inherited by members of sysadmin server role and the db_owner and db_datareader fixed database roles. Denied to all members of the db_denydatareader fixed database role. |
|
INSERT |
Permission to add new data to a table or view. Inherited by members of sysadmin server role and the db_owner and db_datawriter fixed database roles. Denied to all members of the db_denydatawriter fixed database role. |
|
UPDATE |
Permission to update data in a table, column, or view. Inherited by members of sysadmin server role and the db_owner and db_datawriter fixed database roles. Denied to all members of the db_denydatawriter fixed database role. |
|
DELETE |
Permission to delete data from a table or view. Inherited by members of sysadmin server role and the db_owner and db_datawriter fixed database roles. Denied to all members of the db_denydatawriter fixed database role. |
|
EXECUTE |
Permission to run stored procedures and user-defined functions. Inherited by members of sysadmin server role and the db_owner fixed database roles. |
|
REFERENCES |
Permission to refer to a table with a FOREIGN KEY constraint without having SELECT permissions on the table. Inherited by members of sysadmin server role and the db_owner and db_datareader fixed database roles. Denied to all members of the db_denydatareader fixed database role. |
2、 可以把用户加入db_datareader，db_denydatareader角色，相当于拥有/取消SELECT，REFERENCES权限。
3、 可以把用户加入db_datawriter，db_denydatawriter角色，相当于拥有/取消INSERT, UPDATE, and DELETE权限。
4、 EXECUTE权限需要显示指定。
5、 表，视图，存储过程的权限是分离的，也即在不同对象上指定的权限互不影响。
Using SQL Server Enterprise Manager
1、 使用企业管理器察看对象权限：对象－属性－权限
2、 还可以基于用户所拥有的权限察看：用户－属性－权限
Using Transact-SQL to Grant, Deny, or Revoke Object Permissions
1、 举例：
GRANT SELECT ON Customer TO Joe, SalesUsers
指派对象“Customer”的“SELECT”权限给Joe，SalesUsers
DENY INSERT, UPDATE, DELETE TO Joe
禁止用户Joe的INSERT, UPDATE, DELETE权限。
使用WITH GRANT OPTION子句，把权限指派给用户的同时，也允许用户指派获得的权限给其他用户。
GRANT SELECT ON Customer TO SalesManagers WITH GRANT OPTION
要委派权限使用AS选项
GRANT SELECT ON Customer TO Joe AS SalesManagers
Viewing Permissions
1、 察看权限，使用sp_helprotect存储过程，举例：
EXEC sp_helprotect 'Customer'
察看'Customer'对象的权限
EXEC sp_helprotect NULL , 'Joe'
察看Joe拥有的语句，对象权限
EXEC sp_helprotect NULL , NULL , 'SalesManagers'
察看'SalesManagers'所有角色成员在当前数据库拥有的对象权限yy.net
Chapter 12 Performing Administrative Tasks
Lesson 1: Performing Configuration Tasks
Configuring Windows 2000 (and Windows NT 4.0)
Maximizing Throughput for Network Operations
Configuring Server Application Responsiveness
Configuring Virtual Memory
Configuring the SQL Server Service
1、 可以使用企业管理器或sp_configure配置SQL的设置
2、 使用sp_configure时，某些配置不可见（需启用show advance option），某些配置值为0，代表SQL动态配置，修改配置后需要使用reconfigure语句或重启SQL，使设置生效
Connections
Table 12.1 Connection Settings
|
Option Name |
Description |
When to Use |
|
User connections |
Specifies the maximum number of simultaneous user connections, up to the maximum value allowable. The actual number of user connections allowed is dependent upon the version of SQL Server 2000 you are running and upon the limitations of your applications and hardware. Dynamic by default. |
To avoid overloading SQL Server 2000 with too many concurrent connections. |
|
User options |
Specifies default query-processing options for all client connections. No options are set by default. |
To set global query-processing defaults for client connections. |
|
Remote access |
Permits or denies access by remote logins via remote stored procedures. Default is to allow remote access. |
To secure a local server and prevent remote access. |
|
Remote proc trans |
Enforces the use of distributed transactions using MS DTC to protect server-to-server procedures. Default is not to enforce. |
To require an MS DTC distributed transaction to protect Atomicity, Consistency, Isolation, and Durability (ACID) properties of transactions. |
|
Remote query timeout |
Specifies the number of seconds before SQL Server 2000 times out when processing a remote query. Default is 600 seconds (10 minutes). |
To manage the remote timeout default. |
Database
Table 12.2 Database Settings
|
Option |
Description |
When to Use |
|
Fill factor |
Specifies how full each index page should be when creating a new index using existing data. By default, SQL Server 2000 will fill each clustered and nonclustered index page, leaving only a small amount of space for additional data before a page split must occur. |
Set a value to minimize future index page splits. Set at 100% for a read-only table to which new data is not added. |
|
Media retention |
Specifies a default length of time to retain each backup. Default is 0. |
To set a default media retention value for all backup sets. |
|
Recovery interval |
Controls how frequently the checkpoint process runs by specifying a maximum length of time (in minutes) for automatic recovery to complete. Default is dynamic based on number of data modifications and the amount of free space in the transaction log. |
To distribute hard disk writes more evenly and avoid spikes of hard disk activity. |
Memory
Table 12.3 Memory Settings
|
Option |
Description |
When to Use |
|
Max server memory |
Specifies the maximum amount of memory SQL Server 2000 can use for its buffer pool. Default is dynamic memory allocation. |
To limit memory use on a nondedicated computer so other server applications are more responsive. |
|
Min server memory |
Guarantees a minimum amount of memory for SQL Server 2000 to use for its buffer pool. Default is dynamic memory .allocation. |
To guarantee memory use on a nondedicated computer so SQL Server 2000 is more responsive |
|
Set working set size |
Sets aside a specified amount of physical memory for SQL Server 2000. Used with the max server memory and min server memory settings. Default is zero. |
To prevent Windows 2000 from swapping out pages to other server applications (no matter how much they might need memory or how idle SQL Server 2000 is). |
|
Min memory per query |
Specifies a minimum amount of memory (in kilobytes) allocated to each query. Default is dynamic. |
Increase to improve performance of small to medium queries. |
Processor
Table 12.4 Processor Settings
|
Option |
Description |
When to Use |
|
Affinity mask |
Excludes a processor on a multiprocessor computer from processing SQL Server 2000 threads. Default is equal distribution of SQL Server 2000 processes across all processors. |
To exclude SQL Server threads from processors with specific workloads from Windows 2000. |
|
Cost threshold for parallelism |
Determines which query plans are considered long or short. Used by SQL Server 2000 to determine when it should create and execute parallel execution plans for queries. Default value is 5. |
To force or limit the use of parallel query plans on multiprocessor computers. |
|
Lightweight pooling |
Specifies the use of fibers (fiber mode scheduling) within threads, rather than separate threads for each task. Default is thread mode scheduling. |
On multiprocessor computers with excessive context switching and a consistently heavy processor load. |
|
Priority boost |
Specifies the SQL Server 2000 processor scheduling priority. Default is normal priority (which is 7). |
To increase scheduling priority on a dedicated SQL Server 2000 computer with multiple processors. |
|
Max degree of parallelism |
Specify the number of processors used in parallel plan execution. Default is to use all available processors. |
To suppress parallel plan generation. |
|
Max worker threads |
Specify the number of worker threads available to SQL Server 2000 processes. Default is 255. |
Set to smaller value on systems with low numbers of connections to improve performance. |
Integration with Active Directory
1、 SQL可以在AD中发布，在安装SQL的计算机容器中包括－SCP(服务器连接点)容器，MS-SQL-SQLSERVER对象。
2、 MSSQLServerADHelper服务负责自动在AD中注册SQL，并检查注册使用的域账户有足够的权限（本地管理员or power user）。
3、 可以使用企业管理器或SP（sp_activedirectory_scp）注册SQL服务器或数据库（sp_ActiveDirectory_Obj）
Configuring the Service Account
1、 SQL或SQLAgent服务可以使用域用户账号，也可以使用本地系统账号，通常为了实现server－to－server的任务，需要使用域用户账号。在执行某些任务时，域用户账号需要特殊的权限。
Table 12.6 Additional Permissions Required for Certain SQL Server 2000 Functionality
|
Service |
Permission |
Functionality |
|
SQL Server service |
Act as part of the operating system and replace a process-level token. |
Run an operating system command for a user who is not a member of the sysadmin server role. |
|
SQL Server service |
Member of the local Power Users or local Administrators group. |
Publish and manage SQL Server 2000 objects with Active Directory. |
|
SQL Server Agent service |
Member of the local Administrators group. |
Create operating system and Active Script jobs not belonging to members of the sysadmin server role. |
|
SQL Server Agent service |
Member of the local Administrators group. |
Configure the SQL Server Agent service to autorestart if it stops unexpectedly. |
|
SQL Server Agent service |
Member of the local Administrators group. |
Configure the SQL Server Agent service to run jobs when the processor is idle. |
Configuring the SQL Server Agent Service
General Properties
Advanced Properties
Connection Properties
Registering SQL Server 2000 Instances with SQL Server Enterprise Manager
Using the Register SQL Server Wizard
Using SQL Server Enterprise Manager
Sharing Registration Information
1、 默认情况下，SQL的注册信息，各用户之间并不共享。
要使注册信息共享，工具－选项－清除服务器注册信息各用户分别读写yy.net
Lesson 2: Setting Up Additional Features
Setting Up SQL Mail and SQLAgentMail
1、 SQL可以使用SQL MAIL，SQLAgentMail服务连接到EXCHANGE，MAIL，POP3收发消息（需要MAPI客户端应用程序，和MAPI配置文件支持）
2、 MAPI配置文件支持需要使用域用户账号。
Using SQL Mail
1、 SQL Mail是SQL中的邮件服务，它使用xp_sendmail SP发送邮件
2、 举例：
EXEC xp_sendmail 'Glo...@SelfPacedSQL.msft' ,
@subject = 'Performance Information' ,
@query = 'SELECT * FROM master.dbo.sysperfinfo'
发送系统性能信息给Gloria
3、 发送的信息可以是
a) 查询结果
b) 字符串
c) T-SQL或脚本执行结果
d) 电子页
4、 SQL使用sp_processmail，xp_findnextmsg，xp_readmail，xp_deletemail处理邮件的发送，管理员可以设置周期性任务，定期收发邮件。
Using SQLAgentMail
1、 SQLAgentMail是SQL Server Agent的邮件服务，用来在启动SQL Server Agent时定期发送邮件。
Configuring a Messaging (Mail) Profile
1、 需要配置消息配置文件，以使得邮件客户端程序可以连接到exchange，mail，pop3
Setting Up SQL Mail
1、 在企业管理器－支持服务－SQL邮件中选择配置好的配置文件，并测试之。
Setting Up SQLAgentMail
1、 企业管理器－管理－SQLAGENT－属性，配置并测试
Setting Up Linked Servers
1、 通过配置连接服务器可以支持分布式查询，从多个不同数据库、服务器收集信息。
2、 连接提供OLE DB provider，OLE DB data source，security context信息。
Setting Up Linked Servers Using SQL Server Enterprise Manager
1、 设置位置：安全性－连接服务器
Setting Up Linked Servers Using Transact-SQL
1、 使用sp_addlinkedserver
2、 举例：
sp_addlinkedserver 'SelfPacedCPU\MyNamedInstance'
3、 使用sp_addlinkedsrvlogin建立或更新登录映射
sp_addlinkedsrvlogin 'SelfPacedCPU\MyNamedInstance' ,
'FALSE' , 'SelfPacedSQL\Bill' , 'sa' , 'sa_password'
Creating an ODBC SQL Server Data Source
Configuring SQL Server XML Support in IIS
1、 可以配置SQL提供对XML的支持，可以实现：
a) HTTP访问
b) XML-Data schemas and XPath queries
c) Retrieval and writing of XML data
d) The ability to set XML documents as command text and to return result sets as a stream
yy.net
Lesson 2: Creating Jobs
Implementing Jobs
1、 job可以执行预定义的任务，可以基于时间周期，事件相应，条件，执行之。
Types of Job Steps
1、 有5类job：
a) operating system commands
b) CmdExec
c) Transact-SQL statements
d) Microsoft ActiveX scripts
e) replication tasks
Permissions and Ownership of Jobs
1、 默认情况下，job的owner是创建者，根据登录用户的安全上下文运行，而不考虑谁执行job。
2、 sysadmin可以指派job的owner给任何用户，用户执行T-SQL时，要依据在特定T-SQL上的权限决定是否可以执行，而不是看是否是owner。
3、 默认情况下，CmdExec，ActiveX job两种job step只有sysadmin可以执行，其他非sysadmin如果要执行上述两种step，需要设置一个特殊的windows帐号，称作代理帐号（proxy account）。
Multiple Job Steps and Job Responses
1、 一个job可以有多个step，每个step执行可以有success，failure两种状态，可以根据step执行的状态决定job的逻辑流程；job 结束时还可以设置job的success，failure状态，并可以把job最终的执行状态通知给operator。
Scheduling Jobs
1、 job可以设置在定时，持续，满足特定条件，警报执行，一个job可以有多个计划。
2、 每个job的计划可以有enable，disable状态，当都disable时，仍然可以通过手动，警报执行。
3、 当job disable时，手动可执行，警报不能执行。
Creating Jobs
Using the Create Job Wizard
Using SQL Server Enterprise Manager Directly
Using Transact-SQL
Configuring the Proxy Account
Reviewing Jobs and Job History
Job Properties
Job History Log
SQL Server Agent Error Log
Transact-SQL
yy.net
Lesson 3: Performing Maintenance Tasks
Updating Distribution Statistics
1、 分发状态统计是负责记录每个索引（也可以是非索引列），值的选择和分布，由SQL自动创建和维护。
2、 此信息可以为查询优化器使用，以优化查询。
3、 可以使用CREATE STATISTICS创建之，或为查询优化器自动创建。
4、 尽管分发状态统计，由SQL自动创建和维护，但是当索引或数据有较大变化，需要手动更新统计信息。
5、 手动更新统计信息使用UPDATE STATISTICS语句
6、 分发状态统计自动创建，自动更新状态可以在数据库－属性－选项，中看到。
Maintaining Indexes
1、 无论集簇还是非集簇索引，添加数据时都有可能导致降低性能（集簇是重排数据，非集簇是页数据满，重新分页时）。
2、 为了避免（？）分页，可以指定填充因子（fill factor，服务器级，or索引级别），使得页面留一定的空间，但是，索引建立前的已添满的页面就会产生分页。
3、 当产生分页的时候，可以考虑重建索引，并重新指定填充因子。
4、 可以使用的语句：
a) CREATE INDEX带DROP_EXISTING子句
b) DBCC DBREINDEX
Maintaining Full-Text Indexes
yy.net
Chapter 13 Automating Administrative Tasks
Lesson 1: Defining Operators
Methods of Notification
Pager
NET SEND
Fail-Safe Operators
1、 fail-safe operator用来在其他专注operator无法接受警报时，或Agent无法访问msdb系统表时，接受通知
Creating Operators
1、 一个operator可以是一个用户，一个消息组，一个计算机，可以接受e-mail，paper，net send三种通知信息。
Using SQL Server Enterprise Manager
Using Transact-SQL
Creating a Fail-Safe Operator
yy.net
Lesson 3: Configuring Alerts
1、 警报负责响应事件，通知operator或执行job
2、 事件可以时SQL系统消息，用户自定义消息，系统性能条件
Defining Alerts
1、 SQLAgent监视SQL消息，性能条件，满足条件，就触发通知or执行job
SQL Server Event Alerts
Performance Conditions
Responses
Configuring Alerts
Using the Create Alert Wizard
Using SQL Server Enterprise Manager Directly
User-Defined Error Messages
Using Transact-SQL
Reviewing Alerts and Alert History
Alert Properties
Transact-SQL
yy.net
Chapter 14 Monitoring SQL Server Performance and Activity
Lesson 1: Developing a Performance Monitoring Methodology
Establishing Monitoring Goals
1、 要作SQL的性能监控，首先要了解监控要实现的目标
a) 从用户的角度：最短的查询响应时间
b) 从服务器的角度：最大的查询处理能力
Identifying Performance Bottlenecks
1、 要提高SQL的性能需要首先找到性能瓶颈。
2、 性能瓶颈可能是系统资源或数据库对象的过度使用，也可以导致其他资源利用不充分。
3、 通过观察资源使用的状况可以定位瓶颈。
4、 有时，较底的数值不一定代表没有瓶颈，比如查询响应速度快，可能意味者，网络存在瓶颈，查询无法抵达。
5、 瓶颈是可能转换的，解决了一个瓶颈，可能揭示了另一个瓶颈。
Determine Trends
1、 首先要熟悉各种记数器值的常规范围，这个有助于在问题变得严重之前发现它。
2、 其次，要建立性能的基线，并周期性的更新它，以助于了解工作环境中，不同场景下，性能的外观呈现。
3、 通过对相同的指标进行长时间的监控，了解各个不同情况下的表现，有助于确定指标变化的访问，确定需要调节的对象。
Determining Resources and Activities to Monitor
1、可能影响SQL性能的因素包括：硬件资源，网络流量，数据库设计，客户端软件
Isolating the Problem
1、 隔离问题：通常使用多种监控工具寻找问题的症状，当找到症状后，再进一步监测，以找到故障源。
yy.net
Lesson 2: Choosing Among Monitoring Tools
Using System Monitor
1、 系统监视器可以在本机和远程运行，通常对常规资源的使用情况作监控，是进一步分析的基础和保障。
2、 系统监视器会影响性能，通常在远程执行或把监控结果存储于远程或其他磁盘。
3、 系统监视器可以支持实时监控和记录监控结果到文件，方便以后分析。
4、 系统监视器包括几个待分析的对象：性能对象，记数器，实例
5、 系统监视器是可扩展的，下表是SQL安装后，添加到系统监视器的性能对象
Table 14.1 Performance Objects for Tracking SQL Server Activity
|
SQL Server Performance Object |
This Counter Measures. . . |
Used to Monitor. . . |
|
SQL Server: Access Methods |
Access to and allocation of logical SQL Server database objects (such as data and index pages). |
Index and query efficiency based on types of pages accessed, page splits, and page allocations. |
|
SQL Server: Backup Device |
Backup and restore performance information on a per-device basis. |
Throughput or progress of backup and restore operations on a per-device basis. |
|
SQL Server: Buffer Manager |
Memory buffer use, including free buffer pages and buffer cache hit ratio. |
Lack of physical memory, frequency of disk reads, and efficiency of query performance. |
|
SQL Server: Cache Manager |
Memory used for caching stored procedures, Transact-SQL statements, and triggers. |
Efficiency of plan caching and reuse. |
|
SQL Server: Databases |
Database activity, including active transactions, bulk copy throughput, backup and restore throughput, and transaction log activities. |
Level of user activity in a database, autogrowth and autoshrink operations, fullness of the transaction log, and performance levels for bulk copy, backup, and restore operations. |
|
SQL Server: General Statistics |
General server-wide activity, including user connections and logins. |
Overall connection activity. |
|
SQL Server: Latches |
Internal SQL Server resource locks (called latches). |
Performance bottlenecks based on the number and length of waits for internal resource locks to be granted. |
|
SQL Server: Locks |
Individual lock requests made by SQL Server, including number of lock timeouts and number of deadlocks. |
Overall number and types of locks. Minimizing locks improves concurrency and performance. |
|
SQL Server: Memory Manager |
Overall memory usage, including memory used for connections and locks, available memory, and granted memory. |
Overall memory usage for various objects, to determine whether a memory shortage exists. |
|
SQL Server: SQL Statistics |
Transact-SQL queries, including T-SQL compilations, T-SQL recompilations, and number of batches received. |
Query compilation speed and overall efficiency of the query optimizer. Minimizing compilation time and re-compilation frequency improves performance. |
|
SQL Server: User Settable Object |
Custom counters based on stored procedures or Transact-SQL statements. |
Custom information, such as product inventory or number of orders. |
yy.net
Using Task Manager
Using SQL Profiler
1、 用来跟踪SQL事件，并保存在表或文件（.TRC）中。
2、 工作模式：
a) SQL运行中产生各种事件（光标位置，登录，T-SQL，错误，SP，批处理等）
b) Profiler设置一组要跟踪的事件（存放在.TDF跟踪模板文件中），并把结果保存在表或文件中。
3、 趋势分析：使用Profiler长时间跟踪某些事件，非常有利于趋势分析。
4、 不要跟踪太多的事件，默认.TRC 5M一个，文件满，自动生成新的.TRC(数字)的文件，还可以使用条件过滤，控制文件的大小。
5、 事件按组分成若干组，每组事件上有若干事件类（即在该组上可以使用的事件）
Table 14.2 Event Categories That Can Be Monitored with SQL Profiler
|
Event Category |
Event Classes in This Event Category Monitor... |
|
Used to Monitor. . . |
|
Cursors |
Cursor creation, use, and deletion events. |
The actual types of cursors being used, which is not necessarily the type specified by the calling application. |
|
Database |
Automatic data and transaction log file growth and shrinkage events. |
Automatic growth of data and transaction log files, to properly size these files for maximum performance. |
|
Errors and Warnings |
Error and warning events, such as stored procedure compilation errors or missing column statistics warnings. |
The length of waits for resources, which can indicate contention issues. Also, the efficiency of query optimizer execution plans. |
|
Locks |
Lock acquired, canceled, escalated, and released events. |
Contention issues based on type and length of locks. Also, deadlocks and timeout events. Can generate large files. |
|
Objects |
Object creating, opening, closing, dropping, and deleting events. |
Ad hoc creation of objects by applications and users. Can generate particularly large files. |
|
Performance |
Query optimizer showplan information and the execution of SQL data manipulation language (DML) operators. |
Query execution and query optimizer efficiency by capturing the plan tree, the query plan cost estimates, the query execution statistics, and the query plan tree. |
|
Scans |
Tables or indexes scanned. |
Types of scans being performed on an object. |
|
Security audit |
Audit events. |
Logins, logouts, security and permission changes, password changes, and backup and restore events. |
|
Server |
Memory change events. |
Changes in SQL Server memory usage greater than 1 MB or 5% of the maximum server memory, whichever is greater. |
|
Sessions |
Length of time per user connection and amount of SQL Server processor time used by queries submitted using each connection. |
Connected users, databaseactivity, and CPU time used,for charging for usage and activity |
|
Stored procedures |
Stored procedure execution information, including cache hits and misses, order of execution, when aged out of cache, and when recompiled. |
Memory to determine additional memory needs. Also, use of stored procedures by applications. |
|
Transactions |
Transaction execution information. |
Types of logging activity by applications. Also, transaction commits and rollbacks, and distributed transactions. |
|
TSQL |
Execution of SQL Server statements and batch events. |
Accuracy of application results compared to expected results during application testing. Also, events that take a long time to run, including the users who submit these queries. |
|
User configurable |
Custom events. |
User-defined events, such as application progress reports at specified points during application testing. |
yy.net
1、 Profiler预定义了一些模板，可以使用或修改之
Table 14.3 PreconFigured Trace Templates in SQL Profiler
|
Template Name |
A Trace Using This Definition Captures. . . |
|
SQLProfilerSP_Counts |
The number of stored procedures that run; groups the results by stored procedure name and includes the number of times the procedure has executed. |
|
SQLProfilerStandard |
General information regarding SQL batches and stored procedures executed and their connections, in execution order. |
|
SQLProfilerTSQL |
Each Transact-SQL statement issued in execution order including the time each statement was issued. |
|
SQLProfilerTSQL_Duration |
Each Transact-SQL statement issued; groups the results by duration (in milliseconds). |
|
SQLProfilerTSQL_Grouped |
Each Transact-SQL statement issued; groups the results by user submitting them. |
|
SQLProfilerTSQL_Replay |
Details about each Transact-SQL statement issued, in sufficient detail to be used for replay in SQL Query Analyzer. Use this preconfigured template as a starting point for capturing data for replay. |
|
SQLProfilerTSQL_SPs |
Details in execution order about each stored procedure that executes, including the Transact-SQL commands within each stored procedure. |
|
SQLProfilerTuning |
Duration information and binary data about each stored procedure issued and SQL batch executed. Binary data includes information such as session level settings, type of cursor issued, and lock type. |
Using SQL Query Analyzer
1、 主要是通过察看和执行查询的“执行计划”
Using the SQL Server Enterprise Manager Current Activity Window
1、 可以察看进程，用户活动，进程的锁定，对象的锁定，可以杀掉死锁的进程，给用户发送消息，察看用户最近执行的语句等。
Using Transact-SQL
System Stored Procedures
Table 14.4 System Stored Procedures for Monitoring Performance
|
System Stored Procedure |
The Procedure Reports. . . |
|
Used to Monitor. . . |
|
sp_who |
Snapshot of current users and processes, including the currently executing command |
Active users and their processes |
|
sp_who2 |
Snapshot of current users and processes with additional columns (also more readable) |
Active users and their processes |
|
sp_lock |
Snapshot of current locks |
Blocking locks and deadlocks, and the process causing them |
|
sp_spaceused |
Estimate of current disk space reserved and used by a table or the entire database |
Database or object space usage |
|
sp_monitor |
Statistics, including CPU use, I/O use, and idle time since last execution of sp_monitor |
Volume of work performed during period of time |
DBCC(Database Console Commands)
1、DBCC用来检查和修复数据库及对象的一致性问题（物理和逻辑），还可以监控数据库的状态。yy.net
Table 14.5 DBCC Statements Used for Monitoring
|
DBCC Statement |
Statement Activity |
|
DBCC CHECKCATALOG |
Verifies that every data type in the syscolumns table also has an entry in the systypes table, and that every table and view in the sysobjects table has at least one column in the syscolumns table. |
|
DBCC CHECKDB |
Checks the allocation and structural integrity of all objects in a specified database. Use the repair option to correct minor inconsistencies. Includes the functionality of the DBCC CHECKALLOC and DBCC CHECKTABLE statements. |
|
DBCC CHECKCONSTRAINTS |
Verifies foreign key and check constraints on a table. |
|
DBCC CHECKFILEGROUP |
Performs the same function as DBCC CHECKDB, but limited to a single specified filegroup and required tables. |
|
DBCC CONCURRENCYVIOLATION |
Checks how many times more than five batches were executed concurrently on the SQL Server 2000 Personal Edition or the SQL Server 2000 Desktop Engine. Performance of the database engine is limited when users execute more than five batches concurrently on these editions. |
|
DBCC DROPCLEANBUFFERS |
Removes all clean buffers from the buffer pool. You use this statement to test queries with an empty buffer cache without shutting down and restarting the server. |
|
DBCC OPENTRAN |
Displays information regarding the oldest active transaction and oldest distributed and nondistributed replicated transactions within a specified database. |
|
DBCC PROCCACHE |
Displays information regarding the contents of the procedure cache, including number of stored procedures in cache, the number currently executing, and the size of the procedure cache. |
|
DBCC SHOWCONTIG |
Displays fragmentation information for the data and indexes of a table. |
|
DBCC SHOW_STATISTICS |
Displays the current distribution statistics for an index or statistics collection on a table. |
|
DBCC SQLPERF (LOGSPACE) |
Displays statistics about transaction log space size and percent used in all databases for a SQL Server instance. |
|
DBCC UPDATEUSAGE |
Checks and corrects inaccuracies in space usage reports by the sp_spaceused system stored procedure for the sysindexes table. |
Built-in Functions(also called T-SQL globals)
Table 14.6 Commonly Used T-SQL Global Counters
|
Counter |
Count Since SQL Server Started |
|
@@CONNECTIONS |
Number of connections (including attempted connections) |
|
@@CPU_BUSY |
Time in milliseconds that the processor has spent working |
|
@@IDLE |
Time in milliseconds that SQL Server has been idle |
|
@@IO_BUSY |
Time in milliseconds that SQL Server has spent performing input and output operations |
|
@@PACK_RECEIVED |
Number of input packets read from the network |
|
@@PACK_SENT |
Number of output packets written to the network |
|
@@PACKET_ERRORS |
Number of network packet errors that have occurred on connections |
|
@@TOTAL_ERRORS |
Number of disk read/write errors encountered |
|
@@TOTAL_READ |
Number of disk reads (not cache reads) |
|
@@TOTAL_WRITE |
Number of disk writes |
Using SNMP
yy.net
Lesson 3: Performing Monitoring Tasks
Monitoring Resource Usage
Task Manager
System Monitor
Memory Objects and Counters
I/O Objects and Counters
Processor Objects and Counters
Monitoring Stored Procedures, Transact SQL Batches, and User Activity
Monitoring Current Locking and User Activity
yy.net
Lesson 2: Planning for Replication
Planning for Replication Security
1、 复制的安全性分为若干的层次：
a) 只有sysadmin可以添加数据库，执行复制任务；创建出版者，发行人，订阅者。
b) 只有sysadmin和db_owner可以创建和配置发行物和订阅物。
c) 只有sysadmin和replmonitor可以察看复制活动。
2、 如果使用远程发行人，则必须使用SQL认证（使用distributor_admin账号），可以在远程发行人上配置出版者为信任连接或非信任连接。
3、 默认快照复制存放的文件夹是C:\Program Files\Microsoft SQL Server\Mssql\Repldata（对应的共享是\\SelfPacedCPU\C$\Program Files\Microsoft SQL Server\Mssql\Repldata，98上不创建此共享）
4、 在此共享上，快照代理有FC，事务，合并代理是READ;默认情况下这些代理和SQL AGENT使用相同的账号；复制代理需要有本地管理员组的账号；如果没有，或是98，me，则需要创建一个“hare”的账号，并设置适当的权限。
5、 出版物上有一个PAL列表，控制谁可以访问之（默认是sysadmin和出版物的创建者）
Filtering Published Data
1、过滤可以是基于行或列，静态或动态的。
Choosing Initial Snapshot Options
1、可以把快照数据复制到其他地方（如网络驱动器，光碟），可以选择压缩。
yy.net
Lesson 3: Implementing Replication
Configuring Distributor and Publisher Properties
Creating a Publication
Configuring Merge Replication
Configuring Push Subscriptions
Configuring a Pull Subscription
Lesson 4: Monitoring and Administering Replication
Monitoring with Replication Monitor
Publications
Agents
Alerts
Reviewing and Modifying Distributor Properties
Reviewing and Modifying Publication Properties
Reviewing and Modifying Replication Agent Profile
Reviewing and Modifying Subscription Properties
Viewing and Resolving Merge Replication Conflicts
Using Windows Synchronization Manager
yy.net
Chapter 15 Using SQL Server Replication
Lesson 1: Introducing Replication
Describing Replication
Reasons to Replicate Information
1、 减少网络负荷（可以定制在网络不繁忙的时候复制，提供本地查询）
2、 OLTP for 决策支持（也是本地查询的概念）
3、 数据整合
4、 数据冗余
5、 缩放，负载平衡
6、 支持移动用户，离线更新（类似offline folder）
Types of Replication
1、 有三种类型的复制：
a) 快照（snapshot）
b) 事务（transaction）
c) 合并（merge）
2、 快照复制的特点：
a) 周期性，特定时间
b) 复制对象：整个数据集
c) 数据相对静止，有少量变化的数据
3、 事务复制的特点：
a) 首先执行一个快照复制，然后执行事务复制
b) 增量复制自快照以来，事务日志产生的变化的增量数据
c) 数据有较好的同步性
4、 合并复制的特点：
a) 首先执行一个快照复制（方向是L－>R）
b) 然后执行合并复制（数据变化部分，基于数据同步，资源冲突的目的，方向是L<－R）
c) 然后再次更新R（L－>R）
d) 适用于大量数据更新，或离线数据更新
Replication Terminology
1、 发送信息者称作“出版者”（publisher）
2、 出版的信息称作“出版物”（publication），来源于一个数据库，可以包含一个或多个“文章”（article）
3、 文章的内容可以是：
a) 表或表的一部分
b) SP或VIEW定义
c) VIEW
d) 一个SP的执行
e) 一个索引的视图
f) 一个用户自定义函数
4、 发行人（Distributor）：出版者使用发行人辅助复制过程，发行人存储发行数据库，历史记录，metadata；发行人精确的角色和出版者的复制方式有关；发行人可以在本地，也可以在远端。
5、 接受复制信息者称作“订阅者”（Subscribers），订阅者可以有选择的从一个或多个出版者那里订阅出版的内容（订阅），基于复制的类型，订阅者可以修改订阅内容，复制订阅内容回出版者；支持认证订阅和匿名订阅。
6、 复制任务由复制代理（replication agent）自动执行，复制代理是SQL AGENT的JOB，基于特定周期执行特定任务；
7、 复制代理的安全上下文：
a) 2k/nt4――SQL AGENT DOMAIN USER ACCOUNT
b) 98,ME――LOGIN-IN USER ACCOUNT
8、 有多种复制代理，不同复制类型使用一个或多个复制代理。
a) 快照代理：开始一个初始的快照复制，包括schema信息，每种类型的复制都会用到此种代理，每个复制一个。
b) 发行代理：移动快照，增量改变数据从发行人到订阅者；快照，事务复制使用该代理；默认情况下，所有到一个出版者的订阅共享一个代理，也可以设置每个订阅使用独立的代理。
c) 日志阅读代理：移动事务日志中标记为待复制的事务日志，从出版者到发行人。事务复制使用此种代理，在发行人处运行。
d) 队列阅读代理：处理离线订阅者到发行人的改变。快照，事务复制在队列查询使能时也会用到此种代理，运行在发行人处，并且一个实例，对于所有出版者到一个发行人之间，只有一个这样的代理。
e) 合并代理――移动快照从发行人到订阅者；移动和调节出版者和订阅者间的数据改变；取消过期的订阅（14天）；合并复制使用此代理；每个订阅者在和出版者，发行人同步数据时有私有的合并代理。
f) 代理历史清除代理－清除发行数据库关于代理的历史纪录，维护数据库的大小，所有类型的复制都使用之，默认10分钟运行一次。
g) 发行清除代理――清除发行数据库的待复制事务日志，去除设定时间（72h）未更新的订阅者的订阅信息（如果是匿名，则不清除），快照，事务复制使用此代理，默认10分钟运行一次。
h) 过期订阅清除代理――删除过期的订阅，所有类型复制使用，默认一天执行一次。
i) 数据失败重新订阅代理――重新开始数据失败的订阅，手动执行。
j) 复制检查代理――检查其它代理活动情况，并记录到windows日志
Understanding the Types of Replication
Snapshot Replication
1、 工作流程：
a) 快照代理周期性的复制待出版的数据到发行人的快照文件夹
b) 发行代理周期性的复制快照文件夹的数据到订阅者，同时为每个订阅者更新出版信息。
c) 快照代理运行在发行人处，发行代理运行在发行人处或订阅者处。
d) 所有代理记录历史信息和错误信息到发行数据库。
2、 快照复制适用于数据变化不大，信息不要求很及时的情况。
3、 快照复制可以允许订阅者选择及时更新或队列更新（可能可以使用户只接受到最新的信息，而不是每个update都更新）；可以设置为：“复制是事务日志的一部分”，这样可以使得如果发生冲突，要么复制全部数据，要么回滚数据。
4、 当选择立即更新，会在订阅者和出版者之间产生一个自动的两步骤的事务提交；准备阶段和提交阶段，由订阅者的DTS服务负责控制（称作事务管理者）；在准备阶段，事务管理者和出版者，订阅者的SQL SERVER服务相配合，每个动作都作为一个资源管理者，保证每个事务在每个数据库都是成功的。在提交阶段，如果事务管理者收到的每个资源管理者发出的准备通知都是成功的，就向每个资源管理者发出提交命令，从而实现事务的提交。如果存在冲突，则显示订阅失败。发行者删除冲突部分。
5、 如果选择队列更新，由订阅者所作的修改存储在一个队列中，并周期性的更新出版者；可以不要求网络连通，在网络恢复连通时，更新数据；此队列可以在SQL数据库中，也可以在windows消息队列中。如果存在冲突，由出版者创建时建立的“资源冲突策略”解决。
6、 如果同时选中两者，则队列更新作为立即更新的备份
Transactional Replication
1、 首先由快照代理复制待复制数据到快照文件夹，发行代理应用该数据到所有的订阅者
2、 日子阅读代理跟踪待复制数据事务的变化，并记录到发行数据库，发行代理再次按顺序应用变化到订阅者。
3、 如果其中包括一个SP，修改大量数据，则SP被复制到订阅者，而不是由发行人上应用。
4、 发行代理可以持续运行，或按照特定周期运行，可以保证出版者，订阅者间数据的较小延迟。
5、 当所有订阅者收到所有事务日志后，由发行清理代理清理发行数据库。
6、 如果订阅者在订阅期内没有收到复制的事务日志，则复制的日志被删除，订阅被挂起（可重新激活，激活后重新开始快照复制），保持发行数据库的大小。
7、 日志复制也支持立即/队列复制，与快照概念相同。
Merge Replication
1、 首先由快照代理复制待复制数据到快照文件夹，合并代理应用该数据到所有的订阅者
2、 合并代理监控并复制变化的待复制数据（双向，出版者<->订阅者）
3、 如果存在冲突，则由管理员设定的冲突解决方案解决。
4、 合并复制使用唯一列跟踪表的复制情况。
5、 如果表不存在唯一列，快照代理复制创建。
6、 快照代理在出版服务器上建立出发器，跟踪变化，并记录到合并系统表；
7、 合并代理在初始快照复制后，在每个订阅者上建立标识触发器。
Selecting a Physical Replication Model
1、 即各个角色所在的位置，及各种情况的工作模式：
a) 出版者和发行人在一台主机――适用于复制量较小，或合并复制的情况。
b) 出版者和发行人在不同主机――适用于复制量较大。
c) 如果通讯费用昂贵，可以使用一台订阅者接受，然后由此订阅者充当出版者（Republisher）
d) 可以使用一个中心订阅者收敛数据
Choosing Replication Implementation Tools
1、 主要工具：企业管理器
2、 其他工具：
a) ActiveX controls
b) SQL-DMO
c) Replication Distributor Interface
d) Stored procedures
e) Windows Synchronization Manager
f) Active Directory Services