Set up the data mart server

Perform these steps on both the repository and data mart servers.

  1. In the MS SQL Server Management Studio, expand Server Objects.
  2. Right-click Linked Servers and select New Linked Server.
  3. In the Linked server field, specify the server instance.
  4. Select SQL Server as the Server type.
  5. Select the Security page, add the login details for the server instance.
  6. Select the Server Options page and enable these:

    • RPC

    • RPC Out

    Note: 

    These settings enable remote procedure calls.

  7. Set the Enable Promotion of Distributed Transactions to False.
    Note: 

    This setting allows the native client to use remote/network transactions on linked servers.

  8. Click OK.
  9. Navigate to Server Objects > Linked Servers.
  10. Right-click the linked server you created and select Test Connection.