Boosting your Exact Globe / Synergy database server thru Windows Server Settings.

Configuring your Windows server is easy especially if it is a stand-alone (not a domain) or a dedicated server for application like Exact globe. Like SQL Server, Windows Server is mostly self-tuning, but again there are things we can do in order your SQL database server (as a whole) will perform better.

*Selecting the best performing editions of Operating System. While SQL Server can run on standard editions of Windows Server 32-bit, still consider using Enterprise edition or Data Center edition to take advantage of higher memory capability, multiple processors support etc. See chart comparison below

For Windows Server 2003 http://www.microsoft.com/hk/server/windowsserver2003/evaluation/features/default.mspx

For Windows Server 2008 http://www.directionsonmicrosoft.com/sample/DOMIS/update/2008/02feb/0208ws2plp_ch.htm

*Set the processor scheduling to “background services”. By default after Windows server installation it is set to “programs”. By choosing “Background services” it tells the Operating System to favor background applications, such as SQL Server to prioritize in using the server processor, over foreground applications.

To change under Window Server 2003. Open control panel, click system and under the “Advanced” tab, click on the “Setting” button under “Performance,” the click on the “Advanced” tab.

*Set the memory usage to “Programs”. Also on the same tab (Advanced), change the memory allocation to favor “Programs” over “System cache.” Selecting “Programs” will tell the Operating System to give more memory to applications, such as SQL Server, rather than to the system cache. SQL Server loves memoryJ, the more you have, SQL Server loves it.

                                      

*Paging File Size. Also on the same tab (Advanced) under virtual memory, click on the “change button”, set the initial size (MB) and the maximum size (MB) to an equal value. The minimal initial size needs to be equal or greater than the recommended size. But if you are running SQL Full-Text Search service, it is recommended that the PAGEFILE.SYS file is three times of the physical RAM.

                                     

*File and Printer Sharing for Microsoft Networks. If the Maximize data throughput for network applications network connection option is selected, the Operating System gives priority to applications like SQL Server to perform buffered I/O operations by caching their I/O pages in file system cache.

                              

*Network Card Link Speed. Make sure it is set to the correct value. Select 100Mbps/Full duplex if your HUB or SWITCH supports this Link Speed & Duplex.

                              

 

If you want better performance out of your SQL Server, don’t install any server components (with the exception of necessary utilities), such as Domain, Exchange, DNS, DHCP, etc on your SQL Server machine as  these will cause extra overhead. The goal is to dedicate all of the server’s power to SQL Server. Once you are done, don’t forget to restart your server.

Related Blog :
      * Boosting your Exact Globe / Synergy database server thru SQL Server Settings 
       http://anthonyexactglobe.blogspot.com/2011/06/boosting-your-exact-globe-synergy.html
     * Boosting your Exact Globe performance without upgrading
        http://anthonyexactglobe.blogspot.com/2011/06/boosting-your-exact-globe-performance.html

Boosting your Exact Globe / Synergy database server thru SQL Server Settings

SQL Server is mostly self-tuning. But, there are things we can do to help optimize its performance. And every time we help boost the performance of your SQL Server, we are at the same time boosting the performance of your Exact Globe or Synergy then.

Below are the recommended settings for optimum performance of your SQL Server.

* Physical DB location. File access is an essential consideration to an OLTP system like Exact Globe. If your system is busy or you expect it will be in the future, you may consider separating the DB location to prevent waiting time at the disk sub-system.

•  Don’t mix the location of SQL server system DB (master, msdb, temp, model) from your Exact Globe / Synergy DB.
• For best overall performance, locate the database files (.mdf) and log files (.ldf) on separate arrays or disk. To store your Exact Globe / Synergy database files (.mdf), the best performance is storing them on RAID 10 arrays, and then RAID 5 is most likely the next best bet.
• If your database is very large and busy, consider using multiple database data files or file group or you may also consider using DB partitioning (but DB partitioning is tough for Exact Globe / Synergy since the tables and indexes are created during installation)

* Memory Settings. By default, the memory architecture of Windows operating system 32-bit editions is limited to 4GB total physical RAM. 2GB is reserved for the OS and 2GB is reserved for applications like SQL Server. However, by adding switches (/3GB, /PAE) to the Boot.ini, the Enterprise and Data Center editions of Windows 2003, can access more than 4GB of physical memory.  But for Windows 2003 Standard Edition, it is limited to 4GB of RAM and does not support Physical Address Extensions (/PAE).

     - How to configure more than 2 GB memory for SQL Server on X86 (32 bits) version:

• For servers with 4GB of physical memory, add the /3GB switch to the BOOT.INI in order to force the operating system to use only 1 GB, while applications like SQL server can use the remaining 3 GB. Windows Server 2003 Standard, Enterprise and Datacenter editions are supporting this switch.
• For servers with more than 4 GB of physical memory add the /3GB and /PAE switch to the BOOT.INI and enable AWE in SQL Server. Doing this will allows the operating system to access physical memory beyond 4 GB but up to 16GB only. Again Windows Server 2003 Enterprise and Datacenter editions are supporting this switch.
• And for servers with more than 16 GB of physical memory, only add the /PAE switch to the BOOT.INI and enable AWE in SQL Server. Don’t use the /3GB switch because the operating system need more than 1 GB of memory to handle and manage the allocation of 16 GB and above.

    Memory Size        Boot.INI          Enable AWE        Lock Pages in Memory

      < 2 GB                                               x                                       x

      3 to 4 GB               /3GB                     √                                      √

      5 to 16 GB           /3GB /PAE               √                                      √

      > 16 GB                 /PAE                     √                                      √

• Note: In Windows Server 2003 32-bit editions, PAE is automatically enabled only if the server is using hot-plug memory. In this case, you do not have to use the /PAE parameter on a system that is configured to use hot-plug memory. In all other cases, you must use the /PAE parameter in the Boot.ini file to take advantage of memory over 4 GB.

                Example of the BOOT.INI file:
                [boot loader]
                timeout=30
                default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
                [operating systems]
                multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
                Standard" /3GB /fastdetect /NoExecute=OptOut
                or
                multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 003,
                Enterprise" /3GB /PAE /fastdetect /NoExecute=OptOut

• Operating systems on 64-bit editions can use the available memory in the server without the need of /3GB, /PAE or AWE enabling. You only need to grant Lock Pages in Memory rights to the SQL Server account.
• The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE).
• Go to Start \ Run, In the Open box, type gpedit.msc.  The Group Policy dialog box opens. 
• Under Computer configuration \ windows settings \security settings \ local policies
• Select user right assignment folder
• Double click “lock page in memory” – check if the account with privileges to run the sqlserv.exe services is there. If not, then add the account. By default it’s the system account.

                                 

• Note: to check the account used to run the services. Go to Administrative tools \Services \SQL server \Properties \ under tab log on.  

     - How to enable AWE on SQL Server

•  On Server properties of your SQL Server and in the section ‘Server memory options’ put tick mark to enable the option: ‘Use AWE to allocate memory’.

                       

• You can also use the following SQL Commands to enable AWE

          Use master
          Exec sp_configure 'awe enabled', '1'

          Reconfigure

• If your SQL Server is dedicated (means no other windows applications are running except SQL), there is no reason to change the “min server memory” and “max server memory” settings at all. But if you are running other applications on the same server, there is a benefit of changing this setting to a minimum or maximum figure.

* Transaction Isolation Level. By default level 2 will be used to retrieve data. The higher the level the more data is locked during read operation to guarantee consistency. Only level 1 will not block other users during read operations and also you will not be blocked by other users.

• Which levels of data retrieval is used by Exact Globe 2003 and Synergy?  Within Exact Globe and Synergy, the runtime always connects to the database with level 1. This means that read operations never will be blocked by others users who are also reading data with level 1.
• Which levels of data retrieval is used by external applications? External applications like Crystal Reports or Excel connects to the database with the default level 2. Therefore it is important to configure the level of retrieving data for external applications to level 1. So start altering your SQL Store Procedures and issue something like the following (in red).

Set Transaction Isolation Level READ UNCOMMITTED

GO

Begin Transaction

Select * From …..

Select * From …..

Commit Transaction

• Which levels of data retrieval is used by SQL query analyzer?  Within SQL server you can retrieve data via a query. The default transaction isolation level of SQL is read committed or level 2. This means that when you execute a query you need to wait until the transaction is completed before any data will be displayed. Setting the option: 'Set Transaction Isolation Level:' to 'Read Uncommitted' eliminates the waiting time and the query is directly executed.

·  On SSMS main menu

·   Go to Tools \ Options \ Query Executions \ SQL Server \ Advanced

·   Set Transaction Isolation Level to : Read Uncommitted

* Although heavy-duty hardware can help SQL Server's performance but it does not always fix SQL Server performance problems. Application and database design and tuning can play a greater part in overall performance than hardware. Once you are done, don’t forget to restart your server.

Related blog :
     * Boosting your Exact Globe performance without upgrading
        http://anthonyexactglobe.blogspot.com/2011/06/boosting-your-exact-globe-performance.html
     * Boosting your Exact Globe / Synergy database server thru Windows Server Settings
        http://anthonyexactglobe.blogspot.com/2011/06/boosting-your-exact-globe-synergy_28.html

Boosting your Exact Globe performance without upgrading

In the recent Exact Globe product update, system performance is improved by optimizing the database indices. On Batch 397 update, a new tool has been implemented to improve performance by adding indices to your database, because a good index structure enables the SQL Server database engine to retrieve requested data by the Globe application in an efficient way.

Note: you need to execute the tool then, but starting from batch 400, these indices are added automatically to the current standard index structure when you update the database to run on Exact Globe product 400.

However, for some reasons you can not update to this new release immediately. Like for instance, your have a customized solution that needs to be changed inorder to be compatible with the new Exact Globe release or you are already accustomed with the current installed version (I know one company in this situation) or you are just afraid to update because the data might get lost. But still you can make use of these new indexes in your current installation.

First of all, this is only available if your Exact Globe database runs on SQL 2005, SQL 2008 or SQL 2008 R2, because the deployment depends on the SQL Dynamic Management Views (DMVs), which is only available from SQL 2005 onwards.

To create these indexes with the tool: (EPTIndex.exe)

• Download the latest version of the EPTIndex.exe tool
• Copy EPTIndex.exe to the BIN Folder of your Exact Globe installation folder.
• Download the latest version of the PerformanceTuningIndices.xml file. Put your mouse on the filename:  PerformanceTuningIndices.xml and right click, Use Save Target As. Save the file in the XML folder of your Exact Globe installation folder. (If you double click on the filename, the XML will be opened instead of downloaded)
• Browse to the BIN folder of your Globe installation and double click on the EPTIndex.exe to start to the EPTIndex tool
• At the Connection section enter the correct name of the Server and Database name. (Make sure that you connect as a user with the System Role SysAdmin within SQL)
• Press the Preview button to get a list of possible indices which can be implemented
• Press the Generate all button to implement all suggested indices.

Scheduling the EPTIndex tool:

You can schedule this tool to run once a month via Windows Scheduler Task or SQL job to check if there are indices which need to be implemented. When you start the tool with parameters shown below, the Generate all process is automatically started to implement the suggested indices.

• Parameters: -r[DatabaseServerName] -D[DatabaseName]
• Example: "C:\Program Files\Exact Software\Bin\EPTIndex.exe" -rGlobeSVR -D111

Run Exact Globe, do some cards retrieval.  By this time you should encounter noticeable speed then. If you are not satisfied yet, read my other blog.
      * Boosting your Exact Globe / Synergy database server thru SQL Server Settings  
       http://anthonyexactglobe.blogspot.com/2011/06/boosting-your-exact-globe-synergy.html
      * Boosting your Exact Globe / Synergy database server thru Windows Server Settings
       http://anthonyexactglobe.blogspot.com/2011/06/boosting-your-exact-globe-synergy_28.html

Exact AsImport.exe - Where to best run ? Windows Task Scheduler 1.0 or SQL Jobs

These are the two most used tools to run AsImport.exe. Task Scheduler is very straight forward to use and it is designed to meet the basic needs of scheduling  programs or applications to run at predefined dates/times. If you have any level of sophistication in your automation requirements such as dependencies, multi-scheduling, alerts and notifications, history etc. you need to evaluate third party job batch scheduling products then.

That is why I prefer SQL Agent Jobs because, aside from ease of management and monitoring of the events, you can use it for simple to complex scheduling needs. Although there is a drawback in SQL jobs whenever you run the AsImport.exe without the –Oauto parameter, but my automation requirements is addressed then. Here is why.

 * In SQL jobs, you can configure the event to send you an email notification if the jobs have either failed or succeeded. To achieve the emailing notification, the following are required:

• Enable the Database Mail Stored Procedures
• via SQL Script

                     USE Master
                     go
                     sp_configure 'show advanced options', 1
                     reconfigure
                     go
                     sp_configure 'Database Mail XPs', 1
                     reconfigure 
                     go

• Configure the SQL mail or database mail
•  How to: http://www.mssqltips.com/tip.asp?tip=1438
• Add SQL Server Agent – operator and Enable the Alert System of  SQL  Server Agent Properties
• How to : http://www.mssqltips.com/tip.asp?tip=1523
• Create SQL Job (the script below is to transfer humres data from Synergy to Globe)
• USE [msdb]
  GO
  /****** Object:  Job [AsImport (Humres) Synergy to Globe - 555]    Script Date: 06/15/2011 11:41:19 ******/
  BEGIN TRANSACTION
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
  /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 06/15/2011 11:41:20 ******/
  IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
  BEGIN
  EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  
  
  END
  
  
  DECLARE @jobId BINARY(16)
  EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AsImport Synergy to Globe - 111',
                          @enabled=1,
                          @notify_level_eventlog=0,
                          @notify_level_email=2,
                          @notify_level_netsend=0,
                          @notify_level_page=0,
                          @delete_level=0,
                          @description=N'No description available.',
                          @category_name=N'[Uncategorized (Local)]',
                          @owner_login_name=N'DomainName\Anthony',
                          @notify_email_operator_name=N'Anthony', @job_id = @jobId OUTPUT
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  /****** Object:  Step [Humres]    Script Date: 06/15/2011 11:41:20 ******/
  EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Humres',
                          @step_id=1,
                          @cmdexec_success_code=0,
                          @on_success_action=1,
                          @on_success_step_id=0,
                          @on_fail_action=2,
                          @on_fail_step_id=0,
                          @retry_attempts=0,
                          @retry_interval=0,
                          @os_run_priority=0, @subsystem=N'CmdExec',
                          @command=N'C:\Program Files\Exact Software\Globe\bin\AsImport.exe -rGlobeSVR -D111 -u -~ I -URLhttp://SynergySVR/synergy -Tresources -OPT22 -Oauto',
                          @flags=0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  COMMIT TRANSACTION
  GOTO EndSave
  QuitWithRollback:
      IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
  EndSave:

(Unfortunately there is no native alerting system for Tasks Scheduler 1.0, nor any easy way to see what the task is doing. But I have heard so much change in Task Scheduler 2.0 (Windows 2008 server build).  But again my consideration is automation requirements without the use of third party tool.) 

* In SQL jobs, you can define dependencies via Job Steps like after running AsImport.exe, I want to backup my Synergy or Globe DB then.

* In SQL jobs, you can be notified whenever the jobs failed, succeeded and completed.

* The drawback :

• When running the AsImport.exe without the –Oauto parameter, it will bring the import/export screen of Globe application for users intervention. In SQL Jobs, the .exe that you need to start cannot be an interactive program/application (ex: calc.exe) as the user will never see the program popup because it is running inside the SQL Server’s own session and not in your session. The AsImport.exe actually starts, but you just can't see it. You can verify that it did start by searching the AsImport.exe in Windows Task Manager under Processes Tab on the database server. So, make sure you put the -Oauto parameter  when using SQL Agent Jobs for AsImport.exe

• When your Internet Explorer (IE) is configured to prompt for username and password under Internet Options Security Settings. Again, as a rule of thumb, do not launch program/application that needs user intervention. This can be accomplished by playing around with the Internet Explorer setting of the machine on which the job is going to run on. First, log on to the NT account that is used to run the job. Then,
• Open Internet Explorer and click Tools at the menu bar.
• Click Internet options and then the Security tab.
• Click Internet or Local intranet to select the zone to view or change security settings. Next, click Custom level
• Scroll down to the bottom under User Authentication\Logon,  configure the browser to log on automatically under the User Authentication section or automatic log on only in intranet zone.

So be sure to test your AsImport.exe at the command prompt, get rid of any pop-ups before transferring the same into SQL Jobs to keep your synchronization running.


Related blog: http://anthonyexactglobe.blogspot.com/2011/06/asimportexe.html

Exact Globe - asimport.exe

• Make sure the file asimport.exe is in the Exact Software/globe/bin directory or to where you installed the Globe Application.
• The syntax of the ASIMPORT.EXE statement:
• to transfer humres data from Globe to Synergy
• ASIMPORT.EXE -rglobeDBsqlServerName -DglobeDBname -u -~ E -URLsynergyWebAddress    -Tresources -OPT22 –Oauto
• to transfer GL Accounts data from Synergy to Globe
• ASIMPORT.EXE -rglobeDBsqlServerName -DglobeDBname -u -~ I -URLsynergyWebAddress    -Tglaccounts –Oauto
• - Careful with the E and I switch. ( E - stands for export, I - stands for import)
  - The parameters (in blue) are case sensitive, for safety reason, test it without -Oauto first before actual scheduling.
  - It is not possible to use filters (ex: Year, Period, Journal, Entry number) in the command line, that is why i'm not using this approach is most cases. Instead, I'm using Exact.Jobs.FinExchange or XML / Financial  Export module of Globe. I will have a separate blog on AsImport.exe and Exact.Jobs.FinExchange
  
  
  - Here is the actual command with the following assumptions :
       * globeDBsqlServerName = GlobeServer
       * globeDBName = 555
       * synergyWebAddress = http://HQServer/Synergy
  
  
  ASIMPORT.EXE -rGlobeServer -D555 -u -~ E -URLhttp://HQServer/Synergy -Tglaccounts –Oauto
  
  
• To synchronize the data between Globe and Synergy or vice-versa properly, certain criteria are required, I suggest reading this document: 01.457.694 (How-to: Scheduling Import and Export of Data between Exact Globe and Exact Synergy / Exact Synergy Enterprise) thoroughly in order to get the replication up and running. Pay attention to the front office application (Synergy Classic or Synergy Enterprise) you are using, because when installing Globe, the default setting is set to Exact Synergy (also known as Synergy Classic).  To check this, go to System/General/Settings/General settings in Exact Globe. Under Exact Synergy Options, select the appropriate Synergy version you belong.

Related Blog : http://anthonyexactglobe.blogspot.com/2011/06/where-to-best-run-asimportexe-windows.html