Mi primer Screencast!!! Espero que les guste mi primer screencast, trata sobre CTE recursivas (ver post anterior)
lunes, 12 de septiembre de 2011
Screencast: CTE recursivas en SQL Server
Etiquetas:
common table expression,
CTE,
recursión,
recursividad,
SQL 2008
La recursion es divina - Consultas recursivas en SQL
El lenguaje SQL no solía ser lo mas comodo para realizar consultas en donde participe el concepto de "recursión", pero claramente algunos problemas son muy incomodos si los abordamos de otra forma. Por suerte para nosotros, desde la aparición de las CTE, esto ha cambiado y ahora es "simple" realizar consultas recursivas, veremos un ejemplo de como podemos realizarlo.
CREATE TABLE #Empleados(idEmpleado INT, Nombre VARCHAR(10), idJefe INT) INSERT INTO #Empleados SELECT 0, 'Bob', null UNION SELECT 1, 'Tom', 0 UNION SELECT 2, 'Joe', null UNION SELECT 3, 'John', 2 UNION SELECT 4, 'Ringo', 1 UNION SELECT 5, 'Paul', 4 SELECT * FROM #Empleados
Esta tabla contiene los empleados de una empresa e indica sus respectivos jefes. Lo divertido es que los jefes son también empleados, por lo tanto son registros de la misma tabla.
Supongamos que quiero averiguar el nombre del jefe de cada empleado. Esto lo puedo realizar con un "self join" sin necesidad de recursividad.SELECT e.Nombre Empleado, j.Nombre Jefe FROM #Empleados e INNER JOIN #Empleados j ON e.idJefe=j.idEmpleado;
Pero ahora supongamos que quiero una consulta mas compleja, una que me retorne no el jefe directo, sino el jefe superior de cada empleado. Cualquiera que tenga conocimientos de algoritmos sabe que la mejor forma de responder a esta pregunta es con una estructura recursiva. No pretendo en éste artículo escribir que es la recursión, sino como utilizarla en SQL, así que daré por sentado que saben de que hablo.
Una estructura recursiva requiere básicamente de dos partes:
1) El caso base
2) El caso recursivo
Para escribir esto utilizaremos una CTE que tendrá estas dos partes, y deben unirse con un UNION ALL. La primer parte será el caso base (en el WHERE debo filtrar para que lo sea) y en la segunda el caso recursivo, el cual hace un JOIN con la CTE. Este join es lo que indica que es un caso recursivo. Nótese que defino la cte utilizandola a ella misma, por eso mismo es recursiva.
WITH cte AS( -- Caso base SELECT e.idEmpleado, e.Nombre Nombre, e.idEmpleado Jefe FROM #Empleados e WHERE e.idJefe IS NULL -- Fin Caso base UNION ALL -- Caso recursivo SELECT e.idEmpleado, e.Nombre Nombre, j.Jefe FROM #Empleados e INNER JOIN cte j ON e.idJefe=j.idEmpleado -- Fin Caso recursivo ) SELECT c.Nombre Empleado, j.Nombre Jefe FROM cte c INNER JOIN #Empleados j ON c.Jefe=j.idEmpleado DROP TABLE #Empleados
Con esto obtengo el código del jefe superior de cada uno, entonces en el SELECT lo cruzo con la tabla de empleados para obtener su nombre. Parece medio confuso pero es cuestión de acostumbrarse.
Hay algunos inconvenientes que pueden presentarse, pero eso lo comentaré en el próximo artículo.
Etiquetas:
common table expression,
CTE,
recursión,
recursividad,
SQL
jueves, 8 de septiembre de 2011
Como leer desde SQL los MeasureGroups de AS
Hace poco me pasó que necesitaba trabajar con los id de los MeasureGroups desde SQL, y me encontré con que las consultas que podía hacer retornaban siempre el nombre pero no el id. Por este motivo hice un script de powershell que me brinde la información y la cargue en una tabla. La parte de SQL la hice rápido y podría mejorarse con objetos de powershell pero lo importante es lo otro.
#Datos del ejemplo:
#Servidor de AS: AS1
#Base en AS: DB1
#Cubo: Cub1
$sqlServer = $args[0];
$dbName = $args[1];
$tbl = "mgRecolectadas"
# Recolecto la informacion del AS
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
[Microsoft.AnalysisServices.Server]$svr = new-Object([Microsoft.AnalysisServices.Server])
# Tomo uno como ejemplo
$svr.Connect("AS1")
$DatabaseID = "DB1"
$db = $svr.Databases.Item($DatabaseID)
$cubo = $db.Cubes.Item("Cub1")
#Variables donde armaré la cadena de SQL
$mgid = ""
$mgname = ""
$sql = ""
#Recorro la coleccion de MeasureGroups
foreach ($mg in $cubo.MeasureGroups){
#Obtengo el ID$mgid = $mg.id
$mgname = $mg.name
#Armo la cadena
if ($sql){
$sql = "$sql UNION "
}
$sql = "$sql
SELECT '$mgid' mg, '$mgname' mgname"
}
$svr.Disconnect()
## Establezco la conexion para guardar la info
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlServer;Initial Catalog=$dbName; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
# Borro lo anterior
$cmd.CommandText = "TRUNCATE TABLE $tbl"
$null = $cmd.ExecuteNonQuery()
echo "Table truncada"
# Agrego todos los registros nuevos
$cmd.CommandText = "INSERT INTO $tbl SELECT * FROM ($sql) t"
$null = $cmd.ExecuteNonQuery()
echo "Insercion finalizada"
$conn.Close()
Etiquetas:
Analisys Services,
MeasureGroups,
PowerShell,
SQL
miércoles, 24 de agosto de 2011
Restore de CDC en SQL 2008
El cdc es una feature muy buena del SQL2008 visto que de forma sencilla nos permite llevar un registro de los cambios en una tabla, tanto sea para cuestiones de auditoría, procesamientos parciales de información, etc.
Así mismo, cuando se trabaja con cdc es normal que se deseen probar, testear, desarrollar procedimientos que utilicen esta información y naturalmente no queremos que accedan al ambiente productivo hasta estar testeadas. Aquí es cuando nos topamos que si hacemos un restore de una base con cdc en otra instancia no aparecen las tablas correspondientes. Para evitar esto debemos incluir la opción WITH KEEP_CDC al momento de hacer restore. No se si será poco usada, pero no se asusten si no es coloreada en el Managment Studio, funciona a la perfección.
El código debería quedar algo así:
RESTORE DATABASE [AIELLO_DB] FROM DISK = N'D:\Backup\[AIELLO_DB][SQL2008].bak' WITH KEEP_CDC, FILE = 1, MOVE N'AIELLO_DB' TO N'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AIELLO_DB.mdf', MOVE N'AIELLO_DB_log' TO N'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AIELLO_DB_log.ldf', MOVE N'AIELLO_DB_cdc' TO N'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AIELLO_DB_cdc.ndf', NOUNLOAD, REPLACE, STATS = 10
Con esto tendrán la información para poder explotarla, pero no se activará la captura de datos. Si desean que esto ocurra hay que incluir los jobs correspondientes con sys.sp_cdc_add_job aplicando tanto 'capture' como 'cleanup'.
Saludos,
Andrés
Para mas información:
RESTORE Arguments (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms178615.aspx
sys.sp_cdc_add_job (Transact-SQL): http://msdn.microsoft.com/en-us/library/bb510626.aspx
Etiquetas:
CDC,
ChangeDataCapture,
restore,
SQL 2008
jueves, 11 de agosto de 2011
SQL2008: Entendiendo el Waitresource
Introduccion
Al ver los xml de bloqueos o deadlocks muchas veces aparecen números casi inentendibles, y uno de los importantes es el WAITRESOURCE. Este número codifica que objeto de la base de datos está participando en la pelea por el bloqueo. El recurso puede principalmente ser:
Al ver los xml de bloqueos o deadlocks muchas veces aparecen números casi inentendibles, y uno de los importantes es el WAITRESOURCE. Este número codifica que objeto de la base de datos está participando en la pelea por el bloqueo. El recurso puede principalmente ser:
- Una tabla
- Una página
- Una clave
Puede ser algunas cosas mas pero para simplificar el análisis nos enfocaremos en estos.
Manos a la obra
Lo primero que debemos hacer es identificar cual de las tres cosas es, lo cual es fácil leyendo el texto de wait resourse que puede ser:
“OBJECT: 19:1275867612:10”
“PAGE: 12:1:79868773”
“KEY: 12:397371816017920 (760119e06bff)”
En el primer caso es cuando se trata de una tabla. Para este caso (y para los siguientes) lo primero que debemos hacer es analizar el primer número antes del separador (:) . Este número en todos los casos indica el id de la base de datos, por lo tanto debo hacer la siguiente consulta para obtenerla:
SELECT * FROM sys.sysdatabases WHERE Dbid=19
Lo cual me da por resultado Mibase. Recordemos esto porque es común a los tres casos.
Ahora siguiendo con el análisis del primer caso la información brindada se debe analizar de la siguiente manera:
OBJECT: dbId:ObjectId:IndexId
Con la salvedad de que indexId vale 0 cuando se trata del heap y 1 cuando se trata de un índice cluster. En otro caso figurará el número del índice.
Para obtener esta información en un formato útil debería hacer las siguientes consultas:
-- Nota: debo estar situado en MiBase SELECT OBJECT_NAME(1275867612) -- O bien SELECT * FROM MiBase.sys.all_objects WHERE object_id = 1275867612 -- Y para buscar el índice en caso de ser mayor que 1: SELECT * FROM MiBase.sys.indexes WHERE object_id=1275867612De esta forma ya sabemos que table y que índice participaba en el bloqueo.
Para el caso 2 (página) comenzaremos averiguando la base de la misma forma y la siguiente información nos viene como:
PAGE: dbId:FileId:PageId
Si no tenemos nuestra base con varios archivos sino todo en uno del primary el segundo campo siempre será 1.
Para analizar el último debemos obtener la información de la página (79868773 en el ejemplo). Para esto tenemos dos formas:
DBCC TRACEON ( 3604 )
DBCC PAGE (12,1,79868773)
o
DBCC PAGE (12 , 1, 79868773) WITH TABLERESULTS,NO_INFOMSGS
En el primer caso es necesario el traceon porque sino no podemos ver la salida, la cual saldrá en modo texto. En el segundo no es necesario visto que la salida la veremos en modo tabla.
Sea cual fuera el caso que elegimos debemos buscar el renglon (o registro) que tenga la siguiente información:
m_objId (AllocUnitId.idObj) = 1051306955 m_indexId (AllocUnitId.idInd) = 7
y con esto obtengo el ObjectId para poder continuar mi análisis como en el caso anterior.
Por último nos queda el caso del índice (KEY: 12:397371816017920 (760119e06bff)). En este caso debemos leerlo como:
KEY: dbId:hObjecto (hash)
El hash puede ser ignorado, visto que no tenemos función para transformarlo (es la gracia de los hash!), así que nos quedaremos con el hObj. Este número se encuentra almacenado en la msdb así que lo podemos utilizar para calcular el objetcId:
SELECT * FROM MiBase.sys.partitions WHERE hobt_id = 397371816017920
De esta consulta obtenemos el object_id y podemos hacer nuestro análisis de siempre.
Etiquetas:
DBCC PAGE,
SQL 2008,
sys.partitions,
Waitresource
martes, 26 de julio de 2011
Traduciendo jobs names de SQL
Muchas veces estamos monitoreando en tiempo real nuestro SQL y hay cosas interesantes para observar. Este pequeño articulo es la puerta de entrada al de la semana que viene donde profundizaremos este tema.
Por ahora lo que quiero mencionar es simplemente un pequeño y usual problema . La forma mas simple es mediante el sp_who2, el cual en la columna BlkBy nos indicará si un proceso se encuentra proqueado y en la columna ProgramName podremos obtener que programa es la pobre víctima. Es muy importante que si este proceso fue lanzado por un job no veremos el nombre sino una secuencia extraña de números que debemos traducir, por ejemplo:
SQLAgent - TSQL JobStep (Job 0xF64F718235C7154DB6F21B5935D7218A : Step 1)Por ahora lo que quiero mencionar es simplemente un pequeño y usual problema . La forma mas simple es mediante el sp_who2, el cual en la columna BlkBy nos indicará si un proceso se encuentra proqueado y en la columna ProgramName podremos obtener que programa es la pobre víctima. Es muy importante que si este proceso fue lanzado por un job no veremos el nombre sino una secuencia extraña de números que debemos traducir, por ejemplo:
Para traducir esto debemos tomar la parte "numerica" del mensaje y copiarla en la siguiente consulta:
SELECT name FROM msdb.dbo.sysjobs WHERE job_id = CAST( 0xF64F718235C7154DB6F21B5935D7218A AS UNIQUEIDENTIFIER)así obtendremos el nombre del job en cuestión.
Con esta info ahora solo nos resta cruazarla con las tablas del sistema para saber si un spid en particular está molestando o no, pero como dije eso lo veremos la semana siguiente.
Saludos,
Andrés
lunes, 18 de julio de 2011
Cluster desde powershell
Cuando se trabaja con SQL en entornos de alta disponibilidad es muy comun trabajar con windows en cluster y tener que lidiar con varias tareas que involucran tanto sea al sql como al cluster.
La forma tradicional de como hacer esto es con el comando Cluster.exe, pero para esto deberíamos habilitar el cmd shell lo cual nunca es recomendable.
Por suerte desde 2008 tenemos de forma nativa en los jobs incorporar rutinas de powershell, pero hasta ese entonces la única salida era seguir acudiendo al cluster.exe.
Esto ha cambiado con la salida de Windows Server 2008 R2 (no confundir con SQL Server 2008 R2!!!), donde se ha incorporado un modulo de cluster al powershell que hace muy comoda su adminstración.
Para utilizar este modulo primero debemos entrar a nuestra consola de powershell y ejecutar el siguiente comando:
Import-Module FailoverClusters
Con esto se incorporarán las funciones de cluster y podremos utilizarlas. En mi caso me ha servido porque en conjunto con SQL 2008 puedo llamarlas desde un job de sql configurandolo con rutina powershell y
esto es flexible y seguro.
Les adjunto un link de como pasar las tareas de Cluster.exe a powershell:
Como usarlo: http://technet.microsoft.com/en-us/library/ee619751%28WS.10%29.aspx
Mapeo: http://technet.microsoft.com/en-us/library/ee619744%28WS.10%29.aspx
====================================================================
When working with SQL in high availability is very common to work over windows cluster and dealing with various tasks involving both sql server and the windows cluster.
The traditional way of how to do this is with the command Cluster.exe, but for this should enable the cmd shell it is never recommended.
Luckily since 2008 We have powershell natively in jobs routines, but the only way out was to keep going to the cluster.exe.
This has changed with the release of Windows Server 2008 R2 (not to be confused with SQL Server 2008 R2!!!), which has a powershell module of the cluster that makes it very comfortable its management.
To use this module must first enter our powershell console and run the following command:
Import-Module FailoverClusters
This will incorporate the functions of cluster and we use them. In my case I have served it in conjunction with SQL 2008 I can call them from a sql job of setting it powershell with routine and that is
flexible and secure.
I attached a link of how to change tasks Cluster.exe to powershell:
How to use: http://technet.microsoft.com/en-us/library/ee619751%28WS.10%29.aspx
Mapping: http://technet.microsoft.com/en-us/library/ee619744%28WS.10%29.aspx
The traditional way of how to do this is with the command Cluster.exe, but for this should enable the cmd shell it is never recommended.
Luckily since 2008 We have powershell natively in jobs routines, but the only way out was to keep going to the cluster.exe.
This has changed with the release of Windows Server 2008 R2 (not to be confused with SQL Server 2008 R2!!!), which has a powershell module of the cluster that makes it very comfortable its management.
To use this module must first enter our powershell console and run the following command:
Import-Module FailoverClusters
This will incorporate the functions of cluster and we use them. In my case I have served it in conjunction with SQL 2008 I can call them from a sql job of setting it powershell with routine and that is
flexible and secure.
I attached a link of how to change tasks Cluster.exe to powershell:
How to use: http://technet.microsoft.com/en-us/library/ee619751%28WS.10%29.aspx
Mapping: http://technet.microsoft.com/en-us/library/ee619744%28WS.10%29.aspx
jueves, 7 de julio de 2011
Restore de FILESTREAM
El filestream es una funcionalidad nueva del SQL2008 que a veces es discutida pero a mi ver es muy buena en muchos casos. En esta oportunidad me gustaría hablar sobre un caso interesante y es como hacer para restorear una base que tiene campos filestream pero que no ha sido diseñado pensando en esto. ¿A que me refiero? Uno puede diseñar la base desde un principio alojando las tablas que contienen archivos en filegroups diferentes para asegurarse que en un restore parcial no afecten, pero no siempre las cosas nacen en este orden, muchas veces las aplicaciones vienen de hace tiempo y "llegan" al sql 2008 y en la tabla que se encuentra el binario tambien se encuentra otra información indispensable para el funcionamiento del sisetema. En estos casos no restorear el filegroup en cuestión es lo mismo que no restorear nada. Para estos casos el filestream es una buena solución visto que en cierto modo puedo hacer un filegroup "vertical".
Supongamos una tabla "cliente" que tenga los campos id, nombre, apellido, Documento, siendo el último un VARBINARY(MAX). Si esta tabla la pongo en un filegroup diferente y hago un restore del resto obtendría error al realizar la siguiente consulta:
Supongamos una tabla "cliente" que tenga los campos id, nombre, apellido, Documento, siendo el último un VARBINARY(MAX). Si esta tabla la pongo en un filegroup diferente y hago un restore del resto obtendría error al realizar la siguiente consulta:
SELECT id, nombre, apellido FROM Cliente
Restore con PARTIAL
Ahora supongamos que mi campo base tiene definido FILESTREAM y el campo Documento se encuentra albergado de esa forma. En este caso ante un problema por el cual deseo recuperar mi base podría hacer lo siguiente:
RESTORE DATABASE [AIELLO_DBA] FROM DISK = N'D:\Prueba_fs.bak' WITH REPLACE, PARTIAL, RECOVERY
Y ahora la consulta antes mencionada funcionaría sin problemas, solamente obtendría un error si intento acceder al campo documento, como sería con la siguiente consulta:
SELECT id, nombre, apellido, Documento FROM Cliente
Msg 670, Level 16, State 1, Line 2
Large object (LOB) data for table "dbo.Cliente" resides on an offline filegroup ("AIELLO_DBA_FS") that cannot be accessed.
Lo que logré es poner mi base en linea en un tiempo mucho menor. Pero lamentablemente para recuperar todo debo volver a hacer el restore full en otro momento.
Restore operativo por partes
No todo está perdido!! como dice el dicho, hecha la ley hecha la trampa, entonces lo que hice es lo siguiente.
En mi backup nocturno hago lo siguiente:
-- ============================================= -- Author: Andrés Aiello -- Create date: 07/07/2011 -- Backup full poniendo readonly el filestream -- ============================================= ALTER DATABASE [AIELLO_DBA] MODIFY FILEGROUP [AIELLO_DBA_FS] READONLY BACKUP DATABASE [AIELLO_DBA] TO DISK = N'D:\Prueba_fs_readonly.bak' WITH NOFORMAT, NOINIT, NAME = N'PRUEBA FS', SKIP, NOREWIND, NOUNLOAD, STATS = 10 ALTER DATABASE [AIELLO_DBA] MODIFY FILEGROUP [AIELLO_DBA_FS] READWRITE
Por lo tanto el filegroup de los documentos es guardado como readonly. Al momento de hacer restore lo puedo hacer de dos formas:
--============== MODO 1 - Full -- 7:38 min RESTORE DATABASE [AIELLO_DBA] FROM DISK = N'D:\Prueba_fs_readonly.bak' WITH REPLACE, RECOVERY ALTER DATABASE [AIELLO_DBA] MODIFY FILEGROUP [AIELLO_DBA_FS] READWRITE
Esta forma hace un restore completo de la forma tradicional, dejando el servicio bajo el tiempo que dure el restore (en una base donde el mayor porcentaje del espacio son archivos esto puede crecer mucho).
Y el plan b...
Y el plan b...
-- ============================================= -- Author: Andrés Aiello -- Create date: 07/07/2011 -- Restore inteligente -- ============================================= --============== MODO 2 - Sin los binarios -- 0:46 RESTORE DATABASE [AIELLO_DBA] FROM DISK = N'D:\Prueba_fs_readonly.bak' WITH REPLACE, PARTIAL, RECOVERY ALTER DATABASE [AIELLO_DBA] SET RECOVERY FULL WITH NO_WAIT -- Base online sin los binarios!!! -- Recuperar todo... -- 8:30 RESTORE DATABASE [AIELLO_DBA] FILEGROUP='AIELLO_DBA_FS' FROM DISK = N'D:\Prueba_fs_readonly.bak' WITH REPLACE, RECOVERY ALTER DATABASE [AIELLO_DBA] MODIFY FILEGROUP [AIELLO_DBA_FS] READWRITE
De esta forma la base en solo 45 segundos se encuentra operativa!!! y mientras se va utilizando se va recuperando en background los documentos del otro filegroup. Para evitar inconsistencias mientras se recupera esa parte el filegroup se mantiene en readonly.
Andrés
Andrés
Etiquetas:
FILESTREAM,
restore,
SQL 2008,
SQL Server
viernes, 8 de abril de 2011
CREATE USER WITHOUT LOGIN
Una instrucción no muy utilizada es la de create user without login. ¿En que consiste? tal como su nombre lo indica lo que hace es crear un usuario sin asociarlo a un login en el servidor, ahora veamos porque querríamos utilizar esto.
Antes que nada vayamos a la sintaxis
Antes que nada vayamos a la sintaxis
CREATE USER user_name
WITHOUT LOGIN
WITH DEFAULT_SCHEMA = [dbo]
Este usuario podrá recibir permisos, ser incluido en roles y todo lo que considere necesario, pero nadie podrá loguearse con dicho usuario. Un posible uso (aunque a mi ver no recomendado) es cuando aún no se el nombre del login que tendrá y quiero dejarlo listo para una vez definido todo modificar el login asociado y ya tengo todo andando. Esto lo podré hacer con la sentencia:
EXEC sp_change_users_login 'update_one', @username, @loginname
¿Porque digo que no recomiendo esto? porque en estos casos una mejor práctica es asignar los permisos a un role, y una vez definido y creado el usuario simlemente se lo agrega al role. Es mucho mas comoda la administración de permisos si uno utiliza roles.
EXECUTE AS
El uso mas interesante viene de la mano de la opción EXECUTE AS. Esta opción me permite hacer que un procedimiento, función o trigger se ejecute con los permisos de otro usuario que no sea necesariamente el que lo invocó. De esta forma podría por ejemplo hacer un procedimiento que muestre información que de otra forma el usuario no hubiera podido ver. Al no estar este usuario asociado a ningún login no se corre peligro de que alguien se loguee con dicho usuario y escale permisos.
La forma de hacer esto es, por ejemplo:
La forma de hacer esto es, por ejemplo:
CREATE PROCEDURE dbo.procedimiento WITH EXECUTE AS 'UsuarioSinLogin' AS TRUNCATE TABLE ... GO
En este ejemplo un usuario con permisos de datareader y ejecución sobre el stored puede tener una forma comoda y segura de limpiar una tabla en particular.
Huerfanos
Hay un caso muy particular en el cual mucha gente se topa con la sentencia WITHOUT LOGIN sin desearlo, y a veces sin entender porque. Supongamos que tenemos el usuario1 y login1 en la base de datos1 en el servidor1. Mediante backup y restore movemos la base al servidor2. El usuario, como muchos ya saben, queda huerfano de su login, y es necesario utilizar la función nombrada en la primer parte para volver a asociarlo (esto se debe a los ids internos de los usuarios). Si deseamos hacer un script completo de la base nos vamos a encontrar que los logins van a aparecer como WITHOUT LOGIN y esto se debe a que al no coincidir el id interno el motor asume que no tiene login. Es importante considerar esto antes de ponerse a buscar como loco que procedimiento hace un EXECUTE AS o algo similar. En la página de microsoft se puede encontrar un script muy simple para corregir estos casos cuando el usuario y el login tienen el mismo nombre.
Para mas información:
http://msdn.microsoft.com/en-us/library/ms173463.aspx
http://msdn.microsoft.com/es-es/library/ms174378.aspx
http://msdn.microsoft.com/en-us/library/ms188354.aspx
Etiquetas:
CREATE USER,
CREATE USER WITHOUT LOGIN,
EXECUTE AS,
LOGIN,
sp_change_users_login,
SQL,
SQL Server 2008,
WITHOUT LOGIN
miércoles, 16 de marzo de 2011
Contained Databases SQL Denali (2011)
Una de las novedades, a mi ver, mas interesantes del SQL 2011 son las bases autocontenidas (Contained Databases). En las versiones actuales hay cierta información que se guarda en la base, y otra que se guarda en la instancia, como por ejemplos los logins o los jobs. Todo esto seguirá existiendo pero ahora habrá una nueva forma de guardar las cosas y es dentro de la misma base. Esto va a significar una mejora muy importante cuando uno realiza un movimiento de una base de un servidor a otro, permitiendo que toda la lógica de negocio correspondiente a la base viaje con esta, disminuyendo de esta forma las probabilidades de olvidar algo.
Logins
Una de las caractecterísticas mas importantes son los logins. Las bases contenidas permiten que el login lo maneje la misma base y no la instancia. Comunmente cuando uno realiza una migración los usuarios de la base se encuentran asociados a un login de la instancia (tanto sea uno de seguridad integrada o un login sql), y al momento de migrar esta relación se pierde dejando al usuario huerfano. Para solucionar esto es necesario al momento de restorear ejecutar un script que se encargue de mapear nuevamente el usuario con el login correspondiente. Esto no sería necesario en una base contenida visto que toda la información de login se encuentra almacenada en si misma. Es muy importante resaltar que para que esto funcione al momento de establecer la conexion a la base hay que indicar como base default dicha base y no la master como suele venir por defecto.
NOTA: No tuve tiempo de probar cuando es seguridad integrada como se comporta al mover de un servidor a otro y mas allá de que el usuario se mantiene relacionado con el login, ver si el login se encuentra bien definido o requiere algo extra.
Linked servers
Los linked servers también pueden ser almacenados en la base de este nuevo modelo. Es sumamente util esto cuando son utilizados por la aplicación dueña de la base. Los beneficios son notables al momento de puesta en producción inicial, movimientos de base, y para tener mas aislados los elementos de cada base/aplicación. Es una función que me parece va a volverse un MUST en el diseño de bases de datos.
Jobs
Otro elemento que puede pertenecer a la base. En este caso creo que el mayor beneficio es el aislar la lógica de la aplicación. ¿Todos mis jobs van a pertenecer a alguna base ahora? la respuesta es NO. Los jobs de mantenimiento, por ejemplo, va a seguir siendo mejor mantenerlos en la instancia. Ejemplo si tengo un job para hacer backup de mis bases todos los días a las 11PM ese job está bien que se mantenga como un job de la instancia. Lo mismo los de reindexado, actualización de indices etc. En cambio si tengo un job que hace algo propio de la aplicación (ejemplo todas las noches actualiza el estado de los usuarios) ese si debe ir en la base.
Manos a la obra!
El primer paso es habilitar nuestra instancia para utilizar Conteined Databases. El código es el siguiente:
Logins
Una de las caractecterísticas mas importantes son los logins. Las bases contenidas permiten que el login lo maneje la misma base y no la instancia. Comunmente cuando uno realiza una migración los usuarios de la base se encuentran asociados a un login de la instancia (tanto sea uno de seguridad integrada o un login sql), y al momento de migrar esta relación se pierde dejando al usuario huerfano. Para solucionar esto es necesario al momento de restorear ejecutar un script que se encargue de mapear nuevamente el usuario con el login correspondiente. Esto no sería necesario en una base contenida visto que toda la información de login se encuentra almacenada en si misma. Es muy importante resaltar que para que esto funcione al momento de establecer la conexion a la base hay que indicar como base default dicha base y no la master como suele venir por defecto.
NOTA: No tuve tiempo de probar cuando es seguridad integrada como se comporta al mover de un servidor a otro y mas allá de que el usuario se mantiene relacionado con el login, ver si el login se encuentra bien definido o requiere algo extra.
Linked servers
Los linked servers también pueden ser almacenados en la base de este nuevo modelo. Es sumamente util esto cuando son utilizados por la aplicación dueña de la base. Los beneficios son notables al momento de puesta en producción inicial, movimientos de base, y para tener mas aislados los elementos de cada base/aplicación. Es una función que me parece va a volverse un MUST en el diseño de bases de datos.
Jobs
Otro elemento que puede pertenecer a la base. En este caso creo que el mayor beneficio es el aislar la lógica de la aplicación. ¿Todos mis jobs van a pertenecer a alguna base ahora? la respuesta es NO. Los jobs de mantenimiento, por ejemplo, va a seguir siendo mejor mantenerlos en la instancia. Ejemplo si tengo un job para hacer backup de mis bases todos los días a las 11PM ese job está bien que se mantenga como un job de la instancia. Lo mismo los de reindexado, actualización de indices etc. En cambio si tengo un job que hace algo propio de la aplicación (ejemplo todas las noches actualiza el estado de los usuarios) ese si debe ir en la base.
Manos a la obra!
El primer paso es habilitar nuestra instancia para utilizar Conteined Databases. El código es el siguiente:
sp_configure 'show advanced', 1;Y luego crear la base, indicando que será una base contenida:
RECONFIGURE WITH OVERRIDE;
go
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go
CREATE DATABASE MiBase CONTAINMENT = PARTIAL;Con esto ya tenemos nuestra base lista!!!
go
Saludos y espero que les sirva. Para mas información: http://msdn.microsoft.com/en-us/library/ff929071%28v=SQL.110%29.aspx .
Andrés
Etiquetas:
Contained Databases,
linked servers,
SQL 11,
SQL Denali,
SQL Server
Suscribirse a:
Entradas (Atom)