This blog article is written in two parts. Part 1 introduced MS SQL Server 2005 Express Edition in relation to MS Access and discusses essential installation logistics. A LabVIEW –based installer for MS SQL Server 2005 Express Edition is included for download with this second part of the blog article. Use of this utility can be described in one phrase as follows:

“Copy the folder < Database Manager> to your target computer and after you have installed LabVIEW runtime engine, launch the executable: Database Manager -Installer.exe. Later on, you can use the Database Manager –File Copy.exe utility to import, export, or upgrade database files.

There are two download options:

1. LabVIEW Based Installer With All Components ~100 mb
2. LabVIEW Based Installer Minus MS SQL Server Management Studio Express ~65 mb

So go ahead and try it out! By default, the utility will install a sample database called ‘DB’. If you run the installer repeatedly, it will re-use the installed instance and keep replacing the sample database file: DB.mdf. The remainder of this article explains how you can customize this utility to use your own database files. Other options are to choose whether to use an existing installed instance of MS SQL Server 2005 Express Edition, or to install a fresh instance of the server engine at your chosen location on the target PC’s hard drive.

Note on Microsoft’s Redistribution Licensing

To allow you to get started, the utility supplied bundles the freely redistributable components: (1) MS SQL Server 2005 Express Edition, and (2) MS SQL Server Management Studio Express. However, to be sure that you are using the latest version, you should download these components from Microsoft’s website on SQL Server 2005 Express Edition. You should also register with Microsoft to redistribute the same with your own applications. The Addendum section at the end of this article contains info on which folder to place these components for use by the supplied LabVIEW -based wrapper installer.

How the Installer Utility Works

During installation, the folder structure on the customer’s target machine should be organized as shown in Figure 1. Note that the area enclosed in the red border are the contents of the Database Manager folder supplied with this article.

Please Note: The installer executable must be run from a writable location of the target computer’s hard drive and not from a CD. This is because the executable writes script files from the location from which it is running. It follows that, if deploying from a CD media, a pre-installer should run that will install LabVIEW 8.2. runtime engine and then copy to the target machine the Database Manager installer folder. The Database Manager -Installer.exe file can be invoked from the new location anytime thereafter. LabVIEWs 8.0 or higher application installer creation tools can do all of this for you.

Description of Contents of Utility Folder and Files

FOLDER: <Database Manager>

Contents under database manager folder contain two executables:

• Database Manger –Installer.exe : This must be run by the main application’s installer program to install MS SQL Server 2005 Express for the 1^st time. The LabVIEW installer should be configured to run this exe via a ‘run’ command. If you run this utility repeatedly, your *.mdf database files included in the installer package will replace the ones installed previously.
• Database Manager –File Copy.exe: This is a LabVIEW –based database utility to import, export, upgrade, and archive database files, as needed, after MS SQL Server 2005 Express has been installed. This executable is also a SubVI within Database Manager –Installer.exe but it is compiled as a standalone utility for use as a maintenance tool only.

 

Figure 1. Organization of folders and files on customer’s target machine during installation. The ‘Database Manager’ folder is provided in this release. The DATABASE MANAGER –INSTALLER.EXE should be launched by your installer package to begin SQL Server 2005 Express Installer. LabVIEW Runtime Engine 8.2.1 should be pre- installed for this executable to run.

FOLDER: <DB FILES>

• Contains database files to be used for a specific installation, see Figure 2. A pair of files for an example database are provided under this folder. These files are DB.mdf and DB_log.ldf. You must replace these files with corresponding database files that you intend to install on the target machine.
• To do so you must detach a pair of *.mdf and *.ldf files from SQL Server 2005 Express and copy these to the DB Files folder. MS SQL Server Management Studio Express has a shortcut menu item which you can use to detach database files. See Figure 1.
• The file extension *.mdf stands for ‘Microsoft Database File’ and *.ldf stands for ‘Log Database File’. The *.mdf file contains the DB schema and data records while the *.ldf file contains info on query transactions against the database. The *.mdf file is the more important one of these two files.
• Naming convention for database files for MS SQL Server 2005 Express is: <DBName>.mdf and <DBName>_log.ldf. These database file names must be spelled-out correctly in the DB.ini file for the installer to register these files correctly with MS SQL Server Express. The DB.ini file contents are described in a section that follows.

Figure 2. DB Files folder contents. This folder must contain a pair of database files to be deployed on target machine. There is a file called ‘DO NOT DELETE.UDL’ which must be made available in this folder. The installer will customize this template file for connectivity and will make the customized copy available under the <DATABASE MANAGER> folder . The customized UDL file will be called ‘<DBName>.UDL’. Use of this file is optional you can use DSN or a built connection string to connect to the MS SQL Server Database.

 

Figure 3. Shows how to use the ‘file detach’ procedure to prepare to copy files to the DB Files folder for installation on target machine.

 

Purpose of the : ‘DO NOT DELETE.UDL’ File

The DO NOT DELETE.UDL file is a Universal Data Link –type file. A UDL file is a connectivity ‘helper’ file that can be configured to hold information to connect to ODBC or OLEDB compliant data sources such as databases. The LabVIEW-based installer will use this file as a template to create a new UDL file which will hold connection information to your newly installed database. The UDL file will be called: <DBNAME>.UDL and you will find this file under the <DATABASE MANAGER> folder. You can refer to the UDL file in your LabVIEW program to connect to your database or you can use the connection string method described at the end of this article under the section ‘Connecting to Database’. That section includes examples of block diagram code of LabVIEW VIs wired with both UDL and connection string info.

Otherwise, when clicked, the UDL file exposes a tabbed user interface. There is a <Test Connection> button which you can click to verify whether the UDL file was configured correctly, see Figure 4.

 

Figure 4. The installer will create a UDL file for you that is configured to connect to your newly installed database. You can use this file to connect to your database or you can connect using the connection string method described at the end of this article under the section : “Connection String”

 

FILE: < DB.ini>

This file contains parameters to customize installation of MS SQL Server 2005 Express and associated database files. The contents of the ini file are as follows:

[DB]PSWD=sql

DBName=DB

DBInstallPath=C:PROGRAM FILESMICROSOFT SQL SERVER

UseInstanceName=TRUE

InstanceName=SQLEXPRESS

InstallMSE=FALSE

DBInstallPath is the destination path for the SQL Server 2005 database server engine as well as database files. In some end-user cases, such data files must be installed on a separate drive. The DBInstallPath can be edited to change drive location and path as desired.

The instance name ‘SQLEXPRESS’ is the default instance for MS SQL Server 2005 Express installations. You can change this to your desired instancename.

InstallMSE is an option key that determines if Microsoft SQL Server Management Studio Express will be installed. Users can override this setting from the installer’s advanced tab options.

PLEASE NOTE:

• Once MS SQL Server 2005 is installed for the 1^st time. The install settings are copied to a new ini file called <ComputerName>_DB.ini. Moving forward, each time a new DB.ini file is copied over, its contents will be replaced by the contents from <ComputerName>_DB.ini file so that the installer can adopt the last known settings of MS SQL Server installation.
• If you do not want the DB.ini file replaced in this way, you must delete the file <ComputerName>_DB.ini. This arrangement has been put in place so that future upgrades to the database will automatically inherit the correct settings for the last installation of MS SQL Server 2005 Express; without you, the developer, having to ask for those settings from the end user.

FILE: < File_Copy.txt>

The file called File_Copy.txt is a tab de-limited file that enlists database files to be copied to a target computer. This file contains two lines:

DB.MDF C:\DBFILES\DB.MDF

DB_LOG.LDF C:\DBFILES\DB_LOG.LDF
The installer program will search (recursively) the contents of files under the <SUPPORT FILES> folder and will copy the ones specified here to the target computer. Notice that items on the left are the file names to be copied. Items on the right are the default locations where those files are to be copied on the target machine. If, during install runtime, the user changes the path to install MS SQL Server, these files will be installed on the drive letter corresponding to that new edited path.
The rest of the folders contents are supporting files for the installation. The contents of these folders are described under the ADDENDUM section at the end of this article.

Logging On to MS SQL Server Express Database

Applications running locally on the target computer can connect to the database using a security mode called ‘Windows Authentication’. This mode of connection does not require a login and password. Anyone who can logon to a windows session under the operating system of the target computer will be allowed access to the database.

Peer computers on the same Windows network domain can connect to SQL Server 2005 using the same ‘Windows Authentication’ security mode. However, this will require MS SQL 2005 Express to be configured to recognize domain users.

Otherwise, a mode of connection for any computer (local or external) and which does not require admin configuration of a newly installed instance of MS SQL Server 2005 Express is the ‘SQL Server Authentication’ using the ‘sa’ (system administrator) account. The password assigned in the DB.ini file is the one to be used for this ‘sa’ login account.

The LabVIEW -Based installer will configure MS SQL Server Express to work in both modes ‘Windows Authentication’ and ‘SQL Server Authentication’ modes.

Connection Strings

The connection string to MS SQL server is in three forms common forms

1. Windows Authentication Connection String (No password needed)

 Driver={SQL Native Client};Server=<ComputerNameInstanceName>;Database=<Database Name>;Trusted_Connection=yes;

. . . Note: You can use IPAddress in place of computer name or use ‘.\instancename’

2. SQL Login Authentication Connection String

 Driver={SQL Native Client};Server=<ComputerNameInstanceName>;Database=<DB Name>;Uid=sa;Pwd=< Password>;

 

. . . Note: You can use IPAddress, or a period (.) in place of computer name: E.g: you can use ‘.\instancename’

3. Connection String including clause for Attaching Database Files *.mdf

Driver={SQL Native Client};Server=.SQLExpress;AttachDbFilename=c:asdqwemydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

More info on connection strings is available from ConnectionStrings.com

NOTE: Settings to build the connection string should be read from the file. <Computer Name>_DB.INI that will be generated after installation

LabVIEW Code Examples for Connecting to MS SQL Server

 

Figure 5. Connecting using a UDL File.

 

Figure 6. Connecting to database using connection string with Windows authentication. Note that you can replace computer name with a period (.) or with an IP Address of target computer.

 

Figure 7. Connecting to database using connection string with Windows authentication. Note that you can replace computer name with a period (.) or with an IP Address of target computer.

 

 

Figure 8. Connecting to database that uses XCopy feature of MS SQL Server Express and under Windows authentication. Here you use the AttachDBFileName option to attach your database file as needed. The attach command will ignore attaching the database file if found to be already attached. You only need to specify full path for the *.mdf file.

Run-Time Installation Customization Screens

Various situations may arise where items in the DB.INI and File_Copy.txt file setting need to be changed during installation run-time. Examples of these situations are:

i) A customer’s computer may have MS SQL Server 2005 Express pre-installed and the customer may want database files to be hosted (attached) to this pre-installed instance of MS SQL Server 2005.

ii) A customer’s IT department may preclude installation of any applications on the customary C:\ drive and may require that applications be installed on the D:\ drive or any other available drive.

iii) The customer may wish to change the installation path from C:\Program Files\MS SQL Server to a new path such as G:\COMPANY XYZ\MY SQL SERVER 2005 Installation

All of the above scenarios can be handled using the run-time installation screen (see Figure 9). This screen provides just-in-time intervention at run-time to override any settings in the DB.INI and File_Copy.txt files. These situations usually arise because there is no way of predicting the customer’s rules and restriction for new application installations.

Figure 9. Starting screen. Intervention to custom install settings can be accessed using the
option: <Review Modify Advanced Settings>.

 

 

Figure 10. Advanced options for customization of MS SQL Server 2005 installation. An Instance Names combo box will reveal installed instances found on the target computer.

 

 

Figure 11. Custom database files to be copied to target machine. The installer session shown uses the sample files DB.mdf and DB_Log.ldf .

 

 

Figure 12. Installer runs robust (system registry read-only) checks for Dot Net Framework (not shown), existence of Path for SQL Server, SQL Server pre-installation, makes sure that MS SQL Server is running, and validates the password by checking if connectivity can be established. If password fails, a local application can still log-on to the database server by using Windows Authentication.

 

 

Figure 13. Final detach/ attach phase of database files. The database will be detached if found to exist. This features makes the installer tool work as a database upgrade tool as well.

 

Using The Database Manager Utility

The application <Database Manager –Files Copy.exe>, can be used as a post installation database management utility. The database manager utility can be used to accomplish two things:

1. Upgrade or replace database files with new ones & Archive Old files
2. Export database files for use on another computer

The database manager utility can be run as a separate executable or can be launched from within a local application using the command line. You must disconnect all applications from MS SQL 2005 Express database to use this utility successfully.

Upgrading or Replacing Database Files with New Ones & Archive Old

Under this functionality, the tool will automate a three step process.

• Detaches existing database files from MS SQL Server 2005 Express;
• Backs-up or archives these files to a default folder called; C:\DB Backup. This backup path can be changed by the user. Backed-up files will have a timestamp appended to them. Note: The backup file timestamp must be manually removed for the files to be re-used later with MS SQL Server 2005.
• The user must point to new files to be used in place of the old ones. E.g: The user can start with an empty database files or they can use files with an upgraded database table schema.

Export Database Files for Use on Another Computer

Under this functionality, the tool will automate a three step process:

• Will detach files for a chosen database from MS SQL Server 2005.
• Copy the database files to a user-chosen location. Files will be copied as-is and ready for use without appending a time-stamp as was the case above. The database manager application will prompt the user to replace files if files with the same name are found
• Utility will re-Attach the database file for continued use by MS SQL Server.

This database export feature is typically used when the database needs to be copied to another machine.

 

 

 

Figure 14. Entry screen for database manager application. The application will re-call the settings used at installation. These settings can be used by previewing the advanced tab. See Figure 15.

 

 

Figure 15. Advanced tab showing configuration settings for database, manager utility. These settings are read from the file <CompterName>_DB.INI found under the <Database Manager> folder and which gets created when MS SQL Server 2005 was last installed using the Database Manager –Installer.exe tool. Visiting this advanced tab is optional.

 

 

Figure 16. Database import screen is used to upgrade to new database files and archive existing files, a time-stamp will be appended to backed-up files. Users can change backup path here.

 

 

Figure 17. Database file export utility screen. Database files will be copied to a user chosen location (No Time-stamp will be appended). Files will be ready for use on another MS SQL Server 2005. The PC where export was done will continue to use the same files whose copy was exported using this tool.

 

ADDENDUM

FOLDER: <SQL EXP DB Installer>

This folder contains the installer application supplied my Microsoft. The installer file is SQLEXPR32.EXE. This installer executable is run with command line arguments to configure MS SQL Server Express 2005 installation. No user intervention is required in this folder unless a new installer release of MS SQL Server 2005 Express is available form Microsoft. Please visit the Microsofts’ SQL Server 2005 Express Edition link for updates. When replacing this file be sure that the has the same name as the existing one. i.e: SQLEXPR32.EXE. Otherwise the installer will not find it.

FOLDER: < SQL EXP SUPPORT -DOT NET FRAMEWORK>

This folder contains .NET Framework ver: 2 installation files. The LabVIEW installer will check for installation of dot net framework ver: 2, or higher. If not found, .Net Framework ver: 2.0 will be installed first before MS SQL Server 2005 can be installed. No user intervention or customization is required in this folder

FOLDER: <SQL EXP SUPPORT -MANAGEMENT STUDIO>

This folder contains an optional installer for the freely distributable MS SQL Server 2005 Management Studio Express. This is a utility application supplied by Microsoft to view and manage databases for local or remote MS SQL Server Databases. It provides a window to view database tables and supplies tools to write T-SQL stored procedures. It also provides means to configure MS SQL Server 2005 Express databases in various ways. The LabVIEW -based installer’s advanced Options screen gives users an option to install MS SQL Server Management Studio Express. The installer file invoked is.

<SQLServer2005_SSMSEE.msi>

No user intervention is required in this folder unless a new version of Management Studio Express Edition is available form Microsoft. When replacing this file be sure that the installer file has the same name; i.e. SQLServer2005_SSMSEE.msi Otherwise the installer will not find it.