I need to pull the schema only with no data, then push the data back without
deletes on the publisher, then delete the client data. Next time these
deletes would not be pushed up. Only new data.
SQL Server 2005 June CTP and SQL Mobile 2005
"msmith" wrote:
> I need to pull the schema only with no data, then push the data back without
> deletes on the publisher, then delete the client data. Next time these
> deletes would not be pushed up. Only new data.
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Monday, March 26, 2012
Push or Pull
I've always had a hard time getting my head around Push and Pull
Subscriptions.
Ive read that it really doesnt have a huge impact on a box, and that what
really matters is where the Distributor is. That Push or Pull is more for
Administrative type of stuff. Just not sure about this.
I just started at a company that uses quite a bit of replication. So far Ive
come up with two Publishers and propably or 6 or 8 Subscribers. The
Distributors are both Local and the Subscriptions are all Pull. It is all
Transactional Replication, some of it continuos, some every 15 minutes.
So my questions are:
Would I be better off to use a dedicated Remote Distributor? One of my main
objectives here is to speed up these boxes.
If I did use a Remote Distributor, would it matter if I went to Push
Subscriptions for centralized Administration? Or would that defeat my goal
of speeding up the Publisher?
I don't like remote distributors and hesitate before using them. If your
remote distributor goes down you can end up with ballooning tlogs on your
publication databases and you can have problems getting your log reader
agent working again.
If you have high throughput and are experiencing locking you might want to
look at them.
Pull subscriptions do lessen the load on the publisher. They are the best
solution when you have a lot of subscribers, are replicating across the
internet or when your subscribers are not well connected or not always
connected, you can live with no central point of administration (other than
replication monitor group).
the rest of the answers are inline.
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
"ChrisR" <noemail@.bla.com> wrote in message
news:%23ydW$eHNFHA.3668@.TK2MSFTNGP14.phx.gbl...
> I've always had a hard time getting my head around Push and Pull
> Subscriptions.
> Ive read that it really doesnt have a huge impact on a box, and that what
> really matters is where the Distributor is. That Push or Pull is more for
> Administrative type of stuff. Just not sure about this.
> I just started at a company that uses quite a bit of replication. So far
Ive
> come up with two Publishers and propably or 6 or 8 Subscribers. The
> Distributors are both Local and the Subscriptions are all Pull. It is all
> Transactional Replication, some of it continuos, some every 15 minutes.
> So my questions are:
> Would I be better off to use a dedicated Remote Distributor? One of my
main
> objectives here is to speed up these boxes.
I would only use this if you are experiencing considerable locking in your
distribution database.
> If I did use a Remote Distributor, would it matter if I went to Push
> Subscriptions for centralized Administration?
No, not for a few always connected subscribers.
Or would that defeat my goal
> of speeding up the Publisher?
Stop your log reader and distribution agent. Do you get a significant
increase in performance. If so, migrate; if not (and suspect not) stay with
the local distributor.
>
Subscriptions.
Ive read that it really doesnt have a huge impact on a box, and that what
really matters is where the Distributor is. That Push or Pull is more for
Administrative type of stuff. Just not sure about this.
I just started at a company that uses quite a bit of replication. So far Ive
come up with two Publishers and propably or 6 or 8 Subscribers. The
Distributors are both Local and the Subscriptions are all Pull. It is all
Transactional Replication, some of it continuos, some every 15 minutes.
So my questions are:
Would I be better off to use a dedicated Remote Distributor? One of my main
objectives here is to speed up these boxes.
If I did use a Remote Distributor, would it matter if I went to Push
Subscriptions for centralized Administration? Or would that defeat my goal
of speeding up the Publisher?
I don't like remote distributors and hesitate before using them. If your
remote distributor goes down you can end up with ballooning tlogs on your
publication databases and you can have problems getting your log reader
agent working again.
If you have high throughput and are experiencing locking you might want to
look at them.
Pull subscriptions do lessen the load on the publisher. They are the best
solution when you have a lot of subscribers, are replicating across the
internet or when your subscribers are not well connected or not always
connected, you can live with no central point of administration (other than
replication monitor group).
the rest of the answers are inline.
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
"ChrisR" <noemail@.bla.com> wrote in message
news:%23ydW$eHNFHA.3668@.TK2MSFTNGP14.phx.gbl...
> I've always had a hard time getting my head around Push and Pull
> Subscriptions.
> Ive read that it really doesnt have a huge impact on a box, and that what
> really matters is where the Distributor is. That Push or Pull is more for
> Administrative type of stuff. Just not sure about this.
> I just started at a company that uses quite a bit of replication. So far
Ive
> come up with two Publishers and propably or 6 or 8 Subscribers. The
> Distributors are both Local and the Subscriptions are all Pull. It is all
> Transactional Replication, some of it continuos, some every 15 minutes.
> So my questions are:
> Would I be better off to use a dedicated Remote Distributor? One of my
main
> objectives here is to speed up these boxes.
I would only use this if you are experiencing considerable locking in your
distribution database.
> If I did use a Remote Distributor, would it matter if I went to Push
> Subscriptions for centralized Administration?
No, not for a few always connected subscribers.
Or would that defeat my goal
> of speeding up the Publisher?
Stop your log reader and distribution agent. Do you get a significant
increase in performance. If so, migrate; if not (and suspect not) stay with
the local distributor.
>
Tuesday, March 20, 2012
pulling single lines from a DB
hi im making a page where i want a single line at a time to be pulled from my MS Database, basically at the moment i have a list of questions, but the page is displaying all the questions from my database, i only want it to pull out 1 and then if the user clicks the true button then it goes to another page and displays another question?
Any ideas
Regards,
JoeHi Joe,
Are you want to retrieve any 1 question from DB randomly?
FYI:http://www.datawebcontrols.com/faqs/Data/ReturningDataInRandomOrder.shtml
Regards,
Monday, March 12, 2012
Pull Replication Idle Time
Hello,
i set up an pull replication. The first client i setup replication over
LAN and the second client i setup replication over RAS.
Server:
Windows Server 2003
SQL Server 2000
Client PCs:
Windows XP
Pull Replication over MS Syncmanager on demand
Now my problem:
At the first client (setup by LAN) the replication works fine over LAN.
When the client goes outside the office and start the replication over
RAS: It tooks a long time (up to 60 min) to start the replication
process. Sometimes the replication not even starts.
In the syncmanager it looks like the replication hangs.
When the client comes back in the office. The replication works fine?
At the second client (replication setup by RAS) the replication works
fine over RAS.
When the client comes back into the office and start the replication
over LAN: It takes a long time (up to 60 min) to start the replication
process. Sometimes the replication not even starts.
In the syncmanager it looks like the replication hangs.
When the client replicate over RAS. The replication works fine?
The name resolution in my network works good! Is there an cache in the
SQL Server or somebody knows an solution?
Best regards
Tulio Mossoro
looks like a bandwidth problem. Run profiler and see where it is getting
stuck. Also copy a 1 mg file across the RAS link and compare how long this
takes with when you copy it locally.
You can also get an idea of relative bandwidth via ras and compare it with
relative bandwidth via LAN by doing speed tests at
http://www.bandwidth.com/tools/speedTest
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
"Tulio" <mossorous@.yahoo.de> wrote in message
news:1128676983.748349.239970@.z14g2000cwz.googlegr oups.com...
> Hello,
> i set up an pull replication. The first client i setup replication over
> LAN and the second client i setup replication over RAS.
> Server:
> Windows Server 2003
> SQL Server 2000
> Client PCs:
> Windows XP
> Pull Replication over MS Syncmanager on demand
>
> Now my problem:
> At the first client (setup by LAN) the replication works fine over LAN.
> When the client goes outside the office and start the replication over
> RAS: It tooks a long time (up to 60 min) to start the replication
> process. Sometimes the replication not even starts.
> In the syncmanager it looks like the replication hangs.
> When the client comes back in the office. The replication works fine?
> At the second client (replication setup by RAS) the replication works
> fine over RAS.
> When the client comes back into the office and start the replication
> over LAN: It takes a long time (up to 60 min) to start the replication
> process. Sometimes the replication not even starts.
> In the syncmanager it looks like the replication hangs.
> When the client replicate over RAS. The replication works fine?
> The name resolution in my network works good! Is there an cache in the
> SQL Server or somebody knows an solution?
> Best regards
> Tulio Mossoro
>
|||Hello Hilary,
thanks for your fast answer but this can not be an bandwith problem.
On a few clients it is slow over RAS, on other clients it is slow over
LAN, on other clients it work perfect.
When i set up the replication via RAS then it is slow over LAN. Over RAS
it works perfect.
When i set up replication over LAN it is slow over RAS. Over LAN it
works perfect.
And the best at the end: The first two or three times it works perfect
in any direction. After this the replication gets slow up to not
working.
Is there any SQL Cache that holds the IP?
Thanks a lot in advance
Tulio Mossorous
*** Sent via Developersdex http://www.codecomments.com ***
i set up an pull replication. The first client i setup replication over
LAN and the second client i setup replication over RAS.
Server:
Windows Server 2003
SQL Server 2000
Client PCs:
Windows XP
Pull Replication over MS Syncmanager on demand
Now my problem:
At the first client (setup by LAN) the replication works fine over LAN.
When the client goes outside the office and start the replication over
RAS: It tooks a long time (up to 60 min) to start the replication
process. Sometimes the replication not even starts.
In the syncmanager it looks like the replication hangs.
When the client comes back in the office. The replication works fine?
At the second client (replication setup by RAS) the replication works
fine over RAS.
When the client comes back into the office and start the replication
over LAN: It takes a long time (up to 60 min) to start the replication
process. Sometimes the replication not even starts.
In the syncmanager it looks like the replication hangs.
When the client replicate over RAS. The replication works fine?
The name resolution in my network works good! Is there an cache in the
SQL Server or somebody knows an solution?
Best regards
Tulio Mossoro
looks like a bandwidth problem. Run profiler and see where it is getting
stuck. Also copy a 1 mg file across the RAS link and compare how long this
takes with when you copy it locally.
You can also get an idea of relative bandwidth via ras and compare it with
relative bandwidth via LAN by doing speed tests at
http://www.bandwidth.com/tools/speedTest
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
"Tulio" <mossorous@.yahoo.de> wrote in message
news:1128676983.748349.239970@.z14g2000cwz.googlegr oups.com...
> Hello,
> i set up an pull replication. The first client i setup replication over
> LAN and the second client i setup replication over RAS.
> Server:
> Windows Server 2003
> SQL Server 2000
> Client PCs:
> Windows XP
> Pull Replication over MS Syncmanager on demand
>
> Now my problem:
> At the first client (setup by LAN) the replication works fine over LAN.
> When the client goes outside the office and start the replication over
> RAS: It tooks a long time (up to 60 min) to start the replication
> process. Sometimes the replication not even starts.
> In the syncmanager it looks like the replication hangs.
> When the client comes back in the office. The replication works fine?
> At the second client (replication setup by RAS) the replication works
> fine over RAS.
> When the client comes back into the office and start the replication
> over LAN: It takes a long time (up to 60 min) to start the replication
> process. Sometimes the replication not even starts.
> In the syncmanager it looks like the replication hangs.
> When the client replicate over RAS. The replication works fine?
> The name resolution in my network works good! Is there an cache in the
> SQL Server or somebody knows an solution?
> Best regards
> Tulio Mossoro
>
|||Hello Hilary,
thanks for your fast answer but this can not be an bandwith problem.
On a few clients it is slow over RAS, on other clients it is slow over
LAN, on other clients it work perfect.
When i set up the replication via RAS then it is slow over LAN. Over RAS
it works perfect.
When i set up replication over LAN it is slow over RAS. Over LAN it
works perfect.
And the best at the end: The first two or three times it works perfect
in any direction. After this the replication gets slow up to not
working.
Is there any SQL Cache that holds the IP?
Thanks a lot in advance
Tulio Mossorous
*** Sent via Developersdex http://www.codecomments.com ***
Wednesday, March 7, 2012
Publisher/Subscriber role
Can a replica database be publisher and at the same time subscriber. If so,
Is Merge replication OK for applying to go in this environment and how should
I configure the replicas as a subscribers or publishers?
Thanks for your answers.
Car.
yes this will work, its called republishing or hierarchies. Have a look at
this link for more info.
http://msdn2.microsoft.com/en-us/library/ms152553.aspx
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
"Car" <Car@.discussions.microsoft.com> wrote in message
news:5BFA3BA9-9DFE-4B86-A6D1-4FB5EEC89895@.microsoft.com...
> Can a replica database be publisher and at the same time subscriber. If
> so,
> Is Merge replication OK for applying to go in this environment and how
> should
> I configure the replicas as a subscribers or publishers?
> Thanks for your answers.
> Car.
|||Here is information on how to set it all up:
http://www.replicationanswers.com/Republishing2005.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Is Merge replication OK for applying to go in this environment and how should
I configure the replicas as a subscribers or publishers?
Thanks for your answers.
Car.
yes this will work, its called republishing or hierarchies. Have a look at
this link for more info.
http://msdn2.microsoft.com/en-us/library/ms152553.aspx
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
"Car" <Car@.discussions.microsoft.com> wrote in message
news:5BFA3BA9-9DFE-4B86-A6D1-4FB5EEC89895@.microsoft.com...
> Can a replica database be publisher and at the same time subscriber. If
> so,
> Is Merge replication OK for applying to go in this environment and how
> should
> I configure the replicas as a subscribers or publishers?
> Thanks for your answers.
> Car.
|||Here is information on how to set it all up:
http://www.replicationanswers.com/Republishing2005.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Saturday, February 25, 2012
Publication w/o logreader agent. Is it possible?
Hi,
Is it possible to create a publication without creating a logreader agent at
the same time? I'm on MS SQL Server 2000 SP3a.
-- Many thanks, Oskar
Oskar - if you are talking about transactional replication, this is an
integral part of the functioning. I'm interested in why would you like to
avoid the logreader agent?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, transactional is what I am talking about. I am interested in this
because I want to have development databases published but I do not want to
have any real replication going on there. This is needed because we must have
identical "change management" in development and production environments.
Looks like I have partly solved this problem by making each development
server a publisher and a distributor, and regenerating the publication from a
script. I even could do without the snapshot agent, but getting rid of the
logreader seems to be tricky, if not impossible.
-- Thanks, Oskar
"Paul Ibison" wrote:
> Oskar - if you are talking about transactional replication, this is an
> integral part of the functioning. I'm interested in why would you like to
> avoid the logreader agent?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Oskar,
don't know if this helps, but until the snapshot has run, the log reader
isn't moving any commands to the distribution database. To prevent it
parsing the transaction log you could simply stop it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That's exactly what I did. I just don't like to stop it manually everytime
the development database gets refreshed - its job name and id changes at
every such refreshing.
-- Oskar
"Paul Ibison" wrote:
> Oskar,
> don't know if this helps, but until the snapshot has run, the log reader
> isn't moving any commands to the distribution database. To prevent it
> parsing the transaction log you could simply stop it.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||I'd add a piece of code at the end of the script which stops and disables
the job - that way you don't need to worry about it afterwards.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Is it possible to create a publication without creating a logreader agent at
the same time? I'm on MS SQL Server 2000 SP3a.
-- Many thanks, Oskar
Oskar - if you are talking about transactional replication, this is an
integral part of the functioning. I'm interested in why would you like to
avoid the logreader agent?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, transactional is what I am talking about. I am interested in this
because I want to have development databases published but I do not want to
have any real replication going on there. This is needed because we must have
identical "change management" in development and production environments.
Looks like I have partly solved this problem by making each development
server a publisher and a distributor, and regenerating the publication from a
script. I even could do without the snapshot agent, but getting rid of the
logreader seems to be tricky, if not impossible.
-- Thanks, Oskar
"Paul Ibison" wrote:
> Oskar - if you are talking about transactional replication, this is an
> integral part of the functioning. I'm interested in why would you like to
> avoid the logreader agent?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Oskar,
don't know if this helps, but until the snapshot has run, the log reader
isn't moving any commands to the distribution database. To prevent it
parsing the transaction log you could simply stop it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That's exactly what I did. I just don't like to stop it manually everytime
the development database gets refreshed - its job name and id changes at
every such refreshing.
-- Oskar
"Paul Ibison" wrote:
> Oskar,
> don't know if this helps, but until the snapshot has run, the log reader
> isn't moving any commands to the distribution database. To prevent it
> parsing the transaction log you could simply stop it.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||I'd add a piece of code at the end of the script which stops and disables
the job - that way you don't need to worry about it afterwards.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Publication falls to inactive
Hi all
We are using a pull publication what works well. After a time, maybe next
day, the publication must be reinitialized again.
The duration property of the publication does not define a final date. I
could not find other properties controlling the activation state.
Is this a security issue why a publication falls to inactive?
Thanks for help, Jan
Pls have a look at the retention period for the publication, the transactions
and the history retention. One or more of these is likely to be being
exceeded.
Paul Ibison
We are using a pull publication what works well. After a time, maybe next
day, the publication must be reinitialized again.
The duration property of the publication does not define a final date. I
could not find other properties controlling the activation state.
Is this a security issue why a publication falls to inactive?
Thanks for help, Jan
Pls have a look at the retention period for the publication, the transactions
and the history retention. One or more of these is likely to be being
exceeded.
Paul Ibison
Publication
I have a publication on my SQL Server.
The first time my application syncs with the server it works fine.
If I try to sync again I get "Permission Denied Number 80040E09 NativeErr: 0"
Other publications work fine, it just seems to be this one.
Any ideas?
1. What kind of publication is this one, merge publication or transactional publication?
2. How many articles are in this publication and of what kind, table, view, SP or UDF?
3. What version of SQL server are you using on distribution/publisher/subscriber?
Thanks.
|||1 - Merge to SQL CE
2 - 14 tables
3 - SQL Server Enterprise V 8.00.679 (SP2)
|||by "the first time sync to server ", do you mean the initial sync ?
what are the login you used to connect to SQL server ? does this login has the read permission on the database ?
thanks
Yunwen
|||Yes the initial sync
Unfortunately the previous developer used sa as the login (DOH)
Yes sa has privileges.
We are building a new server to get rid of that.
|||Most likely this could a security configuration issue when connecting to the backend sql server. it is still not very clear why the subsequent sync failed with permission error though since SA should have sufficient permission during sync.
Does this still repro with the new application ? we should trace it down if it repro-es.
Thanks
Yunwen
Publication
I have a publication on my SQL Server.
The first time my application syncs with the server it works fine.
If I try to sync again I get "Permission Denied Number 80040E09 NativeErr: 0"
Other publications work fine, it just seems to be this one.
Any ideas?
1. What kind of publication is this one, merge publication or transactional publication?
2. How many articles are in this publication and of what kind, table, view, SP or UDF?
3. What version of SQL server are you using on distribution/publisher/subscriber?
Thanks.
|||1 - Merge to SQL CE
2 - 14 tables
3 - SQL Server Enterprise V 8.00.679 (SP2)
|||by "the first time sync to server ", do you mean the initial sync ?
what are the login you used to connect to SQL server ? does this login has the read permission on the database ?
thanks
Yunwen
|||Yes the initial sync
Unfortunately the previous developer used sa as the login (DOH)
Yes sa has privileges.
We are building a new server to get rid of that.
|||Most likely this could a security configuration issue when connecting to the backend sql server. it is still not very clear why the subsequent sync failed with permission error though since SA should have sufficient permission during sync.
Does this still repro with the new application ? we should trace it down if it repro-es.
Thanks
Yunwen
Subscribe to:
Posts (Atom)