La migración de un schema en SQL Server es una tarea que cualquier DBA se puede encontrar. Este tipo de cambio, se suele producir por varias razones. Desde cambios en la estructura de la aplicación, hasta por motivos de seguridad. Veamos como gestionar este cambio.
En SQL Server, por defecto todas las tablas y objetos que hagamos, se crean sobre «dbo». Este schema es el schema por defecto de SQL Server. Si en el momento de crear una tabla, u otro objeto de base de datos, no definimos ningún schema, por defecto se creará sobre dbo. Por ejemplo:
USE [db_test] GO -- Creación de tabla dbo SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [table_dbo]( [NOMBRE] [varchar](20) NULL, [APELLIDO] [varchar](20) NULL ) ON [PRIMARY] GO
Si posteriormente, mediante SSMS comprobamos que se ha creado la tabla, observaremos que se ha añadido «dbo.» delante del nombre de la tabla.
Normalmente con este schema es suficiente, pero si queremos tener un mayor control sobre las tablas u objetos, o facilitar su gestión, podremos creer uno o varios schemas.
Un schema nos permite:
- Tener más control sobre la seguridad de la base de datos.
- Gestionar de forma más rápida accesos y permisos, a un nuevo empleado.
- Modificar permisos en varias tablas u objetos a la vez.
Migración de un schema en SQL Server en un entorno real
Pongamos un ejemplo. En una empresa con una base de datos SQL Server, cada departamento (recursos humanos, ventas, informática…) tienen sus propias tablas independientes del resto de departamentos y no son visibles entre sí. Si se incorpora una persona de RRHH, el DBA tendrá que otorgar permisos sobre todas las tablas de su departamento. Si tiene 50 tablas dicho departamento, deberá hacerlo una a una.
Para facilitar esta tarea, podremos crear un schema que se llame «rrhh»:
USE [db_test] GO CREATE SCHEMA [rrhh] GO
y crear en él todas las tablas. Para ello debemos migrar un schema (o varios objetos del mismo), en este caso «dbo», al nuevo schema «rrhh«. La migración de un schema en SQL Server puede ser tediosa, pero con la ayuda de este script, nos facilitará bastante la tarea:
USE [db_test] GO SELECT 'ALTER SCHEMA rrhh TRANSFER ' + s.Name + '.' + o.Name FROM sys.Objects o INNER JOIN sys.Schemas s on o.schema_id = s.schema_id WHERE s.Name = 'dbo' And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
Este script, se ejecuta en la base de datos en la que están originalmente las tablas con el schema «dbo». Se encargará de generar la consulta SQL Server necesaria para migrar una tabla del schema «dbo» (original) al nuevo schema (rrhh). Este script sólo genera la consulta, pero no la ejecuta. Posteriormente tendremos que copiar el código generado. El resultado, debería ser un script similar a este, por cada tabla:
ALTER SCHEMA rrhh TRANSFER dbo.table_dbo
Con este código, podremos migrar todas las tablas que necesitemos al nuevo schema. Después de haber hecho esto, tendremos que:
1 – Crear un rol
USE [db_test] GO CREATE ROLE [rrhh_rol] GO
2- Hacemos al rol, owner del schema «rrhh»
USE [db_test] GO ALTER AUTHORIZATION ON SCHEMA::[rrhh] TO [rrhh_rol] GO
3 – Crear un login en el servidor para el usuario nuevo
USE [master] GO CREATE LOGIN [gpsos_rrhh] WITH PASSWORD=N'gpsos', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO
4 – Creación del usuario de base de datos asociado al login
USE [db_test] GO CREATE USER [gpsos_rrhh] FOR LOGIN [gpsos_rrhh] WITH DEFAULT_SCHEMA=[rrhh] GO
5 – Añadimos el usuario al rol
ALTER ROLE [rrhh_rol] ADD MEMBER [gpsos_rrhh] GO
Después de realizar todos estos pasos, si accedemos con el nuevo usuario a la base de datos, únicamente tendremos acceso a las base de datos del schema rrhh. También estarán migradas las tablas de dbo a rrhh.
Si queremos crear un nuevo usuario para RR.HH., tan solo habría que realizar los pasos del 3 al 5 con los datos del nuevo usario.
Esperamos que esta entrada os haya sido útil. Si necesitas ayuda con los logins, aquí, podrás ver todas nuestras entradas sobre los logins en SQL Server.
Nos vemos en próximas entradas.
Un saludo,
Equipo de Base de datos
