Lesson Learned #2 Linked Server from SQL 2008 R2 to Azure SQL Managed Instance


You should know by now that the SQL 2008 and 2008 R2 is at end of support on 2019-07-09 and mainstream support also ended a long time ago

https://www.microsoft.com/en-us/sql-server/sql-server-2008

https://support.microsoft.com/en-us/lifecycle/search?alpha=SQL%202008

The best path to do this migration would be to use DMA (Data Migration Assistant) and DMS (Azure Database Migration Service), however in some scenario or for testing purpose you may want to create a linked server from SQL 2008 to Azure SQL Managed Instance

There are two ways of achieving this depending on the Windows Server version you have:

  • Windows Server 2008 => ODBC 13 or newer
  • Windows Server 2012 or Later => OLE DB

ODBC 13 or newer

I made a test using Azure VM (SQL Server 2008R2SP3 on Windows Server 2008R2)

Just open ODBC

And create a System DNS


In my scenario I already had the ODBC Driver 13 installed. If you do not have it installed, use the ODBC 13 or later to achieve this. I’ve tested also with ODBC 17.3 and got same results

The ODBC have the min supported Operating System list below:

– Windows 10,
– Windows 7,
– Windows 8,
– Windows Server 2008 R2,
– Windows Server 2012 R2

We do NOT recommend using SNAC 11 (SQLNCLI11 provider supports SQL versions from 2005 through 2012), so Managed Instance which is based on latest SQL bits is not compatible with this driver.

More details on driver compatibility here: https://techcommunity.microsoft.com/t5/SQL-Server/SNAC-lifecycle-explained/ba-p/385381

Now just create the linked server, give a name and set the server name

Select the authentication method. In my scenario I’ve used SQL Authentication

Test connection with success

Creating the linked server

Now just go to SSMS and create the linked server using UX ot TSQL

or using script

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SQLMI', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'SQLMI'

GO
EXEC master.dbo.sp_serveroption @server=N'SQLMI', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLMI', @optname=N'rpc out', @optvalue=N'true'
GO


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLMI', @locallogin = NULL , @useself = N'False', @rmtuser = N'xxxxxxxxxxx', @rmtpassword = N'P@ssw0rd'
GO

And just use the linked server using 4 parts name

SELECT [string] FROM [SQLMI].[sefonsec].[dbo].[test123]

Know issue

There is a know issue when using money / small money datatype

SELECT [string],[cash] FROM [SQLMI].[sefonsec].[dbo].[test1234]

You will get an error like the one below

Msg 7356, Level 16, State 1, Line 2
The OLE DB provider “MSDASQL” for linked server “SQLMI” supplied inconsistent metadata for a column. The column “Cash” (compile-time ordinal 2) of object “”sefonsec”.”dbo”.”test1234″” was reported to have a “DBTYPE” of 131 at compile time and 6 at run time.

A workaround to this is to use the OPENQUERY sintax

SELECT * FROM OPENQUERY([SQLMI],  'SELECT [string],[cash] FROM [sefonsec].[dbo].[test1234]')

OLE DB

The OLE DB have some different generations, find more information below:

https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-2017#different-generations-of-ole-db-drivers

1 – Microsoft OLE DB Provider for SQL Server (SQLOLEDB)
The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) still ships as part of Windows Data Access Components. It is not maintained anymore and it is not recommended to use this driver for new development.

2 – SQL Server Native Client (SNAC)
Starting in SQL Server 2005 (9.x), the SQL Server Native Client (SNAC) includes an OLE DB provider interface (SQLNCLI) and is the OLE DB provider that shipped with SQL Server 2005 (9.x) through SQL Server 2012 (11.x).

It was announced as deprecated in 2011 and it is not recommended to use this driver for new development. For more information about the SNAC lifecycle and available downloads, refer to SNAC lifecycle explained.

3 – Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)
OLE DB was undeprecated and released in 2018.

The new OLE DB provider is called the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL). The new provider will be updated with the most recent server features going forward.

Note

To use the new Microsoft OLE DB Driver for SQL Server in existing applications, you should plan to convert your connection strings from SQLOLEDB or SQLNCLI, to MSOLEDBSQL.

Looking here we can see the oficial compatible drivers and versions with SQL MI

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-connect-app#required-versions-of-drivers-and-tools

The following minimal versions of the tools and drivers are recommended if you want to connect to Managed Instance:

Driver/tool Version
.NET Framework 4.6.1 (or .NET Core)
ODBC driver v17
PHP driver 5.2.0
JDBC driver 6.4.0
Node.js driver 2.1.1
OLEDB driver 18.0.2.0
SSMS 17.8.1 or higher

I’ve tried to install last OLEDB driver

https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server

But it required a newer windows version. Windows 2008 is not supported.

https://docs.microsoft.com/en-us/sql/connect/oledb/applications/support-policies-for-oledb-driver-for-sql-server

The following table lists which operating systems support OLE DB Driver for SQL Server.

Windows 8.1 + April 2014 update + KB2999226
Windows 10
Windows Server 2012 + KB2999226
Windows Server 2012 R2 + April 2014 update + KB2999226
Windows Server 2016

I’ve prepared a new machine with Win Server 2016 + SQL 2008 R2 SP3 + Last OLE DB

You can check if everything is ok going to Linked Server > Providers

I’ve created the linked server with sample script

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SQLMI', @srvproduct=N'SQL', @provider=N'MSOLEDBSQL', @datasrc=N'miserver.database.windows.net'

GO
EXEC master.dbo.sp_serveroption @server=N'SQLMI', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLMI', @optname=N'rpc out', @optvalue=N'true'
GO


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLMI', @locallogin = NULL , @useself = N'False', @rmtuser = N'xxxxxxxxxxx', @rmtpassword = N'P@ssw0rd'
GO

Know issue

In this scenario any 4 parts name query is failing

SELECT [string] FROM [SQLMI].[sefonsec].[dbo].[test123]

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider “MSOLEDBSQL” for linked server “SQLMI” supplied inconsistent metadata for a column. The column “string” (compile-time ordinal 1) of object “”sefonsec”.”dbo”.”test123″” was reported to have a “Incomplete schema-error logic.” of 0 at compile time and 0 at run time.

The workaround is same as ODBC, using OPENQUERY

SELECT * FROM OPENQUERY([SQLMI],  'SELECT [string],[cash] FROM [sefonsec].[dbo].[test1234]')

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s