SQL Server issue: switching partitions using the parameter with enabled replication

Forrards
March 30, 2023

It’s quite an unpleasant thing that we had to face once. There was a working script, which had been functioning for a long time and did not cause any problems until a need to replicate a few directories...
But let’s get into the details step by step:

First of all, we create the partition function and a scheme to demonstrate very unexpected behavior of SQL Server
set nocount on;

--Create test database
create database test_db;
go

--Create test partition
create partition function pf_test_dt (datetime)
as range right for values ( '20121201', '20121202', '20121203' );
go

create partition scheme ps_test_dt
as partition pf_test_dt all to ( [Primary] );
go

Now we add 3 tables: one partitioned according to the created scheme, in which we will switch the data and two heap tables

-- Partitioning test table
if object_id ( N'dbo.test_table', N'U' ) is not null
drop table dbo.test_table;
go
create table dbo.test_table ( dt datetime
, a int
, b int
) on ps_test_dt (dt);
go

-- Heap table №1 for switching
if object_id ( N'dbo.test_table_01', N'U' ) is not null
drop table dbo.test_table_01;
go
create table dbo.test_table_01 ( dt datetime
check ( dt >= '20121201' and dt < '20121202' and dt is not null ) , a int , b int ); Go -- Heap table №2 for switching if object_id ( N'dbo.test_table_02', N'U' ) is not null drop table dbo.test_table_02; go create table dbo.test_table_02 ( dt datetime check ( dt >= '20121202' and dt < '20121203' and dt is not null ) , a int , b int ); go -- Insert data into both heap tables insert into dbo.test_table_01 values ( '20121201 04:00', 1, 1 ) , ( '20121201 05:00', 1, 1 ) , ( '20121201 06:00', 1, 1 ); go insert into dbo.test_table_02 values ( '20121202 04:00', 2, 2 ) , ( '20121202 05:00', 2, 2 ) , ( '20121202 06:00', 2, 2 ); go

Now let's switch data from these tables into the partitioned table

-- Make 1st switch through a constant
alter table dbo.test_table_01
switch to dbo.test_table partition $partition.pf_test_dt( '20121201' );
go

-- Make 2nd switch through a variable
declare @dt datetime = '20121202';
alter table dbo.test_table_02
switch to dbo.test_table partition $partition.pf_test_dt( @dt );
go

--Data appeared in the partitioned table
select * from dbo.test_table;

--Delete tables
drop table dbo.test_table
, dbo.test_table_01
, dbo.test_table_02;
go

--Delete partition function and scheme
drop partition scheme ps_test_dt;
drop partition function pf_test_dt;
go

That's all! Now the code works fine ... but up to a certain time! The moment you enable replication a problem appears:

use master
--Enable replication
exec sp_adddistributor @distributor = N'MySQLServer', @password = N'';
exec sp_adddistributiondb @database = N'distribution';
exec sp_adddistpublisher @publisher = N'MySQLServer', @distribution_db = N'distribution';
go

-- Enable the test database for publishing
use test_db;
exec sp_replicationdboption @dbname = N'test_db', @optname = N'publish', @value = N'true';
go

Then run the code specified above (with partitioning) in the context of our database enabled for publishing ... and you get the error:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@dt".
Msg 3609, Level 16, State 2, Line 4
The transaction ended in the trigger. The batch has been aborted.

In real life everything can’t go smoothly and such an error may occur after some time (well, you just have not tested this particular piece of code in the test environment), and you it will take a long time to find out the cause of this behavior. Moreover there are sufficient number of triggers on the production server. But the cause of problem if one of the DDL triggers which are created automatically when you enable publishing, namely tr_MStran_altertable

partitioтing

How to avoid this error:

• Disable replication :)

• Run disable trigger tr_MStran_altertable on database before switching sections with a variable, which is the same absurd:)

• And the most simple way is to make the code dynamic, which will also help.

Later I even found an article in Microsoft Knowledge Base - http://support.microsoft.com/kb/2002474 , but it has reference only to SQL Server 2008 and nothing is said about SQL Server 2008 R2 and SQL Server 2012. And I've got the problem in SQL Server 2012:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

And one more moment that cannot but upset is:

Note:SQL Server 2005 is not affected by this problem since the support for switching partitions in replicated environments is only introduced in SQL Server 2008

Forrards
March 30, 2023
Share
Recent articles:
Database consultancy: SaaS database considerations
March 31, 2023

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

More
Database consultancy: tree-like structure
March 31, 2023

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

More
Database consultancy: Hierarchy in MS SQL Server
March 31, 2023

In our time of greatest prevalence of databases were relational databases, which are the main targets of the tables and the relationships between them. Tables can solve most problems for data storage and manipulation. But in the real world entity requiring storage is not always presented in a tabular form. One of these very common types of data structures other than the table is a tree structure, where each data element is the parent and the offspring. An example of such a structure may be the structure of state enterprises, which is headed by the director (the root of the tree), his deputies, heads of departments from which are subject to certain deputies, employees of departments, which are subject to the rulers.

More

Subscribe to our blog

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Database consultancy: SaaS database considerations

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

March 31, 2023
Database consultancy: tree-like structure

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

March 31, 2023

Discuss your project with us

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.