By: Eric Blinn | Updated: 2019-06-28 / Comments (4)/Related: More > Linked Servers
Problem
veo que hay una opción en SQL Server Management Studio (SSMS) para los servidores vinculados y quiero obtener más información sobre lo que hacen y cómo funcionan.
Solution
los servidores vinculados son un método por el cual un servidor SQL puede hablar con otra base de datos que cumple con ODBC, como otra instancia de SQL Server o una base de datos Oracle, con una consulta de directT-SQL.,
Hay varias configuraciones importantes que debe conocer al configurar un servidor vinculado.Este consejo cubrirá la creación de un servidor vinculado a otra instancia de SQL Server utilizando la interfaz gráfica de usuario SSMS junto con las opciones de seguridad adecuadas. También mostrará cómo usar el servidor vinculado en una instrucción T-SQL. Hay una gran biblioteca de temas más avanzados disponibles una vez que se han dominado los conceptos básicos.,
crear un servidor vinculado a SQL Server
en SQL Server Management Studio, en el panel Explorador de objetos, expanda la sección «ServerObjects», haga clic con el botón derecho en «servidores vinculados» y elija «servidor vinculado New» en el menú.
nuevo servidor vinculado – Página general
que mostrará una pantalla que se parece a la siguiente. Elija servertype como SQL Server y escriba el nombre de la otra instancia de SQL Server. En este ejemplo, la conexión se realizará a una instancia nombrada de SQL Server en la misma máquina., Esto ciertamente está permitido, pero más comúnmente la conexión se realiza a otra máquina por completo. El nombre del servidor vinculado se especifica como theServerName\InstanceName. En el siguiente ejemplo me estoy conectando al localserver y el nombre de la instancia es «SECURITY_TEST».
nueva página de seguridad del servidor vinculado
a continuación, pase de la página General a la página de seguridad utilizando el menú de la izquierda.Esta página permite al administrador indicar a este servidor SQL cómo autenticarse en la otra instancia., La mitad superior de la pantalla permite que los inicios de sesión individuales den acceso para usar el servidor vinculado.
hay 2 métodos para autenticar usuarios. La primera es seleccionar un inicio de sesión para usar cuando se utiliza el servidor vinculado. En la captura de pantalla siguiente, el usuario de Eric iniciará sesión automáticamente en el servidor secundario como el servidor vinculado al inicio de sesión en la otra instancia. Esta credencial se almacena dentro del motor de SQL Server y está encriptada. En la segunda fila, se comprueba el login LinkedServerTest (un Login SQL) El Impersonatebox., Esto intentará usar el mismo nombre de usuario y contraseña en el LinkedServer como lo hizo en el servidor original. La suplantación de un inicio de sesión de Windows también puede hacerse, pero requiere una delegación adecuada.
Ahora se centran en la mitad inferior de la pantalla. Hay 4 opciones disponibles. La opción que se selecciona aquí se aplicará a cualquier usuario que no aparezca en la sección superior.,
| Option | Description |
|---|---|
| Not be made | esto dará un error a cualquier usuario que intente utilizar el servidor enlazado que no está en la lista anterior. Para seleccionar esta opción, debe haber al menos 1 inicio de sesión en la mitad superior de la pantalla. Este es el error que todos los demás usuarios obtendrán. Msg 7416, nivel 16,estado 1, Línea 1 Se deniega el acceso al servidor remoto porque no existe login-mappingexists., |
| se realizará sin usar un contexto de seguridad | esto enviará una conexión anónima al servidor vinculado y probablemente no proporcionará ningún acceso al servidor secundario. |
| se debe hacer usando el contexto de seguridad actual del inicio de sesión | esto es lo mismo que colocar a cada usuario en la sección superior con la casilla de verificación suplantar marcada., |
| se realizará utilizando este contexto de seguridad: | esta opción exigirá que se ingrese un nombre de usuario y una contraseña y funcionará de la misma manera que si cada usuario fuera agregado a la sección superior y le diera exactamente el mismo nombre de usuario y contraseña. Es el método menos seguro para configurar un servidor vinculado, ya que cualquier persona con acceso al primer servidor SQL podrá usar el servidor vinculado utilizando las credenciales guardadas., |
nuevo servidor vinculado: página de opciones del servidor
También hay configuraciones en la página de opciones, pero para consultas simples, estas configuraciones se pueden dejar como valores predeterminados, como se muestra a continuación.
no es necesario cambiar la configuración en la mayoría de los casos de uso y esta tabla explicará lo que hacen las configuraciones.
| Option | Description |
|---|---|
| Collation Compatible | Default is false., Esto se puede establecer en true si el servidor de destino utiliza exactamente la misma intercalación que el servidor principal. El cambio puede mejorar el rendimiento de algunas consultas al permitir la comparación remota de columnas en columnas de tipo de texto. |
| Acceso a Datos | valor Predeterminado es True. Cuando false, todas las ejecuciones de consultas remotas individuales fallarán. Esta configuración es independiente de RPC Out. En un scenariowhere solo llamadas a procedimientos remotos necesitan ser ejecutadas esta configuración puede ser cambiada a false mientras RPC out sigue siendo true., |
| RPC / RPC Out | esto significa llamada a procedimientos remotos y le permite ejecutar storedprocedures en el servidor vinculado. RPC habilita llamadas de Procedimiento remotas desde el servidor especificado y RPC out habilita llamadas de Procedimiento remotas al servidor especificado. |
| Usar intercalación remota / nombre de intercalación | el valor predeterminado es False. Cuando false, La intercalación del servidor local se utilizará en todas las consultas remotas. Cuando sea true, la intercalación del servidor remoto se utilizará en las consultas., Si true y el servidor remoto no es un SQLServer, entonces el servidor local no puede buscar automáticamente el valor de intercalación adecuado. En este caso, use la opción nombre de intercalación para escribir la intercalación del servidor de Remote. |
| tiempo de espera de Conexión | valor Predeterminado es 0. Cuando no es cero, Este es el número de segundos que debe transcurrir antes de que se agote el tiempo de espera al conectarse a un servidor vinculado. Cuando 0 entonces thevalue se lee realmente de un valor predeterminado del servidor que se puede encontrar ejecutando este comando.
exec sp_configure 'remote login timeout (s)' |
| tiempo de espera de Consulta | valor Predeterminado es 0., Cuando no es cero, Este es el número de segundos que debe transcurrir antes de que se agote el tiempo de espera de una consulta que se ejecuta contra un servidor vinculado. Cuando 0, el valor se lee realmente desde un servidor predeterminado que se puede encontrar ejecutando este comando.
exec sp_configure 'remote query timeout (s)' |
| distribuidor / Editor / suscriptor | estas configuraciones solo se utilizan cuando el servidor vinculado se utiliza para respaldar la replicación. |
| validación de esquema perezoso | el valor predeterminado es False. Cuando false El esquema de una consulta remota se comprueba antes de cada ejecución de la consulta., Cualquier cambio en el esquema remoto se detecta de antemano y se puede volver a crear un plan de consulta adecuado para tener en cuenta el cambio. Cuando true el esquema no se valida hasta el momento de la ejecución. Si el cambio en el esquema remoto hace que el plan de consulta sea inválido, causará un error de tiempo de ejecución. |
| activar promoción de transacciones distribuidas | el valor predeterminado es True. Cuando se habilita cualquier consulta remota que cambie los datos invocará el DTC (Coordinador de transacciones distribuidas)., Este proceso protege tales consultas al permitir que ambos servidores tengan una transacción compartida que complete el 100% o el 0%, al igual que una transacción regular con una consulta local. Cuando está deshabilitado, el DTC no se invoca y una remotequery fallida puede fallar y no revertirse por completo. |
consulta de datos a través de un servidor vinculado a SQL Server
para leer datos de un servidor vinculado, se debe hacer referencia a cualquier tabla o Vista utilizando un identificador de 4 partes que consiste en el nombre del servidor vinculado, el nombre de la base de datos, el nombre del esquema y el nombre del objeto, en ese orden.,
Por ejemplo:
SELECT * FROM .master.sys.databasesSELECT * FROM .WideWorldImporters.Sales.Orders
- en Primer lugar el nombre del Servidor Vinculado que es para ver ejemplo. En este ejemplo debe estar entre corchetes debido a su formato. No todos los nombres de LinkedServer deben estar entre corchetes.
- El siguiente es el nombre de la base de datos, master y WideWorldImporters respectivamente.
- tercero es el nombre del esquema, sys y Sales respectivamente.
- Por último, se muestra el nombre del objeto. En estos ejemplos los objetos son bases de datos y órdenes.
ese ejemplo es bastante feo debido a la naturaleza del nombre del servidor vinculado., Afortunadamente, este gran consejo muestra cómo hacer que los nombres sean más fáciles de leer en escenarios como este.
Una vez listadas en una cláusula FROM, estas tablas y vistas pueden ser tratadas de la misma manera que cualquier otra tabla local. Se pueden consultar, aliased, joined, y si se establecen los permisos adecuados, se pueden modificar mediante insert, update y deletestatements.
llamar a procedimientos almacenados a través de un servidor vinculado
para los fines de esta demostración, este pequeño procedimiento se creó en la base de datos maestra en el servidor vinculado de destino.,
USE masterGOCREATE PROCEDURE dbo.SQLTips1 AS SELECT 'I was able to be called';
el procedimiento se ejecuta en la instancia principal utilizando este comando que sigue el mismo esquema de identificador de 4 partes que las instrucciones de la sección anterior.
EXEC .master.dbo.SQLTips1
Sin embargo, en lugar del resultado esperado se genera un error.
RPC significa llamada de procedimiento remoto. De forma predeterminada, los servidores vinculados son notables para realizar llamadas a procedimientos almacenados. Esto se soluciona fácilmente habilitando una configuración única en el servidor vinculado., Busque las propiedades del servidor vinculado, elija Opciones del servidor y la 4thoption en la lista será RPC Out. Simplemente cambie ese valor a True y clickOK.
la configuración etiquetada RPC no es el valor que necesita ser cambiado. Esto puede parecer confuso ya que el mensaje de error parece nombrarlo específicamente, pero en realidad es una configuración heredada que no habilita llamadas RPC.
volver a ejecutar el procedimiento ahora produce un resultado mucho mejor.,
las limitaciones del servidor enlazado de SQL Server
los servidores enlazados pueden ser muy útiles, pero hay algunas limitaciones que debemos tener en cuenta antes de implementarlas. Los servidores vinculados no se pueden usar para llamar a funciones tablevaluadas o escalares. Además, el comando truncate no puede ser llamado usando un servidor vinculado.
casos de uso de SQL Server Linked Server
el rendimiento de los servidores vinculados no siempre es excelente, especialmente con conjuntos de datos grandes y muchas uniones entre tablas locales y vinculadas. Pero pueden ser muy útiles para consultar conjuntos de datos más pequeños.,
a veces se puede cumplir un requisito ETL para un conjunto de datos más pequeño utilizando un LinkedServer en lugar de una herramienta ETL como SSIS. Cuando las filas involucradas se numeran en los mils o 10s menores de miles, un ETL basado en servidor vinculado podría ser capaz de completar el tiempo inless del que se necesita para iniciar el motor SSIS.
próximos pasos
- Hay toda una familia de consejos avanzados de servidor vinculado en MSSQLTips.,com
- Hacer de Servidor Vinculado nombres más fáciles de leer
Última actualización: 2019-06-28
Sobre el autor
Eric Blinn es el Sr. Datos de Arquitecto para Squire Patton Boggs. También es autor de SQL y líder de grupo local de PASS.ver todos mis consejos - Más consejos de DBA de SQL Server…