Data partitioning on Sql Server Express, Standard and Enterprise edition – Part 2
On Sql Server Standard (before 2016 sp1)
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 [Maint - 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.
Related Posts
Leave a Reply Cancel reply
Categories
- Inspiration (1)
- Security (2)
- SQL Architecture (21)
- SQL Monitoring (1)
- SQL Performance (16)
- Tips & tricks (1)
- Uncategorized (5)
- Utilities (1)