Close Menu

    How to configure a Linked Server between Amazon RDS for SQL Server and Teradata database

    Amazon Relational Database Service (Amazon RDS) for SQL Server now enables you to use Teradata databases as external data sources through linked server configuration. This SQL Server feature allows you to execute commands and read data from remote database servers, whether they’re running on AWS or in your on-premises environment. Using ODBC connectivity, you can create linked server configurations between your Amazon RDS for SQL Server instance and a Teradata database without requiring application changes.

    In this post, we demonstrate how to configure a linked server between Amazon RDS for SQL Server and a Teradata database instance. The Teradata instance can be running on Amazon Elastic Compute Cloud (Amazon EC2) or in your on-premises environment. We guide you through the step-by-step process to establish this connection and show you how to verify its functionality.

    Solution overview

    RDS for SQL Server supports linked servers with Teradata ODBC in all AWS Regions for SQL Server Standard and Enterprise Editions on the following versions:

    • SQL Server 2022, all versions
    • SQL Server 2019, all versions
    • SQL Server 2017, all versions

    Linked servers with Teradata ODBC are supported for the following Teradata Database versions:

    • Teradata 17.20, all versions

    The following diagram illustrates the linked server to Teradata architecture.

    Client applications connecting to RDS instances for SQL Server, can query multiple remote data sources using linked servers:

    • Other SQL Server instances on AWS (Amazon RDS or Amazon EC2), using the native SQLOLEDB driver.
    • A Teradata server on Amazon EC2, using native MSDASQL driver in conjunction with Teradata ODBC driver.
    • A SQL Server on-premises or in other cloud providers, using the native SQLOLEDB driver.
    • A Teradata server on-premises or other cloud providers, using native MSDASQL driver in conjunction with Teradata ODBC driver.

    ODBC_TERADATA can be activated on an RDS instance through an option group. For more information, see Working with Option Groups. You can create or use an existing option group for SQL Server based on the edition of your RDS DB instance.

    In the following sections, we walk through the steps of creating an option group, associating the option group with your DB instance, and configuring the linked server.

    Creating an option group

    To create an option group, complete the following steps:

    1. Sign in to the AWS Management Console and go to the Amazon RDS console.
    2. In the navigation pane, choose Option groups. You can create or modify an existing option group. For this post, you create a new option group.
    3. On the Option groups page, choose Create group.
    4. In the Create option group window, do the following:
      1. For Name, enter a name for the option group that’s unique within your AWS account, such as teradata-odbc-se-2019. The name can contain only letters, digits, and hyphens.
      2. For Description, enter a brief description of the option group, such as ODBC_TERADATA option group for SQL Server SE 2019. The description is used for display purposes.
      3. For Engine, choose sqlserver-se.
      4. For Major engine version, choose 00.
      5. Choose Create.
    5. On the Option groups page, search for and choose the option group that you just created, which is teradata-odbc-se-2019 in this example.
    6. Choose Add option.
    7. Under Option details:
      1. Choose ODBC_TERADATA for Option name.
      2. Choose 20.33.00 for Version.
    8. Under Scheduling, choose whether to add the option immediately or at the next maintenance window, and choose Add option.

    Associate the option group with your DB instance

    To finish enabling the linked server for Teradata, associate your ODBC_TERADATA option group with a new or existing DB instance:

    • For a new DB instance, associate it when you launch the instance. For more information, see Creating an Amazon RDS DB instance.
    • For an existing DB instance, associate them by modifying the instance. You don’t need to reboot your RDS for SQL Server DB instance to enable this feature. For more information, see Modifying an Amazon RDS DB instance.

    After you successfully modify the option group on your RDS for SQL Server DB instance, you can connect to SQL Server and create a new linked server to Teradata

    Set up an RDS for SQL Server to a Teradata linked server

    Linked server configuration depends on network connectivity and DNS resolution between RDS for SQL Server and the Teradata server. When you’re setting up a linked server, you must register the connection information and data source information with SQL Server properly. After registration, both the remote and local data source can be referred to with a single logical name. You can use stored procedures and catalog views to manage linked server definitions.

    The following code is an example of using the stored procedure sp_addlinkedserver to create a linked server:

    EXECUTE master.dbo.sp_addlinkedserver 
    @server = N'<LinkedServer_NAME>', 
    @srvproduct=N'', 
    @provider=N'MSDASQL', 
    @provstr=N'"PROVIDER=MSDASQL;DRIVER={Teradata Database ODBC Driver 17.20};
                DBCName=<Server>;UID=<user_name>;PWD=<user_password>;
                UseDataEncryption=<YES/NO>;SSLMODE=<PREFER/ALLOW/DISABLE>;"', 
    @catalog='<database>'
EXECUTE master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'<LinkedServer_NAME>', 
    @locallogin = NULL , 
    @useself = N'False', 
    @rmtuser = N'<user_name>', 
    @rmtpassword = N'<user_password>'

    The following connection information is required:

    <LinkedServer_NAME> – The name of the linked server

    <Server> – Your Teradata DB instance name

    <user_name> – The login for your Teradata database

    <user_password> – The password for your Teradata login to connect

    <YES/NO> – Use encryption. Consult Teradata’s documentation for Enable TLS Connectivity on Teradata Vantage, if you plan to use an encrypted connection.

    Optional: <PREFER/ALLOW/DISABLE> – SSL mode, if encryption is used.

    <database> – Teradata database (catalog) name.

    The following code is an example of the linked server setup between two Amazon RDS DB instances running SQL Server and Teradata:

    EXECUTE master.dbo.sp_addlinkedserver 
    @server = N'LinkedServerToTeradata', 
    @srvproduct=N'', 
    @provider=N'MSDASQL', 
    @provstr=N'"PROVIDER=MSDASQL;DRIVER={Teradata Database ODBC Driver 17.20};
                DBCName=my-teradata-test.cnetsipka.us-west-2.rds.amazonaws.com;
                UID=master;PWD=Test#1234;
                UseDataEncryption=YES;SSLMODE=PREFER;"', 
    @catalog='MyTestTeradataDB'
EXECUTE master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'LinkedServerToTeradata', 
    @locallogin = NULL , 
    @useself = N'False', 
    @rmtuser = N'master', 
    @rmtpassword = N'<user_password>'

    Next, review the linked server using SQL Server Management Studio (SSMS), as shown in the following image.

    You can run the following query using the linked server to verify if the remote data source is accessible. If the COUNT number returns a non-zero number, then the remote data source is accessible.

    SELECT * 
 FROM OPENQUERY(LinkedServerToTeradata, 
               'SELECT COUNT(*) AS Tables FROM DBC.TablesV WHERE TableKind = ''T''');

    You can also use four-part names to refer to an object on a linked server.

    select GlobalID, Description, DateOfSale 
from [LinkedServerToTeradata]..[MyTestTeradataDB].[Sales];

    Cleaning up resources

    To remove the resources deployed in your account from this post, complete the following steps:

    1. To remove a server from the list of known remote and linked servers on the local instance of SQL Server, you can use sp_dropserver: 
      EXEC master.dbo.sp_dropserver
@server=N'LinkedServerToTeradata',
@droplogins='droplogins';
    2. Delete the RDS for SQL Server instance you deployed for this post. For instructions, refer to Deleting a DB instance.
    3. Delete the option group for the RDS SQL Server instance you deployed for this post. For instructions, see Deleting an Option Group.

    Conclusion

    This post demonstrated how to establish and configure a linked heterogeneous server between Amazon RDS for SQL Server and a Teradata database instance. We walked you through the configuration process and showed how to leverage T-SQL statements to access data from your Teradata database through the linked server connection. With this integration, you can now run distributed queries including SELECT and UPDATE commands to interact with external Teradata database instances, supporting use cases such as cross-database reporting and data integration. The linked server capability enables you to access Teradata data using familiar T-SQL syntax, perform distributed queries between both database systems, maintain secure connections with TLS encryption, and execute commands without application modifications. As you implement this solution in your environment, remember to follow security best practices and consider your specific performance requirements.

    If you have any comments or questions, leave them in the comments section.

    About the authors

    Julio Oliveira Leme worked as a Database Engineer at Amazon Web Services. He joined AWS in 2018 and worked on RDS SQL Server team projects to release new features and engine versions for RDS SQL Server customers. Julio enjoys reading and spending time with friends and family.

    Pradipta Kishore Das has vast experience in database management systems and has over 18 years of experience with Microsoft SQL Server. He currently works as a Senior Database Engineer with Amazon Web Services. He works with the Amazon RDS team in building new and exciting features for customers, focusing on commercial database engines. In the past, he was a Technical Lead at Microsoft, where he troubleshot complex SQL Server issues and performed debugging. He has experience working with environments of various scales.

    Source: Read More

    Hostinger

    Related Posts

    Leave A Reply