Perform partition slide in transactional Replication

venk_r
venk_r used Ask the Experts™
on
What are the steps I need to follow to perform successful partition slide in transactional replication?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
Please read the following article on partitionning pruning.  Replication should not be impacted by pruning.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26441035.html

Hope this helps...

Author

Commented:
Thanks for the reply.I dont think the link referes to transactional replication.Currently The partition strategy with switching works fine on a non replication enviornment.But I know there any few steps that needed to be considered before setting transaction replication.
I know we have to set 'allow_partition_switch' and replicate_partition_switch to true on the publication.
Inspite of setting them my replication fails while doing partition slide saying 'The <archived table> is not found'.
Any help is appreciated
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
I see your point now.  What are you replicating ?  How did you plan on setting up or did you set up your pruning.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
Your message that during the pruning, an object the replication expects is not there.   Do you delete any object as a part of the pruning ?

Author

Commented:
Actually part of the partition sliding script is to slide the older partition  to an archive table which gets created on fly Once the slide is done the replication fails saying the archived object doesnt exist anymore.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
Again : what do you replicate ?  Do you replicate a union view ? Do you have any part of the script that deletes the table ?

note that the entire process of what you are basically is also know as partition pruning from an administration perspective (get the recent data into archives)...

Author

Commented:
We replicate almost all articles.We dont have union view.The partiton prune contains a script where it creates a new table and archives the old data into this table.Basically it performs a SWITCH command to switch the data to point to a different intermediary table. I m not dropping any table here.
I just want to accomplish when i do the switch on the publisher it should also reflect in the subscriber but its not doing in this case.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
Please post the script...

Author

Commented:
Please see the below script that does the SWITCH

USE [marcus8]
GO
/****** Object:  StoredProcedure [dbo].[usp_SlidingWindow]    Script Date: 10/18/2010 12:07:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure  [dbo].[usp_SlidingWindow]
@newdate as datetime

as
BEGIN
declare @partition                        int
declare @mindate                        datetime
declare @maxdate                        datetime
DECLARE @fromstagingdate            varchar(25)
DECLARE @tostagingdate            varchar(25)
declare @expirydate                        datetime
declare @partitionname                  varchar(100)
declare @partitionnumber            int
declare @filegroup                        varchar(100)
declare @newmodIFiedtodate            varchar(25)
declare @fromnewdate                  varchar(25)
declare @strsql                              Nvarchar(2000)
declare @strsql1                        Nvarchar(2000)
declare @strsql2                        Nvarchar(2000)
declare @strsql3                        Nvarchar(2000)
declare @strsql4                        Nvarchar(2000)
declare @strsql5                        Nvarchar(2000)
declare @strsql6                        Nvarchar(2000)
declare @strsql7                        Nvarchar(2000)
declare @strsql8                        Nvarchar(2000)
declare @tablename                        Nvarchar(200)
--declare @newdate                        datetime
--SET @newdate='01/09/2009'
SET NOCOUNT, XACT_ABORT ON;

DECLARE
@Error int,
@ErrorLine int,
@Message varchar(255)


BEGIN TRY
BEGIN TRAN;

IF (SELECT OBJECT_ID('tempdb.dbo.##temp')) is not null
BEGIN
DROP table tempdb.dbo.##temp
END
--Formatting the date passed in as input parameter  
SELECT   @newmodIFiedtodate=dbo.getpartitiondate(@newdate)

--Fetch the min and max boundary from the partition
SELECT

                      @mindate= CAST(min(prv.value) AS datetime),
                                @maxdate=CAST(max(prv.value) AS datetime)
         FROM sys.partition_functions AS pf
         JOIN sys.partition_range_values AS prv ON   prv.function_id = pf.function_id
         WHERE pf.name = 'Trackpartitionfn';

--Check for input date validation
IF (@newdate>@maxdate) and (@newdate< convert(varchar(10),dateadd(m,2,@maxdate),101)) --Input date vaildation
BEGIN
      SET @fromstagingdate=CONVERT(VARCHAR(10),DATEADD(D,1,@maxdate),101)

      SELECT @partitionname= ps.name ,
               @partitionnumber=  dds.destination_id ,
                  @filegroup= fg.name
      FROM (((sys.tables AS t
         INNER JOIN sys.indexes AS i
                   ON (t.object_id = i.object_id))
         INNER JOIN sys.partition_schemes AS ps
                   ON (i.data_space_id = ps.data_space_id))
         INNER JOIN sys.destination_data_spaces AS dds
                   ON (ps.data_space_id = dds.partition_scheme_id))
         INNER JOIN sys.filegroups AS fg
                   ON dds.data_space_id = fg.data_space_id
      WHERE (t.name = 'tracks')  AND
      dds.destination_id = $partition.Trackpartitionfn(@mindate)

      --Set the arhival table name
      --Addedd day
      SET @tablename='Archive_'+ ltrim(rtrim(convert(char(10),month(@mindate)))) + ltrim(rtrim(convert(char(10),day(@mindate))))+ ltrim(rtrim(convert(char(10),year( @mindate))))

      --Check if archival table exist
      SET @strsql='SELECT name into tempdb.dbo.##temp from sysobjects where name=''' + @tablename +''''
      EXEC sp_executesql @strsql
      IF EXISTS(SELECT 1 from tempdb.dbo.##temp)
      BEGIN
            SET @strsql2='DROP table ' + @tablename
            EXEC sp_executesql @strsql2
      END

      IF   OBJECT_ID('TrackStaging') is not null  
      BEGIN  
      DROP table TrackStaging  
      END  

      --Create the Archive and Staging tables
      EXEC usp_createTrackArchive @filegroup ,@tablename
      EXEC usp_createTrackStaging @filegroup
      
      --Switch out to archival table
      SET @strsql3= 'ALTER TABLE TRACKS SWITCH PARTITION ' +  CONVERT(CHAR(10),  @partitionnumber ) +  ' TO  '+ @tablename
      EXEC sp_executesql @strsql3
      --Merge
      ALTER PARTITION FUNCTION Trackpartitionfn() MERGE RANGE (@mindate)
      --Next used Filegroup
      SET @strsql4= 'ALTER PARTITION SCHEME trackpartitionsch NEXT USED '+ @filegroup
      EXEC sp_executesql @strsql4
      --Split
      ALTER PARTITION FUNCTION Trackpartitionfn() SPLIT RANGE ( @newmodIFiedtodate )

      --Drop the existing date constraints
      IF EXISTS (SELECT * from dbo.sysobjects where id = object_id(N'[dbo].datecheck') )
      BEGIN
      ALTER TABLE Tracks
      DROP CONSTRAINT datecheck
      END

      IF EXISTS (SELECT * from dbo.sysobjects where id = object_id(N'[dbo].datecheck1') )
      BEGIN
      ALTER TABLE Tracks
      DROP CONSTRAINT datecheck1
      END
      IF EXISTS (SELECT * from dbo.sysobjects where id = object_id(N'[dbo].datecheckstaging') )
      BEGIN
      ALTER TABLE Trackstaging
      DROP CONSTRAINT datecheckstaging
      END

      --refresh max and min date

      SELECT

                                      @mindate= CAST(min(prv.value) AS datetime),
                                      @maxdate=CAST(max(prv.value) AS datetime)
                   FROM sys.partition_functions AS pf
                        JOIN sys.partition_range_values AS prv ON   prv.function_id = pf.function_id
                        WHERE pf.name = 'Trackpartitionfn';

      SET @mindate=dateadd(mm,-12,@mindate)--go prior 12 months for safer side
      SET @tostagingdate=convert(varchar(25),@MAXDATE,21)

      --Create the date constraints
      SET @strsql5='ALTER TABLE  Tracks WITH CHECK ADD CONSTRAINT DateCheck    CHECK (UtcSatDate >='''  + CONVERT(VARCHAR(25),@mindate) + ''')'
      EXEC sp_executesql @strsql5

      SET @strsql7='ALTER TABLE  trackstaging WITH CHECK ADD CONSTRAINT datecheckstaging    CHECK (UtcSatDate >='''  + @fromstagingdate + '''AND UtcSatDate <= ''' + @tostagingdate  + ''')'
      EXEC sp_executesql @strsql7

      SELECT @partition=$partition.trackpartitionfn(@newmodIFiedtodate)

      --Switch in the staging table
      SET @strsql8='ALTER TABLE trackstaging  SWITCH to Tracks PARTITION  ' + CONVERT(CHAR(10),@partition )
      EXEC sp_executesql @strsql8

END
COMMIT;

END TRY
BEGIN CATCH

SELECT
@Error = ERROR_NUMBER(),
@Message = ERROR_MESSAGE(),
@ErrorLine = ERROR_LINE();

SET @Message =
'Partition maintenenace failed with error %d at line %d: ' +
@Message;
RAISERROR(@Message, 16, 1, @Error, @ErrorLine) WITH NOWAIT;
insert into partitionfailuremsg (messages) values(@Message)
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK ;
END;

END CATCH;

Done:

RETURN COALESCE(@error,0);


END
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
Taking a first quick look I quickly noticed that you can drop a table called TrackStaging when it exists.

 IF   OBJECT_ID('TrackStaging') is not null  
      BEGIN  
      DROP table TrackStaging  
      END  

If that table is replicated then that may explain why...Will continue looking but don't have time to reverse engineer your entire partition pruning.  It seems extremely complex compared to the way I do it.


 

Author

Commented:
Oh really.Can you send your script?Lets see if that helps.
Thanks
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
<<Oh really.Can you send your script?Lets see if that helps.>>
I am currently on vacation and don't have access to my work before at least 2 weeks.  My partition pruning is based on the union views so I don't think this will help much.  But if you are not in hurry I can get it for you within 2 weeks...
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
Meanwhile can you temporarily suspend replication on the article TrackStaging before dropping it.  It may confirm your error message.
Database Architect - Dba - Data Scientist
Commented:
Also please read the following document which explains lots of thing concerning partitionning

http://download.microsoft.com/documents/uk/technet/downloads/technetmagazine/40_47_simplifydb_desfin.pdf

Hope this helps...

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial