sql管理4.ppt

Module 5:执行管理任务执行管理任务 概述概述n配置任务配置任务n例行例行SQL ServerSQL Server管理任务管理任务 n自动化例行管理任务自动化例行管理任务 n创建警告创建警告 nSQL Server SQL Server 自动化排错自动化排错n自动化多自动化多SERVERSERVER作业作业u配置任务配置任务n配置配置SQL ServerSQL Server代理代理n配置配置SQLAgentMailSQLAgentMail 和和 SQL MailSQL Mailn配置链接服务器配置链接服务器n设置数据源名字设置数据源名字n配置配置SQL ServerSQL Server在在IISIIS中对中对XMLXML的支持的支持n配置和其他服务器应用共享内存配置和其他服务器应用共享内存配置配置SQL ServerSQL Server代理代理nSQL Server Agent Must Be Running SQL Server Agent Must Be Running a at All Timest All TimeslConfigure SQL Server Agent to auto startlConfigure SQL Server and SQL Server Agent services to restart automatically if these services stop unexpectedlynThe SQL Server Agent Logon Account Must Be Mapped to The SQL Server Agent Logon Account Must Be Mapped to sysadmin Role sysadmin Role lMap this account to the AdministratorsAdministrators local grouplUse a Windows domain user account logon accountnUse Windows Authentication Mode for SQL Server Agent Use Windows Authentication Mode for SQL Server Agent 配置配置SQLAgentMailSQLAgentMail 和和 SQL MailSQL MailSQLSQLServerServerSQL Mail(SQL Server Service)Executes the xp_sendmailExecutes the xp_sendmailextended stored procedure extended stored procedure SQLAgentMail(SQL Server Agent Service)S Sends job and alertends job and alertnotificationsnotifications配置链接服务器配置链接服务器SQLSQLServerServerOLE DB ProviderFileFilesystemsystemOLE DB ProviderSQLSQLServerServer配置和其他服务器应用共享内存配置和其他服务器应用共享内存nConfiguring the Memory OptionsConfiguring the Memory Optionslmin server memorymin server memorylmax server memorymax server memorynDetermining Maximum Amount of Memory Determining Maximum Amount of Memory nUsing Windows 2000 System Monitor to Observe EffectsUsing Windows 2000 System Monitor to Observe Effects例题例题n54.54.You want to configure SQL Server to notify you by e-mail when an You want to configure SQL Server to notify you by e-mail when an alert occurs.Which series of steps must you perform?alert occurs.Which series of steps must you perform?A.Configure the SQL Server Agent service to log on as the local system 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,account.Log on to the server running SQL Server as your user account,and configure e-mail connectivity.Configure the SQL ServerAgent and configure e-mail connectivity.Configure the SQL ServerAgent service to use the resulting e-mail profile.service to use the resulting e-mail profile.B.Configure the SQLServerAgent B.Configure the SQLServerAgent service to log on as a domain user to log on as a domain user account.Log on to the server running SQL Server as the SQL Server account.Log on to the server running SQL Server as the SQL Server Agent account,and configure e-mail connectivity.Agent account,and configure e-mail connectivity.Configure the SQLServerAgent service to use the resulting e-mail Configure the SQLServerAgent service to use the resulting e-mail profile.profile.nC.Configure the MSSQLServer service to log on as a domain user C.Configure the MSSQLServer service to log on as a domain user account.account.Log on to the server running SQL Server as the SQL Server account,Log on to the server running SQL Server as the SQL Server account,and configure e-mail connectivity.and configure e-mail connectivity.Configure SQL Mail to use the resulting e-mail profile.Configure SQL Mail to use the resulting e-mail profile.nD.Configure the MSSQLServer service to log on as a domain user D.Configure the MSSQLServer service to log on as a domain user account.account.Log on to the server running SQL Server as your user account.and Log on to the server running SQL Server as your user account.and configure e-mail connectivity.configure e-mail connectivity.Configure SQL Mail to use the resulting e-mail profile.Configure SQL Mail to use the resulting e-mail profile.Lab A:Lab A:配置配置 SQL ServerSQL Server例行例行SQL ServerSQL Server管理任务管理任务 nPerforming Regularly Scheduled TasksPerforming Regularly Scheduled TaskslBack up databaseslImport and export datanRecognizing and Responding to Potential ProblemsRecognizing and Responding to Potential ProblemslMonitor database and log spacelMonitor performanceu自动化例行管理任务自动化例行管理任务 nSQL ServerSQL Server管理的自动化管理的自动化n创建作业创建作业n验证权限验证权限n定义作业步骤定义作业步骤n决定每一作业步骤的逻辑性决定每一作业步骤的逻辑性n计划作业计划作业n创建要通知的操作员创建要通知的操作员n复习和配置作业历史复习和配置作业历史Multimedia Presentation:SQL ServerMultimedia Presentation:SQL Server管理的自动化管理的自动化创建作业创建作业nEnsure That the Job Is EnabledEnsure That the Job Is EnablednSpecify the Job OwnerSpecify the Job OwnernDetermine Where the Job Will ExecuteDetermine Where the Job Will ExecutenCreate a Job CategoryCreate a Job Category验证权限验证权限nExecuting Transact-SQL JobsExecuting Transact-SQL JobslExecute in the context of the job owner or a specific usernExecuting Operating System Commands or ActiveExecuting Operating System Commands or ActiveX X Script Script JobsJobslMembers of the sysadminsysadmin role use the SQL Server Agent login accountlJob owners that are not members of the sysadminsysadmin role use a defined domain user account called a proxy account定义作业步骤定义作业步骤nUsing Transact-SQL StatementsUsing Transact-SQL StatementsnUsing Operating System CommandsUsing Operating System CommandsnUsing ActiveUsing ActiveX X Script Scripts snUsing ReplicationUsing Replication决定每一作业步骤的逻辑性决定每一作业步骤的逻辑性Job 3.Job 2 Back Up Northwind Database Transaction LogJob 1Job 1Northwind Data TransferNorthwind Data TransferWrite to WindowsWrite to WindowsApplication LogApplication LogNotify OperatorNotify Operator NoNoYesYesNoNoNoNoNotify OperatorYesYesYesYesFail?Fail?Fail?Fail?Job Step 3:Custom ApplicationTypeType:Active Scripting;Retry attemptsRetry attempts:0Fail?Fail?Fail?Fail?Job Step 2:Transfer DataTypeType:CmdExec;Retry attemptsRetry attempts:2Fail?Fail?Fail?Fail?Job Step 1:Back Up DatabaseTypeType:Transact-SQL;Retry attemptsRetry attempts:1计划作业计划作业Job 2:Back Up Northwind Database Transaction LogJob 2:Back Up Northwind Database Transaction LogSchedule:M-F Shift 1Schedule:M-F Shift 1Every 2 HoursEvery 2 HoursFrom:8:00 A.M.To:5:00 P.M.Sun Mon Tues Wed Thur Fri Sat Schedule:WeekendSchedule:WeekendEvery 8 HoursEvery 8 HoursFrom:12:00 A.M.To:11:59 P.M.Sun Mon Tues Wed Thur Fri Sat Schedule:M-F Shift 2Schedule:M-F Shift 2Every 4 HoursEvery 4 HoursFrom:5:01 P.M.To:7:59 A.M.Sun Mon Tues Wed Thur Fri Sat Schedule:CPU IdleSchedule:CPU IdleCPU IdleCPU IdleSun Mon Tues Wed Thur Fri Sat 创建要通知的操作员创建要通知的操作员Pager SchedulePager Schedule12:01-8:00 A.M.12:01-8:00 A.M.Meng PhuaMeng Phua 8:01-6:00 P.M.8:01-6:00 P.M.Nwind AdminsNwind Admins 6:01-12:00 P.M.6:01-12:00 P.M.Jose LugoJose LugoNotify OperatorOperator NameOperator NameMeng PhuaNwind AdminsJose LugoE-mailE-mailNet sendNet sendPagerPagerJob failedJob failedJob failedJob failedJob:Northwind Data Transfer Job:Northwind Data Transfer Job Step 1:Back Up Transaction LogJob Step 1:Back Up Transaction LogJob Step 3:Back Up DatabaseJob Step 3:Back Up DatabaseJob Step 2:Transfer Data复习和配置作业历史复习和配置作业历史nReviewing Individual Job HistoryReviewing Individual Job HistorylJob step resultsuccess or failurelExecution durationlErrors and messagesnConfiguring the Job History Size Configuring the Job History Size lRetain information about each joblHistory overwritten when maximum size is reached例题例题13.13.You implement 10 scheduled jobs on your development server,and you You implement 10 scheduled jobs on your development server,and you verify that they function correctly.You now want to transfer the jobs to verify that they function correctly.You now want to transfer the jobs to your production server.your production server.How should you transfer the jobs with the fewest administrative steps?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 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 production server.Lab B:Lab B:创建作业和操作员创建作业和操作员u创建警告创建警告 n利用警报来响应潜在的故障利用警报来响应潜在的故障n将事件写到应用程序日志中将事件写到应用程序日志中n创建响应创建响应SQL ServerSQL Server错误的警报错误的警报n创建用户自定义错误的警报创建用户自定义错误的警报n响应性能条件的警报响应性能条件的警报n制定防故障操作员制定防故障操作员利用警报来响应潜在的故障利用警报来响应潜在的故障User DatabaseUser DatabaseE-mail MessageE-mail MessageFrom:From:SQL ServerSQL ServerTo:To:Account ManagerAccount ManagerSubject:Subject:Error Number 50099Error Number 50099Customer 732 was deleted by Eva CoretsCustomer 732 was deleted by Eva Coretsmsdb Databasemsdb DatabaseC Customers Tableustomers TableC CC CustomerIDustomerIDustomerIDustomerIDL LL LastNameastNameastNameastName.731731731731HaruiHaruiHaruiHarui.732732732732van Damvan Damvan Damvan Dam.733733733733NiikkonenNiikkonenNiikkonenNiikkonen.732732van Damvan Dam.Raise ErrorRaise ErrorRaise ErrorRaise Error50099500995009950099with Logwith Logwith Logwith LogCustomer deletedCustomer deletedCustomer deletedCustomer deletedby Eva Coretsby Eva Coretsby Eva Coretsby Eva Coretssysalerts Tablesysalerts Tableid idid idnamenamenamename1515151550099500995009950099 .sysnotifications Tablesysnotifications Tablealert_idalert_idalert_idalert_idoperator_idoperator_idoperator_idoperator_id1515151512121212 .sysoperators Tablesysoperators Tableid idid idnamenamenamename12121212Account ManagerAccount ManagerAccount ManagerAccount Manager .将事件写到应用程序日志中将事件写到应用程序日志中nSQL Server Errors Severity Levels Between 19 and 25SQL Server Errors Severity Levels Between 19 and 25nsp_addmessage or sp_altermessage System Stored sp_addmessage or sp_altermessage System Stored ProceduresProceduresnRAISERROR WITH LOG StatementRAISERROR WITH LOG Statementnxp_logevent Extended Stored Procedurexp_logevent Extended Stored Procedure创建响应创建响应SQL ServerSQL Server错误的警报错误的警报nDefining Alerts on SQL Server Error NumbersDefining Alerts on SQL Server Error NumberslMust be written to the Windows application loglSystem-supplied or user-definednDefining Alerts on Error Severity LevelsDefining Alerts on Error Severity LevelslSeverity levels 19 through 25 are automatically loggedlConfigure event forwarding创建用户自定义错误的警报创建用户自定义错误的警报Create the Error MessageCreate the Error MessagelError number must be greater than 50000lParameter placeholders can be usedRaise the Error from Database ApplicationRaise the Error from Database ApplicationlUse the RAISERROR statementlDeclare variables for parameter placeholdersDefine an Alert on the Error MessageDefine an Alert on the Error Message响应性能条件的警报响应性能条件的警报Alert 3All Databases:Severity Level 18Alert 2Northwind Database:Transfer Data ErrorAlert 1:Alert 1:Northwind Database:Log 75%FullNorthwind Database:Log 75%FullExecute Job:Operators to Notify:Operator NameOperator NameMeng PhuaNwind AdminsJose LugoE-mailE-mailNet sendNet sendJob 2:Back Up Northwind Transaction LogJob 2:Back Up Northwind Transaction LogPagerPagerPager SchedulePager Schedule 8:01-6:00 P.M.8:01-6:00 P.M.Nwind AdminsNwind Admins 6:01-12:00 P.M.6:01-12:00 P.M.Jose LugoJose Lugo12:01-8:00 A.M.Meng PhuaThreshold Threshold ReachedReachedat 1:28 A.M.at 1:28 A.M.制定防故障操作员制定防故障操作员Fail-Safe OperatorFail-Safe OperatorPager SchedulePager Schedule12:01-8:00 A.M.12:01-8:00 A.M.Meng PhuaMeng Phua 8:01-6:00 P.M.8:01-6:00 P.M.Nwind AdminsNwind Admins 6:01-12:00 P.M.6:01-12:00 P.M.Jose LugoJose LugoOperator NotificationOperatorsOperatorsMeng PhuaNwind AdminsJose LugoE-mailE-mailNet sendNet sendPagerPagerAlert:Error 18204 Alert:Error 18204 Backup Device FaileduSQL Server SQL Server 自动化排错自动化排错n验证是否已经启动验证是否已经启动SQL ServerSQL Server代理代理n验证是否已经启用作业，调度，警报或操作员验证是否已经启用作业，调度，警报或操作员n确保已经启用代理帐户确保已经启用代理帐户n查看错误日志查看错误日志n查看历史记录查看历史记录n验证邮件客户程序是否运行正常验证邮件客户程序是否运行正常警报排错警报排错nFactors That Cause an Alert Processing BacklogFactors That Cause an Alert Processing BackloglWindows application log is fulllCPU use is unusually highlNumber of alert responses is highnResolving Alert Processing BacklogResolving Alert Processing BackloglDisable the alert temporarilylIncrease delay between responseslCorrect global resource problemlClear the Windows application logLab C:Lab C:创建警报创建警报u多服务器作业自动化多服务器作业自动化n定义主服务器定义主服务器lCreates MSXOperator on master server and all target serverslRepresents a primary department or business unitn定义目标服务器定义目标服务器lAre assigned to one master serverlReside in the same domain as the master server定义主服务器定义主服务器TargetTargetServerServerTargetTargetServerServerTargetTargetServerServerMaster Server Master Server defines jobsdefines jobsTarget server downloads jobs from Target server downloads jobs from Target server downloads jobs from Target server downloads jobs from master servermaster servermaster servermaster server2 2Target server reports job outcome Target server reports job outcome Target server reports job outcome Target server reports job outcome status to master serverstatus to master serverstatus to master serverstatus to master server3 31 12 23 3Use a Domain User Account That Is a Member of the Use a Domain User Account That Is a Member of the Windows Local Group AdministratorsWindows Local Group AdministratorsSend Alerts to E-mail Group Aliases Rather Than to IndividualsSend Alerts to E-mail Group Aliases Rather Than to IndividualsDefine Operators to Respond to Fatal ErrorsDefine Operators to Respond to Fatal ErrorsAssignAssign a a Fail-Safe OperatorFail-Safe OperatorUse Multiserver Jobs to Automate Jobs Across Multiple ServersUse Multiserver Jobs to Automate Jobs Across Multiple Servers推荐练习推荐练习复习复习n配置任务配置任务n例行例行SQL ServerSQL Server管理任务管理任务 n自动化例行管理任务自动化例行管理任务 n创建警告创建警告 nSQL Server SQL Server 自动化排错自动化排错n自动化多自动化多SERVERSERVER作业作业