Is Replication the correct solution?

Hello,
Goal: Maintain a copy of two of our live databases from server A on server
B that are updated hourly. The databases on server B are intended to be read
from, not updated. Database 1 is about 500 MB, database 2 is about 3.8 GB.
Problems: 1) Transactional replication requires a primary key for each
table, not all the tables have primary keys. 2) When I try to set up
snapshot replication I get a warning message about the Identity Property will
not be transferred. I have at least 100 tables with the identity property.
3) Merge replication does not seem suited to my application.
Am I correct with what I am thinking as stated above? Can replication be
used to do what I am trying to do? If so, what would you recommend? I
appreciate any suggestions that can be offered.
IMHO replication is a good way for this.
Why don't you create PKs on the replication tables?
Thomas
On Wed, 9 Mar 2005 08:49:08 -0800, "Dave Johannson"
<DaveJohannson@.discussions.microsoft.com> wrote:

>Hello,
>Goal: Maintain a copy of two of our live databases from server A on server
>B that are updated hourly. The databases on server B are intended to be read
>from, not updated. Database 1 is about 500 MB, database 2 is about 3.8 GB.
>Problems: 1) Transactional replication requires a primary key for each
>table, not all the tables have primary keys. 2) When I try to set up
>snapshot replication I get a warning message about the Identity Property will
>not be transferred. I have at least 100 tables with the identity property.
>3) Merge replication does not seem suited to my application.
>Am I correct with what I am thinking as stated above? Can replication be
>used to do what I am trying to do? If so, what would you recommend? I
>appreciate any suggestions that can be offered.
|||I am new to replication, please explain a little about IMHO.
One of the databases in question is an ERP database, I am uncertain how the
schema update scripts from the vendor will behave if I change things on the
tables, also, I am not certain how the application will handle changes to the
tables, and lastly management is not comfortable with such modifications, so
I shy away from adding PKs to the tables that don't have them.
"Thomas Hase" wrote:

> IMHO replication is a good way for this.
> Why don't you create PKs on the replication tables?
> Thomas
>
> On Wed, 9 Mar 2005 08:49:08 -0800, "Dave Johannson"
> <DaveJohannson@.discussions.microsoft.com> wrote:
>
>
|||IMHO == In My Humble Opinion
Without PKs you can't use stanardreplication.
If you uncertain, ask your vendor about adding PKs.
If you don't add FKs to the PKs, there is IMHO no influence on
your application. It may be a little bit smaler performance.
Most databases have PKs and FKs to implement
referential integrity at database level.
On Wed, 9 Mar 2005 09:39:02 -0800, "Dave Johannson"
<DaveJohannson@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am new to replication, please explain a little about IMHO.
>One of the databases in question is an ERP database, I am uncertain how the
>schema update scripts from the vendor will behave if I change things on the
>tables, also, I am not certain how the application will handle changes to the
>tables, and lastly management is not comfortable with such modifications, so
>I shy away from adding PKs to the tables that don't have them.
>"Thomas Hase" wrote:
|||Is there a way around the problem of the identity property not being
transferred when using snapshot replication? If it is possilbe to make
snapshot replication work then I would not have to add PKs to the tables.
"Thomas Hase" wrote:

> IMHO == In My Humble Opinion
> Without PKs you can't use stanardreplication.
> If you uncertain, ask your vendor about adding PKs.
> If you don't add FKs to the PKs, there is IMHO no influence on
> your application. It may be a little bit smaler performance.
> Most databases have PKs and FKs to implement
> referential integrity at database level.
>
>
> On Wed, 9 Mar 2005 09:39:02 -0800, "Dave Johannson"
> <DaveJohannson@.discussions.microsoft.com> wrote:
>
>
|||As you are interested in having the identity property at
the subscriber, I suspect this is some sort of didaster
recovery strategy. In that case, you might want to
consider log shipping, which would better satisfy your
requirements.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)