《29sql管理4复习课程》由会员分享,可在线阅读,更多相关《29sql管理4复习课程(38页珍藏版)》请在金锄头文库上搜索。
1、Module 5: 执行管理任务,概述,配置任务 例行SQL Server管理任务 自动化例行管理任务 创建警告 SQL Server 自动化排错 自动化多SERVER作业,配置任务,配置SQL Server代理 配置SQLAgentMail 和 SQL Mail 配置链接服务器 设置数据源名字 配置SQL Server在IIS中对XML的支持 配置和其他服务器应用共享内存,配置SQLAgentMail 和 SQL Mail,配置链接服务器,设置数据源名字,A Data Source Name Defines ODBC driver to use Connection information
2、(including name and location of data source, login account, and password) Driver-specific options for the connection,配置SQL Server在IIS中对XML的支持,配置和其他服务器应用共享内存,Configuring the Memory Options min server memory max server memory Determining Maximum Amount of Memory Using Windows 2000 System Monitor to Ob
3、serve Effects,例题,54. You want to configure SQL Server to notify you by e-mail when an alert occurs. Which series of steps must you perform? A. Configure the SQL Server Agent service to log on as the local system account. Log on to the server running SQL Server as your user account, and configure e-m
4、ail connectivity. Configure the SQL ServerAgent service to use the resulting e-mail profile. B. Configure the SQLServerAgent service to log on as a domain user account. Log on to the server running SQL Server as the SQL Server Agent account, and configure e-mail connectivity. Configure the SQLServer
5、Agent service to use the resulting e-mail profile.,C. Configure the MSSQLServer service to log on as a domain user account. Log on to the server running SQL Server as the SQL Server account, and configure e-mail connectivity. Configure SQL Mail to use the resulting e-mail profile. D. Configure the M
6、SSQLServer service to log on as a domain user account. Log on to the server running SQL Server as your user account. and configure e-mail connectivity. Configure SQL Mail to use the resulting e-mail profile.,Lab A: 配置 SQL Server,例行SQL Server管理任务,Performing Regularly Scheduled Tasks Back up databases
7、 Import and export data Recognizing and Responding to Potential Problems Monitor database and log space Monitor performance,自动化例行管理任务,SQL Server管理的自动化 创建作业 验证权限 定义作业步骤 决定每一作业步骤的逻辑性 计划作业 创建要通知的操作员 复习和配置作业历史,Multimedia Presentation: SQL Server管理的自动化,创建作业,Ensure That the Job Is Enabled Specify the Job
8、Owner Determine Where the Job Will Execute Create a Job Category,验证权限,Executing Transact-SQL Jobs Execute in the context of the job owner or a specific user Executing Operating System Commands or ActiveX Script Jobs Members of the sysadmin role use the SQL Server Agent login account Job owners that
9、are not members of the sysadmin role use a defined domain user account called a proxy account,定义作业步骤,Using Transact-SQL Statements Using Operating System Commands Using ActiveX Scripts Using Replication,决定每一作业步骤的逻辑性,计划作业,Job 2: Back Up Northwind Database Transaction Log,创建要通知的操作员,复习和配置作业历史,Reviewing
10、 Individual Job History Job step resultsuccess or failure Execution duration Errors and messages Configuring the Job History Size Retain information about each job History overwritten when maximum size is reached,例题,13.You implement 10 scheduled jobs on your development server, and you verify that t
11、hey function correctly. You now want to transfer the jobs to your production server. How should you transfer the jobs with the fewest administrative steps? A. Script the jobs, and execute the resulting script on the production server. B. Back up the msdb database and restore it onto the production s
12、erver. C. Make the test server a master server, and make the production server a target server. Configure the jobs to run on the target server. D. Manually re-create the jobs on the production server.,Lab B: 创建作业和操作员,创建警告,利用警报来响应潜在的故障 将事件写到应用程序日志中 创建响应SQL Server错误的警报 创建用户自定义错误的警报 响应性能条件的警报 制定防故障操作员,
13、利用警报来响应潜在的故障,将事件写到应用程序日志中,SQL Server Errors Severity Levels Between 19 and 25 sp_addmessage or sp_altermessage System Stored Procedures RAISERROR WITH LOG Statement xp_logevent Extended Stored Procedure,创建响应SQL Server错误的警报,Defining Alerts on SQL Server Error Numbers Must be written to the Windows ap
14、plication log System-supplied or user-defined Defining Alerts on Error Severity Levels Severity levels 19 through 25 are automatically logged Configure event forwarding,创建用户自定义错误的警报,Create the Error Message Error number must be greater than 50000 Parameter placeholders can be used Raise the Error fr
15、om Database Application Use the RAISERROR statement Declare variables for parameter placeholders Define an Alert on the Error Message,响应性能条件的警报,制定防故障操作员,Fail-Safe Operator,Alert: Error 18204,Backup Device Failed,SQL Server 自动化排错,验证是否已经启动SQL Server代理 验证是否已经启用作业,调度,警报或操作员 确保已经启用代理帐户 查看错误日志 查看历史记录 验证邮件
16、客户程序是否运行正常,警报排错,Factors That Cause an Alert Processing Backlog Windows application log is full CPU use is unusually high Number of alert responses is high Resolving Alert Processing Backlog Disable the alert temporarily Increase delay between responses Correct global resource problem Clear the Windows application log,Lab C: 创建警报,多服务器作业自动化,定义主服务器 Creates MSXOperator on master server and all target servers Represents a primary department or business unit 定义目标服务器 Are assigned t