没有主键(唯一索引)的事务复制 [英] Transactional replication with no primary key (unique index)

问题描述

我遇到了一些令人不安的问题，我试图从设计不受我们控制的数据库中实现事务复制.此复制是为了执行报告而不会给系统带来太多负担.尝试复制时，仅会遇到一些表.

I've just come across something disturbing, I was trying to implement transactional replication from a database whose design is not under our control . This replication was in order to perform reporting without taxing the system too much. Upon trying the replication only some of the tables went across.

在调查表中，由于没有主键，因此未选择要复制的表，我想这是不可能的，如果我使用ODBC和ms访问而不是在Management Studio中，它甚至会显示为主键.而且查询也不是很慢.

On investigation tables were not selected to be replicated because they don't have a primary key, I thought this cannot be it is even shown as a primary key if I use ODBC and ms access but not in management studio. Also the queries are not ridiculously slow.

我尝试插入重复的记录，但未能说明唯一索引(不是主键).似乎已使用唯一索引(与主键相反)实现了表.为什么我不知道我会尖叫.

I tried inserting a duplicate record and it failed saying about a unique index(not a primary key). Seems to be the tables have been implemented using a unique index as oppose to a primary key. Why I do not know I could scream.

无论如何，是否有执行事务复制的方法或替代方法，它必须处于活动状态(最后一两分钟).当前的主要数据库服务器是sql 2000 sp3a和报告服务器2005.

Is there anyway to perform transactional replication or an alternative, it needs to be live (last minute or two). The main db server is currently sql 2000 sp3a and the reporting server 2005.

我目前唯一想尝试的就是设置复制，就好像它是另一种类型的数据库一样.我相信说oracle的复制是可能的，这将迫使像我假设正在使用访问的ODBC驱动程序这样使用，因此显示了主键.我不知道这是否准确.

The only thing I have currently thought of trying is setting the replication up as if it is another type of database. I believe replication to say oracle is possible would this force the use of say an ODBC driver like I assume access is using hence showing a primary key. I don't know if that is accurate out of my depth on this.

推荐答案

作为MSDN 状态，无法在没有主键的表上创建事务复制.您可以使用合并复制(一种方式)，这不需要一个主键，如果不存在它会自动创建一个rowguid列:

As MSDN states, it is not possible to create a transactional replication on tables without primary keys. You could use Merge replication (one way), that doesn't require a primary key, and it automatically creates a rowguid column if it doesn't exist:

合并复制使用全局 唯一标识符(GUID)列 识别合并期间的每一行 复制过程.如果发表 表格没有uniqueidentifier ROWGUIDCOL属性的列 和唯一索引，复制会添加 一.确保任何SELECT和INSERT 引用已发表的声明 表使用列列表.如果一个表是 不再发布和复制 添加了列，该列是 删除;如果该列已经 存在，它不会被删除.

Merge replication uses a globally unique identifier (GUID) column to identify each row during the merge replication process. If a published table does not have a uniqueidentifier column with the ROWGUIDCOL property and a unique index, replication adds one. Ensure that any SELECT and INSERT statements that reference published tables use column lists. If a table is no longer published and replication added the column, the column is removed; if the column already existed, it is not removed.

不幸的是，如果使用合并复制，将会降低性能.

Unfortunately, you will have a performance penalty if using merge replication.

如果您只需要使用复制进行报告，并且不需要与发布者上的数据完全相同，则可以考虑使用快照复制

If you need to use replication for reporting only, and you don't need the data to be exactly the same as on the publisher, then you could consider snapshot replication also

这篇关于没有主键(唯一索引)的事务复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！