Obtener el DTS de un paso de un job.

Hola este sera un post pequeño, per me parecio muy util y es que tengo un servidor SQL Server 2000 (si ya se que es bastante antiguo pero es lo que hay), entonces en este servidor hay jobs los cuales dentro de sus steps ejecutan paquetes DTS, sin embargo cuando se abre el step para ver su contenido se ve una sentencia encriptada como la siguiente:

DTSRun /~Z0x6078C236DCDD6A73931306866F9FC179EF750F439603F2CBEB820803744FC6605FF904C2DC6A6F355026A4CF56DEE2CBC7E72D6E7C1C88F17EA6CD17AEA6D7B6D6234D8D743CB0619D8A52006060594AEFEF6EC6582531B5DA1D1F30EDA8B8E2E78099A7869557567EF93557F67265092AF0F4 

Entonces, el problema esta en identificar que DTS es el que ejecuta el step. Luego de buscar puede ver que es tan simple como seguir los siguientes pasos:

• Abrir una ventana de comandos en el servidor donde se alojan los paquetes.
• Pegar toda la cadena antes mencionada
• Agregar al final de esa cadena lo siguiente “/!X /!C”. Entonces quedaria como sigue:

DTSRun /~Z0x6078C236DCDD6A73931306866F9FC179EF750F439603F2CBEB820803744FC6605FF904C2DC6A6F355026A4CF56DEE2CBC7E72D6E7C1C88F17EA6CD17AEA6D7B6D6234D8D743CB0619D8A52006060594AEFEF6EC6582531B5DA1D1F30EDA8B8E2E78099A7869557567EF93557F67265092AF0F4 /!X /!C

Luego de ejecutar la cadena se copiara a la memoria el texto desencriptado de la ejecucion del paquete, entonces solo hay que abrir un notepad y pegar (CTRL + V) y se obtendra algo asi:

DTSRun /S “MiServidor” /N “MiPaqueteDTS_SQL2000″ /E /!X /!C 

Y eso es todo, con eso se puede saber con seguridad cual es el paquete que ejecuta un paso de un job. Espero que les sea de utilidad.

Diseño de una Base de Datos

Las Bases de Datos están compuestas por elementos lógicos y físicos como se puede detallar en el siguiente post. Sin embargo para poder tener un mayor control sobre los objetos que guardamos en ella es necesario realizar un planeamiento previo del diseño ésta, este paso previo a la creación de la base de datos es muy importante ya que si no tenemos un buen diseño inicial, mas adelante cuando encontremos problemas en el diseño, el corregirlo será mucho mas complicado y costoso.

 Comenzaremos viendo desde donde sale la estructura inicial de una base de datos en SQL Server. Cuando se crea una base de datos, internamente SQL Server toma como plantilla la base de datos de sistema llamada “model”, heredando todas sus propiedades y objetos, también copiando los filegroups y datafiles para luego cambiarles de nombre por los de la nueva base de datos, sin embargo este diseño lógico y físico inicial se puede personalizar añadiendo opciones adicionales a la creación de la base de datos, estas opciones adicionales se pueden agregar a través de código o a través del wizard del SSMS (SQL Server Management Studio).

El problema con esto es que normalmente la base de datos model esta configurada sólo con el filegroup “PRIMARY” con un tamaño de 8 MB y un Log de transacciones de 2 MB, ambos configurados con un auto-creacimiento de 10% para ambos archivos. Adicionalmente tiene un modelo de recuperación Full, lo cual ocasiona problemas con el crecimiento del log de transacciones si es que éste no tiene el tratamiento debido, es decir considerar dentro de la estrategia de backup los backups de log de transacciones, lo cual normalmente.

Ahora, personalmente considero que toda base de datos debe tener por lo menos dos filegroups, uno de ellos siempre sera el “PRIMARY” el cual albergara a todos los objetos de sistema como las tablas y vistas de metadata, los subsiguientes filegroups serán dedicados a albergar la data del usuario. Adicionalmente se pueden agregar mas filegroups que sirvan para albergar distintos tipos de data como indices, BLOB data, o tablas de módulos específicos. De esta manera,al tener filegroups destinados a un uso especifico es mucho mas ordenado e incluso permite traer a la base de datos en linea por partes en caso de algún desastre reduciendo el tiempo de espera de los sistemas para poder acceder a la data.

Otra razón importante para poder tener este diseño es para tener mayor flexibilidad al momento de mover los datafiles de un disco a otro mientras estos se encuentran en linea, si es que la data de usuario se encontrara en el filegroup ”PRIMARY” y se quisiera mover el data file con extensión “MDF”, no se podría mover ya que este datafile en especial no puede ser vaciado por completo. Mas adelante tendremos un articulo en el cual se explicara a mas detalle como se debe hacer este movimiento.

Finalmente el realizar este diseño permitirá que la data se distribuya entre varios archivos de datos los cuales según las buenas practicas deberían estar alojados en diferentes discos físicos los cuales permitirán compartir las operaciones de lectura \ escritura de disco, lo cual permitirá el más rápido acceso a los datos. Así que ya saben la próxima vez que se cree una base de datos piense bien el mejor diseño que esta debe tener.

Los números de 2012

Hola,

A pesar de que este año he estado muy ocupado con el trabajo y otras cosas, y no he podido escribir como hubiera querido, los numeros que muestra este reporte anual del blog son muy buenos. Solo quiero agradecerles a todos las personas que visitan este sitio para consulta, ademas hacer el firme compromiso de que este 2013 voy a escribir mas para no tenerlos tan abandonados. Un saludo fuerte y a continuacion los numeros de este 2012.

Los duendes de las estadísticas de WordPress.com prepararon un informe sobre el año 2012 de este blog.

Aquí hay un extracto:

600 personas llegaron a la cima del monte Everest in 2012. Este blog tiene 7.300 visitas en 2012. Si cada persona que ha llegado a la cima del monte Everest visitara este blog, se habría tardado 12 años en obtener esas visitas.

Haz click para ver el reporte completo.

Configurando mi SSMS

Hace unos días entre a trabajar a una nueva empresa y me dieron mi laptop, completamente nueva, para que pudiera instalar el software que necesite para hacer mi trabajo. Bueno lo primero que instale fueron las herramientas de cliente de SQL Server 2008 R2, el cual utilizo para poder conectarme a motores SQL Server 2000, 2005, 2008 y 2008 R2. Utilizo esta versión de herramientas de cliente porque en mi actual trabajo aun no hay ninguna base de datos en SQL Server 2012.

Bueno una vez instaladas las herramientas de cliente, lo que normalmente hago es personalizarlas a mi gusto, y esta es la razón de este post ya que muchas de las opciones por defecto no son de mi agrado, pero felizmente esta herramienta permite personalizar bastante la interface de usuario.

Bueno para empezar, lo primero que configuro los shortcuts ingresando a: Tools->Options->Environment->Keyboard

En esta opción configuro el comando “sp_helptext” bajo el shortcut “CTRL + F1″.

Otra de las personalizaciones que realizo es el agregar los números de linea en el Editor de Texto. Para esto ingreso a: Tools->Options->Environment->Text Editor, y selecciono la siguiente opción:

Adicionalmente dentro de la opción de Text Editor, me gusta modificar la opción de “Editor Bar and Status Bar” debido a que los valores por defecto no me parecen cómodos.

 

Finalmente como paso final, me parece mucho mas legible mostrar los resultados de las consultas en una pestaña aparte. Para poder hacer esto debo ir a: Tools->Options-> Query Results ->SQL Server, ahí selecciono las siguientes opciones:

 

Bueno esto es todo lo que hago en personalización del SQL Server Management Studio. Mas adelante mostrar un poco de las herramientas adicionales al SSMS que utilizo en mi trabajo.

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.