Wix: create sql server database at specified location

Question

I have an installer created using Wix. I would like to indicate where the sql server will be installed. One solution that I can think of is to put the placeholder in the CreateDatabase script, and at runtime just replace the placeholder with the actual path specified by the user.

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

Thanks,

Answer 1

I ultimately solve this problem using dynamic SQL. For those seeking a solution, please check the accepted answer to the following question:

SQL Server: use parameter in CREATE DATABASE

Answer 2

You can use the standard WiX SQL extension . For example, the SqlString element provides the ability to specify an SQL query to execute during installation. The SqlDatabase element provides a ready-made option for creating an SQL database. Both support the Windows Installer properties for the @SQL and @Server attributes, respectively. This means that you can get user input, save it in a property, and use this property in Sql elements.

Answer 3

You cannot get it for free from WiX.

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

• DB_SERVER
• DB_INSTANCE
• DB_PORT
• DB_DATA_FOLDER
• DB_LOG_FOLDER

If you expect to use SQL auth, you will of course need to pass DB_USER / DB_PASS

I would highly recommend writing your own actions to check the parameters.

• Is there a server / instance on the local computer? Although you can always remotely connect to the SQL server and inform it about the installation of files on this drive, it makes it difficult to confirm anything about these drives in advance.
• Using this server / instance / port and credentials, you can connect to the main SQL Server database, you can even check the permission to create a database in advance. You can do without using a port, but some database administrators are still looking for protection through obscurity.
• Have you provided valid data / log folders. Root paths to existing drives.
• Checking for sufficient disk space on the Data / Log is also a good idea. I came across a large number of clients who do not check and complete their data files on a tiny C: drive with a huge huge D: data drive. Usually I use the minimum free space for the combination, as well as the minimum free percentage (for example, at least 25% of free disk space).

Answer 4

Extending the answer to Yan, there is a very simple syntax if you use the Sql extension. You need both MDF and LDF (data + log files) included for your SQL database, but then attach them by referencing the same file name.

It is further assumed that the sql express server will be installed locally in 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 skipped the authentication from this example for brevity, but you can also specify the Sql user to use with the User command and referring to the identifier of the User element in the SqlDatabase attribute.