Friday, November 20. 2009
Publicando un blog en Orafaq
El proyecto en el que actualmente trabajo dedico una pequeña parte a la administración de Oracle. Además este año he finalizado la certificación a OCP 10g (estaba certificado en OCA 9 desde 2004), y todas estas experiencias me están generando contenido suficiente para publicarlo en un blog. Lo estoy haciendo en www.orafaq.com, por ser una web especializada en Oracle. El blog se puede leer aquí: http://www.orafaq.com/blog/16208.
Procedimientos almacenados y extendidos no documentados en SQL Server 2005
XP_FileExist
Su uso:
Devuelve 3 columnas con 0 ó 1: File Exists, File is a Directory, Parent Directory Exists.
Ejemplo:
SP_MSForEachDb
Ejecuta una cadena de texto como un comando por cada base de datos en el servidor. Puede ser útil para buscar una tabla de la que no sabes en qué base de datos está, por ejemplo:
También es útil para lanzar la misma tarea de mantenimiento en todas las bases de datos:
SP_MSForEachTable
Funcionamiento parecido a sp_msforeachdb, pero por cada tabla. Con este ejemplo vaciamos todas las tablas dejando sólo la estructura.
SP_who2
Algunos datos iguales que los de sp_who, con otros nombres de columnas, sin columna ecid y con las columnas cputime, diskio, lastbatch y programname añadidas.
sp_MSdependencies
Sirve para determinar las dependencias de un objeto en la base de datos. Su uso:
Todas las variables son opcionales, con:
obtenemos una descripción breve de las opciones.
Su uso:
EXEC xp_fileexist[, OUTPUT]
Devuelve 3 columnas con 0 ó 1: File Exists, File is a Directory, Parent Directory Exists.
Ejemplo:
exec master.dbo.xp_fileexist 'C:\temp'
SP_MSForEachDb
Ejecuta una cadena de texto como un comando por cada base de datos en el servidor. Puede ser útil para buscar una tabla de la que no sabes en qué base de datos está, por ejemplo:
exec dbo.sp_MSforeachdb 'select ''?'', * from [?].INFORMATION_SCHEMA.TABLES where table_name like ''autores'' '
También es útil para lanzar la misma tarea de mantenimiento en todas las bases de datos:
exec dbo.sp_MSforeachdb 'DBCC SHRINKDATABASE(N''?'' )'
SP_MSForEachTable
Funcionamiento parecido a sp_msforeachdb, pero por cada tabla. Con este ejemplo vaciamos todas las tablas dejando sólo la estructura.
exec dbo.sp_msforeachtable 'delete test.dbo.[?]'
SP_who2
Algunos datos iguales que los de sp_who, con otros nombres de columnas, sin columna ecid y con las columnas cputime, diskio, lastbatch y programname añadidas.
sp_MSdependencies
Sirve para determinar las dependencias de un objeto en la base de datos. Su uso:
exec dbo.sp_MSdependencies [
Todas las variables son opcionales, con:
exec sp_msdependencies '?'
obtenemos una descripción breve de las opciones.
Friday, November 6. 2009
Prevenir el uso de "SELECT * FROM..."
Con esta técnica evitamos que los desarrolladores utilicen SELECT * FROM en su código.
Por cada tabla, añadir un campo "nousesasterisco", al que más adelante le denegaremos el acceso de lectura:
Denegamos el SELECT en esa columna por cada tabla:
Tras asignar el rol "rol_deniega" a los usuarios de la aplicación, les aparecerá este mensaje:
Por cada tabla, añadir un campo "nousesasterisco", al que más adelante le denegaremos el acceso de lectura:
ALTER TABLE dbo.[tabla] ADD
nousesasterisco char(1) NULL
GO
[...]
Denegamos el SELECT en esa columna por cada tabla:
DENY SELECT ON OBJECT:: dbo.[tabla](nousesasterisco) TO [rol_deniega];
Tras asignar el rol "rol_deniega" a los usuarios de la aplicación, les aparecerá este mensaje:
SELECT * FROM dbo.tabla;
--Result
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column 'nousesasterisco' of the object 'tabla', database 'Pruebas', schema 'dbo'.
Tuesday, October 13. 2009
Monitorizar SQL Server en 8 pasos (Paso 8/8)
Paso 8: Consolidación de los datos
Los scripts del 8.1 al 8.3 consolidan los resultados del PerfMon en un esquema de estrella. Luego, se puede construir un cubo OLAP sobre esto.
Para limitar las líneas de código, aquí sólo están las dimensiones de objeto y máquina. Una configuración similar hará falta para Fecha, Instancia y dimensiones del contador.
Script 8.1 crea las tabals
Script 8.2 refresca todas las dimensiones
Para recuperar espacio en disco, puedes truncar la tabla CounterData después de haber hecho la carga.
Los scripts del 8.1 al 8.3 consolidan los resultados del PerfMon en un esquema de estrella. Luego, se puede construir un cubo OLAP sobre esto.
Para limitar las líneas de código, aquí sólo están las dimensiones de objeto y máquina. Una configuración similar hará falta para Fecha, Instancia y dimensiones del contador.
Script 8.1 crea las tabals
--Script 8.1
CREATE TABLE dbo.Fact_Perfmon (
PerfMonID int IDENTITY (1, 1) NOT NULL ,
DateID int NULL ,
MachineID int NULL ,
ObjectID int NULL ,
InstanceID int NULL ,
CounterID int NULL ,
CounterValue float NULL
) ON [PRIMARY]
GO
CREATE TABLE dbo.Dim_Machine (
MachineID int IDENTITY (1, 1) NOT NULL ,
MachineName varchar (100) NULL
) ON [PRIMARY]
GO
CREATE TABLE dbo.Dim_Object (
ObjectID int IDENTITY (1, 1) NOT NULL ,
ObjectName varchar (100) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Fact_Perfmon ADD
CONSTRAINT PK_Fact_Perfmon PRIMARY KEY CLUSTERED
(
PerfMonID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Dim_Machine ADD
CONSTRAINT PK_Dim_Machine PRIMARY KEY CLUSTERED
(
MachineID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Dim_Object ADD
CONSTRAINT PK_Dim_Object PRIMARY KEY CLUSTERED
(
ObjectID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Fact_Perfmon ADD
CONSTRAINT FK_Fact_Perfmon_Dim_Machine FOREIGN KEY
(
MachineID
) REFERENCES dbo.Dim_Machine (
MachineID
),
CONSTRAINT FK_Fact_Perfmon_Dim_Object FOREIGN KEY
(
ObjectID
) REFERENCES dbo.Dim_Object (
ObjectID
)
GO
Script 8.2 refresca todas las dimensiones
--Script 8.2
INSERT Dim_Machine (MachineName) SELECT DISTINCT MachineName FROM MonitoringDB.DBO.CounterDetails WHERE MachineName not in (SELECT DISTINCT MachineName from Dim_Machine)
INSERT Dim_Object (ObjectName) SELECT DISTINCT ObjectName FROM MonitoringDB.DBO.CounterDetails WHERE ObjectName not in (SELECT DISTINCT ObjectName from Dim_Object)
Script 8.3 refreshes your fact table
--Script 8.3
SELECT dd.DateID
, dm.MachineID
, do.ObjectID
, dc.CounterID
, di.InstanceID
, dat.CounterValue
FROM MonitoringDB.DBO.CounterData Dat
INNER JOIN MonitoringDB.DBO.CounterDetails Det ON Dat.CounterID = Det.CounterID
INNER JOIN Dim_Date dd ON DateValue = Dat.CounterDateTime
INNER JOIN Dim_Machine dm ON dm.MachineName = Det.MachineName
INNER JOIN Dim_Object do ON do.ObjectName=Det.ObjectName
INNER JOIN Dim_Counter dc ON dc.CounterName= Det.CounterName
INNER JOIN Dim_Instance di ON di.InstanceName = Det.InstanceName
Para recuperar espacio en disco, puedes truncar la tabla CounterData después de haber hecho la carga.
Monitorizar SQL Server en 8 pasos (Paso 7/8)
Paso 7: El panel de control digital
Si quieres más flexibilidad para manejar las alertas, también puedes almacenar valores de umbrales en una tabla de la base de datos. De esta manera un script externo y/o un interfaz gráfico de usuario puede comparar los valores almacenados con los umbrales y generar todo tipo de mecanismos de alerta. Esto es más potestad de los desarrolladores, pero aunque no lo seas, no es algo difícil.
Si quieres más flexibilidad para manejar las alertas, también puedes almacenar valores de umbrales en una tabla de la base de datos. De esta manera un script externo y/o un interfaz gráfico de usuario puede comparar los valores almacenados con los umbrales y generar todo tipo de mecanismos de alerta. Esto es más potestad de los desarrolladores, pero aunque no lo seas, no es algo difícil.
