发现CRM2011数据库维护的探讨

最新推荐文章于 2021-04-14 10:52:11 发布

hillstream3

最新推荐文章于 2021-04-14 10:52:11 发布

阅读量1k

点赞数

分类专栏：信息系统文章标签： crm crm2011 AsyncOperationBase WorkflowLogBase PrincipalObjectAcces

本文链接：https://blog.csdn.net/haoyujie/article/details/8956120

版权

信息系统专栏收录该内容

12 篇文章 1 订阅

订阅专栏

这段时间，正在进行CRM2011方面的工作，一直都存在一个问题

因为发现数据库，一直在膨胀。

每天30M.

今天抽时间看了看。

看到，有三个表，与第四名不在一个量级。

原因它们三个，占了绝大部分空间。

name	rows	reserved	data	index_size	unused
AsyncOperationBase	2042426	2809384 KB	1994608 KB	806304 KB	8472 KB
WorkflowLogBase	4045276	1950696 KB	1517728 KB	432536 KB	432 KB
PrincipalObjectAccess	10641923	3661480 KB	1395584 KB	2265360 KB	536 KB
ReportBase	100	27760 KB	28360 KB	136 KB	0 KB

前面两个分别是系统任务，和工作流，应当手工删除，也没什么影响。

但第三个，到网上查了查，还是有些麻烦。

微软解释说，要升到Rolling Updating 6 才能解决数据和POA一起删除的问题。

这样说来，并不能解决POA变大的问题。

所以，很犹豫是不是进行升级。

希望有这方面经验的人，帮提提建议。

PrincipalObjectAccess–Performance Recommendations

http://blogs.msdn.com/b/crminthefield/archive/2010/08/16/excessive-principalobjectaccess-poa-table-growth-in-crm-4-0.aspx

http://blogs.msdn.com/b/crminthefield/archive/2011/06/09/principalobjectaccess-performance-recommendations.aspx

http://blogs.msdn.com/b/ukcrm/archive/2011/07/08/automating-crm-2011-security-rules-with-plug-ins.aspx

http://blogs.msdn.com/b/ukcrm/archive/2011/03/10/using-plug-ins-to-modify-views.aspx

http://support.microsoft.com/kb/2664150

还有，关于纪录共享，我也正在找原因。

Why Large-Scale Sharing Is Bad

Sharing in CRM is designed to provide explicit access to records when the role-based security doesn’t allow it. Whenever a record is shared, an entry is added to the PrincipalObjectAccess table in the database. In addition, depending on the cascading options set on different entity relationships, an entry is added for each child record. For example, suppose you take the out-of-the box account entity and look 1:N relationships to the various child entities (e-mail, phone call, task, appointment, letter, fax, lead, opportunity, quote, order, invoice, contact, case, note etc.). Each time you share the parent account, an entry is added for each child record as well. For large-scale CRM deployments, it doesn’t take long for the PrincipalObjectAccess table to grow to several million records, which can have a major impact on performance.

If you want to understand this in more depth, here are some good articles on the subject:

Team Ownership As An Alternative To Sharing

New to CRM 2011 is team ownership of records, and it is this feature that I believe offers a more scalable solution to sharing. In order to make this work, I decided that I would have a separate team for each account record, so for a CRM implementation with 40,000 accounts there would be 40,000 teams. I did think about having a team for each portfolio to keep the number of teams to a relatively small number, but there are some possible future requirements that will mean each account may need slightly different user access.

没完全看明白。

==================================

续：

1。如上所示，数据库变大，有三个表，可以认为两方面主要原因，一个是因为POA，也就是Share 信息，在所属记录被删除后，并没有被删除。

微软给出的官方解决方案为：

http://support.microsoft.com/kb/2664150

大意如下：

1）升级到Rolling Update 6 .

要注意的是升级到期Rolling Update6之前，需要先升级到5。

因为微软已认定，Share信息，没有与记录一起被删除是一个bug。当然是一个bug.

升级的目的是解决这个bug.

2）手动执行一段脚本，用这段脚本把现存错误的POA信息删除。我试了一下，删除了一半的记录。