SQL or ISAM? Who has your data?

Who has your data? SQL or ISAM? Increasingly, customers are demanding that their application data be maintained in accessible SQL databases. As more and more companies consolidate their data into single, enterprise-wide databases, data marts and data warehouses they seek the benefit of having easily accessible data about customers, products, production data and much more. Accessible databases allow for business intelligence, reporting and integration that might not otherwise be possible.

This demand for standardized databases and centralized access requires database servers that are responsive and scalable with near zero latency for extracting and manipulating data. In addition, concern over 24 by 7 data availability puts pressure on IT departments to insist on standardized databases that comply with industry-standard disaster recovery and high availability solutions.

A Relational Database Management System (RDBMS) based on Structured Query Language (SQL) is regarded as the industry standard for data storage while XML is often viewed as an industry standard for data transport. Application integration, business intelligence and virtualization are all simplified when industry standard databases are utilized. Not for any inherently technical reason, but simply because vendors can develop these needed solutions based on commonly accepted standards. This may seem like a circular argument, SQL should be a standard because it is a standard, but this is in essence the reality.

Rightly or wrongly, Indexed Sequential Access Method (ISAM) data is often viewed as outdated and non-compliant. Independent Software Vendors (ISV) that fail to offer products that comply with these perceptions may suffer in the marketplace. In addition, enterprises that run applications with ISAM databases may be perceived as non-compliant with industry best practice and can be cited for this in IT, risk management and security audits.

Market perceptions aside, migrating a flat file or Btrieve type application to the Magic xpa Application Platform using a SQL database can deliver an organization numerous benefits.  Structured Query Language (SQL) databases provide an enterprise class data platform for use in business applications. Most developers would agree that SQL databases are preferred for enterprise-class business applications developed on the Magic xpa Application Platform. Why?

SQL provides: a more robust data functionality to support business requirements such as OLTP and data warehousing; higher levels of security for mission-critical applications; the ability to apply business rules from within the database; and support for increased levels of transaction activity.

Because SQL is a popular concept used in databases such as MS-SQL, DB2 and Oracle databases, it is widely supported by a variety of data management, business applications, reporting and business intelligence tools. Not only are connections to other systems simplified, applications built on SQL are more future-proof than other applications. Standardization on SQL makes it easier for an organization to manage its data regardless of its use context. Because SQL can incorporate logical transactions at the database level, use of stored procedures, and an object-oriented methodology, it is often viewed as a superior choice for all sizes and types of applications.

SQL databases benefit from a wide variety of advanced solutions for data security that are readily available, highly sophisticated and ultimately secure. Identity management, idempotence and overall control over information access is enhanced through SQL across the full range of enterprise systems.

In general, SQL is better suited to very large database sizes and maintains a higher degree of transactional integrity and operational reliability. In other words, it is less likely to crash. Data integrity can be maintained on the database level and metadata is more easily tracked for purposes such as audit logs.

Am I saying that ISAM approaches are inferior in every way? No. Cost is a factor. While there are costs associated with most SQL databases, there are also express or light versions of SQL databases available for more cost-sensitive applications. ISAM is sometimes faster when you know what you're looking for. Having said this, processing times have advanced, disk latency is reduced, and in-memory storage (aka Big Data) is an increasing trend. Furthermore, what ISAM gains it often gives back due to greater bandwidth requirements. 

For these and many other reasons, most Magic xpa Application Platform installations rely upon SQL as their primary database, in part because SQL can deliver database level functionality not inherent to other approaches. 

• Sequence and identity mechanisms are included in SQL databases for better multi-user application functionality.
• SQL statements provide functionality for better application maintenance such as version updates, updated field definitions, table structure conversions, and added fields and indexes.
• The Where clause is a powerful feature of SQL databases that allows for advanced filtering.

Many believe that the relational model for data storage and retrieval has proven to be superior at handling large amounts of data and turning that data into useful information without requiring special programming techniques. The reason for that superiority is the Structured Query Language (SQL). This relatively simple language allows complex data manipulation using only a few simple commands. This is particularly true as the information stored in databases increasingly combines both structured and unstructured information and searches often need to be made across multiple fields and tables rather than searches within a specific field of a specific table.

Migrating applications from ISAM to SQL is not without costs, but the benefits are numerous:

·         Changed Perceptions. Applications that are SQL based are perceived as industry-standard, compliant and reliable. Customer acceptance and audit compliance is improved.

·         Application Performance. Use of stored procedures can take some of the performance burden off of your application servers. In large and complex applications this can improve overall responsiveness and reliability.

·         Complex Searches. Complex searches perform better in SQL databases. In general, SQL databases are faster to benefit from performance enhancements in underlying architecture and processing improvements because they are supported by major players in the software industry.

·         Data Security. Numerous data security, disaster recovery and high availability tools are available for SQL databases to more easily help these databases achieve data security objectives of an organization.

     Once migrated, Magic xpa Application Platform makes working with SQL much easier that in other programming approaches:

    Declarative Logic. The declarative logic capabilities of Magic's technology simplify the implementation and maintenance of complex user interfaces and dynamic data handling. Declarative logic patterns allow developers to define what they want to achieve rather than how the technology should implement it.

     Developers bind properties to complex rules, containing dependencies on multiple data elements and properties. The application engine maintains a hierarchy of dependencies and automatically recomputes these properties when the rules apply, in a similar way to a spreadsheet.

    Platform Approach Leverages Metadata. Magic xpa Application Platform enables developers to avoid technological complexities and achieve the greatest possible agility and productivity. With metadata platforms, the application logic is defined using properties, rules, and configuration that are stored in a central repository, which contains all application definitions, including business logic, forms, and data handling for the SQL database(s). As the metadata is platform-independent, the native metadata engine, which is available on multiple server and client platforms, can run the same application, regardless of the underlying technology or architecture.

     Because metadata development is highly structured and code-free, it reduces the development and deployment. By using a central repository, project governance is strictly enforced, regardless of its complexity, allowing applications developed by large teams to be maintained easily. Database meta-data can be defined and maintained throughout the Data repository. Every modification to the Data repository can be synchronized with the underlying database, assuming that the Change Tables in Studio setting is checked in the property sheet of the selected database.

    DBMS Transparency. The native gateways of the various SQL databases automatically translate every visual definition of the logical data view into an SQL script tailored for the specification of the underlying database.

  

     This enables the developer to develop business applications using a high level data definition and, at any time, automatically switch from one database to the other and have the native gateway that Magic xpa provides adapt the application to the new database.

    Direct SQL Script. In addition to the high level visual definition of specific data views of each task, the developer may choose to retrieve data by directly defining an explicit SQL script. Magic xpa assists the developer by providing a generator to construct the SQL script.

As you consider the benefits of your application migration, upgrading your application to take advantage of SQL looms large among the benefits available when modernizing applications.