Wix: create a sql server database at a specified location [duplicate]

Question

I have an installer created using Wix. I'd like to be able to specify the location where the sql server is installed. One solution I can think of is to put a placeholder in the CreateDatabase script and in the run time just replace the placeholder with the actual path that user specified.

I am wondering if there is any better way to do this? Does Wix provide anything I can use of?

Thanks,

Answer 1

You can take advantage of standard WiX SQL extension. For instance, SqlString element provides an option to specify a SQL query to execute at install time. SqlDatabase element gives an out-of-the-box option to create SQL database. Both accept Windows Installer properties for @SQL and @Server attributes respectively. This means that you can get user input, save it to the property and use that property in Sql elements.

Answer 2

Expanding on Yan's answer, there's a very simple syntax if you use the Sql extension - You need both the MDF and LDF (Data + Log files) included for your SQL database, but then attach them by referencing the same filename.

The following assumes a sql express server will be installed locally under the localhost\sqlexpress instance

<Property Id="SQLINSTANCE" Value="SQLEXPRESS" />
<Property Id="SQLSERVER" Value="LOCALHOST" />    
<Property Id="DATA_FOLDER" Value="C:\Program Files\Microsoft SQL Server\MSSQL11.SQLExpress\MSSQL\DATA\" />

<Component Id="Sql_Database_Deploy" NeverOverwrite="yes" Directory="[DATA_FOLDER]">
    <File Source="Northwind.mdf"></File>
    <File Source="Northwind)log.ldf"></File>
    <sql:SqlDatabase Id="SqlDatabase" Database="Northwind" Server="[SQLSERVER]" Instance="[SQLINSTANCE]" CreateOnInstall="yes" DropOnUninstall="yes">
      <sql:SqlFileSpec Filename="[DATA_FOLDER]Northwind.mdf"  Id="Northwind_SqlFileSpec"/>
      <sql:SqlLogFileSpec Filename="[DATA_FOLDER]Northwind_log.ldf" Id="Northwind_SqlLogFileSpec"/>
    </sql:SqlDatabase>
  </Component>

I've omitted authentication from this example for brevity, but you can also specify a Sql user to use for the command using the User element, and referencing the User element's Id within the SqlDatabase attribute.