Forcing SQL Express to synchronize

I have a situation where I have SQL Server 2005 as my Publisher/Distributor
and I have it setup to run Push Merge Replication. The subscriber is an
instance of SQL Server Express. Obviously SQL Server Express doesn't have
the means to force synchronization. I do have it setup at the moment to run
Continiously but this will definitely be resource intensive running Merge
Replication on a 1000 or so databases...so I would like to be able to set it
up as Run on Demand and force the synchronization from the application. I
have seen BOL but that RMO code is all in .Net, I am programming in
VB6...and furthermore it looks like I have to use a Pull Subscription in
order to code for RMO...is that correct?
How should I go about setting this up for optimum performance?
Thanks!
RSH
I would advise you to use a pull subscription and use the activeX controls
(you can use the SQL 2005 activeX control with VB6 - use set
test=CreateObject("SQLDistribution.90.SQLDistribut ion")
), or use WSM for that.
You want to use a pull as it has a lower impact on the publisher and you can
run it from the subscriber which is especially important if your publisher
is offline from time to time.
For WSM go to Start, All Programs, Accessories and select Synchronize. Click
on MS SQL Server and select Properties to select your publication and set
subscription properties.
Hilary Cotter
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
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:O5%230v1Q9FHA.3044@.TK2MSFTNGP10.phx.gbl...
>I have a situation where I have SQL Server 2005 as my Publisher/Distributor
>and I have it setup to run Push Merge Replication. The subscriber is an
>instance of SQL Server Express. Obviously SQL Server Express doesn't have
>the means to force synchronization. I do have it setup at the moment to
>run Continiously but this will definitely be resource intensive running
>Merge Replication on a 1000 or so databases...so I would like to be able to
>set it up as Run on Demand and force the synchronization from the
>application. I have seen BOL but that RMO code is all in .Net, I am
>programming in VB6...and furthermore it looks like I have to use a Pull
>Subscription in order to code for RMO...is that correct?
> How should I go about setting this up for optimum performance?
> Thanks!
> RSH
>
|||Hilary,
Thanks for your reply.
Question 1:
I set up a Pull subscription scenerio and on the SQLServer Express instance
when I run WSM I get the following error:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber."
"The process could not read the file 'C:\Program Files...' due to OS Error
3"
That directory does not exist from the "unc\" part of the path so I am
assuming that a snapshot hasn't been created. If I am correct how do I
generate a snapshot in SQL Express? Or is it created on the SQL Server
instance and copied over?
Question 2:
The VB Code below is my quick attempt at using the Active X control. The
code generates the following error: "The subscription to publication
'TestSub1' has expired or does not exist" which is not right...it does in
fact exist as illustrated in my screenshot...what am I missing here?
Option Explicit
' Add Reference Microsoft SQL Distribution Control 8.0
Private mobjDistr As SQLDISTXLib.SQLDistribution
Private Sub main()
Set mobjDistr = New SQLDISTXLib.SQLDistribution
On Error GoTo Errhandler
With mobjDistr
'Set up the Publisher
.Publisher = "VIRTUAL1"
.PublisherDatabase = "00010101"
.Publication = "TPTestSub1"
.PublisherSecurityMode = NT_AUTHENTICATION
'Set up the Subscriber.
.Subscriber = "PARENTVM\TPSVCCTR01"
.SubscriberDatabase = "00010101"
.SubscriberDatasourceType = SQL_SERVER
.SubscriberSecurityMode = DB_AUTHENTICATION
.SubscriberLogin = "V1"
.SubscriberPassword = "pw"
'Set up the Subscription.
.SubscriptionType = PULL
.SynchronizationType = AUTOMATIC
'Synchronize the data.
.Initialize
.Run
.Terminate
MsgBox "Complete"
End With
Exit Sub
Errhandler:
MsgBox Error
End Sub
Thanks a ton for your help!
RSH
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ecjk%23rR9FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I would advise you to use a pull subscription and use the activeX controls
>(you can use the SQL 2005 activeX control with VB6 - use set
>test=CreateObject("SQLDistribution.90.SQLDistribu tion")
> ), or use WSM for that.
> You want to use a pull as it has a lower impact on the publisher and you
> can run it from the subscriber which is especially important if your
> publisher is offline from time to time.
> For WSM go to Start, All Programs, Accessories and select Synchronize.
> Click on MS SQL Server and select Properties to select your publication
> and set subscription properties.
> --
> Hilary Cotter
> 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
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:O5%230v1Q9FHA.3044@.TK2MSFTNGP10.phx.gbl...
>
|||This almost always means a permissions problem. From what it looks like you
are connecting from your subscriber to c:\Program files\... on your
publisher, or at least it should be your publisher. Make sure when you
configured your distributor that your snapshot location was defined as a
share. If not you can go back and fix it by right clicking on your
replication folder and selecting distributor properties.Select the
Publishers node, and for your publisher select the browse button to the
right of your publisher and in the default snapshot folder make it a share.
For your second question, the reason you most frequently get this message is
because the snapshot has not being generated for this publication.
Hilary Cotter
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
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:Olcv3AT9FHA.636@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> Thanks for your reply.
> Question 1:
> I set up a Pull subscription scenerio and on the SQLServer Express
> instance when I run WSM I get the following error:
> "The Schema script 'Accrual_2.sch' could not be propogated to the
> Subscriber."
> "The process could not read the file 'C:\Program Files...' due to OS Error
> 3"
> That directory does not exist from the "unc\" part of the path so I am
> assuming that a snapshot hasn't been created. If I am correct how do I
> generate a snapshot in SQL Express? Or is it created on the SQL Server
> instance and copied over?
>
> Question 2:
>
> The VB Code below is my quick attempt at using the Active X control. The
> code generates the following error: "The subscription to publication
> 'TestSub1' has expired or does not exist" which is not right...it does in
> fact exist as illustrated in my screenshot...what am I missing here?
> Option Explicit
> ' Add Reference Microsoft SQL Distribution Control 8.0
> Private mobjDistr As SQLDISTXLib.SQLDistribution
> Private Sub main()
> Set mobjDistr = New SQLDISTXLib.SQLDistribution
> On Error GoTo Errhandler
> With mobjDistr
> 'Set up the Publisher
> .Publisher = "VIRTUAL1"
> .PublisherDatabase = "00010101"
> .Publication = "TPTestSub1"
> .PublisherSecurityMode = NT_AUTHENTICATION
> 'Set up the Subscriber.
> .Subscriber = "PARENTVM\TPSVCCTR01"
> .SubscriberDatabase = "00010101"
> .SubscriberDatasourceType = SQL_SERVER
> .SubscriberSecurityMode = DB_AUTHENTICATION
> .SubscriberLogin = "V1"
> .SubscriberPassword = "pw"
> 'Set up the Subscription.
> .SubscriptionType = PULL
> .SynchronizationType = AUTOMATIC
> 'Synchronize the data.
> .Initialize
> .Run
> .Terminate
> MsgBox "Complete"
> End With
> Exit Sub
> Errhandler:
> MsgBox Error
> End Sub
> Thanks a ton for your help!
> RSH
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ecjk%23rR9FHA.1184@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks.
How do I generate the snapshot?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:evENuiV9FHA.1188@.TK2MSFTNGP12.phx.gbl...
> This almost always means a permissions problem. From what it looks like
> you are connecting from your subscriber to c:\Program files\... on your
> publisher, or at least it should be your publisher. Make sure when you
> configured your distributor that your snapshot location was defined as a
> share. If not you can go back and fix it by right clicking on your
> replication folder and selecting distributor properties.Select the
> Publishers node, and for your publisher select the browse button to the
> right of your publisher and in the default snapshot folder make it a
> share.
> For your second question, the reason you most frequently get this message
> is because the snapshot has not being generated for this publication.
> --
> Hilary Cotter
> 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
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:Olcv3AT9FHA.636@.TK2MSFTNGP10.phx.gbl...
>
|||Hillary,
I set the Snapshot folder up as a Share as you described yesterday. I am no
longer getting the error about:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber." and I see the files being setup in the share.
I am still getting the pesky error: "The subscription to publication
'TestSub1' has expired or does not exist"
I assume that as part of the setup that somewhere the subscriber is aware of
where to look for the snapshot...I did not see any properties for this when
setting up the subscriber. Also something that might be a bit odd is that I
am generating the snapshot from the Publisher side...is this correct? Or
should I somehow generate it from the subscriber (if so how?) and if that is
the case how do I make the Publisher aware of the fact that it was created?
Thanks alot for your time!
Ron
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eZBocEb9FHA.500@.TK2MSFTNGP15.phx.gbl...
> Thanks.
> How do I generate the snapshot?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:evENuiV9FHA.1188@.TK2MSFTNGP12.phx.gbl...
>

Force a push from the subscriber

I'm not too sure I follow this
You have continuous merge replication set up and you want
it to go much faster? Changing it to a user-initiated,
non-continuous one won't speed it up. If I have
misinterpreted the question, and you just require user-
initiated replication, then you could use windows
synchronization manager and have pull subscriptions, or
program the merge activeX control to initiate the pull.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul,
No, I don't want it to go faster. I want the best of both worlds; to use
continuous merge replication (which polls about every 30 secs), AND
on-demand merge replication (triggered by the user on the client workstation
app).
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1eae01c51f07$ff630a00$a501280a@.phx.gbl...
> I'm not too sure I follow this
> You have continuous merge replication set up and you want
> it to go much faster? Changing it to a user-initiated,
> non-continuous one won't speed it up. If I have
> misinterpreted the question, and you just require user-
> initiated replication, then you could use windows
> synchronization manager and have pull subscriptions, or
> program the merge activeX control to initiate the pull.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I must be missing something
If there is continuous merge replication, what effect
would initiating the merge agent have? - it would give
the error message that another merge agent is running.
There again, what effect would you want it to have - if
it is running continuously, why (logically) would you
synchronize it on demand?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I know it sounds strange, but the main user of this app comes into the
office to synchronise his data with the corporate db, and then leaves. He is
impatient, and wants to initiate a merge himself. Because the merge is
transparent to him, there's no way for him to tell when the it has
completed.
I agree that he is *too* impatient, and if it's too hard, he'll just have to
live with it, but if we can give him the ability to initiate the merge
himself, why not?
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3a4f01c51fd3$65cafed0$a601280a@.phx.gbl...
>I must be missing something
> If there is continuous merge replication, what effect
> would initiating the merge agent have? - it would give
> the error message that another merge agent is running.
> There again, what effect would you want it to have - if
> it is running continuously, why (logically) would you
> synchronize it on demand?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Graham,
this sounds fine, but iun your first post you mentioned that ' continuous
push merge' was being used. Continuous and on-demand are mutually exclusive,
which is why I was/am confused. If it is not continuous and user-initiated
synchroniozation is what you need, then windows synchronization manager or
the activex controls coded in a GUI will do the trick.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, I understand now. I wasn't aware that they were mutually exclusive. I'll
leave it in continuous mode (he'll just have to live with it).
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uhTFaOKIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Graham,
> this sounds fine, but iun your first post you mentioned that ' continuous
> push merge' was being used. Continuous and on-demand are mutually
> exclusive, which is why I was/am confused. If it is not continuous and
> user-initiated synchroniozation is what you need, then windows
> synchronization manager or the activex controls coded in a GUI will do the
> trick.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Oh, forgive me. I rudely forgot to say "thanks".
Thanks Paul.
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uhTFaOKIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Graham,
> this sounds fine, but iun your first post you mentioned that ' continuous
> push merge' was being used. Continuous and on-demand are mutually
> exclusive, which is why I was/am confused. If it is not continuous and
> user-initiated synchroniozation is what you need, then windows
> synchronization manager or the activex controls coded in a GUI will do the
> trick.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

FOR UPDATE Trigger on Subscriber Table Won't Fire

Jim,
it is possible that your publisher update is being
replicated as an delete/insert pair. You can run profiler
to check if this is the case. If so, there is a trace flag
to keep singleton updates replicated as updates. See this
article: http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/q3
02/3/41.ASP&NoWebContent=1
HTH,
Paul Ibison
Thank you! It is indeed being sent as a delete/insert pair.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1d51501c45383$f7401610$a001280a@.phx.gbl...
> Jim,
> it is possible that your publisher update is being
> replicated as an delete/insert pair. You can run profiler
> to check if this is the case. If so, there is a trace flag
> to keep singleton updates replicated as updates. See this
> article: http://support.microsoft.com/default.aspx?
> scid=http://support.microsoft.com:80/support/kb/articles/q3
> 02/3/41.ASP&NoWebContent=1
> HTH,
> Paul Ibison
>