Do you have official documentation that SQL 2016 in 2014 Comparability Mode will work with Inventory 9.2.12?
You mean besides the Wiki pages I provided?
Correct. The only mention is the defect below.
Defect - 188918 BigFix Inventory does not block installation with MSSQL 2016 database and later which is not a supported version of the database.
@AlexaVonTess have you tried upgrading to 9.2.12 with a SQL 2016 database in SQL2014 compatibility mode yet? I may want to give that a try but if you already know it doesn’t work it would save me the time. Thanks!
I was waiting for you! 
That was an option I had discussed with our DBA, but I later decided to just wait for the version that support SQL 2016. I’m told they have top men working on it right now.
2 Likes
IBM has provided more details for this issue:
Thanks @itsmpro92 for the update!
I understand that the 9.2.12 update blocks itself when SQL Server 2016 is detected. Do you know whether the upgrade can run if SQL is configured for a lower compatibilitylevel (SQL 2014 compatibility mode for example)?
I am not using subcapacity licensing so I’m not concerned if the subcapacity reports are incorrect, but I’m very interested in the shared disk enhancements in 9.2.12.
I’m not sure if it is blocked, per se. According to the Internal defects fixes list, Defect 188917 is resolved with
Information that MSSQL 2016 database and later is not supported by BigFix Inventory
This corresponds with the warning message in the Fixlet itself, about SQL Server 2016 being unsupported. Also, based on my admittedly cursory examination of the contents of the BFI-server-9.2.12.0-20180620-2309-windows-x86_64.zip file, there don’t appear to be any tests for SQL Server compatibility or version levels.
It would appear from the information provided by Microsoft that this change in datetime2() behavior only applies to compatibility level 130 and above. So keeping your database at SQL Server 2014 compatibility seems like it would work.
I would be concerned about getting into an unsupported configuration if you go down this road. That being said, the following from the Knowledge Center seems like good advice:
Important
It is strongly recommended, especially in big environments, to first perform the upgrade in the test environment. To do this, back up your production database, restore it on the test server, and perform the upgrade there. If it is successful, perform the upgrade on the production server.
Then run the upgrade in interactive mode, rather than from a fixlet.
According to the release notes at https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Tivoli%20Endpoint%20Manager/page/BFI%20update%209.2.12 , there is a fix for issue 188918 described as
"BigFix Inventory does not block installation with MSSQL 2016 database and later which is not a supported version of the database."
If the check is based on Compatibility Level, I’d expect to be ok if I downgrade the databases compatibility level to 2014; I just don’t know whether the check is processed in another way.
I should be finding out this week though 
As always, thanks very much for your insights on this.
@itsmpro92 @AlexaVonTess Well, I finally went and tried it.
Forced the SQL Database Compatibility Level to the SQL 2014 level, applied the BFI 9.2.12 upgrade, services started, looking good…
then I connected to the web interface, was redirected to a message to update the schema, “click here”, and …BAM “Database error: Microsoft SQL 2016 is not compatible with BigFix Inventory. Use a supported version of the SQL Database”
Rolling back the VM snapshot now, and I guess I’m not going to have BFI until 9.2.13. I’m really hoping to not get stuck with ServiceNow Discovery instead.
Anyone have any other ideas to fake a SQL server version report, or a workaround to get BFI 9.2.12 to install on SQL 2016, or any guesses on when we can hope for 9.2.13 ?
@JasonWalker - That’s useful information. It appears that IBM is just being conservative, and avoiding issues with customers that don’t understand compatibility levels.
What’s in the logs on BFI?
I agree with you that this is an urgent requirement. It’s been a few years since SQL Server 2016 was released, so this should not have come as a surprise.
I don’t suppose DB2 is an option for you?
Afraid I don’t have the rest of the BFI logs, I’ve already rolled back my VM snapshot.
I’d hope that since someone went to the effort of coding in a SQL Server version check, there might be some super-secret command line to bypass the check for those of us willing to try out database compatibility level…or better yet, the update itself could downgrade compatibility level since it should have db_owner rights anyway.
Afraid DB2 isn’t much of an option for me, especially if it looks like 9.2.13 may be coming anytime soon.
That’s too bad. Stuck on .12 for the time being, it seems.
Actually stuck on .11 - what’s maddening is that .12 has the enhancement for sharef disks that is important to me.
Thanks for giving it a try and sharing your results! (and reinforcing my decision to hold off) 
Too bad the “incompatible” message didn’t appear during the initial pre-checks of the installer, huh?
BTW: The 9.2.13 supporting SQL 2016 was just a guess (hope) on my part; don’t hold IBM to that. “Later this year” is all I received from IBM".
Any chance we can get a link to the offline installer for 9.2.11? We are on SQL2016 already and running BFI 9.2.7. The fixlet only pushes 9.2.12. I can’t wait for .13, and would like to move to .11 before Oct when we go into holiday change blackout.
Let me present official approach to MS SQL 2016 working with BFI/LMT.
As it was already announced in the technote (https://www-01.ibm.com/support/docview.wss?uid=swg22016328),
the changes introduced in MS SQL 2016 make this database incompatible with 9.2.12 and older versions of ILMT and BFI.
To be more specific, the inventory data is impacted, as even the simple comparison operation of the information about the same date and time performed on this database provides the incorrect result
Due to such MS SQL 2016 behavior change, ILMT and BFI cannot reliably work with the database records, and cannot correctly process the new, updated or deleted data.
For an example of such issue, see:
· MS SQL 2016 is not supported by 9.2.12 or any earlier ILMT/BFI server, regardless of the SQL Server compatibility level (changing compatibility level to SQL Server 2014 does not resolve the problem with the incorrect result of comparison operation).
· Any audit snapshot generated on SQL 2014 and any other database supported for ILMT or BFI is valid.
· Any audit snapshot generated on SQL 2016 is invalid, regardless of the reporting period covered.
o If you step into not supported configuration, if possible, revert to supported database version and recover data from the latest backup performed before switching to MS SQL 2016.
· According to the current plan, ILMT and BFI support for MS SQL 2016 is going to be released at the end of 3Q 2018. Wait for the new release before upgrading ILMT or BFI database to MS SQL 2016.
· ILMT and BFI team is considering a possibility to help customers who entered the unsupported path with a solution to preserve configuration (bundlings, vm managers, custom signatures, users & roles, etc), but not inventory data.
Watch the forum for updates regarding this topic.
2 Likes
Looks like 9.2.13 is out…and it supports SQL 2016.
Though the wording is a little odd…does this mean I cannot upgrade 9.2.11 to 9.2.13 because I’m already on SQL 2016?
BigFix Inventory server is now supported on MS SQL 2016 for fresh installations.
3 Likes
Yep. That makes it sound like you should have no problem upgrading to 9.2.13 from a previous version that has already been using SQL 2016. The caveat being that there are no guarantees on data validity for a very specific collection prior. Now we just need someone to perform the upgrade to validate.