Project Taphouse

There's not really a Project called "Taphouse", at least not that I'm working on.  But I'm calling it that because I can't really discuss what it's really for or for whom it is being built.  However, it has been (and still is) an interesting project.  It's forced me to drill into Google for lots of command goodies I knew about, but hadn't used in a long time.  Many I hadn't had to use in the way I'm using them now either.  So I thought I'd share some of this in case it's helpful to others.

Here's the basis of the requirement:

The Requirements

"Mobile tablets or laptops will be used to collect inventory data from remote locations using a wireless handheld barcode scanner device.  The remote locations will not always have accessible WiFi, nor a reliable 3G or 4G signal.  Further, testing at locations that have 4G LTE coverage indicates excessive battery drain when using active LTE communication.  Requesting a client-based inventory collection tool that can download configuration updates, as well as upload inventory data, but only when attached to the base network.  Material costs must be kept to absolute minimum."

This led to five minutes of head-scratching, a little Google searching, and finally an "a-ha!" moment:

A local web app using a local database.  The web app will offer the means for capturing inventory scan data, manual entry (when needed), and provide upload/download capability when connectivity allows.

The Ingredients

1. Windows 7 tablet or laptop
2. Local IIS instance and virtual directory configuration (windows authentication, disable anonymous authentication, to allow tracking of entries by logged on user)
3. ASP or ASP.NET web app
4. SQL Server 2008 R2 Express
5. Coffee
6. Sugar snacks
7. Bad music

(Note: steps 2, 3, 4 can be done in any order, but it helps to move step 5 to step 1 sometimes)

The Deliverable

1. A packaged installation that can be easily deployed to laptops or tablets, either manually, or via Configuration Manager advertisement, to enable user to begin capturing inventory data at remote locations in the field.

The Chunks

• Step 1 is easy enough.  
• Step 2 was interesting.  First I used the DISM command to install and configure IIS, the necessary component features, and Windows Authentication.
• Step 3 involves creating the virtual directory target folder, copying in the web app content files, and then using APPCMD to create and configure the web site and virtual folder settings
• Step 4 involves creating a configuration (response) file for installing SQL Express 2008 R2 to allow for silent installation on other computers.

Install and Configure IIS features

dism /Online /Enable-Feature /FeatureName:IIS-WebServerRole
dism /OnLine /Enable-Feature /FeatureName:IIS-WebServer
dism /OnLine /Enable-Feature /FeatureName:IIS-ApplicationDevelopment
dism /OnLine /Enable-Feature /FeatureName:IIS-ISAPIExtensions
dism /OnLine /Enable-Feature /FeatureName:IIS-ASP
dism /OnLine /Enable-Feature /FeatureName:IIS-WebServerManagementTools
dism /OnLine /Enable-Feature /FeatureName:IIS-Security
dism /OnLine /Enable-Feature /FeatureName:IIS-WindowsAuthentication

Create Virtual Folder


I created the folder "inventory" beneath "c:\inetpub\wwwroot", but you could put it anywhere really.  The main thing is to poing the "/PhysicalPath:" parameter to the appropriate location.  The APPCMD command is easy to use for creating and configuring the virtual directory.  For this project, I'm building the virtual directory under the Default web site.

appcmd add vdir /app.name:"Default Web Site/" /path:/inventory /physicalPath:c:\inetpub\wwwroot\inventory

appcmd set config "Default Web Site" /section:windowsAuthentication /enabled:true /commit:apphost

appcmd set config "Default Web Site" /section:anonymousAuthentication /enabled:false /commit:apphost

Install SQL Server 2008 R2 Express

Actually, the first step is to launch the installer (.exe) from a CMD console using /Action=Install /UIMode=Normal  - This enables the "Ready to Install" step in the left-hand vertical list of steps shown in the installation dialog.  For whatever reason, if I simply double-click the .exe and run the installation "normally" it doesn't show this feature.  You need the "Ready to Install" feature because it's the only place the shows the path to the "ConfigurationFile.ini" response file it creates, and allows you to abort a "real" installation at the final step and keep the .INI file.

Once you have the .INI, the CMD syntax for using it is pretty simple, but you still need to make some very minor modifications to the .INI first.

INI modifications

Add:  IACCEPTSQLSERVERLICENSETERMS=1

Change:  QUIETSIMPLE to "True"

Change:  SECURITYMODE to "SQL"  (you don't have to do this, but I prefer to)

Add:  SAPWD=[enter a strong password here]

Change:  TCPENABLED to "1"

Change   NPENABLED to "1"

You could set QUIET="True" for no dialog display, but I like to see some progress since it's pretty slow and pauses a few times along the way.

The installation syntax (assumes both files are in the same folder):

SQLEXPRWT_x86_ENU.exe /ConfigurationFile=ConfigurationFile.ini

But wait - There's more!

After installing the database, I still need to automate the setup of the database schema, create tables, users and grant permissions.  I created some T-SQL scripts and saved them in a folder.  Then I use the SQLCMD command to execute them using the default "sa" user account (note that I've replaced the actual password with "**" below, but you have to specify the actual password)

cd "\Program Files\Microsoft SQL Server\100\Tools\Binn"
sqlcmd -U sa -P ** -S %ComputerName%\INVENTORY -i 1_create_database.sql -o "%temp%\1_sql.log"
sqlcmd -U sa -P ** -S %ComputerName%\INVENTORY -i 2_create_login.sql -o "%temp%\2_sql.log"
sqlcmd -U sa -P ** -S %ComputerName%\INVENTORY -i 3_create_user.sql -o "%temp%\3_sql.log"
sqlcmd -U sa -P ** -S %ComputerName%\INVENTORY -i 4_create_table.sql -o "%temp%\4_sql.log"
sqlcmd -U sa -P ** -S %ComputerName%\INVENTORY -i 5_grant_user_privs.sql -o "%temp%\5_sql.log"
sqlcmd -U sa -P ** -S %ComputerName%\INVENTORY -i 6_sample_data.sql -o "%temp%\6_sql.log"

As an example of the T-SQL script, this is "1_create_database.sql" taken from the project at build 3...

( NAME = N'Inventory', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.INVENTORY\MSSQL\DATA\Inventory.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Inventory_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.INVENTORY\MSSQL\DATA\Inventory_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Inventory] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [Inventory] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Inventory] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Inventory] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Inventory] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Inventory] SET ARITHABORT OFF
GO
ALTER DATABASE [Inventory] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Inventory] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Inventory] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Inventory] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Inventory] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Inventory] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [Inventory] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Inventory] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Inventory] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Inventory] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Inventory] SET  DISABLE_BROKER
GO
ALTER DATABASE [Inventory] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Inventory] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Inventory] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Inventory] SET  READ_WRITE
GO
ALTER DATABASE [Inventory] SET RECOVERY SIMPLE
GO
ALTER DATABASE [Inventory] SET  MULTI_USER
GO
ALTER DATABASE [Inventory] SET PAGE_VERIFY CHECKSUM 
GO
USE [Inventory]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Inventory] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

And, here' an example of the T-SQL script, "5_grant_user_privs.sql" taken from the project at the same build...

use [Inventory]
GO
GRANT DELETE ON [dbo].[AuditInventory] TO [InvUser]
GO
use [Inventory]
GO
GRANT INSERT ON [dbo].[AuditInventory] TO [InvUser]
GO
use [Inventory]
GO
GRANT SELECT ON [dbo].[AuditInventory] TO [InvUser]
GO
use [Inventory]
GO
GRANT UPDATE ON [dbo].[AuditInventory] TO [InvUser]
GO
use [Inventory]
GO
GRANT DELETE ON [dbo].[Collections] TO [InvUser]
GO
use [Inventory]
GO
GRANT INSERT ON [dbo].[Collections] TO [InvUser]
GO
use [Inventory]
GO
GRANT SELECT ON [dbo].[Collections] TO [InvUser]
GO
use [Inventory]
GO
GRANT UPDATE ON [dbo].[Collections] TO [InvUser]
GO

Conclusion

I haven't described the download and upload aspects yet, but I will in the near future.  Those features are interesting in and of themselves.  But for now, hopefully this will keep you entertained (or put you fast asleep).

As I've said many times before: Microsoft gives you tons of goodies to help automate almost any task.  The combination of silent installation capabilities, scripts, commands like DISM, APPCMD, SQLCMD, and the ability to string it all together in a simple BAT script, opens the door to unlimited possibilities.  You can wrap all this in a nice .MSI using InstallShield as well, but I wanted to show that it's possible to do all this with absolutely ZERO cash spent on software product licensing (besides Windows itself).  One final note, if you wrap all this in a script, be sure to run the script using "Run as Administrator".

Cheers!