Configuring linked servers: ms sql server and teradata
Hello! I decided to write this article for one simple reason - I did not find detailed instructions for setting up a remote linked server between the teradata and ms sql server servers.
In Sberbank data is stored on different servers: oracle, teradata, mssql. Often (it happens several times a day), my colleagues and I have to perform actions that are called “effective” very difficult: write a request, export data to a file, import it to the mssql server, work. The time spent on one request is more than 3 hours (about 90 million lines).
"Effective work
The reason for such complex actions is the lack of rights to create tables on teradata servers. That is why we “fill in” the data on the ms sql server (everything is fine with the permissions here). This was an introduction, and now turn to the description of the sequence of actions.
Note : the instruction was prepared using SQL Server Management Studio 12 and Teradata Client ver 15 (if the Teradata version is lower, for example 14, then additional software should be downloaded from the official site).
Step 1 - Setting up an ODBC connection
Go to: Control Panel \ System and Security \ Administration \ ODBC Data Sources
In the “User DSN” section, click “Add”. Next, select the driver "Teradata" and fill in the fields.
Important: remember the name of the new source. We still need it.
Step 2 - Linked Server
Open Management Studio - Server Objects - Linked Servers - create a linked server.
Fields of the “General / General” section: Linked Server — arbitrary name; Provider — Microsoft OLE DB Provider for ODBC Drivers; Product Name — ServiceNow; Data Source — the name of the data source that was created in “Action 1”.
Linked server section “General / General”
Fields in the section “Security / Security”: Remote login - name of the KM on the Teradata server, With password - password of the KM on the Teradata server.
Related server section "Security / Security"
So that no one can log in through the connected server (teradata) under my UZ, I will add a login mapping on the local and remote servers. Persist.
Matching logins to local and remote servers.
Open a new query. We write select * from openquery (SERVICENOW, 'select * from dual') and get an error. Access closed. Go ahead.
Error Example
Action 3 - SQLEXPRESS and Embedded KS
We go into SQL Configuration Manager - SQL Server properties (MSSQLSERVER). We change the tick to "Use the built-in ultrasound (Local System)".
SQLEXPRESS and embedded KM
If you run Management Studio 12 as administrator, it will work. We need no administrator. We continue the setting.
Step 4- SQLEXPRESS and network service
Go to SQL Configuration Manager - SQL Server properties (MSSQLSERVER). We change the tick to "Use the built-in UZ (Network Service) for input", but under the NT Service \ MSSQLSERVER .
SQLEXPRESS and network service
It should be like this.
SQL Server Configuration Manager
Step 5 - Component Service
win + R - dcomcnfg - computers - my computer properties - default properties tab.
Fill in the example below:
Component Services
Step 6 - Configure DCOM
win + R - dcomcnfg - computers - my computer - DCOM setting - msdainitialize properties. Copy Application ID / Application Code. Required for finding an object in the windows registry.
Properties of the object msdainitialize
Next win + R - regedit- search. It is necessary to find an object in the registry, by the application code, and enter its permission / permission.
The registry object msdainitialize
Full control for the local PC administrator.
Properties of the msdainitialize object in the WINDOWS registry
Confirm and save. This was done so that the Security section of the object (application code) becomes active.
The security section of the msdainitialize
Customize object / Settings - Edit (the button has become active) - we add full rights to the NT Service \ MSSQLSERVER service .
Full rights for the MSSQLSERVER service.
Grant the rights to the service in the following sections: Launch and Activation Permission, Access Permission, Configuration Permission. Next, you need to restart the MSQSQLSERVER service - Restart / Restart NT Service \ MSSQLSERVER .
Step 7 COM Security / Security COM
win + R - dcomcnfg - computers - my computer - DCOM setting
Change the default value by adding the NT Service \ MSSQLSERVER service . Assign it the maximum rights for the sections: Launch and Activation Permission, Access Permission.
Access Permission
Next, you need to restart the service MSQSQLSERVER - Restart / Restart NT Service \ MSSQLSERVER. Run select * from openquery (SERVICENOW, 'select * from dual'). Works.
Execution of the request
I hope that this instruction will help with the settings of the remote access technology. Now we import the data immediately to the mssql server (not saving minutes, but hours). The query, which was previously executed for about 3 hours (exporting data to a file, importing on mssql), after setting up the technology, works out in 47 seconds (importing the result of the query directly into mssql).
In Sberbank data is stored on different servers: oracle, teradata, mssql. Often (it happens several times a day), my colleagues and I have to perform actions that are called “effective” very difficult: write a request, export data to a file, import it to the mssql server, work. The time spent on one request is more than 3 hours (about 90 million lines).
"Effective work
The reason for such complex actions is the lack of rights to create tables on teradata servers. That is why we “fill in” the data on the ms sql server (everything is fine with the permissions here). This was an introduction, and now turn to the description of the sequence of actions.
Note : the instruction was prepared using SQL Server Management Studio 12 and Teradata Client ver 15 (if the Teradata version is lower, for example 14, then additional software should be downloaded from the official site).
Step 1 - Setting up an ODBC connection
Go to: Control Panel \ System and Security \ Administration \ ODBC Data Sources
In the “User DSN” section, click “Add”. Next, select the driver "Teradata" and fill in the fields.
Important: remember the name of the new source. We still need it.
Step 2 - Linked Server
Open Management Studio - Server Objects - Linked Servers - create a linked server.
Fields of the “General / General” section: Linked Server — arbitrary name; Provider — Microsoft OLE DB Provider for ODBC Drivers; Product Name — ServiceNow; Data Source — the name of the data source that was created in “Action 1”.
Linked server section “General / General”
Fields in the section “Security / Security”: Remote login - name of the KM on the Teradata server, With password - password of the KM on the Teradata server.
Related server section "Security / Security"
So that no one can log in through the connected server (teradata) under my UZ, I will add a login mapping on the local and remote servers. Persist.
Matching logins to local and remote servers.
Open a new query. We write select * from openquery (SERVICENOW, 'select * from dual') and get an error. Access closed. Go ahead.
Error Example
Action 3 - SQLEXPRESS and Embedded KS
We go into SQL Configuration Manager - SQL Server properties (MSSQLSERVER). We change the tick to "Use the built-in ultrasound (Local System)".
SQLEXPRESS and embedded KM
If you run Management Studio 12 as administrator, it will work. We need no administrator. We continue the setting.
Step 4- SQLEXPRESS and network service
Go to SQL Configuration Manager - SQL Server properties (MSSQLSERVER). We change the tick to "Use the built-in UZ (Network Service) for input", but under the NT Service \ MSSQLSERVER .
SQLEXPRESS and network service
It should be like this.
SQL Server Configuration Manager
Step 5 - Component Service
win + R - dcomcnfg - computers - my computer properties - default properties tab.
Fill in the example below:
Component Services
Step 6 - Configure DCOM
win + R - dcomcnfg - computers - my computer - DCOM setting - msdainitialize properties. Copy Application ID / Application Code. Required for finding an object in the windows registry.
Properties of the object msdainitialize
Next win + R - regedit- search. It is necessary to find an object in the registry, by the application code, and enter its permission / permission.
The registry object msdainitialize
Full control for the local PC administrator.
Properties of the msdainitialize object in the WINDOWS registry
Confirm and save. This was done so that the Security section of the object (application code) becomes active.
The security section of the msdainitialize
Customize object / Settings - Edit (the button has become active) - we add full rights to the NT Service \ MSSQLSERVER service .
Full rights for the MSSQLSERVER service.
Grant the rights to the service in the following sections: Launch and Activation Permission, Access Permission, Configuration Permission. Next, you need to restart the MSQSQLSERVER service - Restart / Restart NT Service \ MSSQLSERVER .
Step 7 COM Security / Security COM
win + R - dcomcnfg - computers - my computer - DCOM setting
Change the default value by adding the NT Service \ MSSQLSERVER service . Assign it the maximum rights for the sections: Launch and Activation Permission, Access Permission.
Access Permission
Next, you need to restart the service MSQSQLSERVER - Restart / Restart NT Service \ MSSQLSERVER. Run select * from openquery (SERVICENOW, 'select * from dual'). Works.
Execution of the request
I hope that this instruction will help with the settings of the remote access technology. Now we import the data immediately to the mssql server (not saving minutes, but hours). The query, which was previously executed for about 3 hours (exporting data to a file, importing on mssql), after setting up the technology, works out in 47 seconds (importing the result of the query directly into mssql).