FYI some replication info that you will not find in Books Online

FYI some replication info that you will not find in Books Online

Post by tigh.. » Wed, 25 Oct 2000 04:00:00

To whom it may concern,

If you are looking in this replication discussion then perhaps you
trying to learn something about replication.  Perhaps you would like to
learn from my (sometimes painful) life experiences.

Here are some replication experience from my life.  I work with Ivy.
We do transactional replication of a 5GB database over the Internet.
We also use a VPN and Firewalls.

Yes this is a long post, so brace yourself.  However you might say that
this is just the tip of the iceverg.  There is a lot that I had to
learn on the fly to get this to work.

You might not understand the context or whole situation unless you go
to the original email at the very end of this post.  This post is from
a series of emails that Ivy and I sent to each other.  I hope Ivy will
not mind :).  (At least I took out Ivy's email address and so forth.)


Sorry, but I am a little confused.  You say that:

> "On the subscriber server I did not create the database first.
> There are only 3 stored procedures with sp_MS.....
> I needed to export the database first or not?"

Maybe you mean that you did not create the tables first?  I am thinking
that you did create the subscriber database first.  And you created the
subscription into that database.  And you were able to synchronize the
subscription.  In the subscriber database, you should have gotten one
table and the 3 sp_MS... stored procedures (for that one table).  You
are saying that the stored procedures are there but the table is not.

If that is the case then I would check the history of the snapshot
agent on the publisher and look for any problems.  I would also check
the directory where the snapshot was created and make sure that all the
files that the snapshot agent created are still there.  I would also
check the history of the subscription (distribution agent) (on the
subscriber AND on the publisher) and look for any problems.  If there
are no problems then look closely at the things that succeeded.  If
there are number then are the numbers right?  Are there messages (about
the missing table) that are missing?

I know that looking for missing messages is impossible if you do not
know for what messages to look.  Here are the messages that you should
see in the session details of the snapshot agent:

        A snapshot of 1 article(s) was generated.
        Posting snapshot commands into the distribution database
        Bulk copied snapshot data for article '<table name>'
                (<count(*)> rows).
        Bulk copying snapshot data for article '<table name>'
        Locking published tables while generating the snapshot
        Generating schema script for article '<table name>'
        Connecting to Publisher '<publisher name>'

The files that the snapshot agent should have created are:

        <table name>.bcp
        <table name>.idx
        <table name>.sch

The directory that they will be in is (by default) somewhere under
c:\mssql7\repldata\unc\.  Look for a directory that has the name of the
publication in it.  If you cannot find it then go into Enterprise
Manager on the publisher to find out where they should be going.  Go to
Tools, Replication, Configure Publishing...  Go to the Publishers
envelope tab.  (If you do not see a Publishers envelope tab then you
need to go to the distributor SQL Server to do this.)

In the Publishers envelope tab it will show you the distribution
database for the Publisher.  Click on the little gray square button
with three dots (...) in it that is next to the name of the
distribution database.  That will show you where the snapshot folder
is, but you still have to look for a unc subdirectory and a
subdirectory under that that has the name of the publication in the
name of the directory.  If the snapshot is using FTP then you have to
look for an FTP directory under the snapshot directory and then look
for a directory with the publication in the name under that.  And I am
not sure if there are more or less or the same number of files with FTP.

Here are the messages that you should see in the session details of the
distribution agent:

        Applied the snapshot to the Subscriber.
        Applied script '<table name>.idx'
        Bulk copied snapshot data into table '<table name>'
                (<count(*)> rows)
        Table "<table name>": <100000> row(s) copied. Total: <largest
                multiple of 100000 that is smaller than count(*) for
                this table>
        Table "<table name>": <-BcpBatchSize parameter of snapshot
                agent profile> row(s) copied. Total: <running total>
        Table "<table name>": <100000> row(s) copied. Total: <300000>
        Table "<table name>": <100000> row(s) copied. Total: <200000>
        Table "<table name>": <100000> row(s) copied. Total: <100000>
        Bulk copying data into table '<table name>'
        Applied script '<table name>.sch'

If you have less than 100000 (or whatever the -BcpBatchSize parameter
of your snapshot agent profile is) records in the table then you will
not get the status messages that show the progress of the BCP and the
running total.  This snapshot was using BCP, not FTP.  I think the
messages would be different if the snapshot was using FTP.

If everything leads you to dead ends then delete the subscription and
publication and try again, watching the agents closely.  If that still
does not work then let me know, but give me more details.  Usually the
problem is in the details.

I hope that helps.

If it takes too long to send the schema and all of the data in a
snapshot then there are several ways to get the schema to the
subscriber.  First I am going to describe how we did it then I will
describe one variation.  No matter how you do it, there are still more
steps to send the schema without a snapshot than to send the schema via
a snapshot.

I am not going to specify how, but you will have to send data from the
publisher to the subscriber, outside of replication, for this to work.

1) Create a SQL script to create the tables, and anything else that you
want to replicate, for the production database.

2) Create a TEST replication database on the publisher SQL Server.  Run
the SQL script from 1) in the TEST database on the PUBLISHER SQL
Server.  Now you have the right table structures in the TEST database.

3) Create a publication of the TEST DATABASE on the publisher.

4) Create a TEST SUBSCRIPTION database on the PUBLISHER SQL Server.
For this database you do not have to create any tables in the database
because replication will do that for you.  Create a subscription to the

5) Run snapshot agent for the test publication.

6) Monitor the snapshot agent of the test publication.

7) After the snapshot agent succeeds, have the subscription on the
PUBLISHER do a synchronize now.  This will create the sp_MS... stored
procedures in the TEST SUBSCRIBER database.

8) Create a SQL script to create the sp_MS... stored procedures.

9) Create a publication of the PRODUCTION DATABASE on the publisher.

10) Send the script from 1) to the SUBSCRIBER.  Run the SQL script from
1) in the production database on the SUBSCRIBER SQL Server.

11) Send the script from 8) to the SUBSCRIBER.  Run the SQL script from
8) in the production database on the SUBSCRIBER SQL Server.

12) Create a subscription on the subscriber of the PRODUCTION

13) Export the data from the production database at the publisher.  DTS
packages can be used.  So can BCP.

14) Send the exported data from 13) to the subscriber.  Import the data
into the production database at the subscriber either using DTS
packages or BCP, depending on what you decided to do in step 13.

Here is the variation.  Do not use any SQL scripts.  To do this, the
production database starts out empty.  Use the method from the earlier
email to set up the publication, the subscription, create the snapshot
and synchronize the subscriber.  Then delete the subscription and the
publication.  This will not delete the tables and the stored procedures
at the subscriber.  Populate the production database.  Set up the
publication and subscription using some of the steps from the above,
steps 9) and 12).  Populate the subscriber database.

I have heard (via postings on Usenet) of people doing things
differently than this, but I think these are the two best ways.  Some
of the elements of each variation can be combined, if you have some
problem that you have to deal with.

It sounds like you will be using some advanced replication.  If you are
going to replicate stored procedures (like our rsp_truncate stored
procedure) using transactional replication then make sure that they
know that calls to the stored procedure ARE replicated, but changes to
the stored procedure ARE NOT replicated.

You also might want to tell them that if/when agents stop working to
make sure that the go to the deepest level of detail in Enterprise
Manager or else they might not be able to find all the information that
could help them to fix the problem.  Also if an agent stops working
then once you fix the problem that caused it to stop, start it manually
and it will pick up where it left off.

You also might want to tell them about our experiences with rights and
login IDs.  Problems with rights and login ID's are common with
replication.  IMPORTANT NOTE:  Make sure that they add the subscriber's
login ID to the publication's access list.

Using the wizard to create publications and subscriptions does not give
you access to all the options, so you have to go back into them after
you create them with the wizard.

If you want to increase the TIMEOUT of a replication agent then you
have to stop the agent(s).  Add a new profile for the agent.  If you
tell SQL that all the existing agents should start using this new
profile using the same screen where you added the new profile then
(gotcha) the existing agents will not start using the new profile.
Instead go to the Replication Monitor right click on the agent(s) and
go into the Agent Profiles option.  This is the place to change the

read more »


FYI some replication info that you will not find in Books Online

Post by tigh.. » Sat, 28 Oct 2000 08:34:45

To all,

In order to read my whole post, I think that you have to get all four
segments or something like that.

Also, in another post I read that Michael Hotek found a better way to
deliver a snapshot than I did.  I think it is a very good idea.  Wish I
would have thought of it.  Here it the message that was taken from that
other thread:

I have used another approach today. We have made on one SQL server a
new DB and created a pull subscription to the published DB that is on
the same machine. We applied the snapshot and then backuped the
database which had the pull subsciption. Put that on CD and restored it
on the remote SQL server and recreated the pull subscription and used
the no synch option.

It worked very good that way.

Thanks to Michael Hotek for the information!



Sent via
Before you buy.


1. FYI: on 'SQL.iem not found!'

Hello folks...

Well, this is to help any of you who may have the same problem I've just
struggled with for the past four-and-a-half  hours. I'm running an NT
4.0 server with Cold Fusion 3.01 and Intersolv ODBC 3.0 drivers. I was
trying to connect to an Informix database on a Unix machine. I kept
getting the blasted 'sql.iem file not found' message. I looked through
my setnet32, my ODBC configurations and the CF datasources to no avail.
I even tried having the Cold Fusion service make its connection under
several different user accounts under NT. Looking at Deja News and this
newsgroup all I found was 'check connect permissions and/or
$INFORMIXDIR'. They were both right as far as I could tell.

SO...I was trying all this under the 'Administrator' account. Cold
Fusion was trying to connect as Informix. I got the wild idea that I
should check Informix's environment, so I logged onto the machine as
user 'informix'. Lo and behold, there was nothing set in its setnet32
environment -- including $INFORMIXDIR. I set all that crap, and came
back in as Administrator and the bloody thing worked!

Moral of the story ---> If you're on NT, pay attention to who the
service you're working with is running as. The environment for the
service depends not on who you're logged in as, but who the service is
running as.

My only comment through all of this is you would think the Informix
installation would set up its' own damn user account with the right
environment! SHEESH!!!!

Carry on...

Trevor Conn
ISPAlliance, Inc.

2. Installation error INSTBI01 with NT 4.0 And Paradox 5

3. Here: SQL 7 Info, SQL FAQ, Free Online Books

4. - Senior Oracle Tools Programmer Analyst - Portland, Oregon jw005496

5. Here: SQL 7 info, SQL List Server, FAQ, Online Books Free

6. Getting IDENTITY column value

7. Here: SQL FAQ, Online Books, SQL 7 Info (Free)

8. C libraries

9. I need to find a copy of the online books

10. where to find online books about java?

11. Info Req: Informix online replication services

12. SQL 7 Replication not working...could not find sp_MSupd_Users

13. Online Books (help) not installed correctly SQL 2000