SQL Server 2008 R2培训讲义

select GETDATE()
select @@VERSION
go
create database easthome
go
use easthome
go 
use test
go
create table h3(id int) on hr
go
--查看数据库test的相关信息
sp_helpdb test
go 
sp_help h3
go
select * from master.sys.syslogins
go
create table easthome.dbo.t2(id int)
go

--查看系统用户
select * from easthome.sys.sysusers
go

--生成完全备份数据信息和日志信息
backup database easthome to disk='d:\bk\et.bak'
go
backup log easthome to disk='d:\bk\et_log_1.bak'
go
backup log easthome to disk='d:\bk\et_log_2.bak'
go

--验证备份信息
--使用该语句可以查看备份文件的版本信息、是否进行过压缩
restore headeronly from disk='d:\bk\et.bak'
go

--数据文件和日志文件的信息查看SIZE大小
restore filelistonly from disk='d:\bk\et.bak'
go

--完全备份（压缩）
--Checksum:一边做备份一边验证数据库当中是否有损害的数据页，
--有损坏页就不再进行备份
backup database easthome to disk='d:\bk\east.bak' 
with checksum,compression
go

--察看数据库是否有损坏的数据页，标示为红色（indid）
--indid值为0，1数据页坏了   解决办法：恢复数据
--indid值为〉=2 代表索引坏了  解决办法：重建索引即可
--建议按月查看，但是不能在业务高峰时间查看，会给数据添加锁
dbcc checkdb(easthome)
go

--执行有个友好提示，提示用户备份当前状态的信息
--用户可以先备份一个日志信息，然后再做还
--如果不加recovery选项，默认是recovery(不保留那半个事务）
backup log easthome to disk='d:\bk\et_log_4.bak'
go
restore database easthome from disk='d:\bk\et.bak'
go

--执行log恢复保证你的数据恢复是norecovery
--如果使用norecovery，数据库刷新状态为restoring（不可读也不可写）
--这样数据和日志才能衔接上
restore database easthome from disk='d:\bk\et.bak' with norecovery
go
restore log easthome from disk='d:\bk\et_log_1.bak' 
go

--一步步地还原必须添加with norecovery选项，除非你不想要某个
--备份信息，在还原之前使用with recovery选项
restore log easthome from disk='d:\bk\et_log_1.bak' with norecovery 
go

/*
如果数据库坏掉，应该首先对数据库的日志文件进行备份，然后再进行数据
恢复，这样才能将数据恢复到当前点。

如果数据文件坏掉，但是日志文件没有问题，可以将日志文件放到另外机器
做恢复，将数据恢复。
SQLServer 不能直接读当前日志文件恢复数据，只能读取备份的日志文件恢
复数据，所以要放到其他机器折腾一下
*/


--备份msdb库
backup database msdb to disk='d:\bk\msdb.bak'
go

--启动sqlserver agent服务,处理自动化作业
--需要停止服务，在服务列表中停止，软件不能停止
--只有停止代理服务（sqlserver agent）后才能恢复msdb
restore database msdb from disk='d:\bk\msdb.bak'
go

--备份master
backup database master to disk='d:\bk\master.bak'
go

/*
只能在单用户模式下才能还原master
怎么启动单用户模式方法
1:首先停止SQLServer服务
2:在启动参数添加-m
3:不能点击确定，需要点击启动（注意）
*/

/*
使用抢连接方式
启动单用户模式后，我们重新登陆SQLServer提示错误
原因是连接被其他（reporting service)抢到了
打开CMD输入sqlcmd -E（-E 以操作系统用户连接）
上面的3点击启动，同时赶紧在DOS界面回车（必须快才能抢到连接，多次尝试）
进行单用户模式后才能进行master库的恢复
*/

/*
---------------------------------------------------------------------
**********恢复master数据库总结步骤***********************************
---------------------------------------------------------------------
1：重建系统数据库
   不同数据库版本的命令是不一样的
2：停止SQLServer服务
3：使用-m启动单用户模式
4：恢复master数据库
5：手动正常启动SQL Server服务
6：恢复MSDB数据库（尽管你的MSDB数据库是空的也必须作备份恢复）
   保持Master和MSDB配套，不然以后实行自动化作业是个隐患
7：手动正常启动SQL Server代理服务（Agent）
*/

restore database master from disk='d:\bk\master.bak'
go


/*
---------------------------------------------------------------------
**************维护计划***********************************************
---------------------------------------------------------------------
	建议用户分门别类的进行维护计划
	1：用户数据库备份
	2：系统数据库备份
	3：日志文件的备份
	4：删除以前的备份文件(清除MSDB记录的备份信息）建议删除6个月以前的
	--清除历史任务
	--清除维护任务

	使用之前需要启动SQL Server Agent代理服务
	如果报错：有CLSID等，把SQLServer Management Studio关闭重新打开即可
	执行多次，就产生多个文件
*/

/*
---------------------------------------------------------------------
**************自动化作业*********************************************
---------------------------------------------------------------------
	可以将自动化作业的运行结果、查询结果等以发邮件的形式发送
	发邮件需要注意;SMTP   注意防火墙和杀毒软件
	配置左边树状Database Mail时需要停止 SQL Server Agent代理服务


	配置时会提示启动ServiceBroker，点击Yes，系统会假死，必须关闭
	SQL Server Management Studio 窗口，而不能关闭配置邮件的界面
	如果有问题，多试几次或者重启SQL Server服务
	配置完毕之后使用Database Mail需要启动SQL Server Agent代理服务

	这个是配置邮件的发件人
	在SQL Server Agent的Operators配置收件人

*/

/*
---------------------------------------------------------------------
***************创建一个Job*******************************************
---------------------------------------------------------------------
	a:设置一个Job的名称
	b:选择Steps
		1:backup--备份数据库
		2:copy--拷贝备份好的文件到另外地方
		将自己的SQLSErver文件拷贝到其他机器使用代理服务的启动帐户
		将别的文件拷贝到自己SQlServer使用SQLServer的启动帐户
		Advanced可以确定流程控制
	c:设置时间
	d:发送信息给收件人
		该步骤必须选择Agent右键属性-Alert System-Enable Mail profile
		作业失败，View History，哪个有红色就是哪个失败了
*/

/*
---------------------------------------------------------------------
******************Replication****************************************
---------------------------------------------------------------------
1：数据同步
2：负载均衡
3：HA

该功能是基于表的，不包括Database外面的视图、存储过程、用户等对象
该功能包含
1：快照复制
	复制表里面当前的状态
	一次性的
2：事务复制
	增量的，原有变化目标会跟着变化，
	单向的（源变目标变，目标变对源无影响）
	表里面必须有主键或者唯一值约束
3：合并复制
	增量的 同上
	多向的（一个节点改，所有参与合并复制的都需要改）
	会自动给表添加一列-------客户端程序需要注意
*/

/*
---------------------------------------------------------------------
****************High Availablility***********************************
---------------------------------------------------------------------
1：log shipping 日志传授
	原理：主服务器不停的往一个目录（需要共享）作备份，然后将这个目录
	文件拷贝到另外一个目录，另外一个目录为备用服务器作恢复（norecovery) 
	
	三个周期 两个目录 一个共享
	替代日志文件备份策略，不能同时执行
	
	特点：数据非实时、对主机无影响
	适用于报表服务器
	
2：Mirror（SQL Server2005后）
	特点：实时/非实时的灾备中心、对主机性能有影响
	想读取mirror的数据，需要创建mirror快照
	
	企业面临问题; 需要修改客户端连接字符串，只支持ADO
	如果主服务器宕机了，mirror服务器需要自动起来，需要借助于
	Witness Server（见证服务器），mirror如果不自动起来，手动
	起来，就不需要witness server
	
	保护机器、存储
	
3：Cluster
	保护机器，不保护存储
	Oracle RAC模式在SQL Server 2008 R2 的并行计算版本/云计算版本
	能够实现，企业版只能做（双机热备），不能做类似RAC的模式
*/

/*
---------------------------------------------------------------------
***************监控SQL Server****************************************
---------------------------------------------------------------------

1：管理工具-事件查看器

	Q：以前可以用，现在用不了了
	A：我们需要知道用户作了什么变更
	变更包括（硬件变化、软件变化（打补丁）、升级变化、客户端程序变化等）
	
	经验：
	a:服务器做变更（打补丁）之前一定要做测试
	b:服务器作任何变更，一定要进行记录
	
2：SQL Server-Management-SQL Server Logs
	记录SQL Server运行综合的状况，该日志与时间查看器有重叠
	
	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log	
	查看该文件夹里面的log..文件，双击查看，可以对表结构的增删改、数据库备份等作查看
	
3：管理工具-性能监视器
	锁的问题，一般都是客户端代码的问题
	
	
	选择性能监视器-右边(报告)-添加计数器-LogicalDisk(下面的disk read time等信息不用看，不正确）
	需要查看Avg.Disk Read Queue Length和Avg.Disk Write Queue Length，选择所有实例，点击添加
	相关的值不超过10都算正常，
	
	读队列最容易出问题
	a:消除页碎片
	b:索引
	c:调整SQL语句   
	  不能修改代码的在后台建立索引视图、分区表、计算列
	  
	添加-Memory-Available MBytes
	估值在100MB以上
	
	添加-Memory-Pages/sec(物理内存和虚拟内存的交换）
	选择用户连接-属性-Memory-Maximum Server memory(设置）-避免SQLServer抢操作系统内存
		--Use AWE to allocate memory
		给32Bit的SQL Server的企业版来使用
	
	添加SQLServer：Memory Manger-
	Target Server Memory：Windows给了SQLServer多少内存
	Total Server Memory：SQLServer使用了多少内存
		32Bit的SQL Server软件安装在64Bit的操作系统是不能看到SQL Server开头的添加计数器
	
	SQLServer:SQL Statistics-SQL Compilations/sec
*/

/*
---------------------------------------------------------------------
***************************锁****************************************
---------------------------------------------------------------------
1：阻塞：顺序等待
2：死锁：交叉等待

测试阻塞
*/
use easthome 
go
create table w5(id int)
go
insert into w5 values(1)
insert into w5 values(2)
go
begin tran
	update w5 set id=10 where id=1
	---排他锁需要等事务结束才释放(更新时添加）
	---共享锁需要等程序结束才释放（查询时添加）
go
--使用sp_lock可以监控加锁的情况 X（排他锁） S（共享锁）
sp_lock
--查看DBID（库名）和ObjID（表名）
--TAB（表）PAG（页）RID（行）
--1：79：0（第一个文件上面的第79页，“：0” 是这个页的第一行）
go
select DB_NAME(7)
select OBJECT_NAME(2121058592)
go
--执行下面语句，检测，因为上面的SQL语句事务未提交，所以执行下面语句无相应
--执行Sp_lock有Wait状态，这就模拟了一个阻塞的现象
select * from w5 where id=1
go
--数据连接-右键-Activity Monitor-Processes-HEB1（阻塞的人）

--使用sp_who查看blk是非0值，说明有阻塞现象
sp_who

--阻塞现象修改1：程序  2：索引
--如果表内有并发修改，小表也需要创建索引，不是为了查询性能，提高并发性  
--使用DMV来查询相关信息

/*
---------------------------------------------------------------------
***************************SQL Server Profiler***********************
---------------------------------------------------------------------
	打开SQL Server Profiler-New Trace，Trace属性-Events Selection
	一般选择
	RPC:Completed
	SQL:BatchCompleted
	Show All Event-Locks-Deadlock graph
	Show All columns-添加DatabaseName和HostName
*/

/*
---------------------------------------------------------------------
********************Troubleshooting and Performance Tuning***********
---------------------------------------------------------------------
	Database Engine Tuning Advisor
		File添加[SQL Server Profiler跟踪的结果文件]
		Start Analysis-Apply Recommendations仅供参考即可
	Resource Governor(SQL Server 2008之后）
		限制某个用户、某个数据库最多可以使用CPU、内存等资源
		编程实现
	Data Collector
*/