Module 5: 执执行管理任 务务 概述 n配置任务务 n例行SQL Server管理任务务 n自动动化例行管理任务务 n创创建警告 nSQL Server 自动动化排错错 n自动动化多SERVER作业业 u配置任务务 n配置SQL Server代理 n配置SQLAgentMail 和 SQL Mail n配置链链接服务务器 n设设置数据源名字 n配置SQL Server在IIS中对对XML的支持 n配置和其他服务务器应应用共享内存 配置SQL Server代理 nSQL Server Agent Must Be Running at All Times lConfigure SQL Server Agent to auto start lConfigure SQL Server and SQL Server Agent services to restart automatically if these services stop unexpectedly nThe SQL Server Agent Logon Account Must Be Mapped to sysadmin Role lMap this account to the Administrators local group lUse a Windows domain user account logon account nUse Windows Authentication Mode for SQL Server Agent 配置SQLAgentMail 和 SQL Mail SQL Server SQL Mail (SQL Server Service) Executes the xp_sendmail extended stored procedure SQLAgentMail (SQL Server Agent Service) Sends job and alert notifications 配置链链接服务务器 SQL Server OLE DB Provider File system OLE DB Provider SQL Server 设设置数据源名字 nA Data Source Name Defines lODBC driver to use lConnection information (including name and location of data source, login account, and password) lDriver-specific options for the connection 配置SQL Server在IIS中对对XML的支持 ISAPI Filter Internet Information Services Uses ISAPI Filter (SQLXML.DLL) and the OLEDB Provider HTTP Request SQL ServerIIS OLE DBOLE DB XML 配置和其他服务务器应应用共享内存 nConfiguring the Memory Options lmin server memory lmax server memory nDetermining Maximum Amount of Memory nUsing Windows 2000 System Monitor to Observe Effects 例题题 n54. 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- 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. 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. nC. 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. nD. 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 Mail to use the resulting e-mail profile. Lab A: 配置 SQL Server 例行SQL Server管理任务务 nPerforming Regularly Scheduled Tasks lBack up databases lImport and export data nRecognizing and Responding to Potential Problems lMonitor database and log space lMonitor performance u自动动化例行管理任务务 nSQL Server管理的自动动化 n创创建作业业 n验证权验证权 限 n定义义作业业步骤骤 n决定每一作业业步骤骤的逻辑逻辑 性 n计计划作业业 n创创建要通知的操作员员 n复习习和配置作业历业历 史 Multimedia Presentation: SQL Server管理的自动动化 创创建作业业 nEnsure That the Job Is Enabled nSpecify the Job Owner nDetermine Where the Job Will Execute nCreate a Job Category 验证权验证权 限 nExecuting Transact-SQL Jobs lExecute in the context of the job owner or a specific user nExecuting Operating System Commands or ActiveX Script Jobs lMembers of the sysadmin role use the SQL Server Agent login account lJob owners that are not members of the sysadmin role use a defined domain user account called a proxy account 定义义作业业步骤骤 nUsing Transact-SQL Statements nUsing Operating System Commands nUsing ActiveX Scripts nUsing Replication 决定每一作业业步骤骤的逻辑逻辑 性 Job 3 . Job 2 Back Up Northwind Database Transaction Log Job 1 Northwind Data Transfer Write to Windows Application Log Notify Operator No Yes No No Notify Operator Yes Yes Fail?Fail? Job Step 3: Custom Application Type: Active Scripting; Retry attempts: 0 Fail?Fail? Job Step 2: Transfer Data Type: CmdExec; Retry attempts: 2 Fail?Fail? Job Step 1: Back Up Database Type: Transact-SQL; Retry attempts: 1 计计划作业业 Job 2: Back Up Northwind Database Transaction Log Schedule: M-F Shift 1 Every 2 Hours From: 8:00 A.M. To: 5:00 P.M. Sun Mon Tues Wed Thur Fri Sat Schedule: Weekend Every 8 Hours From: 12:00 A.M. To: 11:59 P.M. Sun Mon Tues Wed Thur Fri Sat Schedule: M-F Shift 2 Every 4 Hours From: 5:01 P.M. To: 7:59 A.M. Sun Mon Tues Wed Thur Fri Sat Schedule: CPU Idle CPU Idle Sun Mon Tues Wed Thur Fri Sat 创创建要通知的操作员员 Pager Schedule 12:01 - 8:00 A.M. Meng Phua 8:01 - 6:00 P.M. Nwind Admins 6:01 - 12:00 P.M. Jose Lugo Notify Operator Operator Name Meng Phua Nwind Admins Jose Lugo E-mailNet sendPager Job failed Job: Northwind Data Transfer Job Step 1:Back Up Transaction Log Job Step 3: Back Up Database Job Step 2: Transfer Data 复习习和配置作业历业历 史 nReviewing Individual Job History lJob step resultsuccess or failure lExecution duration lErrors and messages nConfiguring the Job History Size lRetain information about each job lHistory overwritten when maximum size is reached 例题题 13.You implement 10 scheduled jobs on your development