Selecting Advanced SQL Server Settings

You use the options on the Advanced SQL Server Settings tab to reconfigure certain aspects of your SQL Server instance, including memory management, connection timeout, communication encryption , and certain types of access.

IMPORTANT:  The settings you choose on this tab apply to all the databases in the SQL Server instance, not just to one company.

TIP:  If you want to monitor the effect of changes to these settings on your SQL Server instance, open the Microsoft SQL Server Profiler from the Toolbox.

To reconfigure advanced SQL Server settings

1. Click Advanced Settings > Advanced SQL Server Settings.
2. Select or enter settings on the Advanced SQL Server Settings tab, as follows:

   • Maximum SQL Server memory. Type the maximum amount of memory to allow this instance to use, in megabytes.

   • Server connection timeout. Type the maximum number of seconds to allow the server to respond to a request from another machine before dropping the request and displaying a connection error to the user.

   • Enable other machines to connect to this SQL Server instance. This option is selected by default to allow other machines in a networked environment to connect to the SQL Server instance.

     You may want to clear this option to prevent connections while you perform maintenance on the database, or if you need to isolate the instance from an immediate threat.

   • Encrypt communication from Sage 100 Contractor to this SQL Server instance.

   • Limit access for members of the 'sysadmin' role to this machine only. This option is selected by default to ensure that the sysadmin role can obtain access to the SQL Server instance only from the machine that is acting as the server.

   • Limit access to this SQL Server instance to approved applications. Access to the SQL Server instance is always approved for Sage 100 Contractor because it is necessary to run the software. However, you might want to allow other applications, such as Microsoft Excel, to communicate with the instance as well. You can select which applications can communicate with this SQL Server instance, as follows:

     1. Click [Edit Approvals].

     2. In the Enable Access from Other Applications window, select the check box beside each application that you allow to communicate with this SQL Server instance.

        Note: You can select only applications that include an application name in their database connection strings. You cannot allow applications to connect that do not identify themselves properly.

     3. To edit the XML configuration file directly, click Edit advanced restrictions, and then follow the instructions in the XML file to define the restrictions that you need.
3. If you want to restore the "factory settings" after reconfiguring any of these settings, click [Restore Defaults] .
4. When you are satisfied with your selections, click [Save Changes].