47
7 ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 USO DE ÍNDICES______________________________________________________ 246 ¿QUÉ ES UN ÍNDICE?_________________________________________________ 246 CONCEPTOS DE INDEXACIÓN_________________________________________ 249 CREACIÓN DE ÍNDICES_______________________________________________ 254 USO DEL FACTOR DE RELLENO PARA EVITAR DIVISIONES DE PÁGINA___ 255 RECONSTRUCCIÓN DE LOS ÍNDICES___________________________________ 257 CONSEJOS SOBRE EL USO DE ÍNDICES_________________________________ 258 USO DE VISTAS_______________________________________________________ 259 ¿QUÉ ES UNA VISTA?_________________________________________________ 259 CONCEPTOS DE VISTAS_______________________________________________ 259 CREACIÓN DE VISTAS________________________________________________ 261 MODIFICACIÓN Y ELIMINACIÓN DE VISTAS___________________________ 267 SEGURIDAD Y USUARIOS______________________________________________ 269 CREACIÓN Y ADMINISTRACIÓN DE INICIOS DE SESIÓN DE USUARIO____ 269 CREACIÓN DE USUARIOS DE SQL SERVER_____________________________ 276 ADMINISTRACIÓN DE PERMISOS DE BASES DE DATOS__________________ 277 ADMINISTRACIÓN DE FUNCIONES DE BASES DE DATOS_________________ 283 TRANSACCIONES Y BLOQUEOS________________________________________ 288 TRANSACCIONES____________________________________________________ 288 BLOQUEO DE TRANSACCIONES_______________________________________ 295 ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm 1 de 47 24/05/2011 11:50 p.m.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Embed Size (px)

Citation preview

Page 1: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

7 ADMINISTRACIÓN AVANZADADE MS SQL SERVER 2000

USO DE ÍNDICES______________________________________________________ 246

¿QUÉ ES UN ÍNDICE?_________________________________________________ 246

CONCEPTOS DE INDEXACIÓN_________________________________________ 249

CREACIÓN DE ÍNDICES_______________________________________________ 254

USO DEL FACTOR DE RELLENO PARA EVITAR DIVISIONES DE PÁGINA___ 255

RECONSTRUCCIÓN DE LOS ÍNDICES___________________________________ 257

CONSEJOS SOBRE EL USO DE ÍNDICES_________________________________ 258

USO DE VISTAS_______________________________________________________ 259¿QUÉ ES UNA VISTA?_________________________________________________ 259

CONCEPTOS DE VISTAS_______________________________________________ 259

CREACIÓN DE VISTAS________________________________________________ 261

MODIFICACIÓN Y ELIMINACIÓN DE VISTAS___________________________ 267

SEGURIDAD Y USUARIOS______________________________________________ 269CREACIÓN Y ADMINISTRACIÓN DE INICIOS DE SESIÓN DE USUARIO____ 269

CREACIÓN DE USUARIOS DE SQL SERVER_____________________________ 276

ADMINISTRACIÓN DE PERMISOS DE BASES DE DATOS__________________ 277

ADMINISTRACIÓN DE FUNCIONES DE BASES DE DATOS_________________ 283

TRANSACCIONES Y BLOQUEOS________________________________________ 288TRANSACCIONES____________________________________________________ 288

BLOQUEO DE TRANSACCIONES_______________________________________ 295

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

1 de 47 24/05/2011 11:50 p.m.

Page 2: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

USO DE ÍNDICES ¿QUÉ ES UN ÍNDICE?

Los índices son una de las herramientas disponibles más potentes para el diseñador de basesde datos. Un índice es una estructura auxiliar que permite mejorar el rendimiento de las consultasmediante la reducción de la actividad de entrada/salida necesaria para recuperar los datossolicitados, es decir, un índice permite a Microsoft SQL Server 2000 localizar los datos mediante eluso de una menor cantidad de operaciones de entrada/salida que las que se necesitarían para buscarlos datos accediendo solamente a la tabla de la base de datos. Cuando se utiliza un índice de unatabla de la base de datos para buscar una fila de datos, SQL Server puede determinar rápidamentedónde se almacenan los datos y recuperarlos inmediatamente. Así, los índices de tabla de la base dedatos son muy parecidos a los índices de los libros (ambos proporcionan un acceso rápido a grandescantidades de información). En este capítulo se van a aprender los conceptos básicos de los índices,incluyendo cómo crear un índice y los tipos de índices disponibles en SQL Server. También se va aaprender cuándo utilizar los índices y cuándo no hay que utilizarlos, ya que el uso de los índices noes útil siempre (en algunos casos pueden disminuir el rendimiento).

Como ya se ha mencionado, un índice es una estructura de datos auxiliar que usa SQL Serverpara acceder a los datos. Dependiendo del tipo, un índice se almacena con los datos o separado deellos. Independientemente del tipo, todos los índices trabajan de la misma forma básica.

En los sistemas sin índices, todos las recuperaciones de datos se deben realizar mediante laexploración de las tablas. En un recorrido de tabla, todos los datos de la tabla se deben leer ycomparar con los datos seleccionados. Los recorridos de tabla normalmente se deben evitar, debidoa la cantidad de operaciones de entrada/salida que se generan con esta operación (la exploración detablas grandes puede llevar gran cantidad de tiempo y consumir muchos recursos del sistema).Mediante el uso de un índice, se puede reducir en gran medida el número de operaciones deentrada/salida, acelerando el acceso a los datos, así como liberando recursos del sistema para otras

Un índice de la base de datos se organiza en una estructura de árbol B. Cada página deÍndice se denomina una página índice o un nodo índice. La estructura de índices comienza con unnodo en el nivel superior. El nodo raíz marca el inicio del índice; es el primer dato al que se accedecuando se produce una búsqueda de datos. El nodo raíz contiene una cantidad de filas índice. Estasfilas índice contienen un valor clave y un puntero a una página de índice (denominado nodo rama)como se muestra en la figura siguiente. Esta configuración es necesaria debido a que en una tabla dedatos de tamaño medio, un índice consta de miles o millones de páginas índice. Comenzando por elnodo raíz y atravesando los nodos rama, SQL Server puede acercarse a los dalos que se desean.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

2 de 47 24/05/2011 11:50 p.m.

Page 3: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Usando como analogía un libro, un índice trabaja de forma parecida a lo siguiente: se suponeque el índice comienza en una página que lista los números de las páginas en donde las entradascomienzan por «a», «b», «c», y así consecutivamente. Después se supone que estas páginascontienen los números de páginas para las entradas en los rangos aa-ab, ac-ad, ae-af, etc., y esaspáginas contienen punteros a las entradas de los rangos aaa-aab, aac-aad, aae-aaf, y asísucesivamente. Con esta ordenación, se puede encontrar lo que se está buscando rápidamente, conrelativamente pocas búsquedas. Esto es parecido a un índice de tabla de una base de datos, siendo laprimera página el nodo raíz.

Al igual que el nodo raíz, cada nodo rama contienen un número de filas índice guardadas en unapágina índice. Cada fila índice apunta a otro nodo rama o a un nodo hoja, como se muestra en lafigura siguiente. Los nodos hoja forman el último nivel de un índice. A diferencia del nodo raíz, cadanodo rama también tienen una lista enlazada a otros nodos rama del mismo nivel. En otras palabras,los nodos conocen los nodos adyacentes, así como también conocen los nodos inferiores.

Como implica el nombre «árbol B», los nodos rama se expanden desde el nodo raíz en formade árbol. Cada grupo de nodos rama del mismo nivel de la estructura de árbol se conoce como unnivel de índices, como se muestra en la figura siguiente. El número de operaciones de entrada/salidaque se necesitan para alcanzar los nodos hoja (los nodos del nivel más bajo del árbol) depende delnúmero de niveles de índices. Si la tabla de la base de datos sólo contiene una cantidad pequeña dedatos, el nodo raíz puede apuntar directamente a los nodos hoja, por lo que el índice no tiene quecontener ningún nodo rama (una situación improbable).

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

3 de 47 24/05/2011 11:50 p.m.

Page 4: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

identificador de fila apuntando a la fila deseada de la tabla, o bien la clave de índice agrupado sitambién existe un índice agrupado en la tabla. Y en un índice agrupado el mismo dato está en elnodo hoja. El número de filas de un nodo hoja depende del tamaño de las entradas de índices y, enel caso de un índice agrupado, del tamaño de los datos. Nota: Un identificador de fila es un puntero que construye automáticamente SQL Server a partir delidentificador de archivo, el número de página y el número de la fila de datos. Con el identificador de la fila, se pueden recuperar los datos con únicamente una operación de entrada/salida adicional. Debido aque se conoce qué página hay que recuperar y SQL Server conoce dónde está esa página, la página se leeen memoria con una única solicitud de entrada/salida La simplicidad de este proceso es la razón por la quelos índices son tan eficientes a la hora de recuperar los datos y de proporcionar tan gran mejora en elrendimiento.

Hay que tener en cuenta que, debido a que el índice se crea ordenado, cualquier cambio delos datos puede implicar una sobrecarga adicional. Por ejemplo, si una inserción implica la inserciónde una nueva fila índice en un nodo hoja que está lleno, SQL Server debe hacer sitio para la nuevafila. Esto lo hace moviendo aproximadamente la mitad de las filas del nodo hoja a otra página. Estemovimiento de datos se denomina división de página. Una división de página en un nivel del árbolpuede implicar divisiones en cascada en el árbol. Las divisiones de página se pueden evitar medianteun ajuste cuidadoso del factor de llenado.

CONCEPTOS DE INDEXACIÓN

Ahora que se tiene un conocimiento básico de la estructura de los índices, se van a repasaralgunos de los conceptos de INDEXACIÓN más generales. En esta sección se va a aprender acercade las claves de los índices, la unicidad de los índices y los tipos de índices. Claves de los índices

Una clave de índice designa la columna o columnas que se utilizan para generar el índice. Laclave del índice es el valor que permite buscar rápidamente la fila que contiene los datos que seestán buscando, de igual forma a como una entrada del índice en un libro apunta a un tema concretodel texto. Para acceder a la fila de datos usando un índice, se debe incluir el valor o valores de 1aclave del índice en la cláusula WHERE de la instrucción SQL. Cómo se logra depende de sí el índicees un índice simple o un índice compuesto. Índices simples

Un índice simple es un índice que se define en una sola columna de la tabla. Se debe hacerreferencia a esta columna en la cláusula WHERE de la instrucción para que se use este índice paracumplir la instrucción.

Un índice simple puede ser efectivo dependiendo del tipo de datos que se están ordenando,

del número de elementos únicos de la columna y del tipo de instrucciones SQL que se usen. En otroscasos se necesita un índice compuesto. Por ejemplo, si se está indexando una libreta de direccionescon miles de nombres y direcciones, la columna state no es una buena candidata para un índice

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

4 de 47 24/05/2011 11:50 p.m.

Page 5: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Índices compuestos

Un índice compuesto es un índice que se define en más de una columna. Se puede acceder aun índice compuesto usando una o más claves de índice. Con SQL Server 2000, un índice puedeabarcar hasta 16 columnas y sus columnas clave pueden ser de hasta 900 bytes.

Para las consultas que formen parte de un índice compuesto, no es necesario que se incluyan

todas las claves de índice en la cláusula WHERE de una instrucción SQL, pero es sensato utilizarmás de una de ellas. Por ejemplo, si se crea un índice en las columnas a, b y c de una tabla, se puedeacceder al índice usando una instrucción SELECT que contenga (a AND b AND c) o (a AND b) o a. Por supuesto, el uso de una cláusula WHERE más restrictiva, como la que contiene a AND bAND c, proporciona un mejor rendimiento. Recuperará menos filas de la base de datos ya queidentifica una fila más específicamente. Si se usa a AND b o solamente a, sólo se iniciará unrecorrido del índice.

Un recorrido de índice ocurre debido a que más de una entrada de índice satisface la

búsqueda. En un recorrido de índice, los nodos de un índice se exploran para recuperar vanosregistros de datos. Además, el índice incluye parcialmente el valor que se ha seleccionado. Porejemplo si el índice se ha creado para las columnas a, b y c, y la consulta solamente especifica lacolumna a, se devolverán todas las filas que satisfagan ese valor de a, para todos los valores de b yc.

Debido a que las columnas en que se basa un índice están ordenadas numéricamente, el

optimizador de consultas de SQL Server puede determinar el rango de páginas índices que puedencontener los datos deseados. Una vez que se conocen las páginas de comienzo y fin, se recuperantodas las páginas que contienen los valores de datos y se exploran los datos demandados. Nota: El índice solamente se puede utilizar si al menos una de las claves del índice están en la cláusulaWHERE de la consulta SQL. Continuando con el ejemplo anterior, una consulta con solamente un nombre oun número de teléfono en la cláusula WHERE no usa el índice. En la mayoría de los casos un recorrido deíndice puede ser bastante eficiente; sin embargo, si se accede a más del 20 por ciento de las filas de latabla, es más eficiente un recorrido de tabla, en la cual se leen todas las filas de la tabla. La eficiencia delas consultas que usan un índice depende de cómo su utilice el índice y en la unicidad del índice. Unicidad de los índices

En SQL Server se puede definir un índice como único o no único. En un índice único, cadavalor de la clave del índice debe ser único. Un índice no único permite que las claves del índiceestén duplicadas en los datos de la tabla. La efectividad, o eficiencia, de un índice no único dependede la selectividad del índice. Índices únicos

Un índice único contiene solamente una fila de datos por cada clave del índice (en otraspalabras, los valores de la clave del índice no pueden aparecer en el índice más de una vez). Losíndices únicos trabajan bien debido a que garantizan que solamente se necesita una operación deentrada/salida más para recuperar los datos solicitados. SQL Server obliga a que se cumpla la

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

5 de 47 24/05/2011 11:50 p.m.

Page 6: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

una restricción PRIMARY KEY o una restricción UNIQUE en la tabla. Un índice se puede hacer único solamente si los datos son únicos. Si los datos de la columna

no contienen valores únicos, todavía se puede crear un índice único usando un índice compuesto.Por ejemplo, la columna last name podría no ser única, pero combinando los datos de esta columnacon las columnas first name y middle name, se podría crear un índice único en la tabla. Nota: Si se intenta insertar una fila en una tabla que cree un valor duplicado de la clave de índice de uníndice único, la inserción fallará. Índices no únicos

Un índice no único trabaja de la misma forma que un índice único, salvo que puede contenervalores duplicados en los nodos hoja. Todos los valores duplicados se recuperan si cumplen loscriterios especificados en la instrucción SELECT.

Un índice no único no es tan eficiente como un índice único debido a que necesita

procesamiento adicional (operaciones adicionales de entrada/salida) para recuperar los datosrequeridos. Pero debido a que algunas aplicaciones necesitan utilizar claves duplicadas, algunasveces es imposible crear un índice único. En estos casos, un índice no único frecuentemente esmejor que no tener ningún índice. Tipos de índices

Hay dos tipos de índices de árbol B: índices agrupados e índices no agrupados. Un índiceagrupado almacena las filas de datos reales en sus nodos hoja. Un índice no agrupado es unaestructura auxiliar que apunta a los datos en una tabla. En esta sección, se verán las diferenciasentre estos dos tipos de índices. Índices agrupados

Como se ha mencionado, un índice agrupado es un índice de árbol B que almacena los datosde la fila actual de la tabla en sus nodos hoja, de forma ordenada, como se muestra en la figurasiguiente. Este sistema ofrece algunas ventajas y algunas desventajas.

Debido a que los datos de un índice agrupado se almacenan en los nodos hoja, una vez llegan

al nodo hoja, los datos están disponibles, lo que puede resultar en un número menor de operaciones de entrada/salida. Cualquier reducción de estas operaciones produce un mayor rendimiento, para lasoperaciones individuales y un mejor rendimiento medio para el sistema.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

6 de 47 24/05/2011 11:50 p.m.

Page 7: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

orden del índice. Por ejemplo, si se crea un índice agrupado a partir de las columnas state, country,y city y una consulta que selecciona todos los valores para los que state es Texas, la salida resultanteestá ordenada según country y city en el orden en que se haya definido en el índice. Estacaracterística se puede utilizar para evitar operaciones innecesarias de ordenación si la base de datosy la aplicación se han diseñado cuidadosamente. Por ejemplo, si se sabe que siempre va a sernecesario ordenar los datos de un cierta forma, el uso de un índice agrupado significa que no se va atener que realizar la ordenación una vez se hayan recuperado los datos.

Una desventaja del uso de un índice agrupado es que el acceso a la tabla siempre es a través

del índice, lo que puede provocar una sobrecarga adicional. SQL Server comienza el acceso a losdatos en el nodo raíz y atraviesa el índice hasta que llega al nodo hoja que contiene los datos. Si secrean muchos nodos hoja debido al volumen de datos, el número de niveles de índices necesariospara soportar esta cantidad de nodos hoja también es grande, lo que se necesitan son másoperaciones de entrada/salida para que SQL Server vaya desde el nodo raíz hasta el nodo hoja.

Debido a que los datos reales se almacenan en el índice agrupado, no se puede crear más de

un índice agrupado en una tabla. Por otra parte, se pueden crear índices no agrupados sobre unatabla agrupada (una tabla agrupada es sencillamente una tabla que tiene un índice agrupado). Elíndice agrupado se debería crear usando las claves de índices a las que se accede másfrecuentemente (esto proporciona una mayor probabilidad de acceder a los datos a través del índiceagrupado y por ello se mejora el rendimiento.) Índices no agrupados

A diferencia de los índices agrupados, los índices no agrupados no contienen los datos realesde la tabla en sus nodos hoja. Los nodos hoja pueden contener uno o dos tipos de información deubicación de filas de datos. En primer lugar, si no hay índices agrupados en la tabla, los índices noagrupados de esa tabla almacenan los identificadores de filas en sus nodos hoja, como se puede veren la figura siguiente. Cada identificador de fila apunta a la fila de datos real de la tabla. Elidentificador de fila está formado por un valor que incluye el ID del archivo de datos, el número depágina y la fila de la página. Este valor permite un acceso rápido a los datos reales señalandoexactamente dónde están almacenados los datos.

Si existe un índice agrupado en la tabla, los índices no agrupados contendrán en los nodos

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

7 de 47 24/05/2011 11:50 p.m.

Page 8: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Cuando se alcanza el nodo hoja del índice no agrupado, se usa el valor de la clave agrupada

encontrada en el nodo para buscar en el índice agrupado, donde se encontrará en su nodo hoja la filade datos.

Como ya se ha mencionado, solamente se puede tener un índice agrupado por tabla. Se

pueden crear 249 índices no agrupados por tabla, pero esto no es deseable. Es frecuente tener variosíndices no agrupados en varias columnas de la tabla. El predicado de la cláusula WHERE lo usa eloptimizador de consultas para determinar qué índice se utiliza. CREACIÓN DE ÍNDICES

La creación de índices no es difícil. Se pueden crear índices agrupados o no agrupados casidel mismo modo usando los asistentes que suministra el Administrador corporativo o mediante elcomando CREATE INDEX de SQL. En esta sección, se mostrará cómo crear índices y se mostraráel uso del factor de relleno.

Con el uso de Transact-SQL (T-SQL) para la creación de un índice, se puede hacer unasecuencia de comandos y ejecutarlo una y otra vez. También se puede modificar la secuencia decomandos de creación de índices para realizar otros índices. Además, este método de creación deíndices ofrece mayor flexibilidad debido a que hay más parámetros disponibles. Para utilizar estatécnica, simplemente se escriben los comandos T-SQL en un archivo y se lee este en el OSQLusando la mayor sintaxis:

Osql -U nombre_de_usuario -P contraseña create_index.sql

Este comando asume que el archivo creado se denomina create_index.sql. También se puedeejecutar la secuencia de comandos usando el Analizador de consultas. Para crear un índice usandoT-SQL se debe utilizar el comando CREATE INDEX. La sintaxis de este comando CREATEINDEX se muestra a continuación:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]INDEX nombre_del_índice ON nombre_de_la_tabla(nombre_de_columna [,nombre_de_columna ,nombre_de_columna ,...])

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

8 de 47 24/05/2011 11:50 p.m.

Page 9: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

como no único, como agrupado o no agrupado, con una o más columnas y con los parámetrosopcionales que se listan en la siguiente tabla. También se puede especificar, opcionalmente, ungrupo de archivos en el que emplazar el índice.

Parámetro Descripción

PAD_INDEX Cuando se usa junto con el parámetro FILL_FACTOR, indica qué espacio se debedejar en los nodos rama, en vez de dejarlo en los nodos hoja.

FILL_FACTOR ?número Especifica cómo de lleno se va a hacer cada nodo hoja; porcentaje desde 0 a 100.IGNORE_DUP_KEY Especifica que se ignorará una inserción en un índice único con valores duplicados y se emitirá un mensaje de aviso. Si no se especifica IGNO- RE_DUP_KEY KEY la inserción entera se deshará. DROP_EXISTING Especifica que los índices existentes con el mismo nombre se deben eliminar y

volver a crear el índice. Este parámetro mejora el rendimiento cuando se vuelve acrear un índice agrupado en una tabla que tiene índices no agrupados porque no serequieren pasos separados para eliminar y volver a crear los índices no agrupados.

STATISTICS_NORECOMPUTE Especifica que los datos estadísticos no deben ser recalculados. Esta opción no se

recomienda porque sus planes de ejecución se basan en datos que probablemente noserán óptimos. Se usa este parámetro sólo si se planea actualizar estadísticasmanualmente.

El uso de secuencias de comandos T-SQL es preferible al uso del Asistente para creación deíndices. A pesar que el T-SQL es inicialmente más difícil de utilizar, con el paso del tiempo, seencontrará que es más fácil la creación de múltiples índices si se usa T-SQL. USO DEL FACTOR DE RELLENO PARA EVITAR DIVISIONES DEPÁGINA

Cuando se hacen actualizaciones o inserciones en una tabla que contiene índices también setienen que actualizar las páginas de índices. Las páginas de índices se unen unas con otras mediantepunteros que van desde una página a otra. Hay dos punteros, uno a la siguiente página y otro a lapágina anterior. Cuando una página de índices está completa, una actualización del índice provocaráun cambio en la cadena de punteros porque se tiene que insertar una nueva página de punteros entredos páginas (en un proceso que se conoce como división de páginas de índices) para que se puedaescribir la nueva información en el sitio correcto de la cadena de índices. SQL Server mueveaproximadamente la mitad de las filas de la página existente (donde se han de incluir los nuevosdatos a esta nueva página de índices. Las dos páginas que originalmente se apuntaban una a la otraapuntan a la nueva página y la nueva página apunta a ambas (hacia delante y hacia atrás),

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

9 de 47 24/05/2011 11:50 p.m.

Page 10: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Cuando esto ocurra, se tendrá que encontrar espacio adicional para las nuevas páginas de índices.Para crear más espacio, SQL Server continúa realizando divisiones de página de índice, dando comoresultado un incremento en la sobrecarga del sistema debido a un uso adicional de CPU y deoperaciones de entrada/salida adicionales. Esto también produce índices fragmentados. Los datosdel índice se dispersan por la base de datos, causando un rendimiento menor.

Una manera de reducir la división y fragmentación de la página se realiza configurando el

factor de relleno de los nodos del índice. El factor de relleno especifica el porcentaje de los nodosque se ha de rellenar cuando se crea el índice, así se permite dejar espacio para las filas de índiceadicionales. Se puede especificar el factor de relleno de un índice usando la opción FILL_FACTORde la instrucción T-SQL CREATE INDEX, como se describió anteriormente. Si no se especifica unfactor de relleno en el comando CREATE INDEX, se usará el que tenga predeterminado el sistema.El valor predeterminado se sitúa en el valor que tenga el parámetro factor de relleno desp_configure. Este valor se establece en 0 cuando se instala SQL Server.

Nota: El parámetro de factor de relleno sólo afecta al modo en que se crea un índice; su cambio no tendráefecto una vez se ha construido el índice.

El rango de valor del factor de relleno se sitúa entre 0 y 100, que indica el porcentaje de la

página de índice que se rellena. Un valor de 0 es un caso especial. Cuando se especifica 0, los nodoshoja se rellenan completamente, pero los nodos rama y el nodo raíz dejan espacio libre. Este valor esel predeterminado en la instalación de SQL Server y normalmente funciona bien.

Un factor de relleno de 100 especifica que todos los nodos del índice sean completamente

rellenados cuando se crea el índice. Este valor es óptimo para los índices de las tablas que nunca vana recibir nuevos datos y que no son actualizadas. Ambos, los nodos hoja y los superiores del índice,se rellenan completamente y cualquier inserción causará una división de página. Las tablas de sólolectura son ideales para esta configuración; aunque se borren datos no se causarán divisiones depágina.

Un valor bajo del factor de relleno dejará mucho espacio para las inserciones pero requiere

una gran cantidad de espacio para crear el índice. A menos que se realicen inserciones constantes enla base de datos, la configuración del factor de relleno con un valor bajo no es normalmenteaconsejable. Si se producen muchas divisiones de página, se puede solucionar reduciendo el valordel factor de relleno y reconstruyendo el índice para ver si se produce un menor número dedivisiones.

Se puede conocer el número de divisiones de página por segundo que se producen en el

sistema usando el contador Page Splits/Sec del Monitor de rendimiento. Este contador se encuentraen el objeto SQL Server:Access Methods.

Si con el tiempo se producen divisiones de página y el índice se fragmenta muchas veces, la

solución sería la reconstrucción del índice. La fragmentación ocurre aunque se use un factor derelleno que deje espacio en la página índice. Con el tiempo, ese espacio también se rellenará. RECONSTRUCCIÓN DE LOS ÍNDICES

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

10 de 47 24/05/2011 11:50 p.m.

Page 11: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

realizar una consulta particular de la mejor manera posible. Las estadísticas de los índices seactualizan de forma predeterminada periodicamente. Sin embargo, los índices se fragmentan algunasveces cuando pasa un largo periodo de tiempo debido a las divisiones de páginas, que dispersanfisicamente el índice por la base de datos. Como resultado el rendimiento empeora. Un índicetambién puede llegar a estar desequilibrado, esto es, una porción del árbol tiene más páginas delíndice rellenas que otra. Se puede restablecer el equilibrio y la continuidad reconstruyendo el índice.También se vuelven a crear las estádisticas del índice cuando se reconstruye este.

Otro problema es cuando el índice tiene mas niveles de los necesarios. A mayor número de

niveles menor es el rendimiento ya que se deben realizar más operaciones de entrada/salida. Alreconstruir el índice también se reducen el número de niveles, y con ello, el número de operacionesde entrada/salida necesarias para las busquedas en el índice.

Los dos métodos para reconstruir un índice sin tener que borrarlo y volver a crearlo, se

realizan mediante CREATE INDEX ... DROP_EXISTING y DBCC DBREINDEX. CREATE INDEX ... DROP_EXISTING se usa para reconstruir sólo un índice cada vez en la

tabla. DBCC DBREINDEX se usa con el nombre de una base de datos y el nombre de un tabla

para reconstruir todos los índices de esa tabla, sin tener que ejecutar comandos separados para cadaíndice.

Si no se tiene tiempo o recursos para reconstruir los índices es posible actualizar las

estadísticas de los índices de forma independiente. Aunque esta técnica no es tan eficiente como lareconstrucción de índice, porque aunque actualicemos las estadísticas el índice puede continuarfragmentado, es mejor que tener unas estadísticas anticuadas si se ha desactivado la opción deactualización automática de las estadísticas.

UPDATE STATISTICS nombre_de_la_tabla[ nombre_del_indice | [nombre_de_estadística [, nombre_estadistica, ...]][ WITH[ FULLSCAN || SAMPLE número {PERCENT | ROWS][ ALL | COLUMNS | INDEX][ NORECOMPUTE]]

nombre_de_indice Especifica el índice al que se han de calcular las estadísticas. De forma

predeterminada, si no se especifica se recalculan las estadísticas de todos loíndices de la tabla.

nombre_de_estadística Si no se especifica se recalculan todas las estadísticas. FULLSCAN Especifica que se han de leer todas las filas de la tabla para recalcular las

estadísticas. Es la mejor manera de recalcular estadísticas pero también la máscostosa.

SAMPLE numero ... Especifica el número o porcentaje de columnas en las que se basan las

estadísticas. No se puede utilizar con la opción FULLSCAN. ALL |COLUMNS|INDEX Especifica si se recalculan todas las estadísticas, solo las de las columnas o solo

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

11 de 47 24/05/2011 11:50 p.m.

Page 12: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

CONSEJOS SOBRE EL USO DE ÍNDICES q Si una consulta accede a más del 20% de las filas de una tabla, un recorrido de tabla es más

efectivo que usar un índice. q Se debe tratar que los índice tengan la mayor selectividad posible (es decir el número de filas

obtenido por cada valor del clave del índice). A mayor selectividad del índice mayorprobabilidad de que sea usado por el optimizador de consultas.

q Se deben usar los índices con moderación, es decir, unos pocos índices pueden ser útiles pero

demasiados índices pueden afectar negativamente al rendimiento porque hay que mantenerlos actualizados cada vez que se realizan operación de insercción, actualización y borrado en losdatos.

q No poner índices en tablas pequeñas. q Utilizar el menor número posible de columnas en el índice (índices estrechos), ya que estos

ocupan menos espacio y necesitan menos sobrecarga de mantenimiento.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

12 de 47 24/05/2011 11:50 p.m.

Page 13: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

USO DE VISTAS ¿QUÉ ES UNA VISTA?

Una vista es una tabla virtual que está definida por una consulta que consiste en unainstrucción SELECT. Esta tabla virtual está creada con datos de una o más tablas reales y, para losusuarios, una vista parece una tabla real. De hecho, una vista puede ser tratada del mismo modo queuna tabla normal. Los usuarios pueden hacer referencia a estas tablas virtuales con instruccionesTransact-SQL (T-SQL) del mismo modo que se hace con las tablas. En una vista se pueden realizarlas operaciones SELECT, INSERT UPDATE y DELETE.

En realidad, una vista se almacena simplemente como una instrucción SQL previamente

definida. Cuando se accede a la vista, el optimizador de consultas de SQL Server une la instrucción SQL que se ejecuta en ese momento con la consulta que se use para definir la vista.

La ventaja de utilizar vistas es que se pueden crear vistas que tienen atributos diferentes sin

tener que duplicar los datos. Las vistas son útiles en un gran número de situaciones. Como se verámás adelante en este capítulo, se pueden utilizar para la seguridad de los datos, para facilitar lapresentación de los datos y para realizar una presentación lógica de los datos. También se puedenutilizar para unir datos divididos. CONCEPTOS DE VISTAS

Ahora que se tiene un concepto básico de lo que es una vista, se analizará con mayorprofundidad. En esta sección se explicarán los tipos de vistas, las ventajas de usar vistas y lasrestricciones que tiene SQL Server en el uso de una vista. Tipos de vistas

Se pueden crear varios tipos de vistas, cada uno de los cuales tienen ventajas en ciertassituaciones. El tipo de vista que se ha de crear depende completamente de para qué se quiera usar lavista. Se pueden crear vistas de cualquiera de las siguientes maneras: · Subconjunto de columnas de una tabla: Una vista puede consistir en una o más columnas de

una tabla. Probablemente es el tipo de vista más común y se puede utilizar para simplificar losdatos o para seguridad.

· Subconjunto de filas de una tabla: Una vista puede contener todas las filas que se deseen. Este

tipo de vista también es útil para la seguridad. · Unión de dos o más tablas: Se puede crear una vista usando una operación de unión. Las

operaciones de unión complejas se pueden simplificar cuando se usa una vista. · Información de agregación: Se puede crear una vista que contenga datos de agregación. Este

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

13 de 47 24/05/2011 11:50 p.m.

Page 14: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

posteriormente, se pueden usar las vistas para unir estas tablas en una gran tabla virtual que faciliteel acceso a la misma. Ventajas de las vistas

Una ventaja del uso de las vistas es que éstas siempre ofrecen los datos actualizados. Lainstrucción SELECT que define una vista sólo se ejecuta cuando se accede a la vista, por tanto,todos los cambios de la tabla subyacente se reflejan en la vista.

Otra ventaja de usar vistas es que una vista puede tener un nivel diferente de seguridad del

que posea la tabla subyacente. La consulta que define la vista se ejecuta con el nivel de seguridadque posea el usuario que crea la vista. De esta manera, se puede crear una vista que enmascare losdatos que no se quieran mostrar a ciertas clases de usuarios. Restricciones de las vistas

SQL Server asigna una serie de restricciones en la creación y el uso de las vistas. Estasrestricciones incluyen las siguientes: · Limitación de columnas: Una vista puede hacer referencia hasta a 1024 columnas de una

tabla. Si se necesita hacer referencia a un número mayor de columnas, se tendrán que usar otrosmétodos.

· Limitación de la base de datos: Solamente se puede crear una vista de una tabla en la base de

datos a la que el creador de la vista está accediendo. · Limitación de seguridad: El creador de la vista debe tener acceso a todas las columnas a las

que se haga referencia en la vista. · Reglas de integridad de los datos: Cualquier actualización, modificación, etc., que se haga en

la vista, no puede romper las reglas de integridad de los datos. Por ejemplo, si la tablasubyacente de la vista no admite los valores nulos, la vista tampoco los admitirá.

· Limitación de niveles en las vistas anidadas: Las vistas se pueden crear sobre otras vistas (en

otras palabras, se puede crear una vista que acceda a otras vistas). Las vistas se pueden anidarhasta 32 niveles.

· Limitación de la orden SELECT: La orden SELECT de una vista no puede contener una

cláusula ORDER BY ni la palabra clave INTO. CREACIÓN DE VISTAS

Las vistas, al igual que los índices, se pueden crear de muchas maneras. Se puede crear unavista usando la orden CREATE VIEW de T-SQL. Este método es preferible si hay posibilidades deque se creen más vistas en el futuro, porque se pueden almacenar las órdenes T-SQL en un archivo

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

14 de 47 24/05/2011 11:50 p.m.

Page 15: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Uso de T-SQL para crear una vista

Crear vistas usando el T-SQL es al proceso más rápido: se ejecuta CREATE VIEW paracrear una vista usando ISQL, OSQL o el Analizador de consultas de SQL Server. Como semencionó, es preferible utilizar los comandos T-SQL en un texto porque los comandos pueden sermodificados y reutilizados (también se deben guardar las definiciones de la base de datos en textopara cuando se tenga que volver a crear la base de datos).

El comando CREATE VIEW tiene la siguiente sintaxis:

CREATE VIEW nombre_de_la_vista [ ( columna, columna, ... ) ][WITH ENCRYPTION]ASinstrucción SELECT[WITH CHECK OPTION]

Cuando se crea una vista se pueden activar dos opciones para cambiar el comportamiento de

la vista. Se activan estas opciones incluyendo las palabras claves opcionales WITH ENCRYPTIONo WITH CHECK OPTION, o ambas, en la instrucción T-SQL. Seguidamente se verán estasopciones con más detalle.

La palabra clave WITH ENCRYPTION especifica que la definición de la vista (la

instrucción SELECT que define la vista) sea cifrada. SQL Server usa el mismo método para cifrarlas instrucciones SQL que para las contraseñas. Esta técnica de seguridad puede ser útil si no sequiere que ciertas clases de usuarios sepan a qué tablas se está accediendo.

La palabra clave WITH CHECK OPTION especifica que las operaciones de modificación de

datos que se realizan en una vista deben seguir el criterio contenido en la instrucción SELECT quedefine la vista. Por ejemplo, no estaría permitida una operación de modificación de datos realizadaen una vista para crear una fila de la tabla que no sea visible dentro de la tabla. Supongamos que sedefine una vista para seleccionar información sobre todos los empleados del departamento definanzas. Si no se incluye WITH CHECK OPTION, se puede modificar el valor de la columnadepartment de finanzas a un valor que indique otro departamento. Si se especifica esta palabraclave, esta modificación no estaría permitida porque la alteración del valor de una fila departmentharía que esta fila no volviese a ser accesible desde la vista. La palabra clave WITH CHECKOPTION especifica que no se puede hacer inaccesible una fila desde la vista haciendo un cambiodentro de ésta.

La instrucción SELECT se puede modificar para crear cualquier vista que se quiera. Se

puede utilizar para seleccionar un subconjunto de columnas, un subconjunto de filas o para realizaruna operación de unión. En las siguientes secciones se aprenderá cómo usar el T-SQL para crearvarios tipos de vistas. Subconjunto de columnas

Una vista consistente en un subconjunto de columnas puede ser útil cuando se necesitaproveer de seguridad una tabla que debería ser sólo parcialmente accesible para los usuarios. Ahora

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

15 de 47 24/05/2011 11:50 p.m.

Page 16: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

llamada Employee que contiene las columnas de datos que se muestran en la figura siguiente.

La mayoría de estos datos son críticos y deberían ser vistos sólo por ciertos empleados. Sería

útil, sin embargo, permitir a todos los empleados ver parte de los datos. Si se crea una vista quepermita a todos los empleados acceder sólo a ciertos datos solucionará está cuestión. También sepuede utilizar la vista para impedir que haya datos duplicados en otras tablas de bases de datos.

Para crear una vista de la tabla Employee que solamente permita el acceso a las columnas

name, phone y office se usa la siguiente instrucción T-SQL:

CREATE VIEW emp_vwAS SELECT name, phone, office FROM Employee

La vista resultante contendrá las columnas que se muestran en la figura siguiente. Aunque

estas columnas también existen en la tabla subyacente, los usuarios que usen esta vista para accedera los datos podrán ver las columnas sólo en la vista. Debido a que una vista puede tener un nivel deseguridad distinto al de la tabla subyacente, la vista puede permitir el acceso a cualquier personamientras que la tabla permanece segura. En otras palabras, se puede restringir el acceso a la tablaEmployee sólo al departamento de recursos humanos, por ejemplo, mientras se permite a todos losempleados que usen la vista.

Subconjunto de filas

Se puede utilizar una vista que consista en un subconjunto de filas para restringir el accesolimitando las vistas disponibles para los usuarios. Supongamos que la tabla Employee está llena dedatos, como se muestra en la figura siguiente. En este ejemplo, en vez de limitar las columnas, serestringen las filas especificándolas en una cláusula WHERE, como se muestra aquí:

CREATE VIEW emp_vw2AS SELECT *

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

16 de 47 24/05/2011 11:50 p.m.

Page 17: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

departamento de recursos humanos o departamento 1, como se muestra en la figura siguiente. Esta vista puede ser útil cuando los empleados del departamento de recursos humanos deban

acceder a las fichas de los empleados de su área. A una vista subconjunto_de_filas, al igual que lavista subconjunto_de_columnas, se le puede asignar un nivel de seguridad diferente al de la tabla otablas subyacentes.

Combinaciones

Mediante la definición de uniones en una vista, se puede simplificar las instrucciones T-SQLque se usan para acceder a los datos cuando las instrucciones contengan una instrucción JOIN.Ahora se verá un ejemplo. Supongamos que tenemos dos tablas, Manager y Employee2.

La siguiente instrucción combina las tablas Employee2 y Manager en una única tabla virtual:

CREATE VIEW org_chartAS SELECT Employee2.ename, Manager.mname FROM Employee2,Manager WHERE Employee2.manager_id =Manager.id GROUP BY Manager.mname,Employee2.ename

En este ejemplo, las dos tablas se combinan en el valor manager_id. Los datos resultantes,

que se incluyen en la vista org_chart, se agrupan por el nombre del director, como se muestra en lafigura siguiente. Nótese que sí un director está en la tabla Manager, pero ningún empleado de losque están en la tabla Employee2 trabaja para ese director, no aparecerá ninguna entrada para esedirector en la vista. Tampoco habrá entradas en la vista para un empleado que esté listado en la tablaEmployee2 pero que no tenga a su jefe correspondiente en la tabla Manager Para los usuarios, lo

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

17 de 47 24/05/2011 11:50 p.m.

Page 18: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Agregación

Las vistas de agregación pueden ser útiles de muchas maneras, tales como para recuperaciónde porcentajes y sumas de departamentos. Por ejemplo, para propósitos presupuestarios, se puedeusar una vista de agregado para ver la cantidad de dinero que cada departamento de la corporaciónpaga en salarios. También se puede llevar a cabo esta tarea usando una pregunta T-SQL. La ventajade usar una vista es que los usuarios pueden ejecutarla sin tener que conocer el modo de usar lasfunciones de agregación y las preguntas T-SQL. Nota: Las funciones de agregación de SQL Server realizan cálculos de un conjunto de valores y devuelvenun único valor. Las funciones de agregación incluyen AVG, COUNT MAX MIN y SUM.

La siguiente instrucción establece una vista que usa una función (SUM) en la tablaEmployee:

CREATE VIEW sal_vwAS SELECT dept, SUM(salary) FROM Employee GROUP BY dept

En este ejemplo la vista establece una tabla virtual que muestra el total que cada

departamento paga en salarios. Los datos resultantes se agrupan por departamentos. Esta vista de

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

18 de 47 24/05/2011 11:50 p.m.

Page 19: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

División

Las vistas se usan comúnmente para mezclar datos divididos en una única tabla virtual. Ladivisión se usa para reducir el tamaño de las tablas e índices. Para dividir los datos, se creanmúltiples tablas que reemplazan una única y se le asigna a cada nueva tabla un rango de valores dela tabla original. Por ejemplo, en vez de tener una gran tabla como base de datos que guarde losdatos de las transacciones de ventas de la compañía, se pueden crear muchas tablas pequeñas, cadauna de las cuales contiene los valores de una semana y después usar una vista para combinarlas yver el historial de transacciones. Las distintas tablas pequeñas y sus índices son más manejables quelo que podría ser una gran base de datos y su índice. Más aún, se pueden eliminar datos viejos deuna manera más sencilla borrando una tabla subyacente de datos obsoletos. Ahora se verá esteconcepto en mayor detalle.

La vista que se muestra en la figura siguiente parece una gran tabla para los usuarios, peropor debajo hay muchas tablas, cada una con su propio índice.

Como ya se ha mencionado, la división crea un sistema mucho más manejable para DBA y la

unión de datos divididos simplifican los datos para los usuarios. Para crear una vista que consolide los datos divididos o una vista dividida, primero se deben

crear las tablas divididas. Estas tablas contendrán la mayor parte de los datos de ventas. Cada tablaguardará los datos de un periodo en particular, normalmente una semana o un mes. Una vez se hancreado estas tablas, se puede usar una instrucción UNION ALL para crear una vista que contengatodos los datos. Por ejemplo, supongamos que se tienen cuatro tablas llamadas table_1, table_2,table_3 y table_4. La siguiente instrucción crea una gran tabla virtual que incluye todos los datos deestas tablas:

CREATE VIEW partviewAS SELECT * FROM table_1 UNION ALL SELECT * FROM table_2 UNION ALL SELECT * FROM table_3 UNION ALL SELECT * FROM table_4

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

19 de 47 24/05/2011 11:50 p.m.

Page 20: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

MODIFICACIÓN Y ELIMINACIÓN DE VISTAS

Se utiliza el comando ALTER VIEW para modificar las vistas mediante T-SQL. Estecomando es similar el comando CREATE VIEW y tiene la siguiente sintaxis:

ALTER VIEW nombre_de_la_vista [(columna, columna, ...)][WITH ENCRYPTION]ASsu instrucción SELECT[WITH CHECK OPTION]

La única diferencia entre los comandos ALTER VIEW y CREATE VIEW es que el segundo

comando falla si la vista ya existe, mientras que el comando ALTER VIEW falla si la vistanombrada no existe. (Las palabras clave opcionales WITH ENCRYPTION y WITH CHECKOPTION tienen el mismo significado que en la creación de la vista). Para ver cómo funciona elcomando ALTER VIEW, se volverá a usar el ejemplo la división. Para eliminar una división vieja yañadir una nueva se puede modificar la vista como sigue:

ALTER VIEW partviewAS SELECT * FROM table_2 UNION ALL SELECT * FROM table_3 UNION ALL SELECT * FROM table_4 UNION ALL SELECT * FROM table_5

La vista modificada será similar a la que se hizo con el comando ALTER VIEW, pero ahora se

selecciona un conjunto de datos diferentes. La vista ya no usará table_1, sino que ahora utilizarátable_5.

Para eliminar una vista se usa el comando DROP VIEW. La sintaxis de este comando, que es

muy simple, se muestra a continuación:

DROP VIEW nombre_de_la_vista

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

20 de 47 24/05/2011 11:50 p.m.

Page 21: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

SEGURIDAD Y USUARIOS

En este apartado se aprenderá a administrar usuarios y seguridad en un entorno MicrosoftSQL Server 2000. Una de las tareas más corrientes de un administrador de bases de datos, ademásde la planificación de la recuperación y las copias de seguridad, volumen y administración deespacio, es la administración de la seguridad. Es también una de las tareas más importantes que unadministrador de bases de datos tiene que realizar. Si se viola la seguridad del sistema, los datos sepueden perder o corromper. En este apartado se aprenderá a crear y administrar sesiones de iniciode usuario, así como los modos de autenticación; además se aprenderá sobre los ID de usuario deSQL Server. Una sesión de inicio de usuario se usa para autenticar el acceso a SQL Server. Lasesión de inicio de usuario puede autenticarse por medio de Microsoft Windows NT o MicrosoftWindows 2000 o por medio de SQL Server. Un ID de usuario se usa para asignar permisos ausuarios para acceder a objetos específicos dentro de las bases de datos individuales. Los ID deusuario están asociados a los inicios de sesión de usuarios y podrían o no tener el mismo nombre,como verá más tarde en este apartado. Se aprenderá también sobre los tipos de permisos que sepueden asignar en SQL Server y cómo se usan. Y también se aprenderá a usar funciones paraadministrar con más facilidad a los usuarios. CREACIÓN Y ADMINISTRACIÓN DE INICIOS DE SESIÓN DE USUARIO

Comienza el examen y administración de usuarios considerando los inicios de sesión deusuario. En esta sección se tratará primero por qué son importantes los inicios de sesión, así comolos métodos de autenticación que se pueden usar para mantener los inicios de sesión. Es posiblecrear inicios de sesión mediante tres métodos: mediante el Administrador corporativo de SQLServer, mediante Transact-SQL (T-SQL) y mediante el uso del Asistente para creación de inicio desesión. ¿Por que crear inicios de sesión de usuario?

Los inicios de sesión de usuarios permiten proteger los datos contra modificaciones noautoriza sean intencionadas o no. Gracias a los inicios de sesión de usuario, SQL Server permiteidentificar usuarios individuales autorizados. Cada inicio de sesión de usuario tiene asignado unnombre único y una contraseña. Cada usuario tiene asignado su propia cuenta de inicio de sesión.Sin los inicios de sesión de usuario, todas las conexiones a SQL Server usarían el mismoidentificador, lo que imposibilitaría crear diferentes niveles de seguridad basados en quién accede ala base de datos.

Con los inicios de sesión de usuario, puede crear varios niveles de seguridad permitiendo

permisos diferentes a diferentes cuentas de inicio de sesión con el fin de acceder a objetos y realizarfunciones.

Y gracias a los in sesión de usuario, se puede permitir solamente a algunos usuarios insertar y

actualizar información en ciertas tablas de la base de datos, y conceder accesos de sólo lectura a las

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

21 de 47 24/05/2011 11:50 p.m.

Page 22: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

de teléfono, número de oficina, nivel académico, salario, bonos, etc. Para evitar que ciertos usuariosaccedan a información confidencial en la tabla, se debería crear primero una vista que contuvierasolamente información no sensible, tal como los nombres de los empleados, números de teléfono ynúmeros de oficina.

Después, implementando inicios de sesión de usuario, se puede restringir el acceso a la tabla

subyacente mientras se permite a todos los usuarios acceder a la vista. Sin embargo, si no seaprovecharan las posibilidades de los inicios de sesión de usuario, cualquier usuario podría acceder ala vista o a la tabla, perdiendo así el uso de la vista su intencionalidad. Modos de autenticación

Para acceder a SQL Server se dispone de dos modos de autenticación: Autenticación conWindows de Microsoft y Autenticación en modo mixto. En Autenticación con Windows, el sistemaoperativo es el responsable de identificar al usuario. SQL Server usa después esta identificación delsistema operativo para determinar los permisos de usuario que hay que aplicar. Con Autenticaciónen modo mixto, los dos Windows NT/2000 y SQL Server son responsables de identificar al usuario.Para acceder a SQL Server, hay siempre que acceder primero mediante un inicio de sesión a unacuenta Windows NT/2000, de forma que cuando se selecciona un modo de autenticación, hay quedecidir simplemente si se quiere utilizar la Autenticación SQL Server además de la Autenticación deWindows. Se va a tratar con más detalle cada uno de estos modos de autenticación. Se aprenderácómo implementar estos modos más adelante en esta sección. Autenticación de Windows

Según se ha indicado, con Autenticación de Windows, SQL Server se basa en WindowsNT/2000 para proporcionar seguridad al inicio de sesión. Cuando un usuario inicia su sesión conWindows NT/2000, se comprueba la identificación de la cuenta de usuario. SQL Server compruebaque el usuario fue validado por Windows NT/2000 y permite el acceso basado en esa identificación.

SQL Server integra su proceso de seguridad en el inicio de sesión con el de Windows para

proporcionar estos servicios. Los atributos de seguridad en la red se validan por medio de unsofisticado proceso de cifrado proporcionado por Windows NT/2000.

Debido a que los procesos de seguridad en el inicio de sesión de SQL Server y Windows se

integran cuando se utiliza este modo, una vez que se es identificado por el sistema operativo, no esnecesario utilizar ningún otro método de identificación al acceder a SQL Server. La únicacontraseña que hay que suministrar en el inicio de sesión con SQL Server es la contraseña deWindows NT/2000.

La Autenticación de Windows se considera un método mejor que la Autenticación en modo

mixto debido a las características de seguridad adicionales que proporciona. Estas característicascomprenden la validación y cifrado de forma segura de las contraseñas, auditorías, caducidad decontraseñas, longitud mínima de la contraseña, y bloqueo automático de la cuenta tras undeterminado número de intentos fallidos en el inicio de sesión.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

22 de 47 24/05/2011 11:50 p.m.

Page 23: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

En la Autenticación en modo mixto, los usuarios pueden acceder a SQL Server utilizando laAutenticación de Windows o la Autenticación de SQL Server. Al usar la Autenticación en modomixto, si se produce una conexión desde un sistema inseguro, SQL Server identifica el inicio desesión comprobando si la cuenta de inicio de sesión se ha configurado con el acceso solicitado por elusuario. SQL Server realiza la identificación de la cuenta comparando el nombre del código ycontraseña que el usuario proporciona al conectarse a SQL Server con la información de la cuentaalmacenada en la base de datos. Si la cuenta para el inicio de sesión no ha sido configurada por elusuario o si el usuario no proporciona el nombre correcto y su contraseña, se rechaza el acceso aSQL Server.

El modo de Autenticación de Windows no está disponible cuando se ejecuta SQL Server en

Windows 95/98, de manera que hay que usar la Autenticación de SQL Server (usando laAutenticación en modo mixto) en tales plataformas. Además, las aplicaciones Web requieren laAutenticación de SQL Server (a través de Microsoft Internet Information Server) porque losusuarios de estas aplicaciones no estarán probablemente dentro del mismo dominio que el servidor ypor tanto no pueden trabajar basados en los mecanismos de seguridad de Windows. Otrasaplicaciones que necesitan acceso a base de datos podrían requerir igualmente la autenticación deSQL Server: algunos desarrolladores de aplicaciones prefieren usar la seguridad de SQL Server parasus aplicaciones porque simplifica la seguridad de las mismas. Cuando las aplicaciones utilizan laseguridad de SQL Server (dentro de una red de confianza), los desarrolladores de aplicaciones notienen que proporcionar autenticación de seguridad dentro de la propia aplicación, lo que simplificael trabajo. Configuración del modo de autenticación

En la configuración del modo de autenticación se siguen estos pasos:

1. Abrir la ventana del Administrador corporativo. En el panel izquierdo, pulsar con el botónsecundario el nombre del servidor que contiene la base de datos para la que se desea establecerel modo de autenticación y seleccionar Propiedades del menú emergente para mostrar la ventanade SQL Server. Pulsar en la ficha Seguridad, como aparece en la figura siguiente.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

23 de 47 24/05/2011 11:50 p.m.

Page 24: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

2. En esta ficha se puede seleccionar tanto el método de seguridad como la cuenta de inicio del

servicio. En el área de Seguridad, se especifica si debería usar Autenticación SQL Server yWindows NT/2000 (Modo mixto) o si se debería usar sólo Autenticación Windows NT/2000.También se puede especificar el nivel de auditoría del inicio de sesión. Esta configuracióndetermina qué tipo, en caso de existir, que auditoría de inicio de sesión se realiza. El nivel deauditoría seleccionado dependerá de los requisitos de seguridad. Hay cuatro niveles disponiblesque son los siguientes:

- Ninguno: No realiza ninguna auditoría de inicio de sesión. Es la configuración predeterminada.- Correcto: Registra todos los intentos válidos de inicios de sesión.- Error: Registra todos los intentos fallidos de inicio de sesión.- Todos: Registra todos los intentos de inicio de sesión.

Nota: El nivel de auditoría es una propiedad de la base de datos. De esta manera, se aplica el mismo nivelde auditoría a todos los inicios de sesión. 3. En el área de la cuenta de inicio de servicio, hay que especificar la cuenta de Windows que se

debería usar al iniciar y ejecutar SQL Server. Se puede usar la cuenta del sistema o especificaruna cuenta, por ejemplo la de Administrador, con su contraseña. Pulsar en Aceptar paraconfirmar la configuración establecida.

Inicios de sesión y usuarios

En las dos secciones siguientes se aprenderá cómo crear sesiones de inicio de sesión y

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

24 de 47 24/05/2011 11:50 p.m.

Page 25: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

la base de datos. También podría ser necesaria la Autenticación de SQL Server. Tanto si se va a usarAutenticación Windows o Autenticación en modo mixto, la cuenta que se usa para la conexión aSQL Server se conoce como inicio de sesión de SQL Server.

Además del inicio de sesión a SQL Server, cada base de datos tiene un conjunto de cuentas

ficticias asignadas a ella. Estas pseudocuentas proporcionan alias a cuentas de inicio de sesión deSQL Server.

Por ejemplo, en la base de datos Northwind, podría haber un usuario llamado administrador

que se asocia con el inicio de sesión invitado, y la base de datos pubs podía tener un usuariollamado administrador que se asocia con la cuenta de SQL Server de inicio de sesión sa.

En forma predeterminada, una cuenta de inicio de sesión de SQL Server no tiene un Id. De

usuario de base de datos asociado con ella; por tanto, carece de permisos. Creación de inicios de sesión de SQL Server

Se pueden realizar la mayoría de las tareas administrativas utilizando una de los diversosmétodos, y la tarea de crear los inicios de sesión de usuarios no es una excepción. Según semencionó anteriormente, se puede crear un inicio de sesión de una de las tres formas: mediante usodel Administrador Corporativo, mediante T-SQL, o usando el Asistente de creación de inicios desesión. Utilización del Administrador corporativo para la creación de inicios de sesión de SQL Server

Para crear los inicios de sesión de SQL Server utilizando el Administrador corporativo, sesiguen estos pasos: 1. En el panel izquierdo de la ventana del Administrador corporativo, expandir el grupo servidor,

expandir el servidor, y luego expandir la carpeta Seguridad.Pulsar con el botón secundario en Inicios de sesión, y seleccionar Nuevo inicio de sesión delmenú contextual para mostrar la ventana de Propiedades de inicio de sesión de SQL Server, quese presenta a continuación.En la ficha General, introducir un nombre de inicio de sesión en el cuadro de texto Nombre. Sise usa Autenticación de Windows, el nombre debe ser un nombre de cuenta válido enWindows. A continuación se especifica el dominio de Windows en el cuadro de texto Dominio.En el área Predeterminado, especificar la base de datos y el idioma predeterminado para esteinicio de sesión. En el área Autenticación hay que especificar si se usará una cuenta deAutenticación de Windows o de Autenticación de SQL Server. Si se selecciona Autenticaciónde SQL Server, se usará el Modo de autenticación mixto.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

25 de 47 24/05/2011 11:50 p.m.

Page 26: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

2. Pulsar con el botón la ficha Funciones del servidor, que se presenta en la figura siguiente. En

esta ficha se puede especificar qué funciones del servidor se utilizan, seleccionando estasfunciones de la lista de funciones disponibles al usuario. Pulsando Propiedades se puede ver ymodificar la función seleccionada. Las funciones se explican en la sección «Funciones deadministración de bases de datos» más adelante en este capítulo.)

3. Pulsar la ficha Acceso a base de datos, que aparece en la figura siguiente. Esta ficha permite

especificar qué bases de datos tiene permiso el usuario para acceder. (Los permisos de las basesde datos se cubren en la sección «Administración de permisos de bases de datos» más adelanteen este capítulo.) Se pueden seleccionar varias bases de datos y las funciones que estándisponibles a esas bases de datos. Pulsar Propiedades permite ver y administrar las propiedadesde las funciones de la base de datos.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

26 de 47 24/05/2011 11:50 p.m.

Page 27: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

4. Al finalizar las opciones de especificación, hay que guardar el inicio de sesión pulsando Aceptar.

Para ver listado el nuevo inicio de sesión junto a los demás inicios de sesión hay que pulsar lacarpeta Inicios de sesión en el Administrador corporativo. Los inicios de sesión aparecen listadosen el panel derecho.

CREACIÓN DE USUARIOS DE SQL SERVER

Se pueden crear usuarios de SQL Server utilizando el Administrador corporativo o T-SQL.(SQL Server no incluye ningún asistente de ayuda a este proceso). Hay que recordar que un usuariode SQL Server se define para una base de datos particular y los permisos se asignan a esa base dedatos para un inicio de sesión de usuario específico. El Id. de usuario de SQL Server puede teneruna misión análoga a la del inicio de sesión de SQL Server, pero no necesita tener el mismo nombreque el inicio de sesión. Nota: para crear un usuario SOL Server, tiene que haber definido un inicio de sesión de SQLServer para ese usuario porque el nombre de usuario constituye una referencia a un inicio desesión de SQL Server. Uso del Administrador corporativo en la creación de usuarios

A diferencia de los inicios de sesión de SQL Server, que son creados dentro de la carpetaSeguridad del Administrador corporativo, los usuarios de SQL Server se crean a partir de unacarpeta de base de datos específica en el panel izquierdo del Administrador corporativo. Para crearusuarios que utilicen el Administrador corporativo, se siguen estos pasos:

1 Pulsar el botón secundario la base de datos en la que se va a crear al usuario, apuntar aNuevo en el menú contextual, y luego seleccionar Usuario de base de datos para mostrar laventana Propiedades del usuario de la base de datos, que aparece en la figura siguiente.Introducir un nombre válido de inicio de sesión de SQL Server en la lista desplegable

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

27 de 47 24/05/2011 11:50 p.m.

Page 28: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

este capítulo, asignando permisos a estas funciones, se pueden aplicar los permisos alusuario.

2. Pulsar Propiedades para mostrar la ventana Propiedades de la función de la base de datos.

En esta ventana se puede modificar una función de la base de datos que se hayaseleccionado.

3. Cuando se finalice con las opciones de configuración hay que pulsar Aceptar dos veces

para crear el usuario de la base de datos. ADMINISTRACIÓN DE PERMISOS DE BASES DE DATOS

Los permisos se utilizan para controlar el acceso los objetos de bases de datos y paraespecificar qué usuarios pueden realizar ciertas acciones en las bases de datos. Se pueden establecertanto permisos del servidor como de la base de datos. Los permisos de servidor permiten aladministrador de la base de datos realizar las tareas de administración de la base de datos. Lospermisos de base de datos se usan para permitir o denegar el acceso a objetos de base de datos y ainstrucciones. En esta sección se tratarán todos los tipos de permisos y cómo asignarlos. Permisos de servidor

Como ya se mencionó anteriormente, los permisos de servidor se asignan a losadministradores de la base de datos para permitirles desarrollar tareas administrativas. Los permisosse definen de acuerdo a determinadas funciones del servidor. Los inicios de sesión de usuario

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

28 de 47 24/05/2011 11:50 p.m.

Page 29: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

ser modificados ni concedidos a usuarios individuales. Permisos de objetos de base de datos

Los permisos de objetos de base de datos son una a clase de permisos que se conceden parapermitir el acceso a objetos de base de datos. Los permisos de objetos son necesarios para acceder auna tabla o vista utilizando instrucciones SQL tales como SELECT, INSERT UPDATE y DELETE.

Un permiso de objeto también se necesita para usar la instrucción EXECUTE para la

ejecución de un procedimiento almacenado. Se puede utilizar el Administrador corporativo o loscomandos T-SQL para asignar permisos a objetos. Utilización del Administrador corporativo para asignar permisos de objetos

Para usar el Administrador corporativo para conceder permisos a objetos de base de datos aun usuario, se siguen los pasos siguientes: 1. Expandir un grupo servidor, expandir la base de datos a la que se desea asignar permisos y

pulsar la carpeta Usuarios. Los usuarios aparecen listados entonces en el panel derecho. Pulsarcon el botón secundario un nombre de usuario y seleccionar propiedades del menú emergentepara mostrar la ventana Propiedades del usuario de la base de datos, que aparece en la figurasiguiente.

2. Pulsar el botón Permisos para mostrar la ventana Propiedades del usuario de la base de datos,

que aparece en la figura siguiente. (Para mostrar esta ficha, también se puede pulsar con el botónsecundario el nombre del usuario, apuntar a Todas las tareas en el menú emergente y luegoseleccionar Administrar permisos.) En esta ficha se administran los permisos asignados alusuario. Para asignar permisos al usuario para acceder a objetos dentro de la base de datos, seseleccionan las casillas de comprobación en las columnas SELECT, INSERT, UPDATE,

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

29 de 47 24/05/2011 11:50 p.m.

Page 30: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

superior de la ventana para enumerar todos los objetos o sólo los objetos que tienen permiso deacceso para este usuario.

Utilización de T-SQL para asignar permisos de objeto

Para usar T-SQL en la asignación de permisos de objeto a un usuario, se ejecuta lainstrucción GRANT. La instrucción GRANT tiene la sintaxis siguiente:

GRANT {ALL | permisos } [ columna ON { tabla | vista} ] [ ON tabla (columna) ] | [ ON vista (columna) ] | [ ON procedimiento_almacenado | procedimiento_extendido ]TO cuenta_de_seguridad [ WITH GRANT OPTION ] [ AS { grupo | función } ]

El parámetro cuenta_de_seguridad tiene que tener el tipo de cuenta siguiente:

· Usuario SQL Server.· Función SQL Server.· Usuario Windows.· Grupo Windows.

La utilización de la palabra clave GRANT OPTION permite al usuario o usuariosespecificados en la instrucción conceder el permiso especificado a otros usuarios. Esto puede ser útilcuando se conceden permisos a otros administradores de la base de datos. Sin embargo, la opciónGRANT se deberá usar con cuidado.

La opción AS especifica cuál es la autoridad con la que se ejecuta la instrucción GRANT.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

30 de 47 24/05/2011 11:50 p.m.

Page 31: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

GRANT SELECT, INSERT, UPDATEON CustomersTO MaríaWWITH GRANT OPTIONAS Accounting

La opción AS Accounting se usa porque la función Accounting tiene derecho a conceder

permisos en la tabla Customers. La palabra clave GRANT OPTION permite a MaríaW concederpermisos a otros usuarios. Más información: Para ver una lista de los permisos que se pueden especificar en la instrucción GRANTbúsquese «GRANT descrita(GRANT)» en el índice de Libros en pantalla. Uso de T-SQL para revocar permisos de objeto

Se puede usar el comando REVOKE de T-SQL para revocar los permisos de objeto a unusuario.

La instrucción REVOKE tiene la sintaxis siguiente: REVOKE [ GRANT OPTION FOR ] { ALL [ PRIVILEGES ] | permisos }

[ columna ON { tabla | vista } ] | [ ON tabla (columna) ] | [ ON vista (columna) ] | [ ON { procedimíento_almacenado | procedimiento_extendido } ] { TO | FROM } cuenta_de_seguridad [ CASCADE ] [ AS { grupo | función }

El parámetro cuenta_de_seguridad tiene que pertenecer a uno de los tipos siguientes:

· Usuario SQL Server.· Función SQL Server.· Usuario Windows.· Grupo Windows.

La opción GRANT OPTION FOR permite revocar permisos que se concedieron conanterioridad usando la palabra clave GRANT OPTION, así como revocar permisos. La opción ASespecifica con qué autoridad se ejecuta la instrucción REVOKE.

Un ejemplo de uso de la instrucción REVOKE es el siguiente:

REVOKE ALLON CustomersFROM MaríaW

La instrucción REVOKE ALL elimina todos los permisos que el usuario tiene en la tabla

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

31 de 47 24/05/2011 11:50 p.m.

Page 32: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Permisos de instrucción de base de datos

Además de asignar permisos de objeto de base de datos, se pueden asignar permisos deinstrucción. Los permisos de objeto capacitan a los usuarios para acceder a los objetos dentro de labase de datos, mientras que los permisos de instrucción les autorizan a crear objetos de base dedatos, inclusive bases de datos y tablas. Los permisos de instrucción se listan a continuación: · BACKUP DATABASE: Permite al usuario ejecutar el comando BACKUP DATABASE. · BACKUP LOG: Permite al usuario ejecutar el comando BACKUP LOG. · CREATE DATABASE: Permite al usuario crear nuevas bases de datos. · CREATE DEFAULT: Permite al usuario crear valores predeterminados que pueden ser

vinculados a las columnas. · CREATE PROCEDURE: Permite al usuario crear procedimientos almacenados. · CREATE RULE: Permite al usuario crear funciones. · CREATE TABLE: Permite al usuario crear nuevas tablas. · CREATE VIEW: Permite al usuario crear nuevas vistas. Se pueden asignar permisos de

instrucción por medio del Administrador corporativo o por T-SQL. Utilización del Administrador corporativo para asignar permisos de instrucción

El uso del Administrador corporativo para conceder permisos de instrucción a un usuariorequiere los pasos siguientes:

1. Expandir un grupo servidor, expandir un servidor y luego expandir la carpeta Base dedatos. Pulsar con el botón secundario el nombre de la base de datos a la que se desea asignarpermisos y seleccionar Propiedades del menú emergente para mostrar la ventanaPropiedades de la base de datos.

2. Pulsar la ficha permisos como se muestra en la figura siguiente. Aquí se pueden asignar

permisos de instrucción a los usuarios y funciones que tengan acceso a la base de datos. Lascolumnas que contienen casillas de comprobación definen los permisos de instrucción que sepueden asignar y la columna Usuario o Función lista los usuarios y las funciones que tienenacceso a esta base de datos.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

32 de 47 24/05/2011 11:50 p.m.

Page 33: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Utilización de T-SQL para asignar permisos de instrucciones

Para asignar permisos de instrucción a un usuario utilizando T-SQL, se usa la instrucciónGRANT. La instrucción GRANT tiene la sintaxis siguiente:

GRANT { ALL | instrucción }TO cuenta_de_seguridad

Los permisos de instrucción que se pueden asignar a un usuario son CREATE DATABASE,

CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATEVIEW, DROP TABLE, DROP VIEW, BACKUP DATABASE y BACKUP LOG, según se dijoanteriormente. Por ejemplo, para agregar los permisos de instrucción CREATE DATABASE yCREATE TABLE a la cuenta de usuario JackR, se usa el comando siguiente:

GRANT CREATE DATABASE, CREATE TABLETO 'JackR’

Como se puede ver, agregar permisos de instrucción a una cuenta de usuario no es un

proceso nada complicado. Utilización de T-SQL para revocar permisos de instrucción

Puede usar la instrucción T-SQL, REVOKE para eliminar permisos de instrucción de unacuenta de usuario. La instrucción REVOKE tiene la sintaxis siguiente:

REVOKE { ALL | instrucción }FROM cuenta_de_seguridad

Por ejemplo, para eliminar sólo el permiso de instrucción CREATE DATABASE de la

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

33 de 47 24/05/2011 11:50 p.m.

Page 34: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Como se puede ver, eliminar permisos de instrucción de una cuenta de usuario no es un

proceso nada complicado. ADMINISTRACIÓN DE FUNCIONES DE BASES DE DATOS

Se puede simplificar la tarea de administrar muchos permisos a muchos usuarios usando lasfunciones de base de datos. Las funciones de base de datos se diseñan para permitir a grupos deusuarios recibir los mismos permisos de base de datos sin tener que recibir los permisosindividualmente. En lugar de asignar permisos individuales a usuarios individuales, se puede crearuna función que represente los permisos utilizados por un grupo de usuarios y después asignarlos algrupo.

Normalmente, las funciones se configuran para grupos de trabajo particulares, clases detrabajo, o tareas de trabajo. De esta manera, los nuevos usuarios se pueden hacer miembros de una omás funciones de base de datos basadas en los trabajos que se irán a realizar. Por ejemplo, sepodrían definir funciones para clase de trabajos tales como cuentas de pago, cuentas de recibos,ingeniería y recursos humanos. Cuando un usuario se une a uno de estos departamentos o grupos, sele asigna simplemente como un miembro de la función creada para ese grupo. Un usuario puede sermiembro de una o más funciones, pero el usuario no necesita ser miembro de ninguna de lasfunciones. Además para ser asignado miembro de una función de base de datos, el usuario tiene quetener asignados permisos individuales. Creación y modificación de funciones

Se cumple con la tarea de crear y modificar funciones de base de datos usando las mismasherramientas que se usan para realizar la mayoría de las tareas relacionadas con la administración debase de datos:

Administrador corporativo o comandos T-SQL. (SQL Server no proporciona asistente para

estas tareas.) Cualquiera que sea el método que use, se deben realizar las tareas siguientes alimplementar una función: · Crear una función de base de datos.· Asignar permisos a la función.· Asignar usuarios a la función

Al ver una función se podrán ver tanto los permisos asignados a la función como los usuariosasignados a la función. Utilización del Administrador corporativo para administrar funciones

Para crear funciones de base de datos con el Administrador corporativo, se siguen los pasossiguientes:

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

34 de 47 24/05/2011 11:50 p.m.

Page 35: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Función de base de datos. Como alternativa, se puede expandir la base de datos, pulsarFunciones con el botón secundario y seleccionar Nueva función de base de datos del menúcontextual. En cualquier caso, aparece la ventana Propiedades de la función de la base de datos,según se muestra en la figura siguiente.

2. Asignar un nombre descriptivo a la función introduciendo el nombre en el cuadro de texto

Nombre -seleccionar un nombre que ayude a recordar el papel de la función. La figura anteriormuestra el nombre Cuentas de pago seleccionado para la función.

3. Para asignar usuarios a la función, pulsar Agregar. Aparece una lista de cuentas de usuario que

tienen acceso a la base de datos. Seleccionar los usuarios que se desee asignar a esta función.Para cancelar una selección, se pulsa otra vez sin más el nombre del usuario apropiado. Alfinalizar la modificación de los miembros de la función, se pulsa Aceptar y se crea la función. Sevuelve a la ventana del Administrador corporativo.

4. Para asignar permisos a la función, primero hay que abrir la ventana Propiedades de la función

de la base de datos expandiendo la carpeta Funciones, pulsar con el botón secundario el nombre

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

35 de 47 24/05/2011 11:50 p.m.

Page 36: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

En esta ventana se pueden asignar varios permisos a esta función de los objetos dentro de labase de datos que contiene la función. Para ello, se seleccionan las casillas de comprobación en elcuadro de lista. Los objetos de la base de datos se listan en la columna Objeto. Se pueden usar losbotones de opciones en la parte superior de la ventana para ver todos los objetos o sólo aquellospara los cuales ya tiene permisos la función. Una vez que asigna la función a un usuario, ésterecibirá todos los permisos que tiene asignados la función.

Una vez que se crea una función, se puede modificar en la ventana Propiedades de la funciónde la base de datos. Para modificar una función, se siguen los pasos utilizados para agregar permisosa una función. Puede agregar y eliminar usuarios y permisos en la ventana Propiedades de la funciónde la base de datos. Uso de funciones fijas de servidor

Cierto número de funciones predefinidas que se aplican en torno al servidor se crean en elmomento de la instalación de SQL Server en el servidor. Estas funciones fijas del servidor se usanpara conceder permisos a los administradores de la base de datos y pueden contener tanto permisosde servidor como permisos de objeto y de instrucción.

Las funciones se listan a continuación:

· Bulkadmin: Puede realizar inserciones masivas.· Dbcreator: Puede crear y alterar bases de datos.· Diskadmin: Puede administrar archivos en disco.· Processadmin: Puede administrar procesos de SQL Server.· Securityadmin: Puede administrar inicios de sesión y crear permisos de base de datos.· Serveradmin: Puede establecer cualquier opción del servidor y puede cerrar la base de datos.· Setupadmin: Puede administrar servidores enlazados y arrancar procedimientos.· Sysadmin: Puede realizar actividades del servidor.

Mediante la asignación de cuentas de usuario a funciones fijas del servidor, capacita a losusuarios para realizar las tareas administrativas para aquellas funciones que tienen permisoconcedido. Dependiendo de las necesidades, podría ser preferible que todos los administradores dela base de datos tuvieran la misma cuenta administrativa. Al igual que las funciones de base dedatos, las funciones fijas de servidor son mucho más fáciles de mantener que los permisosindividuales, pero las funciones fijas de servidor no se pueden modificar. Se puede asignar unusuario a una función fija de servidor siguiendo los pasos que se listan en la página siguiente. 1. En el Administrador corporativo, expandir grupo servidor, expandir un servidor, expandir la

carpeta Seguridad y luego pulsar Funciones del servidor. Pulsar con el botón secundario lafunción fija del servidor que desea agregar al usuario y seleccionar Propiedades del menúcontextual. Lo que invoca la ventana Propiedades de la función del servidor.

2. Para agregar una cuenta de usuario a la función, se pulsa primero Agregar. Lo que invoca el

cuadro de diálogo Agregar miembros, en la que se seleccionan nuevos miembros de la función.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

36 de 47 24/05/2011 11:50 p.m.

Page 37: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

TRANSACCIONES Y BLOQUEOS

Este apartado explica los fundamentos de las transacciones y del bloqueo de transacciones.En este apartado se aprenderá lo que es una transacción, las propiedades que Microsoft SQL Server2000 exige para considerar válida una transacción, la manera en que se utilizan los modos detransacciones para especificar el comienzo y el final de una transacción y el modo de confirmar ydeshacer una transacción. También se examinarán los tipos y modos de bloqueos que utiliza SQLServer al realizar transacciones y los conceptos de bloqueo e interbloqueo. TRANSACCIONES

Una transacción es una serie de operaciones que se realizan como una unidad lógica detrabajo. Las transacciones permiten a SQL Server asegurar un cierto nivel de integridad y derecuperabilidad de los datos. El registro de transacciones, que debe tener toda base de datos,guarda un registro de todas las transacciones que realizan cualquier tipo de modificación (inserción,actualización o eliminación) en la base de datos. SQL Server utiliza este registro de transaccionespara recuperar los datos en caso de que se produzcan errores o fallos del sistema.

La integridad de una transacción depende en parte del programador de SQL. El programador

debe saber el momento en que debe iniciar y finalizar la transacción y el orden en que debe llevar acabo las modificaciones de los datos para asegurarse de la consistencia lógica y del significado de losdatos. Se estudiará la manera de comenzar y concluir las transacciones en apartados posteriores.Una vez que se sabe lo que es una transacción se examinarán las propiedades que se exigen a unatransacción para considerarla válida.

PROPIEDADES ACID

Una transacción debe reunir cuatro requisitos para poder considerarla válida. Estos requisitosse conocen como propiedades ACID. «ACID» es un acrónimo de atomicidad, consistencia,aislamiento (isolation) y durabilidad. SQL Server ofrece mecanismos para ayudar a asegurar que unatransacción cumpla todos estos requisitos. Atomicidad

SQL Server asegura que todas las modificaciones de datos de una transacción se completencomo un grupo si la transacción tiene éxito o que no se lleve a cabo ninguna de ellas si no lo tiene -en otras palabras, SQL Server asegura la atomicidad de las transacciones. La transacción debellevarse a cabo como si fuera una unidad atómica - de ahí el término «atomicidad». Para que unatransacción tenga éxito, cada paso (o instrucción) de la transacción debe tener éxito. Si uno de lospasos falla, falla toda la transacción y cualquier modificación efectuada desde su comienzo sedeshace. SQL Server proporciona un mecanismo de administración de transacciones que lleva acabo de manera automática la tarea de determinar si una transacción ha tenido éxito o ha fracasado

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

37 de 47 24/05/2011 11:50 p.m.

Page 38: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Consistencia

SQL Server también asegura la consistencia de las transacciones. Consistencia significa quetodos los datos permanecen en un estado consistente -que la integridad de los datos quedapreservada - una vez finalizada una transacción, independientemente de sí la transacción hafracasado o se ha completado con éxito. Antes de que comience una transacción la base de datosdebe hallarse en un estado consistente, lo que significa que se conserva la integridad de los datos yque las estructuras internas, como los índices de los árboles B y las listas doblemente enlazadas, soncorrectas. Y una vez haya tenido lugar una transacción, la base de datos debe hallarse en un estadoconsistente - un estado nuevo si la transacción ha tenido éxito o, si la transacción ha fracasado, elmismo estado consistente en el que se hallaba antes de que se iniciase la transacción.

La consistencia también es una característica de la administración de transacciones ofrecida

por SQL Server. Si los datos son consistentes y las transacciones mantienen la consistencia lógica yla integridad de los datos, SQL Server asegura la consistencia de los datos después de unatransacción. Aislamiento

Aislamiento significa que los efectos de cada transacción son iguales que si esa transacciónfuera la única del sistema; en otras palabras, las modificaciones realizadas por una transacción estánaisladas de las realizadas por cualquier otra transacción concurrente. De esta manera, unatransacción no se verá afectada por un valor que haya sido modificado por otra transacción hastaque el cambio se confirme. Si una transacción fracasa, sus modificaciones no tienen ningún efecto,ya que los cambios se deshacen. SQL Server permite ajustar el nivel de aislamiento de lastransacciones. El comportamiento de aislamiento de una transacción depende del nivel deaislamiento especificado. Nota: Cuando se confirma una transacción todas sus modificaciones se convierten en parte permanente dela base de datos. Cuando una transacción se deshace los cambios se anulan y la base de datos funcionacomo si la transacción nunca hubiera tenido lugar Durabilidad

La última propiedad ACID es la durabilidad. Durabilidad significa que, una vez confirmadauna transacción, sus efectos son permanentes en la base de datos, incluso en caso de fallo delsistema. El registro de transacciones de SQL Server y las copias de seguridad de la base de datosproporcionan la durabilidad. Si SQL Server, el sistema operativo, o un componente del servidorfallan, la base de datos se recuperará de manera automática cuando SQL Server se reinicie. SQL Serutiliza el registro de transacciones para repetir las transacciones confirmadas que fueron afectadaspor la caída del sistema y para deshacer las transacciones no confirmadas.

Si falla una unidad de disco y se pierden o se deterioran los datos se puede recuperar la base

de datos mediante las copias de seguridad de la base de datos y del registro de transacciones. Si se

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

38 de 47 24/05/2011 11:50 p.m.

Page 39: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

concluirlas.

MODOS DE TRANSACCIONES

Las transacciones pueden comenzar en tres modos diferentes: confirmación automática,explícita o implícita. El modo predeterminado de SQL Server es confirmación automática. Seexaminará el significado de cada uno de estos modos. Modo de confirmación automática

En el modo de confirmación automática cada instrucción T-SQL se confirma cuandoconcluye; con este modo no hacen falta instrucciones adicionales para controlar las transacciones.

En otros términos, cada transacción consiste en una única instrucción de T-SQL. El modo de

confirmación automática resulta útil cuando se ejecutan instrucciones mediante la línea decomandos interactiva, mediante OSQL o el Analizador de consultas de SQL Server, ya que no hayque preocuparse de iniciar y concluir explícitamente cada transacción. Se sabe que cada instrucciónserá tratada por SQL Server como una transacción en sí misma y se confirmará en cuanto concluya.Cada conexión a SQL Server utilizará el modo de confirmación automática hasta que se comienceuna transacción explícita utilizando BEGIN TRANSACTION o hasta que se especifique el modoimplícito. Una vez concluida la transacción explícita o desactivado el modo implícito, SQL Servervolverá al modo de confirmación automática. Modo de transacciones explícitas

El modo de transacciones explícitas es el utilizado con más frecuencia al programaraplicaciones y en los procedimientos almacenados, los desencadenadores y las secuencias decomandos. Cuando se ejecuta un grupo de instrucciones para llevar a cabo una tarea puede que hagafalta determinar los puntos en que debería iniciarse y concluir la transacción de modo que todo elgrupo de instrucciones tenga éxito o se deshaga. Cuando se identifica de manera explícita elcomienzo y el final de una transacción se utiliza el modo de transacciones explícitas y la transacciónse denomina transacción explícita. Una transacción explícita se especifica utilizando instruccionesT-SQL o funciones API.

El uso de transacciones explícito cuando la tarea consta de varios pasos, también resulta

beneficioso porque, aunque no se especifiquen instrucciones ROLLBACK, SQL Server deshace demanera automática las transacciones cuando se produzca un error grave, como una interrupción delas comunicaciones en la red, la caída de un sistema de bases de datos o de cliente o uninterbloqueo. La instrucción T-SQL empleada para comenzar una transacción es BEGINTRANSACTION. Se especifica el final de la transacción mediante COMMIT TRANSACTION oROLLBACK TRANSACTION. De modo opcional se puede especificar un nombre para latransacción en la instrucción BEGIN TRANSACTION, y hacer referencia a ella por su nombre enlas instrucciones COMMIT TRANSACTION o ROLLBACK TRANSACTION. La sintaxis de estastres instrucciones es la siguiente:

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

39 de 47 24/05/2011 11:50 p.m.

Page 40: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

@nombre_variable_transacción | nombre_punto_de_almacenamiento | @nombre_variable_punto_de_almacenamiento ]

Como ya se ha indicado, una transacción confirmada es una en la que todas las

modificaciones realizadas por la transacción se transforman en parte permanente de la base dedatos. Antes de poder confirmar una transacción se escriben en el registro de transacciones de labase de datos un registro de sus modificaciones y un registro de confirmaciones. Por tanto, lasmodificaciones que forman parte permanente de la base de datos pueden hallarse en dos ubicacionesposibles: o se escriben realmente en el disco y, por tanto, se hallan literalmente en la base de datos, oen la caché de datos y el registro de transacciones puede rehacerla en caso de fallo para que latransacción no se pierda.

Todos los recursos empleados por una transacción, como los bloqueos, se liberan en cuanto

la transacción se confirma. Una transacción se confirma con éxito si cada una de sus instruccionestiene éxito. A continuación se ofrece una pequeña transacción explícita denominada update_state,que actualiza el valor de la columna state a XX en la tabla publishers para que todos los editoresque tengan el valor NULL en esa columna:

USE pubsGOBEGIN TRAN update_state,UPDATE publishers SET state = 'XX’WHERE state IS NULLCOMMIT TRAN update_stateGO

Si se ejecuta esta transacción se verá que hay dos columnas afectadas. Para devolver la tabla

a su estado original (como si hubiera tenido lugar una cancelación en lugar de la confirmación) hayque ejecutar la transacción siguiente:

USE pubsGOBEGIN TRAN undo_update_stateUPDATE publishers SET state = NULLWHERE state = 'XX’COMMIT TRAN undo_update_stateGO

Una vez más, se debe ver que quedan afectadas dos filas. SQL Server ignora los nombres de

transacción update_state y undo_update_state empleados con COMMIT TRAN -los nombres de lastransacciones sólo sirven de ayuda al programador para identificar la transacción que se confirma.SQL Server confirma de manera automática la última transacción sin confirmar que hayacomenzado antes de la confirmación, independientemente de sí se ha especificado un nombre parala transacción. Creación de transacciones anidadas

SQL Server permite transacciones anidadas, o transacciones situadas dentro de otras

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

40 de 47 24/05/2011 11:50 p.m.

Page 41: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

instrucción COMMIT por cada transacción, SQL Server no confirma realmente las transaccionesinteriores hasta que haya confirmado con éxito la transacción exterior; al mismo tiempo, SQL Serverlibera todos los recursos empleados por las transacciones interiores y por la exterior. Si latransacción exterior no logra confirmar, ninguna de las transacciones interiores se confirma, y sedeshacen tanto la transacción exterior como las interiores. Si la transacción exterior se confirma, seconfirman todas las transacciones interiores. En otros términos, SQL Server básicamente ignoratodas las instrucciones COMMIT de las transacciones anidadas interiores, en el sentido de que lastransacciones interiores no se confirman y, en vez de eso, espera a la confirmación o cancelaciónfinal de la transacción exterior para determinar el estado de terminación de todas las transaccionesinteriores.

Además, en las transacciones anidadas, si se ejecuta una instrucción ROLLBACK en el

interior de la transacción exterior o de alguna de las transacciones interiores, se deshacen todas lastransacciones. Modo de transacciones implícitas

En el modo de transacciones implícitas las transacciones comienzan automáticamentesiempre que se emplean determinadas instrucciones de T-SQL y continúan hasta que se concluyende manera explícita con una instrucción COMMIT o ROLLBACK. Si no se especifica unainstrucción de conclusión, la transacción se deshará cuando el usuario desconecte. Las siguientesinstrucciones comienzan transacciones nuevas en el modo de transacciones implícitas: · ALTER TABLE· CREATE· DELETE· DROP· FETCH· GRANT· INSERT· OPEN· REVOKE· SELECT· TRUNCATE TABLE· UPDATE

Cuando se emplea una de estas instrucciones para comenzar una transacción implícita, latransacción continúa hasta que se concluye de manera explícita, aunque se ejecute otra de estasinstrucciones en el interior de la transacción. Una vez confirmada o cancelada de manera explícitacita la transacción, se comienza una nueva transacción en la siguiente en que se emplea de estasinstrucciones. Este proceso continúa hasta que se desactiva el modo de implícitas.

Para dejar el modo de transacciones implícitas de transacciones en ON, se puede emplear el

siguiente comando de T-SQL:

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

41 de 47 24/05/2011 11:50 p.m.

Page 42: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

Las transacciones implícitas resultan útiles cuando se ejecutan secuencias de comandos que

llevan a cabo modificaciones de los datos que hay que proteger dentro de la transacción. El modo detransacciones implícitas se puede activar al comienzo de la secuencia de comandos, llevar a cabo lasmodificaciones necesarias y luego desactivar el modo al final de la secuencia. Para evitar problemasde concurrencia conviene desactivar el modo de transacciones implícitas después de finalizar lasmodificaciones de los datos y antes de explorarlos. Si la instrucción que sigue a una confirmación esuna instrucción SELECT, comenzará una nueva transacción en modo de transacciones implícitas, ylos recursos no se liberarán hasta que se confirme esa transacción. Puntos de almacenamiento

Se puede evitar tener que deshacer toda la transacción empleando un punto dealmacenamiento para deshacer hasta cierto punto de la transacción, en lugar de llegar hasta elcomienzo de la transacción.

Todas las modificaciones producidas hasta el punto de almacenamiento siguen siendo válidas

y no se deshacen, pero las instrucciones ejecutadas después del punto de almacenamiento (que debeespecificarse en la transacción) y hasta la instrucción ROLLBACK se deshacen. Las instruccionessituadas a continuación de la instrucción ROLLBACK seguirán ejecutándose. Si se deshaceposteriormente la transacción sin especificar un punto de almacenamiento, todas las modificacionesse anulan hasta el principio de la transacción, como siempre; se deshace toda la transacción. Hayque tener en cuenta que cuando se deshace una transacción hasta un punto de almacenamiento,SQL Server no libera los recursos bloqueados. Se liberan cuando se confirma la transacción o alproducirse una cancelación de toda la transacción.

Para especificar un punto de almacenamiento en una transacción se utiliza la instrucción

siguiente:

SAVE TRAN[SACTION] {nombre_de_punto_de_almacenamiento | @variable_nombre_de_punto_de_almacenarniento}

Se añade de un punto de almacenamiento en la transacción en la posición hasta la que se

desea deshacer. Para deshacer hasta el punto de almacenamiento hay que utilizar ROLLBACKTRAN con el nombre del punto de almacenamiento, como se muestra a continuación:

ROLLBACK TRAN nombre_del_punto_de_almacenamiento

Se pueden tener más instrucciones de T-SQL después de la instrucción ROLLBACK paracontinuar la transacción. Hay que recordar incluir una instrucción COMMIT u otra instrucciónROLLBACK después de la primera instrucción ROLLBACK para que se complete toda latransacción. BLOQUEO DE TRANSACCIONES

SQL Server utiliza un objeto denominado bloqueo para evitar que varios usuarios realicen

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

42 de 47 24/05/2011 11:50 p.m.

Page 43: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

adquieren por la conexión de usuario. Cuando un usuario adquiere (o posee) un bloqueo sobre unrecurso ese bloqueo indica que el usuario tiene derecho a utilizar ese recurso. Entre los recursos quese pueden bloquear por los usuarios están las filas de datos, las páginas de datos, las extensiones(ocho páginas), las tablas y toda la base de datos en su conjunto. Por ejemplo, si un usuario tiene unbloqueo sobre una página de datos, los demás usuarios no pueden realizar ninguna operación en esapágina que puedan afectar a las que realice el usuario que posee el bloqueo. Por tanto, un usuario nopodrá actualizar ninguna página de datos que se halle bloqueada por otro usuario que la estérecuperando. Tampoco puede adquirir un bloqueo que pueda entrar en conflicto con otro bloqueo yaestablecido por otro usuario. Por ejemplo, dos usuarios no pueden tener al mismo tiempo bloqueospara actualizar la misma página. El mismo bloqueo no puede emplearlo más de un usuario.

La gestión de bloqueos de SQL Server adquiere y libera los bloqueos de manera automática

de acuerdo con las acciones de los usuarios. No hace falta que el administrador de la base de datosni el programador realicen ninguna acción. No obstante, se pueden utilizar sugerencias deprogramación para indicar a SQL Server el tipo de bloqueo que se va a adquirir al realizar unaconsulta modificación de la base de datos.

En este apartado se examinarán los niveles de granularidad de los bloqueos y los modos de

bloqueo. Pero, en primer lugar, se examinarán algunas de las características de gestión de' mejoranel rendimiento de SQL Server. Niveles de bloqueo

Los bloqueos pueden adquirirse sobre varios recursos diferentes; el tipo de recurso determinael nivel de granularidad del bloqueo. La siguiente tabla muestra los recursos que puede bloquearver, ordenados desde el nivel más fino de granularidad de bloqueo hasta el más basto.

Recursos bloqueables

Recurso Tipo de bloqueo Descripción

Identificador de fila Nivel de fila Bloquea una sola fila de una tabla Clave Nivel de fila Bloquea una sola fila de un índice Página Nivel de página Bloquea una página de 8-KB de una tabla o de un índice Extensión Nivel de extensión Bloquea una extensión, un grupo de ocho páginas contiguas de

datos o de índices Tabla Nivel de tabla Bloquea toda una tabla Base de datos Nivel de base de datos Bloquea toda una base de datos

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

43 de 47 24/05/2011 11:50 p.m.

Page 44: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

ejemplo la concurrencia aumenta porque se permite a más usuarios tener acceso a varias páginas o avarias filas de una tabla de manera simultánea. En este caso también se incrementa la sobrecarga, yaque se necesitan más bloqueos cuando se tiene acceso por separado a muchas filas o a muchaspáginas.

SQL Server selecciona de manera automática el tipo de bloqueo apropiado para la tarea en

cuestión, al tiempo que minimiza la sobrecarga de bloqueos. SQL Server también determina demanera automática el modo de bloqueo de cada transacción que implica a un recurso bloqueado;estos modos se tratarán a continuación. Modos de bloqueo

Los modos de bloqueo especifican el modo en que usuarios concurrentes (o transaccionesconcurrentes) pueden tener acceso a los recursos. Cada tipo de recursos se puede adquirir en algunode estos modos.

Se dispone de seis modos de bloqueo: compartido, actualización, exclusivo, tentativo,

esquema y actualización masiva. Compartido

El modo de bloqueo compartido se utiliza para operaciones sólo de lectura como las que sellevan a cabo utilizando la instrucción SELECT. Este modo permite que las transaccionesconcurrentes lean el mismo recurso de manera simultánea, pero no le permite modificarlo a ningunade ellas. Los bloqueos compartidos se liberan en cuando la lectura ha concluido a menos que sehaya definido el nivel de aislamiento como lectura repetible o superior, o si se ha especificado en latransacción una sugerencia de bloqueo que anula este comportamiento. Actualización

El modo de bloqueo de actualización se utiliza cuando cabe la posibilidad de que se ejecuteuna actualización del recurso.

Sólo una transacción puede obtener un bloqueo de actualización en un momento dado. Si la

transacción realiza una modificación (porque, por ejemplo, la condición de búsqueda ha hallado filasque modificar), el bloqueo de actualización se transforma en bloqueo exclusivo (que se describe acontinuación); en caso contrario, se transforma en bloqueo compartido.

Exclusivo

El modo de bloqueo exclusivo se utiliza para operaciones que modifican los datos, como lasactualizaciones, las inserciones y las eliminaciones. Cuando una transacción establece un bloqueo

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

44 de 47 24/05/2011 11:50 p.m.

Page 45: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

De intención

El modo de bloqueo de intención se emplea para establecer una jerarquía de bloqueos. Porejemplo un bloqueo de intención en el nivel de las tablas indica que SQL Server pretende adquirirbloqueo sobre una o varias páginas o filas de esa tabla. Generalmente, si una transacción adquirir unbloqueo exclusivo sobre un recurso, SQL Server comprueba en primer lugar algún bloqueo deintención sobre el mismo. Si una transacción ha establecido un bloqueo de intención que estáesperando ese recurso, la segunda transacción no puede adquirir el bloqueo exclusivo. Si no hayninguna transacción que mantenga un bloqueo de intención y esté esperan recurso, la transacciónpuede adquirir el bloqueo exclusivo sobre él. Hay tres tipos de bloqueo de intención que son lossiguientes: · Compartido de intención: Indica que una transacción pretende establecer un bloqueo

compartido sobre el recurso. · Exclusivo de intención: Indica que una transacción pretende establecer un bloqueo exclusivo

sobre el recurso. · Compartido de intención exclusivo: Indica que una transacción pretende establecer un

bloqueo compartido sobre algunos recursos y un bloqueo exclusivo sobre otros. Esquema

El modo de bloqueo de esquema se utiliza cuando se ejecuta una operación de cambio delesquema de una tabla, como la adición de una columna a una tabla, o cuando se compila unaconsulta. Hay dos tipos de bloqueo de esquema para estos casos: modificación de esquema (SchemaModification, Sch-M) y estabilidad de esquema (Schema Stability, Sch-S). Se emplea un bloqueo demodificación de esquema cuando se lleva a cabo alguna operación de tabla del lenguaje dedefinición de datos (Data Definition Language, DDL). Los bloqueos de estabilidad de esquema seemplean para compilar las consultas. Cuando se compila una consulta las demás transaccionespueden ejecutar y adquirir simultáneamente bloqueos sobre la tabla, incluso bloqueos exclusivos,pero no se pueden ejecutar instrucciones DDL sobre la tabla cuando hay un bloqueo de estabilidaddel esquema. Actualización masiva

El modo de bloqueo de actualización masiva se utiliza cuando se copian masivamente datos auna tabla con la sugerencia TABLOCK especificada o cuando se define la opción table lock on bulkload empleando sp_tableoption. El objetivo del bloqueo de actualización masiva es permitir losprocesos que copian datos masivamente en la misma tabla de manera concurrente mientras se evitael acceso a esa tabla de los procesos que no llevan a cabo una copia masiva. BLOQUEOS E INTERLOQUEOS

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

45 de 47 24/05/2011 11:50 p.m.

Page 46: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

trate de la mejor manera posible; aquí sólo se describirán para que se tenga noticia de su existencia ypara que se comprendan los conceptos correspondientes. Evitar y resolver los problemas debloqueos y de interbloqueos es responsabilidad del programador.

Los bloqueos tienen lugar cuando una transacción mantiene un bloqueo sobre un recurso y

otra transacción solicita un tipo de bloqueo sobre ese recurso que entra en conflicto con el existente.La segunda transacción debe esperar a que la primera libere su bloqueo - en otros términos, quedaatascada o bloqueada por la primera transacción. Los bloqueos suelen producirse cuando unatransacción mantiene un bloqueo durante mucho tiempo, lo que genera una cadena de transaccionesatascadas que esperan que concluyan otras transacciones para poder obtener los bloqueossolicitados - una situación denominada bloqueo en cadena. La siguiente figura muestra un ejemplode bloqueo en cadena.

Los interbloqueos se diferencian de las transacciones bloqueadas en que implican a dos

transacciones bloqueadas que se esperan la una a la otra. Por ejemplo, supóngase que unatransacción mantiene un bloqueo exclusivo sobre Tabla 1 y otra mantiene un bloqueo exclusivosobre Tabla 2. Antes de que se libere cada uno de los bloqueos exclusivos la primera transacciónsolicita un bloqueo sobre Tabla 2 y la primera transacción solicita un bloqueo sobre Tabla 1. Desdeese momento cada transacción espera a que la otra libere su bloqueo exclusivo, pero ninguna lo va aliberar hasta que no se produzca una confirmación o cancelación que complete la transacción. No sepuede completar ninguna de las transacciones porque para continuar solicitan un bloqueo quemantiene la otra transacción: ¡interbloqueo! La figura siguiente ilustra esta situación. Cuando seproduce un interbloqueo bloqueo SQL Server termina una de las transacciones, que habrá quevolver a ejecutar.

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

46 de 47 24/05/2011 11:50 p.m.

Page 47: ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000

ADMINISTRACIÓN AVANZADA DE MS SQL SERVER 2000 http://usuarios.multimania.es/cursosgbd/UD7.htm

47 de 47 24/05/2011 11:50 p.m.