Archive

Archive for the ‘2005’ Category

A portable generic maintenance plan

June 14, 2011 Comments off

Involved in a large scale virtualisation, consolidation and migration project lately and have been automating as much as possible for SQL installs. Due to the sheer volume of the number of SQL servers, high degree of automation is desirable for SQL infrastructure.

I intended to use Codeplex SQL Server Fine Build by Ed Vasie as a common framework and guidance for installation and configuration of SQL servers. However due to various reasons was unable to use it. I highly recommend using it for guidance even if you are not interested in the scripted unattended installs.

Finebuild provides SQL jobs for routine maintenance activities. Monitoring and maintaining of multiple jobs on few 100 servers could be challenging. Instead I choose single Maintenance Plan approach to carry out maintenance tasks.

Issue

A generic maintenance plan for any new SQL server- I found there is not much guidance on how to render an SSIS package generic with regards to connection and execution context. Also when a SSIS package is exported, associated jobs and any referential integrity constraints are not exported.

Solution

Instead of discussing all the issues I ran into, I will simply take you thro’ a few steps to create and port your SSIS package

You will need two instances of SQL Server to test this.

Source Server – A default or named instance of SQL . Also will need SSIS installed with BIDS.

Example – SERVERA

Target Server – A default or named instance of SQL. SSIS optional

Example – SERVERB\Inst1

  1. Create a single maintenance plan with a one encompasing (single) schedule by going to SSMS > Connect Server > Management > Maint Plan > Maint Plan Wizard. Please refer to MSDN articles for maint plan creation. Note – My plan was called GenericMaint
  2. Now connect to SSIS from SSMS > Object Explorer > Integration Services. Expand Stored Packages > MSDB > Right click on GenericMaint > Export Package…
  3. On the export Package dialog, choose Pacakage location as File System and Pacakage path as <your preferred location>. This is in order to view, edit and export the package to ther servers
  4. Using BIDS open the SSIS pacakage and add a new User variable called TargetSrvr. Also copy the Package ID higlighted on the screen below for later use.

     

  5. Parametrise the Server context by opening the Connection Manager > Properties > Expand Expressions by clicking on the ellipsis > Choose ServerName from Property dropdown > Expression >

  1. Apply changes and save the package
  2. Make note of the SubplanId by highlighting the Subplan_1 as seen below and ID from properties window as highlighted below.

  1. Export the GenericMaint.dtsx plan to destination servers. Note – SSIS packages can be exported and imported only if the server has Integration Services installed on it. Your account will need requisite permissions on the server and SQL instance to perform an export/import.

     

  2. Export/Import using DTUtil. Open SSMS > New Query window to <<Your Target Server>>

  3. Change to SQLCMD mode > SSMS Menu> Query > SQCMD Mode. Copy the code below to Query window

—    SCRIPT to Export SSIS package from 127.0.0.1 server to a target server

— Author: Ajith@Dell 08/06/2011

–/FILE “\\127.0.0.1\it\DTS Packages\GenericMaintPlan.dtsx” – Location of SSIS package

–/DestServer <<SQL Instance Name>> – SQL Instance name

–/COPY SQL;”Maintenance Plans/GenericMaintPlan” – Target location for package to be copied

!!dtutil /Q /FILE “\\127.0.0.1\<<Your Package location>>\DTS Packages\GenericMaintPlan.dtsx” /DestServer <<SQL Instance Name>> /COPY SQL;”Maintenance Plans/GenericMaintPlan”

DTUtil is reusable and beneficial in large scale deployments. Alternatively copy the package to your target server by Open SSMS > Connect to Integration Services > Choose source server > Expand Source Server -> Stored Packages > Maintenance Plans > Right click > Export Package…

Export the package to the target SQL Server – Package Location: SQL Server; Server: <<Target Server>>; Package Path: Maintenance Plans

Click OK to complete the save and export

  1. Now you will need to create a job to execute the maintenance plan SSIS package.
    Important – Exporting a maintenance plan does not export associated jobs.
  2. Run the following script on each target server to create the job. Highlighted sections of the script should be eyeballed and changed if required. Some of the highlighted sections in the script are self-explanatory where critical changes were made.

USE [msdb]

SET
ANSI_NULLS
ON

GO

SET
QUOTED_IDENTIFIER
ON

GO

— SCRIPT to create a job to execute generic SSIS package

— Author: Ajith@Dell 08/06/2011

–#region DropSPROC-DropJob

IF
EXISTS
(SELECT * FROM

sys.objects
WHERE
object_id =

OBJECT_ID(N'[dbo].[DropJob]’)
AND
type
in
(N’P’, N’PC’))

BEGIN

    DROP
PROCEDURE [dbo].[DropJob]

END

–#endregion

GO

–#region CreateSPROC-DropJob

USE [msdb]

SET
ANSI_NULLS
ON

GO

SET
QUOTED_IDENTIFIER
ON

GO

CREATE
PROC [dbo].[DropJob]

@JobName AS
VARCHAR(200)
=
NULL

AS

DECLARE @msg AS
VARCHAR(500);

–Author Clay McDonald – http://claysql.blogspot.com/2009/07/cant-delete-job-microsoft-sql-server.html

IF @JobName IS
NULL

BEGIN

SET @msg =
N’A job name must be supplied for parameter @JobName.’;

RAISERROR(@msg,16,1);

RETURN;

END

IF
EXISTS
(

SELECT subplan_id FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN

(
SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN

(SELECT job_id FROM msdb.dbo.sysjobs_view
WHERE name =
@JobName)))

BEGIN

DELETE
FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN

(
SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN

(SELECT job_id FROM msdb.dbo.sysjobs_view
WHERE name =
@JobName));

DELETE
FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN

(SELECT job_id FROM msdb.dbo.sysjobs_view
WHERE name =
@JobName);

EXEC msdb.dbo.sp_delete_job
@job_name=@JobName, @delete_unused_schedule=1;

END

ELSE
IF
EXISTS
(

SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN

(SELECT job_id FROM msdb.dbo.sysjobs_view
WHERE name =
@JobName))

BEGIN

DELETE
FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN

(SELECT job_id FROM msdb.dbo.sysjobs_view
WHERE name =
@JobName);

EXEC msdb.dbo.sp_delete_job
@job_name=@JobName, @delete_unused_schedule=1;

END

ELSE

BEGIN

EXEC msdb.dbo.sp_delete_job
@job_name=@JobName, @delete_unused_schedule=1;

END

GO

–#endregion

–#region Scripted Job for GenericMaintPlan

BEGIN
TRAN

DECLARE @myjobname NVARCHAR(256), @planname NVARCHAR(256)

SET @planname =
N’GenericMaintPlan’

SET @myjobname =
N’DBA-GenericMaintPlan.Subplan_1′

DECLARE @retjobId BINARY(16)

DECLARE @ReturnCode INT, @schedule_id_Out INT

SELECT @ReturnCode = 0

DECLARE @jobscheduleid BINARY(16)

SELECT @jobscheduleid =
NEWID()

DECLARE @theplanid UNIQUEIDENTIFIER, @thesubplanid UNIQUEIDENTIFIER, @subplanname NVARCHAR(56)

DECLARE @cmdtxt NVARCHAR(MAX)

SET @theplanid =
‘F99D7F9E-848A-464C-86C7-80642561CFFC’
–Should be brought over from SSIS Package see package properties

SELECT @thesubplanid=‘8C8459C8-D38E-4E4F-BB9A-4E4E45C4A69D’
–Should be brought over from SSIS Package’s subplanid see package properties

SELECT @subplanname =
N’Subplan_1′

SET @cmdtxt=N’/SQL “Maintenance Plans\’+ @planname +‘” /SERVER “$(ESCAPE_NONE(SRVR))” /CHECKPOINTING OFF /SET “\Package\Subplan_1.Disable”;false /SET “\Package.Variables[User::TargetSrvr]”;”$(ESCAPE_NONE(SRVR))”

IF
EXISTS
(SELECT job_id FROM msdb.dbo.sysjobs_view
WHERE name =
@myjobname)

BEGIN

    EXEC msdb.dbo.DropJob
@myjobname

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

EXEC @ReturnCode=msdb.dbo.sp_add_job
@job_name=@myjobname,

        @category_id=3, @job_id = @retjobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode =msdb.dbo.sp_add_jobstep
@job_id=@retjobId, @step_name=N’Subplan_1′,

        @step_id=1, @subsystem=N’SSIS’,

        @command=@cmdtxt


IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode =msdb.dbo.sp_update_job
@job_id=@retjobId,

        @enabled=1,

        @start_step_id=1,

        @category_name=N’Database Maintenance’,

        @owner_login_name=N'<<Domain>>\<<User Name>>’
–Update as required

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id=@retjobId

IF
EXISTS(SELECT 1 WHERE
@@VERSION LIKE

‘%SQL Server 2008%’)

BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id=@retjobId, @name=@myjobname,

            @enabled=1,

            @freq_type=8,

            @freq_interval=1,

            @freq_subday_type=1,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=1,

            @active_start_date=20110610,

            @active_end_date=99991231,

            @active_start_time=0,

            @active_end_time=235959,

            @schedule_uid=@jobscheduleid,

            @schedule_id=@schedule_id_Out OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

ELSE

BEGIN

    PRINT
‘Non 2008 version detected’

    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id=@retjobId, @name=@myjobname,

            @enabled=1,

            @freq_type=8,

            @freq_interval=1,

            @freq_subday_type=1,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=1,

            @active_start_date=20110610,

            @active_end_date=99991231,

            @active_start_time=0,

            @active_end_time=235959,

            –@schedule_uid=@jobscheduleid, — @schedule_uid not supported for 2005

            @schedule_id=@schedule_id_Out OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

EXEC @ReturnCode=msdb.dbo.sp_attach_schedule
@job_id=@retjobId,@schedule_id=@schedule_id_Out

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

–The following step was added to overcome job execution failure.

EXEC @ReturnCode=msdb.dbo.sp_maintplan_update_subplan

    @subplan_id     =@thesubplanid,

@plan_id =@theplanid,

@name =@subplanname,

@description =‘Created by CREATE_JOB_DBA-GenericMaintPlan…SQL’,

@job_id =@retjobId,

@schedule_id =@schedule_id_Out,

@allow_create =1 — Allow create =1 enables creating of subplans where SSIS packages are imported

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT
TRANSACTION

GOTO EndSave

QuitWithRollback:

    print
‘error occured’


IF (@@TRANCOUNT > 0) ROLLBACK
TRANSACTION

EndSave:

–#endregion

  1. Verify by executing the job

Disclaimer- I have done very limited testing on the scripts. I have found the scripts are working satisfactorily on SQL Server 2005, 2008 and R2. Use it at your own risk. Feel free to make any amendments as required.

If you have any improvements or suggestions please feel free to contact me or comment here….

Advertisements
Categories: 2005, 2008, R2, SQL Server, SSIS