SQL Alias vs DNS Alias for Project Server 2010 install #PS2010 #EPM #ProjectServer

July 6, 2011 at 5:44 pm | Posted in Installation | 2 Comments
Tags: , ,

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).

image

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

Advertisements

2 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Hi Paul,
    disadvantage of the DNS alias is, that it will neither allow for mapping for a specific port (to avoid browser Service), nor to allow to set the alias for a specific SQL instance (servername\instance) what might be necessary if e.g. a cluster is used.

    • Hi Christoph,

      There are occasions you will want to use a SQL Alias, just bear in mind that the client machines that connect to the SQL server to create the Project Server excel services reports will need to have the same SQL alias created locally.

      Thanks
      Paul


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: