July 24, 2007 at 12:15 pm
Dear Friends,
I have a transactional replication set up in my environment and it's running fine. Now I wanted to add a new article (Table) to the publication with out generating a new snapshot because the snapshot generates hugh file (Database size is 200GB, new table has 160 million records) and locks the tables momentarily. Instead I add the new table's schema on both publisher and subscriber and move data to subscriber using DTS/SSIS. After that add the article to the publication using sp_addarticle to the existing publication. But some reason this method is not working. If anyone knows how to do a table to the existing publication with generating snapshot file please let me know.
Thanks
BK
July 25, 2007 at 6:47 am
Probably where you are encountering issues is the @sync_type of sp_addsubscription. You want this to be 'replication support only' if the table and data are already identical on both servers and you don't need a snapshot. Here is an example for a table 't2' in database 'testdb1':
------------------------
--Connect to Server 1
-------------------------
sp_addpublication @publication='test_pub'
go
create table t2 (id int primary key identity, testval varchar(max))
go
insert t2 (testval)
select 'abc' union select 'def' union select 'ghi'
go
exec sp_addarticle @publication = N'test_pub',
@article = N'dbot2', @source_owner = N'dbo', @source_object = N't2',
@type = N'logbased',
@pre_creation_cmd = N'none', @schema_option = 0x0000000008035FDF,
@identityrangemanagementoption = N'manual', @destination_table = N't2', @destination_owner = N'dbo';
go
EXEC sp_addsubscription @publication = 'test_pub',
@article = N'dbot2',
@subscriber = 'amrdc01',
@destination_db = 'testdb1',
@sync_type = 'replication support only', -- do not init with snapshot agent, just create helper SPs for repl
@status = N'active';--set to active because no snapshot stuff needs to init it
go
------------------------
--Connect to Server 2
-------------------------
sp_addpublication @publication='test_pub'
go
create table t2 (id int primary key identity, testval varchar(max))
go
insert t2 (testval)
select 'abc' union select 'def' union select 'ghi'
go
--change identity range if needed for your scenario
DBCC CHECKIDENT('t2', reseed, 1000000);
go
exec sp_addarticle @publication = N'test_pub',
@article = N'dbot2', @source_owner = N'dbo', @source_object = N't2',
@type = N'logbased',
@pre_creation_cmd = N'none', @schema_option = 0x0000000008035FDF,
@identityrangemanagementoption = N'manual', @destination_table = N't2', @destination_owner = N'dbo';
go
EXEC sp_addsubscription @publication = 'test_pub',
@subscriber = 'amrdb02',
@article = N'dbot2',
@destination_db = 'testdb1',
@sync_type = 'replication support only', -- do not init with snapshot agent, just create helper SPs for repl
@status = N'active';--set to active because no snapshot stuff needs to init it
go
--add simple value to verify repl from server 2 to server 1
insert t2 (testval) values ('server 2 insert 1');
go
------------------------
--Connect to Server 1
-------------------------
--should see value 'server 2 insert 1'
select * from t2
go
insert t2 (testval) values ('server 1 insert 1');
go
------------------------
--Connect to Server 2
-------------------------
--should see value 'server 1 insert 1' (and other rows)
select * from t2
go
July 26, 2007 at 2:02 pm
I will try this and let you know how it goes.
Thank you very much for the reply
August 1, 2007 at 1:04 pm
HI Adrian,
Why we have to execute SP_ADDPUBLICATION stored procedure on server2 which is scriber. As far as I know sp_addpublication can be executed on only at publisher.
August 1, 2007 at 1:10 pm
Oops, I run bi-directional replication (both servers publish/subscribe to each other), so I assumed you did too even though you didn't say as such.
If you just go one direction you can skip the second step.
Thanks!
-Adrian
August 1, 2007 at 7:57 pm
Thank you for your reply.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply