How many times have you see this error message when you try to sync your Act! database?
Error: “Cannot insert duplicate key row in object ‘sys.syscommittab’ with unique index ‘si_xdes_id’
Well there is good news, you can finally permanently resolve this issue and prevent it from returning to your Act! sync database.
This issue is actually not an Act! specific issue. It’s an issue in SQL Server 2008. While there are hot fixes and cumulative updates that can patch this issue. Remembering to patch it before you setup a remote database on a new users new computer and bring back the error message for everyone, is something that no one ever seems to remember to do. The easy fix is use SQL Server 2012 or 2014 as neither have this issue. The problem is until now you would have to do a manually install of SQL Server 2012 or SQL Server 2014 on every computer that was syncing, then install Act!. That’s not efficient and not many people are willing to put forth that kind of effort.
The good new is that as of Act! Version 17.1, Swiftpage has added support for SQL Server 2014 (Yeaaa!).
This is the first time that Act! has upgraded the SQL version in a service pack. I am happy to see this change but this change requires planning. It is my opinion that all Act! users should be on the same SQL version to keep the support cost down.
Microsoft SQL databases are not backwards compatible.
You cannot save a SQL Server 2014 database as a SQL Server 2008 database, unlike how you can save a Word 2013 document as a Word 2003 document. Once a SQL database is upgraded to a newer version, the formatting of the file is upgraded and it cannot be opened by a older version of SQL. Sure there is always a way, but to get a SQL Server 2014 database back to SQL Server 2008 you would have to do a SQL Server Import/Export from one server to another server and this would not be supported by Swiftpage and honestly this is something that only someone who is very skilled at SQL should try to accomplish. In the end it’s easier to just roll out SQL Server 2014 to all offline clients (computers that sync Act!) and enjoy the added stability and benefits.
So how do offline clients get SQL Server 2014 installed?
To get Act! 17.1 to install SQL Server 2014 automatically as part of the install, you must first uninstall Act!, and the ACT7 SQL Server 2008 instance and all the other SQL Server 2008 items in the add & remove programs area of your computer. NOTE: If you have other software that uses SQL Server 2008, you might want to proceed with caution. Reboot your Windows computer. Then download the Act! 17.1 installer (not the updater) and run the Act! Install as you usually do by clicking next, next, next, I agree, & finish all the while not reading the End User License Agreement you just agreed to. Once you have Act! 17.1 installed you can verify that you have SQL Server 2014 installed by using the ActDiag tool (Start > Run > actdiag) on the first screen ( after the nag prompt) the Server Edition should say “Microsoft SQL server 2014 …”.
The Swiftpage documentation states that you can mix SQL Server 2008 and SQL Server 2014 in a Act! database sync environment.
Yes, yes you can. You can have offline clients using SQL Server 2014 and the Act! Server using SQL Server 2008. You cannot have the Act! Server using SQL Server 2014 and offline clients using SQL Server 2008. This is because when remote databases (which are installed on offline clients) are created, they are copies of the Act! SQL database that is on the SQL server. So if the Server is SQL Server 2014, this means that those remote databases are SQL Server 2014 and can only be restored by SQL Server 2014 instances on offline clients. As I mentioned earlier you cannot restore a SQL Server 2014 database to SQL Server 2008.
So back to my opinion, like it really matters but your still reading so we will get to the point of all this boring backstory.
If you have a setup in which the Act! server is using SQL Server 2008 and some offline clients using SQL Server 2014, this environment will naturally evolve over time when you install 17.1 fresh on users new computers, eventually you will need to import data from a remote that is a SQL Server 2014 database. The reasons for this event are many, maybe because the user imported 100,000 records and now their remote won’t sync. The problem is you won’t be able to restore the SQL Server 2014 remote database on the server and use the nifty merge tool, because of the difference in the versions. You will have to upgrade the server first, then all the end users machines so they can restore the new remotes you will create.
If you’re an Act! Consultant, its even more of a rub.
You want to minimize your support cost with your customers by rolling out SQL Server 2014 to all of them but if you upgrade your machine to SQL Server 2014 then every database you touch will be SQL Server 2014 and clients that had 17.0 and did not uninstall 17.0 and SQL Server 2008 before installing 17.1 will have to go through the motions in order to open the database backup you return to them. If you have client using SQL Server 2014 and you need to do some work on their database but your using SQL Server 2008, you wont be able to restore the Act! backup.
So why not useSQL Server 2012?
The use of SQL Server 2012 with Act! is so minimal because it requires the manual installation that we speculate the market share of SQL Server 2012 with Act! is probably less than 3% of all act users. Remote Tech Group does not support SQL Server 2012.At Remote Tech Group, we try to plan for everything.
We support Act! Hosted version 17.1 on both SQL Server 2008 and SQL Server 2014. Our MaxControl self-service portal is even smart enough to determine the SQL version of the backups user upload.