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.
我刚刚遇到一些令人不安的事情,我试图从一个设计不受我们控制的数据库中实现事务复制。这种复制是为了执行报告而不会对系统造成太大的负担。在尝试复制时,只有一些表发生了。
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.
由于调查表没有主键,因此没有选择复制调查表,我认为如果我使用ODBC和ms访问而不是在管理工作室中,它甚至不会显示为主键。查询也不是非常慢。
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.
我尝试插入一个重复的记录,它没有说出一个唯一的索引(不是主键)。似乎是使用唯一索引实现的表与主键相对。为什么我不知道我会尖叫。
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.
无论如何都要执行事务复制或替代方案,它需要是实时的(最后一两分钟)。主db服务器目前是sql 2000 sp3a和报告服务器2005。
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.
我目前唯一想到的尝试就是将复制设置为另一种类型的数据库。我相信复制说oracle是可能的,这会强制使用像我假设访问正在使用因此显示主键的ODBC驱动程序。我不知道这是否准确无误。
1 个解决方案
#1
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:
正如MSDN所述,无法在没有主键的表上创建事务复制。您可以使用不需要主键的合并复制(单向),如果不存在,它会自动创建一个rowguid列:
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.
合并复制使用全局唯一标识符(GUID)列来标识合并复制过程中的每一行。如果发布的表没有带有ROWGUIDCOL属性和唯一索引的uniqueidentifier列,则复制会添加一个。确保引用已发布表的任何SELECT和INSERT语句都使用列列表。如果不再发布表并且复制添加了列,则删除该列;如果列已存在,则不会删除。
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
如果您只需要使用复制进行报告,并且您不需要数据与发布者上的数据完全相同,那么您也可以考虑快照复制
#1
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:
正如MSDN所述,无法在没有主键的表上创建事务复制。您可以使用不需要主键的合并复制(单向),如果不存在,它会自动创建一个rowguid列:
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.
合并复制使用全局唯一标识符(GUID)列来标识合并复制过程中的每一行。如果发布的表没有带有ROWGUIDCOL属性和唯一索引的uniqueidentifier列,则复制会添加一个。确保引用已发布表的任何SELECT和INSERT语句都使用列列表。如果不再发布表并且复制添加了列,则删除该列;如果列已存在,则不会删除。
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
如果您只需要使用复制进行报告,并且您不需要数据与发布者上的数据完全相同,那么您也可以考虑快照复制