Diferencia entre revisiones de «Curso SQL Server administration»

Última revisión de 10:17 24 mar 2012

Información de apoyo

WEB: red_gate para aplicaciones de ayuda en sql server.

Ayuda para monitorización: SQL Server central [1] HAY LIBROS GRATIS!!

FICHEROS DE BASES DE DATOS

Se pueden crear varios ficheros de bases de datos y se puede hacer separación de las tablas para que estén en diferentes ficheros, o incluso se puede hacer particionado de tablas para que los datos estén en dos ficheros diferentes. Esto puede servir para mejorar el rendimiento de la tabla, o incluso para hacer bacukp de datos nuevos y no hacer siempre backups de datos antiguos.

COPIADO DE TABLAS

Se puede hacer un copiado de tablas simplemente ejecutando

SELECT * INTO <tabla_destino> FROM <tabla.origen>

TRABAJO CON FICHEROS DE BASE SE DATOS

Con el comando ALTER DATABASE <databasename> se puede trabajar con los ficheros que tiene una DB. Se puede cambiar tamaño, renombrar ficheros, etc.

También se pueden borrar ficheros, pero antes hay que hacer un srink especial, diciendo que libere de tablas ese fichero

DBCC SRINKFILE (N'<nombre_fichero>, EMPTYFILE);

Cuidado con este comando que borrar datos que hay en ese fichero. Si tiene datos hay que moverlos manualmente a otro fichero.

cHEQUEO DE TABLAS

Se puede ver el comando DBCC CHECKDB; para hacer un checkeo del estado de una base de datos. Tiene parámetros para hacer reparaciones y más cosas. (consultar documentacion)

TRABAJO CON FILEGROUPS

Se pueden crear diferentes grupos de ficheros donde le diremos a las tablas donde ser almacenadas. Se suele usar un solo grupo de ficheros, pero tiene ventajas usar varios, por ejemplo almacenar tablas a otros filegrups en discos físicos diferentes (por ejemplo tablas de historicos en discos más lentos o remotos).

Para ver los filegroups

select * from sys.filegroups

Para decir que filegroup es el por defecto, usamos:

alter database <databasename> modify filegroup <filegroupname> DEFAULT;

Si quieres crear una tabla en un filegroup que no es el default se añade al CREATE TABLE la clausula ON <filegroupname> al final.

NOTA: Libro para consultar SQL INTERNALS explicando arquitectura y funcionamiento interno de sql server

Backups

Existe el tipo de backup "copy only" que hace una copia de las bases de datos sin afectar a la secuencia. Esto está bien para hacer copias de producción a desarrollo...

Se pueden hacer backups APPEND al mismo fichero, y cuando vayamos a hacer la restauración nos deja elegir en que punto se puede hacer la restauración. Para una restauración al punto más reciente es más cómodo porque no tienes que especificar todos los ficheros para hacer la restaruación, solo especificando un fichero ya te coge los full, los diferenciales y los transacionales.

También se puede mezclar el hacer un full de fin de semana, un delta cada día y en medio transacionales (A PROBAR EN NUESTRO SQL SERVER)

Recovery

Cuando se hace un restore, si va a implicar hacer varios hasta llegar al punto deseado (full y transacicional por ejemplo) el premer restore se hace with norecovery, y cuando se hace el último transaccinoal se hace with recovery, que habilita la base de datos para ser usada ya. Por defecto, si no se pone nada hace el reover.

Bases de datos de sistemas

Hay que hacer backup de todas las bases de datos de sistemas menos de la tempdb. Se hace backup de forma normal. Exite una herramienta que se llama SQLCMD. Si arrancamos en el modo MONO USUARIO, podemos usar con esta herramienta para intentar corregir algún herror que se hay aproducido en la base de datos y por el que no arranque en modo normal.

Para arrancar en modo mono-usuario se pueden ver las opicones de arranque en el sqlserver.exe /?

Integration services

Es el servicio ETL de SQL Services

REcomendaciones antes de hacer volcados de datos

• Desactivar índices
• Desactivar constraints

Para desactivar las constraints ALTER TABLE tablename NOCHECK CONSTRAINT constraintname; Para volver a activarlas ALTER TABLE tablename CHECK CONSTRAINT constraintname

Para activar pero ademas comprobando que ningún campo incumple la constraint: ALTER TABLE tablename WITH CHECK CHECK

CONSTRAINT constraintname;

Hemos hecho un ejemplo de ETL. Para orientarme, es muy muy parecido a la creación de ETL's en Websphere DataStage

Para consultar scripts en vb y en powershell para la adminstración de sql server (u otras plataformas) podemos 

consultar la página de microsoft script center: http://technet.microsoft.com/en-us/scriptcenter/bb410849

Bulk Insert

Para hacer este ejemplo hemos usado el comando BCP

bcp <dbtable|query> <in|out|...> <fichero.xml> opciones

por ejemplo

bcp MarketDev.DeirectMarketing.ExchangeRate in ExchangeRates.csv -T -f ExchangeRates.xml -S proseware

También se puede hacer bulk insert desde SQL Server con el comando BULK INSERT, haciendo referencia a un fichero (en este caso de tipo fmt, que es un formato especial de bulk insert)

BULK INSERT tabla FROM '<fichero_CSV>' WITH ( FORMATFILE='<fichero_fmt>', BATCHSIZE=200, FISTROW=2 );

otra forma de hacer los bulk es usando la clausula OPENROWSET.

SELECT * FROM OPENROWSET ( BULK '<fichero_csv>',FORMATFILE = '<fichero_fmt>', FIRSTROW = 2) AS a;

asi que podemos hacer un

INSERT INTO <tabla> SELECT * FROM OPENROWSET ...

Seguridad

Autenticación y autorización

Para crear un usuario

CRATE LOGIN <login_name> FROM WINDOWS;

Se pueden lanzar consolas de powershell desde el management studio para trabjar con sql server en modo consola

También se puede crear un login de grupo de windows. Esto es, que el login no es un usuario en concreto sinó que le das permiso a logarse a cualquier usuario del grupo.

Una vez que tenemos el login creado tendremos que crear un usuario a partir de ese login en cada base de datos. Desde la pantalla de configuración de un login se puede MAPEAR un login a varias bases de datos. Cada mapeo de esos es un user.

IMPORTANTE: Delegación de login, Una aplicación que se ejecuta con un usuario concreto puede usar unas credenciasles suministradas de otro usuario para logarse en la DB. De esta forma se controlan los permisos de acceso a datos no solo desde aplicación, también desde DB

Problemas de usuarios huerfanos

El problema está en el sid que tiene el usaurio en la base de datos, que no coincide con el sid del login. Esto se puede ver consultando de la siguiente forma:

SELECT name, principal_id, sid FROM sys.server_principals WHERE name='<nombre_usuario>'; SELECT name, principal_id, sid FROM sys.database_principals WHERE name='<nombre_usuario>';

A veces intentamos crear un usaurio nuevo en la DB con un login que hemos creado nuevo, pero esto no funciona, poruqe ya existe un usuario con el mismo nombre en la DB. Lo que hay que hacer es ALTER del usuario que ya hay creado para que coja el nuevo login.

ALTER USER <usuario_db> WITH LOGIN = <login>

De esta forma se podría cambiar el user de la DB dbo con un login en concreto.

Existe un procedimiento almacenado ya creado por defecto para corregir estos problemas (BUSCAR)

Con esto podemos ver con que la tabla sys.server_pincipals se ven los logins y con sys.database_principals los usuarios asignados a una db

Los esquemas son como en Oracle. Agrupaciones de tablas. Los roles principales tienen su esquemaa (BUSCAR INFO)

Podemos crear roles nuevos para nuestra db, en la que damos permisos a esquemas o tablas u objetos en concreto

También tenemos los roles de aplicacioin, que son usdos para habilitar permisos para usuarios solo cuando están ejecutando una aplicacion en particular. (BUSCAR INFO)

Auditorias

Activar C2 audit Mode

Se pueden usar auditorias modo C2 (TCSEC). Se habilita la variable c2 audit mode con el sp_configure. Con este flag

se empieza a auditar muchas cosas segun ese modo de auditoría.

Common criteria audit option

se ahabilitar via sp_configure solo en enterprise y datacenter. Ofrece residual information protection (rip),

estadistica

Trigger para auditoría

Para auditar ciertos elementos de una db se pueden usar triggers, que hagan inserts en tablas preparadas para esto.

NOTA, se pueden crear varios campos con datos por defecto variables:

userchanged sysname NOT NULL default (USER_NAME) datechanged datetime2 NOT NULL default (SYSDATETIME)

Esta forma de auditoría es muy primitiva, pero efectiva para ciertos casos. Por supuesto hay que controlar muy bien

el donde se aplica ya que puede cargar el sistema.

Sobre triggers

Hay triggers AFTER e INSTEAD (se ejecuta el trigger en vez de la operación en si). Ambos tipos tambien pueden ser de 

tipo CLR, que enlazan con código .NET. 

Profiler

Con este programa se pueden trazar códigos de SQL Server,

Servidores de auditoría

Guarda info de sucesos en sql server. Utiliza service blocker. Se definie con el comando:

CREATE SERVER AUDIT MarkDevLog TO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

También se puede definir bases de datos para autitoría

CREATE DATABASE AUDIT SPECIFICATION MarketingSelectSpec FOR SERVER AUDIT MarkDevLog ADD (SELECT ON SCHEMA::Marketing

BY public);

El servidor realmente audita todo pero con el CREATE DATABASE AUDIT decimos que es lo que queremos que se guarde de

una base de datos.

En el ejemplo que ponemos guarda lOS mensajes de auditoriá al log de eventos de aplicación de windows. Se podría

cambiar el destino de los logs. Lo bueno de mandar los logs de auditoría a un fichero es que después se pueden leer

esos ficheros con sentencias SELECT

AUTOMATIZANDO EL MANTENIMIENTO

Powershell

Existe un provider especial para manejar sql server desde powershell... ver info

Existen herramientas especificas para programar en powershell , PScode???

JOBS

Creación de jobs a través de ventana, tambien se puede exportar a código. Se compone de pasos, y se puede ver el

historico de ejecuciones de jobs.

Todo esta información está en la base de datos msdb.

msdb.sysjobs msdb.sysjobhistory

Credenciales

Un contenedor que lleva un usuario y password encriptados, para poder conectar a recursos externos.

CREATE CREDENTIAL Agent_Export WITH IDENTITY = N'VIENNA\Agent_Exports', SECRET = N'Pa$$w0rd

Se puede usar en conjunto con EKM

Proxy

Se puede configurar un proxy con una credencial dentro. Los proxys upeden ser para diferentes tipos de ejeucciones

de jobs. Dentro de un proxy va una credencial.

Database Mail

MONITORIZACION

Para ver que datos se pueden cosultar para monitorizar y que informacion recoger se puede buscar en la ayuda sobre

sys.dm_*

Fallos en DB

CheckDB

DBCC CHECKDB

Se usa para hacer comprobaciones básicas sobre las bases de datos sql server. Hay muchos parámetros para varios

tipos de comprobaciones.

Opciones de Repair

Tiene que estar en modo single_user ALTER DATABASE <database> SET SINGLE_USER WITH ROLLBACK IMMEDIATE; También se puede hacer en forma gráfica.

repair_rebuild repair_allow_data_loss

En los ejercidios de ejemplo hemos visto una DB que tiene un error en una página de datos. Eso hace que la tabla no devuelva todos los resultados pero si algunas filas de la tabla. Cuando haces el select dice la página que da error. Se puede hacer restauración a nivel de página (para restaurar esa parte de la tabla.)

INDICES

Hay diferentes tipos de indices. B-tree, Clustered y Nonclustered

Los indices clusterizados no tienen punteros, sin o que tienen datos, y solo se permite un indice de este tipo por tabla.