BDD Scenarios: Automating Application Deployment

Often when installing an operating system on an existing computer, it is desirable to install the same applications that were previously on the computer. This can be achieved using the Zero Touch Installation rules engine querying two separate sources of information:

To simplify this database access, first a table needs to be created to map specific Add/Remove Programs entries to SMS packages.

use [BDDAdminDB]
go

if exists (select * from dbo.sysobjects where id = 
 object_id(N'[dbo].[PackageMapping]') and 
 OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PackageMapping]
go

CREATE TABLE [dbo].[PackageMapping] (
	[ARPName] [nvarchar] (50),
	[Packages] [nvarchar] (50)
)
go

This table can then be populated with rows that map individual Add/Remove Programs entries to SMS packages.

use [BDDAdminDB]
go

INSERT INTO [PackageMapping] (ARPName, Packages) 
 VALUES('Office8.0', 'XXX0000F-Install Office 2003 Professional')
go

Once done, any computer that has the "Office8.0" entry in Add/Remove Programs (indicating that Microsoft Office 97 is installed) will have Office 2003 Professional installed. Similar entries can be added for any other packages; any Add/Remove Programs for which there is no entry will be ignored (no package will be installed).

With this table in place, it is then possible to create a stored procedure to simplify the joining of this information with the SMS inventory data.

use [BDDAdminDB]
go

if exists (select * from dbo.sysobjects where id = 
 object_id(N'[dbo].[RetrievePackages]') and 
 OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RetrievePackages]
go

CREATE  PROCEDURE [dbo].[RetrievePackages]
@MacAddress CHAR(17)
AS

SET NOCOUNT ON

/* Select and return all the appropriate records based on current inventory */
SELECT * FROM PackageMapping
WHERE ARPName IN
(
  SELECT ProdID0 FROM SMS_SMS.dbo.v_GS_ADD_REMOVE_PROGRAMS a, 
  	SMS_SMS.dbo.v_GS_NETWORK_ADAPTER n
  WHERE a.ResourceID = n.ResourceID AND
  MACAddress0 = @MacAddress
)
go

In this case, the SMS central primary site database is assumed to reside on the same computer running SQL Server as the BDDAdminDB database; if this is not the case, some modifications will be needed to the stored procedure. Also, the name of the SMS database (in this case, SMS_SMS) will need to be updated. It may also be necessary to grant additional accounts read access to the v_GS_ADD_REMOVE_PROGRAMS view in the SMS database.

With this stored procedure in place, it must still be put to use by editing the CustomSettings.ini to call it. This can be done by adding an entry to the Priority line in the Settings section.

[Settings]
…
Priority=MacAddress, DefaultGateway, DynamicPackages, Default
…

This new DynamicPackages section must then specify the name of a database section.

[DynamicPackages]
SQLDefault= DB_DynamicPackages

Then that section specifies the database connection information and query details.

[DB_DynamicPackages]
SQLServer=SERVER1
Database=BDDAdminDB
StoredProcedure=RetrievePackages
Parameters=MacAddress
SQLShare=Logs

In this case, the BDDAdminDB database on the computer running SQL Server named SERVER1 will be queried. This database contains a stored procedure named RetrievePackages (created above).

EXECUTE RetrievePackages ?

The first MacAddress value will be substituted for the "?" values above. (Because stored procedures cannot take a variable number of arguments, it is not possible to pass all MAC address values to the stored procedure. If this causes any issues, the stored procedure can be replaced with a view, as the query to the view can include the "IN" clause required to pass all MAC address values.)

Assuming the current computer has the Office8.0 value inserted into the table (above), one row will be returned indicating that package "XXX0000F-Install Office 2003 Profession" should be installed by the Zero Touch Installation process during the state restore phase.

Top of page

18 of 20