Introducción

Cuando usted tiene miles de SQL Servers, es muy difícil administrarlos todos. Este artículo mostrará algunos consejos para ayudarle en este tipo de tareas.

En este nuevo artículo mostraremos cómo correr scripts T-SQL contra múltiples servidores usando SQL Server Management Studio (SSMS). Para hacer esto, usaremos Central Management Server. La idea principal de esta característica es administrar múltiples servidores en una manera centralizada usando consultas o políticas. Esta característica está disponible en SQL Server 2008 o versiones posteriores y no puede ser aplicada en versiones más antiguas.

Requerimientos

• SQL Server 2008 o posterior (en este ejemplo estamos usando SQL Server 2014)
• Estamos usando 2 instancias SQL locales con la base de datos AdventureWorks instalada en cada instancia.

Iniciando

1. Abra SSMS.

2. En el menú, vaya a View;Registered Servers

   
   

   Figura 1. Registered Servers

3. Usted puede crear un Nuevos Grupos de Servidores o usar la carpeta actual Local Server Groups. En este ejemplo, vamos a usar el grupo de carpetas existente y añadir New Server Registration a este grupo.

   
   

   Figura 2. New Server Registration

   Usted puede añadir la información de autenticación de los servidores para conectarse a ellos. Usted puede probar la conexión y añadir una descripción.

   
   

   Figura 3. Propiedades de conexión

4. Usted puede opcionalmente definir en la pestaña Connection Properties a qué base de datos conectarse por defecto, el protocolo de red (TCP-IP, Memoria Compartida o Canalizaciones con Nombre). Usted también puede definir el tamaño del paquete. Si ejecuta muchas operaciones en masa, puede que sea conveniente incrementar el tamaño del paquete. Si no realiza muchas operaciones, la otra mano puede reducir el tamaño del paquete para incrementar la eficiencia..

   Usted también puede especificar el tiempo de espera de la conexión y de la ejecución. También es posible encriptar la conexión y seleccionar un color personalizado.

   
   

   Figura 4. Las Propiedades de Conexión

5. Repita los pasos 3, 4 y 5 para añadir todos los SQL Servers disponibles.

6. Una vez que todos los SQL Servers son añadidos, haga clic derecho en Server Group y seleccione New Query. . Con esta opción, usted puede correr una consulta T-SQL en múltiples SQL Servers.

   

   Figura 5. Corriendo una Consulta

7. Corramos una consulta simple mostrando los nombres del servidor:

   1	select @@servername

8. La consulta mostrará los diferentes nombres del servidor.

   

   Figura 6. Los Nombres de SQL Server

9. Usted también puede crear una base de datos en cada SQL Server:

   1	create database db4

10. Si usted corre la consulta, obtendrá un mensaje de éxito o error.

    
    

    Figura 7. Creando Bases de Datos

11. Usted también puede verificar sesiones, usuarios, bloques de procesos usando el procedimiento de sistema sp_who2:

    1

    sp_who2

    
    

    Figura 8. Los usuarios actuales, sesiones y procesos de todas las instancias SQL Server

12. Si usted tiene las mismas bases de datos en el Servidor con las mismas tablas, usted puede verificar la fragmentación en todos los servidores. El siguiente ejemplo muestra cómo revisar que el porcentaje de fragmentación promedio sea más alto que 30% en la tabla Person.Address de la base de datos AdventureWorks2014:

    1 2 3 4 5 6 7 8 9

    DECLARE @db_id SMALLINT; DECLARE @object_id INT;   SET @db_id = DB_ID(N'AdventureWorks2014'); SET @object_id = OBJECT_ID(N'AdventureWorks2014.Person.Address');     SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED') where avg_fragmentation_in_percent>30

13. Usted también puede verificar el estado de los trabajos de SQL Server Agent. Por ejemplo, para verificar los trabajos SQL que fallaron en todas las instancias SQL Server, usted puede usar la siguiente consulta T-SQL:

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

    SELECT [instance_id]       ,[job_id]       ,[step_id]       ,[step_name]       ,[sql_message_id]       ,[sql_severity]       ,[message]       ,[run_status]       ,[run_date]       ,[run_time]       ,[run_duration]       ,[operator_id_emailed]       ,[operator_id_netsent]       ,[operator_id_paged]       ,[retries_attempted]       ,[server]   FROM [msdb].[dbo].[sysjobhistory]   where run_status=0

14. La información del historial de trabajos está en la tabla de sistema sysjobhistory; run_status muestra el estado del trabajo. Si el estado del trabajo es 0, significa que el trabajo falló. Si el estado es 1, significa que el trabajo corrió exitosamente. 2 es que el trabajo reintentó y 4 que el trabajo fue cancelado.

    
    

    Figura 9. El estado de los trabajos

15. Otra consulta popular es para ver el tamaño de todas las bases de datos al mismo tiempo. El tamaño de base de datos puede ser obtenido desde sys.master_files. En la tabla de sistema sys.databases usted puede obtener todas las bases de datos. Usted suma todos los tamaños de los archivos porque una base de datos puede tener múltiples archivos de datos y registros. El tipo 0 son archivos de datos y el tipo 1 son archivos de registros. La consulta sería la siguiente:

    1 2 3 4 5 6 7 8 9 10 11 12

    with f as (     select database_id, type, size * 8.0 / 1024 filesize     from sys.master_files ) select     name,     (select sum(filesize) from f where type = 0 and f.database_id = d.database_id) DataSize,     (select sum(filesize) from f where type = 1 and f.database_id = d.database_id ) LogSize from sys.databases d

16. El resultado de la consulta mostrará los archivos de datos y registros de todas las bases de datos de todas las instancias:

    
    

    Figura 10. Los tamaños de archivos de datos y registros

17. El siguiente comando habilitará las opciones avanzadas en todos los SQL Servers:

    1 2 3 4

    USE master; GO EXEC sp_configure 'show advanced option', '1'; RECONFIGURE WITH OVERRIDE;

18. Una vez habilitado, habilitaremos la opción avanzada con xp_cmdshell. Este procedimiento almacenado extendido permite correr la línea de comandos desde el T-SQL. Es una característica muy poderosa que funcionará dependiendo de los privilegios de la cuenta usada para conectarse en el paso 3 a los servidores. Usted necesitará algunos privilegios administrativos para correr el comando. Habilitemos el procedimiento xp_cmdshell primero:

    1 2	EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE;

19. Xp_cmdshell es un comando muy poderoso, pero también peligroso por razones de seguridad. Es por eso que está deshabilitado por defecto. El siguiente ejemplo crea una carpeta llamda backupfolder en el disco c:

    1	xp_cmdshell 'mkdir c:\\backupsfolder'

20. Si todo salió bien, usted tendrá una nueva carpeta en el Sistema Operativo:

    
    

    Figura 11. La carpeta backupsfolder creada

21. Finalmente, corramos la copia de seguridad en la carpeta creada:

    1 2	BACKUP DATABASE [db4] TO  DISK = N'c:\\backupsfolder\db4_a.bak' WITH NOFORMAT, NOINIT,  NAME = N'db4-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 GO

22. Si todo está bien, usted tendrá una copia de seguridad creada para todas las instancias SQL Server.

    
    

    Figura 12. La copia de seguridad de la base de datos

Conclusión

En este capítulo mostramos cómo correr diferentes consultas en múltiples instancias de SQL Server. Verificamos los nombres del servidor, creamos una base de datos en todas las instancias de SQL Server, verificamos los usuarios, sesiones y procesos actuales, verificamos la fragmentación mayor a 30 de una tabla específica en todas las instancias, también verificamos si los Trabajos de SQL Agent tienen algunos errores en la ejecución. Finalmente, habilitamos las opciones de configuración avanzada y creamos una carpeta en todos los SQL Servers. El último paso fue crear la copia de seguridad en la carpeta nueva. Como puede ver, administrar todos los servidores en una manera centralizada puede ser una tarea sencilla.

Usted también puede aplicar políticas SQL a múltiples Servidores al mismo tiempo. El artículo The evaluating policies on multiple instances le mostrará cómo hacerlo.

• Acerca de
• Últimas entradas

Daniel Calbimonte

Daniel Calbimonte es un Microsoft Most Valuable Professional, Microsoft Certified Trainer y Microsoft Certified IT Professional en SQL Server. He is an accomplished SSIS author, Intructor en academias de IT y tienedécadas de experience trabajando con diferentes bases de datos.

Realizó consultorias para empresas gubernamentales, empresas petroleras, sitios web, revistas y universidades alrededor del mundo. Daniel participa regularmente en conferencias de SQL Server y blogs. Escribe material sobre SQL Server y exámenes de certificación.

También realiza la traducción de artículos de SQLShack al español

Ver todas las publicaciones de Daniel Calbimonte

Últimas entradas de Daniel Calbimonte (ver todo)

• Cómo construir un cubo desde cero usando SQL Server Analysis Services (SSAS) - December 16, 2019
• Fecha de conversión de SQL - December 11, 2019
• Funciones frente a los procedimientos almacenados en SQL Server - October 14, 2019