Install Sql Server database using Wix

We decided to use a database for logging for this new project I'm working on, which presented us with a problem, how to automate the installation of the Logging database? The answer was Wix, of course, but how?

I believe there are two ways of using Wix to install a database. This is my favourite way, which simply entails using sqlcmd to run a sql script that creates a DB.

Product.wxs file:

  <?xml version="1.0" encoding="UTF-8"?>
  <Wix xmlns="http://schemas.microsoft.com/wix/2006/wi" 
   xmlns:util="http://schemas.microsoft.com/wix/UtilExtension">
  
    <Product Id="702881b5-9a64-4ab8-ab47-f3babcd950a2"
            Name="Logging Database" Language="1033"
          Version="$(var.versionNumber)"
            Manufacturer="SEPH"
            UpgradeCode="42b7872b-78c8-4a0b-abcd-28a30c9804ab">
  
      <Package InstallerVersion="200" Compressed="yes"  Platform="x64" InstallScope="perMachine"/>
  
      <Media Id="1" Cabinet="media1.cab" EmbedCab="yes" />
  
      <!--Find the Path to the sqlcmd.exe by performing a registry search. This is hardcoded for SQL 2012.
      Change 110 to 100 for 2008. Might need to change the script too.-->
      <Property Id="SQLBINDIR">
        <RegistrySearch Id="SqlBinDir"
          Root="HKLM" Key="SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\ClientSetup"
          Name="Path"
          Type="raw"
          Win64="yes"/>
      </Property>
  
      <Condition Message="Microsoft SQL Server 2012 needs to be installed before this installer can run">
        <![CDATA[SQLBINDIR]]>
      </Condition>
  
      <Directory Id="TARGETDIR" Name="SourceDir">     
          <Directory Id="INSTALLLOCATION" Name="Logging Database">
            <Component Id="Database" Guid="a8dc2Fcd-087d-e393-b059-c67877e51b8a">
              <File Id="LoggingDBCreationScript" Source="SQLScripts\LoggingDatabase.sql"/>
              <RemoveFile Id ="RemoveLoggingInstall.log" Name="LoggingInstall.log" On="uninstall" />            
            </Component>
        </Directory>
      </Directory>
  
      <Feature Id="ProductFeature" Title="Database" Level="1">
        <Feature Id="Database" Title="Logging Database" Description="Logging Database" Level="1" Display="expand" AllowAdvertise="no"
                        ConfigurableDirectory="INSTALLLOCATION" Absent="allow">
          
          <ComponentRef Id="Database"/>
        </Feature>
      </Feature>
  
      <InstallExecuteSequence>
  
        <!--If the database feature is selected these actions will run to install the DB-->
        <Custom Action="logging.cmd" After="InstallFiles" >
          <![CDATA[NOT Installed]]>
        </Custom>
  
        <!--Ensure this runs after the custom action to set up the properties for its cmd line-->
        <Custom Action="logging" After="logging.cmd">
          <![CDATA[NOT Installed]]>
        </Custom>
        
      </InstallExecuteSequence>
  
      <UI>
        <UIRef Id="GUI"/>
        <UIRef Id="WixUI_ErrorProgressText" />
      </UI>
  
      <!--This is the sequence script. Best way to understand this is by running sqlcmd -?
          Use "property" Custom action to get variable substitution working-->
      <CustomAction Id="logging.cmd"  Property="logging"
    Value="&quot;[SQLBINDIR]sqlcmd.exe&quot; -E -S &quot;[SQLSERVER]&quot; -i &quot;[#LoggingDBCreationScript]&quot; -o &quot;[INSTALLLOCATION]LoggingInstall.log&quot;"/>
  
      <!-- Note that the cmd line and args will come from a property with the same name as the custom action, this has been set by the custom action above -->
      <CustomAction Id="logging"  BinaryKey="WixCA"   DllEntry="CAQuietExec"   Return="check"   Execute="deferred"   Impersonate="yes"  />
  
    </Product>
  </Wix>

Gui.wxs file:

  <?xml version="1.0" encoding="utf-8"?>
  <Wix xmlns="http://schemas.microsoft.com/wix/2006/wi">
    <Fragment>
      <WixVariable Id="WixUIBannerBmp" Value="Images\WixTopBanner.jpg" />
      <!-- This should be 493 x 58-->
      <WixVariable Id="WixUIDialogBmp" Value="Images\WixSideBanner.jpg" />
      <!--This should be 493 x 312.-->
      <UI Id="GUI">
        <TextStyle Id="WixUI_Font_Normal" FaceName="Tahoma" Size="8" />
        <TextStyle Id="WixUI_Font_Bigger" FaceName="Tahoma" Size="12" />
        <TextStyle Id="WixUI_Font_Title" FaceName="Tahoma" Size="9" Bold="yes" />
        <Property Id="DefaultUIFont" Value="WixUI_Font_Normal" />
        <Property Id="WixUI_Mode" Value="FeatureTree" />
        <DialogRef Id="BrowseDlg" />
        <DialogRef Id="DiskCostDlg" />
        <DialogRef Id="ErrorDlg" />
        <DialogRef Id="FatalError" />
        <DialogRef Id="FilesInUse" />
        <DialogRef Id="MsiRMFilesInUse" />
        <DialogRef Id="PrepareDlg" />
        <DialogRef Id="ProgressDlg" />
        <DialogRef Id="ResumeDlg" />
        <DialogRef Id="UserExit" />
  
        <Publish Dialog="ExitDialog" Control="Finish" Event="EndDialog" Value="Return" Order="999">1</Publish>
        <Publish Dialog="WelcomeDlg" Control="Next" Event="NewDialog" Value="CustomizeDlg">1</Publish>
        <Publish Dialog="CustomizeDlg" Control="Back" Event="NewDialog" Value="MaintenanceTypeDlg" Order="1">Installed</Publish>
        <Publish Dialog="CustomizeDlg" Control="Back" Event="NewDialog" Value="WelcomeDlg" Order="2">NOT Installed</Publish>
        <Publish Dialog="CustomizeDlg" Control="Next" Event="NewDialog" Value="DatabaseDlg">1</Publish>
        <Publish Dialog="DatabaseDlg" Control="Back" Event="NewDialog" Value="CustomizeDlg">1</Publish>
        <Publish Dialog="DatabaseDlg" Control="Next" Event="NewDialog" Value="VerifyReadyDlg">1</Publish>
        <Publish Dialog="VerifyReadyDlg" Control="Back" Event="NewDialog" Value="DatabaseDlg" Order="1">NOT Installed OR WixUI_InstallMode = "Change"</Publish>
        <Publish Dialog="VerifyReadyDlg" Control="Back" Event="NewDialog" Value="MaintenanceTypeDlg" Order="2">Installed</Publish>
        <Publish Dialog="MaintenanceWelcomeDlg" Control="Next" Event="NewDialog" Value="MaintenanceTypeDlg">1</Publish>
        <Publish Dialog="MaintenanceTypeDlg" Control="ChangeButton" Event="NewDialog" Value="CustomizeDlg">1</Publish>
        <Publish Dialog="MaintenanceTypeDlg" Control="RepairButton" Event="NewDialog" Value="VerifyReadyDlg">1</Publish>
        <Publish Dialog="MaintenanceTypeDlg" Control="RemoveButton" Event="NewDialog" Value="VerifyReadyDlg">1</Publish>
        <Publish Dialog="MaintenanceTypeDlg" Control="Back" Event="NewDialog" Value="MaintenanceWelcomeDlg">1</Publish>
        <Property Id="ARPNOMODIFY" Value="1" />
        
        <Dialog Id="DatabaseDlg" Width="370" Height="270" Title="[ProductName] Setup">
          <Control Id="Title" Type="Text" X="15" Y="6" Width="200" Height="15" Transparent="yes" NoPrefix="yes" Text="{\WixUI_Font_Title}Database Settings" />
          <Control Id="Description" Type="Text" X="25" Y="23" Width="280" Height="15" Transparent="yes" NoPrefix="yes" Text="Enter Database Details" />
          <Control Id="BannerBitmap" Type="Bitmap" X="0" Y="0" Width="370" Height="44" TabSkip="no" Text="!(loc.InstallDirDlgBannerBitmap)" />
          <Control Id="BannerLine" Type="Line" X="0" Y="44" Width="370" Height="0" />
  
          <Control Id="DBServerLabel" Type="Text"   X="20" Y="65" Width="290" Height="13" Text="Logging DB Database Server Instance:" />
          <Control Id="DBServer" Type="Edit"    X="20" Y="77" Width="320" Height="15" Property="SQLSERVER" ToolTip="Change for Test Environments"/>
  
  
          <Control Id="BottomLine" Type="Line" X="0" Y="234" Width="370" Height="0" />
          <Control Id="Next" Type="PushButton" X="236" Y="243" Width="56" Height="17" Default="yes" Text="Next">
            <Condition Action="disable">
              (SQLSERVER = "")
            </Condition>
            <Condition Action="enable">
              <![CDATA[(SQLSERVER <> "")]]>
            </Condition>
          </Control>
          <Control Id="Back" Type="PushButton" X="180" Y="243" Width="56" Height="17" Text="Back" />
          <Control Id="Cancel" Type="PushButton" X="304" Y="243" Width="56" Height="17" Cancel="yes" Text="Cancel">
            <Publish Event="SpawnDialog" Value="CancelDlg">1</Publish>
          </Control>
        </Dialog>     
      </UI>
      <UIRef Id="WixUI_Common" />
    </Fragment>
  </Wix>

Sql Script

 /****** Object:  Database Logging    Script Date: 8/22/2005 ******/
  USE [master]
  GO
  
  IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Logging')
          DROP DATABASE [Logging]
  GO
  
  CREATE DATABASE [Logging]
   COLLATE SQL_Latin1_General_CP1_CI_AS
  GO
  
  exec sp_dboption N'Logging', N'autoclose', N'false'
  GO
  
  exec sp_dboption N'Logging', N'bulkcopy', N'false'
  GO
  
  exec sp_dboption N'Logging', N'trunc. log', N'false'
  GO
  
  exec sp_dboption N'Logging', N'torn page detection', N'true'
  GO
  
  exec sp_dboption N'Logging', N'read only', N'false'
  GO
  
  exec sp_dboption N'Logging', N'dbo use', N'false'
  GO
  
  exec sp_dboption N'Logging', N'single', N'false'
  GO
  
  exec sp_dboption N'Logging', N'autoshrink', N'false'
  GO
  
  exec sp_dboption N'Logging', N'ANSI null default', N'false'
  GO
  
  exec sp_dboption N'Logging', N'recursive triggers', N'false'
  GO
  
  exec sp_dboption N'Logging', N'ANSI nulls', N'false'
  GO
  
  exec sp_dboption N'Logging', N'concat null yields null', N'false'
  GO
  
  exec sp_dboption N'Logging', N'cursor close on commit', N'false'
  GO
  
  exec sp_dboption N'Logging', N'default to local cursor', N'false'
  GO
  
  exec sp_dboption N'Logging', N'quoted identifier', N'false'
  GO
  
  exec sp_dboption N'Logging', N'ANSI warnings', N'false'
  GO
  
  exec sp_dboption N'Logging', N'auto create statistics', N'true'
  GO
  
  exec sp_dboption N'Logging', N'auto update statistics', N'true'
  GO
  
  use [Logging]
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Category]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
  CREATE TABLE [dbo].[Category](
          [CategoryID] [int] IDENTITY(1,1) NOT NULL,
          [CategoryName] [nvarchar](64) NOT NULL,
   CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
  (
          [CategoryID] ASC
  ) ON [PRIMARY]
  ) ON [PRIMARY]
  END
  GO
  SET ANSI_NULLS OFF
  GO
  SET QUOTED_IDENTIFIER OFF
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CategoryLog]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
  CREATE TABLE [dbo].[CategoryLog](
          [CategoryLogID] [int] IDENTITY(1,1) NOT NULL,
          [CategoryID] [int] NOT NULL,
          [LogID] [int] NOT NULL,
  CONSTRAINT [PK_CategoryLog] PRIMARY KEY CLUSTERED 
 (
         [CategoryLogID] ASC
 ) ON [PRIMARY]
 ) ON [PRIMARY]
 END
 GO
 SET ANSI_NULLS OFF
 GO
 SET QUOTED_IDENTIFIER OFF
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Log]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
 CREATE TABLE [dbo].[Log](
         [LogID] [int] IDENTITY(1,1) NOT NULL,
         [EventID] [int] NULL,
         [Priority] [int] NOT NULL,
         [Severity] [nvarchar](32) NOT NULL,
         [Title] [nvarchar](256) NULL,
         [Timestamp] [datetime] NOT NULL,
         [MachineName] [nvarchar](32) NOT NULL,
         [AppDomainName] [nvarchar](512) NOT NULL,
         [ProcessID] [nvarchar](256) NOT NULL,
         [ProcessName] [nvarchar](512) NOT NULL,
         [ThreadName] [nvarchar](512) NULL,
         [Win32ThreadId] [nvarchar](128) NULL,
         [Message] [nvarchar](2500) NULL,
         [FormattedMessage] [ntext] NULL,
  CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
 (
         [LogID] ASC
 ) ON [PRIMARY]
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 END
 GO
 SET ANSI_NULLS OFF
 GO
 SET QUOTED_IDENTIFIER OFF
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[InsertCategoryLog]') AND type in (N'P', N'PC'))
 BEGIN
 EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE InsertCategoryLog
         @CategoryID INT,
         @LogID INT
 AS
 BEGIN
         SET NOCOUNT ON;
 
         DECLARE @CatLogID INT
         SELECT @CatLogID FROM CategoryLog WHERE CategoryID=@CategoryID and LogID = @LogID
         IF @CatLogID IS NULL
         BEGIN
                 INSERT INTO CategoryLog (CategoryID, LogID) VALUES(@CategoryID, @LogID)
                 RETURN @@IDENTITY
         END
         ELSE RETURN @CatLogID
 END
 ' 
 END
 GO
 SET ANSI_NULLS OFF
 GO
 SET QUOTED_IDENTIFIER OFF
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddCategory]') AND type in (N'P', N'PC'))
 BEGIN
 EXEC dbo.sp_executesql @statement = N'
 
 CREATE PROCEDURE [dbo].[AddCategory]
         -- Add the parameters for the function here
         @CategoryName nvarchar(64),
         @LogID int
 AS
 BEGIN
         SET NOCOUNT ON;
     DECLARE @CatID INT
         SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @CategoryName
         IF @CatID IS NULL
         BEGIN
                 INSERT INTO Category (CategoryName) VALUES(@CategoryName)
                 SELECT @CatID = @@IDENTITY
         END
 
         EXEC InsertCategoryLog @CatID, @LogID 
 
         RETURN @CatID
 END
 
 ' 
 END
 GO
 SET ANSI_NULLS OFF
 GO
 SET QUOTED_IDENTIFIER OFF
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[ClearLogs]') AND type in (N'P', N'PC'))
 BEGIN
 EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE ClearLogs
 AS
 BEGIN
         SET NOCOUNT ON;
 
         DELETE FROM CategoryLog
         DELETE FROM [Log]
     DELETE FROM Category
 END
 ' 
 END
 GO
 SET ANSI_NULLS OFF
 GO
 SET QUOTED_IDENTIFIER OFF
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[WriteLog]') AND type in (N'P', N'PC'))
 BEGIN
 EXEC dbo.sp_executesql @statement = N'
 
 
 
 /****** Object:  Stored Procedure dbo.WriteLog    Script Date: 10/1/2004 3:16:36 PM ******/
 
 CREATE PROCEDURE [dbo].[WriteLog]
 (
         @EventID int, 
         @Priority int, 
         @Severity nvarchar(32), 
         @Title nvarchar(256), 
         @Timestamp datetime,
         @MachineName nvarchar(32), 
         @AppDomainName nvarchar(512),
         @ProcessID nvarchar(256),
         @ProcessName nvarchar(512),
         @ThreadName nvarchar(512),
         @Win32ThreadId nvarchar(128),
         @Message nvarchar(1500),
         @FormattedMessage ntext,
         @LogId int OUTPUT
 )
 AS 
 
         INSERT INTO [Log] (
                 EventID,
                 Priority,
                 Severity,
                 Title,
                 [Timestamp],
                 MachineName,
                 AppDomainName,
                 ProcessID,
                 ProcessName,
                 ThreadName,
                 Win32ThreadId,
                 Message,
                 FormattedMessage
         )
         VALUES (
                 @EventID, 
                 @Priority, 
                 @Severity, 
                 @Title, 
                 @Timestamp,
                 @MachineName, 
                 @AppDomainName,
                 @ProcessID,
                 @ProcessName,
                 @ThreadName,
                 @Win32ThreadId,
                 @Message,
                 @FormattedMessage)
 
         SET @LogID = @@IDENTITY
         RETURN @LogID
 
 
 
 ' 
 END
 GO
 SET ANSI_NULLS OFF
 GO
 SET QUOTED_IDENTIFIER OFF
 GO
 IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CategoryLog_Category') AND parent_obj = OBJECT_ID(N'[dbo].[CategoryLog]'))
 ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Category] FOREIGN KEY(      [CategoryID])
 REFERENCES [dbo].[Category] (   [CategoryID])
 GO
 IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CategoryLog_Log') AND parent_obj = OBJECT_ID(N'[dbo].[CategoryLog]'))
 ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Log] FOREIGN KEY(   [LogID])
 REFERENCES [dbo].[Log] (        [LogID])
 GO
 
 SET QUOTED_IDENTIFIER ON 
 SET ARITHABORT ON 
 SET CONCAT_NULL_YIELDS_NULL ON 
 SET ANSI_NULLS ON 
 SET ANSI_PADDING ON 
 SET ANSI_WARNINGS ON 
 SET NUMERIC_ROUNDABORT OFF 
 go
 
 DECLARE @bErrors as bit
 
 BEGIN TRANSACTION
 SET @bErrors = 0
 
 CREATE NONCLUSTERED INDEX [ixCategoryLog] ON [dbo].[CategoryLog] ([LogID] ASC, [CategoryID] ASC )
 IF( @@error <> 0 ) SET @bErrors = 1
 
 IF( @bErrors = 0 )
   COMMIT TRANSACTION
 ELSE
   ROLLBACK TRANSACTION