As the next step we will provide mechanism for detecting if given SQL Server instance is locally installed. This mechanism assumes that a given instance name (eg. MYINSTANCE) is exclusively used by the application to be installed. This does not mean that the user will not be able to use other instances that might exist on the local machine, only that the installer will deal with existence or installation of SQL Server with that specific instance name.

Adding prerequisite

SharpSetup does not come with prerequisite module designed specifically for SQL Server. However, we can use GenericExe prerequisite to install SQL Server:

  • Go to Assembler project, right-click Prerequisites folder and click on "Add Prerequisite..." entry.
  • In the "Add Prerequisite Dialog" select "Generic Exe" in the first drop down, change the file name to sql.sspf and click OK.
  • Double click the sql.sspf file to open prerequiste configuration editor.
  • In left hand side grid set CloseHandles to True (makes SharpSetup close all MSI handles prior to installing this prerequisite), CommandLineArguments to "/Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install /FEATURES=SQLEngine /INSTANCENAME=MYINSTANCE /SAPWD=(SAPWD) /SECURITYMODE=SQL", RebootAction to RebootImmediate (the system will restarted immediately after the SQL Server requests it; user will be asked however) and Visible to False (will prevent entry from being displayed in PrerequisiteList if one is used eg. on PrerequisiteCheckStep). Make sure the IsInstalledProperty is set to SQL_INSTALLED (need to match the name we used in previous articles in C# code).
  • In the right hand side of the editor please configure sql.exe file so that it is correctly resolved during installer runtime. In the simplest form (suitable for testing only) make sure sql.exe file is defined (top part) and it has Retrieve local file provider executed on Installation configured (middle part) for which there is a fixed path defined eg. c:\SQLEXPR_x64_ENU.exe (bottom part).
  • Note that this setup has been tested with SQL Server Express 2012.

After completing this part we have a mechanism for installing SQL Server Express. However, no databases will be set up.

Adding .wxs file

To install new database or upgrade existing database we need to add .wxs file that will handle this part.

  • Go to Package project, right click the project node in Solution Explorer and select Add->New Item... menu entry.
  • In the dialog that will show up select Sql Server Database feature, change the name to Product.Sql.wxs and click Add.
  • Add reference to Sql.ComponentGroup in the feature of your choice (typically in Product.Features.wxs file) using <ComponentGroupRef Id="Sql.ComponentGroup"/> fragment.
  • Go to solution folder in Windows Explorer, enter the ProductBuild folder and create createDb.sql file with the following contents:
    CREATE TABLE Tests (Id INT IDENTITY(1,1) PRIMARY KEY, Name nchar(20));
    INSERT INTO Tests(Name) VALUES('Test 1');
    GO
  • Go to solution folder in Windows Explorer, enter the ProductBuild folder and create upgradeDb.sql file with the following contents:
    IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'Count' AND Object_ID = Object_ID(N'Tests'))
    BEGIN
        ALTER TABLE Tests ADD Count int NULL;
    END
  • In Product.Sql.wxs file and add the following lines after sql:SqlScript tag with Id attribute of Sql.UpgradeScript.$(var.AuthType):
    <sql:SqlString Id='Sql.UpgradeAlterLogin.$(var.AuthType)' SqlDb="Sql.ExistingDatabase.$(var.AuthType)" SQL="ALTER LOGIN AppLogin WITH PASSWORD=N'[RANDOM_PASSWORD]'" ExecuteOnInstall='yes' Sequence='2000' />
  • In Product.Sql.wxs file and add the following lines after sql:SqlScript with Id attribute of Sql.UpgradeScript.$(var.AuthType):
    <sql:SqlString Id='Sql.CreateLogin.$(var.AuthType)' SqlDb="Sql.NewDatabase.$(var.AuthType)" SQL="CREATE LOGIN AppLogin WITH PASSWORD=N'[RANDOM_PASSWORD]'" ContinueOnError='yes' ExecuteOnInstall='yes' Sequence='2000' />
    <sql:SqlString Id='Sql.AlterLogin.$(var.AuthType)' SqlDb="Sql.NewDatabase.$(var.AuthType)" SQL="ALTER LOGIN AppLogin WITH PASSWORD=N'[RANDOM_PASSWORD]'" ExecuteOnInstall='yes' Sequence='2001' />
    <sql:SqlString Id='Sql.CreateUser.$(var.AuthType)' SqlDb="Sql.NewDatabase.$(var.AuthType)" SQL="CREATE USER AppUser FOR LOGIN AppLogin" ExecuteOnInstall='yes' Sequence='2002' />
    <sql:SqlString Id='Sql.CreateAddRole.$(var.AuthType)' SqlDb="Sql.NewDatabase.$(var.AuthType)" SQL="EXEC sp_addrolemember N'db_owner', N'AppUser'" ExecuteOnInstall='yes' Sequence='2003' />
  • Note that RANDOM_PASSWORD is handled in part concerning installation of IIS website.
  • Build the solution and run the main installer executable from Debug (or Release) subfolder under solution folder.