Overblog Suivre ce blog
Editer l'article Administration Créer mon blog
9 octobre 2012 2 09 /10 /octobre /2012 09:13

On Sql Server Standard

On Sql server Standard edition, this is actually easier in a way. You do not manage partition scheme or function or at least not in the way the enterprise version does. You actually set up tables to be your partitions. If you want to access several storage units, you can then affect different filegroup to the different tables.  In our case, I did not work on it but this would be easy to set this up. Identically to the previous example, here we will keep only three months worth of log data, keeping 4 rolling monthly partition scheme.

First, we create the 4 partitions (4 tables):

USE[DataPartitions]

GO

CREATE SCHEMA Standard Authorization dbo

GO

CREATE TABLE Standard.[LogTrace_Partition1](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [insertDate] [datetime] NULL CONSTRAINT [DF_LogTrace_InsertDate_Partition1]  DEFAULT (getdate()),

      [sessionID] [varchar](36) NULL,

      [userAgent] [varchar](500) NULL,

      [eventID] [int] NULL,

      [requestID] [int] NULL,

      [frontEndSessionID] [varchar](36) NULL,

      [trace] [varchar](5000) NULL,

      [serverName] [varchar](50) NULL,

      [userIP] [varchar](15) NULL,

      [note] [varchar](50) NULL,

PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

CREATE TABLE Standard.[LogTrace_Partition2](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [insertDate] [datetime] NULL CONSTRAINT [DF_LogTrace_InsertDate_Partition2]  DEFAULT (getdate()),

      [sessionID] [varchar](36) NULL,

      [userAgent] [varchar](500) NULL,

      [eventID] [int] NULL,

      [requestID] [int] NULL,

      [frontEndSessionID] [varchar](36) NULL,

      [trace] [varchar](5000) NULL,

      [serverName] [varchar](50) NULL,

      [userIP] [varchar](15) NULL,

      [note] [varchar](50) NULL,

PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE Standard.[LogTrace_Partition3](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [insertDate] [datetime] NULL CONSTRAINT [DF_LogTrace_InsertDate_Partition3]  DEFAULT (getdate()),

      [sessionID] [varchar](36) NULL,

      [userAgent] [varchar](500) NULL,

      [eventID] [int] NULL,

      [requestID] [int] NULL,

      [frontEndSessionID] [varchar](36) NULL,

      [trace] [varchar](5000) NULL,

      [serverName] [varchar](50) NULL,

      [userIP] [varchar](15) NULL,

      [note] [varchar](50) NULL,

PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE Standard.[LogTrace_Partition4](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [insertDate] [datetime] NULL CONSTRAINT [DF_LogTrace_InsertDate_Partition4]  DEFAULT (getdate()),

      [sessionID] [varchar](36) NULL,

      [userAgent] [varchar](500) NULL,

      [eventID] [int] NULL,

      [requestID] [int] NULL,

      [frontEndSessionID] [varchar](36) NULL,

      [trace] [varchar](5000) NULL,

      [serverName] [varchar](50) NULL,

      [userIP] [varchar](15) NULL,

      [note] [varchar](50) NULL,

PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

 

Second, we create the partition scheme (1 table):

 

CREATE TABLE Standard.LogTracePartition

(

PartitionId tinyint,

TableName varchar(256),

IsCurrentPartition Bit,

NextPartitionId tinyint,

PurgePartitionId tinyint null,

CONSTRAINTPK_LogTracePartition PRIMARY KEY CLUSTERED (PartitionId)

)

Go

insert into Standard.LogTracePartition

(PartitionId, TableName, IsCurrentPartition, NextPartitionId, PurgePartitionId)

Values (1,'Standard.LogTrace_Partition1',1,2,2)

GO

insert into Standard.LogTracePartition

(PartitionId, TableName, IsCurrentPartition, NextPartitionId, PurgePartitionId)

Values (2,'Standard.LogTrace_Partition2',0,3,3)

GO

insert into Standard.LogTracePartition

(PartitionId, TableName, IsCurrentPartition, NextPartitionId, PurgePartitionId)

Values (3,'Standard.LogTrace_Partition3',0,4,4)

GO

insert into Standard.LogTracePartition

(PartitionId, TableName, IsCurrentPartition, NextPartitionId, PurgePartitionId)

Values (4,'Standard.LogTrace_Partition4',0,1,1)

GO

 

Third, we create the stored procedure to switch partition to be the current one (using Synonym and truncate the data we do not need to have on production):

 

/*

Author: Chuge

Creation Date: 2012-10-08

Reason: Partition scheme on standard edition to switch current partition

Sample: EXEC Standard.P_SwitchLogTracePartition @SynonymName = 'Standard.LogTraceS', @Debug = 1

select object_id('Standard.LogTraceS')

select * from Standard.LogTraceS

*/

CREATE PROCEDURE Standard.P_SwitchLogTracePartition

@SynonymName VARCHAR(256) = 'Standard.LogTraceS',

@Debug bit = 0

AS

 

SET NOCOUNT ON

DECLARE@TableNameNewPartition varchar(256), @TableNamePurgePartition varchar(256), @Stmt VARCHAR(1024), @NextPartitionId tinyint, @PurgePartitionId tinyint

 

BEGIN TRY

 

SELECT TOP 1 @NextPartitionId = NextPartitionId, @PurgePartitionId = PurgePartitionId

FROM Standard.LogTracePartition

WHEREIsCurrentPartition = 1

 

SELECT@TableNameNewPartition = TableName

FROM Standard.LogTracePartition

WHEREPartitionId = @NextPartitionId

 

SELECT@TableNamePurgePartition = TableName

FROM Standard.LogTracePartition

WHEREPartitionId = @PurgePartitionId

 

IF OBJECT_ID(@TableNameNewPartition) IS NULL

BEGIN

      SELECT 1

      RETURN

END

IF OBJECT_ID(@TableNamePurgePartition) IS NULL

BEGIN

      SELECT 2

      RETURN

END

 

IF OBJECT_ID(@SynonymName) IS NOT NULL

BEGIN

      SET @Stmt = 'DROP SYNONYM ' + @SynonymName

      IF @Debug = 1

            PRINT @Stmt

      ELSE

            EXEC ( @Stmt )

END

 

SET @Stmt = 'TRUNCATE TABLE ' + @TableNamePurgePartition

IF @Debug = 1

      PRINT @Stmt

ELSE

      EXEC ( @Stmt )

SET @Stmt = 'CREATE SYNONYM ' + @SynonymName + ' FOR ' + @TableNameNewPartition

IF @Debug = 1

      PRINT @Stmt

ELSE

      EXEC ( @Stmt )

 

IF @Debug = 1

      RETURN

 

UPDATE P

SETIsCurrentPartition = CASE WHEN PartitionId = @NextPartitionId THEN 1 ELSE 0 END

FROM Standard.LogTracePartition P

 

END TRY

BEGIN CATCH

      DECLARE @Error VARCHAR(512)

      SET @Error = ERROR_MESSAGE()

      RAISERROR(@Error,16,1)

      RETURN @@Error

END CATCH

SET NOCOUNT OFF

GO

 

 

Fourth, we create the scheduled job to switch partition at midnight the 1rst of each month;

 

 

USE [msdb]

GO

/****** Object:  Job [WebApper - Data partition Switch out]    Script Date: 02/01/2012 12:08:05 ******/

BEGIN TRANSACTION

DECLARE@ReturnCode INT

SELECT@ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 02/01/2012 12:08:05 ******/

IF NOT EXISTS (SELECT name FROM msdb.Standard.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC@ReturnCode = msdb.Standard.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

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

 

END

 

DECLARE @jobId BINARY(16)

EXEC@ReturnCode =  msdb.Standard.sp_add_job @job_name=N'WebApper - Data partition Switch out',

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=0,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'No description available.',

            @category_name=N'[Uncategorized (Local)]',

            @owner_login_name=N'sa', @job_id = @jobId OUTPUT

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

/****** Object:  Step [EXEC Standard.P_SwitchLogTracePartition @SynonymName = 'Standard.LogTrace', @Debug = 0]    Script Date: 02/01/2012 12:08:05 ******/

EXEC@ReturnCode = msdb.Standard.sp_add_jobstep @job_id=@jobId, @step_name=N'EXEC Standard.P_SwitchLogTracePartition @SynonymName = ''Standard.LogTrace'', @Debug = 0',

            @step_id=1,

            @cmdexec_success_code=0,

            @on_success_action=1,

            @on_success_step_id=0,

            @on_fail_action=2,

            @on_fail_step_id=0,

            @retry_attempts=0,

            @retry_interval=0,

            @os_run_priority=0, @subsystem=N'TSQL',

            @command=N'EXEC Standard.P_SwitchLogTracePartition @SynonymName = ''Standard.LogTrace'', @Debug = 0',

            @database_name=N'DataPartitions',

            @flags=0

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

EXEC@ReturnCode = msdb.Standard.sp_update_job @job_id = @jobId, @start_step_id = 1

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

EXEC@ReturnCode = msdb.Standard.sp_add_jobschedule @job_id=@jobId, @name=N'Webapper - Monthly',

            @enabled=1,

            @freq_type=16,

            @freq_interval=1,

            @freq_subday_type=1,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=1,

            @active_start_date=20110303,

            @active_end_date=99991231,

            @active_start_time=0,

            @active_end_time=235959

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

EXEC@ReturnCode = msdb.Standard.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

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

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

 

GO

 

On Sql server Express

There are even more limitations for the Express version such as no agent (need to have different scheduler) and database size is limited to 1 GB.
Your partitioning would then be managed by an application (scheduled services and web services probably).
The partitions would then be identical to the standard version one except that you would need to specify the context of the shard.
In the order example, one can decide to set up 24 databases, one for each monthly partition, assuming that a month worth of data/index would not be bigger than 1 GB. SQL server express data partitioning will work best with a service oriented architecture where all applications will need to go through intelligent web methods that will select the proper database to select or update the data.
Obviously you can use the system used in standard version, above, within the databases as well. You just need to be aware of the 1 GB limitation.

 

Partager cet article

Repost 0
Published by clementhuge.over-blog.com - dans Administration
commenter cet article

commentaires