by 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.)
Ivy,
Sorry, but I am a little confused. You say that:
> There are only 3 stored procedures with sp_MS.....
> I needed to export the database first or not?"
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>'
Initializing
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'
Initializing
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
TEST SUBSCRIPTION database on the PUBLISHER of the TEST PUBLICATION.
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
PUBLICATION, with the DO NOT INITIALIZE SCHEMA option.
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 »