SQL Alias vs DNS Alias for Project Server 2010 install #PS2010 #EPM #ProjectServer
When installing Project Server 2010 it is always best practice to use an alias name for the SQL server as this makes life easier should you want to move SQL servers at a later date. This raises the question, SQL alias (using SQL cliconfg utility – pic below) or a network DNS Alias name (CNAME or HOST Record).
Pic1: SQL Server Client Network Utility (cliconfg)
For Project Server 2007 I would have said there weren’t really any advantages for one method over the other but for Project Server 2010 it’s different. For Project Server 2010 I would always recommend using a network DNS Alias, the reason being is to do with creating Excel Services reports.
When a user either creates a new or edits an existing report in Excel, their Excel client will connect directly to the data source (either Reporting database, custom database or OLAP cube). The ODC file will contain the connection details, SQL server name (alias name if Project Server was installed with an Alias for SQL), and database or cube name. If you installed Project Server 2010 with a SQL Alias on the Application Server, the user’s client machine will not know anything about the SQL Alias name unless you set up the same SQL Alias on all users machines who will create Excel Reports. This is because SQL Alias are only local to the machine that is it created on. Using a network DNS Alias to alias the SQL server will remove this issue.
Paul Mather
SharePoint (and Project Server) Shenanigans 
Thanks for this, it can be a real life-saver!
I just ran into this scenario, but combined with a named SQL Server instance – which kind of breaks everything. Sure, the client can find the correct SQL Server using the SQL alias, thanks to a DNS alias with the same name resolving to the correct SQL server. But no instance information is passed on, thus giving the user an “Instance error” when trying to view the sample reports in Excel. (I don’t remember the specific error message text.)
Is there a way around the above? Or should you just make sure to use “server\instance” in your manually created .odc files instead of an SQL alias? I don’t really consider distributing SQL aliases to clients via GPO as an option. SQL aliases could anyway be considered as a “server exclusive” convenience…
Hi there,
For the OOB Reporting DB ODC files and manually created ODC files, you could update the SQL Server name for DNSAlias\instance. For the OLAP ODC files, you could just update the server name\instance on the OLAP database build settings page. Once the cube is rebuilt the OLAP ODC files will update.
Paul