MSDE General Questions

I am trying to better understand my replication options with MSDE.
I am pretty sure that with merge replication the remote MSDE database will
track the changes in a set of state tables. However with transactional
replication I have read conflicting information as to whether it tracks
changes in the MSDE transaction log or in state tables. Does anyone know the
answer to this?
Also since MSDE has a limit to the size of the database does anyone know how
much additional data is stored in these state tables whenever a change is
made?
Thanks
Answers in line
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
"Cavanagh" <Cavanagh@.discussions.microsoft.com> wrote in message
news:DBE421DC-3E4B-4BC6-9275-0DB77F7133E0@.microsoft.com...
> I am trying to better understand my replication options with MSDE.
> I am pretty sure that with merge replication the remote MSDE database will
> track the changes in a set of state tables. However with transactional
> replication I have read conflicting information as to whether it tracks
> changes in the MSDE transaction log or in state tables.
In merge replication is uses the same state tabels for all versions of SQL
Server including MSDE. For transactional replication MSDE can only subscribe
to transactional replication publications.
Does anyone know the
> answer to this?
> Also since MSDE has a limit to the size of the database does anyone know
how
> much additional data is stored in these state tables whenever a change is
> made?
For an insert or update roughly 2329 bytes. For a delete about 748.

> Thanks
|||> For transactional replication MSDE can only subscribe
> to transactional replication publications.
So for transactional replication (MSDE), does it track changes in state
tables or in the transaction log?
Thanks for the other answers.
|||I don't like this question, because if I was to say yes, which strictly
speaking is true, you would probably get a incorrect idea of how it works.
The state of the subscription is tracked in a table in MSDE called
MSreplication_subscriptions. This table contains information about what
transaction was last applied on the subscription database. It contains no
information about changes which were made to the subscriber tables per se.
So if a user makes a change to a subscriber table, it won't appear in
MSreplication_subscriptions. If a replication process makes a change some
metadata will be logged in MSreplication_subscriptions.
Changes themselves are logged in two "state tables" in the distribution
database on the publisher. Prior to being logged in the distribution
database, they are logged in the transaction log in the publication
database.
No changes are logged in the subscription database on the MSDE database,
unless you are using queued updating.
The way to think about it is, with plain vanilla transactional replication,
any user activity originating on the subscriber (MSDE), in the subscription
database will not be logged in state tables, but replication activity
originating on the publisher will update the subscriber tables, and write
replication metadata to the "state" table MSreplication_subscription.
HTH
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
"Cavanagh" <Cavanagh@.discussions.microsoft.com> wrote in message
news:181BE87C-FAEC-467E-861C-0E19DED023F8@.microsoft.com...
> So for transactional replication (MSDE), does it track changes in state
> tables or in the transaction log?
> Thanks for the other answers.
|||Thanks!
"Hilary Cotter" wrote:

> I don't like this question, because if I was to say yes, which strictly
> speaking is true, you would probably get a incorrect idea of how it works.
> The state of the subscription is tracked in a table in MSDE called
> MSreplication_subscriptions. This table contains information about what
> transaction was last applied on the subscription database. It contains no
> information about changes which were made to the subscriber tables per se.
> So if a user makes a change to a subscriber table, it won't appear in
> MSreplication_subscriptions. If a replication process makes a change some
> metadata will be logged in MSreplication_subscriptions.
> Changes themselves are logged in two "state tables" in the distribution
> database on the publisher. Prior to being logged in the distribution
> database, they are logged in the transaction log in the publication
> database.
> No changes are logged in the subscription database on the MSDE database,
> unless you are using queued updating.
> The way to think about it is, with plain vanilla transactional replication,
> any user activity originating on the subscriber (MSDE), in the subscription
> database will not be logged in state tables, but replication activity
> originating on the publisher will update the subscriber tables, and write
> replication metadata to the "state" table MSreplication_subscription.
> HTH
> --
> 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
> "Cavanagh" <Cavanagh@.discussions.microsoft.com> wrote in message
> news:181BE87C-FAEC-467E-861C-0E19DED023F8@.microsoft.com...
>
>