Sage 100

Sage 100 ERP premium upgrades are proving to be challenging. On a related issue, does anyone know if you can simply move the premium databases to a different SQL server and tell the Sage server and workstations to work from the new server?

So...that's a no then?

I do not know if there is a shorter way than this

Thanks @WayneSchulz that's all I could find too. Seems like there should be a way to reassign the SQL Server.

You can but it's a bit more involved than it appears at first blush: 1. On new SQL server, in SQL Mgmt Studio, manually create the MAS_User and MAS_Reports SQL login accounts. 2. Backup the MAS_XXX and MAS_SYSTEM databases from old SQL Server and restore to new. Or detach / attach. 3. Set the database permissions for MAS_User and MAS_System. Somewhere on SocialCast I posted a SQL script to @JimWoodhead on how to do that once. 4. Run the Sage SQL Settings Utility from Start / All Programs / Sage. Change the Database Server field to the new SQL Server. Also put in the same passwords for Mas_user and Mas_Reports you did in Step 1. Make sure Test Connection button works. The point of this whole step is to update the MASSQL.Settings file in \mas90\home on App Server

Ahhh, that's what I'm talking about! Thanks @AlnoorCassim !!

Alnoor was referring too. - Run for every MAS_XXX database include MAS_SYSTEM exec sp_dropuser 'MAS_User' exec sp_grantdbaccess 'MAS_User' exec sp_addrolemember 'db_owner', 'MAS_User' exec sp_dropuser 'MAS_Reports' exec sp_grantdbaccess 'MAS_Reports' exec sp_addrolemember 'db_datareader', 'MAS_Reports'

Thanks for the info @jimwoodhead. We accomplished the upgrade by moving their 4.5 databases to a new awl server instance then removing them from the ""live"" instance installing 2014 to the original sql server and migrating data from the temp instance back to the ""live"" instance!

That seems to be a good method. Thank for he update.

Great thread. The instructions form Sage state to install Sage 100 Premium on the new server. If it is just the SQL Server being changed and not the App Server, would Sage's instructions be misleading?

I'm not sure I'd say misleading. But I would say unnecessary. If you are upgrading the Sage Premium you can install it on the same App server. As @AlnoorCassim explained you can change the SQL server without reinstalling.