Hi Kristin,

Not too long ago Sage published a KB on SQL Optimization, although where it might have gone to these days I cannot say. Internally, when I worked for Sage and was training on MAS 500, we had a great class (taught, I think, by Hassan Fahimi) - which dove even deeper into SSMS optimization, etc. Some would say, in answer to your question, that you should work with your DBA to optimize and maintain the databases according to your company's policies and requirements.

Soapbox time - any company that is on a Sage 100 Premium system should have an in-house DBA or at least a consultant on call with DBA qualifications and skills.

Bottom line, which I believe you indicated you already know, is that yes - you need to create and configure your SQL Server to backup (and maintain, I suggest) your databases. You also, of course, want to continue to backup the windows directories.

Regarding your questions about the number of servers, it really depends on the quantities of transactions, database sizes, expected database growth, database recovery models, backup methods, etc. I have seen companies use the 2 server approach, as well as companies use the 3 server approach. I've even seen some larger companies that have Sage on Server 1, SSMS on Server 2, DBs on Server 3 and Transaction Logs on Server 4. These would be good questions for your DBA.

Sorry about getting so loquatious there. Hope this helps a little.

Your awesome consultant should have some SOP's for this!  The questions you are asking should easily be answered by them.

Yes, i spoke with them about it yesterday. However, there are a lot of moving parts to our upgrade, with many dependencies. And our consultant has other customers, as well. I highly value this forum and appreciate whatever I can learn here.

We have been using this software for 20 years now and are the type of users who like to "own it," while also leaning on our consultants when necessary.

best wishes, Kristin

Thanks so much for this feedback. There is definitely a need for Sage 2023 Premier backup documentation from Sage. I'm surprised it doesn't exist already. My memory is that previous versions of the software included backup strategy information. 

The main reason to put SQL on a separate machine from Sage is if you use SQL for more than just Sage 100.  (Sometimes the Sage 100 server needs to be restarted, and you don't want that to affect other systems).  Otherwise (for our clients) it's more common to have SQL and Sage on the same VM... of course, your IT team's input is import too.

Sage supports their product, not SQL. As recommended by RCLowe, get your own SQL DBA / expert consultant and ask for their advice. Server snapshots are not the best options for SQL data, but again, ask a SQL DBA for recommendations according to your requirements.

All databases named MAS_### contain your company data.  MAS_System is the core system data (paired with the MAS90 folder).  You need both the SQL databases and the MAS90 folder to get a Premium system restored from backups.  (Obviously include other peripheral folders in your backups too, like Paperless PDF's, ACH, Positive Pay...).

Thank you, Kevin. This is very helpful. We appreciate it!

We are running SQL for KnowledgeSync and also for email archiving. This is why we wanted the Sage application server separate from the data.