Hello:

I'm new to replication and need you help with a scenario.
1. We have a central SQL 2000 database and multiple offline MSD
databases. Data will be captured in MSDE while they are offline an
will be synchronized with the central database when the MSDE machine
get online.

2. The DB admin at the server will setup publications but has n
information about the subscribers. The subscriptions will be create
when the application is installed on the offline machines. I'm workin
on this by using SQL-DMO but am not sure if it will work in an interne
scenario. The server can be accessed in the code by using IP but wha
about the MSDE machines?

3. Again the databases are synchronized when user clicks
"Synchronize" button. I can specify the server by using IP but how do
refer the MSDE client machines?

Thanks in advance.

Regards,
Niles


-
nileshdia
-----------------------------------------------------------------------
Posted via http://www.mcse.m
-----------------------------------------------------------------------
View this thread: http://www.mcse.ms/message496906.htm

I'd use merge replication with a central publisher and each MSDE database is an anonymous pull subscriber. Synchronization can be programatic using the activex controls (optionally installed with samples on a full SQL Server install) or more simply using windows synchronization manager. To be secure I'd use this replication topology over a VPN. When you come to implementing it, the main potential stumbling points are security and sql registration. Both are explained in detail in this article: http://support.microsoft.com/?id=321822. I set up non-trusted domain sql security and had a couple of extra issues so if you go down that route then let us know
HTH

I personally would not use the ActiveX controls for this. They are more
simpler to program but more difficult to administer. For instance right
click on your merge agent after you have created your pull/push subscription
and try to start it. You can't as the start and stop agent menu options are
grayed out.

You can't create push subscriptions with them either (I realize you are not
doing this).

Here is some SQL DMO to do what you are trying to do (this is for
transactional).
Option explicit
Const SQLDMOPubAttrib_AllowAnonymous = 4

Const SQLDMOPubAttrib_AllowPull = 2

Const SQLDMOPubAttrib_ImmediateSync = 16

Const SQLDMOPubAttrib_IndependentAgent = 32

Const SQLDMOPubAttrib_InternetEnabled = 8

Const SQLDMOCreationScript_PrimaryObject =1

Const SQLDMOCreationScript_DRI_Defaults = 2048

Const SQLDMOCreationScript_DRI_ForeignKeys = 512

Const SQLDMOCreationScript_ClusteredIndexes = 16

Const SQLDMOCreationScript_NonClusteredIndexes = 64

Const SQLDMOCreationScript_DRI_Checks = 1024

Const SQLDMOCreationScript_PKUKAsConstraints = 32768

Const SQLDMOCreationScript_UDDTsToBaseTypes = 32

Const SQLDMOTranSubscriber_Default = 0

Const SQLDMOSubscription_Pull = 1

Const SQLDMOSubscription_Anonymous = 2

Const SQLDMOReplSecurity_Normal = 0

Const SQLDMOReplSecurity_Integrated = 1

Const SQLDMOSubscription_All = 3

Dim objServer, objReplication, objSubscriber, _ objReplicationDatabases,
objReplicationDatabase, _

objTransPublications,objTransPublication,objTransArticle, objTransArticles,
objTransSusbcriptions,_ objTransPullSubscription,objTransPullSubscriptions,_

objReplicationSecurity, objJobServer, objJobs, objJob,_ property

set objServer = CreateObject("SQLDMO.SQLServer")

objServer.Connect "hilary2kp", "sa","se1cure#"

set objReplication=objServer.Replication

set objReplicationDatabases=objReplication._

ReplicationDatabases

set objReplicationDatabase=objReplicationDatabases("Pubs")

set objTransPublications=objReplicationDatabase._

TransPublications

set objTransPublication=_

CreateObject("SQLDMO.TransPublication2")

set objTransArticle=CreateObject("SQLDMO.TransArticle2")

with objTransArticle

.Name="authors"

.SourceObjectName="authors"

.SourceObjectOwner="dbo"

.CreationScriptOptions=_

SQLDMOCreationScript_PrimaryObject+_

SQLDMOCreationScript_DRI_Defaults + _

SQLDMOCreationScript_DRI_ForeignKeys + _

SQLDMOCreationScript_ClusteredIndexes + _

SQLDMOCreationScript_DRI_Checks +_

SQLDMOCreationScript_PKUKAsConstraints +_

SQLDMOCreationScript_UDDTsToBaseTypes +_

SQLDMOCreationScript_NonClusteredIndexes

end with

with objTransPublication

.Name="test"

.AltSnapShotFolder="C:\inetpub\ftproot"

.Enabled=TRUE

.FTPAddress="hilary2kp"

.FTPLogin="Anonymous"

.FTPSubdirectory="/ftp"

.PublicationAttributes=SQLDMOPubAttrib_AllowAnonymous+_

SQLDMOPubAttrib_AllowPull+_

SQLDMOPubAttrib_ImmediateSync+_

SQLDMOPubAttrib_IndependentAgent+_

SQLDMOPubAttrib_InternetEnabled

.RetentionPeriod=14

end with

objTransPublications.Add objTransPublication

objTransPublication.TransArticles.Add objTransArticle

'Starting the snapshot job

set objJobServer=objServer.JobServer

set objJobs=objJobServer.Jobs

for each objJob in objJobs

if objJob.JobID=objTranspublication.SnapshotJobID then

objJob.Start

end if

next

set objJob=Nothing

set objJobs=Nothing

set objJobServer=Nothing

set objTransArticle=Nothing

set objTransPublications=Nothing

set objTransPublication=Nothing

set objReplication=Nothing

objServer.Disconnect

objServer.Connect "Hilary2kp\test","sa","se1cure#"

set objReplication=objServer.Replication

set objReplicationDatabases=objReplication._

ReplicationDatabases

set objReplicationDatabase=objReplicationDatabases("sub")

set objTransPullSubscription=_

CreateObject("SQLDMO.TransPullSubscription2")

set objReplicationSecurity=objTransPullSubscription._

DistributorSecurity

objReplicationSecurity.SecurityMode=_

SQLDMOReplSecurity_Normal

objReplicationSecurity.StandardLogin="sa"

objReplicationSecurity.StandardPassword="se1cure#"

with objTransPullSubscription

.FTPAddress="hilary2kp"

.FTPLogin="Anonymous"

.Publisher= "hilary2kp"

.Distributor="hilary2kp"

.DistributorSecurity.SecurityMode=_

SQLDMOReplSecurity_Normal

.DistributorSecurity.StandardLogin="sa"

.DistributorSecurity.StandardPassword="se1cure#"

.PublicationDB="pubs"

.Publication="test"

.PublisherSecurity.SecurityMode=_

SQLDMOReplSecurity_Normal

.PublisherSecurity.StandardLogin="sa"

.PublisherSecurity.StandardPassword="se1cure#"

.SubscriberType = SQLDMOTranSubscriber_Default

.SubscriptionType = SQLDMOSubscription_Anonymous

.PublicationAttributes=SQLDMOPubAttrib_AllowAnonymous+_

SQLDMOPubAttrib_AllowPull+_

SQLDMOPubAttrib_ImmediateSync+_

SQLDMOPubAttrib_IndependentAgent+_

SQLDMOPubAttrib_InternetEnabled

.SubscriberSecurityMode=SQLDMOReplSecurity_Normal

.SubscriberLogin="sa"

.SubscriberPassword="se1cure#"

.UseFTP=True

end with

set objTransPullSubscriptions=objReplicationDatabase._

TransPullSubscriptions

objTransPullSubscriptions.Add objTransPullSubscription

set objTransPullSubscription=Nothing

set objTransPullSubscriptions=Nothing

set objReplicationDatabases=Nothing

set objReplication=Nothing

set objServer=Nothing


[quoted text, click to view]
is an anonymous pull subscriber. Synchronization can be programatic using
the activex controls (optionally installed with samples on a full SQL Server
install) or more simply using windows synchronization manager. To be secure
I'd use this replication topology over a VPN. When you come to implementing
it, the main potential stumbling points are security and sql registration.
Both are explained in detail in this article:
http://support.microsoft.com/?id=321822. I set up non-trusted domain sql
security and had a couple of extra issues so if you go down that route then
let us know.
[quoted text, click to view]

Nilesh

Were you able to accomplish this replication over internet using MSDE
I have a similar requirement and wondering if I should go with this approach of MSDE & SQL replication

Can you share your do's and dont's with me

Thank