02第三章 管理sql server.docx
- 文档编号:5282490
- 上传时间:2022-12-14
- 格式:DOCX
- 页数:19
- 大小:28.40KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 02第三章 管理sql server 02 第三 管理 sql