2023 SQL Premium configuration & backups

Question

Hello friends, 
 We have begun working with our awesome consultant on our Sage 2023 Premium upgrade from an older Providex version of MAS. It's very exciting! 
 We are at the point now where we need to get backups running for our new accounting system. And it looks like the backup system for Sage 100 Premier is a different beast than what we've been accustomed to. On the old ProvidEx system, we were able to simply make copies of the company data folders. Now we will need to backup the SQL data separately from the Sage system. 
 We are a distributor with a lot of data and don't want to let go of our historical data. 
 With this in mind... 
 Should we site our Sage SQL data on the same virtual machine that is running our Sage accounting software? 
 OR 
 Should we keep our Sage SQL data on the same virtual machine that is running our SQL Server software? 
 
 Note: both virtual machines (Sage virtual machine and MS SQL Server virtual machine) are on the same Hyper-V host machine. 
 
 Thank you for any insight! best wishes, Kristin

rclowe · Accepted Answer

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.

Kevin M · Answer

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...).

Sage 100

2023 SQL Premium configuration & backups

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING