Una actividad común cuando se escriben consultas T-SQL es conectarse a base de datos locales y procesar datos directamente. Pero habrá situaciones en las cuales usted necesitará conectarse a una base de datos remota que está localizada en una instancia diferente en el mismo servidor o en un servidor físico diferente, y procesar sus datos en paralelo con el procesamiento local de datos.
SQL Server nos provee con cuatro métodos útiles para conectarnos a servidores remotos de bases de datos, incluso otros tipos de servidores de bases de datos, y consultar sus datos dentro de su sentencia T-SQL. En este artículo, discutiremos estos cuatro métodos y cómo usarlos para consultar bases de datos de SQL Server remotas.
OPENDATASOURCE
El primer método para consultar una base de datos SQL Server remota es la función T-SQL OPENDATASOURCE como sigue:
|
1 |
OPENDATASOURCE ( provider_name as char, init_string ) |
Donde provider_name es el provisor OLE DB usado para accede la fuente de datos. E init_string es la cadena de conexión del servidor remoto.
Para poder usar la sentencia OPENDATASOURCE, usted necesita asegurarse de que la clave de registro DisallowAdhocAccess esté configurada a 0 para el provisor al que desea conectarse aparte de SQL Server, el cual puede ser encontrado en la siguiente ruta de Clave del Registro:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\<ProviderName>
También usted necesita habilitar la opción de configuración avanzada Ad Hoc Distributed Queries, la cual está deshabilitada por defecto en SQL Server. Si usted trata de correr la siguiente consulta simple que está usando la sentencia T-SQL OPENDATASOURCE usted obtendrá un error:
|
1 2 3 4 |
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=DEV_SQL;Integrated Security=SSPI') .testdb.dbo.AddressBook |
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.
Como puede ver en el mensaje de error, la opción de configuración avanzada Ad Hoc Distributed Queries debería estar habilitada para abrir la conexión a un servidor remoto usando OPENDATASOURCE. Esto puede ser logrado usando la consulta sp_configure como sigue:
|
1 2 3 4 5 6 |
EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE GO |
Una vez que la opción de configuración avanzada Ad Hoc Distributed Queries está habilitada, la consulta previa correrá exitosamente. OPENDATASOURCE puede reemplazar el nombre del servidor en el nombre de cuatro partes en la tabla o vista en una sentencia SELECT, INSERT, UPDATE, o DELETE. También puede ser usado en la sentencia EXECUTE para correr un procedimiento almacenado remoto.
OPENROWSET
La segunda manera de consultar una base de datos hospedada en un SQL Server remoto es la función T-SQL OPENROWSET. Para usar el método ad hoc OPENROWSET usted necesita proveer toda la información de conexión que es requerida para conectarse a un SQL Server remoto y muchos otros recursos. También puede ser usada para una operación masiva a través del provisor integrado BULK para leer datos desde archivos. OPENROWSET es usado desde la cláusula FROM como un nombre de tabla en las sentencias SELECT, INSERT, UPDATE o DELETE. Aunque la consulta puede retornar múltiples conjuntos de resultados, OPENROWSET retorna sólo el primero.
Usar OPENROWSET requiere habilitar la opción de configuración avanzada Ad Hoc Distributed Queries al igual que la función OPENDATASOURCE.
Usted necesita proveer el nombre del provisor, la cadena de conexión y la consulta como sigue:
|
1 |
OPENROWSET('providername', 'datasource','query’) |
Usted puede escribir la anterior consulta OPENDATASOURCE usando la función OPENROWSET como sigue:
|
1 2 3 |
SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=DEV_SQL;Trusted_Connection=yes;', 'SELECT * FROM testdb.dbo.Profile') AS a; |
Servidor Enlazado
Un Servidor Enlazado SQL Server es usado para acceder fuentes de datos remotas OLE DB como una instancia SQL Server localizada fuera de SQL Server u otros sistemas como Oracle, Microsoft Access y Excel, y ejecutar las consultas T-SQL distribuidas contra ellos.
Un Servidor Enlazado SQL Server es diferente de las consultas ad hoc en que las consultas ad hoc abren una conexión temporal con el servidor remoto y lo cierran, donde el servidor enlazado permanente está siempre disponible para su uso. Cuando el usuario ejecuta una consulta distribuida contra una fuente de datos remota usando un servidor enlazado, el motor de SQL Server codifica ese comando y envía las solicitudes a OLE DB. Esta solicitud puede ser una consulta para ejecutar o una tabla para abrirse en ese servidor remoto.
Un Servidor Enlazado puede ser configurado usando SQL Server Management Studio o usando la sentencia T-SQL sp_addlinkedserver.
Para configurar un servidor enlazado usando SQL Server Management Studio, expanda el nodo Server Objects desde la ventana Object Explorer. Haga clic derecho en el nodo Linked Server y elija New Linked Server.
En la pestaña General de la ventana New Linked Server, elija un nombre para su servidor enlazado, luego escoja el tipo de servidor al que usted necesita conectarse usando ese servidor enlazado. Seleccione SQL Server si usted se conectará a una instancia remota de SQL Server, o elija Other Data Source para seleccionar desde los tipos de servidores OLE DB disponibles desde la lista desplegable Provider. SI usted elige SQL Server como Tipo de Servidor, el nombre del Servidor Enlazado debería ser el nombre de la red del SQL Server remoto.
Llene el campo Product Name con el nombre del producto de la fuente de datos OLE DB seleccionada, como SQL Server si se está conectando a un SQL Server remoto. Escriba el nombre de la fuente de datos seleccionada en el campo Data Source, como el nombre de la Instancia SQL Server si usted se está conectando a una instancia SQL Server remota. Llene el campo Provider String con la cadena de conexión específica del provisor OLE DB que identifica una fuente de datos única. El campo Location puede ser llenado con la localización de la base de datos remota como lo interpreta el provisor OLE DB. Escriba el nombre del catálogo a ser usado por el provisor OLE DB en el campo Catalog.
No se requiere que use todos los argumentos descritos juntos, ya que los argumentos necesarios dependen del provisor seleccionado. Por ejemplo, usando el provisor de SQL Server, usted sólo usará dos argumentos.
En la página Security de la ventana New Linked Server, especifique el contexto de seguridad que el servidor enlazado usará para conectar el SQL Server original a la fuente remota de datos. Como el nombre explica, en la parte Local server logins to remote server logins mappings de la ventana Security, usted puede especificar una lista de usuarios que pueden usar el servidor enlazado para conectarse con el servidor remoto mapeando estos usuarios locales con los inicios de sesión del servidor remoto. Estos usuarios pueden ser vía SQL Server Authentication o un inicio de sesión de Windows Authentication.
Para los inicios de sesión que no han sido definidos en la lista de mapeo, usted tiene que elegir el contexto de seguridad para sus conexiones al servidor remoto usando ese servidor enlazado. Elegir Not be made evitará que cualquier usuario no incluido en la lista previa use ese servidor enlazado. Seleccionando Be made without using a security contex, los usuarios no incluidos en la lista de mapeo previa se conectarán al servidor remoto usando el servidor enlazado sin especificar un contexto de seguridad para ellos. Si usted escoge Be made using the login’s current security context, entonces la conexión al servidor remoto será establecida usando el usuario que se está conectando, lo cual es la mejor opción. En la opción Be made using this security context, especifique las credenciales de SQL Server Authentication que serán usadas para establecer la conexión al servidor remoto para los usuarios no definidos en la lista de mapeo.
Hay muchas opciones que usted puede ajustar dependiendo de sus requerimientos desde la página Server Options de la ventana New Linked Server. Por ejemplo, establezca la opción Collation Compatible a verdadero si usted está seguro de que la fuente de datos remota tiene el mismo conjunto de caracteres y orden que el servidor local. Por defecto, SQL Server evalúa comparaciones en columnas de caracteres localmente si usted no usa esta opción. La opción Data Access es usada para habilitar y deshabilitar el acceso de la consulta distribuida para el servidor enlazado. Para habilitar la llamada al procedimiento remoto desde el servidor especificado, establezca RPC a verdadero, y para habilitar la llamada al procedimiento remoto al servidor especificado, establezca RPC Out a verdadero.
Decidir si la colación del servidor remoto o local será usada en la consulta es determinado por la opción Use Remote Collation. Si el valor de esta opción es verdadero, usted puede esècificar el nombre de la colación que será usada por el servidor remoto en la opción Collation Name. Esto es aplicable si la fuente de datos remota no es SQL Server, donde usted puede especificar cualquier nombre de colación soportado por SQL Server en ese campo.
Usted puede sobrescribir el tiempo de espera del inicio de sesión remoto por defecto del servidor para el servidor enlazado cambiando la opción Connection Timeout a cualquier valor más grande que 0. Este valor especifica el tiempo de espera en segundos para conectarse al servidor enlazado. También usted puede especificar el tiempo de espera en segundos para las consultas conectándose al servidor enlazado cambiando la opción Query Timeout a cualquier valor más grande que 0. Esto sobrescribirá el tiempo de espera de la consulta remota del servidor para el servidor enlazado.
Otra opción útil que es usada para comenzar una transacción distribuida cuando se llama a un procedimiento almacenado remoto, en el cual esta transacción será manejada y protegida por MS DTC. Esta opción es llamada Enable Promotion of Distributed Transactions.
Usted puede asegurarse de que el servidor enlazado está trabajando bien haciendo clic derecho en ese servidor enlazado y elegir Test Connection como sigue:
Si la conexión al servidor remoto es abierta exitosamente, usted recibirá el siguiente mensaje. En caso contrario, un mensaje de error será mostrado indicando que hay algo que no permite que se abra la conexión.
Usted puede crear fácilmente el previo servidor enlazado usando la sentencia T-SQL sp_addlinkedserver pasando los argumentos requeridos como sigue:
|
1 2 3 4 |
USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'DEV_SQL', @srvproduct=N'SQL Server' GO |
Una vez que el servidor enlazado es creado exitosamente, usted podrá usarlo especificando el nombre de cuatro partes que incluye: Nombre_Servidor_Enlazado.Nombre_Base_Datos_Remota.Nombre_Esquema.Nombre_Tabla como en el siguiente ejemplo:
|
1 2 |
SELECT * FROM DEV_SQL.testdb.dbo.Profile GO |
OPENQUERY
El último método de SQL Server que es usado para conectarse a una fuente remota de datos es la función OPENQUERY. Es un método ad hoc alternativo de una sola vez para conectarse a un servidor remoto usando el servidor enlazado. Para conexiones más frecuentes al servidor remoto es mejor usar el servidor enlazado en lugar de la función OPENQUERY.
La función OPENQUERY puede ser usada en la cláusula FROM de la sentencia SELECT, INSERT, UPDATE, o DELETE, reemplazando el nombre de la tabla. Toma dos argumentos: el nombre del servidor enlazado y la consulta. Estos parámetros no pueden ser variables, como sigue:
|
1 |
OPENQUERY ( linked_server ,'query' ) |
Abajo está un ejemplo del uso de OPENQUERY:
|
1 |
SELECT * FROM OPENQUERY(DEV_SQL,'SELECT * FROM testdb.dbo.Profile') |
Comparación
Cuando se usa el Servidor Enlazado para consultar un servidor remoto, el optimizador de consultas creará el plan de ejecución después de clasificar y dividir la consulta en consultas locales y remotas, donde las consultas locales serán ejecutadas localmente y las consultas remotas serán enviadas al servidor remoto, luego combinadas para mostrar el resultado final al usuario como un solo conjunto de resultados. Otra desventaja del Servidor Enlazado es que ningún filtro será aplicado en el servidor remoto si la consulta tiene una cláusula WHERE, donde recuperará todos los registros desde la tabla remota y hará el filtrado y la combinación localmente.
En el caso de la función OpenQuery, el Motor de SQL no tratará de clasificar la consulta o verificar qué hará, simplemente enviará la consulta como está al servidor remoto. El análisis gramatical de la consulta SQL, la generación del plan de ejecución y todo el filtrado serán realizados en el servidor remoto.
En general, OpenQuery es más rápida que el servidor enlazado ya que el Motor SQL no romperá la consulta antes de enviarla al servidor remoto, pero es útil sólo para las conexiones remotas menos frecuentes de una sola vez.
Usando las funciones OPENROWSET y OPENDATASOURCE, usted tiene que especificar todos los detalles de la conexión, incluyendo el nombre del usuario y la contraseña cada vez que la usa. Aunque estas funciones no proveen toda la funcionalidad del servidor enlazado como la administración de seguridad, consume menos recursos desde su servidor. Ya que estas funciones abren una conexión de una sola vez al servidor remoto, es mejor usar el servidor enlazado para un acceso frecuente al servidor remoto.
Enlaces Útiles
- Linked Servers
- Create Linked Servers
- How to Create a Linked Server
- sp_addlinkedserver
- OPENDATASOURCE
- OPENROWSET
- OPENQUERY
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Restricciones en SQL Server: SQL NOT NULL, UNIQUE y SQL PRIMARY KEY - December 16, 2019
- Operaciones de copia de seguridad, truncamiento y reducción de registros de transacciones de SQL Server - November 4, 2019
- Qué elegir al asignar valores a las variables de SQL Server: sentencias SET vs SELECT T-SQL - November 4, 2019