I would like to add SQL Server 2014 Analysis Services to a specific instance of SQL Server and to configure it in such a way, that the Analysis Services (SSAS) and future Reporting Services (SSRS) will only pick up requests on the instance-specific IP, in order to allow the SQL Server Browser Service to remain in the state stopped.
I have the following prerequisites regarding a single Windows Server running multiple SQL Server 2014 instances:
- 2 x NICs - 12 x IP Addresses - 10 x SQL Server Server Service Instances (running) - 10 x SQL Server Agent Server Service Instances (running) - 1 x SQL Server Browser Service (not running)
The Windows Server NICs are configured as follows:
- 1 x NIC (Backup Network) - 10.2.0.1 - 1 x NIC (LAN) - 10.2.0.2 (Windows Server) - 10.2.1.1 (SQL Server "Instance01"/ CNAME: servername-ins01) - 10.2.1.2 (SQL Server "Instance02"/ CNAME: servername-ins02) - 10.2.1.3 (SQL Server "Instance03"/ CNAME: servername-ins03) - 10.2.1.4 (SQL Server "Instance04"/ CNAME: servername-ins04) - 10.2.1.5 (SQL Server "Instance05"/ CNAME: servername-ins05) - 10.2.1.6 (SQL Server "Instance06"/ CNAME: servername-ins06) - 10.2.1.7 (SQL Server "Instance07"/ CNAME: servername-ins07) - 10.2.1.8 (SQL Server "Instance08"/ CNAME: servername-ins08) - 10.2.1.9 (SQL Server "Instance09"/ CNAME: servername-ins09) - 10.2.1.10 (SQL Server "Instance10"/ CNAME: servername-ins10)
SQL Server Configuration Manager
The SQL Server Configuration Manager is configured as follows for each "Instance nm" of SQL Server:
- Protocol for 'Instance01' - Shared Memory : Enabled - Named Pipes : Disabled - TCP/IP : Enabled
TCP / IP Properties 'Instance01' - Protocol Tab
- Protocol - Enabled : Yes - Keep Alive : 30000 - Listen All : No
With the above settings each 'Instance nm' will have to be configured individually to respond to IP-specific calls on the configured port. (Note: As an added benefit, each instance will be able to listen on the default SQL Server port 1433, which in turn will greatly simplify firewall configuration, because the you will be able to use the port rule
sql-server. Just saying.)
TCP / IP Properties 'Instance01' - IP Addresses Tab
For the IPn entry which corresponds to the IP address of the specific instance, the settings are as follows:
- IPn - Active : Yes - Enabled : Yes - IP Address : 10.2.1.1 - TCP Dynamic Ports : - TCP Port : 1433 - IPAll - TCP Dynamic Ports : - TCP Port :
Any other IPn entry not related to the specific IP address of the instance will configured as follows:
- IPn - Active : Yes - Enabled : No - IP Address : 10.2.1.xx - TCP Dynamic Ports : - TCP Port :
With the above settings and configuration, each SQL Server instance will run on/with a dedicated IP address on the standard SQL Server port 1433. Each instance has its own CNAME, which allows the users to connect to the SQL Servers:
...without having to specify the port or the instance-name. Another plus is that the SQL Server Browser Service doesn't have to be running to distribute calls to SQL Server instances, because each call is directly routed to the instance specific SQL Server (service).
Add Analysis Services to an Instance
The software vendor would now like to increase the user's experience and provide the ability to run some analytical queries against the existing data. The software vendor requires us to install SQL Server Analysis Services. This is an instance-specific feature and during the installation process you have to select the instance you will be adding the feature to.
To allow connections to the Analysis Services you have to configure the following rule in the firewall:
10.2.0.2:2382 (Browser Service)
If I would have used the default instance (MSSQLSERVER) then I could have configured the following rule in the firewall:
10.2.1.1:2383 (Default Instance)
This is according to the following documentation:
SQL Server 2005 Analysis Services (SSAS) Server Properties (SQL Server 2005, but apparently still valid for current versions)
Connecting to Instance-Specific Analysis Services
After the installation has been finalised you connect to the instance-specific Analysis Services with the following syntax:
SQL Server Browser Service
After configuring the SQL Server instance-specific Analysis Service, I noticed that the SQL Server Browser service was now in the running state.
Oops, this means that requests to the Analysis Services are being routed through the Window Server's IP address (10.2.0.2) to the SQL Server specific Analysis Service instance.
If I stop the SQL Server Browser service, then I can no longer connect to the SQL Server Analysis Services via servername\Instance01.
According to the above mentioned documentation:
The SQL Server Browser service is shared between the SQL Server 2005 Database engine and the Analysis Services engine. For the Database engine, for security purposes, it is recommended to turn the SQL Server Browser service off. This can create a dilemma because you may need it for the Analysis Services engine. It is a recommended practice to turn the service on if you need it, but otherwise leave it off.
Installing the SQL Server Analysis Services has started the SQL Server Browser service, creating a security consideration.
Can I configure the SQL Server Analysis Services of the
Instance01with the CNAME
servername-ins01, to be bound to the IP address
Can I configure SQL Server Analysis Services of my named instance in such a way, so that I can then stop the SQL Server Browser service again?