管理任務自動化

SQL Server代理服務概述: 作業  警報  操作員

                                使用存儲過程定義作業-->sp_add_job(update,delete) sp_add_jobstep(update,delete) sp_add_jobschedule(update,delete) sp_add_jobserver(update,delete)

使用存儲過程定義作業-->建立作業   Declare @jobid uniqueidentifier

                                                          Exec msdb.dbo.sp_add_job

                                                          @job_name=N'Jobname',

                                                          @job_id=@jobid output

                                使用存儲過程定義作業-->定義作業步驟   Declare @sql nvarchar(100),@dbname sysname

                                                              Select @dbname=db_name(),--目前資料庫

                                                              @sql=N'job内容'  -T-SQL語句

                                                              Exec msdb.dbo.sp_add_jobstep

                                                              @job_id=@jobid,

                                                              @step_name=N'job步驟名稱'

                                                              @subsystem='TSQL'-步驟類型

                                                              @database_name=@dbname

                                                              @command=@sql

使用存儲過程定義作業-->定義作業排程   Exec msdb.sp_add_jobschedule

                                                              @job_id=@jobid

                                                              @name=N'排程名'

                                                              @freq_tpye=4                  --每天

                                                              @freq_interval=1              --每天1次

                                                              @freq_subday_type=0x8         --重複方式,0x1:指定時間,0x4:多少分鐘,0x8多少小時執行一次

                                                              @freq_subday_interval=1       --重複周期,每小時執行一次

                                                              @freq_start_data=NULL         --NULL表示目前日期YYYYMMDD

                                                              @freq_end_data=99991231       --預設為99991231

                                                              @freq_start_time=0000         --HHMMSS

                                                              @freq_end_time=235959

SQL Server 2005代理服務安全性增強: 若要使用SQL Server代理,使用者必須是下列一個或多個固定資料庫角色的成員: SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole  這些角色存儲在msdb資料庫中  任何使用者都不是這些資料庫角色的成員,必須顯式授予這些角色中的成員身份  sa可以完全通路SQL Server代理

如果使用者不是其中某個角色的成員,連接配接到SQL Server Management Studio中的SQL Server時,對象資料總管中的"SQL Server代理"将不可見。使用者必須是這些固定資料庫角色之一的成員,或者是sysadmin固定伺服器角色的成員才能使用SQL Server代理。

SQL Server代理安全性原則: 專門為代理建立專用的使用者賬戶,并且隻使用這些代理使用者賬戶來運作作業步驟。隻為代理使用者賬戶授予必需的權限。隻授予運作配置設定給給定代理賬戶的作業步驟實際所需的那些權限。不要作為Windows Administrators組成員的Microsoft Windows賬戶運作SQL Server代理服務。

     較高特權的角色繼承較低特權的角色對SQL Server代理對象(包括警報、運算符、作業、計劃和代理)的權限

SQLAgentUserRole權限: SQLAgentUserRole是具有最低特權的SQL Server代理固定資料庫角色。SQLAgentUserRole的成員隻對它們所擁有的本地作業和作業計劃擁有權限。它們不能使用多伺服器作業(主伺服器作業和目标伺服器作業),也不能通過更改作業所有權來獲得對它們還沒有擁有的作業的通路權限。SQLAgentUserRole的成員隻能在SQL Server Management Studio的"作業步驟屬性"對話框中檢視可用的代理清單。在SQL Server Management Studio對象資料總管中,SQLAgentUserRole的成員隻能看到"作業"節點。

SQLAgentUserRole對SQL Server代理對象的權限-->見下清單:

            操作                  運算符            本地作業(僅限于所擁有的作業)          作業計劃(僅限于所擁有的計劃)             代理

       建立/修改/删除             否                  是                                     是                                   否

       視圖清單(枚舉)             是                  是                                     是                                   是

         啟用/禁用                 否                  是                                     是                                 不适用 

       視圖屬性                    否                  是                                     是                                    否

       執行/停止/開始         不适用                是                                   不适用                               不适用

       檢視作業曆史記錄       不适用               是                                   不适用                               不适用

       删除作業曆史記錄       不适用               否                                   不适用                               不适用

          附加/分離             不适用             不适用                                   是                                 不适用  

SQLAgentReaderRole權限: SQLAgentReaderRole包括所有的SQLAgentUserRole權限,以及檢視可用的多伺服器作業及其屬性和曆史記錄的清單的權限。此角色的成員還可以檢視所有可用作業和作業計劃以及它們的屬性的清單,而不隻是它們所擁有的那些作業和作業計劃。SQLAgentReaderRole成員不能通過更改作業所有權來獲得對它們還沒有擁有的作業的通路權限。在SQL Server Management Studio對象資料總管中,SQLAgentReaderRole的成員隻能看到"作業"節點。

                                    SQLAgentOperatorRole權限: SQLAgentOperatorRole是具有最高特權的SQL Server代理固定資料庫角色。可以執行、停止或啟動所有本地作業,還可以删除伺服器上的任何本地作業的作業曆史記錄。它們還可以啟用或禁用伺服器上的所有本地作業和計劃。若要啟用或禁用本地作業或計劃,此角色的成員必須使用存儲過程sp_update_job和sp_update_schedule。SQLAgentOperatorRole的成員隻能指定那些指定了作業名稱、計劃名稱或辨別符的參數和@enabled參數。

SQL Server自動管理應用場景: 每天晚上自動20點停用UserDB資料庫,早上7點自動啟用該資料庫   建立作業-->Declare @jobid uniqueidentifier Exec msdb.dbo.sp_add_job @job_name=N'定時停用UserDB資料庫',@job_id=@jobid output

                                                                                       定義作業步驟-->Declare @sqlnvarchar       (400),@dbname sysname

                                                                                                      Select @dbname=N'master',

                                                                                                      @sql=N'alter database UserDB set offline with rollback after 10'

                                                                                                      Exec msdb.dbo.sp_add_jobstep

                                                                                                      @job_id=@jobid,

                                                                                                      @step_name=N'啟用UserDB資料處理',

                                                                                                      @subsystem='TSQL',

                                                                                                      @database_name=@dbname,

                                                                                                      @command=@sql

建立排程-->Exec msdb.sp_add_jobschedule

                                                                                                  @job_id=@jobid,

                                                                                                  @name=N'啟用UserDB資料庫處理排程',

                                                                                                  @freq_type=4,

                                                                                                  @freq_interval=1,

                                                                                                  @freq_subday_type=0x1,

                                                                                                  @freq_subday_interval=1,

                                                                                                  @active_start_time=200000  -晚上20點.

                                                                                       添加目标伺服器-->Declare @servername sysname

                                                                                                        Set

                                                                                                            @servername=convert(nvarchar(128),serverproperty(N'serverName'))

                                                                                                        Exec msdb.dbo.sp_add_jobserver

                                                                                                        @job_id=@jobid,

                                                                                                        @server_name=@servername

企業範圍的自動化管理: 跨多個SQL Server執行個體的自動化管理稱為"多伺服器管理"。使用多伺服器管理可以執行的操作: 管理兩台或多台伺服器。在企業伺服器之間安排資料倉庫的資訊流。

                           主伺服器将作業分發到目标伺服器并從它那裡接收事件。目标伺服器定期連接配接到主伺服器來更新它們的作業計劃。如果主伺服器上存在新作業,目标伺服器将下載下傳該作業。目标伺服器在完成作業後,會重新連接配接到主伺服器并報告作業狀态。

                  将該備份作業一次性寫入主伺服器,然後登記目标伺服器。從它們登記時刻起,所有部門伺服器将運作相同的備份作業,而隻需定義一次作業。

                          多伺服器管理功能用于sysadmin角色成員。然而,目标伺服器上的sysadmin角色成員無法編輯目标伺服器上由主伺服器執行的操作。這項安全措施可防止意外删除作業步驟,并可防止目标伺服器上的操作中斷。

兩個服務都應該在Microsoft Windows域賬戶下運作。

                           如果存在大量目标伺服器,應避免将生産伺服器定義為主伺服器。否則,目标伺服器的通信量會降低生産伺服器的性能。

                           如果在Microsoft SQL Server Management Studio以外對多伺服器作業定義進行了更改,則必須将更改釋出到下載下傳清單中,以便目标伺服器可以再次下載下傳更新後的作業。為了確定目标伺服器具有目前的作業定義,在更新多伺服器作業後,需釋出一條INSERT指令。

EXECUTE sp_post_max_operation 'INSERT','JOB','<job id>'

                           實作多伺服器管理後,目标伺服器将定期聯系主伺服器以上載有關已執行的作業的資訊,并下載下傳新的作業。聯系主伺服器的過程稱為"伺服器輪詢",該過程每隔"輪詢間隔"(定期)發生一次。輪詢間隔預設情況下為一分鐘。

                           當目标伺服器輪詢主伺服器時,它從msdb資料庫的sysdownloadlist表中讀取配置設定給目标伺服器的操作。操作包括删除作業、插入作業、啟動作業和更新目标伺服器的輪詢間隔等。

将操作釋出到sysdownloadlist表中有兩種方式: 使用sp_post_max_operation存儲過程顯式釋出。使用其他作業存儲過程隐式釋出。EXECUTE msdb.dbo.sp_post_msx_operation'INSERT','JOB','<job id>'