Curso De Administracion Y Sintonizacion De Microsoft Sql

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

SolucionJava.com

Manual del alumno

Ing. Cedric Simon – Tel: 2268 0974 – Cel: 8888 2387 – Email: c e d r i c @ s o l u c i o n j a v a . c o m – Web: www.solucionjava.com

Curso de administración y sintonización de Microsoft SQL Server 2000 – 2005 – 2008

Curso de administración y sintonización de Microsoft SQL Server

Pagina 2 / 23

Índice 1 Introducción al curso.....................................................................................................................................................................5 1.1Objetivo de este curso.............................................................................................................................................................5 1.2Manual del alumno.................................................................................................................................................................5 1.3Ejercicios prácticos.................................................................................................................................................................5 1.4Requisitos para atender a este curso.....................................................................................................................................5 1.5Soporte después del curso.......................................................................................................................................................5 2INSTALACIÓN DEL SERVIDOR.................................................................................................................................................6 2.1Planificación............................................................................................................................................................................6 2.1.1Requisitos materiales.........................................................................................................................................................6 2.1.1.1Para SQL Server 2000:..............................................................................................................................................6 2.1.1.2Para SQL Server 2005:..............................................................................................................................................6 2.1.1.3Para SQL Server 2008:..............................................................................................................................................6 2.1.2Requisitos de software......................................................................................................................................................7 2.1.2.1SQL Server 2000.......................................................................................................................................................7 2.1.2.2SQL Server 2005.......................................................................................................................................................7 2.1.2.3SQL Server 2008.......................................................................................................................................................7 2.1.3Requisitos de sistema operativo........................................................................................................................................7 2.1.3.1SQL Server 2005.......................................................................................................................................................7 2.1.3.2SQL Server 2008.......................................................................................................................................................7 2.1.4Requisitos de memoria......................................................................................................................................................8 2.1.4.1Memoria mínima recomendada con SQL Server 2000:............................................................................................8 2.1.4.2SQL Server 2005 32 bits...........................................................................................................................................8 2.1.4.3SQL Server 2008.......................................................................................................................................................8 2.1.5Requisitos de disco............................................................................................................................................................8 2.1.5.1SQL Server 2000.......................................................................................................................................................9 2.1.5.2SQL Server 2005.......................................................................................................................................................9 2.1.6Determinar las cuentas apropiadas para los servicios SQL Server y SQL Agent.............................................................9 2.1.7Determinar el modo de autenticarse..................................................................................................................................9 2.1.8Intercalación: determinar el juego de caracteres...............................................................................................................9 2.1.9Seleccionar la librería de red.............................................................................................................................................9 2.1.10Decidir del modo de licencias clientes..........................................................................................................................10 2.2Instalación..............................................................................................................................................................................10 2.3Múltiples instancias...............................................................................................................................................................10 2.4Otras informaciones útiles....................................................................................................................................................10 3CREACIÓN DE BASES DE DATOS...........................................................................................................................................11 3.1Bases de datos del sistema ...................................................................................................................................................11 3.1.1La base de datos master...................................................................................................................................................11 3.1.2La base de datos model...................................................................................................................................................11 3.1.3La base de datos msdb.....................................................................................................................................................11 3.1.4La base de datos tempdb.................................................................................................................................................11 3.2Bases de datos de usuarios ...................................................................................................................................................11 3.3Configuración........................................................................................................................................................................11 3.3.1Intercalación....................................................................................................................................................................11 3.3.2Archivos de datos............................................................................................................................................................12 3.3.3Registros de transacción..................................................................................................................................................12 4TABLAS E INDICES....................................................................................................................................................................13 4.1Creación ................................................................................................................................................................................13 4.2Uso .........................................................................................................................................................................................13 ©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 3 / 23

4.3Opciones y impacto a nivel del rendimiento del sistema...................................................................................................13 4.3.1Tablas..............................................................................................................................................................................13 4.3.1.1Tipos de datos de SQL Server Data Types..............................................................................................................13 4.3.2Indices.............................................................................................................................................................................14 4.3.3Estadísticas......................................................................................................................................................................14 5MANEJO DE USUSARIOS Y SEGURIDAD DEL SERVIDOR................................................................................................15 5.1Autentificar............................................................................................................................................................................15 5.2Usuarios .................................................................................................................................................................................15 5.3Grupos y roles........................................................................................................................................................................15 5.4Permisos.................................................................................................................................................................................15 5.4.1Permisos a nivel de sistema.............................................................................................................................................15 5.4.1.1Roles de sistema......................................................................................................................................................15 5.4.2Permisos a nivel de base de datos...................................................................................................................................15 5.4.2.1Roles de base de datos.............................................................................................................................................15 5.4.3Otros métodos de restricción de acceso..........................................................................................................................16 5.4.4Precedencia de derechos..................................................................................................................................................16 6RESPALDO Y REPOSICIÓN......................................................................................................................................................17 6.1Tipos de respaldos.................................................................................................................................................................17 6.1.1Respaldo completo..........................................................................................................................................................17 6.1.2Respaldo diferencial........................................................................................................................................................17 6.1.3Log de transacciones.......................................................................................................................................................17 6.2Medios de grabación de respaldo........................................................................................................................................17 6.3Tipos de reposición................................................................................................................................................................17 6.4Planes de contigencia............................................................................................................................................................17 6.4.1Plan de respaldo..............................................................................................................................................................17 6.4.2Retención de datos de respaldo.......................................................................................................................................18 7AUTOMATIZACIÓN DE TAREAS EN EL SERVIDOR...........................................................................................................19 7.1Operadores............................................................................................................................................................................19 7.2Tareas.....................................................................................................................................................................................19 7.3Alertas ...................................................................................................................................................................................19 7.4Planes de mantenimientos....................................................................................................................................................19 7.5SQL Mail................................................................................................................................................................................19 8SINTONIZACIÓN FÍSICA DE LA BASE DE DATOS..............................................................................................................20 8.1Monitoreo del disco...............................................................................................................................................................20 8.2Monitoreo de la memoria.....................................................................................................................................................20 8.3Monitoreo del CPU...............................................................................................................................................................20 8.4Fragmentación de los archivos.............................................................................................................................................20 9SINTONIZACIÓN LÓGICA DE LA BASE DE DATOS............................................................................................................21 9.1Monitoreo de la carga y de las consultas SQL....................................................................................................................21 9.2Sintonización de consultas SQL...........................................................................................................................................21 9.3Diseño lógico normalizado....................................................................................................................................................21 9.4Indices....................................................................................................................................................................................21 9.5Procedimientos......................................................................................................................................................................21 9.6Vistas......................................................................................................................................................................................21 10SINTONIZACIÓN EXTERNA A LA BASE DE DATOS.........................................................................................................22 10.1Servidor compartido...........................................................................................................................................................22 ©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 4 / 23

10.2Red........................................................................................................................................................................................22 11Ejercicios......................................................................................................................................................................................23

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 5 / 23

1 Introducción al curso 1.1 Objetivo de este curso En este curso vamos a ver como administrar y sintonizar Microsoft SQL Server. Este curso no cubre todos los aspectos ni todas las opciones de administración, enfocando en las más comunes. El foco está más puesto al nivel de sintonización de un servidor SQL.

1.2 Manual del alumno Este manual del alumno es una ayuda para el alumno, para tenga un recuerdo del curso. Este manual contiene un resumen de las materias que se van a estudiar durante el curso, pero el alumno debería de tomar notas personales para completas este manual.

1.3 Ejercicios prácticos Para demostrar y captar mejor la teoría, se harán muchos ejercicios con los alumnos.

1.4 Requisitos para atender a este curso Se requiere un conocimiento de base del lenguaje SQL. Si el alumno tiene dificultades en un u otro capitulo, el debe sentirse libre de pedir explicaciones adicionales al profesor.

1.5 Soporte después del curso Si tienes preguntas sobre la materia del curso en tus ejercicios prácticos, puedes escribir tus preguntas a [email protected]. Para informaciones sobre otros cursos, visita el sitio web www.solucionjava.com.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 6 / 23

2 Instalación del servidor 2.1 Planificación La planificación permite escoger las opciones correctas al momento de correr el instalador de la base de datos. 2.1.1

Requisitos materiales

Los requisitos materiales dependen de la versión exacta de SQL Server que quieres instalar. 2.1.1.1

Para SQL Server 2000:

Recurso

Requerimiento

Computadora

Intel or compatible

CPU

Pentium 166 o arriba

Entorno gráfico

800x600 para usar la herramientas gráficas 640 x 480 si no se usan las herramientas gráficas

Dispositivo de puntero

Ratón Microsoft o compatible

Tarjeta de red

Opcional (pero requerido para acceso via la red)

CD-ROM

Requerido si se usa el CD para instalar (si no, opcional)

Tambien se requiere que Internet Explorer 5 o arriba este disponible. 2.1.1.2

Para SQL Server 2005:

Recurso

Requerimiento

Computadora

Intel or compatible. 32 o 64 bits

CPU

Procesador de 500 megahertz (MHz) o superior (se recomienda 1 gigahertz o superior)

Entorno gráfico

800x600 para usar la herramientas gráficas 640 x 480 si no se usan las herramientas gráficas

Dispositivo de puntero

Ratón Microsoft o compatible

Tarjeta de red

Opcional (pero requerido para acceso via la red)

CD-ROM

Requerido si se usa el CD para instalar (si no, opcional)

2.1.1.3

Para SQL Server 2008:

Recurso

Requerimiento

Computadora

Pentium II o arriba

CPU

Procesador de 1 gigahertz (GHz) o superior (se recomienda 2 gigahertz o superior)

Entorno gráfico

1024 x 768 para usar la herramientas gráficas 640 x 480 si no se usan las herramientas gráficas

Dispositivo de puntero

Ratón Microsoft o compatible

Tarjeta de red

Opcional (pero requerido para acceso via la red)

CD-ROM

Requerido si se usa el CD para instalar (si no, opcional)

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

2.1.2

Pagina 7 / 23

Requisitos de software

2.1.2.1 SQL Server 2000 SQL Server 2000 requiere que Internet Explorer 5 o arriba este disponible. 2.1.2.2 SQL Server 2005 El programa de instalación de SQL Server 2005 requiere Microsoft Windows Installer 3.1 o posterior y el SP1 de Microsoft Data Access Components (MDAC) 2.8 o posterior. Puede descargar SP1 de MDAC 2.8 de este sitio Web de Microsoft. Antes de instalar SQL Server 2005 Express Edition, debe descargar e instalar .NET Framework 2.0 Se requiere el SP1 de Microsoft Internet Explorer 6.0 o posterior para SQL Server 2005 Management Studio, Business Intelligence Development Studio y el componente Diseñador de informes de Reporting Services. 2.1.2.3 SQL Server 2008 El programa de instalación de SQL Server requiere Microsoft Windows Installer 3.1 o una versión posterior, y Microsoft Data Access Components (MDAC) 2.8 SP1 o una versión posterior. 2.1.3 2.1.3.1

Requisitos de sistema operativo SQL Server 2005

Versión de SQL Server

Versión de Windows

SQL Server 2005 Enterprise Edition y Standard Edition

Windows Server 2003, Standard Edition1 Windows Server 2003, Enterprise Edition2 Windows Server 2003, Datacenter Edition3 Windows Small Business Server 2003 Standard Edition Windows Small Business Server 2003 Premium Edition Windows 2000 Server* Windows 2000 Advanced Server* Windows 2000 Datacenter Server*

SQL Server 2005 Evaluation Edition y Workgroup Edition Los de antes indicados más: Windows XP Professional** Windows XP Media Edition** Windows XP Tablet Edition** Windows 2000 Professional* SQL Server 2005 Developer Edition y Express Edition

Los de antes indicados más: Windows XP Home Edition** Windows Server 2003 Web Edition4 (Express únicamente)

* requiere la aplicación de Windows 2000 SP4 o posterior ** requiere la aplicación de Windows XP SP2 o posterior Para Windows SQL Server 2005 64 bits: ver en http://www.microsoft.com/spain/sql/productinfo/sysreqs/default.mspx 2.1.3.2 SQL Server 2008 SQL Server 2008 32 bits se puede instalar bajos los OS siguientes: ● Windows Vista Ultimate Edition ● Windows Vista Home Premium Edition ● Windows Vista Home Basic Edition ● Windows Vista Enterprise Edition ● Windows Vista Business Edition ● Windows Vista 64-bit x64 Ultimate Edition3 ● Windows Vista 64-bit x64 Home Premium Edition3 ● Windows Vista 64-bit x64 Home Basic Edition3 ● Windows Vista 64-bit x64 Enterprise Edition3 ● Windows Vista 64-bit x64 Business Edition3 ● Windows Server 2008 Standard Edition RC0 o una versión posterior5 ● Windows Server 2008 Data Center Edition RC0 o una versión posterior5 ● Windows Server 2008 Enterprise Edition RC0 o una versión posterior5 ● Windows Server 2008 64-bit x64 Standard Edition RC0 o una versión posterior3,5 ©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server ● ● ● ● ● ● ● ● ●

Pagina 8 / 23

Windows Server 2008 64-bit x64 Data Center Edition RC0 o una versión posterior3,5 Windows Server 2008 64-bit x64 Enterprise Edition RC0 o una versión posterior3,5 Windows Server 2003 Service Pack 2 Windows Server 2003 Enterprise Edition Service Pack 2 Windows XP Professional Service Pack 2 Windows Server 2003 64-Bit x64 SP23 Windows Server 2003 64-Bit x64 Enterprise Edition SP23 Windows Server 2003 64-Bit Itanium SP23 Windows Server 2003 64-Bit Itanium Enterprise Edition SP23

Para Windows SQL Server 2008 64 bits: igual a la lsita de arriba menos las versiones que no son 64 bits. 2.1.4

Requisitos de memoria

Los requisitos de memoria dependen de la versión exacta de SQL Server que quieres instalar, de la versión de Windows que se usa para hospedar el servidor, y del uso que se le va a dar. 2.1.4.1 Memoria mínima recomendada con SQL Server 2000: Enterprise Edition Windows 2000 Server

Standard Edition

Developer Evaluation Edition Editions

256 MB (128 256 MB (128 256 MB (128 MB MB MB supported) supported) supported)

Personal and Desktop Engine Editions

256 MB (128 256 MB (128 MB MB supported) supported)

Windows NT 4.0 128 MB (64 Server con Service MB Pack 5 (SP5) o más supported)

64 MB

128 MB recommended (64 MB supported)

64 MB

32 MB

Windows 2000 Professional

N/A

N/A

128 MB recommended (64 MB supported)

64 MB

64 MB

Windows NT 4.0 Workstation, with SP5 or later

N/A

N/A

128 MB recommended (64 MB supported)

64 MB

32 MB

Windows ME

N/A

N/A

N/A

N/A

32 MB

Windows 98

N/A

N/A

N/A

N/A

32 MB

2.1.4.2 SQL Server 2005 32 bits Enterprise Edition: se recomienda 512 megabytes (MB) 1 gigabyte (GB) o más Standard Edition: 512 MB (se recomienda 1 GB o más) Workgroup Edition: 512 MB (se recomienda 1 GB o más; 3 GB como máximo) Evaluation Edition: 512 MB (se recomienda 1 GB o más) Developer Edition: 512 MB (se recomienda 1 GB o más) Express Edition: 512 MB (se recomienda 512 MB o más; 1 GB como máximo) Puede necesitarse memoria adicional, según los requisitos del sistema operativo. 2.1.4.3 SQL Server 2008 Se requiere un mínimo de 512 MB de memoria, pero se aconseja tener 1 GB o más disponible. 2.1.5

Requisitos de disco

Los requisitos de disco dependen de la versión exacta de SQL Server que quieres instalar, de la versión de Windows que se usa para hospedar el servidor, y del uso que se le va a dar. Hay que tener cuidado que el cache de escritura del controlador de disco que utiliza la base de datos este APAGADO, a menos que haya sido diseñado específicamente para manejar bases de datos. En otros casos, podría generar corrupción de la base de datos.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 9 / 23

2.1.5.1 SQL Server 2000 Requisitos mínimos para SQL Server 2000: Opción de instalación Espacio de disco duro necesario Servidor y clientes

95-270 MB depending on the installation options selected

Instalación típica

250 MB (178 MB on the system drive, plus 72 MB for program and data files)

Instalación Mínima

110 MB (73 MB on the system drive, plus 37 MB for program and data files)

Herramientas administrativas

113 MB (system drive only)

Libro en línea

30 MB (system drive only)

Servicios de análisis

47 MB minimum 120 MB typical

English Query

80 MB

Solo el motor de escritorio

44 MB

2.1.5.2 SQL Server 2005 SQL Server 2005 requiere: 350 MB de espacio disponible en el disco duro para la instalación completa. 390 MB para bases de datos de muestra. 2.1.6

Determinar las cuentas apropiadas para los servicios SQL Server y SQL Agent

Cada servicio de SQL Server representa a un proceso o conjunto de procesos para administrar la autenticación de las operaciones de SQL Server con Microsoft Windows. En este tema se presenta la configuración predeterminada de los servicios en esta versión de SQL Server, así como las opciones de configuración de los servicios de SQL Server que se pueden establecer durante la instalación de SQL Server. Durante la instalación se le pedirán bajo cual usuario deben de correr estos servicios. 2.1.7

Determinar el modo de autenticarse

Hay dos manera de autenticarse para conectarse a SQL Server: Modo de autentificar de Windows, que usa la cuenta de usuario de Windows, y el modo mixto, que ademas permite especificar un usuario/clave propios a SQL Server. El modo mixto es necesario, por ejemplo, si quieren desarrollar un sistema que se conecta a SQL Server con una conexión compartida, o desde un sistema que no fue autenticado en el dominio de Windows. 2.1.8

Intercalación: determinar el juego de caracteres

Este determina la codificación que será utilizada en la base de datos para los caracteres especiales (non Unicode). Este es especialmente importante si se usan alfabetos extranjeros, como el Ruso, el japones,... Por lo general, se escoje el juego de caracteres propuesto (SQL_Latin1_General...). La intercalación se define a nivel del servidor, y luego se puede sobrescribir a nivel de base de datos, y a nivel de campo de una tabla. La intercalación tiene tambien efecto sobre la ordenación de datos, y la comparación entre datos. 2.1.9

Seleccionar la librería de red

Se pueden usar diferentes librerías de red para la comunicación entre un cliente y el servidor. Por lo general se usa el TCP/IP, y la canalización de nombre (IPC).

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

2.1.10

Pagina 10 / 23

Decidir del modo de licencias clientes

Depende de si compraron licencias por cliente o por CPU.

2.2 Instalación Además de las opciones explicadas en el punto anterior, se puede seleccionar cual(es) componentes de SQL Server usted quiere instalar. La instalación es muy sencillas si usted ya tiene la planificación de todas las opciones clara (ver punto anterior).

2.3 Múltiples instancias Es posible de utilizar varias instancias (copias) de SQL Server en una misma computadora. Cada instancia de SQL Server actuará como un servidor aparte, sin compartir datos entre instancias. En la instalación, se le pide de utilizar la instancia por defecto, o una instancia nombrad. Solo puede haber una instancia por defecto, pero pueden haber cero a varias instancias nombradas. Hay que notar que usar múltiples instancias de SQL server aumenta la tarea de administración y aumenta la cantidad de recursos necesarios en el servidor. Por lo general una versión de SQL Server puede

2.4 Otras informaciones útiles Microsoft edita libros en pantalla con toda la información útil para planificar, instalar y manejar SQL Server 200/2005/2008.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 11 / 23

3 Creación de bases de datos 3.1 Bases de datos del sistema Son las bases de datos creadas al instalar SQL Server y que son necesarias para que pueda correr el servidor. 3.1.1

La base de datos master

Es la base de datos de configuración principal de SQL Server. Contiene la información sobre todas las bases de datos que existen en el servidor, incluido los archivos de bases de datos y sus ubicaciones. Contiene también los datos de configuración del servidor y las cuentas de usuarios. Debido a su importancia, un respaldo corriente de esta base de datos es crítico para que pueda arrancar el servidor despues de una restauración. En caso de pérdida de esta base de datos, solo podrá arrancar el servidor usando la versión original que viene en el CD de instalación y ligando las bases de datos de usuario usando una herramienta que se llama 'rebuildm.exe', que viene con el servidor. 3.1.2

La base de datos model

Es una base de datos de plantilla que SQL Serve utiliza para crear nuevas bases de datos. Cada vez que usted crea una nueva base de datos (incluso cuando el servidor inicia y (re)crea la base de datos tempdb), el contenido de model esta copiado hacia la nueva base de datos para establecer los objetos por defecto de la nueva base. 3.1.3

La base de datos msdb

Es al base de datos utilizada por diversos componentes de SQL Server, como el SQL Server Agent. Contiene también la información de trabajos programados, planes de mantenimientos, información de replicación y de envío de logs. 3.1.4

La base de datos tempdb

Esta base de datos se usa para guardar todos los objetos temporales del servidor. Tablas temporales, espacio de trabajo temporal,... Esta base de datos esta recreada a cada reinicio del servidor, con un tamaño inicial de 8 MB, y luego crece hasta el tamaño requerido por la aplicación. Para evitar este trabajo de crecimiento, se puede definir un tamaño inicial mas grande, cerca de lo que se estima necesario.

3.2 Bases de datos de usuarios Son las bases de datos que contienen los datos de su aplicación. Es ahí que se van a almacenar sus datos propios. Al instalar SQL Server se pueden instalar bases de datos de usuario de muestra, como Northwind o pubs, que pueden servir de prueba.

3.3 Configuración La configuración correcta de las bases de datos es crítica para un desempeño óptimo de su servidor. 3.3.1

Intercalación

Ver el punto anterior (planificación). Permite sobrescribir la intercalación definida a nivel de servidor para la nueva base de datos.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

3.3.2

Pagina 12 / 23

Archivos de datos

Permite definir cuales serán los archivos que se usarán para almacenar la información de su base de datos, y la ubicación de estos mismos. Lo ideal es de tener uno o varios archivos para almacenar las tabas y otro(s) alojados en un disco físicamente diferente para los índices. Cuando se definirá luego una tabla/un índice, se podrá especificar cual archivo(s) de datos usted quiere utilizar para almacenar esta información. 3.3.3

Registros de transacción

Permiten almacenar la historia de todas las transacciones, para poder manejar transacciones de varias consultas, deshacer (rollback) o rehacer (roll forward) transacciones incompletas, y para poder reponer una base de datos hasta un cierto punto en el pasado. Lo ideal es de tener esto archivos en un disco físicamente diferente de los archivos de datos.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 13 / 23

4 Tablas e indices 4.1 Creación Al crear una tabla,se definen los campos, la ubicación de los archivos, y opcionalmente la clave primaria, las claves secundarias, y los índices.

4.2 Uso Las tablas almacenan los datos, los indices mejoran la velocidad de búsqueda de datos en una tabla.

4.3 Opciones y impacto a nivel del rendimiento del sistema 4.3.1

Tablas

A nivel de la definición de campos, escoge el tamaño exacto para los datos esperados. Escoger un tamaño más grande va a ocupar más espacio, y entonces generar más actividad en el disco. Toma también en cuenta el tamaño máximo de una fila, que no debería ser más que el tamaño de un bloque (por lo general 8kb). Son importantes también el tamaño inicial y el tamaño de crecimiento del archivo de datos. No hay valores normales para estos parámetros ya que dependen de los datos a almacenar, y de la utilización y del crecimiento de ellos. 4.3.1.1 Tipos de datos de SQL Server Data Types Númericos exactos Type

From

To

bigint

-9,223,372,036,854,775,808

9,223,372,036,854,775,807

int

-2,147,483,648

2,147,483,647

smallint

-32,768

32,767

tinyint

0

255

bit

0

1

decimal

-10^38 +1

10^38 –1

numeric

-10^38 +1

10^38 –1

money

-922,337,203,685,477.5808

+922,337,203,685,477.5807

smallmoney

-214,748.3648

+214,748.3647

numeric and decimal are Fixed precision and scale data types and are functionally equivalent. Numericos aproximados Type

From

To

float

-1.79E + 308

1.79E + 308

real

-3.40E + 38

3.40E + 38

Fechas Type

From

To

datetime (3.33 milliseconds accuracy)

Jan 1, 1753

Dec 31, 9999

smalldatetime (1 minute accuracy)

Jan 1, 1900

Jun 6, 2079

Cadena de caracteres ©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 14 / 23

Type

Description

char

Fixed-length non-Unicode character data with a maximum length of 8,000 characters.

varchar

Variable-length non-Unicode data with a maximum of 8,000 characters.

varchar(max) Variable-length non-Unicode data with a maximum length of 231 characters (SQL Server 2005 only). text

Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Cadena de caracteres Unicode Type

Description

nchar

Fixed-length Unicode data with a maximum length of 4,000 characters.

nvarchar

Variable-length Unicode data with a maximum length of 4,000 characters.

nvarchar(max) Variable-length Unicode data with a maximum length of 230 characters (SQL Server 2005 only). ntext

Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

Cadenas Binarias Type

Description

binary

Fixed-length binary data with a maximum length of 8,000 bytes.

varbinary

Variable-length binary data with a maximum length of 8,000 bytes.

varbinary(max) Variable-length binary data with a maximum length of 231 bytes (SQL Server 2005 only). image

Variable-length binary data with a maximum length of 2,147,483,647 bytes.

Otros tipos: sql_variant: Stores values of various SQL Server-supported data types, except text, ntext, and timestamp. timestamp: Stores a database-wide unique number that gets updated every time a row gets updated. uniqueidentifier: Stores a globally unique identifier (GUID). xml: Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only). cursor: A reference to a cursor. table: Stores a result set for later processing. 4.3.2

Indices

Los índices aumentan mucho la velocidad de búsqueda, pero tienen también un costo de mantenimiento. Por eso, hay que escoger los índices correctos, que dependerán de las consultas que se harán sobre la tabla. Un índice agrupado (clustered) permite ordenar físicamente la tabla en este orden. Solo puede haber un indice agrupado por tabla. Por lo general se usa para la clave primaria. Valores únicos permite evitar valores duplicadas en un campo. Una tabla puede tener un máximo de 249 indices no agrupados. Un índice puede ser compuesta de varios campos (max 16). Entre más campos, más eficiente será la búsqueda que incluye dichos campos en filtro. El relleno define el espacio libre para modificaciones de valores en el índice. Un campo que nunca se modificará puede tener los índices rellenos. Una campo que se modificará (update) con frecuencia debería dejar un espacio libre para estas modificaciones usando un factor de relleno <100. 4.3.3

Estadísticas

Las estadísticas indican al servidor como se presentan los datos en las tablas y los índices (cuantas filas, columnas, valores únicas,...). Este permite al servidor decidir cual es según el el mejor camino para recuperar datos de una consulta: cuales indices usar, orden de consulta de las tablas,... Por eso, es muy importante mantener las estadísticas al día, después de cambios grandes en datos, o después de un periodo de actividad.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 15 / 23

5 Manejo de ususarios y seguridad del servidor 5.1 Autentificar La autentificación funciona recuperando el usuario autenticado por Windows (automático), o el usuario SQL (ingreso de usuario y clave).

5.2 Usuarios Los usuarios pueden ser usuarios Windows o usuarios SQL. Para que un usuario de Windows tenga acceso a SQL server, el debe estar definido (con derecho de acceso) a nivel de SQL server, o pertenecer a un grupo Windows que esta definido (con derecho de acceso) en SQL server.

5.3 Grupos y roles Un grupo es un conjunto de usuarios, a quien se pueden otorgar una serie de derechos de una sola vez. Un usuario puede pertenecer a varios grupos, y un grupo puede tener varios usuarios. Un grupo se maneja como un usuario autenticado por Windows. Existen también los roles que son conjuntos de derechos, que son detallados en el punto siguiente.

5.4 Permisos 5.4.1

Permisos a nivel de sistema

Son permisos generales válidos para todo el sistema (y todas la base de datos). 5.4.1.1 Roles de sistema Son conjunto de derechos definidos a nivel de sistema. El objetivo es poder otorgar de una sola vez todos los derechos necesarios (y nada más) para un cierto tipo de trabajo, como respaldar bases de datos, administrar los archivos en disco, crear nuevas bases de datos,... 5.4.2

Permisos a nivel de base de datos

Permiten definir para un usuario cuales derechos el tiene (o no) sobre los objetos de una base de datos, o parte de ellos. 5.4.2.1 Roles de base de datos Los roles son un conjunto de , como los grupos, A nivel de base de datos existen 3 tipos de roles: predefinidos, estándar, y de aplicación. Los roles predefinidos no se pueden cambiar. Los roles de base de datos estándar y de aplicación se pueden definir y modificar a su gusto. Un rol estándar otorga al usuario todos los derechos del rol al usuario al momento de conectarse. A la diferencia, un rol de aplicación necesita ser activado manualmente (o vía una aplicación), entrando una clave de acceso para activarlo. La clave se puede encontrar en el código, así que vía la aplicación un usuario podría tener acceso a datos que no tiene acceso usando su usuario conectándose directamente a SQL Server.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

5.4.3

Pagina 16 / 23

Otros métodos de restricción de acceso

Para restringir el acceso a los datos, además de definir derechos sobre tablas, se pueden usar vistas, procedimientos, y funciones para dar acceso solo a una parte de los datos, o a efectuar ciertas acciones de manera mejor controlada. 5.4.4

Precedencia de derechos

Todo los derechos de un usuario (personales, vía grupos, y vía roles) se agrega, menos los que son explicitamente desnegados. Un derecho explicitamente desnegado por cualquier manera (usuario, grupo, rol) no se puede sobrescribir otorgando el derecho via otro medio.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 17 / 23

6 Respaldo y reposición 6.1 Tipos de respaldos 6.1.1

Respaldo completo

El respaldo completo respalda todas las bases de datos. Este opción es la más segura, y la más fácil de reponer, pero es la que ocupa más tiempo y más espacio de respaldo. Un respaldo completo es siempre necesario como base a los otros tipos de respaldos. 6.1.2

Respaldo diferencial

Aquí se respaldan las diferencias entre el último respaldo comlpeto y el respaldo corriente. Es más rápido al principio que el respaldo completo pero si no se hacen respaldos completos regularmente, el tamaño del diferencial podría llegar a ser más grande que el respaldo completo. 6.1.3

Log de transacciones

Los log recuerdan todos los cambios hechos en la base de datos. Entonces los logs permiten rehacer todos los cambios entre el último respaldo completo/diferencial y el último log disponible. Los logs se respaldan con mucho más frecuencia que los otros tipos de respaldo (cada hora por ejemplo).

6.2 Medios de grabación de respaldo Los respaldos se pueden guardar en disco, en Tape, o en un PC remoto (conexión named pipe).

6.3 Tipos de reposición La reposición puede ser completa o hasta un cierto punto en el pasado, dependiendo de los archivos de respaldo disponible. Un respaldo parcial puede servir a refrescar un ambiente de prueba con una copia de producción (reposición de un respaldo completo), o reponer la producción hasta antes de una mala manipulación (respaldo completo + diferencial + logs).

6.4 Planes de contigencia 6.4.1

Plan de respaldo

La frecuencia de los respaldos depende de la actividad en la base de datos, y de la importancia de la pérdida de datos (cuanto tiempo se puede perder de datos?). Un ejemplo de plan de contingencia podría ser lo siguiente: 1) Cada domingo a media noche, respaldo completo de todas las bases de datos de usuario + master + msdb + logs. 2) Cada dia a media noche (excepto el domingo): respaldo completo de master + diferencial de las base de datos de usuario y msdb + logs 3) Cada 6 horas (menos media noche): respaldo diferencial de las bases de datos de usuarios + transaction logs 4) Cada hora: transacion logs Las bases de datos master y msdb son críticas para poder recuperar un sistema funcional ya que contienen informaciones como las bases de datos de usuarios disponibles, las cuentas de usuarios, la configuración del servidor, el histórico de los respaldos, los trabajos programados,... Después de crear una nueva base de datos o agregar o re ubicar archivos de una base de datos, hay que respalda ©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

6.4.2

Pagina 18 / 23

Retención de datos de respaldo

Depende de las necesidades del negocio y de las políticas de auditoria. Es importante también guardar regularmente copias de respaldo en un lugar lejano del servidor (otra sucursal, en caja en el banco,...) para sobrevivir un desastre local (incendio, terremoto,...). Un buen intermedio podría ser guardar: ● el primer respaldo completo (domingo) mensual durante 1 año, guardado fuera del sitio ● los dos últimos respaldos semanales, guardado fuera del sitio ● los 7 últimos respaldos diarios ● los 4 últimos respaldos de 6h ● los 24 últimos respaldos horario de logs Es también buen costumbre de mantener un libro donde se anotan todos los respaldos (diarios), con la fecha, la hora, y quien verifico que se hizo (o no) el respaldo esperado.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 19 / 23

7 Automatización de tareas en el servidor 7.1 Operadores Un operador es una cuenta que ha sido identificada para recibir emails o notificaciones. Los operadores son usualmente notificados solamente cuando dispara una alarma.

7.2 Tareas Una tarea es un conjunto de acciones a ejecutar. Las tareas se pueden programar en el tiempo para ejecutarse sin presencia física de alguien.

7.3 Alertas Son acciones que se ejecutan cuando se produce un cierto evento. Usualmente se usan para monitorear errores y tomar acciones específicas para este error.

7.4 Planes de mantenimientos Son conjuntos de acciones que se pueden programar en el tiempo, con el objetivo de darle mantenimiento al servidor, verificado integridad de base de datos, haciendo respaldo, etc... Son como tareas que se pueden programar con un interface gráfico pre diseñado.

7.5 SQL Mail Es el componente que permite a SQL Server enviar emails a los operadores para notificarles. Funciona con una conexión MAPI, usando por ejemplo una cuenta de usuario en Microsoft Exchange.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 20 / 23

8 Sintonización física de la base de datos El monitoreo de los aspectos físicos se hace usando herramientas a nivel del sistema operativo. Un monitor rápido para ver el uso corriente de recursos en el sistema es el administrador de tareas de Windows. El otro monitor que provee Windows es el monitor de performancias, que se llama teclando “perfmon” desde la línea de comando. Ver detalles en: http://www.microsoft.com/technet/prodtechnol/Windows2000Pro/reskit/part6/proch30.mspx?mfr=true

8.1 Monitoreo del disco El disco es casi siempre el punto crítico de un servidor de base de datos. Si se pueden agregar fácilmente memoria, o multiplicar los CPU's disponibles, los discos están limitados por su numero de revoluciones por minutos (RMP). Lo común es 7,200 RMP, los discos de velocidad superior (pero más caros) tienen 10,000 o 15,000 RPM. Se pueden usar sistema RAID para mejorar el rendimiento y dar tolerancia a fallos, pero no es suficiente si el resto del diseño no está enfocado en disminuir y repartir al máximo el uso de los discos. Para disminuir el uso de los discos, hay que escribir consultas lo más especificas posible (filtrando) y asegurarse que los índices se usan siempre cuando es necesario. Se puede también guardar datos frecuentes en memoria a nivel del cliente o de la aplicación, dependiendo del sistema que se conecta al servidor. Para repartir el uso de los discos, hay que tratar de que las tablas, los indices, los logs, y el sistema operativo tengan sus archivos en discos físicamente diferente. Eventualmente, las bases de datos o tablas más utilizadas se pueden repartir también en discos diferentes.

8.2 Monitoreo de la memoria SQL Serve usa memoria para mejorar el rendimiento y en tiempo de respuesta. Aumentando la memoria es una manera fácil y rápida de mejorar el rendimiento, hasta un cierto punto.

8.3 Monitoreo del CPU SQL Server tiene la posibilidad de utilizar más de 1 CPU para su trabajo. Es posible definir a nivel del servidor (si tiene más de 1 CPU) cuantos CPU se pueden utilizar para la base d datos.

8.4 Fragmentación de los archivos SQL Server permite detectar fragmentación de indices usando el comando : DBCC SHOWCONTIG (nombre_de_tabla) WITH ALL_INDEXES Para defragmentar indices, existen dos comandos: DBCC INDEXDEFRAG (base_de_datos, tabla, indice) y DBCC REINDEX (tabla, indice). La diferencia entre los dos es que REINDEX agara un bloqueo, y por eso es más rápido. DBCC REINDEX (tabla) reconstruye todos los indices de una tabla. Ver en la documentación de SQL Server los otros comando de DBCC que pueden ayudar en el mantenimiento del servidor.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 21 / 23

9 Sintonización lógica de la base de datos 9.1 Monitoreo de la carga y de las consultas SQL SQL Server tiene una herramienta que se llama Analizador (Profiler) que permite registrar las consultas SQL que se hacen a nivel de un servidor. Para evitar de monitorear todas las consultas se pueden usar filtros de consultas. La analisis del archivo generdo permitirá detectar consultas SQL que afectan el rendimiento.

9.2 Sintonización de consultas SQL Para sintonizar una consulta, hay que pasarla por el analizador de consulta y asegurarse que SQL Sever escoje el mejor camino para recuperar los datos, y usa los indices adecuados. Si faltan indices, hay que ver si es necesario mandarlos a crear.

9.3 Diseño lógico normalizado Es muy importante tener un diseño de base de datos normalizado en 3ra forma normal... con las desnormalizaciones necesarias... ;-) Si por lo general se ganan en rendimiento normalizando el esquema de la base de datos, en ciertos casos muy específicos, puede ser necesario desnormalizar una parte del esquema repitiendo un campo en diferentes tablas.

9.4 Indices Los índices permiten aumentar fantásticamente la velocidad de búsqueda de datos, pero tiene un pequeño costo a nivel de escritura de datos, ya que hay que mantener el índice ademas de las tablas. Teniendo los índices ubicados en un disco físicamente diferente de los datos ayuda en aumentar la velocidad de búsqueda como la velocidad de escritura, ya que reparte los IO en discos diferentes. SQL Server viene con una herramienta de analisis de ínidces, que se baza un archivo de traza del analizador, y hace recomendaciones a nivel de indices.

9.5 Procedimientos Los procedimientos pueden ayudar en mejorar el rendimiento ya que se ejecutan localmente y en lenguaje 'nativo' del servidor. Obligar el uso de procedimiento para recuperar datos permite evitar llamadas descontroladas al servidor.

9.6 Vistas El uso de vistas para recuperar datos permite limitar llamadas descontroladas al servidor gracias a la consulta 'preparada' y ojala 'optimizada' de la vista, agregando todas o parte de datos de una o varias tablas ligadas.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 22 / 23

10 Sintonización externa a la base de datos 10.1 Servidor compartido Si el servidor en el cual está hospedado la base de datos también hospeda otros tipos de aplicaciones, y/o sirve con otros fines (servidor de archivos, de mail, ...) hay que investigar el uso de los recursos físicos y averiguar cuales componentes producen el exceso de uso, ya que podría ser otro componente que la SQL Server que produce bajo rendimiento.

10.2 Red Debido a que los clientes se conectan vía la red, un sobrecargo de uso de la red puede producir bajo rendimiento a nivel de los clientes. Lo mismo si se conectan vía Internet. Una manera fácil de sospechar un problema de red es de abrir una conexión en el servidor mismo y compara el rendimiento en el servidor con el rendimiento desde un cliente vía la red.

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida

Curso de administración y sintonización de Microsoft SQL Server

Pagina 23 / 23

11 Ejercicios 1) Comprobar diferencias en tiempo de ejecución entre SELECT no_lab_test FROM [curso].[dbo]. [mtm_lab_presc2lab_test] y SELECT * FROM [curso].[dbo].[mtm_lab_presc2lab_test] 2) Comprobar diferencias en tiempo de ejecución entre el uso de un claúsula Where y sin 3) Comprobar diferencias en tiempo de ejecución entre el uso de un claúsula Where usando un índice y sin 4) Comprobar diferencias en tiempo de ejecución entre la importación de una tabla grande (copia de mtm_lab_presc2lab_test) en una tabla hospedada en un grupo de archivo con incremento de 10% y otra con un incremento de 50 MB 5) Comprobar diferencias en tiempo de ejecución entre la importación de una tabla grande (copia de mtm_lab_presc2lab_test) en una tabla sin índice, y una tabla con muchos índices 6) Comprobar diferencias en tiempo de ejecución entre un update sobre una tabla grande (copia de mtm_lab_presc2lab_test) que tiene un ínidice sobre el campo modificado, y cuando no tiene. 7) Probar todos los tipo de consulta SQL que le vienen a la mente, y analyza en plan de ejecución generado por SQL Server para cada consulta 8) Corre las estadisticas sobre la base de datos. 9) Usar los diferentes comandos de DBCC. 10) Probar la herramienta de analisis de ínidces 11) Usar el monitor de performacias de Windows, y el administrador de tareas. 12) Prueba los niveles de seguridad , usando los derechos a nivel de usuario, grupo, y rol. Verifica los que pasa cuando se desnego un derecho en algun nivel de seguridad. 13) Haga respaldo de la base de datos. Luego trata de reponerla hasta un cierto punto en el pasado. 14) Diseña un plan de respaldo en SQL Server y automatizalo 15) Abre el adminstrador de tareas y el monitor de performancias de Windows, y avergua lso que permiten ver. 16) Diseña una base de datos que respecta al máximo la 3ra forma normal, y pueda contener la información siguiente ● Datos del cliente (nombre, apellido, tel, domicilio, etc... ● Productos más comprados por el cliente (productos preferidos del cliente) ● Lista de productos ● Invemtario ● Ordenes de compras ● Facturas de las compras

©Copyright Cédric Simon, 2008

Versión 1.0

Reproducción prohibida