Sage 100

End user is currently has 2014 Premium and wants to upgrade to 2016 Premium. Currently, 2014 resides on the same server as SQL. What is the best practice to upgrade 2014 to 2016? Can I install 2016 programs and use the same SQL server installation? I would prefer they create a new VM and re-install everything to keep the two versions and databases separated, but not sure if that is feasible.

I asked Sage support two weeks ago and they confirmed separate instances of SQL are still required. I did a test on a laptop trying to avoid that, and it wasn't possible or at least obvious to me as to how it could be done.

If Sage has a best practice i'd like to see it. The easiest way is to have a second SQL server available. That as you know is not always feasible. You can and we have setup a separate instance of SQL Server on the same physical server. It's a bit trickier unless you or they have a good dba that knows SQL pretty well.

@AlnoorCassim - Comments??

Just not worth thr risk if it isnt supported if a CF occurs. I will advise the client we need a new VM with as new installation of SQL. Thanks for the input.

@DougHiggs you need a separate INSTANCE of SQL but that second instance can run on the same SERVER (physical or virtual). Having multiple instances of SQL running on a single server is not an unusual. One of the reasons for the separate instance is Sage 100 only expects to find one Sage 100 SYSTEM database on a SQL instance. To run two versions in the same SQL instance would require a way to differentiate between two SYSTEM databases and Sage 100 Premium wasn't written to do that. Please note that I am not a Sage 100 developer, nor do I pretend to be so there could be many more reasons behind the cause. I might even be all wet on my observation regarding the SYSTEM database but I'm sticking to my story until educated differently.

As with most things Sage, it is ""Why spend an hour programming a routine to let you set the system database name for each installation when we can make each of our customers do an extra hour's work for each and every upgrade?

I wondet if the Sage 100 installation routine is smart enough to create a new SQL instance.

Shirley you jest...

Shawn is mostly right, though i'm not sure how you'd get around having multiple copies of any of the databases including the actual company databases in the same instance of SQL Server. But I think @RobertWhite and I were both trying to say that it's perfectly possible and acceptable to use a separate INSTANCE of SQL Server on the same physical server. In my case i was just trying to express that that is a little trickier for some of us that aren't SQL experts.

If you crrate the new instance then if Sage prompts for the server instance name during the installation I guess it SHOULD be OK.

I'm not a SQL expert. My masters has a database concentration so I know enough to know to be cautious. Just curious if anyone has performed a premium upgrade on the same virtual server. Would be nice to know if there are any gotchas..

I believe the installer only asks for the server name. Include the instance in that field as Server\Instance.

You would hope the installer would check for multiple instance. Holy smokes, if you forget the instance name I wonder if you overwrite the MAS_System DB. It certainly wouldn't surprise me.

It wouldn't technically be an overwrite of the database. It would be a drop and then a create. Same result.

@DougHiggs, have you run into any issues with indexing errors during the data conversion process? I'm seeing that with IM and SO history in a 4.45 Premium to 2016 Premium upgrade. The parallel migration process seems to be the culprit. And, yes, I have multiple SQL instances on the same server.

@JohnShaver I haven't seem any indexing errors... yet. Glad to know someone has successfully upgraded using different SQL instances.

Well, the upgrade isn't complete so I can't call it successful quite yet!

I am scheduled to do a 4.45 to 2016 upgrade soon (this is a different customer than the one in the thread above). Waiting on IT to increase the hard drive space on the server. I am leaning toward creating a new VM and starting fresh. Too many variables, and I don't feel comfortable with the level of support I would get from Sage if something goes awry.

I can see it now... ""Our SQL expert is on another call. I can schedule this for a call back."". .... wonderful.

or, ""Did you say version 4.45? We haven't supported that version in several years."" In other words, You're screwed!

Thing to add to check list for this process - make sure the new SQL instance is at least as high a version (including service pack) as the one you are migrating from!

Thanks Phil.