数据库复制或表复制是否通过触发？ [英] DB Replication or Table Replication via triggers?
问题描述
大家好，
我参与了一个场景，其中有一个巨大的（SQL Server 2005）
生产数据库，其中包含多个更新的表时间
每秒。需要根据这个数据库中的数据生成最终用户报告，因此得出的结论是：
报告数据库是必要的。为了卸载报告处理
来自生产;当然，这意味着必须将数据复制到报告数据库。但是，我们不需要生成数据库中的所有数据，也许可以建立过滤标准
，其中只有某些行被复制到
报告数据库，因为它们已插入（并可能更新/删除）。
当前的流程是程序员设计
查询/报告将确切地知道他们需要从生产中获得哪些数据
并且能够根据需要修改复制标准。例如，
程序员A可能会编写一份报告，其中所需的数据可以用表格T的简单复制标准表示，其中第X列是
=" WOOD"和列Y =MAHOGANY。程序员B可能会在一个月之后出现，然后编写一份报告，该报告依赖于同一个表格，其中
列X =METAL。和（12,24,36）中的列Z.程序员B将需要修改程序员A的复制标准，以便
适应两种报告，在这种情况下类似于复制行来自
表T其中（col X =" WOOD"和col Y =" MAHOGANY"）或（col X =
" METAL"和col Z in（12 ，24,36））。我给出的例子当然是非常简单的，但是足以让你了解
当前的思维过程是什么。
我认为这是一个要求你们许多人过去遇到过
我想知道你们有什么解决方案
能够拿出来。就个人而言，我认为上述方法容易出错（在这种情况下使用触发器来指定
复制标准）而且我宁愿使用复制服务到
完整复制表。但是，由于某些表格的庞大规模，在我的情况下，这似乎不是一个
选项。是否存在基于复杂的
程序员定义标准执行复制的任何内容？
？触发器是否可行？任何
替代开箱即用的解决方案？
任何反馈都将不胜感激。
问候！
Anthony
我建议使用nosync初始化进行事务复制。这个
是报告服务器上的初始设置是通过使用数据库的
恢复来实现的，之后只发送后续更改
下来。如果您使用的是SQL Server 2005，则可以通过使用读取提交的快照隔离级别来实现更高的并发性。
干杯，
Paul Ibison SQL Server MVP， www.replicationanswers.com
你好Paul，
感谢您的回复。我将研究SQL Server 2005的
复制和静态行级别过滤;你可能会推荐哪些书籍或网站？
网站？我需要能够以编程方式设置和修改行过滤条件，以及评论@Barnes和
Noble onPro SQL Server 2005 Replication非常令人沮丧。
问候，
Anthony
4月17日，3：09下午，保罗·伊比森 < Paul.Ibi ... @ Pygmalion.Comwrote：
我建议使用nosync初始化进行事务复制。这个
是报告服务器上的初始设置是通过使用数据库的
恢复来实现的，之后只发送后续更改
下来。如果您使用的是SQL Server 2005，则可以通过使用读取提交的快照隔离级别来实现更高的并发性。
干杯，
Paul Ibison SQL Server MVP， www.replicationanswers.com
看看希拉里的快照和交易书，但是如果你有更多的合并信息并且不喜欢Pro书那么你需要b $ b你需要的确是BOL，然后为自己做一些场景以获得经验。对于网站来说，我有一些有用的信息，下面有一些有用的信息，你可以通过谷歌搜索获得各种SQL Server网站上的其他
文章，但是
没什么我认为特定于你的要求。
BTW这并不适合动态修改过滤器。至少
，这并不像你想象的那么简单。通常，
分区设计得很好。如果你想要更多动态的东西，那么我就不会在复制中进行过滤，而是在客户端应用程序上使用过滤器来代替。/ br / >
干杯，
Paul Ibison SQL Server MVP， www.replicationanswers .com
Hello everyone,
I am involved in a scenario where there is a huge (SQL Server 2005)
production database containing tables that are updated multiple times
per second. End-user reports need to be generated against the data in
this database, and so the powers-that-be came to the conclusion that a
reporting database is necessary in order to offload report processing
from production; of course, this means that data will have to be
replicated to the reporting database. However, we do not need all of
the data in the production database, and perhaps a filtering criteria
can be established where only certain rows are replicated over to the
reporting database as they''re inserted (and possibly updated/deleted).
The current though process is that the programmers designing the
queries/reports will know exactly what data they need from production
and be able to modify the replication criteria as needed. For example,
programmer A might write a report where the data he needs can be
expressed in a simple replication criteria for table T where column X
= "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
month later and write a report whose relies on the same table T where
column X = "METAL" and column Z in (12, 24, 36). Programmer B will
have to modify Programmer A''s replication criteria in such a way as to
accomodate both reports, in this case something like "Copy rows from
table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
"METAL" and col Z in (12, 24, 36))". The example I gave is really
trivial of course but is sufficient to give you an idea of what the
current thought-process is.
I assume that this is a requirement that many of you may have
encountered in the past and I am wondering what solutions you were
able to come up with. Personally, I believe that the above method is
prone to error (in this case the use of triggers to specify
replication criteria) and I''d much rather use replication services to
copy tables in their entirety. However, this does not seem to be an
option in my case due to the sheer size of certain tables. Is there
anything out there that performs replication based on complex
programmer defined criteria? Are triggers a viable alternative? Any
alternative out-of-the-box solutions?
Any feedback would be appreciated.
Regards!
Anthony
I''d recommend transactional replication with a nosync initialization. This
is where the initial setup on the reporting server is achieved by using a
restore of the database and after that, only subsequent changes are sent
down. If you are using SQL Server 2005, greater concurrency can be achieved
by using the read committed snapshot isolation level.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Hello Paul,
Thank you for your response. I will look into SQL Server 2005''s
replication and static row level filtering; are there any books or web-
sites you might recommend? I will need to be able to set up and modify
the row-filter criteria programatically, and the reviews @ Barnes and
Noble on "Pro SQL Server 2005 Replication" are pretty dismal.
Regards,
Anthony
On Apr 17, 3:09 pm, "Paul Ibison" <Paul.Ibi...@Pygmalion.Comwrote:I''d recommend transactional replication with a nosync initialization. This
is where the initial setup on the reporting server is achieved by using a
restore of the database and after that, only subsequent changes are sent
down. If you are using SQL Server 2005, greater concurrency can be achieved
by using the read committed snapshot isolation level.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com
Have a look at Hilary''s book for snapshot and transactional, but if you''re
after more merge info and don''t like the Pro book then it''s really BOL that
you need and then doing some scenarios for yourself to gain experience. As
for websites, I have some useful info on the site below and there are other
articles out on the various SQL Server sites you can get by googling, but
nothing I think specific to your requirements.
BTW this doesn''t really lend itself to modifying the filters dynamically. At
least this is not as straightforward as you might think. Normally the
partitions are well designed to start with. If you want something more
dynamic, then I''d not filter at all in replication and I''d use filters on
the client application instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
这篇关于数据库复制或表复制是否通过触发？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
