Archive

Archive for the ‘SQL Server’ Category

When SQL Server AlwaysOn for HADR is not always suitable

November 22, 2013 Comments off

Contrary to popular perception on how smooth SQL AlwaysOn can be for HADR scenarios, my experiences with it has been not so great when it comes to DR situation. AlwaysOn will need meticulous design, planning and DR procedures (TEST, TEST, and TEST) to ensure smooth failover/failback.

I embarked on designing and implementing SQL 2012 HADR solution across two sites based on white paper FCI+AG. My implementation was 2 node SQL VM FCI at each site and had additional complexities –

  • HyperV Virtualization
  • Only Guest Failover Clustering
  • SQL Disks presented directly to the guest
  • Mount Points to circumvent limited drive letters

You might be asking me WHY FOR HEAVENS SAKE? Yes I did as well. It was a strict customer requirement to virtualize everything. Virtualizing also helped reduce SQL Licensing cost (for core counts). FCI will require Guest clustering hence the disks need to be direct attached to the guest from a shared SAN with multipath IO. Redundancy was built in at each physical and logical layers sufficiently (rather excessively).

Figure 1: High Level SQL Solution

Note: Storage replication was strictly to transport backups between site

Although it was complex I successfully implemented it and tested the failover and failback within the stipulated RPO/RTO. But soon after we started experiencing intermittent disruptions at host level resulting in unexpected power on/off of SQL nodes. This resulted in some databases on DR appear in RECOVERY PENDING status. Resolution for which is to remove database from AG or recreate the AG. Important fact is the server instance on which the secondary replica is hosted will be unavailable in such case. Your upstream applications are using AG Listener and any changes or reverting to instance name will have further rework, testing and downtime.

Lesson learnt –

  • You may want a second local server instance to host a secondary replica. This should help you restore and recreate AG Databases without having to entirely rely on second site. But beware of the licensing implications and cost of doubling compute, storage and network resources at each site
  • Alternatively completely forgo FCI
  • Look at further abstraction from AG such as SQL ALIAS for upstream applications
  • Have a strong SQL DBA monitor and oversee SQL environment and SQL DR procedures instead of your platform guys

Question is though does it really pay to have AlwaysOn HADR? Using Storage Replication technologies for syncing backups/Tlogs and manual DR restore maybe smoother in some cases. What are your thoughts?

Advertisements

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….

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