SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Vertical Partitioning with transactional replication


Vertical Partitioning with transactional replication

Author
Message
paul.clark 83106
paul.clark 83106
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 67
I have a publisher and a subscriber database under transactional replication. The schemas are identical. One of the tables has 2 columns (BIGINT NOT NULL and CHAR(1) NOT NULL) that i do not want to be replicated.

I've tried applying vertical partitioning by removing the 2 columns after the article has been created, however i'm getting the following error:

Error messages:
The process could not bulk copy into table '"dbo"."TestTable"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
End of file reached, terminator missing or field data incomplete
To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253
bcp "PortalRepTest"."dbo"."TestTable" in "\\CHEEZLYVMAIN642\ReplData\unc\CHEEZLYVMAIN64_PORTALREPTEST_BASE\20121207164942\TestTable_451.bcp" -e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SCHEEZLYVMAIN642 -T -w (Source: MSSQLServer, Error number: 20253)



Running the BCP manually:
SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Native Client] Unexpected EOF encountered in BCP data-file



This doesn't occur if i don't remove the 2 columns from the article. I can see where the error message is coming from but i would expect that during an insert the default values for the 2 unreplicated columns would be taken (i have default constraints defined).

Am i missing something? What's the point of vertical partitioning if this doesn't work?
erichbrinker
erichbrinker
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 540
Sorry, not sure I understand your question...Are you just wanting to keep from replicating 2 of the columns, or are you trying to implement partitioning on the table as well?

Also, just to be clear...Are publisher and subscriber on 2008?
paul.clark 83106
paul.clark 83106
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 67
I'm trying to prevent replication of 2 of the columns. Both tables have the same schema. It works if the subscriber doesn't have these 2 columns but i need those 2 unreplicated columns on both the publisher and subscriber.

Yes SQL Server 2008.
erichbrinker
erichbrinker
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 540
Sorry, for the delay...

How are you trying to filter out the columns you don't want? Did you uncheck from the article, did you apply a filter and leave all the columns? Not sure how you are doing it now?

What methods have you tried to not replicate them?
paul.clark 83106
paul.clark 83106
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 67
Thanks for replying. I've tried via the UI by unticking them from the article, i've tried RMO using the example here (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.transarticle.removereplicatedcolumns.aspx) and the sp_articlecolumn sproc. All essentially do the same.

Previously the columns were in a separate unreplicated table but due to performance problems caused by joining 100,000's rows together and index fragmentation i'm trying to move them into the same table.
erichbrinker
erichbrinker
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 540
Can you post the table scripts from publisher and subscriber? Also if you can script out the publication and post that or send it to me that would help alot. Also, please make sure nothing sensitive or secure would show up in the scripts.

Thanks
paul.clark 83106
paul.clark 83106
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 67
I've created a test script. The error message has changed slightly:

The process could not bulk copy into table '"dbo"."TEST"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Field size too large

...but the cause of the problem is still fundamentally the same.

USE [master]
GO

/****** Object: Database [TestRepl] Script Date: 12/14/2012 16:12:57 ******/
CREATE DATABASE [TestRepl] ON PRIMARY
( NAME = N'TestRepl', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\TestRepl.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestRepl_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\TestRepl_log.ldf' , SIZE = 1280KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [TestRepl] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestRepl].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

USE [TestRepl]
GO

/****** Object: Table [dbo].[TEST] Script Date: 12/14/2012 16:13:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TEST](
[ID] [int] IDENTITY(1,4) NOT FOR REPLICATION NOT NULL,
[NAME] [nvarchar](50) NOT NULL,
[DESCRIPTION] [nvarchar](50) NULL,
[LOCAL] [bigint] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_TEST] 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

ALTER TABLE [dbo].[TEST] ADD CONSTRAINT [DF_TEST_LOCAL] DEFAULT ((0)) FOR [LOCAL]
ALTER TABLE [dbo].[TEST] ADD CONSTRAINT [DF_TEST_msrepl_tran_version] DEFAULT (NEWID()) FOR [msrepl_tran_version]
GO


INSERT INTO dbo.TEST (NAME, DESCRIPTION, LOCAL)
VALUES ('Tester1', 'Tests things', 3)

INSERT INTO dbo.TEST (NAME, DESCRIPTION, LOCAL)
VALUES ('Tester2', 'Tests more things', 8)

INSERT INTO dbo.TEST (NAME, DESCRIPTION, LOCAL)
VALUES ('Tester3', 'Tests other things', 10)
GO




-------------------------------------------------------------------------------------------------------------------------------------
-- Create Publication
-------------------------------------------------------------------------------------------------------------------------------------


-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'TestRepl', @optname = N'publish', @value = N'true'
GO

exec [TestRepl].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
exec [TestRepl].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
GO
-- Adding the transactional publication
use [TestRepl]
exec sp_addpublication @publication = N'TestPub', @description = N'Transactional publication with updatable subscriptions of database ''TestRepl'' from Publisher ''PUBLISHERBOX''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'true', @autogen_sync_procs = N'true', @allow_queued_tran = N'true', @allow_dts = N'false', @conflict_policy = N'pub wins', @centralized_conflicts = N'true', @conflict_retention = 14, @queue_type = N'sql', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @publication = N'TestPub', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'TestPub', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'PUBLISHERBOX\sql_agent_user'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'WIN-LI1G3M9O0B4\SQLServer2005SQLAgentUser$WIN-LI1G3M9O0B4$MSSQLSERVER'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'WIN-LI1G3M9O0B4\SQLServer2005MSSQLUser$WIN-LI1G3M9O0B4$MSSQLSERVER'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'distributor_admin'
GO

-- Adding the transactional articles
use [TestRepl]
exec sp_addarticle @publication = N'TestPub', @article = N'TEST', @source_owner = N'dbo', @source_object = N'TEST', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'delete', @schema_option = 0x000000000003008F, @identityrangemanagementoption = N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @destination_table = N'TEST', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'true', @ins_cmd = N'CALL [dbo].[sp_MSins_dboTEST]', @del_cmd = N'VCALL [dbo].[sp_MSdel_dboTEST]', @upd_cmd = N'VCALL [dbo].[sp_MSupd_dboTEST]'

-- Adding the article's partition column(s)
exec sp_articlecolumn @publication = N'TestPub', @article = N'TEST', @column = N'ID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'TestPub', @article = N'TEST', @column = N'NAME', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'TestPub', @article = N'TEST', @column = N'DESCRIPTION', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'TestPub', @article = N'TEST', @column = N'msrepl_tran_version', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

-- Adding the article synchronization object
exec sp_articleview @publication = N'TestPub', @article = N'TEST', @view_name = N'SYNC_TEST_1__54', @filter_clause = N'', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO

-- Adding the transactional subscriptions
use [TestRepl]
exec sp_addsubscription @publication = N'TestPub', @subscriber = N'SUBSCRIBERBOX', @destination_db = N'TestRepl', @subscription_type = N'Pull', @sync_type = N'automatic', @article = N'all', @update_mode = N'queued failover', @subscriber_type = 0
GO







-------------------------------------------------------------------------------------------------------------------------------------
-- Create Subscription
-------------------------------------------------------------------------------------------------------------------------------------
-- Adding the transactional pull subscription

/****** Begin: Script to be run at Subscriber ******/
use [TestRepl]
exec sp_addpullsubscription @publisher = N'PUBLISHERBOX', @publication = N'TestPub', @publisher_db = N'TestRepl', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'queued failover', @immediate_sync = 1
exec sp_link_publication @publisher = N'PUBLISHERBOX', @publication = N'TestPub', @publisher_db = N'TestRepl', @security_mode = -1, @login = N'', @password = null
exec sp_addpullsubscription_agent @publisher = N'PUBLISHERBOX', @publisher_db = N'TestRepl', @publication = N'TestPub', @distributor = N'PUBLISHERBOX', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = N'', @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
/****** End: Script to be run at Subscriber ******/

/****** Begin: Script to be run at Publisher ******/
/*use [TestRepl]
-- Parameter @sync_type is scripted as 'automatic', please adjust when appropriate.
exec sp_addsubscription @publication = N'TestPub', @subscriber = N'SUBSCRIBERBOX', @destination_db = N'TestRepl', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'queued failover'
*/
/****** End: Script to be run at Publisher ******/
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3926 Visits: 8472
Did anyone get anywhere with this?


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
erichbrinker
erichbrinker
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 540
sorry, had some personal things come up and was out for awhile. Will try and look at this later this evening
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search