Sage 100

Do you have a document you provide to your Sage 100 Premium customers which instructs them further on the SQL Server configuration they will need? I have a customer pushing back hard on this and asking for more detail on the configuration. All I have found to really give them is the supported platforms which I have to say is very slim in what it gives for information. It is also dated Feb 2018. Am I overlooking any other technical resources I can provide to the customer's IT department specifying the configuration needed for the SQL server? They are also using Data Center 2012 which I would not certify for them due to it not being SPM (though I did pass along feedback that it should work). Why can't Sage have an updated SPM to give customers so they don't feel compelled to step back to a server OS that they may not own but they install because it's listed on the SPM.?

This is what we usually tell our clients/IT folks Additional Server recommendations: Windows 2012 R2 Server or 2016. SQL Server 2014 or 2016 standard with mixed mode authentication enabled. Be sure to set the server collation method as SQL_Latin1_General_CP1_CI_AS (if not, there will be data integrity issues). This collation method is normally set by default when the server is setup with a default language of US English. If using a different language setting, be sure to set this collation method as part of the SQL Server installation. Default SQL data path should have sufficient space for the databases / log files. Required access: Full administrator control over the server. SQL ""sa"" password. Remote access using a method that allows us to reboot the machine and reconnect afterwards. We run the services under a Windows login. If there is a password policy which involves expiring passwords, we'll want a utility Windows login with admin access over the server and a password that does not expire.

There's a few details at the end of the Install and System Administrator's guide.

Now the customer reads the install guide and determines that the way to go is to buy the Sage SQL Runtime -- is this as self-install as they apparently presume? I could swear we have some conversation previously that buying SQL through Sage was not the way to go ...

Here's the latest SQL discussion which seemed to talk negatively about using the Sage OEM SQL version - https://90minds-com.socialcast.com/messages/39143945?ref=stream

The only difference between the SQL run time and SQL standard is the licensing restriction. Installation and setup are the same.

+1 on what @PhilMcIntosh If SQL Server is being hosted in a data center then you have a licensing restriction. AND +1 on what and @RobertWhite said. ""Optimized"" is a bit misleading. It really means ""configured for basic compatibility with Sage 100 Premium"". The installer defaults to Mixed Mode Authentication (required for Premium and prompts to create a 'sa' password) and uses the default collation method (basically it chooses Case Insensitive for you) and default language of Englinsh. IOW the 2nd point @RobertWhite mentioned. It does not ""optimize"" for performance since it does not for example ask you where to create the Sage 100 databases (like on a drive with more disk space), what setting to use for Database Recovery Model, etc. You have to move/set those things _**AFTER**_ installing.

Thanks, this is all great info. What's the rule of thumb on how many users they should purchase if they are using Sage 100 only and how many if using Sage CRM alongside Sage 100?

Sage wants to collect a SQL user for each Sage 100 user. I know this because a couple of years ago I asked for a quote for additional users for a customer who had purchased the SQL OEM. Sage gave me a quote, and then when the order was placed wanted to add on for the SQL licenses. I did get them to honor the quote. How many to add for CRM would depend on how many users of CRM were NOT Sage 100 users. Someone who uses both shouldn't have to pay for two SQL licenses.

Thanks everyone for the great feedback. I'm not sure that I'm out of the woods WRT to this customer but I pulled together a handy FAQ with all of your feedback. Feel free to copy/paste for your own usage - https://docs.google.com/document/d/1_y2wlmQSZGVtew4zVctpQKZKnQX0kl-lcTWUZ8UdIKM/edit?usp=sharing

FWIW, we have been told by Sage several times that Sage CRM uses database pooling and does not require a one to one license with MS SQL. This came from the development team in Dublin who passed it along to my sales rep in North America years ago.

For user licenses in Sage 100, I think it is the printing which causes per-user licensing being needed (and / or if you don't enable the Spawn Tasks from server option for users). I believe all normal read-write activities happen through the service (MAS_User... assuming the Spawn Tasks is enabled) but when it comes to reporting the workstation's Crystal Reports runtime engine connects with SQL directly (being passed the MAS_Reports connection information for authentication). Note: for work table reports this doesn't give you significant speed benefits (because the temp table data needs to be processed by the Sage service before that point) but for custom reports, things can be super speedy because it is pure SQL (with Sage just handing over the initial connection to the report engine).