Como hacer un Downgrade de SQL 2005 a SQL 2000 o de SQL 2008 a SQL 2005.
Hay muchas ocasiones en las cuales nos enfrentamos a una situacion que requiere upgradear una base de datos o bien una instancia completa de SQL.
El proceso de upgrade para una sola base de datos es bastante directo ya que solo requiere un backup del server original y un restore en el nuevo. Y por supuesto el scripteo y copia de cualquier login u objeto de servidor relacionado a esa BD.
Sin embargo esta clase de situacion involucra una serie de pruebas exhaustivas de la aplicacion que usa dicha base, y muchas veces una bateria completa de pruebas no es posible ya sea por limitaciones de tiempo o debido a alguna clase de proceso que no es regular (como ser un cierre mensual o algun proceso que corre una vez cada tanto), y muchas veces no es tan sencillo probar todo debido a la complejidad de los procesos y la informacion que requieren para hacerlo.
Teniendo en cuenta esto, la mayoria de los DBAs sabemos que la probabilidad de falla de este tipo de procesos de upgrade es muy baja, sin embargo los procedimientos existentes para la mayoria de los procesos de control de cambios requieren un plan de rollback de todo en el peor escenario.
En este caso el peor escenario seria que tenemos una base upgradeada (por ej. a SQL 2005) y los testeos iniciales fueron exitosos y todo sigue su marcha, pero en algun punto del mes la aplicacion falla debido a una incompatibilidad con la nueva version de la base e imaginemos que es aun peor, y que nos vemos obligados a hacer un rollback a la vieja version debido a una directiva de las altas gerencias de la empresa.
Como ya deben saber no se puede hacer un restore de una base de 2005 en un server de SQL 2000, asi que la unica opcion viable es realizar un export de los datos de la base 2005 e importarlos en la base 2000 en el viejo server.
Para realizar esto necesitamos seguir los siguientes pasos:
- Deshabilitar todas las constraints en la vieja base.
- Deshabilitar todos los triggers en la vieja base.
- Realizar un BCP out de todas las tablas de la nueva BD.
- Poner offline la base con version mas nueva.
- Truncar todas las tablas en la base con version vieja.
- Realizar un BCP in en las tablas de la vieja DB con la nueva data.
- Habilitar las constraints nuevamente.
- Habilitar los triggers nuevamente.
Para hacer esto se deben usar los siguientes scritps:
/**********************************************************************************************/
/* ENABLE/DISABLE FOREIGN KEYS */
/**********************************************************************************************/
DECLARE
@ExStr nVARCHAR(4000),
@tabla VARCHAR(100),
@fkname VARCHAR(100)
DECLARE @ESTADO VARCHAR (20)
SET @ESTADO = 'ENABLE' --change to DISABLE or ENABLE depending what you need to do
DECLARE fke_cur INSENSITIVE CURSOR FOR
SELECT object_name(parent_obj), name
FROM sysobjects s
where xtype in ('F')
order by object_name(parent_obj)
OPEN fke_cur
FETCH NEXT FROM fke_cur INTO @tabla,@fkname
WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@ESTADO='ENABLE')
SET @ExStr= 'ALTER TABLE '+@tabla+' CHECK CONSTRAINT '+@fkname
ELSE
IF (@ESTADO='DISABLE')
SET @ExStr= 'ALTER TABLE '+@tabla+' NOCHECK CONSTRAINT '+@fkname
PRINT @ExStr;
EXECUTE sp_executesql @ExStr
FETCH NEXT FROM fke_cur INTO @tabla,@fkname
END
CLOSE fke_cur
DEALLOCATE fke_cur
/**********************************************************************************************/
/* ENABLE/DISABLE TRIGGERS */
/**********************************************************************************************/
DECLARE @ESTADO VARCHAR (20)
SET @ESTADO = 'ENABLE' -- change for DISABLE or ENABLE depending what you need to do
DECLARE cur_trigger INSENSITIVE CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype='U';
DECLARE @n_trig VARCHAR(50), @ExStr nVARCHAR(4000), @tabla VARCHAR(100);
OPEN cur_trigger;
FETCH NEXT FROM cur_trigger INTO @tabla;
WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@ESTADO='ENABLE')
SET @ExStr='ALTER TABLE '+@tabla+' ENABLE TRIGGER ALL;'
ELSE
IF(@ESTADO='DISABLE')
SET @ExStr='ALTER TABLE '+@tabla+' DISABLE TRIGGER ALL;';
PRINT @ExStr;
EXECUTE sp_executesql @ExStr;
FETCH NEXT FROM cur_trigger INTO @tabla;
END;
CLOSE cur_trigger;
DEALLOCATE cur_trigger;
/* ************************************************************************
BORRA DATA DE TODAS LAS TABLAS
Nota: No use Truncate porque para usar truncate las FK
tienen que ser dropeadas no es suficiente con deshabilitarlas
************************************************************************* */
DECLARE cur_delete INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects WHERE xtype='U';
DECLARE @n_trig VARCHAR(50),
@ExStr nVARCHAR(4000),
@tabla VARCHAR(100);
OPEN cur_delete;
FETCH NEXT FROM cur_delete INTO @tabla;
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @ExStr='DELETE FROM '+@tabla+';';
print @ExStr
EXECUTE sp_executesql @ExStr;
FETCH NEXT FROM cur_delete INTO @tabla;
END;
CLOSE cur_delete;
DEALLOCATE cur_delete;
/* ************************************************************** */
Espero que este simple proceso los ayude a documentar apropiadamente sus cambios sobre SQL y que los ayude a completar exitosamente un downgrade en el caso que tengan que enfrentarse al mismo.
Nota: Recomiendo que tengan a mano at menos por los primeros 2 meses el ultimo backup realizado sobre la base en la vieja version (por ej. el ultimo backup de la base en SQL 2000), asi tienen el esqueleto de la BD para poder trabajar en caso de tener que hacer un downgrade.
Saludos!
MaX
Wednesday, December 1, 2010
SQL - How to Downgrade a SQL2005 DB to SQL 2000 or SQL 2008 to SQL2005
There are many times when you have to face a situation that requires to upgrade a database or an entire SQL Instance.
The process for upgrade a single db it's very straightforward since this only requires a backup on the original version and a restore on the new one. And of course script and copy any login or server object related to that DB.
However this kind of situation involves a very comprehensive test of the application that use that database and many times a full battery of test cannot be completed due to time limitations or because some kind of operations are not quite regular (monthly account process or some kind of process that runs every once in a while), and many times it's not so easy to fully test everything due to the complexity of that processes and the input information needed.
Knowing this in most of the cases as a DBA we know that failure probability for this kind of upgrade process is very low, however the procedures in place for most of the change management process requires a backout plan for the worst case scenario.
In this case the worst case would be that we have the DB upgraded for example to SQL 2005, the initial test are good and everything moves on, but in some point of the month the App fails do to some kind of incompatibility with the new version of the DB and imagine even worst that we have to rollback to the older version due to a directive from the high management.
As you may know we cannot restore a SQL 2005 DB into a SQL 2000 server, so the only viable option it's to perform an export and later an import of the data in the old server.
To accomplish that you need to do these steps:
- Disable all the constraints in the old DB
- Disable all triggers in the old DB
- Perform a BCP out of all the tables on the new DB.
- Put the new DB offline.
- Truncate all the data from the tables on the old DB.
- Perform a BCP in of the tables on the old DB.
- Enable all the constraints in the old DB.
- Enable all the triggers in the old DB.
To do this here are the scripts:
/**********************************************************************************************/
/* ENABLE/DISABLE FOREIGN KEYS */
/**********************************************************************************************/
DECLARE
@ExStr nVARCHAR(4000),
@tabla VARCHAR(100),
@fkname VARCHAR(100)
DECLARE @ESTADO VARCHAR (20)
SET @ESTADO = 'ENABLE' --change to DISABLE or ENABLE depending what you need to do
DECLARE fke_cur INSENSITIVE CURSOR FOR
SELECT object_name(parent_obj), name
FROM sysobjects s
where xtype in ('F')
order by object_name(parent_obj)
OPEN fke_cur
FETCH NEXT FROM fke_cur INTO @tabla,@fkname
WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@ESTADO='ENABLE')
SET @ExStr= 'ALTER TABLE '+@tabla+' CHECK CONSTRAINT '+@fkname
ELSE
IF (@ESTADO='DISABLE')
SET @ExStr= 'ALTER TABLE '+@tabla+' NOCHECK CONSTRAINT '+@fkname
PRINT @ExStr;
EXECUTE sp_executesql @ExStr
FETCH NEXT FROM fke_cur INTO @tabla,@fkname
END
CLOSE fke_cur
DEALLOCATE fke_cur
/**********************************************************************************************/
/* ENABLE/DISABLE TRIGGERS */
/**********************************************************************************************/
DECLARE @ESTADO VARCHAR (20)
SET @ESTADO = 'ENABLE' -- change for DISABLE or ENABLE depending what you need to do
DECLARE cur_trigger INSENSITIVE CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype='U';
DECLARE @n_trig VARCHAR(50), @ExStr nVARCHAR(4000), @tabla VARCHAR(100);
OPEN cur_trigger;
FETCH NEXT FROM cur_trigger INTO @tabla;
WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@ESTADO='ENABLE')
SET @ExStr='ALTER TABLE '+@tabla+' ENABLE TRIGGER ALL;'
ELSE
IF(@ESTADO='DISABLE')
SET @ExStr='ALTER TABLE '+@tabla+' DISABLE TRIGGER ALL;';
PRINT @ExStr;
EXECUTE sp_executesql @ExStr;
FETCH NEXT FROM cur_trigger INTO @tabla;
END;
CLOSE cur_trigger;
DEALLOCATE cur_trigger;
/* ************************************************************************
DELETE DATA FROM ALL TABLES
Note: I didn't use truncate because to truncate the FK must be dropped
it's not enough with the disable.
************************************************************************* */
DECLARE cur_delete INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects WHERE xtype='U';
DECLARE @n_trig VARCHAR(50),
@ExStr nVARCHAR(4000),
@tabla VARCHAR(100);
OPEN cur_delete;
FETCH NEXT FROM cur_delete INTO @tabla;
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @ExStr='DELETE FROM '+@tabla+';';
print @ExStr
EXECUTE sp_executesql @ExStr;
FETCH NEXT FROM cur_delete INTO @tabla;
END;
CLOSE cur_delete;
DEALLOCATE cur_delete;
/* ************************************************************** */
I hope this simple process help you to properly document your changes to the SQL and if you face a downgrade scenario help you to complete it successfully.
Note: I recommend that you have at hand for at least a couple of months the last backup of the old server version of the DB (last backup of the DB on SQL 2000 for example), so you have the complete skeleton of the DB to work with in case of a downgrade.
Regards!
MaX
The process for upgrade a single db it's very straightforward since this only requires a backup on the original version and a restore on the new one. And of course script and copy any login or server object related to that DB.
However this kind of situation involves a very comprehensive test of the application that use that database and many times a full battery of test cannot be completed due to time limitations or because some kind of operations are not quite regular (monthly account process or some kind of process that runs every once in a while), and many times it's not so easy to fully test everything due to the complexity of that processes and the input information needed.
Knowing this in most of the cases as a DBA we know that failure probability for this kind of upgrade process is very low, however the procedures in place for most of the change management process requires a backout plan for the worst case scenario.
In this case the worst case would be that we have the DB upgraded for example to SQL 2005, the initial test are good and everything moves on, but in some point of the month the App fails do to some kind of incompatibility with the new version of the DB and imagine even worst that we have to rollback to the older version due to a directive from the high management.
As you may know we cannot restore a SQL 2005 DB into a SQL 2000 server, so the only viable option it's to perform an export and later an import of the data in the old server.
To accomplish that you need to do these steps:
- Disable all the constraints in the old DB
- Disable all triggers in the old DB
- Perform a BCP out of all the tables on the new DB.
- Put the new DB offline.
- Truncate all the data from the tables on the old DB.
- Perform a BCP in of the tables on the old DB.
- Enable all the constraints in the old DB.
- Enable all the triggers in the old DB.
To do this here are the scripts:
/**********************************************************************************************/
/* ENABLE/DISABLE FOREIGN KEYS */
/**********************************************************************************************/
DECLARE
@ExStr nVARCHAR(4000),
@tabla VARCHAR(100),
@fkname VARCHAR(100)
DECLARE @ESTADO VARCHAR (20)
SET @ESTADO = 'ENABLE' --change to DISABLE or ENABLE depending what you need to do
DECLARE fke_cur INSENSITIVE CURSOR FOR
SELECT object_name(parent_obj), name
FROM sysobjects s
where xtype in ('F')
order by object_name(parent_obj)
OPEN fke_cur
FETCH NEXT FROM fke_cur INTO @tabla,@fkname
WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@ESTADO='ENABLE')
SET @ExStr= 'ALTER TABLE '+@tabla+' CHECK CONSTRAINT '+@fkname
ELSE
IF (@ESTADO='DISABLE')
SET @ExStr= 'ALTER TABLE '+@tabla+' NOCHECK CONSTRAINT '+@fkname
PRINT @ExStr;
EXECUTE sp_executesql @ExStr
FETCH NEXT FROM fke_cur INTO @tabla,@fkname
END
CLOSE fke_cur
DEALLOCATE fke_cur
/**********************************************************************************************/
/* ENABLE/DISABLE TRIGGERS */
/**********************************************************************************************/
DECLARE @ESTADO VARCHAR (20)
SET @ESTADO = 'ENABLE' -- change for DISABLE or ENABLE depending what you need to do
DECLARE cur_trigger INSENSITIVE CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype='U';
DECLARE @n_trig VARCHAR(50), @ExStr nVARCHAR(4000), @tabla VARCHAR(100);
OPEN cur_trigger;
FETCH NEXT FROM cur_trigger INTO @tabla;
WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@ESTADO='ENABLE')
SET @ExStr='ALTER TABLE '+@tabla+' ENABLE TRIGGER ALL;'
ELSE
IF(@ESTADO='DISABLE')
SET @ExStr='ALTER TABLE '+@tabla+' DISABLE TRIGGER ALL;';
PRINT @ExStr;
EXECUTE sp_executesql @ExStr;
FETCH NEXT FROM cur_trigger INTO @tabla;
END;
CLOSE cur_trigger;
DEALLOCATE cur_trigger;
/* ************************************************************************
DELETE DATA FROM ALL TABLES
Note: I didn't use truncate because to truncate the FK must be dropped
it's not enough with the disable.
************************************************************************* */
DECLARE cur_delete INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects WHERE xtype='U';
DECLARE @n_trig VARCHAR(50),
@ExStr nVARCHAR(4000),
@tabla VARCHAR(100);
OPEN cur_delete;
FETCH NEXT FROM cur_delete INTO @tabla;
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @ExStr='DELETE FROM '+@tabla+';';
print @ExStr
EXECUTE sp_executesql @ExStr;
FETCH NEXT FROM cur_delete INTO @tabla;
END;
CLOSE cur_delete;
DEALLOCATE cur_delete;
/* ************************************************************** */
I hope this simple process help you to properly document your changes to the SQL and if you face a downgrade scenario help you to complete it successfully.
Note: I recommend that you have at hand for at least a couple of months the last backup of the old server version of the DB (last backup of the DB on SQL 2000 for example), so you have the complete skeleton of the DB to work with in case of a downgrade.
Regards!
MaX
Tuesday, November 23, 2010
IG - A que te dedicas? .... Ah sabes de computacion!
Bueno, como muchos (o casi todos) los que trabajamos en el rubro informatico me he encontrado incontables veces en la dificil e incomoda situacion de contestar la simple pregunta de: "A que te dedicas?".
Aquel que lea esto y trabaje en sistemas entendera a que me refiero.
Y es que la concepcion de cualquier persona que no tiene contacto con este ambiente es que alguien que trabaja en sistemas "arregla computadoras" o "sabe de computacion" como he escuchado muchas veces.
Y mas alla de lo que el comun de la gente puede especular, trabajar en sistemas NO es sinonimo de saber arreglar una computadora, mas alla de que uno puede estar familiarizado con los componentes de la maquina hay mucha gente que trabaja en distintas posiciones relacionadas a sistemas que no tiene la mas minima idea de como reparar una PC o instalar Windows.
Es por eso que me dedico a estas lineas para clarificar un poco esto y de paso servir para aclarar a mis propios conocidos que es "a lo que me dedico", ya que explicar que es un administrador de bases de datos (o DBA por sus siglas en ingles) a alguien que no trabaja en sistemas es muy dificil, ya que es algo que NO es TANGIBLE.
PRIMERO LO PRIMERO
Primero que nada rompamos el mito general, aquel que estudia sistemas NO estudia como arreglar una computadora, es mas en la mayoria de las carreras de Licenciatura o Ingenieria en sistemas jamas se toca el tema de reparación de PC.
Para reparar una computadora, en general se hace un curso de Técnico reparador de PC o de Tècnico en computación (muchos colegios secundarios los tienen hoy en dia).
Para ponerlo simple un técnico reparador de PC se asemeja a lo que es un mecánico de autos y cumplen a grandes rasgos las mismas funciones.
Tanto el mecanico como el técnico de PC reparan, arman, tunean, customizan y asesoran acerca de FIERROS, es por eso que en la computacion al técnico reparador se lo suele tildar de fierrero muchas veces entre la gente del ambiente, porque es alguien que disfruta y le gusta trabajar con la parte relacionada al hardware y componentes de computadora.
Pero bueno, eso es solo una parte del mundo de la computacion o del mundo de sistemas, es la parte relacionada al hardware y a lo TANGIBLE.
Pero sin embargo hay todo un mundo aparte que se relaciona con lo INTANGIBLE de la computacion o de sistemas. Y eso es donde se enfocan las carreras universitarias de Analista, Licenciatura e Ingenieria de Sistemas.
LO INTANGIBLE
Aca es donde se complica mas explicar que es lo que uno hace. Cuando algo es intangible, donde no es claro a la vista es muy dificil de explicarlo.
Hay una serie de trabajos que se desprenden de las carreras de sistemas como puede ser:
Programadores o Administradores de bases de datos, de plataforma, de Storage o bien
Gerentes y Lideres de Proyectos, Analistas funcionales, Analistas tecnicos, diseñadores de sistemas y la lista podria seguir...
Para hacer una analogía podriamos decir que estudiar sistemas es muy parecido a estudiar medicina.
En la carrera de medicina uno se recibe de Medico Clinico y luego se dedica a especializarse en alguna rama particular de la medicina (traumatologia, obstetricia, ginecologia, etc).
El Licenciado/Ingeniero en sistemas es muy parecido a esto, ya que una vez recibido (a veces desde antes de recibirse inclusive) se especializa en alguna rama en particular de la informatica como las que enumeramos arriba.
El medico trata pacientes, el que trabaja en sistemas trata con sistemas.
Un sistema de informacion puede ser muchas cosas, puede ser un programa en particular, por ejemplo el programa que se usa para la contabilidad, el programa que se usa para liquidar un sueldo. O bien tambien pueden ser cosas mas "abstractas" un sistema tambien es una red, un centro de computos puede ser tratado como un sistema.
El termino es muy amplio.
Y EL DBA ? (administrador de bases de datos)
Bueno, ahora lo que me incumbe a mi.
Que es un DBA, y que significa administrar bases de datos.
Voy a empezar diciendo que todos tratamos con bases de datos a diario sin darnos cuenta en lo absoluto.
Para que se den una idea de como conviven continuamente con bases de datos, cuando van a sacar plata de un cajero, toda esa informacion de su cuenta de banco, su estado de cuenta, sus tarjetas de credito, las operaciones que realizan, todo eso se guarda en una base de datos en algun lado de la empresa BANELCO.
Y para nombrar ejemplos de bases de datos con las que uno convive diariamente tenemos: en internet (que esta plagado), facebook, twitter, wikipedia, la informacion que nos imprimen en los recibos de sueldo, de las boletas de luz, gas , telefono, de sus numeros de celular esta guardado en bases de datos.
Las bases de datos en si son archivos como cualquier archivo de Windows, como un archivo de texto o un archivo de word, nada mas que son archivos de bases de datos y se necesita un programa especifico para manejarlos.
Los administradores de bases de datos son gente especializada para trabajar con estos programas y con datos e informacion de todo tipo y esta capacitada para resolver problemas simples o complejos relacionados a la base de datos que pueden ir desde hacer una copia de respaldo de la informacion hasta resolver problemas complejos o errores que pueden comprometer la disponibilidad de los datos guardados.
Se imaginan que lo ultimo que uno quiere es que por un problema con una base de datos no puedan acceder a su informacion de BANELCO y no puedan sacar su plata.
Para que eso NO pase hay toda una planificacion y mantenimiento de las bases de datos que garantizan que si hay algun problema todo pueda estar funcionando de nuevo lo mas rapido posible sin perder ningun dato en el medio.
Se que es un concepto muy abstracto y complicado de entender, pero espero al menos con esto acercar un poco mas a la compresion de lo que la gente de sistemas hace y especificamente un DBA hace.
Si alguno quiere que explique de manera sencilla alguna de las ramas de la informatica para entender exactamente que hace me lo dicen y se los explico.
Espero no haberlos aburrido.
Saludos!
MaX
Aquel que lea esto y trabaje en sistemas entendera a que me refiero.
Y es que la concepcion de cualquier persona que no tiene contacto con este ambiente es que alguien que trabaja en sistemas "arregla computadoras" o "sabe de computacion" como he escuchado muchas veces.
Y mas alla de lo que el comun de la gente puede especular, trabajar en sistemas NO es sinonimo de saber arreglar una computadora, mas alla de que uno puede estar familiarizado con los componentes de la maquina hay mucha gente que trabaja en distintas posiciones relacionadas a sistemas que no tiene la mas minima idea de como reparar una PC o instalar Windows.
Es por eso que me dedico a estas lineas para clarificar un poco esto y de paso servir para aclarar a mis propios conocidos que es "a lo que me dedico", ya que explicar que es un administrador de bases de datos (o DBA por sus siglas en ingles) a alguien que no trabaja en sistemas es muy dificil, ya que es algo que NO es TANGIBLE.
PRIMERO LO PRIMERO
Primero que nada rompamos el mito general, aquel que estudia sistemas NO estudia como arreglar una computadora, es mas en la mayoria de las carreras de Licenciatura o Ingenieria en sistemas jamas se toca el tema de reparación de PC.
Para reparar una computadora, en general se hace un curso de Técnico reparador de PC o de Tècnico en computación (muchos colegios secundarios los tienen hoy en dia).
Para ponerlo simple un técnico reparador de PC se asemeja a lo que es un mecánico de autos y cumplen a grandes rasgos las mismas funciones.
Tanto el mecanico como el técnico de PC reparan, arman, tunean, customizan y asesoran acerca de FIERROS, es por eso que en la computacion al técnico reparador se lo suele tildar de fierrero muchas veces entre la gente del ambiente, porque es alguien que disfruta y le gusta trabajar con la parte relacionada al hardware y componentes de computadora.
Pero bueno, eso es solo una parte del mundo de la computacion o del mundo de sistemas, es la parte relacionada al hardware y a lo TANGIBLE.
Pero sin embargo hay todo un mundo aparte que se relaciona con lo INTANGIBLE de la computacion o de sistemas. Y eso es donde se enfocan las carreras universitarias de Analista, Licenciatura e Ingenieria de Sistemas.
LO INTANGIBLE
Aca es donde se complica mas explicar que es lo que uno hace. Cuando algo es intangible, donde no es claro a la vista es muy dificil de explicarlo.
Hay una serie de trabajos que se desprenden de las carreras de sistemas como puede ser:
Programadores o Administradores de bases de datos, de plataforma, de Storage o bien
Gerentes y Lideres de Proyectos, Analistas funcionales, Analistas tecnicos, diseñadores de sistemas y la lista podria seguir...
Para hacer una analogía podriamos decir que estudiar sistemas es muy parecido a estudiar medicina.
En la carrera de medicina uno se recibe de Medico Clinico y luego se dedica a especializarse en alguna rama particular de la medicina (traumatologia, obstetricia, ginecologia, etc).
El Licenciado/Ingeniero en sistemas es muy parecido a esto, ya que una vez recibido (a veces desde antes de recibirse inclusive) se especializa en alguna rama en particular de la informatica como las que enumeramos arriba.
El medico trata pacientes, el que trabaja en sistemas trata con sistemas.
Un sistema de informacion puede ser muchas cosas, puede ser un programa en particular, por ejemplo el programa que se usa para la contabilidad, el programa que se usa para liquidar un sueldo. O bien tambien pueden ser cosas mas "abstractas" un sistema tambien es una red, un centro de computos puede ser tratado como un sistema.
El termino es muy amplio.
Y EL DBA ? (administrador de bases de datos)
Bueno, ahora lo que me incumbe a mi.
Que es un DBA, y que significa administrar bases de datos.
Voy a empezar diciendo que todos tratamos con bases de datos a diario sin darnos cuenta en lo absoluto.
Para que se den una idea de como conviven continuamente con bases de datos, cuando van a sacar plata de un cajero, toda esa informacion de su cuenta de banco, su estado de cuenta, sus tarjetas de credito, las operaciones que realizan, todo eso se guarda en una base de datos en algun lado de la empresa BANELCO.
Y para nombrar ejemplos de bases de datos con las que uno convive diariamente tenemos: en internet (que esta plagado), facebook, twitter, wikipedia, la informacion que nos imprimen en los recibos de sueldo, de las boletas de luz, gas , telefono, de sus numeros de celular esta guardado en bases de datos.
Las bases de datos en si son archivos como cualquier archivo de Windows, como un archivo de texto o un archivo de word, nada mas que son archivos de bases de datos y se necesita un programa especifico para manejarlos.
Los administradores de bases de datos son gente especializada para trabajar con estos programas y con datos e informacion de todo tipo y esta capacitada para resolver problemas simples o complejos relacionados a la base de datos que pueden ir desde hacer una copia de respaldo de la informacion hasta resolver problemas complejos o errores que pueden comprometer la disponibilidad de los datos guardados.
Se imaginan que lo ultimo que uno quiere es que por un problema con una base de datos no puedan acceder a su informacion de BANELCO y no puedan sacar su plata.
Para que eso NO pase hay toda una planificacion y mantenimiento de las bases de datos que garantizan que si hay algun problema todo pueda estar funcionando de nuevo lo mas rapido posible sin perder ningun dato en el medio.
Se que es un concepto muy abstracto y complicado de entender, pero espero al menos con esto acercar un poco mas a la compresion de lo que la gente de sistemas hace y especificamente un DBA hace.
Si alguno quiere que explique de manera sencilla alguna de las ramas de la informatica para entender exactamente que hace me lo dicen y se los explico.
Espero no haberlos aburrido.
Saludos!
MaX
Wednesday, November 17, 2010
SQL Query - Activity Monitor
Hi All,
Here is quick one.
There is times when you look at the SQL Server activity monitor and you find out that some job triggered by the SQL Agent it's consuming resources or locking processes, and many times when that happen you could have a hard time getting the info from the visual interface from Jobs Activity because you have many jobs running at the same time and you need to identify which is the problematic job.
The problem is that when that happen when you look at the ProgramName column you only will see something like this:
I've used a query to the master..sysprocesses table to return the results in the image above (it's really the same the Activity Monitor GUI does).
Well, as you can see the JobId it's coded and there is no clear reference to which job is generating the problem and as I said, if you have many jobs running at the same time it's a little hard to get the job identified.
Well, for that cases I wrote a query that displays the same information from the Activity Monitor, BUT the decodes the JobName from the JobID and displays the JobName so you can quickly identify the source of your problem.
Here is the code you can execute.
'----------------------------------------------------------------------------------
declare @jobid varchar(60),
@jobname varchar(200),
@stmt nvarchar(100),
@stmt_select varchar(100)
if object_id('tempdb..#procesos')is not null
drop table #procesos;
select spid,
loginame,
blocked,
waittime,
lastwaittype,
db_name(dbid) as dbname,
cpu,
physical_io,
memusage,
open_tran,
status,
cmd,
hostname,
login_time,
last_batch,
program_name,
uid,
hostprocess
into #procesos
from master.dbo.sysprocesses
order by spid
declare cur_procesos cursor for
select substring(program_name,patindex('%0x%',program_name), patindex('%:%',program_name)-patindex('%0x%',program_name))
from #procesos
where program_name like '%Jobstep%'
open cur_procesos
fetch next from cur_procesos into @jobid
while @@fetch_status = 0
begin
set @stmt = N'select @stmt_jobname=name from msdb.dbo.sysjobs where job_id = '+@jobid
exec sp_executesql @stmt,
N'@stmt_jobname varchar(100) OUTPUT',
@stmt_jobname = @jobname OUTPUT;
if (@jobname is not null and @jobname <> '')
begin
set @jobname = 'SQLAgentJob: '+@jobname;
update #procesos
set program_name = @jobname
where current of cur_procesos;
end
fetch next from cur_procesos into @jobid
end
close cur_procesos
deallocate cur_procesos
set @stmt_select = 'select * from #procesos'
exec (@stmt_select)
drop table #procesos
'----------------------------------------------------------------------------------
I hope this help you.
RegardS!
MAX!
Here is quick one.
There is times when you look at the SQL Server activity monitor and you find out that some job triggered by the SQL Agent it's consuming resources or locking processes, and many times when that happen you could have a hard time getting the info from the visual interface from Jobs Activity because you have many jobs running at the same time and you need to identify which is the problematic job.
The problem is that when that happen when you look at the ProgramName column you only will see something like this:
I've used a query to the master..sysprocesses table to return the results in the image above (it's really the same the Activity Monitor GUI does).
Well, as you can see the JobId it's coded and there is no clear reference to which job is generating the problem and as I said, if you have many jobs running at the same time it's a little hard to get the job identified.
Well, for that cases I wrote a query that displays the same information from the Activity Monitor, BUT the decodes the JobName from the JobID and displays the JobName so you can quickly identify the source of your problem.
Here is the code you can execute.
'----------------------------------------------------------------------------------
declare @jobid varchar(60),
@jobname varchar(200),
@stmt nvarchar(100),
@stmt_select varchar(100)
if object_id('tempdb..#procesos')is not null
drop table #procesos;
select spid,
loginame,
blocked,
waittime,
lastwaittype,
db_name(dbid) as dbname,
cpu,
physical_io,
memusage,
open_tran,
status,
cmd,
hostname,
login_time,
last_batch,
program_name,
uid,
hostprocess
into #procesos
from master.dbo.sysprocesses
order by spid
declare cur_procesos cursor for
select substring(program_name,patindex('%0x%',program_name), patindex('%:%',program_name)-patindex('%0x%',program_name))
from #procesos
where program_name like '%Jobstep%'
open cur_procesos
fetch next from cur_procesos into @jobid
while @@fetch_status = 0
begin
set @stmt = N'select @stmt_jobname=name from msdb.dbo.sysjobs where job_id = '+@jobid
exec sp_executesql @stmt,
N'@stmt_jobname varchar(100) OUTPUT',
@stmt_jobname = @jobname OUTPUT;
if (@jobname is not null and @jobname <> '')
begin
set @jobname = 'SQLAgentJob: '+@jobname;
update #procesos
set program_name = @jobname
where current of cur_procesos;
end
fetch next from cur_procesos into @jobid
end
close cur_procesos
deallocate cur_procesos
set @stmt_select = 'select * from #procesos'
exec (@stmt_select)
drop table #procesos
'----------------------------------------------------------------------------------
I hope this help you.
RegardS!
MAX!
Tuesday, November 16, 2010
ToolBox - WinSQL an ODBC Based SQL Client
Hi All,
Today I will quickly review one of the Apps from my personal Toolbox.
In this case I would like to talk about WinSQL Client.
WinSQL it's an ODBC based SQL Client which uses any ODBC installed on your machine to connect to the database. This feature makes it very flexible regarding to which database could connect to, since ODBC protocol it's very ANSI and very universal for all kind of databases. So if your DB Engine has an ODBC Driver you can connect to it.
This App has several editions, Trial, Lite, Developer and Professional.
We can download for free the Trial and if we register they will send us the Lite Edition registration number which is for free.
http://www.synametrics.com/SynametricsWebApp/Download.do?ProgName=WinSQL
I actually started to use this one several years ago when I had one big problem, I had to manage to connect to SQL Server and Oracle while I was making work as a consultant. The problem was that not all companies has the proper tools installed in the PC that they let me use (and of course I didn't had administrative rights over them to install anything), so I had to come up with an App which were portable (no installation needed) and could connect to both SQL and Oracle, so I could run my scripts without issues.
This one was the best option available, and fortunetly we have the possibility to download an version without installer (Portable). This is very useful resource to add to your personal toolbox. I always kept this one in my pendrive which I carry everywhere I go (while working of course :P ).
Here is a couple of screens about the Tools. I'm currently using version 4 (it's quite old) but the current one you can download I think it's 8.
I hope this helps you in your work!
Regards!
MAX!
Today I will quickly review one of the Apps from my personal Toolbox.
In this case I would like to talk about WinSQL Client.
WinSQL it's an ODBC based SQL Client which uses any ODBC installed on your machine to connect to the database. This feature makes it very flexible regarding to which database could connect to, since ODBC protocol it's very ANSI and very universal for all kind of databases. So if your DB Engine has an ODBC Driver you can connect to it.
This App has several editions, Trial, Lite, Developer and Professional.
We can download for free the Trial and if we register they will send us the Lite Edition registration number which is for free.
http://www.synametrics.com/SynametricsWebApp/Download.do?ProgName=WinSQL
I actually started to use this one several years ago when I had one big problem, I had to manage to connect to SQL Server and Oracle while I was making work as a consultant. The problem was that not all companies has the proper tools installed in the PC that they let me use (and of course I didn't had administrative rights over them to install anything), so I had to come up with an App which were portable (no installation needed) and could connect to both SQL and Oracle, so I could run my scripts without issues.
This one was the best option available, and fortunetly we have the possibility to download an version without installer (Portable). This is very useful resource to add to your personal toolbox. I always kept this one in my pendrive which I carry everywhere I go (while working of course :P ).
Here is a couple of screens about the Tools. I'm currently using version 4 (it's quite old) but the current one you can download I think it's 8.
I hope this helps you in your work!
Regards!
MAX!
SQL 2000 - The ArithAbort affects execution plan
A couple of weeks ago I was asked to take a look on a Database View that had some serious performances issues, but with some odd behavior.
I will describe the scenario for you here..
- SQL Server 2000 SP4 Engine, Standard Edition (build 2055)
- This is a very complex view with several subqueries in it, many of them in the Where clause in many others as part of the result set.
The application were this was running it's an old version of an application which uses ODBC to connect to the database.
The thing is that same query to that view runned within the application and from Query Analyzer or SSMS gave different behaviors.
In the first case the query seems to hang and never return the results.
In the second case the query returned the results in just 5 secs.
So, I took several approaches to this problem, here I will enumerate everything that I've considered and tested in order to get to a solution.
- I ruled out the possibility of the problem was the query or the view itself seems the query works on from SSMS or Query Analyzer, so it could not be the query syntax by itself.
- I ran the query using the option SET SHOWPLAN_ALL ON, so it throws me the execution plan.
The problem here it's that I needed to emulate the ODBC connection to the DB, so I needed to get a third party tool from my reliable Pendrive (my personal toolbox), a little application totally portable called WINSQL which connects to any database using the ODBC connections from the local machine. So I ran it in both once in SSMS and another run with WINSQL.
The results from both apps I put it in another app from my toolbox WinMerge to compare both plans line by line.
Here was my surprise when I found out that the same query throwed very different results depending if you use SSMS or WINSQL to run it.
So my first question was, why the hell the SQL optimizer is producing two different plans for the exactly same query.
My first thought was, must be the god damn ODBC Driver. So I performed another battery of test making the following changes on the ODBC Drivers:
- Ran the query using the SQL Server Native Cliente Drivers from SQL 2008 SP1.
- Replace the sqlsrv32.dll DLL from MDAC 2.81 SP1 from the one from MDAC 2.81 SP2.
- Installed a third party ODBC Driver using the DataDirect SQL Wired Drivers.
Non of these test produced different results, the execution plan for every of them was the exactly horrible plan from the beginning.
So I start to think that may be some specific configuration from the ODBC could be messing the plan, my first thoughts were the SET ANSI_NULLS and QUOTED_IDENTIFIERS and the PACKETSIZE. So I did these test to probe if I was right:
Ran the query changing in the ODBC Connection String the following parameters:
- SET QUOTED_IDENTIFIERS and ANSINULLS ON
- SET QUOTED_IDENTIFIERS and ANSINULLS OFF
- SET QUOTED_IDENTIFIERS ON and ANSINULLS OFF
- SET QUOTED_IDENTIFIERS OFF and ANSINULLS ON
- All the same of above both specifically set the PACKETSIZE in the connection string to the same value as it's configured on the SQL Server (using sp_configure to know which value was).
Non of the tests worked as expected, everyone throw the exact laussy execution plan.
So finally scratching my head without much more in it, I start to make a deep search on the wave to try to find anything about this kind of behavior.
Finally one blog article describe that some oddities in the execution plan could occur when you have the SET ARITHABORT user option OFF.
And checking I found out that the damn option was automatically set as default from the Query Analyzer or SSMS (it's a default in the user preferences).
But since this is a execution parameter the ODBC does not have any way to pre-set this to be a default, so of course the ODBC was taking the default settings configured on the Database or Instance level of SQL Server which off course was setted in OFF (it's the default).
I made a final test and found that when I explicitly SET ARITHABORT ON on the ODBC query pane and execute it with the query TA DA, magically the query return results in about 5-6 secs and the execution plan it's identical to the produced when run it from SSMS.
According to BOL, the change of the choices of the optimizer to generate the execution plan it's affected by the ARITHABORT when you have Indexed Views or Computed Columns (which I don't have in my database).
So this was the solution, I mean I only had to change the Database ARITHABORT option setting using ALTER DATABASE Statement and everything start to work fine (don't reboot or outage needed).
For a quick fix was good, well quick(?).. It took me the whole day to find this out.
A couple of days later there was something bothering me, because it does not make sense that the ARITHABORT option affected the execution plan when non of the conditions to it was present, so I started to make a deep analysis of the problem.
What I found out it's that the execution plan for this View has an operation on it called RANK, this operation as per it's description on the visual plan, says that creates a computed column to sort the results. TA DA again, that was the reason, the optimizer was creating at runtime a computed column to perform some kind of sorting and therefore triggering the ARITHABORT constraint.
I still have to analyze which part exactly of this gigantic view triggers the RANK operation (I didn't have time to do it yet), but I will.
This is a pretty weird case at least in my experience and make you thing twice when you analyze something like this because many times the optimizer could do things that indirectly affects the behavior or add constraints to the behavior of the resulting execution plan.
Hope this helps to anyone!.
MAX!
I will describe the scenario for you here..
- SQL Server 2000 SP4 Engine, Standard Edition (build 2055)
- This is a very complex view with several subqueries in it, many of them in the Where clause in many others as part of the result set.
The application were this was running it's an old version of an application which uses ODBC to connect to the database.
The thing is that same query to that view runned within the application and from Query Analyzer or SSMS gave different behaviors.
In the first case the query seems to hang and never return the results.
In the second case the query returned the results in just 5 secs.
So, I took several approaches to this problem, here I will enumerate everything that I've considered and tested in order to get to a solution.
- I ruled out the possibility of the problem was the query or the view itself seems the query works on from SSMS or Query Analyzer, so it could not be the query syntax by itself.
- I ran the query using the option SET SHOWPLAN_ALL ON, so it throws me the execution plan.
The problem here it's that I needed to emulate the ODBC connection to the DB, so I needed to get a third party tool from my reliable Pendrive (my personal toolbox), a little application totally portable called WINSQL which connects to any database using the ODBC connections from the local machine. So I ran it in both once in SSMS and another run with WINSQL.
The results from both apps I put it in another app from my toolbox WinMerge to compare both plans line by line.
Here was my surprise when I found out that the same query throwed very different results depending if you use SSMS or WINSQL to run it.
So my first question was, why the hell the SQL optimizer is producing two different plans for the exactly same query.
My first thought was, must be the god damn ODBC Driver. So I performed another battery of test making the following changes on the ODBC Drivers:
- Ran the query using the SQL Server Native Cliente Drivers from SQL 2008 SP1.
- Replace the sqlsrv32.dll DLL from MDAC 2.81 SP1 from the one from MDAC 2.81 SP2.
- Installed a third party ODBC Driver using the DataDirect SQL Wired Drivers.
Non of these test produced different results, the execution plan for every of them was the exactly horrible plan from the beginning.
So I start to think that may be some specific configuration from the ODBC could be messing the plan, my first thoughts were the SET ANSI_NULLS and QUOTED_IDENTIFIERS and the PACKETSIZE. So I did these test to probe if I was right:
Ran the query changing in the ODBC Connection String the following parameters:
- SET QUOTED_IDENTIFIERS and ANSINULLS ON
- SET QUOTED_IDENTIFIERS and ANSINULLS OFF
- SET QUOTED_IDENTIFIERS ON and ANSINULLS OFF
- SET QUOTED_IDENTIFIERS OFF and ANSINULLS ON
- All the same of above both specifically set the PACKETSIZE in the connection string to the same value as it's configured on the SQL Server (using sp_configure to know which value was).
Non of the tests worked as expected, everyone throw the exact laussy execution plan.
So finally scratching my head without much more in it, I start to make a deep search on the wave to try to find anything about this kind of behavior.
Finally one blog article describe that some oddities in the execution plan could occur when you have the SET ARITHABORT user option OFF.
And checking I found out that the damn option was automatically set as default from the Query Analyzer or SSMS (it's a default in the user preferences).
But since this is a execution parameter the ODBC does not have any way to pre-set this to be a default, so of course the ODBC was taking the default settings configured on the Database or Instance level of SQL Server which off course was setted in OFF (it's the default).
I made a final test and found that when I explicitly SET ARITHABORT ON on the ODBC query pane and execute it with the query TA DA, magically the query return results in about 5-6 secs and the execution plan it's identical to the produced when run it from SSMS.
According to BOL, the change of the choices of the optimizer to generate the execution plan it's affected by the ARITHABORT when you have Indexed Views or Computed Columns (which I don't have in my database).
So this was the solution, I mean I only had to change the Database ARITHABORT option setting using ALTER DATABASE Statement and everything start to work fine (don't reboot or outage needed).
For a quick fix was good, well quick(?).. It took me the whole day to find this out.
A couple of days later there was something bothering me, because it does not make sense that the ARITHABORT option affected the execution plan when non of the conditions to it was present, so I started to make a deep analysis of the problem.
What I found out it's that the execution plan for this View has an operation on it called RANK, this operation as per it's description on the visual plan, says that creates a computed column to sort the results. TA DA again, that was the reason, the optimizer was creating at runtime a computed column to perform some kind of sorting and therefore triggering the ARITHABORT constraint.
I still have to analyze which part exactly of this gigantic view triggers the RANK operation (I didn't have time to do it yet), but I will.
This is a pretty weird case at least in my experience and make you thing twice when you analyze something like this because many times the optimizer could do things that indirectly affects the behavior or add constraints to the behavior of the resulting execution plan.
Hope this helps to anyone!.
MAX!
Subscribe to:
Posts (Atom)