Lonehill System, Inc.

SQL 2000 Foundation Manager

Objective

To provide a scalable, robust, secure database platform for the SYSPRO ERP System.

The Value of SYSPRO SQL Foundation Manager

• Modern ERP systems caring for rapidly expanding customer needs and dynamic business environments utilize the powerful capabilities of relational database management systems (RDBMS) offerings.
• Applications based on an RDBMS can offer better performance and scalability, and more replication features than ISAM-based solutions.
• Microsoft SQL provides this solid RDMBS backbone structure to SYSPRO's Accounting, Manufacturing and Distribution systems.
• Microsoft SQL Server is a powerful and flexible RDBMS solution.  The ease of use and scalability has made it attractive for thousands of application developers.

The SQL Foundation Manager comprises all the programming code that forms a part of all SYSPRO modules.  This code manages and optimizes SYSPRO utilization of this high-end RDBMS system, with a goal to optimize data access speeds and provide sophisticated tools for complex operations.

Innovations and Benefits

Innovations enable SQL Server to lead several of the database industry's fastest growing application categories, including data warehousing, line-of-business applications, mobile computing, branch automation and eCommerce.  These include:

• Scalability from the laptop to branch offices using the same code base and offering 100 percent code compatibility.
• Unlimited scalability is provided with the introduction of Distributed Partitioned Views in SQL Server, dividing workload across multiple, independent SQL Server-based servers.
• Security administration is highly effective through integration with Windows NT security and SQL Server roles.  Windows NT integration includes authentication, support for multiple groups, grant/revoke/deny permission management activities and dynamic use of groups.
• Event/Alert Management enhances the ability to monitor performance, availability and security status through policy-based event management.
• Support for auto configuration and self-tuning.
• Integrated online analytical processing (OLAP) server.
• Integrated Data Transformation Services (DTS).
• The Microsoft Data Warehousing Framework, the first comprehensive approach to solving the metadata problem.
• Multi-server and multi-site management for large numbers of servers.
• A wide array of replication options, including replication to any database that supports OLE DB or ODBC.
• Integration with the Microsoft Windows operating systems, Microsoft Office and the Microsoft Back Office family of products.
• Universal Data Access, the Microsoft strategy for enabling high-performance access to a variety of information sources.
• Self-tuning relates to the database automatically tuning and managing itself, helping customers focus on strategic business initiatives rather than micro-managing technology.
• SQL Server includes features that dynamically optimize for recent hardware innovations including large memory, ultra high-speed disk subsystems and SANs.
• Availability and reliability minimize downtime and add to the efficiency and effectiveness, ensuring system reliability.  Databases are transferred to newer versions via full automated upgrade utilities.
• Data warehousing and access is facilitated by built-in OLAP relating to the tools used for the online analysis of information in the database.  While this is generally a third-party tool purchased separately from the database software, these tools are included as standard with SQL Server.
• Total cost of ownership is reduced as a result of the features like multiserver, single-console management, event-based job execution and alerting, integrated security and administrative scripting, which minimizes costs relating to database administration.

SYSPRO Specific Advantages

• Microsoft SQL Server incorporates a rich set of functionality to assist customers in the analysis of their corporate data using techniques such as Data Warehousing, Data Mining and OLAP.
• A significant benefit of implementing the SQL Server version of SYSPRO is that it ensures consistent and reliable data at all times.  This overcomes a limitation of C-ISAM file structures which are unable to provide the necessary levels of data integrity required for enterprise systems (e.g., data can be left in an incomplete state if either the server or client crashes or network problems occur).
• One of the ways in which the SQL Server version of SYSPRO ensures the integrity of data is by using a facility known as Transaction Processing.  This facility ensures that a group of transactions (e.g., the processing of an invoice) is committed to the database.  In addition, SYSPRO includes a timestamp data type column in every table.  The timestamp allows an application to view the chronological sequence of events and to verify whether any changes have occurred to data on a row since the timestamp was last accessed.
• NT authentication reduces security risks by allowing users the option of accessing SYSPRO using the robust security protocols provided by Windows NT and Windows 2000.
• SYSPRO uses native ODBC calls to access the SQL Server database.  ODBC is one of the most effective ways of accessing SQL Server, as this method does not require the database to interpret the calls before being processed.

Configuration Options

When setting up SQL Server, there are a number of issues that affect the performance of the system.  The most important of these include:

• Using a fast server
• Using SQL Server 2000 on Windows 2000.
• Tuning the memory the SQL Server uses for system use and caching.
• Configuring SYSPRO as a multiple-tier system.
• Separating the database and log files across disk drives.
• Using a Binary database collation.
• As with most performance issues, the general speed and configuration of the server and client computers together with the network can have the most dramatic effect on performance.  A faster computer will generally provide the better performance and the more memory it has often improves the situation.

Diagnostic Program (IMPDG6)

• A SYSPRO utility program forms part of the SQL Foundation Manager, used to help diagnose SQL Server problems.
• This utility will assist in identifying potential problems with the SQL Server database used by SYSPRO companies.
• Its main task is to identify any differences between the existing database and the standard SYSPRO tables, columns and indexes that should exist.  In addition, the database properties and company configuration can be viewed.

The program displays information using a simple point and click interface.  If required, this information can be printed.