Answer
You can choose to let the installing user pick an SQL Server to connect to, from the available Microsoft SQL Servers on the network. Advanced Installer, starting with version 6.4, can automatically browse the network to discover any SQL Server (instances) online.
Go to the Custom Actions page and add the predefined UI Custom
Action "Browse for SQL Servers".
Go to the Dialog Editor
page.
Add the Advanced Installer "SQLConnectionDlg" to
the dialogs tree.
You can use this functionality with any other dialog of your choice,
or you can customize the Advanced Installer built-in dialog.
Browse Method
Advanced Installer supports two methods for listing the SQL Servers from a network:
- Enumerate SQL Servers in the primary domain using Windows Network Manager API (NetServerEnum).
- Enumerate SQL Server instances on the network using the ODBC Driver Manager (SQLBrowseConnect).
(Optional) You can configure which browse method you want to use by setting the corresponding value for the SqlServerBrowseMethod property. In order to use Windows Network Manager API (default), set the property to 1 ; to use the ODBC Driver Manager functionality, set the property to 2 .
Go to the Initialization Events tab and add a new "SetProperty" event with the following parameters:
- Name: [SqlServerBrowseMethod]
- Argument: 2 (browse using ODBC Driver Manager)
- Condition: 1
ODBC Driver Manager enumerates SQL Server
instances using a network broadcast (UDP port 1434). This
means it will not always be able to return a conclusive or complete list
of SQL Server instances because:
- UDP port 1434 might be blocked by a firewall;
- Instances that are not up and running do not respond to the discovery broadcast;
- Instances might respond after the timeout, which will exclude there response from the list;
- Instances might not be listening on the correct protocol, so will never receive the discovery broadcast;
- The HideInstance flag might be set for some instances;
- Network infrastructure, especially routers, might limit the effect/visibility of the broadcast, since most routers are configure not to relay broadcast request to the other side of the router;
However, browsing the network using ODBC Driver Manager
functionality can return more information about servers that have
SQL Server Browser service running. For example:
ServerName\InstanceName;Clustered:No;Version:9.00.3042.00
Connection Timeout
(Optional) You can specify a connection timeout (in seconds) for the browse operation. The default is 60 seconds. The timeout can be specified through the Windows Installer property SqlConnectionTimeout. You can set this property statically (from Install Parameters), or dynamically (from the installer UI - associated with an Edit Control, or with a SetProperty Initialization Event).
A "Connection Timeout" can be set only when using ODBC Driver
Manager to browse the network, and has no effect when
enumerating SQL Servers with Windows Network Manager
API.
You can suppress the spawned message box for the SQL
Server browse operation by setting the SqlSuppressSpawnMsgBox property to 1 .
You can set this property statically (from Install Parameters), or dynamically (from
the installer UI - associated with
an Edit Control, or with a
SetProperty
Initialization Event).
Finally, add an Initialization Event with the following settings:
- Name: DoAction
- Argument: BrowseSqlServers (the name of the custom action you created earlier)
- Condition: 1
Browse Result (output)
Advanced Installer dumps the result of the browse operation (the SQL Servers list) in the following locations:
- Any ComboBox control that has the property SqlServerBrowseComboBox associated with it.
- Any ListBox control that has the property SqlServerBrowseListBox associated with it.
- Advanced Installer "SQLConnectionDlg": you can replace the Edit control (ServerEdit) with a ComboBox control that has the same SERVER_PROP property attached to it.
- A flat, comma separated list of SQL Servers (names only) will be dumped into the SqlServerBrowseOutput property. You can used this value if you wish to do any custom processing of the servers list.
For ComboBox and ListBox controls, the
output format will be as follows:
- Item text: ServerName (Server's Description) - when using Windows Network Manager API, or ServerName\InstanceName;Clustered:No;Version:9.00.3042.00 - when using ODBC Driver Manager (The item text could be reduced to just the server name, if the remote machine did not provide any additional information.)
- Item value: ServerName (always)
See also: Test SQL Connection