《sql管理》ppt课件

1、Module 5: 执行管理义务执行管理义务 概述概述n配置义务配置义务n例行例行SQL Server管理义务管理义务 n自动化例行管理义务自动化例行管理义务 n创建警告创建警告 nSQL Server 自动化排错自动化排错n自动化多自动化多SERVER作业作业u配置义务配置义务n配置配置SQL Server代理代理n配置配置SQLAgentMail 和和 SQL Mailn配置链接效力器配置链接效力器n设置数据源名字设置数据源名字n配置配置SQL Server在在IIS中对中对XML的支持的支持n配置和其他效力器运用共享内存配置和其他效力器运用共享内存配置配置SQL Server代理代理nS

2、QL Server Agent Must Be Running at All TimesnConfigure SQL Server Agent to auto startnConfigure SQL Server and SQL Server Agent services to restart automatically if these services stop unexpectedlynThe SQL Server Agent Logon Account Must Be Mapped to sysadmin Role nMap this account to the Administra

3、tors local groupnUse a Windows domain user account logon accountnUse Windows Authentication Mode for SQL Server Agent 配置配置SQLAgentMail 和和 SQL MailSQLServerSQL Mail(SQL Server Service)Executes the xp_sendmailextended stored procedure SQLAgentMail(SQL Server Agent Service)Sends job and alertnotificati

4、ons配置链接效力器配置链接效力器OLE DB ProviderOLE DB Provider设置数据源名字设置数据源名字nA Data Source Name DefinesnODBC driver to usenConnection information (including name and location of data source, login account, and password)nDriver-specific options for the connection配置配置SQL Server在在IIS中对中对XML的支持的支持ISAPI FilterInternet

5、Information ServicesUses ISAPI Filter (SQLXML.DLL) and the OLEDB Provider RequestXML配置和其他效力器运用共享内存配置和其他效力器运用共享内存nConfiguring the Memory Optionsnmin server memorynmax server memorynDetermining Maximum Amount of Memory nUsing Windows 2000 System Monitor to Observe Effects例题例题n54. You want to configure

6、 SQL Server to notify you by when an alert occurs. Which series of steps must you perform?nA. 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 connectivity. Configure the SQL ServerAgent service

7、to use the resulting profile.nB. 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 connectivity.n Configure the SQLServerAgent service to use the resulting profile.nC. Configure the MSSQLSer

8、ver service to log on as a domain user account.n Log on to the server running SQL Server as the SQL Server account, and configure connectivity.n Configure SQL Mail to use the resulting profile.nD. Configure the MSSQLServer service to log on as a domain user account.n Log on to the server running SQL

9、 Server as your user account. and configure connectivity.n Configure SQL Mail to use the resulting profile.Lab A: 配置配置 SQL Server例行例行SQL Server管理义务管理义务 nPerforming Regularly Scheduled TasksnBack up databasesnImport and export datanRecognizing and Responding to Potential ProblemsnMonitor database and

10、 log spacenMonitor performanceu自动化例行管理义务自动化例行管理义务 nSQL Server管理的自动化管理的自动化n创建作业创建作业n验证权限验证权限n定义作业步骤定义作业步骤n决议每一作业步骤的逻辑性决议每一作业步骤的逻辑性n方案作业方案作业n创建要通知的操作员创建要通知的操作员n复习和配置作业历史复习和配置作业历史Multimedia Presentation: SQL Server管理的自动化管理的自动化创建作业创建作业nEnsure That the Job Is EnablednSpecify the Job OwnernDetermine Where

11、 the Job Will ExecutenCreate a Job Category验证权限验证权限nExecuting Transact-SQL JobsnExecute in the context of the job owner or a specific usernExecuting Operating System Commands or ActiveX Script JobsnMembers of the sysadmin role use the SQL Server Agent login accountnJob owners that are not members of

12、 the sysadmin role use a defined domain user account called a proxy account定义作业步骤定义作业步骤nUsing Transact-SQL StatementsnUsing Operating System CommandsnUsing ActiveX ScriptsnUsing Replication决议每一作业步骤的逻辑性决议每一作业步骤的逻辑性Job 3 .Job 2 Back Up Northwind Database Transaction LogWrite to WindowsApplication LogN

13、otify OperatorNoYesNoNoNotify OperatorYesYesJob Step 3: Custom ApplicationType: Active Scripting; Retry attempts: 0Job Step 2: Transfer DataType: CmdExec; Retry attempts: 2Job Step 1: Back Up DatabaseType: Transact-SQL; Retry attempts: 1方案作业方案作业Schedule: M-F Shift 1Every 2 HoursFrom: 8:00 A.M.To: 5:

14、00 P.M.Sun Mon Tues Wed Thur Fri Sat Schedule: WeekendEvery 8 HoursFrom: 12:00 A.M.To: 11:59 P.M.Sun Mon Tues Wed Thur Fri Sat Schedule: M-F Shift 2Every 4 HoursFrom: 5:01 P.M.To: 7:59 A.M.Sun Mon Tues Wed Thur Fri Sat Schedule: CPU IdleCPU IdleSun Mon Tues Wed Thur Fri Sat 创建要通知的操作员创建要通知的操作员Notify

15、OperatorOperator NameMeng PhuaNwind AdminsJose LugoNet sendPagerJob Step 2: Transfer Data复习和配置作业历史复习和配置作业历史nReviewing Individual Job HistorynJob step resultsuccess or failurenExecution durationnErrors and messagesnConfiguring the Job History Size nRetain information about each jobnHistory overwritte

16、n 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 the jobs, and exe

17、cute 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 the jobs on the prod

18、uction server.Lab B: 创建作业和操作员创建作业和操作员u创建警告创建警告 n利用警报来呼应潜在的缺点利用警报来呼应潜在的缺点n将事件写到运用程序日志中将事件写到运用程序日志中n创建呼应创建呼应SQL Server错误的警报错误的警报n创建用户自定义错误的警报创建用户自定义错误的警报n呼应性能条件的警报呼应性能条件的警报n制定防缺点操作员制定防缺点操作员利用警报来呼应潜在的缺点利用警报来呼应潜在的缺点User Databasemsdb Database732van Dam.将事件写到运用程序日志中将事件写到运用程序日志中nSQL Server Errors Severity

19、Levels Between 19 and 25nsp_addmessage or sp_altermessage System Stored ProceduresnRAISERROR WITH LOG Statementnxp_logevent Extended Stored Procedure创建呼应创建呼应SQL Server错误的警报错误的警报nDefining Alerts on SQL Server Error NumbersnMust be written to the Windows application lognSystem-supplied or user-defined

20、nDefining Alerts on Error Severity LevelsnSeverity levels 19 through 25 are automatically loggednConfigure event forwarding创建用户自定义错误的警报创建用户自定义错误的警报Create the Error MessageError number must be greater than 50000Parameter placeholders can be usedRaise the Error from Database ApplicationUse the RAISERR

21、OR statementDeclare variables for parameter placeholdersDefine an Alert on the Error Message呼应性能条件的警报呼应性能条件的警报Alert 3All Databases: Severity Level 18Alert 2Northwind Database: Transfer Data ErrorExecute Job: Operators to Notify:Operator NameMeng PhuaNwind AdminsJose LugoNet sendJob 2: Back Up Northw

22、ind Transaction LogPager12:01 - 8:00 A.M.Meng PhuaThreshold Reachedat 1:28 A.M.制定防缺点操作员制定防缺点操作员Operator NotificationOperatorsMeng PhuaNwind AdminsJose LugoNet sendPagerBackup Device FaileduSQL Server 自动化排错自动化排错n验证能否曾经启动验证能否曾经启动SQL Server代理代理n验证能否曾经启用作业，调度，警报或操作员验证能否曾经启用作业，调度，警报或操作员n确保曾经启用代理帐户确保曾经启用代

23、理帐户n查看错误日志查看错误日志n查看历史记录查看历史记录n验证邮件客户程序能否运转正常验证邮件客户程序能否运转正常警报排错警报排错nFactors That Cause an Alert Processing BacklognWindows application log is fullnCPU use is unusually highnNumber of alert responses is highnResolving Alert Processing BacklognDisable the alert temporarilynIncrease delay between responsesnCorrect g