Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Vertical Partitioning with transactional replication Expand / Collapse
Author
Message
Posted Friday, December 07, 2012 10:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 27, 2013 2:39 AM
Points: 5, Visits: 65
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?
Post #1394168
Posted Wednesday, December 12, 2012 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 5:31 AM
Points: 20, Visits: 364
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?
Post #1395738
Posted Wednesday, December 12, 2012 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 27, 2013 2:39 AM
Points: 5, Visits: 65
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.
Post #1395757
Posted Thursday, December 13, 2012 4:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 5:31 AM
Points: 20, Visits: 364
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?
Post #1396433
Posted Friday, December 14, 2012 2:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 27, 2013 2:39 AM
Points: 5, Visits: 65
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.
Post #1396534
Posted Friday, December 14, 2012 8:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 5:31 AM
Points: 20, Visits: 364
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
Post #1396693
Posted Friday, December 14, 2012 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 27, 2013 2:39 AM
Points: 5, Visits: 65
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 ******/


Post #1396735
Posted Tuesday, January 08, 2013 7:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:06 AM
Points: 2,404, Visits: 7,311
Did anyone get anywhere with this?


Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1404247
Posted Tuesday, January 08, 2013 8:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 5:31 AM
Points: 20, Visits: 364
sorry, had some personal things come up and was out for awhile. Will try and look at this later this evening
Post #1404271
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse