by Jerod Johnson | November 29, 2021

Extend SQL Server with Real-Time Cloud Data Connectivity

SQL Server has long been the data hub of choice for IT teams. In fact, a recent survey of 10,000 customers conducted by CData found 75% of organizations rely on SQL Server as their operational database to run their businesses.

According to a recent study conducted by Deloitte, 68 percent of CIOs rank cloud migration as the top driver of future spending. And Gartner has found that the vast majority of enterprises lack the skills to execute comprehensive cloud migration strategies. Worse, cloud migrations typically take a year or longer and often run over budget.

For many organizations charting a path towards cloud migration, SQL Server is seen as the 800lb gorilla standing in the way of cloud data processing – an expensive and frustrating obstacle to realizing the dream of creating a data-driven, cloud-first enterprise.

But could SQL Server actually serve as a key ally in the shift to the cloud and become an integrated data hub for cloud data accessibility? In this article, we explore how you can leverage SQL Server as a consolidated entry point for real-time data access using a simple approach: linked server technologies.

Taking SQL Server Investments to the Cloud with Linked Servers

If your organization is like the many companies around the world relying heavily on SQL Server, you've already invested in reporting capabilities and integrations that work specifically with SQL Server. Most data professionals at your company almost certainly have a working knowledge of and deep experience with SQL Server. And your IT department probably even has an array of automations, scripts, and other tools designed expressly to work with SQL Server.

Creating a linked server instance allows you to leverage your existing SQL Server investment against cloud data - instead of breaking or altering your existing SQL Server data infrastructure.

What is a Linked Server?

A linked server is simply a link to an external (remote) data source. The remote data source could be Oracle, Salesforce, NetSuite, Excel or any other data source that uses a compatible provider. It allows SQL Server to execute SQL scripts against the other data sources as if the data existed inside SQL Server.

The Benefits of a Linked Server

There's no need to reinvent the wheel with a linked server. Leverage and extend your existing infrastructure and solutions, rather than working against or recreating them.

Linked servers work the same as a regular SQL database. Use the same queries, scripts, and automations already created and running in your SQL Server infrastructure to run a range of operations:

• Access, read and execute commands against remote databases outside of SQL Server.
• Efficiently distributed queries, commands, and updates against different enterprise data sources.
• Enjoy real-time, bi-directional data services.
• Empower knowledge workers, business analysts, and data scientists to create data access configurations.
• Maintain complete line of sight over data access.

Extending your existing database with linked server support enables you to use it to run your analysis workflows and automations. For example, you may build flows to connect to your cloud CRM, ERP or e-commerce tools, access data, and use that data in cloud BI & reporting platforms. This means you can integrate SQL Server processes directly to Salesforce, NetSuite, Microsoft Dynamics 365, and others.

Create Linked Servers with CData Connect

While SQL Server's linked server technology supports ODBC connectivity, there are some limitations. For starters, the ODBC Drivers for connecting to other systems have to be installed locally on the same machine where SQL Server is running. This often poses a logistical challenge as IT may have security protocols to limit the software that can be installed alongside SQL Server.

Beyond that, there are also limitations to working with ODBC sources from SQL Server. As an ODBC source, SQL server cannot optimize queries to its fullest extent, causing some operation to incur certain performance penalties.

CData Connect gets around these limitations and provides an optimal experience for linked server integration. It does this by supporting the SQL Server wire protocol itself – looking exactly like another instance of SQL Server for linked server integration. This provides the best possible performance for data connectivity, and doesn't require any additional software to setup or configure.

Using CData Connect, you can quickly create a linked SQL Server that brings your existing SQL Server workflows to scattered enterprise data. Examples include:

• BI & analytics tools like Tableau, Power BI, and Excel
• CRMs and ERP systems like Salesforce, NetSuite, Dynamics 365 and others
• Data warehousing platforms, such as Snowflake, Google BigQuery, Amazon Redshift, and Oracle
• And many more

Streamline your data connectivity and cut down on manual work required for your linked server application integration.

Cloud Linked Server Use Case: Connecting NetSuite Data with CData Connect

NetSuite is an online service used by many companies as an ERP system. It allows companies to manage key business processes in one system. NetSuite's popularity is due in part to its cloud nature, as businesses don't have to install hardware to run it.

Other uses for NetSuite include customer relationship management, tracking financials, and managing inventory. The information housed in the NetSuite database is helpful to data professionals looking to track various functions in an organization, making NetSuite a prime data source for a linked server.

Use the CData SQL Gateway to configure a SQL Server's remoting service, then use CData Connect to set up your linked server. You can then use the SQL Server Management Studio (SSMS) UI to complete your linked server setup. Once you have completed the setup, you can start executing queries against NetSuite data.

Imagine accessing NetSuite information the same way you would a regular SQL Server instance. You can now seamlessly pull NetSuite information into a Power BI report using existing SQL Server automations and scripts.

Integration through linked servers help you create seamless connections to vital data sources, such as NetSuite, throughout your organization.

Start Working with Linked Servers Using CData Connect

CData Connect is a consolidated connectivity platform that lets you easily connect any application, on-prem or in the cloud, with real-time data from anywhere.

If you've never worked with linked servers before, CData Connect gets you up and running _in minutes_. You don't have to be a code guru to create a reliable linked server and gain live data access. Use linked servers to turn your on-prem SQL Server into a cloud data operations hub.

Get a 30-Day Free Trial of CData Connect or Request a Demo