Several new features could make upgrading to the latest version of this popular relational database a smart move.

Snapshot

Vendor: Microsoft Corp.

Product: SQL Server Version 7.0

Requirements:

  • Client: Microsoft NT 4.0 or NT 5.0 on Intel or Alpha platforms, Microsoft Windows 95 or 98 on Intel Platforms
  • Server: Microsoft NT 4.0 or NT 5.0 on Intel or Alpha platforms, Microsoft Windows 95 or 98 on Intel platforms

Web: http://www.microsoft.com/sql

Microsoft SQL Server 7.0 is the latest version of one of the most popular relational databases used by vendors of mid-tier and ERP accounting software for data storage and management. If your present accounting system uses an older version of SQL Server, Microsoft and your accounting vendor or value-added reseller (VAR) will soon begin encouraging you to upgrade to SQL Server 7.0.

SQL Server 7.0 is not just a significant upgrade for existing accounting applications. This release expands the range of accounting applications that can use the SQL Server database. Lawson Software, Navision Software and TecSys are just three accounting software vendors expected to roll out their first SQL Server-compliant applications on or around the release of 7.0. And SQL Server is even moving down the accounting food chain. For example, Peachtree Software has announced a new accounting suite to be based on SQL Server 7.0.

Here are five potential benefits that SQL Server 7.0 brings to accounting applications.

  1. Lower Acquisition Cost and Easier Database Setup
    More vendors will embed the SQL Server database engine into their accounting application, so the database is installed "out-of-the-box" with the accounting application. For example, Solomon Software is bundling SQL Server 7.0 with its new Solomon IV for BackOffice suite. An embedded database should lower the cost of SQL Server accounting, because vendors can pass on preferential pricing they get from Microsoft for the database engine to their customers. Also, an application with an embedded engine should make setup easier, especially if an SQL Server is not already installed. This is because the database installation and configuration will become an automated step in the accounting application's own installation process when an embedded database is included.

  2. Reduced Implementation and Ownership Costs
    Version 7.0 includes a wide range of step-by-step wizards to make database setup, maintenance and tuning easier, reducing the amount of time-consuming "grunt work" for database administrators to perform. These wizards can create database objects, manage database administration, import and export data from the database and make replication (copying data to and from other SQL Server databases) easier to setup and manage. Also, new Data Transformation services in 7.0 should reduce implementation time and cost by making it easier for new installations to migrate data from a legacy application database or file system into SQL Server. For a single site installation, after an initial setup phase, the SQL Server database will only need the services of a part-time database administrator. Less administrator support means a lower cost of ownership and the potential to free up the existing database administrator to undertake more advanced maintenance and fine tune tasks to improve overall system performance.

  3. Common Database Platform
    Support for SQL Server 7.0 as an application database is likely to be pervasive, not just among accounting vendors but also among vendors of many other applications that complement accounting systems. Furthermore, 7.0 now runs on Windows 95/98 client desktops as well as desktops and servers running versions of Windows NT. A single database engine for both client and server applications, albeit limited to Microsoft-centric clients and servers, is an attractive proposition because it reduces the number of databases that an information systems (IS) department needs to buy, learn and maintain. It may allow customers to get better per-user license fees since more users will connect to the same database engine. And it will make it easier to transfer data from corporate server databases to personal desktop databases for local analysis. This will certainly make SQL Server more comparable to competitive products such as Sybase SQL Anywhere that have offered this client-server operation for some time. Microsoft is also shipping a 7.0 engine with Office 2000, which may cut into the use of Microsoft's own Access database — certainly the rationale for an Access-based accounting system looks shaky with the release of 7.0.

  4. Improved Performance and Scalability
    Microsoft changed the way SQL server stores its data on the hard disk, improved the ways that SQL Server executes and optimizes database queries and introduced full row-level locking. These enhancements are likely to mean that SQL Server will perform better than previous versions, especially with larger data sets and when higher numbers of users are processing repetitive accounting transactions such as sales orders. Microsoft's competition has provided row-level locking for some time, and the addition of this capability will allow SQL Server to support ERP-level systems that have hundreds of online users. The Enterprise editions of SQL Server (as opposed to the Desktop and Standard editions) claim to support over 2 gigabytes of extended memory for data caching, terabyte-sized databases and multiprocessor servers running up to 32 processors. These high-end scalability thresholds could make Enterprise SQL Server a viable alternative to other popular databases, such as Oracle, that run on the UNIX operating system, which has proven to scale better than Microsoft NT.

  5. Improved Decision Support
    A primary design focus of 7.0 was to make SQL Server a more attractive database engine for the foundation of a data warehouse. One of the main results of this focus is the addition of an online analytical processing (OLAP) services layer (formerly known as "Plato") to SQL Server that makes extracting data easier for desktop tools focused on ad-hoc, multidimensional information analysis. Dozens of OLAP vendors have announced support for this interface to SQL Server, and Microsoft will use this layer to make extracting and analyzing data from SQL Server more efficient in their own tools, such as Microsoft Excel. The bottom line is, by providing this interface and persuading so many OLAP vendors to support it, data stored in SQL Server is more accessible and easier to analyze from desktop tools. This can only lead to accounting users and managers getting better decision-support value from their accounting database.

A Look at SQL Server 7.0

129881

(For larger screen, click image 27 KB)

SQL Server 7.0 includes several new wizards. Here is the start page for running wizards that define database data imports and exports, access data via OLAP services and create an English-language query.

129881

(For larger screen, click image 20 KB) Version 7.0 lets users view the entity-relationship diagram or data-model for a selected database, making it easier to understand the database tables construction and the relationships between them without accessing a third-party tool.

129881

(For larger screen, click image 31 KB) The database backup and restore function is easier to use than previous versions. A database administrator can create a backup schedule and leave it to run unattended, reducing some of the administration overhead of maintaining the database server.

Great Plains Software, with its Dynamics C/S+ suite, has one of the largest number of SQL Server-based accounting installations. According to Ross Hillier, data services manager at Great Plains Software in Fargo, N.D., a number of small but important changes to the core database engine will benefit almost every Dynamics C/S+ site that upgrades. Improved row-level locking in 7.0 means that more users can access the Dynamics database before locking contention kicks-in, or current user counts will be able to access the database with fewer or no contention problems. The ability to automatically shrink or grow the database according to an application's need will mean less administration and will free up disk space and prevent errors that can crash systems. To avoid running out of room, many database administrators reserve more disk space for SQL Server databases than they need, but they now can recover that space, and in the future, they can leave the allocation of disk space up to the database manager. Improved indexing in 7.0 means that efficient clustered indexes that force the rows in tables to be stored in a logical order, can be used without their current performance hit when updating table rows. Also, multiple table indexes can be merged into temporary "virtual" indexes to make complex queries faster and replace the slow and costly table scans to find records that match the query requirements. In short, 7.0 will be faster, allow Dynamics to scale to higher numbers of users and take up less disk space, all without Great Plains making significant changes to its application.

However, there is plenty of room in 7.0 for accounting software vendors to leverage other 7.0 database features. Improved job scheduling and alerting capabilities can automate batch processes, such as backups, or can generate business alerts to warn users of exception conditions. Data replication that is more reliable and easier to setup and maintain can keep subsidiary charts of accounts in sync with headquarters or pass month-end transactions between organizational units on a push-up or push-down basis. The English Query function (available in SQL Server 6.5) provides a library of English-language queries such as "Show me the accounts where actuals exceed budget by more than 10 percent" to make it easier for nonaccounting users to get information from the database. And new OLAP-driven features such as the Top-N query answer requests such as "Show me the top ten customers by revenue during the current fiscal year" as part of new "cut-to-the-chase" information consoles for business analysts and managers.

Of course, many of these benefits and application features of SQL Server 7.0 have yet to be proven in production sites running accounting applications, although Microsoft is already touting the improvements gained from converting its own substantial SAP R/3 installation to SQL server 7.0. And upgrading to 7.0 is not without some costs, including:

  • Time and effort to convert and migrate existing SQL Server databases (if used)
  • Time and effort to migrate and test existing accounting applications
  • Potential server and client license upgrade fees (if SQL server is already in use)
  • Training IS staff and database administrators in the new version functionality
  • Dealing with the inevitable new release bugs

Nevertheless, if the benefits of 7.0 are delivered, the advantages of upgrading to 7.0 should clearly outweigh the costs. Seven might just be your lucky number.