I believe a better question would have been... SQL Alias or DNS Alias?
SQL Alias (AKA WINS)
- Good to use if you are not a DNS administrator and you want to have full control over the alias setup.
Good if you change the default listening port of SQL Server 1433 for something else. (Improve the database server security)
- You have to configure it on every SP server (WFE and APP servers)
- You have to install SQL Server add-in on non-SQL server machines.
!! Recent versions of Windows operating systems have the SQL Server Client Network Utility built-in. (Command Prompt: **cliconfg**). You do not need to install anything.
When configuring DNS Alias in DNS manager, I suggest you use A record, and not CNAME.
DNS Alias is now the MS recommended approach to configuring SQL Server instances for SharePoint. reference (see the start of the second video)
Instructions on how to setup DNS Alias can be found here
I personally use DNS alias. On the SQL Server machine, I use the same IP for all the instances and the SQL Server machine itself, but the ports are different. The main reason is that I am not a fan of using multi-homing (multiple ip on a single NIC).
Ex: let's say I have server called SPSQL01 and two instances on that server called: SPDB01 and SPDB02.
- Create A record for each of those instance in the DNS Manager
- In the SQL Server, go to the SQL Server Configuration Manager
- Under SQL Server Network Configuration, select "Protocols for SPDB01"
- Select Protocol tab, set Listen All to No
- Select IP Addresses tab, for IP1 set Active=Yes, Enabled=Yes, IP Address=ip set in DNS manager for this instance, TCP Dynamic Ports=make blank, TCP Port=1433 or your choice
- Select Apply, close.
- Under SQL Server Network Configuration, select "Protocols for SPDB02"
- Repeat step 4-6, using the same IP, but different port.