MS SQL Server - Replicationtables with diff schema

Author

Replicationtables with diff schema

mecn

2006-10-24, 6:28 pm

Hi,
Thanks for all the reponses yesterday regarding replication tables of diff
schema.

I understand now. Now we have 3 tables out of 56 in diff schema.
I need to replicate the whole databse. What should I do? replication all
tables one by one?
Or i could do replication database and 3 addtional diff schema tables?

Thanks,

mecn

2006-10-24, 6:28 pm

To replicate indexed view require sql2k enterprise edtion. We have standard
edtionin prod.
any alternative way that I could replicate tables with diff schemas????

Thanks

"mecn" <mecn2002@yahoo.com> wrote in message
news:%23DDNzYN2GHA.476@TK2MSFTNGP06.phx.gbl...
> Hi,
> Thanks for all the reponses yesterday regarding replication tables of diff
> schema.
>
> I understand now. Now we have 3 tables out of 56 in diff schema.
> I need to replicate the whole databse. What should I do? replication all
> tables one by one?
> Or i could do replication database and 3 addtional diff schema tables?
>
> Thanks,
>

mecn

2006-10-24, 6:28 pm

Hilary Cotter

2006-10-24, 6:28 pm

no you don't. Here is an example.

CREATE Database Resumes
GO

Use Resumes

GO

CREATE TABLE Resumes (PK int identity constraint ResumesPK primary key,
Skills VARCHAR(100), Location char(2))

GO

INSERT Resumes (Skills, Location) VALUES ('SQL','NE')

INSERT Resumes (Skills, Location) VALUES ('SQL','NW')

INSERT Resumes (Skills, Location) VALUES ('NT','NE')

INSERT Resumes (Skills, Location) VALUES ('NT','NW')

GO

CREATE fulltext catalog cat1

GO

CREATE VIEW myview WITH SCHEMABINDING AS

SELECT PK, Skills, Location FROM dbo.Resumes

WHERE

Location='NE'

GO

-- create the index on the view to be used as fulltext key index

CREATE UNIQUE CLUSTERED INDEX idx ON myview(PK)

GO

CREATE fulltext INDEX ON myview(Skills) KEY INDEX idx ON cat1

GO

WHILE fulltextcatalogprope
rty('cat1','populate
status') <> 0

BEGIN

WAITFOR DELAY '00:00:01'

END

GO

-- note only resulsts are returned where skill=sql and location=ne

SELECT * FROM myview WHERE CONTAINS(Skills,'SQL
')

GO

Create database ResumeSubs

GO

use resumes

indexed view logbased

-- Enabling the replication database

use master

exec sp_replicationdbopti
on @dbname = N'Resumes', @optname = N'publish',
@value = N'true'

GO

exec [Resumes].sys. sp_addlogreader_agen
t @job_login = null, @job_password =
null, @publisher_security_
mode = 1

GO

exec [Resumes].sys.sp_addqreader_agent @job_login = null, @job_password =
null, @frompublisher = 1

GO

-- Adding the transactional publication

use [Resumes]

exec sp_addpublication @publication = N'test', @description =
N'Transactional publication of database ''Resumes'' from Publisher .',
@sync_method = N'concurrent', @retention = 0, @allow_push = N'true',
@allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_interne
t =
N'false', @snapshot_in_default
folder = N'true', @compress_snapshot =
N'false', @ftp_port = 21, @ftp_login = N'anonymous',
@allow_subscription_
copy = N'false', @add_to_active_direc
tory = N'false',
@repl_freq = N'continuous', @status = N'active', @independent_agent =
N'true', @immediate_sync = N'true', @allow_sync_tran = N'false',
@autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts =
N'false', @replicate_ddl = 1, @allow_initialize_fr
om_backup = N'false',
@enabled_for_p2p = N'false', @enabled_for_het_sub
= N'false'

GO

-- Enabling the replication database

use master

exec sp_replicationdbopti
on @dbname = N'Resumes', @optname = N'publish',
@value = N'true'

GO

exec [Resumes].sys. sp_addlogreader_agen
t @job_login = null, @job_password =
null, @publisher_security_
mode = 1

GO

exec [Resumes].sys.sp_addqreader_agent @job_login = null, @job_password =
null, @frompublisher = 1

GO

-- Adding the transactional publication

use [Resumes]

exec sp_addpublication @publication = N'test', @description =
N'Transactional publication of database ''Resumes'' from Publisher',
@sync_method = N'concurrent', @retention = 0, @allow_push = N'true',
@allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_interne
t =
N'false', @snapshot_in_default
folder = N'true', @compress_snapshot =
N'false', @ftp_port = 21, @ftp_login = N'anonymous',
@allow_subscription_
copy = N'false', @add_to_active_direc
tory = N'false',
@repl_freq = N'continuous', @status = N'active', @independent_agent =
N'true', @immediate_sync = N'true', @allow_sync_tran = N'false',
@autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts =
N'false', @replicate_ddl = 1, @allow_initialize_fr
om_backup = N'false',
@enabled_for_p2p = N'false', @enabled_for_het_sub
= N'false'

GO

exec sp_addpublication_sn
apshot @publication = N'test', @frequency_type = 1,
@frequency_interval = 0, @frequency_relative_
interval = 0,
@frequency_recurrenc
e_factor = 0, @frequency_subday = 0,
@frequency_subday_in
terval = 0, @active_start_time_o
f_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'test', @login = N'sa'

GO

exec sp_grant_publication
_access @publication = N'test', @login = N'NT
AUTHORITY\SYSTEM'

GO

exec sp_grant_publication
_access @publication = N'test', @login =
N'BUILTIN\Administra
tors'

GO

exec sp_grant_publication
_access @publication = N'test', @login = N'rndev'

GO

exec sp_grant_publicatins
bhy+Cevfon_access @publication = N'test', @login =
N'distributor_admin'

GO

-- Adding the transactional articles

use [Resumes]

exec sp_addarticle @publication = N'test', @article = N'myview',

@source_owner = N'dbo',

@source_object = N'myview', @type = N'indexed view logbased',

@description = N'', @creation_script = N'',

@pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001,

@destination_table = N'myview',

@destination_owner = N'dbo', @status = 16

GO

exec sp_addsubscription @publication = N'test', @subscriber = @@ServerName,
@destination_db = N'ResumeSubs', @subscription_type = N'Push', @sync_type =
N'automatic', @article = N'all', @update_mode = N'read only',
@subscriber_type = 0
exec sp_addpushsubscripti
on_agent @publication = N'test', @subscriber =
@@ServerName, @subscriber_db = N'ResumeSubs', @job_login = null,
@job_password = null, @subscriber_security
_mode = 1, @frequency_type = 64,
@frequency_interval = 1, @frequency_relative_
interval = 1,
@frequency_recurrenc
e_factor = 0, @frequency_subday = 4,
@frequency_subday_in
terval = 5, @active_start_time_o
f_day = 0,
@active_end_time_of_
day = 235959, @active_start_date = 0, @active_end_date =
0, @dts_package_locatio
n = N'Distributor'
GO

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

"mecn" <mecn2002@yahoo.com> wrote in message
news:u4O$XhN2GHA.324@TK2MSFTNGP05.phx.gbl...
> To replicate indexed view require sql2k enterprise edtion. We have
> standard
> edtionin prod.
> any alternative way that I could replicate tables with diff schemas????
>
> Thanks
>
> "mecn" <mecn2002@yahoo.com> wrote in message
> news:%23DDNzYN2GHA.476@TK2MSFTNGP06.phx.gbl...
>
>

Sponsored Links