《sql管理》PPT课件.ppt

1、Module 5: 执行管理任务,概述,配置任务 例行SQL Server管理任务 自动化例行管理任务 创建警告 SQL Server 自动化排错 自动化多SERVER作业,配置任务,配置SQL Server代理 配置SQLAgentMail 和 SQL Mail 配置链接服务器 设置数据源名字 配置SQL Server在IIS中对XML的支持 配置和其他服务器应用共享内存,配置SQL Server代理,SQL Server Agent Must Be Running at All Times Configure SQL Server Agent to auto start Configure

2、 SQL Server and SQL Server Agent services to restart automatically if these services stop unexpectedly The SQL Server Agent Logon Account Must Be Mapped to sysadmin Role Map this account to the Administrators local group Use a Windows domain user account logon account Use Windows Authentication Mode

3、 for SQL Server Agent,配置SQLAgentMail 和 SQL Mail,配置链接服务器,设置数据源名字,A Data Source Name Defines ODBC driver to use Connection information (including name and location of data source, login account, and password) Driver-specific options for the connection,配置SQL Server在IIS中对XML的支持,配置和其他服务器应用共享内存,Configurin

4、g the Memory Options min server memory max server memory Determining Maximum Amount of Memory Using Windows 2000 System Monitor to Observe 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

5、 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-mail 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

6、. Log on to the server running SQL Server as the SQL Server Agent account, and configure e-mail connectivity. Configure the SQLServerAgent 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

7、the SQL Server account, and configure e-mail connectivity. Configure SQL Mail to use the resulting e-mail profile. D. Configure the MSSQLServer 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 M

8、ail to use the resulting e-mail profile.,Lab A: 配置 SQL Server,例行SQL Server管理任务,Performing Regularly Scheduled Tasks Back up databases Import and export data Recognizing and Responding to Potential Problems Monitor database and log space Monitor performance,自动化例行管理任务,SQL Server管理的自动化 创建作业 验证权限 定义作业步骤

9、 决定每一作业步骤的逻辑性 计划作业 创建要通知的操作员 复习和配置作业历史,Multimedia Presentation: SQL Server管理的自动化,创建作业,Ensure That the Job Is Enabled Specify the Job 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

10、Operating System Commands or ActiveX Script Jobs Members of the sysadmin role use the SQL Server Agent login account Job owners that 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 Usin

11、g ActiveX Scripts Using Replication,决定每一作业步骤的逻辑性,计划作业,Job 2: Back Up Northwind Database Transaction Log,创建要通知的操作员,复习和配置作业历史,Reviewing Individual Job History Job step resultsuccess or failure Execution duration Errors and messages Configuring the Job History Size Retain information about each job His

12、tory overwritten when maximum size is reached,例题,13.You implement 10 scheduled jobs on your development server, and you verify that they 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 t

13、he jobs, and execute the resulting script on the production server. B. Back up the msdb database and restore it onto the production server. 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 t

14、he jobs on the production server.,Lab B: 创建作业和操作员,创建警告,利用警报来响应潜在的故障 将事件写到应用程序日志中 创建响应SQL Server错误的警报 创建用户自定义错误的警报 响应性能条件的警报 制定防故障操作员,利用警报来响应潜在的故障,将事件写到应用程序日志中,SQL Server Errors Severity Levels Between 19 and 25 sp_addmessage or sp_altermessage System Stored Procedures RAISERROR WITH LOG Statement xp

15、_logevent Extended Stored Procedure,创建响应SQL Server错误的警报,Defining Alerts on SQL Server Error Numbers Must be written to the Windows application log System-supplied or user-defined Defining Alerts on Error Severity Levels Severity levels 19 through 25 are automatically logged Configure event forwardin

16、g,创建用户自定义错误的警报,Create the Error Message Error number must be greater than 50000 Parameter placeholders can be used Raise the Error from Database Application Use the RAISERROR statement Declare variables for parameter placeholders Define an Alert on the Error Message,响应性能条件的警报,制定防故障操作员,Fail-Safe Oper

17、ator,Alert: Error 18204,Backup Device Failed,SQL Server 自动化排错,验证是否已经启动SQL Server代理 验证是否已经启用作业，调度，警报或操作员 确保已经启用代理帐户 查看错误日志 查看历史记录 验证邮件客户程序是否运行正常,警报排错,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: 创建警报,多服务器作业自动化,定义主服务器 Create