SQL Server 2012 and ResultsPlus

Now that the newest version of Microsoft SQL Server© has been out for a while, we get calls from customers asking about compatibility between ResultsPlus and SQL Server 2012. I thought I’d share what we know from testing here.

ResultsPlus 11 supports SQL Server 2012. Our testing here has indicated no issues with it, but that doesn’t mean there are no issues with SQL Server 2012 itself. We know of one major change in SQL Server 2012 that can be disconcerting. There are situations where SQL Server can be restarted in such a way that it skips 1000 values in fields’ people typically think of as “auto-increment” fields. No records or data are lost, but when the server starts again, it simply jumps up 1000 numbers and then continues with the numbering scheme.

The numbering scenario was originally reported as a defect on Microsoft Connect by users in the wild. Microsoft has now stated that this behavior is by design, so we do not expect a return to the “old way.” It was done, per the statement here, as part of the changes made to increase performance of SQL Server.

What does this mean for ResultsPlus? It means that ResultsPlus will continue to work the same way it has in the past. However, it is now possible, though uncommon, to find legitimate gaps of 1000 values in fields that store auto-increment values. Example fields are FolderID, GiftID, AddressID, etc. This change in behavior on the part of SQL Server serves to highlight how important it is to have all of the auditing and logging features enabled in ResultsPlus as well as SQL Server, just in case you ever question a gap in a numbering sequence.

Here’s a quick rundown of the most common features you may want to have enabled:

  • Upgrade to ResultsPlus 11. We have expanded the data logging to this version based on input from previous versions.
  • Uncheck the user option to allow editing gifts after posting. If this option is checked, users can delete donations, creating gaps in the GiftID sequence. This will not prevent the deletion of unposted donations, however.
  • Only allow one user (or login) to delete constituent records. To do this, place that login in its own workgroup. Then, grant Read Only or Hidden access to all workgroups except the one that should be used for Maintenance functions. This will ensure that all deletions (and constituent merges) go through a single source and are properly vetted at your organization prior to occurring.
  • Set your database to be fully logged in SQL Server. This will enable you to identify all deletions by “replaying” the actions up until certain points. This is not something that can be handpicked, though, so don’t feel this is a way to choose to undo certain actions and not others.Recovery Model
  • Consider enabling “Change Tracking” in SQL Server. There are a number of options for configuring this. Choose the options best for your organization and usage.