Tablas Temporales vs Variables Tipo Tabla

Desde que era programador pensé que las variables de tipo tabla eran mejores que las tablas temporales porque alguna vez escuché que las primeras eran tablas que vivían en la memoria, mientras que las tablas temporales almacenaban sus datos en disco. Sin embargo, este es un mito que debe ser desterrado ya que la verdad es completamente opuesta y a continuación lo vamos a comprobar.
Sigue leyendo “Tablas Temporales vs Variables Tipo Tabla”

Procedimientos Almacenados Temporales

Esto puede sonar algo extraño, ya que lo común es hablar de tablas temporales, sin embargo, también existen procedimientos almacenados temporales, sí, estos al igual que las tablas temporales tienen como ambito de vida la sesión que los creó. En este post discutiremos cómo crearlos y porqué crearlos. Sigue leyendo “Procedimientos Almacenados Temporales”

Configurando las Reglas de Firewall para Azure SQL Database

Las bases de datos Azure SQL Database son ofrecidas como un producto PasS dentro de la plataforma cloud de Azure, y por defecto son seguras, al no permitir el acceso a ellas si es que antes no se configuran las reglas de Firewall para permitir las conexiones externas. Esto es lo que vamos a ver en esta ocasión, la configuración del Firewall de las bases de datos Azure SQL Database. Sigue leyendo “Configurando las Reglas de Firewall para Azure SQL Database”

Para que sirve el ANSI_PADDING

22effbMuchas veces me he encontrado con scripts que tenían entre sus lineas iniciales la configuración explicita del ANSI_PADDING, y siempre la ignoraba al no saber que implicaba tenerla e ignorar los subsecuentes efectos que podría causar el configurar dicha opción en ON u OFF. Sin embargo hace poco tuve un caso algo extraño para mi en un cliente, el cual reportaba que su data parecía cortada al final. Luego de revisar los procedimientos almacenados que guardaban la información en esta tabla y confirmar que estos no estaban haciendo algo raro al momento de grabar la información, me percate que la tabla al que el cliente hacia referencia fue creada con la opción ANSI_PADDING en OFF.

Esta es una opción de configuración de la sesión que básicamente controla la forma en la que SQL Server almacena los espacios en blanco o ceros situados al final del valor de la columna. Los tipos de datos que son afectados por esta opción de configuración son: char, varchar, binary, varbinary. Es importante notar que NO están considerados los tipos de datos UNICODE, es decir los siguientes: nchar y nvarchar. Finalmente hay que indicar que esta opción solo afecta al momento de crear una tabla, agregar o modificar una columna, y si ya se tienen registros en la tabla, estos NO son afectados con el cambio.

Cuando se crea o se altera una columna y se tiene la opción de ANSI_PADDING ON, el cual es el valor por defecto, las columnas mantendrán los espacios en blanco o ceros al que se coloquen al final del valor de la columna, en el caso particular de los char se rellanaran con espacios en blanco hasta alcanzar el valor máximo de caracteres definidos para la columna. Caso contrario, cuando se crea o altera una columna y la sesión tiene la opción de ANSI_PADDING OFF, SQL Server eliminara los espacios en blanco o ceros situados al final del valor de la columna. Sin embargo si hubieran espacios en blanco o ceros al inicio del valor de la columna, estos se mantienen y no son afectados.

Microsoft recomienda siempre mantener el valor por defecto ANSI_PADDING ON en la documentación oficial

Para poder comprobar esto vamos a crear dos tablas, ambas con columnas char y varchar, pero la diferencia entre ellas sera el valor de la opción ANSI_PADDING que tendrá al momento de crear la tabla.

SET ANSI_PADDING ON
GO
CREATE TABLE TablaConPadding
  (col1 char(50)
  ,col2 varchar(50))
GO

SET ANSI_PADDING OFF
GO
CREATE TABLE TablaSinPadding
  (col1 char(50)
  ,col2 varchar(50))
GO

Una vez creadas vamos a verificar si efectivamente la opción de ANSI_PADDING esta colocada a nivel de las columnas creadas:

select
  t.object_id
  ,t.name as table_name
  ,c.column_id
  ,c.name as column_name
  , c.is_ansi_padded
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name IN ('TablaConPadding','TablaSinPadding')

Ssms_2018-01-09_14-29-46

Ahora veremos su comportamiento insertando algunos valores sin espacios para ver cuanto es en realidad lo que se ha guardado del valor completo, para esto utilizaremos la función DATALENGHT la cual retorna el numero de bytes que ocupa un dato.

-- insertamos los valores sin espacios
insert into TablaConPadding values ('Mundo','Mundo');
insert into TablaSinPadding values ('Mundo','Mundo');

-- revisamos la cantidad de bytes guardados
select 'TablaConPadding' as TableName
  ,col1, col2
  ,DATALENGTH(col1) BytesCharCol
  ,DATALENGTH(col2) BytesVarcharCol
from TablaConPadding
union all
select 'TablaSinPadding' as TableName
  ,col1, col2
  ,DATALENGTH(col1) BytesCharCol
  ,DATALENGTH(col2) BytesVarcharCol
from TablaSinPadding

Ssms_2018-01-09_15-08-41

Como se puede observar, en la tabla que se creo con la opción ANSI_PADDING ON, la columna “col1”, la cual es CHAR(50), se relleno con espacios en blanco a la derecha hasta llegar a los 50 bytes de limite que tiene. Mientras que en la tabla que se creo con la opción de ANSI_PADDING OFF, la columna “col1” se comporta como si fuera un VARCHAR ya que no considera todos los espacios en blanco que se colocarían en la derecha como se hizo con la primera tabla.

Ahora ingresaremos valores con tres espacios al inicio y al final, luego verificaremos cuanto espacio ocupan estos datos nuevos dentro de nuestras dos tablas:

-- insertamos los valores
insert into TablaConPadding values ('   Hola   ','   Hola   ');
insert into TablaSinPadding values ('   Hola   ','   Hola   ');

-- revisamos la cantidad de bytes guardados
select 'TablaConPadding' as TableName
  ,col1, col2
  ,DATALENGTH(col1) BytesCharCol
  ,DATALENGTH(col2) BytesVarcharCol
from TablaConPadding
union all
select 'TablaSinPadding' as TableName
  ,col1, col2
  ,DATALENGTH(col1) BytesCharCol
  ,DATALENGTH(col2) BytesVarcharCol
from TablaSinPadding

select col1, col2, LEN(col1),LEN(col2) from TablaConPadding
select col1, col2, LEN(col1),LEN(col2) from TablaSinPadding

2018-01-09_16-03-18

Ahora, si notamos la tabla que se creo con ANSI_PADDING ON, si considero los caracteres en blanco tanto a la derecha como a la izquierda, tanto en el caso de los CHAR como el de los VARCHAR, repitiendose el comportamiento de los CHAR de llenar todo el espacio disponible. Finalmente la tabla que se creo con ANSI_PADDING OFF se puede verificar que mantuvo los caracteres en blanco de la izquierda, pero elimino los de la derecha, eso era el comporamiento esperado.

Finalmente actualizaremos la tabla que se creo con ANSI_PADDING OFF para que su columna “col2” funcione con ANSI_PADDING ON y comprobaremos que los registros existentes no son afectados y la nueva configuración entra en funcionamiento desde ese punto en adelante.

-- cambiamos la col2
SET ANSI_PADDING ON
GO
ALTER TABLE TablaSinPadding
  ALTER COLUMN col2 varchar(50)
GO

-- insertamos los valores
insert into TablaSinPadding values ('   Dummy   ','   Dummy   ');

-- revisamos la cantidad de bytes guardados
select 'TablaSinPadding' as TableName
  ,col1, col2
  ,DATALENGTH(col1) BytesCharCol
  ,DATALENGTH(col2) BytesVarcharCol
from TablaSinPadding

2018-01-09_16-44-29

Como vemos en los resultados, luego de agregar el valor “Dummy” con tres espacios al inicio y tres al final, la columna “col2” contabilizo 11 bytes correspondientes a los 3 espacios en blanco iniciales y 3 finales, más los 5 caracteres de la palabra “Dummy”. Los registros existentes se mantienen con sus valores anteriores.

T-SQL Tuesday #98 – Your Technical Challenges Conquered

sqltuesdayThis is the first time I post for this fantastic initiative called T-SQL Tuesday, created by Adam Mechanic (b|t), and in this occasion hosted by Arun Sirpal (b|t) on this post. I postponed my participation on this blog party, mostly because my blog is mostly targeted to spanish speaking readers, however I did not want to let it pass this time, because I have a really good story to share.

Not long ago I entered into a new company where they had several database related problems, however the most urgent was the infamous blocking sessions. These blocking problems were created by a poorly programmed application, but since it was so old and so critical, nobody had the curage or knowledge to change a bit of the code. The duration of each blocked session could reach up to 2 minutes, yes you read that right, 2 minutes.

Digging into the issue I found that the most common blocking pattern was as follow:

  • Session 1: updated an auditing column on the parent table. (Blocker Session)
  • Session 2: inserted a record on the child table for the same record updated on session 1. (Blocked Session)

Off the top of my head I could no find a reason why session 2 got blocked by session 1, as it was not touching any column from parent table. Reviewing the blocking resources I found that since both tables were joined by a foreign key constraint, when the insert in the child table occurred, SQL Server had to check on the parent table if the value for the foreign key was valid, so it had to do a cluster index seek on the parent table. I thougth I found the cause and then I decided that since there were so much blocking in this database I would enable the “Read Commited Snapshot” isolation level, allowing readers not to be blocked by writers and viceversa, that would allow the insert statement to complete without getting blocked, right? However when I reviewed the database configuration it was already enabled.

Turns out that Foreign Key validation is always done under the “Read Commited” isolation level regardless of the isolation level you configured on your user database. So I spent several hours banging my head on the wall trying to come up with a workaround to solve the issue or at least reduce the impact, and then it hit me like a lightning, I have to avoid validating against the Cluster index, so I came up with two possible work arounds:

  1. Convert the Cluster Primary Key into a Non-cluster primary key, which would require to lock the table during the whole process, rebuild indexes, and re-create foreign key constraints, plus the possibility of ending up with a “Heap” table or choosing a new Cluster index (major change).
  2. Create a Unique Index on the same primary key, meaning having a duplicate index, but much faster to implement.

I chose to do the later, so after creating the unique key, I rebuild only foreign key constraint on the child table and the problem was gone, SQL Server was smart enough to choose the unique index instead of the Cluster index to build the foreign key. After that change all blocking problems between both tables were gone, we had to struggle with other patters of blocking processes but those were much simpler and easier to solve.

I hope you enjoied this experience that challenge my technical skills to the limit. Here is a script to reproduce the issue,

use master
GO
-- Create the test database and configure it as read commited snapshot
IF (DB_ID('WeirdLockIssue') IS NOT NULL)
BEGIN
  ALTER DATABASE WeirdLockIssue SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE WeirdLockIssue;
END
CREATE DATABASE WeirdLockIssue
GO
CREATE DATABASE WeirdLockIssue
GO
ALTER DATABASE WeirdLockIssue SET READ_COMMITTED_SNAPSHOT ON;
GO
USE WeirdLockIssue
GO
-- create test tables
CREATE TABLE dbo.ParentTable
  (ParentTableId int not null identity(1,1)
  ,DateCreated datetime not null
  ,DateUpdated datetime null
  ,CONSTRAINT pk_ParentTable PRIMARY KEY (ParentTableId))
GO
CREATE TABLE dbo.ChildTable
  (ChildTableId int not null identity(1,1)
  ,ParentTableId int not null
  ,DateCreated datetime not null
  ,DateUpdated datetime null
  ,CONSTRAINT pk_ChildTable PRIMARY KEY (ChildTableId)
  ,CONSTRAINT fk_ChildTable_ParentTableId
    FOREIGN KEY (ParentTableId)
      REFERENCES dbo.ParentTable (ParentTableId))
GO
-- insert test record on parent
INSERT INTO ParentTable (DateCreated) VALUES (GETDATE())

/*
-- Open new session and execute the following (session 1)
USE WeirdLockIssue
GO
BEGIN TRANSACTION
UPDATE ParentTable
SET DateUpdated = GETDATE()
WHERE ParentTableId=1
*/

/*
-- Open new session and execute the following (session 2)
use WeirdLockIssue
go
INSERT INTO dbo.ChildTable  (ParentTableId  ,DateCreated)
VALUES  (1,GETDATE())
*/

-- to solve the issue, create a unique index on parent table
CREATE UNIQUE INDEX uix_ParentTable_ParentTableId
  ON dbo.ParentTable (ParentTableId)
-- rebuild foreign key constraint
ALTER TABLE dbo.ChildTable
  DROP CONSTRAINT fk_ChildTable_ParentTableId
GO
ALTER TABLE dbo.ChildTable
  ADD CONSTRAINT fk_ChildTable_ParentTableId
    FOREIGN KEY (ParentTableId)
      REFERENCES dbo.ParentTable (ParentTableId)
GO
-- Query to confirm that the child table is aiming the unique key index
select
  fk.name as ForeignKeyName,
  obj.name as ReferencedTable,
  ix.name as ReferencedIndex
from sys.foreign_keys fk
inner join sys.objects obj
  on fk.referenced_object_id = obj.object_id
inner join sys.indexes ix
  on obj.object_id = ix.object_id
  and fk.key_index_id = ix.index_id
where fk.parent_object_id = OBJECT_ID('dbo.ChildTable')

¿Problemas con “Collations”?

No es raro encontrar situaciones en las que un procedimiento almacenado o una consulta funcionaba correctamente en casi todos los ambientes excepto en uno, y normalmente nos encontramos con el siguiente error:

Cannot resolve the collation conflict between “Modern_Spanish_100_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

ira maquina

Este error es producido porque las columnas de condición que se esta usando para un JOIN entre dos tablas son de tipo cadena (CHAR, NCHAR, VARCHAR, NVARCHAR), y el collation de ambas es distinto.

Primero debemos definir que es el “collation”. El “collation” en resumen es una configuracion de servidor, bases de datos y columnas que determinan el comportamiento que tendra la data de tipo caracter con relacion al idioma seleccionado. Para mayor informacion sobre el tema recomiento el siguiente link. Esta configuración no se puede obviar o desaparecer. Para el servidor se determina al momento de la instalacion, asi que si para instalar SQL Server siempre hacer “Next” hasta llegar al final, seguramente instalaste el collation por defecto de SQL Server. Para las bases de datos se determina al momento de crearlas, si no se coloco valor, heredaran la del servidor. Y finalmente la de las columnas se determinan al momento de crear la tabla o agregar nuevas columnas a una tabla existente, si no se determino valor, heredará la de la base de datos. Como se puede observar todo es una catarata de configuraciones que si no se planean correctamente pueden causar problemas mas adelante.

Regresando al tema, en este caso yo tengo instalado una instancia de base de datos con el collation configurado en “Modern_Spanish_100_CI_AI”, mientras que tengo una base de datos configurada en collation “SQL_Latin1_General_CP1_CI_AS”. Ahora dentro de mi base de datos de usuario tengo una tabla a la cual quiero unir con un JOIN a otra tabla temporal que contiene algunos valores, y la quiero unir usado la columna comun entre ambas, la cual es “Codigo”, sin embargo como el collation entre el servidor y la base de datos es diferente me sale el error indicado mas arriba. Para poder reproducir el escenario se puede utilizar el siguiente codigo:

-- verificamos collation del servidor
SELECT SERVERPROPERTY('Collation') AS Collation
/*
Collation
------------------------
Modern_Spanish_100_CI_AI
*/

-- creamos la base de datos con un collation distinto al de la instancia
CREATE DATABASE CollationTest  COLLATE SQL_Latin1_General_CP1_CI_AS

USE CollationTest
GO
-- creamos la tabla
CREATE TABLE TablaA
	(Codigo varchar(30) not null
	,Nombre varchar(255) not null
	,CONSTRAINT pk_TablaA PRIMARY KEY (Codigo))

-- insertamos alguna data de prueba
INSERT INTO TablaA
select 'COD' + CAST(object_id AS VARCHAR(27)), name
from sys.objects

-- creamos una tabla temporal
CREATE TABLE #tmp_tablaA
(Codigo varchar(30) not null
,Nombre varchar(255) not null)

-- agregamos algunos registros a la temporal
insert into #tmp_tablaA
select top 5*
from TablaA
order by 1 desc

-- hacemos una consulta uniendo ambas tablas, y se
-- generará el error
select *
from TablaA a
inner join #tmp_tablaA t on a.Codigo = t.Codigo

Ahora que pudimos generar el error, hay que entender porque se produjo. En este caso el error se debe a que al momento de crear la tabla temporal, esta se crea por defecto con el “collation” definido en la instancia de base de datos al momento de instalarse, es por eso que los “collation” de ambas tablas son distintos.

Hay 2 soluciones a este problema, la primera es la mas rápida, y es que al momento de crear la tabla temporal, explicitamente se le indique el “collation” que usará para sus columnas con el objetivo de que sea el mismo de la base de datos usuario. Mas que una solución permanente es un “workaround” al problema ya que no lo estamos resolviendo sino mas bien tapando, ya que ese mismo patrón de código puede aparecer en otras partes de la aplicación o de los procedimientos almacenados, y tendriamos el mismo problema. Para aplicarla usaremos el siguiente código:

USE CollationTest
GO
-- creamos la tabla temporal con collation explicito
CREATE TABLE #tmp_tablaA
(Codigo varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS not null
,Nombre varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS not null )

-- insertamos data de prueba a la temporal
insert into #tmp_tablaA
select top 5*
from TablaA
order by 1 desc

-- hacemos el join, y esta vez sin error
select *
from TablaA a
inner join #tmp_tablaA t on a.Codigo = t.Codigo

La segunda solución es más permanente y considero que es la que se debería seguir, sin embargo requiere de mas tiempo y cuidado para implementarla. Lo que haremos en esta segunda solución es cambiar el “collation” de la instancia para que sea igual al de la base de datos. Normalmente todos las instancias donde esta  una misma base de datos deben tener el mismo “collation” configurado, sin embargo esto no siempre es así. Esta segunda solución propuesta la desarrollare en detalle en otro post.

Espero les sea util la información incluida en este post. Nos vemos. Hasta la próxima.

Foreign Keys Duplicadas

Hola, hace ya algún tiempo que no posteo nada, y bueno la verdad es que he estado en una transición bastante amplia en mi vida profesional y me aleje un poco de mi blog, pero ahora pienso retomarlo con fuerza.

Bueno el tema que me trae a este nuevo post son los constraints duplicados especialmente los foreign keys. Hace un tiempo recibí un servidor productivo de base de datos del cual los usuarios tenían quejas constantes sobre lentitud. Una de las tareas que realice sobre el servidor fue la revisión de las claves foráneas de las diferentes bases de datos, y encontré una gran cantidad de estas, la gran mayoría estaban duplicadas, triplicadas y hasta cuadriplicadas.

Haciendo consultas a los desarrolladores, me comentaron que ellos usaban el ERwin para poder hacer la creación de objetos de base de datos, y simplemente ejecutaban los scripts que este generaba, entonces comencé a entender el problema, ahora ya puedia plantear una solución al mismo. Pero antes quisiera mencionar que por cada constraint de clave foránea que tiene la tabla, cuando se ejecute alguna operación de modificación de datos, el motor validara los datos contra todos los constraints de la tabla generando lecturas en las tablas referenciadas, entonces si tenemos muchos contraints duplicados, el motor de base de datos hara muchas lecturas innecesarias para validar dichos constraints. A continuación se muestra un ejemplo de esto:

CREATE DATABASE DUPFKS
GO

USE DUPFKS
GO

IF (OBJECT_ID(‘dbo.Hijo’)>0)
DROP TABLE dbo.Hijo
GO
IF (OBJECT_ID(‘dbo.Padre’)>0)
DROP TABLE dbo.Padre
GO
— Creamos la tabla padre
CREATE TABLE dbo.Padre
(col1 INT CONSTRAINT PK_Padre PRIMARY KEY)
GO
— Creamos la tabla hijo sin NINGUN constraint duplicado
CREATE TABLE dbo.Hijo
(col1 INT NOT NULL
,
col2 INT NOT NULL)
GO
ALTER TABLE dbo.Hijo
ADD CONSTRAINT PK_Hijo
PRIMARY KEY (col1,col2)
GO
ALTER TABLE dbo.Hijo
ADD CONSTRAINT FK_Hijo_col1_1
FOREIGN KEY (col1)
REFERENCES Padre(col1)
GO
— Llenamos la tabla padre con algunos registros
INSERT INTO Padre
SELECT OBJECT_ID
FROM sys.objects

— Indicamos que se midan las estadisticas de IO
SET STATISTICS IO ON

INSERT INTO Hijo VALUES (4,1)
GO
/*
Table ‘Padre’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

INSERT INTO Hijo VALUES (4,2)
GO
/*
Table ‘Padre’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Ingresamos el primer constraint Duplicado
ALTER TABLE dbo.Hijo
ADD CONSTRAINT FK_Hijo_col1_2
FOREIGN KEY (col1)
REFERENCES Padre(col1)
GO

— Volvemos ha hacer una insercion
INSERT INTO Hijo VALUES (4,3)
GO
/*
Table ‘Padre’. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Si comparamos las estadisticas contra las anteriores
— vemos que las lecturas en la tabla padre se duplicaron

— Ingresamos el segundo constraint Duplicado
ALTER TABLE dbo.Hijo
ADD CONSTRAINT FK_Hijo_col1_3
FOREIGN KEY (col1)
REFERENCES Padre(col1)
GO

— Volvemos ha hacer una insercion
INSERT INTO Hijo VALUES (4,4)
/*
Table ‘Padre’. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Si comparamos las estadisticas contra las anteriores
— vemos que las lecturas en la tabla padre se triplicaron

— Para comprobar la teoria deshabilitaremos el ultimo constraint duplicado creado
ALTER TABLE dbo.Hijo
NOCHECK CONSTRAINT FK_Hijo_col1_3
GO

— Realizamos la inserción
INSERT INTO Hijo VALUES (4,5)
/*
Table ‘Padre’. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Podemos ver que las lecturas contra la tabla padre ya no validaron
— el ultimo constraint duplicado ya que este fue eliminado.

Para solucionar este problema se ha realizado el siguiente script el cual permite identificar los constraints duplicados además de proveer la sentencia necesaria para eliminarlos.

;WITH ForeignKeys1 AS
(
SELECT fk.OBJECT_ID
,OBJECT_NAME(fk.parent_object_id) AS table_name
,fk.name AS foreign_key_name
,fk.create_date
,(SELECT CAST(parent_object_id AS VARCHAR(50))
+
SPACE(1) + CAST(parent_column_id AS VARCHAR(50))
+
SPACE(1) + CAST(referenced_object_id AS VARCHAR(50))
+
SPACE(1) + CAST(referenced_column_id AS VARCHAR(50)) AS [data()]
FROM sys.foreign_key_columns fkc
WHERE fk.OBJECT_ID = fkc.constraint_object_id
ORDER BY constraint_column_id
FOR XML PATH('')) foreign_key
FROM sys.foreign_keys fk
WHERE is_disabled = 0
)
,
ForeignKeys2 AS
(
SELECT OBJECT_ID, table_name, foreign_key_name, foreign_key
,COUNT(1) OVER (PARTITION BY foreign_key) AS NbrDuplicated
,ROW_NUMBER() OVER (PARTITION BY foreign_key ORDER BY foreign_key_name) AS Rownum
FROM ForeignKeys1
)

— Sentencia para listar los constraints duplicados
SELECT table_name AS [Table Name]
,foreign_key_name AS [Foreign Key]
,‘ALTER TABLE ‘ + QUOTENAME(table_name,‘]’) + ‘ DROP CONSTRAINT ‘ +
QUOTENAME(foreign_key_name,‘]’) AS [Drop Sentence]
FROM ForeignKeys2
WHERE NbrDuplicated > 1
ORDER BY table_name, foreign_key_name

Este script ha sido modificado del original encontrado en la siguiente URL: http://www.jasonstrate.com/2009/01/find-duplicate-foreign-keys/#

Espero haya sido de utilidad este post y sirva para mantener más sanas sus bases de datos. Hasta la próxima.

Encriptación de datos en SQL Server – Parte 3

Hola, parece increible que ya estoy en la tercera parte de esta serie de post dedicados a la encriptación de datos en SQL Server, espero que este siendo de ayuda para poder tomar decisiones sobre la encriptación de los datos de una manera mas informada. Si gustan pueden visitar la Parte 2 or la Parte 1 de los post relacionados con este tema. Ahora procederemos a explicar la encriptación basada en llaves simetricas.

Symmetric Key: La encriptación a partir de llaves simétricas tiene como principio que para  encriptar y desencriptar la información se necesita la misma llave. Es decir que si nuestra llave llega a ser pública y accesible para todos los usuarios, estos podrían ver la información sensible que tenemos encriptada en la base de datos. Pero no hay que alarmarse tanto, ya este tipo de encriptación es la más común, y es la que la mayoría de ejemplos en la red usan para explicar el tema de la encriptación de datos en SQL Server. Para crear una llave simétrica, esta debe ser encriptada a partir de un certificado, de una llave asimétrica o de otra llave simétrica, lo cual nos brinda mayor seguridad porque el usuario deberá pasar por encima de todos estos métodos de encriptación para poder acceder a la llave que le permitirá encriptar o desencriptar la información. A continuación veremos un ejemplo simple de encriptación de datos.

-- limpiamos el ambiente
IF (DATABASEPROPERTY('SecureDB','version') > 0)
BEGIN
USE MASTER
ALTER DATABASE
SecureDB SET single_user WITH ROLLBACK IMMEDIATE
DROP DATABASE
SecureDB
END

— Creamos la base de datos de prueba
USE MASTER
GO
CREATE DATABASE SecureDB
GO

—  Usamos nuestra base de datos de demo
USE SecureDB
GO

— Creamos una tabla cualquiera con una columna DocNum
— de tipo varbinary para que contenga la informacion encriptada
CREATE TABLE dbo.SymetricKeyEncription
(Nombres VARCHAR(100)
,
DocNum VARBINARY(128))
GO

— Creamos una Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MiClaveSegura’
GO

— Creamos el certificado
CREATE CERTIFICATE MiPrimerCertificado WITH SUBJECT=‘DBAMemories Certificate’,
EXPIRY_DATE = ’12/31/2012′
GO

— Creamos la llave simetrica
CREATE SYMMETRIC KEY MiLlaveSimetrica
WITH
KEY_SOURCE = ‘MyKeySource’,
IDENTITY_VALUE = ‘MyIdentityValue’,
ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MiPrimerCertificado;
GO

— Hacemos una consulta para visualizar las llaves simetricas de la base de datos
— en este caso podemos observar 2 las cuales son la DMK y la llave simetrica creada
— a partir del certificado.
SELECT name, algorithm_desc, create_date
FROM sys.symmetric_keys
/*
name                     algorithm_desc create_date
———————— ————– ———————–
##MS_DatabaseMasterKey## TRIPLE_DES     2011-10-12 21:34:50.147
MiLlaveSimetrica         AES_256        2011-10-12 21:34:57.500
*/

— Ahora procederemos a ingresar valores a nuestra tabla
— Para eso debemos abrir la llave simetrica
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;

— insertamos un valor
INSERT INTO dbo.SymetricKeyEncription (Nombres, DocNum)
VALUES (‘Juan Perez’, ENCRYPTBYKEY(KEY_GUID(‘MiLlaveSimetrica’),‘12345678’))
GO

— Una vez que se termino de encriptar los datos, se cierra la llave simetrica
CLOSE SYMMETRIC KEY MiLlaveSimetrica

— Intentamos hacer un select convencional
SELECT Nombres, DocNum
FROM dbo.SymetricKeyEncription
/*
Nombres    DocNum
———- ————–
Juan Perez 0x0015A4ACF…
*/

— Ahora para poder ver los datos en claro, debemos desencriptarlos
— con la llave simetrica, y para hacerlo debemos primero abrirla
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;

— Ahora seleccionamos los datos pero antes los desencriptamos con la llave simetrica
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription
/*
Nombres    DocNum
———- ————–
Juan Perez 12345678
*/

— Y ahora volvemos a cerrar la llave simetrica
CLOSE SYMMETRIC KEY MiLlaveSimetrica

— Ahora para probar la seguridad de la llave simetrica
— Creamos un usuario de prueba
CREATE LOGIN [Usuario1] WITH PASSWORD = N’123′
GO
CREATE USER [Usuario1] FOR LOGIN [Usuario1]
GO
GRANT SELECT ON dbo.SymetricKeyEncription TO Usuario1
GO

— Probamos seleccionar la informacion con la llave simetrica
EXECUTE AS USER = ‘Usuario1’
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription;
REVERT

— Como se puede ver nos aparece un error porque el usuario no tiene permisos sobre la llave simetrica

— Para poder hacer uso de la llave simetrica debemos tener el permiso
— de control sobre el certificado que la encripta y sobre la misma llave simetrica.
— Ahora asignaremos ese permiso al Usuario1
GRANT CONTROL ON CERTIFICATE::[MiPrimerCertificado] TO [Usuario1]
GO
GRANT CONTROL ON SYMMETRIC KEY::MiLlaveSimetrica TO Usuario1
GO

— Probamos seleccionar la informacion con la llave simetrica
EXECUTE AS USER = ‘Usuario1’
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;
SELECT Nombress, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription;
CLOSE SYMMETRIC KEY MiLlaveSimetrica
REVERT
/*
Nombres    DocNum
———- ————–
Juan Perez 12345678
*/

— Como vemos ahora la informacion si aparece.

Al igual que los certificados, las llaves simetricas deberían tener una copia de seguridad, pero lamentablemente esto no es posible debido a que dentro de las sentencias T-SQL para manejar las llaves simetricas no hay ninguna que sirva para sacar copias de seguridad, entonces que podemos hacer para poder crear una llave simetrica que pueda recuperarse, bueno hay dos atributos especiales que fueron especificados en la creación de la llave simetrica en el ejemplo anterior, estos dos atributos son:

  • IDENTITY_VALUE: el cual SQL Server usa para generar in valor GUID para la llave.
  • KEY_SOURCE: el cual SQL Server usa como material para poder generar la llave en sí.

Si estos dos atributos fueron especificados en la creación de la llave simetrica, entonces esta podrá ser recreada siempre y cuando se tengan copias de seguridad de los objetos usados para generarla, en este caso del certificado usado. Si alguno de estos requisitos no es completado, entonces la información estará en peligro ya que si se llegan a perder alguno de los objetos de encriptación la información puede perderse para siempre. A continuación veremos un ejemplo de cómo sacar copia de seguridad a estos objetos y luego simularemos su perdida y su posterior recuperación.

USE SecureDB
GO

-- sacaremos una copia de seguridad de la DMK
BACKUP MASTER KEY TO FILE = 'D:\DBAMemories\DMK.dat'
ENCRYPTION BY PASSWORD = 'MiPasswordSeguro';
GO

-- Sacaremos una copia de seguridad del certificado
BACKUP CERTIFICATE MiPrimerCertificado TO FILE = 'D:\DBAMemories\MiPrimerCertificado.cert'
WITH PRIVATE KEY ( FILE = 'D:\DBAMemories\MiPrimerCertificado.key' ,
ENCRYPTION BY PASSWORD = 'pass' );
GO

-- Ahora perderemos la llave simetrica
DROP SYMMETRIC KEY MiLlaveSimetrica
GO

-- Ahora perderemos el certificado.
DROP CERTIFICATE MiPrimerCertificado
GO

-- Ahora perderemos la DMK
DROP  MASTER KEY
GO

-- Listo ahora si tenemos un desastre completo
-- al no tener la llave simetrica la informacion es inaccesible
-- Ahora para recrear nuestro ambiente
-- Primero restauraremos la DMK
RESTORE MASTER KEY
FROM FILE =
'D:\DBAMemories\DMK.dat'
DECRYPTION BY PASSWORD = 'MiPasswordSeguro'
ENCRYPTION BY PASSWORD = 'MiClaveSegura'
GO

-- Ahora abrimos la DMK sino no podremos restaurar el certificado
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MiClaveSegura'
GO

-- Ahora restauramos el certificado
CREATE CERTIFICATE MiPrimerCertificado
FROM FILE = 'D:\DBAMemories\MiPrimerCertificado.cert'
WITH PRIVATE KEY (FILE = 'D:\DBAMemories\MiPrimerCertificado.key',
DECRYPTION BY PASSWORD = 'pass');
GO

-- Ahora recreamos la llave simetrica
CREATE SYMMETRIC KEY MiLlaveSimetrica
WITH
KEY_SOURCE = 'MyKeySource',
IDENTITY_VALUE = 'MyIdentityValue',
ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MiPrimerCertificado
GO

-- Ahora si, como tenemos todo restaurado abrimos la llave simetrica
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;

-- Consultamos la informacion encriptada
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription
/*
Nombres    DocNum
---------- --------------
Juan Perez 12345678
*/

-- Y ahora volvemos a cerrar la llave simetrica
CLOSE SYMMETRIC KEY MiLlaveSimetrica

-- Como se puede observar la informacion volvio a estar disponible

Limpieza del Historial de Backups

Hola, queria compartir con todos una experiencia que tuve el día de hoy y es que mientras estaba sacando un backup a una base de datos relativamente pequeña, y el proceso estaba demorando mucho tiempo y el causante de esa demora es algo que normalmente uno no toma en cuenta, y es el historial de backups de las bases de datos de nuestros servidores SQL Server. Muchas veces tenemos servidores que se encuentran corriendo ya varios años e incluso algunos configurados con log shipping lo cual aumente considerablemente nuestro historial de backups, y con el pasar del tiempo los procesos de backup y restore van haciendose mas lentos y es debido a que cada vez que se saca una backup a una base de datos, algunos registros son ingresados dentro de la base de datos de sistema MSDB, y con el pasar del tiempo esta base de datos puede llegar a ser bastante grande si es que no se hace un trabajo de depuracion regularmente.

Entonces ya habia encontrado el problema, ahora tenia que buscar una solucion al mismo y buscando en internet encontre este post de Brent Ozar, donde explica que el problema se puede solucionar haciendo uso del procedimiento almacenado sp_delete_backuphistory, el cual justamente se encarga de limpiar el historial de los backups.

Bueno para no tener este problema mas decidí crear un job que hiciera este trabajo regularmente para mantener activos solo hasta tres meses de historial de los backups. El código que use en el job es el siguiente:


USE msdb;
GO
DECLARE @v_MaxDateHistory DATETIME
SET
@v_MaxDateHistory = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(MONTH,-3,GETDATE()),112))
EXEC sp_delete_backuphistory @v_MaxDateHistory;
GO

Bueno, espero que les haya sido de utilidad.