Wix: create a sql server database at a specified l

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

I end up solving this problem by using a dynamical SQL. For anyone looking for solution, please check the accepted answer for the following question:

SQL Server: use parameter in CREATE DATABASE

Answer 3

You can't get this for free from WiX.

Using SqlString, some parameters and as you noted direct use of CREATE DATABASE. You'll need:

• DB_SERVER
• DB_INSTANCE
• DB_PORT
• DB_DATA_FOLDER
• DB_LOG_FOLDER

If you are expecting to use SQL auth you'll of course also need to pass in DB_USER/DB_PASS

I would strongly suggest writing your own custom actions to verify the parameters.

• Do the server/instance exist on the local machine? While you can always remotely connect to a SQL server and tell it to install files on a given drive it makes it harder to validate anything about those drives in advance.
• Using the given Server/Instance/Port and credentials can you connect to the sql server master DB, you might even check permission to create DB in advance. You can get away with not using Port but some DBAs still go for security through obscurity.
• Were you given valid Data/Log folders. Rooted paths to drives that actually exist.
• Verifying enough space on the Data/Log folder drive is a good idea as well. I've run into plenty of customer who don't check and wind up with their data files on the tiny C: drive with an empty huge D: data drive. I usually do a combination minimum free space as well as minimum free percentage (e.g. at least 25% free space on disk)

Answer 4

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.