Blackbaud CRM and SQL Server 2014 CL 120 (Updated on 8/31/15)
Published
Revised 08/31/2015
​In the time since the original posting, we have completed functional and performance testing of Blackbaud CRM 3.0 and 4.0 with Microsoft SQL Server 2014 Service Pack 1. I have highlighted changes to the original post below. In summary, we have tested both MSSQL 2014 SP1 base and Cumulative Update 1 (CU1). Both of these test configurations resulted in passing functional testing and failing performance testing (due again to the new cardinality estimator in SQL Server 2014).
As part of our standard product lifecycle, we test all new major releases and service packs of Microsoft SQL Server. In the event that a Cumulative Update contains a fix to a known security vulnerability or performance regression, we will review the changes and decide whether or not to test the CU. This is how we arrived at the decision to test CU1, as it contained two fixes that appeared to solve known performance regressions due to the cardinality estimator change. Unfortunately, through testing we found that the bulk of performance regressions on CL 120 remained, and we could not certify it as a supported configuration. As newer CUs are released, we will evaluate the fixes they contain, with the ultimate goal of supporting CL 120 in a way that minimizes code churn and disruption to our current Blackbaud CRM customers.
As a result of our testing, we now support SQL Server 2014 SP1 at CL 110. We do not support SQL Server 2014 SP1 at CL120. Updated system requirements can be found here. ​
Blackbaud CRM System Requirements:
Update on SQL Server 2014 Compatibility Level 120
Recent information about SQL Server 2014 has resulted in an update to the Blackbaud CRM System Requirements. This post will provide an explanation of the current state, how we’ve arrived at this point, and our future plans for supporting Compatibility Level (CL) 120.How have the system requirements changed?
| Blackbaud now explicitly recommends that Blackbaud CRM customers on SQL Server 2014, use CL 100 or 110 for both versions 3.0 and 4.0. SP 1 is now supported. Original:  Old:  New:  Please note that these changes also apply to the data warehouse. |
Is SQL Server 2014 supported with Blackbaud CRM?
| Yes. In May 2014, we announced that the Blackbaud CRM 4.0 release would require SQL Server 2012 or SQL Server 2014. In September 2014, it was also announced that SQL Server 2014 would be added to the list of supported SQL Server versions for Blackbaud CRM 3.0. The decision to support SQL Server 2014 on 3.0 was made to allow customers the flexibility of upgrading their SQL Server instance, independent of their 4.0 upgrade. |
What change prompted the update to the System Requirements?
| Customers using SQL Server 2014 with CL 120 reported a series of performance issues. After further investigation into the issues, it was determined that the cause of these issues is a change to the Cardinality Estimator (CE) changes in CL 120. |
What were the SQL Server 2014 Cardinality Estimator changes?
| From Microsoft: The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads, and our learnings over the past 15 years of improving the SQL Server cardinality estimator. Feedback from customers shows that while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator. Note: Cardinality estimates are a prediction of the number of rows in the query result. The query optimizer uses these estimates to choose a plan for executing the query. The quality of the query plan has a direct impact on improving query performance. Source: https://msdn.microsoft.com/en-us/library/dn600374(v=sql.120).aspx |
Where can I find additional information Microsoft that lists the differences between CL 120 and previous versions?
| This page from the MSDN provides information on the difference between CL versions, and how to change CL for your database. |
Where can I find information specific to rolling back from CL 120 to CL 110 for a Blackbaud CRM database?
| This page provides a sample script that executes a rollback to CL 110. |
What were the results of the Blackbaud Enterprise Performance Team’s testing CL 120?
| This whitepaper describes the tests run by the Performance Team, and the conclusion that the while there are potential performance benefits to the improved CE, there is not enough evidence today to support using CL 120 as a means to improve system-wide performance. |
What Blackbaud CRM improvements will I miss out on by not configuring my SQL Server 2014 instance to CL 120?
| By using CL 100 or CL 110 today, you will not be forgoing any new SQL Server 2014 features that Blackbaud CRM versions 3.0 and 4.0 leverage. If future updates are made to CL 120 by Microsoft, we will evaluate them to determine if they result in functional or performance enhancements to Blackbaud CRM, and will update you accordingly. |
Will rolling back my Blackbaud CRM database to CL 100 or CL 110 affect other databases on my SQL Server 2014 instance?
| No. Compatibility level affects behaviors only for the specified database, not for the entire server. |
Will Blackbaud CRM support SQL Server 2014 CL 120 in the future?
| This has yet to be determined. What we know today is that changes to the CE in CL 120 have resulted in severely degraded query performance for our Blackbaud CRM customers. It is likely that updating Blackbaud CRM to take full advantage of these changes will require significant re-writes to a number of queries, and possibly require changes to the Platform. As with any prioritization decision, we will need to evaluate if the potential improvements of CL 120 are commensurate with the investment and opportunity cost of updating Blackbaud CRM to take advantage of them. While some early testing of SQL Server 2014 Service Pack 1 at compatibility level 120 produced better performance for some specific queries, these tests were not representative of our entire suite of performance testing. We still observed significant performance regressions at CL 120 after applying SP1 and CU1. We do not currently have an expected time of when support for CL 120 will be determined, but we remain committed to updating you as new information becomes available. |
How will Blackbaud improve on the current process of supporting 3rd party technology?
| A positive outcome of this event is an improvement to the Infinity process for supporting 3rd party technology. In response to our shared experience with CL 120, we are improving our process for 3rd party technology support. A technical committee has been established to improve our current processes around review, adoption, and communication of current state and future plans for 3rd party technology. We will update our customers as additional committee information, such as the frequency of external communication, is established. |
News
Blackbaud CRM™ and Blackbaud Internet Solutions™ Blog
07/17/2015 8:36am EDT
Leave a Comment