Cơ sở dữ liệu - Chapter 5: Automating administrative tasks

CHAPTER 5Automating Administrative Tasks AgendaAutomating Administrative Tasks in SQL Server SQL Server AgentCreating Maintenance Plans1. Overview of Automating Admin TaskOne of the most important advantages of Database Engine is its capability to automate administrative tasks and hence to reduce costs. Examples of some important tasks that are performed frequently and therefore could be automated:Backing up the database and transaction logTransferring dataDropping and re-creating indexChecking data integrity2. SQL Server AgentAutomation Components: There are four basic components of SQL Server Agent:Jobs: Define the work to be done.Schedules: Define hen the job will be executed.Alerts: Enables you to set up an automatic response or notification when an event occurs.Operators: The people who can be notified regarding job status and alerts.2. SQL Server AgentJobs:Jobs are the tasks you wish to execute on a schedule. Jobs consist of job steps, which define the individual task to run and what happens if the step fails. Steps are defined using Transact-SQL or wizards.When defining job steps, you can specify their order.Jobs are associated with flexible schedules.2. SQL Server AgentSchedule:Each created job can be executed on demand (manually by the user) or by using one or more schedules. A scheduled job can occur at any of these times. When SQL Server Agent starts.Once, at a specified date and time.On a recurring basis.When the CPU utilization of your server is idle3. SQL Server AgentAlert:Actions that SQL Server will perform when a particular event occurs. Alerts consist of conditions and actions. Conditions can be specific error codes, error severities or object states like databases files growing to a certain size. Actions performed by an alert are notifying a person, or running a job3. SQL Server AgentAlert:SQL Server event.The SQL Server event alerts are based mainly on error messages. You might create an alert on error message number 9002 (log file full) or 1105 (out of disk space) message. An alert can be fired for any particular database or all databasesSQL Server performance condition.Windows Management Instrumentation (WMI) event.3. SQL Server AgentOperator:People who will be notified of alerts or job completion. SQL Server Agent can notify operators through e-mail, pager, or a net send network command.3. SQL Server AgentMulti Server Job: SQL Server also supports the ability to create and manage jobs on one server that can be run on multiple SQL Servers. This functionality grants you the ability to administer and control multiple servers at once.Multi-server jobs are configured by first defining a master server. 3. Maintenance planMaintenance plans are a quick and easy way to automate routine maintenance tasks in SQL Server.There are two ways to create maintenance plans:Use the Maintenance Plan WizardThe manual way: use the Maintenance Plan Designer.3. Maintenance planMaintenance Plan Wizard:Through the Wizard, available tasks to be performed automatically:Checking database integrityShrink the databaseRe-organize indexesRe-build indexesUpdate statisticsClean up historyExecuting a SQL Server Agent jobBacking up databases using full, differential, or transaction log backups3. Maintenance planMaintenance Plan Wizard: Following steps are used to create plan:The first step is to launch the Wizard.On this page, specify a name and description for the plan and select the scheduling optionsSelect Next to move on to the Select Maintenance Tasks screen to choose the tasks that needs the plan to perform. If multiple tasks are selected  reorder them to run in the orderSelect the reporting options for the plan: write a log to a specific locationSend an e-mail, or both.Confirm the selections and click “Finish”3. Policy-based ManagementPolicy-Based Management allows user to do the following tasks:view and configure settings on multiple instances of SQL Server.apply a configuration change to a group of servers with one administrative action. enforce standards on your SQL Server, for example, object naming standards. 3. Policy-based ManagementKey components of Policy-Based ManagementManaged targets: Are the objects will be managed with Policy-Based Management. Instances of SQL Server, databases, tables, indexes, and other database objects can be targeted for Policy-Based Management. Targets are presented in a logical hierarchy. Facet:Are essentially a logical group of properties that apply to a particular target. For example, the facet Server Information applies to targets of type Server. This facet contains many properties including Collation, IsClustered, and IsSingleUser.3. Policy-based ManagementKey components of Policy-Based ManagementCondition:Are expressions that represent what states are allowed for a particular target or target set.The expression is generally represented by a formula such as facet property value. An example is Table facet, Name LIKE ‘tbl_’. This condition states that table names must start with ‘tbl_’. 3. Policy-based Management3. Policy-based Management