Setup Replication with MSDE

[Alexander Baumgart]

Hello,

i plan to setup merge replication between 2x msde instance (for testing
purpose on the same pc too). At the moment i didnt have any sql , like
dev,sd,ee, so i only can use Access and sp to setup. Here what i did as
yet:

1. Setup a DB on the distributor/publisher msde instance and fill it
with data
2. use master
go

sp_adddistributor @distributor = @@servername,
@heartbeat_interval = 2
go
3. use master
go

sp_adddistributiondb @database = N'distrib'
,@data_file = N'distrib.mdf'
,@log_file = N'distrib.ldf'
go
4. use master
go

sp_adddistpublisher @publisher = @@servername,
@distribution_db = N'distrib',
@working_directory = N'D:\Program
Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData',
@trusted = N'false',
@thirdparty_flag = 0
go
5. use master
go

sp_replicationdboption @dbname = N'ttpdb',
@optname = N'merge publish',
@value = N'true'
go
6. use ttpdb
go

sp_addsubscriber @subscriber = N'my_pc\replicationdb',
@description = N'Redundanz'
go
7. use ttpdb
go

sp_addmergepublication @publication = N'TTPMerge',
@description = N'Merge Publication of TTP.',
@retention = 2,
@allow_push = N'true',
@allow_pull = N'false',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@centralized_conflicts = N'true',
@dynamic_filters = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@conflict_retention = 2,
@keep_partition_changes = N'false',
@allow_subscription_copy = N'false',
@allow_synctoalternate = N'false',
@add_to_active_directory = N'false',
@max_concurrent_merge = 5,
@max_concurrent_dynamic_snapshots = 2
go
8. use ttpdb
go

sp_addmergesubscription @publication = N'TTPMerge',
@subscriber = N'my_pc\replicationdb',
@subscriber_db = N'ttpdb',
@subscription_type = N push',
@description = N 'TTPMerge Subscription',
@enabled_for_syncmgr = N'true',
@merge_job_name = N 'MergeJob'
go
9. use ttpdb
go

sp_addpublication_snapshot @publication=N'TTPMerge',
@snapshot_job_name = N'MergeSnapshot'
go

The goal is a pure push merge replication with 5min sync intervall.
The snapshot_folder get created under D:\Program
Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData\unc\my_pc$SUDSQLDB_ttpdb_TTPMerg
e, but no snapshot get created ;( I also didnt see any error, i checked
jobs and all succeeds.
I check BOL, whats realy nice, and there i reat i need to startup the
snapshot agent, but didnt i do that from t-sql , only per EE-manager or
SQL-DMO. Also sp_addpublication_snapshot is only used in snapshot and
transaction replication, but under "create publication and define
article (translated by me, i only have german BOL) it get used for
merge replication too. Any other documentation on replication with
t-sql ?
Alot praise Hilarys book, are there a chapter about setup replication
with t-sql only ? I also have heard that dev. edition only cost 49$
bugs, are there any limitation or tools missing ?

Thx Baumgart

[Paul Ibison]

Alexander,
I have a script on http://www.replicationanswers.com/Script4.asp which set's
up merge replication.
You can use sp_start_job to initiate an agent.
Developer Edition is fine to use but you can't use it in production
environments.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[Alexander Baumgart]

Paul Ibison wrote:

Thx for the quick help. Your script only cover the subscriber site of
the replication, thats true ? Next u use PULL subscribtion, i used
PUSH. The problem is PULL has what i did aspect from PUSH to, u set
something on subscriber site (sp_addpullsubcribtion) so the
subscriber notice its part of a publication and u start a agent that
controll the publication (sp_addmergepullsubscription_agent).
Both are missing on PUSH subscription, thats correct ?
U used a linkserver, so u could setup all from the subscriber. One
problem i have notice, in MERGE publication (SNAPSHOT i didnt checked,
only the first procedure sp_addsubscriber, what for better
understanding u should exclude from SNAPSHOT part, because its need for
both or ? ) is
sp_addmergesubscription on the publisher, what didnt seem to work
for me, because it use master as DB and master isn't published. Didnt
need a "use testsubscription1 , go" in front of so the right DB
get used ?
My problem continue to exist.
1.) why i didnt see any startsnaphot in the snapshotfolder ? Did i need
to fire up sp_addpublication_snapshot , for the startsnapshot ?
2.) are there any way to see whats going on without gui tools of real
sql server ?
3.) how i start a PUSH Merge replication ? Did i need to start it ?

Thx for all help
Baumgart

[Paul Ibison]

Alexander,
if you need the script for a PUSH, then it's even easier. Create the
publication and subscription in Enterprise Manager on a test server then get
Enterprise Manager to script out the publication. Change the servernames
then you should have a script you can run. The schedules will cause the jobs
to start, or you can use sp_start_job to kick things off.

[Alexander Baumgart]

Paul Ibison wrote:

Like i told , i ONLY have MSDE at the moment. The main problem at the
moment ist the kick off, how i do that ?? Here what i did in common

1. sp_adddistributor
2. sp_adddistributiondb
3. sp_adddistpublisher
4. sp_replicationdboption
5. sp_addmergepublication
a) sp_addpublication_snapshot
b) sql_sp_grant_publication_access
6. sp_addsubscriber
7. sp_addmergesubscription

And now ? How i kick off the replication ? Perhaps u could create a
common PUSH script with EE and post it so i can see what get done.
I found some sample for transaction or merge + sql-dmo and allways
something like this is in the beginning

*
Type of agent:
1 = Snapshot Agent <-- @profile_id = 1
2 = Log Reader Agent <-- @profile_id = 2
3 = Distribution Agent <-- @profile_id = 4
4 = Merge Agent <-- @profile_id = 6
9 = Queue Reader Agent <-- @profile_id = 11
*/

use master
go

sp_MSupdate_agenttype_default @profile_id = 1
go
sp_MSupdate_agenttype_default @profile_id = 2
go
sp_MSupdate_agenttype_default @profile_id = 4
go
sp_MSupdate_agenttype_default @profile_id = 6
go
sp_MSupdate_agenttype_default @profile_id = 11
go

BOL didnt have any info to MSupdate_agenttype_default ;(
seems a hidden SP ;(. Also a major problem is, where could i check
for errors ? All sp return with 0 , but whats if i user login isnt
valid for instance ? Where the error msg get saved ? Are 5a) 5b) are
needed ?

Thx for help

[Alexander Baumgart]

Paul Ibison wrote:

Hello,

me again. I found a way to startup the snapshot agent manually.

@echo off
set _pl="my_pc\sudsqldb"
set _pldb="ttpdb"
set _d="my_pc\sudsqldb"
set _dlogin="sa"
set _dsec="1"
set _pc="TTPMerge"
REM 0 = NT 1 =SQL
set _reptype="2"
REM 1 = SnapTrans 2 = Merge
set _log=".\snapshot_agent.log"
set _verbose=2
set _logtout=5
set _querytout=5

del /q %_log%
"C:\Program Files\Microsoft SQL
Server\80\COM\snapshot.exe" -Publisher %_pl%
-Publisherdb %_pldb% -Publication %_pc%
-ReplicationType %_reptype% -Distributor %_d%
-DistributorLogin %_dlogin% -DistributorPassword %1
-DistributorSecurityMode %_dsec% -Output %_log%
-OutputVerboseLevel %_verbose% -LoginTimeout
%_logintout% -QueryTimeout %_querytout%

This give me :

Microsoft SQL Server-Snapshot-Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation

Verbindung mit Distributor 'my_pc\sudsqldb'
Connecting to Publisher 'my_pc\sudsqldb.ttpdb'

Server:
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[11.05.2005 16:47:08]my_pc\sudsqldb.ttpdb:
sp_MSgetversion
Initializing the publication 'TTPMerge'

*** [Publication:'TTPMerge'] Publication view generation
time: 94 (ms) ***

*** [Publication:'TTPMerge'] Make generation time: 94
(ms) ***

Keine neuen Artikel in Publikation 'TTPMerge' gefunden.
(No new article in publication 'TTPMerge' [translated by me])

Disconnecting from Publisher 'my_pc\sudsqldb'

Mean this i need always setup a article ? I thought a publication sync
the whole db , and article are only for special data , like 1 table or
filter (h/v).

Thx Baumgart

[Paul Ibison]

Alexander,
if you only have MSDE as the publisher and subscriber and have no GUI at
all, then I think you'll find the whole development process slow and
difficult - I'd definitely consider purchasing the developer edition (£40)
to help generate the scripts or the free evaluation edition. As long as
you're not using this as a GUI to your MSDE then this is fine AFAIK.
Anyway, here's a script to create a publication and a subscription running
at 5min intervals. The snapshot agent runs once a week on sundays at 225200
and the parameters can be tweaked to suit your timescale. Starting the job
in code involves sp_start_job, and you'll have to use the name in the script
below ('C11116\LOCAL-testRep-testRep1-2').
Rgds,
Paul Ibison

-- Enabling the replication database
use master
GO

exec sp_replicationdboption @dbname = N'testRep', @optname = N'merge

publish', @value = N'true'

GO

use [testRep]
GO

-- Adding the merge publication
exec sp_addmergepublication @publication = N'testRep1', @description =
N'Merge publication of testRep database from Publisher C11116\LOCAL.',
@retention = 14, @sync_mode = N'native', @allow_push = N'true', @allow_pull
= N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false',

@centralized_conflicts = N'true', @dynamic_filters = N'false',
@snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false',

@ftp_port = 21, @ftp_login = N'anonymous', @conflict_retention = 14,

@keep_partition_changes = N'false', @allow_subscription_copy = N'false',
@allow_synctoalternate = N'false', @add_to_active_directory = N'false',

@max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @publication = N'testRep1',@frequency_type =
8, @frequency_interval = 64, @frequency_relative_interval = 0,
@frequency_recurrence_factor = 1, @frequency_subday = 1,
@frequency_subday_interval = 0, @active_start_date = 0, @active_end_date =
0, @active_start_time_of_day = 225200, @active_end_time_of_day = 0,
@snapshot_job_name = N'C11116\LOCAL-testRep-testRep1-2'
GO

exec sp_grant_publication_access @publication = N'testRep1', @login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'testRep1', @login =
N'distributor_admin'
GO
exec sp_grant_publication_access @publication = N'testRep1', @login = N'sa'
GO

-- Adding the merge articles
exec sp_addmergearticle @publication = N'testRep1', @article = N'vvv',
@source_owner = N'dbo', @source_object = N'vvv', @type = N'table',
@description = null, @column_tracking = N'true', @pre_creation_cmd =
N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1,
@article_resolver = null, @subset_filterclause = null, @vertical_partition =
N'false', @destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO

-- Adding the merge subscription
exec sp_addmergesubscription @publication = N'testRep1', @subscriber =
N'C11116\LOCAL', @subscriber_db = N'testrepSubs', @subscription_type =
N'push', @subscriber_type = N'local', @subscription_priority = 0.000000,
@sync_type = N'automatic', @frequency_type = 4, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0,
@frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date =
0, @active_end_date = 0, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @enabled_for_syncmgr = N'false',
@offloadagent = 0, @use_interactive_resolver = N'false'
GO

[Alexander Baumgart]

Paul Ibison wrote:

> Alexander,
....

> = 0, @use_interactive_resolver = N'false' GO

SPecial thx,

at the moment our dealer is on vacation, but right development is very
slow ;( , but interesting , to work at a low level.

As i mention in a other thread, there seem to be a problem with the
startupsnapshot. Now i have the affirmation. I started the merge agent
manual and get :

...
[11.05.2005 16:59:09]my_pc\sudsqldb.ttpdb: {call
sp_MSgetversion }
Der Anfangssnapshot für Publikation 'TTPMerge' ist noch nicht
verfügbar.

(the startup snampshot for 'TTPMerge' publication isnt available
[translated by me])

Category:NULL
Source: Mergereplikationsprovider
Number: -2147201021
Message: Der Anfangssnapshot für Publikation 'TTPMerge' ist noch
nicht verfügbar.
...

i will check your script tomorrow , special thx to u for support on
this deep down below lvl

[Paul Ibison]

Hopefully my simultaneous reply answers these questions, but if not, please
let me know.
Rgds,
Paul Ibison

"Alexander Baumgart" <al.ba...@gmx.de> wrote in message
news:d5t6a4$auv$1...@mail1.sbs.de...

[Paul Ibison]

This message means that the snapshot agent hasn't been run or hasn't
finished or has finished and has errored. Check the snapshot agent history
to see if this explains it, also check that the snapshot agent's job is
owned by sa.
Rgds,
Paul Ibison

"Alexander Baumgart" <al.ba...@gmx.de> wrote in message

news:d5t7bm$rh1$1...@mail1.sbs.de...

[Alexander Baumgart]

Paul Ibison wrote:

> Alexander,
> if you only have MSDE as the publisher and subscriber and have no
GUI
> at all, then I think you'll find the whole development process
slow
> and difficult - I'd definitely consider purchasing the developer
> edition (£40) to help generate the scripts or the free
evaluation
> edition. As long as you're not using this as a GUI to your MSDE
then
> this is fine AFAIK. Anyway, here's a script to create a
publication
> and a subscription running at 5min intervals. The snapshot agent
runs
> once a week on sundays at 225200 and the parameters can be
tweaked to
> suit your timescale. Starting the job in code involves
> sp_start_job, and you'll have to use the name in the
script
> below ('C11116\LOCAL-testRep-testRep1-2'). Rgds,
> Paul Ibison
>
> -- Enabling the replication database

..

Hello,

me again. I couldn wait till tomorrow and adapt the
sp_addmergearticle part to my skript and now it works ;) its
only missed the article part.
So i will study BOL about the main of replication again, there a lot
more to learn, but now i have a base to start from.

Special thx to u Paul