none
how to generate DNS Alias for SQL Instances and using OLAP in Project Server 2010 RRS feed

  • Question

  • We are using Project Server 2010 and have deployed the whole SharePoint Farm with SQL Aliases. But if you create a Project Server Instance and want to create an OLAP Cube, sql aslises are not working. Many people say the resolution are DNS alises. But we are using SQL Instances and nobody knows how to create DNS aliases to work with sql instances and working for creating OLAP cubes in Project Server 2010 and is supportet by Microsoft!

    thank you for any help
    Steffen


    • Edited by lasslo Friday, January 11, 2013 12:29 PM
    Friday, January 11, 2013 12:28 PM

Answers

  • Hello,

    To confirm, you can get the OLAP cube building with a SQL Alias. Ensure that you set up the same SQL Alias that you set up on the Application server on to the SQL Server - I assume SQL AS is running on the farm SQL Server? Ensure you set both the 32 bit and 64 bit alias on the App and SQL Servers. This should work fine.

    Regarding DNS mapping to a named SQL instance - you can't. All you can do is create a DNS record for the hostname of the SQL Server then \instance name. For example, my SQL server is called machine03 and I'm using a named instance - SharePointSQL. The full instance name is machine03\SharePointSQL. We could create a 32 bit and 64 bit SQL Alias that maps "SPSQL" (our SQL Alias Name) to machine03\SharePointSQL on all servers in the farm including SQL. If we wanted to use DNS, you can only get "halfway" there. You could create a DNS Alias that maps SPSQL (our DNS Alias) to machine03 but the farm would need to use SPSQL\SharePointSQL. DNS Alias will work fine on the default SQL instance.

    Also bear in mind that when using a SQL Alias, the client machines that connect to OLAP cube and PWA Reporting database using Office Excel will need to have the have SQL Alias configured on the local machines.

    Hope that helps

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Tuesday, December 17, 2013 1:37 PM
    Moderator

All replies

  • Hi Steffen,

    you can create an OLAP Cube with SQL aliases - on the DB Server side.

    The Project Server databases can reside on a sql server that is configured with an SQL alias. You will need to define the alias not only on the Project Server machine, but also on the Analysis Services machine. This is necessary, because Project Server forwards the connection string to the Project Server Reporting database to the Analysis Server - and the connection string will contain only the alias.

    The Analysis Server instance itself cannot be adressed with an alias. You will have to configure the correct instance name in the OLAP cube configuration in Project Server server settings. But this has nothing to do with Project Server itself, but is because the SQL alias technology is only usable for the database engine itself.

    I hope that helps

    Kind regards

    Christoph


    Friday, January 11, 2013 3:58 PM
  • Hi Christoph

    that's not really what I want to do. I want to point to an Alias in the Project Server Wizzard to Create a Cube.
    At the moment I see just two ways to do this:

    1. create cube with a fixed SSAS instance name - or
    2. create DNS Alias to a default SSAS instance

    What I need is the possibility to create an Alias to a named SSAS instance.

    thanks
    Steffen

    Tuesday, January 15, 2013 2:41 PM
  • Hi Steffen,

    ok. I understand. I never tried to use a DNS alias for a default SSAS instance. Did you try and it fails? Then perhaps the SSAS server does not respond correctly if he is not adressed with his real name. Perhaps someone over in the SQL forum has an idea here.

    Kind regards

    Christoph


    Christoph Muelder | Senior Consultant, MCSE, MCT | SOLVIN information management GmbH, Germany

    Tuesday, January 15, 2013 4:19 PM
  • Hello,

    To confirm, you can get the OLAP cube building with a SQL Alias. Ensure that you set up the same SQL Alias that you set up on the Application server on to the SQL Server - I assume SQL AS is running on the farm SQL Server? Ensure you set both the 32 bit and 64 bit alias on the App and SQL Servers. This should work fine.

    Regarding DNS mapping to a named SQL instance - you can't. All you can do is create a DNS record for the hostname of the SQL Server then \instance name. For example, my SQL server is called machine03 and I'm using a named instance - SharePointSQL. The full instance name is machine03\SharePointSQL. We could create a 32 bit and 64 bit SQL Alias that maps "SPSQL" (our SQL Alias Name) to machine03\SharePointSQL on all servers in the farm including SQL. If we wanted to use DNS, you can only get "halfway" there. You could create a DNS Alias that maps SPSQL (our DNS Alias) to machine03 but the farm would need to use SPSQL\SharePointSQL. DNS Alias will work fine on the default SQL instance.

    Also bear in mind that when using a SQL Alias, the client machines that connect to OLAP cube and PWA Reporting database using Office Excel will need to have the have SQL Alias configured on the local machines.

    Hope that helps

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Tuesday, December 17, 2013 1:37 PM
    Moderator