Replication to different table structure

I had set up some Snapshot and Transactional replication in the past but
would like to see if following is possible.
Is it possible to do either snapshot or transactionl replication between
tables with 2 different table structure? Lets say I have a tableA which i
like to replicate to TableB. But TableB has few additional columns from
tableA. Also, How about datatype difference on some columns between TableA
and TableB?
Version is Sql server 2000 sp4.
I appreciate your reply.
Filtering (vertical) can be used to restrict the columns. Where the
datatypes are different there are a few posibilities, but replicating
indexed views is quite straightforward.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Have a look at this:
http://www.dbazine.com/sql/sql-articles/cotter1
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
"James" <kush@.brandes.com> wrote in message
news:uc8kggmaHHA.808@.TK2MSFTNGP04.phx.gbl...
>I had set up some Snapshot and Transactional replication in the past but
>would like to see if following is possible.
> Is it possible to do either snapshot or transactionl replication between
> tables with 2 different table structure? Lets say I have a tableA which i
> like to replicate to TableB. But TableB has few additional columns from
> tableA. Also, How about datatype difference on some columns between TableA
> and TableB?
> Version is Sql server 2000 sp4.
> I appreciate your reply.
>
|||http://www.dbazine.com/sql/sql-articles/cotter1
I just get a page prompting to renew the domain name... do you have another
link Hilary?
"Hilary Cotter" wrote:

> Have a look at this:
> http://www.dbazine.com/sql/sql-articles/cotter1
> --
> 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
>
> "James" <kush@.brandes.com> wrote in message
> news:uc8kggmaHHA.808@.TK2MSFTNGP04.phx.gbl...
>
>
|||Wow, I just checked this link and it was valid, and now I get the domain
expired as well
Have a look at this:
http://209.85.165.104/search?q=cache:4m4CLStYfWIJ:www.dbazine.com/sql/sql-articles/cotter1+http://www.dbazine.com/sql/sql-articles/cotter1&hl=en&ct=clnk&cd=1&gl=us
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
"Steve Hall" <SteveHall@.discussions.microsoft.com> wrote in message
news:4E6A1EF4-E639-4ED7-B4BA-F6DEE42F016E@.microsoft.com...[vbcol=seagreen]
> http://www.dbazine.com/sql/sql-articles/cotter1
> I just get a page prompting to renew the domain name... do you have
> another
> link Hilary?
>
> "Hilary Cotter" wrote:
|||Thanks Hilary,
I got the same text minus the images from google cache and
waybackmachine.org. Just got finished reading it and a few of your other
articles on dbdirections.com.
Steve
"Hilary Cotter" wrote:

> Wow, I just checked this link and it was valid, and now I get the domain
> expired as well
> Have a look at this:
> http://209.85.165.104/search?q=cache:4m4CLStYfWIJ:www.dbazine.com/sql/sql-articles/cotter1+http://www.dbazine.com/sql/sql-articles/cotter1&hl=en&ct=clnk&cd=1&gl=us
> --
> 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
>
> "Steve Hall" <SteveHall@.discussions.microsoft.com> wrote in message
> news:4E6A1EF4-E639-4ED7-B4BA-F6DEE42F016E@.microsoft.com...
>
>
|||Thanks Hilary! Helpful article.
I am still having some trouble though. I am trying to implement some of the
suggestion from that Article for Case 2 (where subscriber has more columns
than publisher) and I am using Snapshot replication only.
Suggestion1: I precreated the table on the subscriber and provide default
value for the column (which doesn't exist on publisher), but when the
distributer agent runs it gives me following error:
"Unexpected EOF encountered in BCP data-file"
Suggestion 2: "Supply a value for those columns in the replication stored
procedure". Where can I find this SP? It doesn't exist on Subscription
database.
Suggestion 3: "Index View". haven't tried yet. going to try very soon.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoznYMuaHHA.4396@.TK2MSFTNGP06.phx.gbl...
> Have a look at this:
> http://www.dbazine.com/sql/sql-articles/cotter1
> --
> 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
>
> "James" <kush@.brandes.com> wrote in message
> news:uc8kggmaHHA.808@.TK2MSFTNGP04.phx.gbl...
>
|||Could you post the table schema on the publisher and what you want it to
look like on the subscriber?
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
"James" <kush@.brandes.com> wrote in message
news:ux0YSQwaHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Thanks Hilary! Helpful article.
> I am still having some trouble though. I am trying to implement some of
> the suggestion from that Article for Case 2 (where subscriber has more
> columns than publisher) and I am using Snapshot replication only.
> Suggestion1: I precreated the table on the subscriber and provide default
> value for the column (which doesn't exist on publisher), but when the
> distributer agent runs it gives me following error:
> "Unexpected EOF encountered in BCP data-file"
> Suggestion 2: "Supply a value for those columns in the replication stored
> procedure". Where can I find this SP? It doesn't exist on Subscription
> database.
> Suggestion 3: "Index View". haven't tried yet. going to try very soon.
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoznYMuaHHA.4396@.TK2MSFTNGP06.phx.gbl...
>

replication problems with structure/trigger/identity

Hello alltogether,
i'm working on a database-plattform which should work with
fail-over-technics.
Therefor 2 identic machines with internal RAID are available, which work
in single-host-netloadbalance on win2003-server.
Because of the internal RAID and caused by costs, a solution without
external SCSI-RAID and win2003-clustering-mechanism is looked for.
First i tried to use merge-replication but the rowguid-column, which is
added, makes the application unable to use the database (sql-statements
without column-specification). The application is bought and not easy
adaptable.
Second i tried transaction-replication, which seamed to work, but no
key- and identity-attributes are replicated. When the first machine now
stops and second gets master, there are problems caused by the missing
identities (auto-increment). I got errors caused by triggers too.
Manual copy of the structure with key- and identity-attributes and
triggers causes new problems when the replication is active (i.e. a
replication insert datas in a table with a trigger, the trigger on the
master takes effect, the replication tries to copy the effected datas,
but the trigger on the slave already work with the datas).
Actual way is a differential copy periodical from master to slave, but
is not nice and just a periodical actual slave.
Any ideas for more solutions?
PS: Sorry for X-Post, but i'm not sure, if there are "active" reader of
"m.p.s.r"
Thanks & Greets
Marko Damaschke
trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
student of applied computer science at Chemnitz University of Technology
Hello,
i got an answer from Paul Ibison via a website-based newsforum, but
don't want to get a profile there - so the answer here.
Thanks for the idea via Log Shipping but i see there a problem with the
state of standby-database. The database has to be in "NORECOVERY-Mode",
when i understood it correct, while the whole Log-Shipping-process.
But how to "trigger" it, when suddenly the primary machine crashes?
Somehow the recover-mode has to be started.
Write-access is necessary and without manual actions the db is read-only.
We need automated failover without manual administrator actions.
Thanks & Greets
Marko Damaschke
Trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
Student of applied computer science at Chemnitz University of Technology
|||Marko,
the only automatic failover is from clustering. Log-
shipping, replication each have a manual failover. SQL
Server 2005 has database mirroring, but this is a few
months away from release yet. There are 3rd party tools -
DataMirror, but I don't know offhand if these offer
automatic failover.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication of structure

Hello

I have a server with SQL Server 2005 and several machines with SQL Server Express.

I have my "master" DB on the SQL Server 2005 on which I have created a transactional publication (push)

Then all my other DB have a subscription to that publication.

I have different scenarios:

The structure needs to be replicated --> OK it works The data of some table needs to be replicated --> OK it works Whatever the data at the client, they are replaced after the replication The data of some table needs to be partially replicated --> OK it works Whatever the data at the client, they are replaced after the replication (only part of the server data is replicated with the filters) Some data must not be replicated --> Don't know how to do this Whatever the data at the client and at the server, I want them unchanged after the replication

Can you help me with this latest point ? I have tried with the filtering but it always remove all data from the client. What I need is: If I have 10 lines on my server, and 20 on my client, I only want the structure to be updated. But I still need my 10 lines on the server and my 20 on my client after the replication.

Thanks !

By default, when you apply the snapshot, it will drop the table(s) at the subscriber and replace it with refreshed data generated by the snapshot. If you don't want the data dropped, then make sure to clear the "initialize" checkbox in the subscription wizard. See topic "How to: Initialize a Subscription Manually".

Replication newbie, help please...

Dear all,
I have a web application that uses sql2005 database. And a local windows
application that uses exactly same sql2005 database structure. I want to
synchronize databases. When new record added from web I want them into my
local db. When my local db changes I want them to go my server database.
Is this "replication" concept for this kind operation. there are many
resources on the net but I could not figure out how.
Any sample documentation, link on tghe web, sample project, anything is
wellcome. but I need to synchronize 2 databases in different locations.
Best regards.
Have a look in BOL for setting up merge replication. The exact setup depends
on how the web application and the local windows application can connect -
over tcp/ip (trusted?) or if not https?
Rgds,
Paul Ibison

Replication in SQL

Hi,
I have 2 server A & B which A has been set up to push the data over to B
However, i found that B's structure is not correct so i intend to backup A
and retorse into B , will the replication to B fail after restoration from
the backup of A ?
tks & rdgs
Once you make a copy of A -> B then both A and B are in sync.
In this case, as per my knowledge, the replication should not fail
best Regards,
Chandra
http://chanduas.blogspot.com/
"maxzsim" wrote:

> Hi,
> I have 2 server A & B which A has been set up to push the data over to B
> However, i found that B's structure is not correct so i intend to backup A
> and retorse into B , will the replication to B fail after restoration from
> the backup of A ?
> tks & rdgs

Replication in SQL

Hi,
I have 2 server A & B which A has been set up to push the data over to B
However, i found that B's structure is not correct so i intend to backup A
and retorse into B , will the replication to B fail after restoration from
the backup of A ?
tks & rdgsOnce you make a copy of A -> B then both A and B are in sync.
In this case, as per my knowledge, the replication should not fail
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:

> Hi,
> I have 2 server A & B which A has been set up to push the data over to B
> However, i found that B's structure is not correct so i intend to backup A
> and retorse into B , will the replication to B fail after restoration from
> the backup of A ?
> tks & rdgs

Replication in SQL

Hi,
I have 2 server A & B which A has been set up to push the data over to B
However, i found that B's structure is not correct so i intend to backup A
and retorse into B , will the replication to B fail after restoration from
the backup of A ?
tks & rdgsOnce you make a copy of A -> B then both A and B are in sync.
In this case, as per my knowledge, the replication should not fail
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi,
> I have 2 server A & B which A has been set up to push the data over to B
> However, i found that B's structure is not correct so i intend to backup A
> and retorse into B , will the replication to B fail after restoration from
> the backup of A ?
> tks & rdgs