02第三章 管理sql server.docx
《02第三章 管理sql server.docx》由会员分享，可在线阅读，更多相关《02第三章 管理sql server.docx（19页珍藏版）》请在冰豆网上搜索。
02第三章管理sqlserver
在命令行启动/停止mssqlserver从命令行打开查询分析器
——netstartmssqlserverisqlw
——netstopmssqlserver
第三章管理SQLServer2000数据库
一、数据库结构
1当创建了数据库，就创建了数据的存储结构。
这个结构包含两种类型的文件：
数据文件（.mdf主的或.ndf从的），日志文件（.ldf）。
.mdf——主数据文件
.ndf——从数据文件
数据文件——存放表、索引等
日志文件——存放所有对数据库修改（插入操作、删除操作、修改操作、创建表操作等等）的历史信息
----在已经投入运行的数据库中，日志文件远远大于数据文件
2限制：
行的最大大小不能超过8K（一行中所有列的列宽加在一起，不能超过8060字节；如果超过了，创建表的命令就会失败——叫做“行不能跨页”）
提示：
一个数据库可以：
有且仅有一个MDF文件，用于存放系统数据和用户数据
零个或多个NDF文件，用于存放用户数据（由管理员创建）
一个或多个LDF文件，用于存放日志数据
***数据被分散到多个数据文件中（如果有多个MDF或NDF文件）
问题：
一个表有1000行，每行占空间5000个字节，问该表占多个K字节：
是1000*5KB吗？
答案是：
1000*8KB
所以，在设计表时一个数据页中应包含尽可能多的行。
节省空间性能优化
二、创建与管理数据库
1创建sqlserver2000数据库
权限要求：
必须是系统管理员和或者被系统管理员授于了createdatabase权限的用户
在一个服务器上，最多可以创建32767个数据库。
规划：
数据库名称：
要满足命名规范
数据库文件位置
数据库文件的属性：
（初始）大小、（是否自动）增长率、（有无）最大限制
排序规则（本数据库是否与服务器采用同样的排序规则，这个非常重要）
创建SQLServer2000数据库语法
**创建新数据库要在mastre的数据库环境中。
因为mastre存放了这个系统的信息，每创建一个新的数据库，也就是在mastre中添加了一个新信息。
usemaster打开
go执行
createdatabasehrdb创建
on
（name=hr_dat,数据文件的名
filename='c:
\programfiles\microsoftsqlserver\mssql\data\hrdata.mdf',保存的位置
size=10MB,大小
maxsize=50mb,最大
filegrowth=5争张
logon日志文件等的放在哪里和文件的名
（name=hr_log,filename='c:
\programfiles\microsoftsqlserver\mssql\data\hrlog.ldf',
size=5mb,maxsize=25mb,filegrowth=5mb）
go
在企业管理器创建数据库
例一：
没有规划的数据库testdb测试数据库
**模版数据库model——所有新建的数据库都以此为模板，新建的数据库会拷贝model数据库的很多属性、选项。
差别：
a数据库的名称；b数据文件的特征：
大小、位置；c日志文件名称
数据库的属性：
“常规”——
普通：
处于联机、可用状态；
其它状态：
脱机、挂起、怀疑等不可用状态
大小：
可用空间：
用户数：
排序规则名称：
——默认情况下，排序规则名称都与SQLServer服务器排序规则一致。
“数据文件”——
文件名，默认情况下，是数据库名加下划线加data
位置，默认情况下，是安装时指定的位置
分配空间
“文件属性”
自动增长
系统优化建议：
根据实际使用来设置文件的初始大小，自动增长。
如果数据量的更新、插入非常频繁，而且量比较多，可以使用按百分比增长；
“事务日志”——
位置：
以数据文件位置一致
分配空间：
**技巧—修改数据文件、日志文件的保存位置（默认是安装目录）
右击数据库—属性—数据库设置－新数据库位置
例二、有规划的数据库hrdb人力资源数据库
数据文件：
初始大小10M；文件自动增长；每次增长5M；最大大小50M
日志文件：
初始大小5M；文件自动增长；每次增长5％；文件增长不受限制
在查询分析器中创建数据库
设置数据库选项
我们所创建的数据库，都拷贝了model数据库各个选项；在大多情况下这些来自于模版的选项已经够用了，无需再做额外的设置。
但是，在某些特殊的场合，还是要做特殊的设置。
右击数据库——属性——选项：
（1）访问——默认情况下，不做选择。
而是在数据库中做用户权限的分配。
限制访问：
——适用于开发环境，只有数据库管理员、数据库创建者、或者系统管理员才能访问，以避免这个场合被打扰。
——单用户一次只能有一个用户访问，通常用在数据库的维护场景下，比如，恢复数据库时。
（2）只读
（3）故障还原
——日志文件中记录所有对数据库的修改；故障还原模型的设置，直接影响到哪些数据会被记录到日志文件中。
完全——所有的修改都记录，在应用环境，已经上线使用。
大容量日志——某些块操作，比如批处理不被记录。
简单——所有对数据的修改都不被记录；在开发、测试环境。
所有“数据库选项”的操作都可以在图形化界面完成；唯独“故障还原”要在命令行中实现。
alterdatabasehrdb
setrecoverysimple
simple——简单
full——完全
（4）设置（保持默认最好）
“ANSINULL默认设置”——
“自动关闭“——如果这个数据库当前没有用户访问，就是没有连接的话，就自动关闭；好处在于能释放一些内存给操纵系统；坏处在于，一旦下一次再有一个用户访问这个数据库时，第一次访问将会花费比较长时间。
对于处于生产型的数据而应，应取消自动关闭。
“自动收缩“——sqlserver会定期检查数据文件中是否有过多的空间没有被使用，被浪费掉了。
如果有的话，就自动收缩，把浪费的空间去掉还给操作系统。
但是，当进行自动搜索的时候会影响性能，此时访问数据库会感觉到很慢。
所以，建议处于运行、已经上线的数据库不要“自动收缩”。
“自动更新统计信息”——
“自动创建统计信息”——
“残缺页检测“——“残缺页”,在极其偶然情况下丢失了指针的数据页；这时将会影响数据库的一致性。
“兼容性”——对应相对的版本
数据库的管理任务
（1）生成SQL脚本——将图形化操作创建对象的操作，生成命令行脚本，以便管理。
练习：
把创建hrdb1数据库的操作生成SQL脚本。
（2）分离数据库
用于：
把一个数据库从一台服务器移植到另外一台服务器上。
分离完成后，该数据库从系统中消失，既删除了注册信息；但是文件仍然存在。
（3）附加数据库
移植数据库的时候，日志文件可以不必移植（太大的话）；sql会自动创建新的日志文件。
（4）脱机
灰色、并注明；脱机的数据库不能被访问。
在维护的情况下，把数据库置为脱机。
三、管理数据库文件大小
1、管理数据文件和日志文件增长
⏹使用自动文件增长——在创建数据库的时候指定允许自动增长
⏹手工扩展文件大小——
以上两种方法的局限，就是都不能超出磁盘的容量。
⏹添加额外的文件——在别的磁盘上添加数据文件和日志文件，使得能容纳更多是数据。
手工扩展文件大小语法示例
alterdatabasehrdb
modifyfile
（name=hr_dat,size=20mb）
go
添加额外的文件语法示例
alterdatabasehrdb
addfile
（name=hrdb2,filename='c:
\programfiles\microsoftsqlserver\mssql\data\hrdat2.ndf',size=5mb,maxsize=100mb,filegrowth=5mb）
go
练习1：
将hrdb1的事务日志文件的大小改为10M；
为hrdb1添加一个数据文件hr_data2，大小为50M
练习2：
在命令行中执行以下操作：
事务日志文件的大小改为20M；
为hrdb1再添加一个数据文件hr_data3，大小为50M
2、管理数据文件
（1）据库或数据库文件——自动收缩
⏹使用自动收缩
数据库选项中允许“自动收缩”
alterdatabasedatabasenamesetauto_shrinkon命令
sp_dboption‘databasename’,’autoshrink’,’true’命令
⏹自动收缩将在未用空间超过25％后开始
⏹自动收缩在后台运行，以减少对用户活到的影响
（2）收缩数据库或数据库文件——手工收缩
dbccshrinkdatabase
（database_name[,target_percent][,{notruncate|truncateonly}]）
限制：
不能收缩到比初始创建更小，即使有多余空间。
手工收缩数据和日志文件
dbccshrinkefile
（{flie_name|file_id}
{[,target_size]
|[,{emptyfile|notruncate|truncateonly}]}）
对于日志文件，有时需要和backuplog配合使用
练习：
手工收缩hrdb1
四、删除数据库
⏹在企业管理器中删除一次只能删除一个
⏹用命令dropdatabase删除一次可以删除多个
限制：
当用户正在连接时不能删除
系统数据库不能删除
练习：
删除hrdb1数据库、删除master数据库
查看正在连接数据库的用户：
管理——当前活动——进程信息——
取消连接：
右击找到的“进程消息”——取消连接
***在实际应用中，该操作很难实现，因为取消进程的速度，可能远远赶不上新用户连接的速度。
应该使用脚本的方式。
Alterdatabasehrdb1
Setsingle_user——置为单用户模式；运行成功后，新用户再连接是都会失败，都会得到一个提示“当前数据库已处于单用户模式”；而目前已有的连接将被保持，直到它的工作完成。
Alterdatabasehrdb1
Setsingle_user
Withrollbackimmediate——置为单用户模式并立刻断开所有连接，如果它的工作还没完成，则撤销。
Alterdatabasehrdb1
Setsingle_user
Withrollbackafter60seconds——置为单用户模式，在60秒钟后断开；但是之前会发送一个提示信息。
五、规划数据文件和日志文件位置——数据库的物理设置
1指导思想：
⏹要支持高性能和容错
⏹将数据文件分散存放以提高I/O并发
⏹将日志文件和数据文件分别存在在不同的磁盘上
⏹把临时数据库放在单独的磁盘上
实现技术
⏹RAIN磁盘阵列
⏹文件组
2使用RAID提高数据库I/O吞吐
⏹适合数据库的RAID技术有：
RAID0（调带级，速度最快、不支持容错、对磁盘的消耗最大，要浪费一半的磁盘空间）、RAID1（镜像，支持容错）
RAID5（支持容错）
⏹实际运用中通常结合多种RAID技术
数据文件：
RAID0+1、RAID1+0
日志文件：
RAID1
六、在数据库中使用文件组提高数据库性能和管理性
⏹文件组定义
sqlserver中一个或多个数据文件的命名集合，它构成分配或用于数据库管理的单个单元。
文件不能跨文件组（一个文件组中可以包含多个数据文件，而数据文件不能跨文件组）
⏹文件组分为：
主文件组（primary）:
有且仅有一个主文件组,用于存放.mdf，.ndf文件；
其次可以创建多个用户定义文件组，仅用于存放ndf文件。
⏹默认文件组选项——主文件组和用户定义文件组都具有的选项
在一个数据库中，只能有一个文件组是默认文件组；默认是主文件组。
在创建表和索引之类的对象时（这些对象含有数据），如果没有明确的指明放
在哪个文件组，则放在默认文件组中。
在数据库中使用文件组
文件组原理
文件组对组内的所有文件都使用按比例填充策略。
当将数据写入文件组时，sqlserver2000根据文件中的可用空间量将一定比例的数据写入文件组的每个文件，而不是将所有的数据先写满第一个文件，接着再写入下一个文件。
此时，如果每个文件组都放在不同的磁盘上，便可实现类似并发I/O的效果。
02_在数据库中使用文件组.jpg
文件组实现步骤
alterdatabasehrdbaddfilegrouptestfg1－为hrdb添加一个用户自定义文件组
alterdatabasehrdbaddfile（name=test1dat3,fllename=’c:
\programfiles\Microsoftsqlserver\mssql\data\t1dat3.ndf’,size=5mb,maxsize=100mb,filegrouwth=5mb）tofilegrouptest1fg1
——将新创建的数据文件，放入文件组test1fg1中。
Alterdatabasehrdbmodifyfilegrouptest1fg1default－设为默认的文件组（设为默认文件组的好处在于，之后所创建的用户表、用户数据都会被被放进来；不会同主文件组中的系统数据发生冲突）
Createtablet1（f1int）ontestfg1——在创建对象的时候指明存放至用户文件组
练习：
1为hrdb数据库多创建一个文件组fg1
2创建数据文件，归属的文件组指向之前所创建的文件组
3将用户定义的文件组设为默认文件组。
练习2：
在企业管理器中删除文件组
◎◎◎（要先移除文件再删除文件组）
1删除fg1
2删除testfg1
何时选用文件组、何时不选用文件组
在数据库中使用文件组：
基于维护的目的
备份文件或文件组而不是整个数据库（超大型数据库）
如果某个表需要单独的维护，把其放到一个单独的文件组（被频繁使用的表）
把具有相同维护需求的表和索引放到一个文件组中
如果仅仅为了性能，使用RAID而不是文件组
指定一个用户定义文件组为默认文件组
注意：
文件组不能提供容错
七、容量规划——对于数据库管理员而言，还有一个很重要的任务，就是容量规划。
刚开始多大，2年、3年有多大，数据文件、日志文件的规划以及增长的限制
⏹估量数据库的大小
⏹估量表的数据量
⏹sp_spaceused——查看当前数据库大小，以及未用空间
估量数据库大小，要从两方面入手，估量我的数据文件大小、估量我的日志文件大小。
在估量数据文件大小时，要考虑表的大小，以及索引的大小；因为只有表和索引里面才会真正的存放用户数据，其它对象只是存放一些定义而已。
对于表的估量，主要是估量用户表和系统表，以及每个表中有大概有多少行；
而对于索引的估量，则估量索引有多少行，索引采用的关键字是如何，已经索引的填充因子是怎样的。
对于日志文件的估量，更为复杂些，要估量数据的活动性、数据修改的频率、备份的频率
估量表中的数据量
估计表中行的平均字节数＝x
需要了解各种数据类型所占的字节数
变长字段的平均长度
判断一个数据页有多少航＝800/x=Y取下整
再用表的（总行数/Y）*8KB
实践技巧
⏹定期使用dbcccheckdb来检查数据库的一致性
发现当前的数据页是否有损坏，列入数据库维护计划中。
⏹定期关注数据库空间使用率sp_spaceused
如果有较多空间未使用，则考虑做数据收缩；如果空间不够用，则考虑是否指定额外的数据文件
⏹不要使用自动收缩数据库，手工收缩影响小
虽然sql支持这种操作，但是考虑到对性能的影响、对正常操作的影响，还是建议使用手工收缩
⏹把日志文件和数据文件放在不同的硬盘上
避免对磁盘的抢夺
⏹每个物理硬盘只创建一个文件
⏹尽可能使用RAID技术来提供性能容错
⏹指定最大文件大小，避免磁盘满
如果C盘满的话,则可能造成系统无法启动。
⏹将文件初始大小和增长率适当加大，具体根据实际情况
尽可能避免频繁的发生文件自动增长的请求；若是把数据库空间设得太大，又会浪费
⏹如果有多个文件组，务必将用户定义文件组设置为默认文件组
可以避免把用户数据也放在我们的主文件组里
⏹数据库选项采用默认设置最好
第四章SQLServer2000安全
数据的安全性：
a只能查看数据、不能修改数据；
b修改、删除、添加部分数据；
c只能查看部分数据；
d修改、删除、添加全部数据；
一、SQLServer2000安全架构——两级
1第一级：
必须先登陆到SQLServer。
02_安全架构.jpg
（1）名词：
Login（登陆）：
一个用户（不论是windows2000的用户或组，或者是SQLServer2000的登陆）当它要访问SQLServer中的某个数据时，首先要做的就是登陆的到SQLServer上，通过合法的名字和口令。
（2）有两种登陆方法：
A用windows2000的用户登陆——信任连接（TrustedConnection）
信用连接——当一个用户使用windows2000的用户或组身份登陆到SQLServer时，这种登陆就是信用连接。
若是以用SQLServer自己账户登陆，则称为非信任连接。
b用SQLServer自己的登陆账户登陆——非信任连接
一个用户登陆到了SQLServer后并不意味着它能够对数据进行访问，它必须经过第二个安全级别的验证之后才能进行访问。
2第二级登陆后必须与所登陆数据库中的用户账户和角色有联系user
经过第一级验证后，SQLServer把之前所创建的登陆分配给用户账户和角色；而这些用户账户和角色是在数据库范围内的。
在需要访问的数据库里创建用户账户和角色，并赋予一定的权限，让这些用户账户和角色能够访问数据，执行某些命令。
登陆的用户自动将映射到SQLServer的用户账户和角色，从而执行相应的命令、操作，查看相应的数据。
3身份验证02_身份验证.jpg
A对于windows2000的组和用户，可以通过windows2000本身的安全验证体系自动的映射到SQLServer上。
这时，不论是身份验证，还是口令验证，全都交给windows2000执行。
一旦windows2000认定了这个用户的合法性，SQLServer也就认定了这个用户的合法性。
BSQLServer创建自己的登陆账户；这个账户直接登陆到SQLServer。
而有关的身份验证、口令验证都是由SQLServer本身来完成。
￥￥￥两者的区别仅在于安全验证由谁来执行
C不论是windows2000的组和用户登陆账户，还是SQLServer己的登陆账户，都会在SQLServer的sysxlogins表上存储相应的信息。
4选择身份验证模式
——是SQLServer安全级中的第一步，也是非常重要的一步；只有选择了适合的身份验证模式，才能据此创建对应的login，才能进行此后的安全规划工作。
windows身份验证模式优点：
更好的安全性——因为它充分的利用了win2000的安全性体系
可以将windowsgroup作为一个账户管理——可以有效的同公司内部的组织结构相结合。
在win2000中为管理用户所创建的组，在SQLServer中可以作为单独的账户进行授权，无需再对该组中的用户作额外的管理。
快速访问，无需再次输入登录名和密码
混合身份验证模式优点：
非windows2000用户和Internet客户端可以通过它来联接到SQLServer。
windows身份验证模式——只允许win2000的用户登陆
混合身份验证模式——win2000的用户和SQLServer自己的登陆用户都可以访问
windows身份验证模式安全性更强，但应用场合受到限制（必须全都是windows环境）
混合身份验证模式对环境的包容性更强，适用的场合更多，尤其是通过internet访问的情况
5实现身份验证步骤
（1）选择身份验证模式；
（2）重启MSSQLServerService服务；安装SQLServer时默认是windows身份验证，但在实际应用中修改成混合验证模式时，要重启服务才能生效。
（3）创建win2000组，或用户；
（4）为所创建的组、用户赋予访问数据的权利；
（5）对于非win2000用户，为其创建SQLServer登陆账户，并设置口令、分派权限；（非信任连接）
二、创建登陆账号
1
用于SQLServer身份验证
例：
为用户Albert创建一个SQLServer登录，并指定密码为food
EXECsp_addlogin’Albert’,‘food’
用于windows身份验证
例：
为用户BobJ创建一个登录，BobJ为windows用户
EXECsp_grantlogin’corporate\bobj’
2修改身份验证：
企业管理器——右击服务器（实例）——属性——安全选项——
***要重启MSSQLServerservice服务；意味着服务器上的所有的连接都要断开。
所以，对于一个已经上线、正在运作的SQLServer服务器而言，更改身份验证是十分重要的事。
在规划阶段就要选择好身份验证模式。
3创建登录
（1）默认的登录
企业管理器——实例——安全性——登录
***安装好SQLServer后，SQLServer会创建几个默认的登录，分为两大类：
windows用户和组、标准登录
这三个登录都具备系统管理员的权限。
练习：
分别使用两种身份验证模式登陆默认实例与命名实例。
默认实例设为混合验证登录，命名实例设为windows登录
——查询分析器
登陆默认实例，直接输入server（或者用.表示）；登陆命名实例，直接输入server\实例名（或者用.\表示）
（2）新建登陆
企业管理器——实例——安全性——右击“登录”——新建登录——输入名称
（如果是windows验证登录，则输入windows用户或组的名称；如果是SQLServer登录，……）
a新建标准类型的登录
b新建windows用户类型的登录
前提是先在win2000创建相应的用户，或相应的组；
***新建的登录连接成功后，可以访问到***等数据库，但是却访问不到用户建立的数据库，以及model数据库，如：
hrdb、hrdb1、model。
？
？
？
一个login通过第一个安全架构访问到SQLServer；但是它要访问到某个数据库里的数据，则要依赖于第二级安全架构。
也就是说，要为前面那个login分配数据库里的用户账户或角色，之后这个login才能够访问数据。
在每个系统数据库中（除model
升级会员 