Replication

Question

Replication

prabhakarannsn

SSC Eights!

Points: 834
More actions
October 28, 2008 at 4:15 am

#214629

Is this possible , to replicate a table with in the same database?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Clive Strong SSChampion Points: 11222 More actions · Answer 1

Not that I am aware of. There maybe a trick out there to do this. Anyways, you get an error message indicating the that subscription database is the same as the publisher database.

If you need to keep 2 tables in sync in the same database you could use a trigger.

dbaker-620086 SSCrazy Points: 2336 More actions · Answer 2

of course you can't repl from svr1.db1.dbo.tbl1 -> svr1.db1.dbo.tbl1

- i.e. pub source to same sub target

but you can do to

-> svr1.db1.dbo.tbl2

-> svr1.db1.xyz.tbl1

-> svr1.db2.dbo.tbl1

-> svr2.db1.dbo.tbl1

where svr2 could be another instance on the same box

HTH

Dick

prabhakarannsn SSC Eights! Points: 834 More actions · Answer 3

Dear Dick,

i tried to vreate replication between svr1.db1.dbo.tbl1 -> svr1.db1.dbo.tbl2

while creating the new subscription i got the error

"subscription database is the same as the publisher database"

Pls advise me

Regards,

N.Prabhakaran

dbaker-620086 SSCrazy Points: 2336 More actions · Answer 4

the SSMS (GUI) wizard is over-protective in this case ...

you can use SSMS to replicate

svr1.db1.dbo.tbl1 -> svr1.db2.dbo.tbl1

and produce scripts. then edit the subscription script to change all "db2.dbo.tbl1" to "db1.dbo.tbl2" (and don't forget to put in the password)

you can successfully run that edited script in SSMS and re-run Snapshot job

means you'd get 2 subs and you can delete the older to leave just

svr1.db1.dbo.tbl1 -> svr1.db1.dbo.tbl2

and you can rescript if you'd ever need this again

Dick

PS I've just done it, so here's final script

-- Dropping the transactional subscriptions

use [DickPub]

go

exec sp_dropsubscription @publication = N'Prabhakaran', @article = N'all', @subscriber = N'all', @destination_db = N'all'

exec sp_droparticle @publication = N'Prabhakaran', @article = N'all', @force_invalidate_snapshot = 1

exec sp_droppublication @publication = N'Prabhakaran'

GO

-- Enabling the replication database

use master

exec sp_replicationdboption @dbname = N'DickPub', @optname = N'publish', @value = N'true'

GO

exec [DickPub].sys.sp_addlogreader_agent @job_login = N'MYDOMAIN\sqlrep', @job_password = 'MYPWD', @publisher_security_mode = 1

GO

-- Adding the transactional publication

exec sp_addpublication @publication = N'Prabhakaran', @description = N'Transactional publication of database ''DickPub'' from Publisher ''MYBOX''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @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'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

exec sp_addpublication_snapshot @publication = N'Prabhakaran', @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 = N'MYDOMAIN\sqlrep', @job_password = null, @publisher_security_mode = 1

exec sp_grant_publication_access @publication = N'Prabhakaran', @login = N'sa'

exec sp_grant_publication_access @publication = N'Prabhakaran', @login = N'distributor_admin'

GO

-- Adding the transactional articles

exec sp_addarticle @publication = N'Prabhakaran', @article = N't_Demo', @source_owner = N'dbo', @source_object = N't_Demo', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N't_Demo2', @destination_owner = N'dbo', @status = 8, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbot_Demo]', @del_cmd = N'CALL [sp_MSdel_dbot_Demo]', @upd_cmd = N'SCALL [sp_MSupd_dbot_Demo]'

GO

-- Adding the transactional subscriptions

exec sp_addsubscription @publication = N'Prabhakaran', @subscriber = N'MYBOX', @destination_db = N'DickPub', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N'Prabhakaran', @subscriber = N'MYBOX', @subscriber_db = N'DickPub', @job_login = N'MYDOMAIN\sqlrep', @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'

GO