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.
Sberbank data is stored on different servers: oracle, teradata, mssql. Often (sometimes 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 into 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 here with the permissions). This was the 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 - Connected 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 — name of the data source that was created in “Action 1”.
Linked server section "General"
Fields of the Security / Security section: Remote login - the name of the UZ on the Teradata server, With password - the UZ password on the Teradata server.
Related server section "Security / Security"
So that no one can log in through a connected server (teradata) under my UZ, I will add a login mapping on the local and remote servers. Persist.
Mapping logins on local and remote servers
Open a new request. We write select * from openquery (SERVICENOW, 'select * from dual') and get an error. Access closed. Go ahead.
Error example
Step 3 - SQLEXPRESS and Embedded UZ
Go to the SQL Configuration Manager - properties of SQL Server (MSSQLSERVER). We change the tick to "Use the input built-in ultrasound (Local System)".
SQLEXPRESS and embedded KM
If you run Management Studio 12 as an administrator, it will work. We need no administrator. We continue the setting.
Step 4 - SQLEXPRESS and Network Service
Go to the SQL Configuration Manager - properties of SQL Server (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 - properties my computer - default properties tab.
Fill in the example below:
Component service
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.
Msdainitialize object properties
Next win + R - regedit- search. It is necessary to find an object in the registry, by the application code, and enter its permission / permission.
Msdainitialize object registry
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 object
Customize / Settings - Edit (the button has become active) - we add full service to the NT Service \ MSSQLSERVER .
Full rights for the MSSQLSERVER service
Grant the service the right to in the sections: Launch and Activation Permission, Access Permission, Configuration Permission. Next, you need to restart the service MSQSQLSERVER - Restart / Restart NT Service \ MSSQLSERVER .
Step 7 COM Security / Security COM
win + R - dcomcnfg - computers - my computer - DCOM setup
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.
Request execution
I hope that this manual will help with the settings of the remote access technology. Now we import the data directly to the mssql server (saving hours and not minutes). The query, which was previously executed for about 3 hours (exporting data to a file, importing to mssql), after setting up the technology, works out in 47 seconds (importing the result of the query directly to mssql).
Sberbank data is stored on different servers: oracle, teradata, mssql. Often (sometimes 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 into 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 here with the permissions). This was the 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 - Connected 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 — name of the data source that was created in “Action 1”.
Linked server section "General"
Fields of the Security / Security section: Remote login - the name of the UZ on the Teradata server, With password - the UZ password on the Teradata server.
Related server section "Security / Security"
So that no one can log in through a connected server (teradata) under my UZ, I will add a login mapping on the local and remote servers. Persist.
Mapping logins on local and remote servers
Open a new request. We write select * from openquery (SERVICENOW, 'select * from dual') and get an error. Access closed. Go ahead.
Error example
Step 3 - SQLEXPRESS and Embedded UZ
Go to the SQL Configuration Manager - properties of SQL Server (MSSQLSERVER). We change the tick to "Use the input built-in ultrasound (Local System)".
SQLEXPRESS and embedded KM
If you run Management Studio 12 as an administrator, it will work. We need no administrator. We continue the setting.
Step 4 - SQLEXPRESS and Network Service
Go to the SQL Configuration Manager - properties of SQL Server (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 - properties my computer - default properties tab.
Fill in the example below:
Component service
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.
Msdainitialize object properties
Next win + R - regedit- search. It is necessary to find an object in the registry, by the application code, and enter its permission / permission.
Msdainitialize object registry
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 object
Customize / Settings - Edit (the button has become active) - we add full service to the NT Service \ MSSQLSERVER .
Full rights for the MSSQLSERVER service
Grant the service the right to in the sections: Launch and Activation Permission, Access Permission, Configuration Permission. Next, you need to restart the service MSQSQLSERVER - Restart / Restart NT Service \ MSSQLSERVER .
Step 7 COM Security / Security COM
win + R - dcomcnfg - computers - my computer - DCOM setup
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.
Request execution
I hope that this manual will help with the settings of the remote access technology. Now we import the data directly to the mssql server (saving hours and not minutes). The query, which was previously executed for about 3 hours (exporting data to a file, importing to mssql), after setting up the technology, works out in 47 seconds (importing the result of the query directly to mssql).
Source: https://habr.com/ru/post/427437/
All Articles