Best Practices When Setting up DSD Business Systems SQL Mirroring

Best Practices When Setting up DSD Business Systems SQL Mirroring

The DSD Business System Sage 100 Enhancement, SQL Mirroring, is a very useful utility to move standard Sage 100 data into a SQL server for further processing.   It works on a real-time basis and is very dependable once set up correctly.   The setup of SQL Mirroring can sometimes be confusing and opaque to end-users, or even IT departments.  Here are some some best practices when it comes to initially setting up SQL Mirroring.

Sage 100 Standard

The first requirement is to determine if you are using Standard Sage 100 or Advanced Sage 100.  Since SQL connections from the Sage server are started from the workstation that requests it, if you are using Standard Sage 100 then you will need a DSN (Data Source Name) connection from each workstation that is accessing the Sage 100 server.  We recommend you set a system DSN rather than a User DSN.

Sage 100 Advanced

If you are using Sage 100 Advanced, then you need to determine if the Sage server is running as an application, or as a windows service.  If the Sage server is running as an application, the Windows user that starts the application must have Create, Update and Delete rights to the mirrored SQL server.  Some clients use an administrator login for this application, and as long as that Administrator is in the SQL Server Administrators then you will have full rights to do mirroring.  If the Sage server is running as a windows service, then the Windows user that the service logs in as will need to have Create, Update and Delete rights to the mirrored SQL server.  The default user that the Sage service is started by doesn’t usually have any rights to the mirrored SQL server and will need to be changed.

The other set of windows permissions that can trip up IT departments is BOI scripting.  This can be really tricky to implement.  Since SQL mirrored scripting depends on a dynamic linked library (DLL) that we ship, the first thing is to insure that the PxPlus DLL has replaced the standard Sage providex.dll.  The next step is to insure that the process that is running the script has all the required rights to mirror to the SQL server, and has a DSN to connect to it.  If all those prerequisite are met, then BOI scripting will mirror data into the SQL server.

And as always, we here at DSD Business Systems will be happy to help you sort through all of these issues should you find yourself at a loss, or things are not quite working correctly, or even not at all, but this should help in initially sorting things out in order to mirror data.

DSD Business Systems has been a Sage Development Partner for the past 30 years.  We currently have over 400 enhancements to Sage 100 and have created thousands of custom solutions for end-users around the world.  We have had the privilege of working with hundreds of Sage consultants, resellers, and end-users to produce powerful custom solutions that enhance the functionality of Sage 100.  Please contact DSD Business Systems to learn more about our Enhancements and Custom Programming.

Charlie Spirz


Written by Charlie Spirz
, Senior Programmer at DSD Business Systems

Category:
DSD Business Systems
Tags:
Custom EnhancementsDSD Business SystemsMas200 (Sage 100 Advanced)

Congratulations! You’re registered to join us.

Acumatica Lunch and Learn Irvine, CA
We’re so excited to show you the power of Acumatica!

Should you have any immediate questions or needs, please feel free to reach out to your event host: ktucker@dsdinc.com