El día 8 de Julio de 2017 participamos en el evento SQL Tips ‘n’ Tricks, organizado por Pass Lima. En esta ocación presentamos 10 Tips ‘n’ Tricks for DBAs, en el que mostramos recomendaciones en base a las mejores prácticas de SQL Server, explicando la razón de por qué implementarlas y los scripts necesarios para hacerlo. Aquí compartimos tanto la presentación como los scripts utilizados:
1.Controlar del espacio disponible.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--Obtener información de tamaño y espacio libre de los archivos SELECT DB_NAME() AS BaseDatos, file_id, type_desc AS TipoArchivo, name AS NombreArchivo, physical_name AS ArchivoFisico, --size, size/128.0 AS TamañoMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS EspacioLibreMB, ((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)/(size/128.0))*100 AS PctLibre FROM sys.database_files; GO |
2.Configurar del espacio de crecimiento automático.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
--Obtener información de autocrecimiento de los archivos SELECT DB_NAME() AS BaseDatos, file_id, type_desc AS TipoArchivo, name AS NombreArchivo, physical_name AS ArchivoFisico, size/128.0 AS TamañoMB, CASE is_percent_growth WHEN 1 THEN RTRIM(CAST(growth AS VARCHAR(30)))+'%' ELSE CAST(ROUND((growth*8.)/1024.00,0) AS VARCHAR(30)) END CrecimientoMB FROM sys.database_files WHERE type_desc = 'ROWS'; GO --Obtener sentencias de configuración de autocrecimiento, en base a 1/4 del tamaño actual de los archivos SELECT CONCAT('ALTER DATABASE ', DB_NAME(), ' MODIFY FILE ( NAME = ''',name,''',', ' FILEGROWTH = ', CAST((size/128)/4 AS varchar(100)),'MB);') FROM sys.database_files WHERE type_desc = 'ROWS'; GO |
3.No activar la característica de auto-encogido.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--Identificar configuración de autoencogido SELECT name AS BaseDatos, CASE is_auto_shrink_on WHEN 1 THEN '******* Prendido - FATAL *******' ELSE 'Apagado - OK' END AutoEncogidoON, CASE is_auto_shrink_on WHEN 1 THEN CONCAT('ALTER DATABASE ',name,' SET AUTO_SHRINK OFF WITH NO_WAIT;') ELSE NULL END ScriptCorreccion FROM sys.databases; GO |
4.Ubicar los archivos en arreglos físicos separados.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
--Identifica Ubicación de archivos SELECT DB_NAME() AS BaseDatos, file_id, type_desc AS TipoArchivo, name AS NombreArchivo, LEFT(physical_name,2) AS Unidad, physical_name AS ArchivoFisico FROM sys.database_files GO --Cambia la ubicación de los archivos USE master; GO ALTER DATABASE TestFiles SET OFFLINE; GO ALTER DATABASE TestFiles MODIFY FILE ( NAME = TestFiles, FILENAME = 'D:\SQLData\SQL130\TestFiles.mdf' ); GO ALTER DATABASE TestFiles SET ONLINE; GO |
5.Distribuir la base de datos tempdb en varios archivos de datos.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
--Identificamos configuración actual USE tempdb SELECT * from sys.database_files --Determinamos en numero de CPUs DECLARE @Contador tinyint, --Control del loop @ConteoCPU tinyint, --Cantidad de CPUS para el numero de archivos. Máximo 8 @TamañoArchivoMB smallint, --Tamaño de los nuevos archivos @Query nvarchar(1000), --Variable para armar la consulta de creación de los archivos @Ruta varchar(1000) --Ruta de ubicación del archivo --Obtenemos la cantidad de CPUS, aprovechamos el query para definir demás variables SELECT @Contador =1, @ConteoCPU=cpu_count,@TamañoArchivoMB=10 FROM sys.dm_os_sys_info --Obtenemos la ruta actual de los archivos de datos SELECT TOP 1 @Ruta=LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1) FROM sys.database_files WHERE Type=0 WHILE @Contador<=@ConteoCPU-1 AND @Contador<=8 BEGIN SET @Query = CONCAT('ALTER DATABASE tempdb ADD FILE (NAME = ''tempdev',CAST(@Contador+1 AS char(1)),'''', ', SIZE = ', CAST(@TamañoArchivoMB AS varchar(10)), ', FILENAME = ''',@Ruta,'tempdev',CAST(@Contador+1 AS char(1)),'.ndf''', ')') PRINT @Query EXEC sys.sp_executesql @command1 = @Query SET @Contador += 1 END |
6.Verificar los trabajos (JOBS) programados.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
--Obtener detalle de los Trabajos (Jobs) SELECT j.name AS Job, j.description AS Descripcion, CASE WHEN j.enabled = 1 THEN 'Habilitado' ELSE 'Deshabilitado' END Estado, CASE WHEN ss.freq_type = 1 THEN 'Una vez' WHEN ss.freq_type = 4 THEN 'Diario' WHEN ss.freq_type = 8 THEN 'Semanal' WHEN ss.freq_type = 16 THEN 'Mensual' WHEN ss.freq_type = 32 THEN 'Relativo mensual' WHEN ss.freq_type = 64 THEN 'Cuando se inicia el Agente SQL Server' END Frecuencia, CASE WHEN next_run_date = 0 THEN CONVERT(SMALLDATETIME, CONVERT(char(10), GETDATE() + 1, 20) + ' ' + LEFT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2) + ':' + SUBSTRING(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 3, 2) + ':' + RIGHT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2)) WHEN active_start_time = 0 THEN CONVERT(SMALLDATETIME, LEFT(CAST(next_run_date AS char(8)), 4) + SUBSTRING(CAST(next_run_date AS char(8)), 5, 2) + RIGHT(CAST(next_run_date AS char(8)), 2) + ' 00:00:00') WHEN active_start_time <> 0 THEN CONVERT(SMALLDATETIME, LEFT(CAST(next_run_date AS char(8)), 4) + SUBSTRING(CAST(next_run_date AS char(8)), 5, 2) + RIGHT(CAST(next_run_date AS char(8)), 2) + ' ' + LEFT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2) + ':' + SUBSTRING(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 3, 2) + ':' + RIGHT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2)) END SiguienteEjecucion, jh.run_date, STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(jh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'Duración', CASE jh.run_status WHEN 0 THEN 'Fallo' WHEN 1 THEN 'Éxito' WHEN 2 THEN 'Reintento' WHEN 3 THEN 'Cancelado' END Resultado from msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobschedules s ON j.job_id = s.job_id INNER JOIN msdb.dbo.sysschedules ss ON s.schedule_id=ss.schedule_id INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id=jh.job_id AND jh.step_id=0; GO |
7.Confirmar las copias de seguridad.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
USE msdb; GO --Historial de copias de seguridad SELECT DISTINCT DB_ID(s.database_name) AS IDBaseDatos, s.database_name AS BaseDatos, s.backup_start_date AS Inicio, s.backup_finish_date AS Fin, CAST(s.backup_finish_date-s.backup_start_date AS TIME) AS Tiempo, CASE s.type WHEN 'D' THEN 'D - Full' WHEN 'I' THEN 'I - Diferencial' WHEN 'L' THEN 'L - Log' WHEN 'F' THEN 'F - Archivo/Grupo archivo' WHEN 'G' THEN 'G - Archivo diferencial' WHEN 'P' THEN 'P - Parcial' WHEN 'Q' THEN 'Q - Parcial diferencial' END AS Tipo, CASE s.is_copy_only WHEN 1 THEN 'Si' ELSE 'No' END SoloCopia, s.backup_size/1024.0/1024.0/1024.0 AS TamañoGB, ISNULL(mf.logical_device_name,'') AS DispositivoLogico, mf.physical_device_name AS ArchivoFísico, LEFT(mf.physical_device_name,2) AS Unidad, s.name AS BackupSet, ISNULL(s.description,'') AS Descripción FROM msdb.dbo.backupmediafamily mf INNER JOIN msdb.dbo.backupset s ON mf.media_set_id = s.media_set_id WHERE (CONVERT(datetime, s.backup_start_date, 102) >= GETDATE() - 180) ORDER BY s.database_name, s.backup_finish_date; GO --Backup más reciente por base de datos SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date, DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)] FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D' GROUP BY msdb.dbo.backupset.database_name ORDER BY msdb.dbo.backupset.database_name; GO |
8.Asignar Password fuerte a la cuenta SA.
1 2 3 4 |
--Modificar el password del SA ALTER LOGIN SA WITH PASSWORD = '1mTheStr0ngerDBAOnE@rt2017'; GO |
9.Crear usuarios con privilegios mínimos indispensables.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
--Creamos un inicio de sesión con un password fuerte CREATE LOGIN AdminApp WITH PASSWORD = 'Cl@veSuperSegura2017'; GO USE WideWorldImporters; GO --Creamos un usuario en la base de datos CREATE USER AdminApp FOR LOGIN AdminApp; GO --Añadimos el usuario a los roles de lectura y escritura ALTER ROLE db_datareader ADD MEMBER AdminApp; GO ALTER ROLE db_datawriter ADD MEMBER AdminApp; GO --Creamos un rol para ejecución de código CREATE ROLE dbu_execute AUTHORIZATION dbo; GO GRANT EXECUTE TO dbu_execute; GO --Añadimos al usuario al rol de ejecución de código ALTER ROLE dbu_execute ADD MEMBER AdminApp; GO |
10.Ejecutar DBCC CHECKDB.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DBCC CHECKDB('CorruptDB') WITH NO_INFOMSGS; GO --Cambiar la BD a usuario único ALTER DATABASE CorruptDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO --Corrección permitiendo perdida de datos DBCC CHECKDB('CorruptDB', REPAIR_ALLOW_DATA_LOSS); GO --Cambiar la BD a usuario multiple ALTER DATABASE CorruptDB SET MULTI_USER WITH ROLLBACK IMMEDIATE; GO |