una actividad común al escribir consultas T-SQL es conectarse a bases de datos locales y procesar datos directamente. Pero habrá situaciones en las que necesita conectarse a una base de datos remota que se encuentra en una instancia diferente en el mismo servidor o en un servidor físico diferente, y procesar sus datos en paralelo con el procesamiento de datos local.,
SQL Server nos proporciona cuatro métodos útiles para conectarnos a los servidores remotos de bases de datos, incluso a otros tipos de servidores de bases de datos, y consultar sus datos dentro de su instrucción T-SQL. En este artículo, discutiremos estos cuatro métodos y cómo usarlo para consultar bases de datos remotas de SQL Server.
OPENDATASOURCE
el primer método para consultar una base de datos remota de SQL Server es la función T-SQL de OPENDATASOURCE a continuación:
OPENDATASOURCE ( provider_name como char, init_string)
donde provider_name es el proveedor OLE DB utilizado para acceder al origen de datos., Y el init_string es la cadena de conexión del servidor remoto.
para poder usar la instrucción OPENDATASOURCE, debe asegurarse de que la clave de registro DisallowAdhocAccess esté establecida en 0 para el proveedor al que desea conectarse que no sea SQL Server, que se puede encontrar en la siguiente ruta de las claves de Registro :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\<ProviderName>
También debe habilitar la opción de configuración avanzada de consultas distribuidas ad hoc que está deshabilitada de forma predeterminada en SQL Server., Si intenta ejecutar la siguiente consulta simple que está utilizando la instrucción T-SQL de OPENDATASOURCE, obtendrá el error:
Msg 15281, nivel 16, estado 1, Línea 1
SQL Server bloqueó el acceso a la instrucción ‘OpenRowset/OpenDatasource’ del componente ‘consultas distribuidas Ad Hoc’ porque este componente está desactivado como parte de la configuración de seguridad para este servidor. Un administrador del sistema puede habilitar el uso de ‘consultas distribuidas Ad Hoc’ usando sp_configure., Para obtener más información sobre cómo habilitar ‘consultas distribuidas Ad Hoc’, busque ‘consultas distribuidas Ad Hoc’ en los libros en línea de SQL Server.
como puede ver en el mensaje de error, la opción de configuración avanzada de consultas distribuidas Ad Hoc debe estar habilitada para abrir la conexión a un servidor remoto utilizando OPENDATASOURCE.,
|
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
|
Once the Ad Hoc Distributed Queries advanced configuration option is enabled, the previous query will run successfully., OPENDATASOURCE puede reemplazar el nombre del servidor en el nombre de cuatro partes de la tabla o vista en una instrucción SELECT, INSERT, UPDATE o DELETE. También se puede usar en la instrucción EXECUTE para ejecutar un procedimiento almacenado remoto.
OPENROWSET
la segunda forma de consultar una base de datos alojada en un servidor SQL remoto es la función T-SQL de OPENROWSET. Para utilizar el método ad hoc OPENROWSET, debe proporcionar toda la información de conexión necesaria para conectarse al servidor SQL remoto y a muchos otros recursos., También se puede usar para una operación masiva a través del proveedor masivo incorporado para leer datos de archivos. OPENROWSET se utiliza en la cláusula FROM como nombre de tabla en las instrucciones SELECT, INSERT, UPDATE o DELETE. Aunque la consulta puede devolver varios conjuntos de resultados, OPENROWSET devuelve solo el primero.
el uso de OPENROWSET requiere habilitar la opción de configuración avanzada de consultas distribuidas Ad Hoc igual que la función OPENDATASOURCE.,979d8a2e»>
puede escribir la consulta OPENDATASOURCE anterior utilizando la función OPENROWSET de la siguiente manera:
servidor vinculado
un servidor vinculado de SQL Server se utiliza para acceder a fuentes de datos OLE DB remotas, como una instancia de SQL Server ubicada fuera de SQL Server u otros sistemas como Oracle, Microsoft Access y Excel, y ejecutar las consultas T-SQL distribuidas contra ellos.,
un servidor vinculado a 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 la cierran, donde el servidor vinculado permanente siempre está disponible para su uso. Cuando el usuario ejecuta una consulta distribuida contra una fuente de datos remota utilizando un servidor vinculado, el motor de SQL Server analiza ese comando y envía las solicitudes a OLE DB. Esta solicitud puede ser una consulta a ejecutar o una tabla a abrir en ese servidor remoto.,
un servidor vinculado se puede configurar mediante SQL Server Management Studio o mediante la instrucción sp_addlinkedserver T-SQL.
para configurar un servidor vinculado mediante SQL Server Management Studio, expanda el nodo Objetos del servidor desde la ventana del Explorador de objetos. Haga clic con el botón derecho en el nodo servidor vinculado y elija nuevo servidor vinculado.
en la pestaña General de la ventana nuevo servidor vinculado, elija un nombre para su servidor vinculado y, a continuación, elija el tipo de servidor al que necesita conectarse utilizando ese servidor vinculado., Seleccione SQL Server si logra conectarse a una instancia remota de SQL Server o elija otro origen de datos para seleccionar de los tipos de servidor OLE DB disponibles en la lista desplegable proveedor que no sea SQL Server. Si elige SQL Server como tipo de servidor, el nombre del servidor vinculado debe ser el nombre de red del servidor SQL remoto.
rellene el campo Nombre del producto con el nombre del producto de la fuente de datos OLE DB seleccionada, como SQL Server si se conecta a un SQL Server remoto., Escriba el nombre del origen de datos seleccionado en el campo Origen de datos, como el nombre de la instancia de SQL Server si se conecta a una instancia remota de SQL Server. Rellene el campo cadena del proveedor con la cadena de conexión específica del proveedor de OLE DB que identifica una fuente de datos única. El campo ubicación se puede rellenar con la ubicación remota de la base de datos según la interpretación del proveedor OLE DB. Escriba el nombre del catálogo que utilizará el proveedor OLE DB en el campo catálogo.,
no es necesario utilizar todos los argumentos descritos juntos, ya que los argumentos necesarios dependen del proveedor seleccionado. Por ejemplo, si utiliza SQL Server provider, solo utilizará dos argumentos.
en la página Seguridad de la ventana nuevo servidor vinculado, especifique el contexto de seguridad que utilizará el servidor vinculado para conectar el servidor SQL original al origen de datos remoto., Como se explica en su nombre, en la parte asignaciones de inicios de sesión del servidor Local a inicios de sesión del servidor remoto de la ventana seguridad, puede especificar una lista de usuarios que pueden usar el servidor vinculado para conectarse al servidor remoto asignando estos usuarios locales con inicios de sesión del servidor remoto. Estos usuarios pueden ser a través de la autenticación de SQL Server o un inicio de sesión de autenticación de Windows.
para los inicios de sesión que no se han definido en la lista de asignación, debe elegir el contexto de seguridad para sus conexiones al servidor remoto que utiliza ese servidor vinculado., Elegir no ser realizado evitará que cualquier usuario no incluido en la lista anterior use ese servidor vinculado. Si selecciona Realizar sin usar un contexto de seguridad, los usuarios no incluidos en la lista de asignación anterior se conectarán al servidor remoto utilizando ese servidor vinculado sin especificar un contexto de seguridad para ellos. Si elige realizar usando el contexto de seguridad actual del inicio de sesión, entonces la conexión al servidor remoto se establecerá utilizando el usuario que se conecta, que es la mejor opción., En la opción Realizar con este contexto de seguridad, especifique las credenciales de autenticación de SQL Server que se utilizarán para establecer la conexión con el servidor remoto para los usuarios no definidos en la lista de asignación.
hay muchas opciones que puede ajustar según sus requisitos desde la página de opciones del servidor de la ventana nuevo servidor vinculado. Por ejemplo, establezca la opción compatible con intercalación en true si está seguro de que la fuente de datos remota tiene el mismo conjunto de caracteres y orden de clasificación que el servidor local., De forma predeterminada, SQL Server evalúa las comparaciones en columnas de caracteres localmente si no utiliza esta opción. La opción Acceso a datos se utiliza para habilitar y deshabilitar el acceso a consultas distribuidas para el servidor vinculado. Para habilitar la llamada a procedimiento remoto desde el servidor especificado, establezca el RPC en true y, para habilitar la llamada a procedimiento remoto al servidor especificado, establezca el RPC en true.
decidir si se utilizará la intercalación del servidor local o remoto en la consulta se determina mediante la opción Usar intercalación remota., Si el valor de esta opción es true, puede especificar el nombre de intercalación que utilizará el servidor remoto en la opción nombre de intercalación. Esto se aplica si el origen de datos remoto no es SQL Server, donde puede especificar cualquier nombre de intercalación compatible con SQL Server en ese campo.
Puede anular el tiempo de espera de inicio de sesión remoto predeterminado del servidor para ese servidor vinculado cambiando la opción Tiempo de espera de conexión a cualquier valor mayor que 0.Este valor especifica el tiempo de espera en segundos para conectarse al servidor vinculado., También puede especificar el tiempo de espera en segundos para las consultas que se conectan al servidor vinculado cambiando la opción Tiempo de espera de consulta a cualquier valor mayor que 0. Esto anulará el tiempo de espera de consulta remota del servidor para el servidor vinculado.
otra opción útil que se utiliza para iniciar una transacción distribuida al llamar a un procedimiento almacenado remoto, en el que esta transacción será administrada y protegida por el MS DTC. Esta opción se llama habilitar promoción de transacciones distribuidas.,él a continuación el mensaje, de lo contrario un mensaje de error que muestra que hay algo que está impidiendo la conexión de apertura:
Usted puede crear fácilmente el anterior servidor vinculado mediante la sp_addlinkedserver instrucción T-SQL pasando los argumentos requeridos de la siguiente manera:
|
1
2
3
4
|
USO
IR
EXEC master.,dbo.sp_addlinkedserver @server = n’DEV_SQL’, @srvproduct=N’SQL Server ‘
GO
|
Una vez que el servidor vinculado se haya creado correctamente, podrá usarlo especificando el nombre de cuatro partes que incluye: Linked_Server_Name.Remote_Database_Name.Schema_Name.Table_Name como en el ejemplo de abajo:
|
1
2
|
SELECT * FROM DEV_SQL.,testdb.dbo.Profile
GO
|
OPENQUERY
El último método de SQL Server que se utiliza para conectarse a una fuente de datos remota es la función OPENQUERY. Es un método ad hoc alternativo de una sola vez para conectarse a un servidor remoto utilizando el servidor vinculado. Para conexiones más frecuentes con el servidor remoto, es mejor usar el servidor vinculado en lugar de la función OPENQUERY.
la función OPENQUERY se puede utilizar en la cláusula FROM de la instrucción SELECT, INSERT, UPDATE o DELETE que reemplaza el nombre de la tabla., Toma dos argumentos; el nombre del servidor vinculado y la consulta. Estos parámetros no puede ser variable, de la siguiente manera:
OPENQUERY ( linked_server ,’query’ )
a Continuación es un ejemplo sencillo de la OPENQUERY uso:
|
1
|
SELECT * FROM OPENQUERY(DEV_SQL,’SELECT * FROM testdb.dbo.,Profile’)
|
Comparison
Cuando se utiliza el servidor vinculado 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 se ejecutarán localmente y las consultas remotas se enviarán al servidor remoto, el usuario como conjunto de resultados único., Otra desventaja del servidor vinculado es que, no se aplicará ningún filtrado en el servidor remoto si la consulta tiene la cláusula WHERE, donde recuperará todos los registros de la tabla remota y realizará el filtrado y la Unión localmente.
en el caso de la función OpenQuery, el motor SQL no intentará clasificar la consulta ni comprobar lo que hará, simplemente enviará la consulta tal cual al servidor remoto. El análisis de la consulta SQL, la generación del plan de ejecución y todo el filtrado se realizarán en el servidor remoto.,
En general, OpenQuery es más rápido que el servidor vinculado, ya que el motor SQL no interrumpirá la consulta antes de enviarla al servidor remoto, pero es útil solo para conexiones remotas de una sola vez menos frecuentes.
Usando las funciones OPENROWSET y OPENDATASOURCE, debe especificar todos los detalles de conexión, incluyendo el nombre de usuario y la contraseña cada vez que lo use. Aunque estas funciones no proporcionan toda la funcionalidad del servidor vinculado, como la administración de seguridad, consume menos recursos de su servidor., Como estas funciones abren una conexión única al servidor remoto, es mejor usar el servidor vinculado para el acceso frecuente al servidor remoto.
- Autor
- mensajes Recientes
Además, está contribuyendo con sus consejos SQL en muchos blogs.,
Ver todos los mensajes por Ahmad Yasin
- como monitor de Azure Datos de Fábrica – 15 de enero de 2021
- Usar Fuente de Control en Azure Datos de Fábrica – 12 de enero, 2021
- el Uso de Plantillas en Azure Datos de Fábrica – 8 de enero de 2021