Configuring MS SQL for Server link
When installing standalone data warehouse MS SQL database (deployment scenario First server installation with external data warehouse database) the Distributed Transaction Coordinator (MSDTC) has to be properly configured between both communicating databases - production database and data warehouse database.
MSDTC configuration step-by-step
-
On the server with production database open Component Services (C:\Windows\System32\msdtc.exe)
-
Navigate to Computers > My Computer > Distributed Transaction Coordinator > Local DTC
-
Go to Local DTC > Properties > tab Security and change the configuration as follows:
-
Check options Network DTC Access, Allow Remote Clients, Allow Remote Administration, Allow Inbound, Allow Outbound, Enable XA Transactions
-
Select No Authentication Required option
-
-
Restart the MSDTC service explicitly in Services
-
Restart the MS SQL database server
-
Repeat steps 1. - 5. on the server with data warehouse database
When the setup of the Distributed Transaction Coordinator is changed, a popup windows appears saying that the service will be restarted. Confirm this restart, but make sure to still restart the MSDTC service at the end as said in step 4.
For more information about MSDTC configuration eg. firewall settings and configuration options, please refer to Troubleshooting Problems with MSDTC
MS SQL server configuration
To allow connections from another server, check that inbound connections are allowed on both database servers
Check option: Microsoft SQL Server Management Studio > database name > Properties > Connections > Allow remote connection to this server
