Referring to the sequence in the slogan: design, develop, prototype, and deploy, this blog article starts at the tail end of that sequence. The reasoning is that: by addressing the number-one bottle neck in adoption of client/server databases, namely: portability, distribution, and advanced custom installation, LabVIEW users will be motivated to seriously consider bundling these powerful databases with LabVIEW applicationsThis blog article is written in two parts, Part 1 introduces MS SQL Server 2005 Express Edition in relation to MS Access and discusses essential installation logistics. Part 2 delivers the LabVIEW -based installer wrapper for MS SQL Server Express 2005.

Blog articles that follow will discuss fundamentals of relational database design suited to automated measurements and control applications. The intent is to help developers design and program functional databases relevant to the measurements and automation field of LabVIEW applications.

MS Access Database Usage with Distributable Applications

Microsoft Access is a popular database of choice when it comes to building distributable LabVIEW applications that must interface with a portable database. Microsoft Access is common because it comes readily packaged with Microsoft Office; its database files can be copied and moved between machines; and most developers are likely to have a working knowledge of MS Access first, before trying out and migrating to other databases systems.

However, MS Access as a file-based-only database system has a scalability downside. For example, Microsoft Access suffers impaired performance with a possibility of database file corruption, that may be beyond repair, if a database becomes larger than 1- GB in file size. Performance also degrades when multiple distributed computers connect remotely and concurrently to an MS Access database under heavy network data traffic loads.

Advantages of Client-Server Database Systems

Client/Server -type database systems such as MySQL, MS SQL Server, Oracle and others have the capability to resolve MS Access scalability issues and are worthwhile considering by developers who plan to upgrade from this file-based database system.

Client server databases come with a server engine that hosts database files. This server engine off-loads query processing from client applications; provides services to better manage multiple usage scenarios; and efficiently handles other database maintenance chores. Many commercial client/server databases offer a free version of a compact edition of their databases. However, despite all these advantages, portability and distribution difficulty is the number-one challenge responsible for low developer adoption rate of client-server database systems with distributable applications.

Facilitating Client-Server Database Distribution and Installation

The purpose of this article is to introduce a LabVIEW –based utility that simplifies the process of packaging, distributing, and installing MS SQL Server 2005 Express Edition database with LabVIEW applications. This installer utility is really a wrapper utility that can customized MS SQL Server install settings using an *.ini file. This means that the executable does not need to be re-written and recompiled when you want to: Either install a different instance name of the MS SQL Server; Re-install or upgrade different database files; or · Change the default installation path of SQL Sever engine and associated components.

The LabVIEW -based wrapper supplied with this article implements the long code base described in the MSDN document: “Deploying SQL Server 2005 Express Edition by Using a Wrapper”. and includes error handling and other advanced customization options.

In this first part of the discussion, essentials of MS SQL Server installation logistics is provided to help developers understand typical real-world installation scenarios that may need intervention. One example of such a scenario is the pre-existence of SQL Server Express 2005 installation on the target host computer; another scenario is a need to install the database on a different drive or path. The LabVIEW -based utility provided with this article facilitates such intervention by providing advanced but user friendly option steps during installation.

In addition to other checks, this utility searches the host PC’s registry to explore previous installations of SQL Server Express 2005, informs you about any installations found. You can then choose to use one of the pre-installed MS SQL Server instances with your LabVIEW application or you can install a new fresh instance of the database. MS SQL Server Express installations are becoming more common because Microsoft bundles MS SQL Server Express database with MS Office Professional.

It is important to note that the methods described in this article are specific to MS SQL Server 2005 Express edition and are not transferable for use with other compact client/server databases such as MySQL’s Embedded, and Oracle’s Personal Edition databases. These tools also work only on the Windows Operating System because MS SQL Server runs exclusively on the Windows platform.

Background on SQL Server Installation Logistics

Among other components, MS SQL Server has three important installation components that you need to be familiar with:

1. MS SQL Server Engine and Server Instance Names
2. MS SQL Server Database files
3. MS SQL Server Management Studio Express

SQL Server Engine & Server Instance Name

SQL Server Engine

MS SQL Server Engine is a server application that hosts and services MS SQL Server database files. The server runs as a ‘Windows Service’ that is always ‘ON’ or ‘Running’ and therefore comes with some overhead, about 25 mb memory and less than 1% CPU usage when idle. In its idle state, MS SQL Server listens for local or remote connections from the default TCP/IP port number 1433.

Note: A Windows Service Application is one that is configured to run automatically on OS Startup, without the requirement that a user be logged on to Windows. Windows Service applications always resume a normal run state when a PC is powered-on. Such applications can also be configured to automatically restart if the executable stops running or crashes for any reason. Windows services is an essential way to run mission critical applications.

When systems connect to an SQL Server -hosted database to run SQL commands, the MS SQL Server engine processes these commands from the server end. Data search, filter, and sort operations, are all accomplished by the server engine that is specifically optimized for these tasks. MS SQL Server only returns the final results which helps to reduce network data traffic.

• The LabVIEW –based wrapper utility supplied with this article for MS SQL Server Express installation invokes the SQL Server installer application SLQEXPR32.EXE which installs this SQL Server Engine.

Server Instance Names

MS SQL Server can be installed to run as separate instances with each instance distinguished by an instance name. An instance name is a unique name of an SQL Server process that runs independently of other instances. Up to 16 independent instances can be installed on one PC. By default MS SQL Server 2005 Express installs the default instance name ‘SQLEXPRESS’.

By default, the first instance of SQL Server Express 2005 installs under the folder: <INSTALL_PATH>\MSQL.1;. . . the second instance installs under <INSTALL_PATH>\MSQL.2;. . . the third under

<INSTALL_PATH>\MSQL.3, . . . and so on

The default <INSTALL_PATH> = C:\Program Files\Microsoft SQL Server

The first instance name may not necessarily be named ‘SQLEXPRESS’, however, the install folder for the first instance will always be <INSTALL PATH>\MSQL.1.

The setup program for MS SQL Server Express Edition (Namely: SQLEXPR32.EXE) can be run with optional command line arguments to customize MS SQL 2005 Express installation. Customizable settings include: installation path, the server instance name, administrators login username & password, and much more.

• The LabVIEW –based wrapper utility supplied with this article for MS SQL Server Express installation determines installed instance names and their respective installation paths and helps you manage which instances to re-use or whether to install a fresh instance of MS SQL Server Engine. Command line arguments are built from a user friendly options tab and applied during installation.

SQL Server Database Files

A given MS SQL Server databases is made up of two files with a default nomenclature of:

1. <DATABASE NAME>.MDF
2. <DATABASE NAME>_LOG.LDF

A given database can be hosted by one and only one instance of MS SQL Server. The files of the said database are then considered to be attached and registered to the hosting server. One MS SQL Server Instance can host many databases. This is why it is possible to use a pre-existing instance to host database files for a new application.

For security reasons, MS SQL Server files cannot be readily copied and moved between machines. This gives rise to portability and distribution challenges which this articles addresses. To prepare database files to be copied or moved, the database must be detached from MS SQL SERVER using a database detach command. Detaching database files de-registers the database from the MS SQL Server Engine. After making copies of the file the database must be re-attached to the server using database attach command. Attaching database files to MS SQL Server registers the file with the server engine and brings the database back online.

Note: MS SQL Server 2005 Express edition has a new feature called XCopy where you can use commands in the server connection string to automatically attach detach files in the background. XCopy -enabled Connection strings will be discussed later detail later.

• The LabVIEW –based wrapper utility supplied with this article for MS SQL Server Express installation handles database file attach and detach procedures automatically and gives you the option to export, import, backup, or upgrade database files.

MS SQL Server Management Studio Express

Microsoft supplies the free licensed MS SQL Server Management Studio Express to help you administer and program MS SQL Server databases.

Administrative functions include management of user logins, database backup and restore functions, monitioring database activity, compacting the database, and edit database performance options.

Programming functions include utilities to design, view, and edit database tables and database schema diagrams; and to write and test SQL queries, stored procedures and user defined functions.

• The LabVIEW –based wrapper utility supplied with this article for MS SQL Server Express installation includes an optional installation of MS SQL Server Management Studio Express. The installer is launched after installing MS SQL SERVER Express Database Engine. MS SQL Server Management Studio Express has pre-requisite components, namely: .NET Framework and MSXML 6.0 Parser that get installed with MS SQL Server Express database or can be installed separately.

To be continued in the second part of the article. Subscribe ExpressionFlow feed not to miss it.