Dynamics AX 2012 R2 Retail deployment with SQL Server named instance

Just a quick post to share the experience on deploying Dynamics AX 2012 R2 Retail online store with SQL Server named instance.
The deployment is done by following the guide at: http://technet.microsoft.com/en-us/library/jj991927.aspx

In brief:

• The Online store deployment script is not written to covers SQL Server named instance
• If you’re using a named instance instead of default instance, the easiest way is to make the named instance listen to default port (act like a default instance)

More details:

I was doing a fresh installation (Windows Server 2012, SQL Server 2012, SharePoint 2013, Dynamics AX 2012 R2, and their respective Service Pack or Cumulative Update).

All goes well until I reach the Online store deployment stage, the PowerShell script “InstallPrereqs-SPFarm.ps1” is complaining the server cannot be pinged. Digging down to the script, it was testing the connection to the server by using the instance name.




Below is part of the “oob-topology.xml” file.
    <Database install="true" dropifexists="true">
      <ServerName>UKLON--VS-W2DV2\SQLSERVER2012</ServerName>
      <DatabaseName>SpFBA</DatabaseName>
      <WindowsLogin id="RetailFBAFullAccessUsers" GroupName="UKLON--VS-W2DV2\RetailFBAFullAccessUsers" CreateIfNotExists="true" MappedSqlRoleName="aspnet_Membership_FullAccess" />
    </Database>
    <WindowsGroupMemberShips>
      <add MachineName="UKLON--VS-W2DV2" GroupName="RetailFBAFullAccessUsers" UserName="TECTURA\sharepointadmin" />
      <add MachineName="UKLON--VS-W2DV2" GroupName="RetailFBAFullAccessUsers" UserName="TECTURA\sharepointadmin" />
</WindowsGroupMemberShips>

Reading the script further, it was found that it assume the SQL server is a default instance, therefore using the server name for the following usage:

• To access the SQL server instance / configure SQL access
• To access Windows server / configure security group
• To check/validate the server name match the security group server name
  
  Due to it assume the SQL Server is default instance, the server name itself would be the name of the Windows server as well. It then compare this against the machine name. This won’t cause issue if it is a default instance, which both the SQL Server instance name and the Windows server name would be the same. But if you have a named instance for your SQL Server, this would cause issue, it is comparing “ServerName\NamedInstance” against “ServerName”, which give error due to mismatch.
  
  

Initially I thought I would just change the “oob-topology.xml” file to use the SQL server named instance as “ServerName” and the Windows server name as “MachineName”.

Then make some changes to the script to cater to this, but it seems that it is used in many places, changing it bit by bit and keep hitting error due to the same reason from various different place is not fun.

So I took another approach, to make the SQL server named instance to “act” like a default instance, that way it can have the same value for both the “ServerName” and “MachineName”.

To do this, below are the steps:

1. Go to: SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <theNamedInstance>
   
   
   
2. Enable the “TCP/IP” is it hasn’t been enabled
   
3. Then right click on the “TCP/IP” and click on “Properties”
   
   
   
4. Click on “IP Addresses” tab, scroll to the bottom
   
   
   
5. At the “IPAll”, empty the “TCP Dynamic Ports” and enter ‘1433’ for “TCP Port”
   
6. Click “OK” to apply and close it, restart SQL Server as needed

This will make the SQL Server named instance listen to port 1433 (default port). Therefore it can access the SQL Server named instance by both the following:

• ServerName\NamedInstance
  Eg. In my case: UKLON--VS-W2DV2\SQLSERVER2012
  
• ServerName
  Eg. In my case: UKLON--VS-W2DV2
  Screenshot showing the SQL Server accessible by both the server name itself and the named instance as well.
  

Then change the “oob-topology.xml” file to use the server name only.
    <Database install="true" dropifexists="true">
      <ServerName>UKLON--VS-W2DV2</ServerName>
      <DatabaseName>SpFBA</DatabaseName>
      <WindowsLogin id="RetailFBAFullAccessUsers" GroupName="UKLON--VS-W2DV2\RetailFBAFullAccessUsers" CreateIfNotExists="true" MappedSqlRoleName="aspnet_Membership_FullAccess" />
    </Database>
    <WindowsGroupMemberShips>
      <add MachineName="UKLON--VS-W2DV2" GroupName="RetailFBAFullAccessUsers" UserName="TECTURA\sharepointadmin" />
      <add MachineName="UKLON--VS-W2DV2" GroupName="RetailFBAFullAccessUsers" UserName="TECTURA\sharepointadmin" />
</WindowsGroupMemberShips>