Push Subscription - will it overwrite existing data first?

If I push a new subscription to an existing target database, and I choose or
check the box to include schema and data, will replication first delete or
empty tables at the target before pushing a new?
I did this, that is to push the subscription and checked the box to send
schema and data and killed the process mid-stream. At the target I found
certain tables to be empty. I am concluding that replication will first
empty or delete tables at the target.
Am I correct?
thanks,
bob
Bob,
on the article properties of a table, on the snapshot tab there is the
option to control this behaviour. By default if you look at the bcp files
per article, they will start with a drop table then a create table and after
that the rows will be inserted. It looks like you caught the process in
between these batches.
HTH,
Paul Ibison

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.
>

Pull data from Cache 5.0 to SQL 2000 (fast?)

I'm currently working with a 10 million plus row database with the data
residing on a Unix box with Cache 5.0. The problems is that it can take five
days to pull one table from Cache to SQL 2000 using the ODBC connection
provided by Cache in a SQL 2000 DTS package. I think the real problem is
converting the data from the post relational format (Cache) to a relational
format (SQL 2000)?

Does anyone have any ideas / suggestions on how to speed this transfer of
data? I'm very new to Cache and any help would be greatly appreciated.

Thanks,

-p"Pippen" <123@.hotmail.com> wrote in message
news:uadBb.1525$8y1.14237@.attbi_s52...
> I'm currently working with a 10 million plus row database with the data
> residing on a Unix box with Cache 5.0. The problems is that it can take
five
> days to pull one table from Cache to SQL 2000 using the ODBC connection
> provided by Cache in a SQL 2000 DTS package. I think the real problem is
> converting the data from the post relational format (Cache) to a
relational
> format (SQL 2000)?
> Does anyone have any ideas / suggestions on how to speed this transfer of
> data? I'm very new to Cache and any help would be greatly appreciated.

I don't know a thing about Cache, but it could be several things.

Is the table you're pulling the data into have a large number of indices?
If so, it is probably faster to drop them, load the data and then rebuild
them.

Also, you can try dumping the cache data to a text file of some sort and
using BCP or a DTS package to load it.

As reference, I load 3 tables compromising I think about 10 gig of data (I'd
have to look it up) and index them in I think under 6 hours. And that's on
moderately old hardware (3 years old).

I do this 4 times a year for 3 different databases. If I had to do it more
often I could probably speed it up even faster than that.

So, it's quite possible you may be able to speed this up.

> Thanks,
> -p|||"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:5AdBb.198332$1N3.102110@.twister.nyroc.rr.com. ..
> "Pippen" <123@.hotmail.com> wrote in message
> news:uadBb.1525$8y1.14237@.attbi_s52...
> > I'm currently working with a 10 million plus row database with the data
> > residing on a Unix box with Cache 5.0. The problems is that it can take
> five
> > days to pull one table from Cache to SQL 2000 using the ODBC connection
> > provided by Cache in a SQL 2000 DTS package. I think the real problem is
> > converting the data from the post relational format (Cache) to a
> relational
> > format (SQL 2000)?
> > Does anyone have any ideas / suggestions on how to speed this transfer
of
> > data? I'm very new to Cache and any help would be greatly appreciated.
> I don't know a thing about Cache, but it could be several things.
> Is the table you're pulling the data into have a large number of indices?
> If so, it is probably faster to drop them, load the data and then rebuild
> them.
> Also, you can try dumping the cache data to a text file of some sort and
> using BCP or a DTS package to load it.
> As reference, I load 3 tables compromising I think about 10 gig of data
(I'd
> have to look it up) and index them in I think under 6 hours. And that's
on
> moderately old hardware (3 years old).
> I do this 4 times a year for 3 different databases. If I had to do it
more
> often I could probably speed it up even faster than that.
> So, it's quite possible you may be able to speed this up.
>
> > Thanks,
> > -p
>
Thanks for the advice. I probably should have mentioned that the data is
going in to empty non indexed tables...

-js|||"Pippen" <123@.hotmail.com> wrote in message
news:9koBb.484514$Tr4.1330184@.attbi_s03...
> Thanks for the advice. I probably should have mentioned that the data is
> going in to empty non indexed tables...

Hmm, interesting. I'd then try to dump the data out of Cache and use BCP
and see if that's any faster.

At the very least it may break it into two parts and give you an idea of
which one is the slow part.

> -js

publisher, distributor, subscriber all on one box?

has anyone done this? I need to test something and thought I would give
this a try...
tia
-comb
I do it all the time.
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
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1cfeab14ad684989697@.news.newsreader.com.. .
> has anyone done this? I need to test something and thought I would give
> this a try...
> tia
> -comb
|||In article <eU3NT3YYFHA.3584@.TK2MSFTNGP12.phx.gbl>,
hilary.cotter@.gmail.com says...
> I do it all the time.
>
so do i set it up just under the configure replication... in enterprise
manager? is there anything special i need to do? Is there a smarter
way to move tables from one db to another continually?
Thanks,
-comb
|||Yes, and what do you mean by move tables continuously? For this possibly log
shipping is the best as it will mirror objects with no intervention, but
your destination will have to go off line and must be treated as read only.
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
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1cff9d563dc5267e989698@.news.newsreader.co m...
> In article <eU3NT3YYFHA.3584@.TK2MSFTNGP12.phx.gbl>,
> hilary.cotter@.gmail.com says...
> so do i set it up just under the configure replication... in enterprise
> manager? is there anything special i need to do? Is there a smarter
> way to move tables from one db to another continually?
> Thanks,
> -comb
|||In article <eBEXevgYFHA.1796@.TK2MSFTNGP15.phx.gbl>,
hilary.cotter@.gmail.com says...
> Yes, and what do you mean by move tables continuously? For this possibly log
> shipping is the best as it will mirror objects with no intervention, but
> your destination will have to go off line and must be treated as read only.
They are historical tables that are updated throughout the day. See the
db has about 750 tables in it, I am only needing to replicate about 4 of
them. That is why I 86'd the idea of logshipping. I thought with log
shipping it was an all or nothign type deal? Like the entire db or
nothing at all.
Thanks for any advice.
comb

PSSDIAG

I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4.
The collectif of the trace seems to go well.
When I stop the trace the programm seems to hang after the last message that
says that it's exiting.
I dont't have the problem on a Windows XP SP1.
Is there an issue for windows 2003 sp1 ?
Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
diagnostic ?
1- Do not install PSSDiag in c:\program files or any other directory that
have blank in its name
2- There are two requirements for the PSSDIAG service startup account: 1) it
must be a member of the local administrators group (though not necessarily
the local Administrator), and 2) it must be a member of the SQL Server
sysadmin server role. The default PSSDIAG service startup account is
LocalSystem, and by default it satisfies both of these requirements.
However, if you have removed the local Administrators group from the SQL
sysadmin role, you will need to choose a new PSSDIAG service startup account
that is both an NT administrator and a SQL sysadmin.
3- The .trc file is actually written by the SQL Server process. For this
reason, the SQL Server service startup account must be able to write to the
output directory.
"Sylvain Nadeau" wrote:

> I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4.
> The collectif of the trace seems to go well.
> When I stop the trace the programm seems to hang after the last message that
> says that it's exiting.
> I dont't have the problem on a Windows XP SP1.
> Is there an issue for windows 2003 sp1 ?
> Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
> diagnostic ?

PSSDIAG

I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4.
The collectif of the trace seems to go well.
When I stop the trace the programm seems to hang after the last message that
says that it's exiting.
I dont't have the problem on a Windows XP SP1.
Is there an issue for windows 2003 sp1 ?
Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
diagnostic ?1- Do not install PSSDiag in c:\program files or any other directory that
have blank in its name
2- There are two requirements for the PSSDIAG service startup account: 1) it
must be a member of the local administrators group (though not necessarily
the local Administrator), and 2) it must be a member of the SQL Server
sysadmin server role. The default PSSDIAG service startup account is
LocalSystem, and by default it satisfies both of these requirements.
However, if you have removed the local Administrators group from the SQL
sysadmin role, you will need to choose a new PSSDIAG service startup account
that is both an NT administrator and a SQL sysadmin.
3- The .trc file is actually written by the SQL Server process. For this
reason, the SQL Server service startup account must be able to write to the
output directory.
"Sylvain Nadeau" wrote:

> I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4
.
> The collectif of the trace seems to go well.
> When I stop the trace the programm seems to hang after the last message th
at
> says that it's exiting.
> I dont't have the problem on a Windows XP SP1.
> Is there an issue for windows 2003 sp1 ?
> Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
> diagnostic ?

PSSDIAG

I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4.
The collectif of the trace seems to go well.
When I stop the trace the programm seems to hang after the last message that
says that it's exiting.
I dont't have the problem on a Windows XP SP1.
Is there an issue for windows 2003 sp1 ?
Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
diagnostic ?1- Do not install PSSDiag in c:\program files or any other directory that
have blank in its name
2- There are two requirements for the PSSDIAG service startup account: 1) it
must be a member of the local administrators group (though not necessarily
the local Administrator), and 2) it must be a member of the SQL Server
sysadmin server role. The default PSSDIAG service startup account is
LocalSystem, and by default it satisfies both of these requirements.
However, if you have removed the local Administrators group from the SQL
sysadmin role, you will need to choose a new PSSDIAG service startup account
that is both an NT administrator and a SQL sysadmin.
3- The .trc file is actually written by the SQL Server process. For this
reason, the SQL Server service startup account must be able to write to the
output directory.
"Sylvain Nadeau" wrote:
> I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4.
> The collectif of the trace seems to go well.
> When I stop the trace the programm seems to hang after the last message that
> says that it's exiting.
> I dont't have the problem on a Windows XP SP1.
> Is there an issue for windows 2003 sp1 ?
> Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
> diagnostic ?