SQL Server Disaster Recovery

Revisiones

Lista de distribución

Contenido

Acerca de este documento

Introducción

Este documento es un resumen de la información obtenida en la Expert Round Table de Microsoft celebrada el 1 de Febrero del 2000.

Objetivos

El objetivo de este documento es transmitir la información práctica obtenida durante la Expert Round Table. En ella se abordaron temas muy concretos, de modo que, en este texto, algunos puntos pueden parecer incompletos. Para obtener información más amplia, consultar la ayuda (Books Online, MSDN, Knowledge Base) o un manual de administración de SQL Server. Aunque la información contenida en este documento es eminentemente práctica, en su mayoría no ha sido contrastada por nosotros, de modo que se agradecerá cualquier comentario acerca de su exactitud y utilidad.

A quién está dirigido

A todos aquellos que realicen tareas administrativas en SQL Server.

Instalación de SQL Server en cluster

Sistemas cluster

Un cluster es un sistema formado por dos máquinas que comparten una cabina de discos. Pueden considerarse dos tipos de configuración:

• Configuración Activo-Pasivo: Uno de los nodos se encuentra en estado de `espera', y solo entra en funcionamiento en caso de fallo del nodo activo, mediante un proceso denominado failover.

• Configuración Activo-Activo: Ambos nodos se funcionan al mismo tiempo. El administrador puede priorizar uno de los nodos respecto al otro.

Instalación de SQL Server en cluster

Para utilizar SQL Server en una configuración cluster es necesario tener instalado Windows NT Server Enterprise Edition más el Cluster Server del sistema operativo. Además, la versión de SQL Server ha de soportar instalación cluster.

Procedimiento de instalación

En la instalación de SQL Server en una configuración cluster es importante el orden en el que se instalan los Service Packs, herramientas adicionales, option pack, etc. El orden recomendado por Microsoft es el siguiente:

• Orden de instalación con SQL 6.5

• NT Enterprise Edition + Internet Explorer 4.x o 5.x + Cluster Server

• MS Message Queue (Q188685)

• SQL Server 6.5 Enterprise Edition + SP 5 + Clusterizar SQL Server (MSSQL\Cluster\clustwiz.exe) + MS DTC (Q183672)

• Windows NT Option Pack (Q191138)

• NT SP4 o SP5 o SP6a

Referencias:

Q188685 HOWTO: Install an MSQM PSC on Cluster Server

Q183672 Upgrade a clustered MSQM SQL to SQL Enterprise Edition

Q191138 How to install the NTOP on Cluster Server

• Orden de instalación con SQL 7.0

• NT Enterprise Edition + Internet Explorer 4.x o 5.x + Cluster Server

• Windows NT Option Pack (Q223258)

• NT SP4 o SP5 o SP6

• MS DTC + Crear recursos IP, Network Name

• SQL Server 7.0 Enterprise Edition + SP 1 + Clusterizar SQL Server (MSSQL\Cluster\clustwiz.exe)

• NT SP4 o SP5 o SP6

Referencias:

Q223258 How to install the Windows NT Option Pack on MSCS 1.0 with SQL Server 6.5 or 7.0

Consideraciones adicionales:

• En ambas versiones, en el caso de una configuración Activo-Activo hay que clusterizar dos veces, una en cada sentido.

• En general, antes de instalar NT SP4 o posterior es conveniente instalar NT Option Pack.

• Orden de instalación de SQL 6.5 y 7.0 sobre Windows 2000

• Windows 2000

• Crear cuenta de servicios de SQL Server + Detener servicios

• SQL Server 6.5 + SP5a / SQL Server 7.0 + SP 1

• Clusterizar SQL (MSSQL\Cluster\clustwiz.exe)

• Comprobar si existe el recurso DTC en el grupo Cluster group. Ejecutar comclust.exe

• Orden de actualización de SQL Server 6.5 a 7.0

• Desclusterizar SQL Server 6.5

• Instalar SQL Server 7.0 + SP1

• Actualizar las bases de datos a 7.0

• Comprobar que los datos se han actualizado correctamente.

• Comprobar si existe el recurso DTC en el grupo Cluster group. Ejecutar comclust.exe

Problemas de instalación

Los problemas más habituales durante la instalación se producen debido a conflictos con el MS DTC. Pueden ser:

• NT Option Pack no puede ser instalado.

• NT Option Pack se ha instalado pero el recurso MS DTC falla en el Administrador de Cluster

• SQL Server y NT Option pack se han instalado, y MS DTC es el único recurso que falla

Existe un procedimiento para desinstalar DTC e instalar el que trae SQL Server, que en la mayoría de los casos resuelve estos conflictos:

• En la clave de registro:

HKLM\

cluster\

resource types\

distribution transaction coordinator\

DLL name

Sustituir el nombre mtxclu.dll por clussrv.dll

• Borrar toda la clave:

HKLM\Software\Microsoft\MSDTC

• Repetir 1 y 2 en el otro nodo del cluster.

• En el CD de SQL Server 7.0 ejecutar Other\DTCSetup.exe. Antes de pulsar OK, realizar el paso 4 en el otro nodo del cluster.

Ficheros creados durante la instalación

Durante la instalación se escribe en los siguientes ficheros:

• Winnt\sqlstp.log

• Winnt\setup.log

• MSSQL7\install\cnfgsvr.out

• MSSQL7\Log\errorlog

• Cluster.log (la escritura en cluster.log se configura mediante las variables de entorno CLUSTERLOG y CLUSTERLOGLEVEL, que pueden cambiarse desde Panel de control/Sistema)

• Clustwiz.log (requiere habilitar la variable de sistema _PRINT_FILE_)

• %systemroot%\sqlclstr.log (sólo en 7.0)

En SQL 7.0 si se establece la variable de sistema _PRINT_CONSOLE con valor TRUE aparece una ventana con mensajes adicionales.

Referencias:

Q221832 How to enable logging for SQL Server cluster wizard

Best practices en el mantenimiento del servidor

Conectividad

Comprobaciones para resolución de problemas

Para realizar comprobaciones disponemos de los siguientes medios:

• En conexiones Named Pipes:

• Testeo: net view \\ nombreservidor

• Verificación conexión: net use \\ nombreservidor

• Chequeo integridad: makepipe, readpipe

• En conexiones TCP/IP:

Ping IP | nombre_servidor

• Trazas ODBC

• Trazas de monitor de red

Procesos huérfanos

Los procesos huérfanos son conexiones que se quedan abiertas sin que haya un proceso usándolas. Para detectarlas puede utilizarse el Enterprise Manager o el SP sp_who. Si aparecen con frecuencia puede ser debido a que se tiene un tiempo de vida demasiado alto para las conexiones. Como solución, comprobar las claves de registro:

• En conexiones Named Pipes:

HKLM\system\ccs\services\netbt\parameters\SessionKeepAlive

• En conexiones TCP/IP:

HKLM\system\ccs\services\tcpip\parameters\KeepAliveInterval

HKLM\system\ccs\services\tcpip \parameters\SessionKeepAlive

HKLM\system\ccs\services\tcpip \parameters\TcpMaxDataRetransmissions

Referencias:

Q137983 INF: How to troubleshoot orphaned connections in SQL Server

Permisos

Para un funcionamiento correcto de SQL Server hay que tener en cuenta lo siguiente sobre las cuentas de arranque de servicios:

• El usuario que inicia ha de tener permisos de control total en los directorios MSSQL, MSSQL7 y en aquellos directorios donde residan bases de datos.

• Hay que habilitar la directiva “Iniciar sesión como servicio”.

• Hay que verificar que no existan horas de restricción de logon y marcar que el password nunca caduca.

• Las cuentas deben pertenecer al grupo de administradores locales de la máquina.

• Han de tener control total en las claves:

HKLM\Software\Microsoft\MSSQLServer

HKLM\Software\Microsoft\WindowsNT\CurrentVersion\perflib

HKLM\System\CCS\Services\ MSSQLServer

Configuración

Errores habituales por problemas de configuración y sus soluciones son:

• PROBLEMA:

“Could not start the MSSQLServer service on \\ servername Error 2140: An internal error ocurred”

• SOLUCION:

Revisar en log en busca de la causa. Causas probables son:

• Valor de memoria muy elevado

• Valor de tempdb en RAM muy alto

Para solucionarlos:

• Arrancar el servidor en modo consola y configuración mínima (sqlservr.exe -c -f)

• Realizar las modificaciones en la configuración:

sp_configure memory 8192

sp_configure `tempdb' in ram, 2

• PROBLEMA:

Memoria virtual insuficiente

• SOLUCION

• Es recomendable que la memoria virtual sea 1.5 veces la RAM

• Utilizar max server memory y min server memory

• PROBLEMA

“The working thread limit of 255 has been reached.”

• SOLUCION

Aumentar Max worker threads. Tener en cuenta que si se aumentan demasiado el efecto es negativo.

• PROBLEMA

Problema en el winlogon asociado a los contadores extendidos de SQL Server.

• SOLUCION

Alguna aplicación de terceros a sobrescrito los contadores. Reinstalar sqlctrl70.dll.

• PROBLEMA

Consumo excesivo de recursos

• SOLUCION

Buscar el motivo monitorizando los siguientes contadores para cada proceso:

• Handle count

• Page file bytes

• Pool paged bytes

• Pool nonpaged bytes

• Private bytes

• Thread count

• Virtual bytes

• Working set

Alertas

SQL Server gestiona las alertas mediante el SQL Server Agent (SQL Executive en versiones anteriores a la 7.0), por tanto, si de da el problema de que una alerta no se ejecuta, además de verificar si está arrancado el servicio EventLog, habrá que comprobar que esté arrancado el Server Agent.

Para monitorizar el funcionamiento del SQL Server Agent se puede habilitar el log desde el Enterprise Manager o mediante el comando:

Sqlexec.exe -c -v > sqlexec.out

Si lo que se desea es configurar un error para que no dispare una alerta, puede hacerse modificando esta clave de registro:

HKLM\Software\Microsoft\MSSQLServer\SQLServerAgent\NonAlertableErrors

Referencias:

Q155283: Troubleshooting SQL Executive and Task Scheduling

Bloqueos

SQL Server resuelve los bloqueos, eligiendo una tarea como víctima. Para concocer el tipo de bloqueo y el comando afectado existen dos posibilidades:

• Sqlservr -T1204

• Dbcc traceon(1204)

Algunos comandos útiles para el análisis de bloqueos son:

DBCC INPUTBUFFER (spid)	Devuelve la última consulta ejecutada por el proceso
DBCC PSS	Proporciona información de qué conexiones están bloqueando a otras
sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']	Proporciona información sobre bloqueos

Referencias:

Q162361: Understanding and Resolving SQL Server Blocking Problems

Mantenimiento de las Bases de Datos

Procesos de backup y restore

Políticas de copia de seguridad

Los procesos de backup escriben conjuntos de páginas en principio inconsistentes. Para hacerlos consistentes, los backup incluyen el Transaction Log. Las posibles metodologías de copia de seguridad son las siguientes:

• Backups completos periódicos

Se realizan backups periódicos de la base de datos completa. La ventaja es la facilidad de administración, el inconveniente es que no se garantizan los cambios desde el último backup.

• Backups completos periódicos con Transaction Log

Se realizan backups periódicos de la base de datos completa y con mayor frecuencia, backups del Transaction Log. La realización de copias de seguridad del Transaction Log proporciona una gran flexibilidad en la restauración de bases de datos, ya que permite restaurar la base de datos hasta el instante anterior a que se produjese un error, o hasta un instante determinado del tiempo:

• Restaurar hasta el punto anterior a un fallo

• Realizar un backup del Transaction Log “activo” con WITH NO_TRUNCATE. De este modo, SQL Server no truncará la parte inactiva del Transaction Log.

• Restaurar la base de datos con WITH RECOVERY. No se hará rollback de todas las transacciones uncommited.

• Restaurar los Transaction Logs de los que se había hecho backup desde el backup de la base de datos hasta el backup del Transaction Log “activo”. Usar la opción WITH NORECOVERY.

• Restaurar el Transaction Log que estaba “activo” durante el error, esta vez con la opción WITH RECOVERY, para que se haga rollback de las transacciones uncommited.

• Restaurar hasta un punto determinado en el tiempo

• Restaurar la base de datos, con WITH NORECOVERY.

• Restaurar todos los Transaction Logs, usando, p. ej., WITH RECOVERY, STOPAT = `Jul 1, 1998 10:00 AM

• Backups diferenciales (a partir de 7.0)

Copian los cambios en la base de datos desde el ultimo backup completo.

• Backups parciales (a partir de 7.0)

SQL Server 7 permite realizar backups de determinados FileGroups de una misma base de datos. Esto permite:

• Realizar backups/restores en paralelo.

• Separar en un FileGroup las filas de sólo lectura y hacer backup periódico de las filas que cambian.

• Incrementar el paralelismo en acceso.

• Restaurar sólo el FileGroup dañado, manteniéndose mientras tanto el acceso a los demás.

Rendimiento de los procesos de backup

Los benchmarkings de SQL Server (www.microsoft.com/sql) proporcionan los siguientes resultados en cuanto a procesos de backup:

• Un backup online consume aproximadamente un 15% de recursos de procesador. Un restore consume un 8%.

• El ancho de banda utilizado por el backup apenas se ve penalizado por la carga del servidor.

Para incrementar el rendimiento de los procesos de backup pueden tomarse las siguientes medidas:

• Incrementar el paralelismo, es decir aumentar el número de unidades de cinta.

• Balancear velocidades de discos y dispositivos de backup. La velocidad de lectura de disco puede medirse realizando un backup a nul.

• No debe compartirse la controladora SCSI entre la unidad de cinta y el disco.

• No debe utilizarse compresión hardware si los datos no son comprimibles (p.ej. una base de datos de imágenes)

Recuperación de corrupciones de bases de datos

Reparación de una base de datos `suspect' en SQL Server 6.5

• Hacer una copia de los dispositivos afectados.

• Instalar el script ubicado en MSSQL\INSTALL\instsupl.sql

• Ejecutar sp_resetstatus, que intentará volver a poner el bit de estado a normal. En determinadas ocasiones SQL Server pone la base de datos en Suspect sin que haya existido un problema con los datos. Si no es posible volver al estado normal, pasar al paso 4.

• Poner la base de datos en modo de emergencia. Esto nos permitirá extraer los datos.

UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'

• Extraer los datos mediante SELECT INTO/BCP

Restauración de bases de datos de las que no se tiene backup

SQL Server 6.5

• Utilizar el comando DISK REINIT para reinicializar el dispositivo de datos y añadir las entradas adecuadas en sysdevices:

DISK REINIT

NAME = `DEVICE5'

PHYSNAME = 'c:\sqldata\device5.dat'

VDEVNO = 5

SIZE = 25600

• Utilizar el comando DISK REFIT, que escanea el disco físico

SQL Server 7.0

Cuando se desea restaurar una base de datos de la que no se tiene backup, la base de datos puede encontrarse en alguno de los siguientes estados:

HEX Status	Value
0x0000 0001	Autoclose
0x0000 0004	Select into/bulkcopy
0x0000 0008	Trunc.log on chkpt.
0x0040 0000	Autoshrink
0x4000 0000	Cleany shutdown

Si la base de datos ha sido desvinculada de un servidor mediante sp_detach_db (p.ej. para trasladarla de un servidor a otro), podrá volver a instalarse en el servidor destino mediante sp_attach_db o sp_attach_single_file_db.

En el caso de una base de datos que no ha sido desvinculada correctamente, los pasos son los siguientes:

• Crear una B.D. con el mismo nombre y tamaño en los ficheros mdf, ndf, ldf.

• Detener SQL Server y arrancarlo con:

Sqlservr -m

Esto arrancará con Single user

• Poner la base de datos en modo Recovery:

Use master

Go

Update sysdatabases set status=-32768 where name='dbname'

• Detener SQL Server

• Renombrar los ficheros mdf, ndf, ldf y remplazarlos con los originales.

• Arrancar SQL Server con:

Sqlservr -m -T910

De este modo el motor ignorará que la base de datos no se encuentra en un estado correcto.

• Actualizar sysdatabases, poniendo la base de datos en estado correcto:

Use master

Go

Update sysdatabases set status=0 where name='dbname'

• Si la base de datos no se recupera:

• Iniciar el servidor con la línea de comandos:

Sqlservr -m -T3608 -T4022

El parámetro 3608 sólo inicia la master, y el 4022 no dispara el procedimiento almacenado de inicialización.

• Poner la base de datos en modo Recovery

• Utilizar DTS o bcp para extraer la mayor cantidad de datos posible.

En el caso de que en SQL Server 7.0 se desee restaurar bases de datos del sistema en otras ubicaciones, pueden seguirse estos procedimientos:

• MASTER: Modificar los Startup Parameters del servidor:

• MODEL: Utilizar:

Sqlservr -T3608

Sp_detach_db `model'

Sp_attach_db `model', `E:\data\model.mdf','E:\data\modellog.ldf'

• TEMPDB: Utilizar:

Alter database tempdb modify file (name=tempdev, filename='E:\data\tempdb.mdf')

Alter database tempdb modify file (name=templog, filename='E:\data\tempdb.ldf')

Referencias:

Q224071: Moving SQL Server 7.0 Databases to a new location

Errores comunes de chequeos de tablas

Error 605

“Attempt to fetch logical page %ld in database `%.*s' belongs to object `%.*s' not to object `%.*s'

Las opciones de recuperación son las siguientes:

• Recuperación usando un clustered index

Las páginas de datos de una tabla están enlazadas mediante punteros en doble sentido. Si el error ha sido debido a que alguno de los punteros apunta donde no debe, podremos extraer datos haciendo una consulta ordenada en un determinado sentido, dependiendo del sentido del puntero erróneo. P. Ej:

SELECT * FROM titles ORDER BY pub_id DESC

• Recuperación usando un non-clustered index

El error también puede ser debido a una corrupción del índice cluster, en este caso podremos extraer datos utilizando un índice non-clustered en la SELECT:

SELECT * FROM authors (INDEX=aunmind)

• Recuperación modificando la columna sysindexes.first del objeto

Este método está indicado sólo si no funciona ninguno de los anteriores. Los pasos son los siguientes:

• Extraer todos los datos posibles (bcp). Obtendremos los datos hasta la página donde se haya producido el error.

• Localizar la página errónea:

Dbcc traceon(3604)

Dbcc pagelinkage

• Modificar la columna sysindexes.first del objeto de modo que apunte a la página siguiente a la errónea. En esta columna se indica cuál es la página donde se inicia una tabla.

• Extraer datos.

• En el caso de que hayan más páginas erróneas, repetir el procedimiento.

Error 2503

“Table corrupt: Page linkage is not consistent; check the following pages: (current page#=%ld; page# pointing to this page=%ld; previous page# indicated on this page=%ld)”

Utilizar BCP / SELECT INTO.

Error 2540

“Allocation discrepancy: Page is allocated but not linked; check the following pages and ids: allocation pg#=%ld extent=%ld logical pg#=%ld object id on extent=%ld (object name=%.*s) indid on extent=%ld”

El motivo de este error es que una página está siendo marcada como reservada para un objeto, pero no está siendo utilizada. Este error puede solucionarse mediante el comando DBCC FIX_AL.