Curso SQL Server administration

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)