Se puede dar el caso que necesitemos acceder a MySql desde Sql Server 2008. Para hacerlo podemos crear un servidor vinculado que use una conexión odbc. Como hacerlo paso a paso:

1. Descargar el cliente ODBC de Mysql para la plataforma del sevidor sql. Lo podeis hacer aquí.

2. Instalarlo (siguiente, siguiente, siguiente) y configurar un DSN de sistema. Para ello en el Administrador de orígenes de datos ODBC, en la pestaña DSN de sistema pulsamos Agregar y seleccionamos MySQL ODBC 5.1 driver. Acepatemos y aparece un formulario como el siguiente. Lo rellenaremos y probaremos pulsando Test para comprobar que funciona.

 

3. Añadir el servidor vinculado en la base de datos. Para ello nos conectamos a la base de datos y en el árbol de objetos vamos a  Objetos de servidor, pulsamos botón derecho en Servidores vinculados y clickamos en Nuevo Servidor Vinculado, rellenamos los datos y ya lo tenemos.

 

4. Al finalizar el paso anterior ya tendremos acceso al servidor. Podemos verlo en la lista de servidores vinculados y opdremos navegar para ver las tablas que hay en la base de datos que hemos vinculado. También podremos usarlo para hacer consultas mediante la función tsql openquery.

Sintaxis: select [campo,] from OPENQUERY([servidor vinculado], [select mysql]).

 

Al igual que en Oracle existe una tabla donde se listan todas las tablas de la base de datos (dba_tables) y podemos usarla para realizar operaciones de mantenimiento de forma dinámica, en Sql Server podemos hacer lo mismo consultando la tabla [basededatos].dbo.sysobjects.

En el ejemplo inferior (como en otros que he colgado) actualizo las estadísticas de todas las tablas de una base de datos de Sql Server de forma dinámica consultando el diccionario de datos. Este se podría encapsular en un stored procedure o directamente ejecutarlo en un job del Agente de Sql Server para mantener actualizadas las estadísticas de todas las tablas de una base de datos de forma automática.

-- Declaración de variables
    DECLARE @dbName    sysname
    DECLARE @sample int
    DECLARE @SQL nvarchar(4000)
    DECLARE @ID int
    DECLARE @Tabla sysname
    DECLARE @RowCnt int

    --Filtro por base de datos y porcentaje para recalculo de estadísticas
    SET @dbName = 'AdventureWorks2008'
    SET @sample = 100

    --Tabla temporal
    CREATE TABLE ##Tablas
    (
     TableID INT IDENTITY(1, 1) NOT NULL,
     TableName SYSNAME NOT NULL
    )

    --Alimentamos la tabla con la lista de tablas
    SET @SQL = ''
    SET @SQL = @SQL + 'INSERT INTO ##Tablas (TableName) '
    SET @SQL = @SQL + 'SELECT [name] FROM ' + @dbName + '.dbo.sysobjects WHERE xtype = ''U'' AND [name] <> ''dtproperties'''

    EXEC sp_executesql @statement = @SQL

    SELECT TOP 1 @ID = TableID, @Tabla = TableName
    FROM ##Tablas
    ORDER BY TableID

    SET @RowCnt = @@ROWCOUNT
   
    --Por cada tabla
    WHILE @RowCnt <> 0
    BEGIN

     SET @SQL = 'UPDATE STATISTICS ' + @dbname + '.dbo.[' + @Tabla + '] WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'

     EXEC sp_executesql @statement = @SQL

     SELECT TOP 1 @ID = TableID, @Tabla = TableName
     FROM ##Tablas
     WHERE TableID > @ID
     ORDER BY TableID

     SET @RowCnt = @@ROWCOUNT

    END

--Eliminamos la tabla
DROP TABLE ##Tablas

 

use <base de datos a usar>

SET @SQL = 'INSERT INTO ##Tablas(TableName) '
    SET @SQL = @SQL + ' select ''['' + TABLE_CATALOG + ''].['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'' from INFORMATION_SCHEMA.TABLES'
    EXEC sp_executesql @statement = @SQL

-- Luego solamente usamos el nombre de la tabla para actualizar estadísticas:
SET @SQL = 'UPDATE STATISTICS ' + @Tabla + ' WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'
     EXEC sp_executesql @statement = @SQL

Puede ser que en algun momento pueda ser necesario cambiar el tipo de columna clave para nuestra tabla/s por un mal diseño o un cambio posterior x que nos obliga a ello. Si lo hacemos y la nueva tiene que ser una columna del tipo entero, quizás identity, podemos hacerlo con algun criterio para que quede ordenado (pk=indice clusterizado=order by en disco por ese campo). En este ejemplo para hacerlo más completo, lo hacemos en dos tablas Maestro-Detalle donde los campos claves son de tipo nchar (CABECERAS.ID_CABECERA y LINEAS.ID_LINEA). Pasos a seguir...

• Empezamos por elegir el tipo de columna y la añadimos a la tabla principal, CABECERAS. Seguimos con un update que parte de una join con una select sobre la misma tabla incluyendo la función ROW_NUMBER() para el contador de linea que nos servira como valor autoincremental (identity?). 

USE DBTEST; ALTER TABLE CABECERAS ADD PK_CABECERA int NULL; UPDATE CABECERAS SET PK_CABECERA = A.NUMERO_FILA FROM CABECERAS INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY FECHA ASC) AS NUMERO_FILA FROM CABECERAS) A ON CABECERAS.ID_CABECERA = A.ID_CABECERA;

• A coninuacións seguimos con el detalle, LINEAS, donde hacemos lo mismo que antes con su propia PK. Para mantener el mismo orden hacemos una join con la tabla CABECERAS y aprovechamos también para asignar también el valor de la nueva foreign key.

USE DBTEST; ALTER TABLE LINEAS ADD PK_LINEA int NULL; ALTER TABLE LINEAS ADD FK_CABECERA int NULL; UPDATE LINEAS SET PK_LINEA = A.NUMERO_FILA, FK_CABECERA = PK_CABECERA FROM LINEAS INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY FECHA ASC) AS NUMERO_FILA, ID_LINEA, PK_CABECERA FROM LINEAS INNER JOIN CABECERAS ON CABECERAS.ID_CABECERA=LINEAS.ID_CABECERA) A ON LINEAS.ID_LINEA = A.ID_LINEA;

Ahora que ya tenemos los nuevos candidatos a campo clave, los marcaremos como NOT NULL, como campo clave y optaremos si es necesario por ponerlos como identity. También modificaremos la FOREIGN KEY o añadiremos una nueva. Una forma muy fácil de hacer lo anterior es hacerlo de forma visual, usando Management Studio, en la parte de Diagramas de Bases de datos (en el árbol del Explorador de objetos).

Por cierto, si finalmente hace falta que las nuevas claves sea identity deberemos recuperar antes el valor máximo del campo en las dos tablas para asignar el valor inicial de la identidad. Si queremos ser más puristas, debemos saber que desde SqlServer 2012 existen las secuencias y ya podríamos hacer lo mismo usándolas. Es interesante saber por una eventual migración de tipo de base de datos. A saber, Oracle también permite el uso de secuencias pero no existe equivalente a campos identity. MySql en cambio si tiene identity y es AUTO_INCREMENT.

 

 

 

¿Alguna vez os habeis preguntado como deshabilitar el autocommit en el Management Studio de Sql Server? Pues la respuesta es rápida. Lo podeís cambiar activando  en el menú Herramientas > Opciones > Ejecución de la consulta > SQL Server > Ansi > SET IMPLICIT_TRANSACTIONS.

 

 

Parece algo simple pero como me lo han preguntado un par de veces...

Hay muchas maneras de liarla con nuestra base de datos SqlServer y una de ellas es olvidar o no tener a nuestra disposición la clave del usuario sa. Puede ser debido a que nunca la usemos, porque tenemos nuestro propio usuario administrador y realmente no iniciamos sesión con esa cuenta. Puede ser que hayamos heredado esa maravillosa base de datos y no tengamos ningún usuario ni de dominio que sea administrador. O peor aún, que incluso desde el mismo día de la instalación no sepamos esa clave y acabamos de eliminar el único login con los permisos adecuados... Para cualquiera de estas historias tristes puede haber una solución que no sea reinstalar.

Desde SqlServer 2005 (y hasta SqlServer 2012 que yo sepa) existe, como plan de recuperación para este tipo de desastre, la posibilidad de arrancar la base de datos SqlServer en modo "single-user" y poder acceder a ella con cualquier usuario de miembro del grupo de administradores del sistema. Arrancar la base de datos en single-user está pensado para realizar tareas de mantenimiento, como por ejemplo aplicar patches y realizar otras tareas. En nuestro caso, donde hemos perdido la clave del usuario sa, nos permitirá, una vez limitado el acceso, conectarnos por ejemplo via sqlcmd y agregar un usuario de la base de datos al rol sysadmin dentro de Sql Server. A continuación un resumen...

Pasos a seguir:

• Abrimos el Administrador de Configuración de SqlServer. Buscamos el Servicio de SqlServer y miramos las Propiedades, en la pestaña Opciones Avanzadas o Parámetros de inicio añadimos un -m al final de la linea. (puede variar segun la versión). Presionamos aceptar y reiniciamos el servicio.

 

• Una vez reiniciado, abrimos la linea de comandos y el cliente sqlcmd. Ejecutamos lo siguiente:

 sqlcmd -S localhost

 1> EXEC sp_addsrvrolmember 'DOMINIO\Dba','sysadmin';

 

• Volvemos al Administrador de Configuración de SqlServer, quitamos el -m que hemos añadido en el primer punto y volvemos a reiniciar el servicio. Finalmente probamos de acceder con el usuario de dominio anterior y restauramos la clave del usuario sa (y la guardamos en lugar seguro). Misión cumplida.

Existe una variante sobre el uso del parámetro -m para arrancar la bbdd en modo usuario único, es -m"Nombre app" (En el nombre de la aplicación cliente se distinguen mayúsculas y minúsculas). Este uso del parámetro limita las conexiones a una aplicación cliente con el nombre especificado. Por ejemplo, -m"SQLCMD" limita las conexiones a una conexión única y esa conexión se debe identificar como el programa cliente SQLCMD. Se puede usar esta opción cuando estemos iniciando SQL Server en modo de usuario único y una aplicación cliente desconocida esté usando la única conexión disponible. Para limitarlo al Management Studio, usamos -m"Microsoft SQL Server Management Studio - Query".

 

---

Libros de SQL Server

¿Quieres profundizar más en Transact-SQL o en administración de bases de datos SQL? Puedes hacerlo consultando alguno de estos libros de SQL Server.

Mejor revisa la lista completa de los últimos libros de SQL Server publicados en Amazon según lo que te interese aprender, pero estos son los que a mi me parecen más interesantes, teniendo en cuenta precio y temática:

• eBooks de SQL Server gratuítos para la versión Kindle, o muy baratos (menos de 4€):

• Libros recomendados de SQL Server

---

 

 

En ocasiones podemos necesitar hacer un informe que debe contener datos de nuestro cubo de ventas (por ejemplo) y complementarlo con datos que nos faltan y que solo podemos encontrar en el esquema relacional del origen o directamente de otra fuente de datos. 

Si la bbdd complementaria está en una instancia de sql server, una solución bastante comoda es crear un servidor vinculado. Eso nos permita hacer la consulta MDX desde la propia instancia de SQL Server y hacer una JOIN para conseguir los datos que no tenemos en el cubo. Si nos paramos a pensarlo, quizás no es lo más elegante e incluso puede ocultar alguna carencia de diseño, pero seguro que cosas peores hemos hecho. 

A continuación los cuatro pasos a seguir para crear el servidor vinculado y la construcción de la query trivial para completar el total de ventas anual con el nombre del encargado de la tienda.

 

Primero preparamos la consulta MDX con los datos del cubo que podamos necesitar y un campo clave con el que luego podamos relacionar la otra tabla:

SELECT [Measures].[Sales amount] ON COLUMNS, [Business].[Store id].members ON ROWS FROM [Sales] WHERE [Time].[Year].&[2013]

 

Empezamos por ir al Explorador de objetos y buscamos en nuestra instancia el apartado Objetos del servidor>Servidores vinculados> Nuevo servidor vinculado. Complementamos con los datos de la instancia SSAS que corresponda.

 

 

Editamos la consulta sobre la instancia de Sql Server. Aquí encapsulamos la consulta MDX en una nueva consulta SQL con openquery. El tip aquí para facilitar la selección de campos es usar alias ya que por defecto el encabezado de la columna nos saldra con el formato mdx del atributo/medida. Luego simplemente hacemos la JOIN como queramos con la otra tabla y listos.

SELECT ssas.Store_id ,ssas.Sales_Amount ,store.manager FROM (SELECT "[Business].[Store id].[MEMBER_CAPTION]" as Store_id
,"[Measures].[Sales amount]" AS Sales_Amount
FROM openquery( SSAS_INSTANCE, 'SELECT [Measures].[Sales amount] ON COLUMNS,
[Business].[Store id].members ON ROWS
FROM [Sales]
WHERE [Time].[Year].&[2013]')) ssas
LEFT JOIN [dbo].[Stores] store ON ssas.Store_id = store.Store_id

 

 

En ciertas ocasiones necesitamos comprobar la existencia de una tabla en un script o tarea programada para poder registrar eventos de erro, primeras ejecuciones etc... Pongamos un ejemplo, un paquete de integration services que solemos distribuir o ejecutar allà a donde vamos y que deja trazas en un tabla personalizada que no es la predefinida para los logs de carga. Podríamos incluir siempre una Tarea de ejecución sql o de script, que se ejecute bien o mal, sea la primera en ejecutarse en el paquete y luego continue. Siendo puristas esto solo no es del todo “prolijo”:

CREATE TABLE LogsEtl
(Ejecucion int PRIMARY KEY,
Paquete varchar(50),
Fecha datetime);
GO

En la primera ejecución la salida será correcta pero en posteriores fallará en la creación de la tabla. Esto lo podemos suplir consultando la vista sys.Objects, donde existe un registro por cada objeto de la base de datos, y comprobar la existencia de la tabla antes de crearla. La visibilidad de los metadatos se limita a los elementos protegibles y que son propiedad del usuario o sobre los que el usuario tiene algún permiso. La estructura de la vista es la siguiente:

name                 (sysname)
object_id             (int)
principal_id            (int)
schema_id            (int)
parent_object_id        (int)
type                (char(2))
type_desc            (nvarchar(60))
create_date            (datetime)
modify_date            (datetime)
is_ms_shipped        (bit)
is_published            (bit)
is_schema_published    (bit)

Si cambiamos la sentencia anterior por un create table condicionado por una consulta sobre la vista buscando por el nombre de la tabla como parámetro de object_id(función que devuelve el identificador único de un objeto por su nombre) tendremos algo así:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogsEtl]') AND TYPE = N'U')
CREATE TABLE LogsEtl
(Ejecucion int PRIMARY KEY,
Paquete varchar(50),
Fecha datetime);
GO

De esta manera lo estamos haciendo “prolijo” y la ejecución siempre será correcta (a no ser por la falta de permisos).
Realmente esto tiene otras aplicaciones porque podríamos hacer cualquier tipo de script condicionado por la existencia o no de objetos en la bbdd o modificación. Por ejemplo uno mismo podría hacer una consulta o script universal que actualizara/reconstruyera índices en base al tiempo que ha pasado desdela última modificación. Podríamos controlar a nivel de administración que esta “inventando” aquel usuario con permisos de más etc etc... en msdn hay algun ejemplo más.

Dado que es algo que se suele repetir y ya me lo han comentado más de una vez, creo oportuno crear un post donde se describa  el problema y la solución en Sql server 2008 para newbies. Más que nada para que no perdaís tiempo buscando...

Problema:
Al modificar el tipo de campo en una tabla ya creada (pero vacía) o al añadir alguna clave foránea me aperece un mensaje como el siguiente:"No se permite guardar los cambios. Los cambios que ha realizado requieren que se quiten y se vuelvan a crear las tablas".

Ejemplo:
 

 

Solución:
Desmarcar la opción que impide hacer este tipo de cambios. Para ello debemos abrir la pantalla de opciones (Herramientas>Opciones), y en el apartado Designers>Diseñadores de tabla y base de datos desmarcar la opción Impedir guardar cambios que requieran volver a crear tablas. Captura:
 

 

 

 

Con SQL Server podemos caer muy fácilmente en lo que se dice habitualmente sobre los productos Microsoft "Siguiente, siguiente y listo". No vamos a negarlo, Microsoft consigue hacer que gente sin mucha idea salga adelante y es todo un mérito. Pero vayamos al tema. Si se empieza una nueva aplicación y tenemos que crear la estructura de datos, no dejemos solos a los desarroladores y tampoco que usen el MS Management Studio. Normalmente, en lo que a la base de datos se refiere, cuando se crean se tienen en cuenta varias cosas:

1. Ajuste adecuado de los tipos de datos para cada columna
2. Foreign Keys e índices
3. Tamaños por defecto en ficheros de log
4. Fillfactor en los índices

Los dos primeros puntos son las buenas prácticas que se suelen comentar pero poca cosa podemos hacer como administradores de la base de datos, más que asegurarnos que tiene lugar y ayudar si es necesario. Además el tema índices es algo que se puede plantear más tarde. Pero de los dos últimos puntos somos los responsables. Deberíamos conocer el tipo de aplicación, el uso que tendrá(lect/escr) y estimar el volumen de crecimiento de los datos para poder aportar nuestro granito de arena.

Inicialmente podemos ajustar el tamaño de los archivos de la base de datos (propiedades de la base de datos). Si esperamos montar una base de datos que crecerá muy rápido incrementaremos el tamaño inicial si hace falta y ajustaremos el crecimiento de los archivos de Registro(.ldf) y de Datos de filas (.mdf). Si por el contrario es pequeña podríamos dejar los valores por defecto. Ajustando este valor evitaremos diseminar los datos por el disco (son las dos imágenes de abajo).
Otro tema a tener en cuenta y que también tiene impacto es jugar con los valores de fillfactor de los índices, en base también al porcentaje de lectura/escritura y el volumen de datos.

 

Archivos de la base de datos

Configuración del crecimiento automático.

Eso entre otras cosas de las que ya hablaré. También es interesante que si tenemos poco espacio en disco para datos y ya no vale solo con el SHRINKFILE para vaciar logs, ante una situación de crisis podemos jugar con las prioridades y poner límites en el crecimiento automático para ciertas bases de datos que crecen de forma desmesurada ...

 

A continuación dejo un par de ejemplos de como funciona el backup simple de sqlserver 2008 y como hacer un restore. En el primer ejemplo hacemos un drop de la base de datos que en un entorno real puede significar la perdida de un datafile o disco etc etc. El segundo ejemplo es algo más rebuscado y lo que se hace es restaurar la copia de la base de datos para recuperar una tabla y extraer sus datos. En ambos ejemplos se trabaja con bases de datos de ejemplo descargables aquí.

 

Ejemplo 1. BACKUP Y RECUPERACION SIMPLE EN CASO DE PERDIDA DE LA BASE DE DATOS

En el siguiente ejemplo se hace una copia simple y una diferencial de la base de datos AdventureWorks2008. Una vez hecho se hace un drop de la base de datos para luego restaurar la base de datos al último estado antes de borrar la base de datos.

USE master;
--Modo de recuperació simple para la base de datos en cuestion
ALTER DATABASE AdventureWorks2008 SET RECOVERY SIMPLE;
GO
--Backup simple
BACKUP DATABASE AdventureWorks2008 TO DISK='F:\SQL08\BACKUPDATA\AW2008_Full.bak'
WITH FORMAT;
GO
--Backup diferencial
BACKUP DATABASE AdventureWorks2008 TO DISK='F:\SQL08\BACKUPDATA\AW2008_Diff.bak'
WITH DIFFERENTIAL;
GO

--Destrucción!!!!
DROP DATABASE AdventureWorks2008;
GO

--Restauración del último backup
RESTORE DATABASE AdventureWorks2008 FROM DISK='F:\SQL08\BACKUPDATA\AW2008_Full.bak'
WITH FILE=1, NORECOVERY;
--Restauración de los diferenciales
RESTORE DATABASE AdventureWorks2008 FROM DISK='F:\SQL08\BACKUPDATA\AW2008_Diff.bak'
WITH FILE=1, RECOVERY;

Para este ejemplo cabe destacar que cada vez que restauramos utilizamos la opción NORECOVERY hasta importar el último fichero. También es bueno saber que el parámetro WITH FILE=X indica que "fichero" se importa. En el caso de que estuvieramos añadiendo al primer fichero la copia diferencial en lugar de crear un nuevo fichero, en el momento de restaurar el segundo fichero deberíamos poner WITH FILE=2. Por último, WITH FORMAT elimina el contenido en lugar de añadir.

Ejemplo 2. BACKUP Y RECUPERACIÓN SIMPLE EN CASO DE PERDIDA DE UNA TABLA

Este es el típico caso en el que en una base de datos conviven distintas aplicaciones y hemos perdido alguna tabla o se han hecho modificaciones que requieren recuperar datos. En este caso es posible que por ello no podamos restaurar la base de datos actual con la copia. A continuación muestro como recuperar la base de datos en una nuevo con otro fichero y otro nombre para poder traspasar o comprobar datos:

Partimos de un caso como el anterior donde se tiene una copia full y diferenciales. Suponemos que sabemos cuando se ha producido el error.

USE master;
--Modo de recuperació simple para la base de datos en cuestion
ALTER DATABASE AdventureWorksDW2008 SET RECOVERY SIMPLE;
GO
--Backup simple
BACKUP DATABASE AdventureWorksDW2008 TO DISK='F:\SQL08\BACKUPDATA\AWDW2008.bak'
WITH FORMAT;
GO
--Creación de tabla
CREATE TABLE AdventureWorksDW2008.dbo.Prueba
(   
    F1    char(2000),
    F2    char(2000)
)

--Backup diferencial
BACKUP DATABASE AdventureWorksDW2008 TO DISK='F:\SQL08\BACKUPDATA\AWDW2008_D1.bak'
WITH DIFFERENTIAL;

--Drop de la tabla
DROP TABLE AdventureWorksDW2008.dbo.Prueba;

--Segundo backup diferencial
BACKUP DATABASE AdventureWorksDW2008 TO DISK='F:\SQL08\BACKUPDATA\AWDW2008_D2.bak'
WITH DIFFERENTIAL;
GO

--Restauración del último backup completo en otra base de datos
RESTORE DATABASE AdventureWorks2008DWTemp FROM DISK='F:\SQL08\BACKUPDATA\AWDW2008.bak'
WITH FILE=1,
MOVE N'AdventureWorksDW2008_Data' TO 'F:\SQL08\DATA\MSSQL10.TEST\MSSQL\DATA\AdventureWorksDW2008Temp_Data.mdf',
MOVE N'AdventureWorksDW2008_Log' TO 'F:\SQL08\DATA\MSSQL10.TEST\MSSQL\DATA\AdventureWorksDW2008Temp_Log.mdf',
NORECOVERY;
--Restauración de los diferenciales
RESTORE DATABASE AdventureWorks2008DWTemp FROM DISK='F:\SQL08\BACKUPDATA\AWDW2008_D1.bak'
WITH FILE=1,
RECOVERY;
 

Este segundo caso sería más sencillo en un entorno donde se incluyera copia de los ficheros de transacciones y pudieramos hacer una restauración point-in-time.

 

El código que aquí encontramos perfectamente se puede partir y podemos programar ya las copias (full y/o diferenciales) en jobs del agente de sql server. Por ejemplo podríamos hacer la copia full el domingo y de lunes a sabado seguir con los diferenciales, pero esto también se puede programar usando SQL Server Management Studio y hacerlo de forma visual (aunque saber hacerlo desde código no está de más)...

Para aquellos administradores de bases de datos que deban tratar con un tal Dynamics Ax 2009 y sus secuaces (desarrolladores, consultores, etc ) dejo aquí un par de cosillas que se deben saber(o te deben decir) cuando unimos ax2009 y sql server 2008. A veces se puede apuntar a la base de datos como fuente del problema pero no siempre es así.  Algunos requerimientos a tener en cuenta para la instalación de Ax2009 son que el usuario con el que quieran acceder para hacer la instalación debe ser usuario de DOMINIO y en sql server debe ser miembro de rol dbcreator y securityadmin para poder crear la nueva base  de datos desde el instalador de Ax. Una vez instalado (o durante el proceso de instalación) los problema con la base de datos que nos podemos encontrar pueden ser:

Caso 1:
Otro problema conocido en la sincronización de datos puede producirse por la falta de permisos. El mensaje dice algo así:

"Cannot execute a data definition language command on  ().
The SQL database has issued an error.
Problems during SQL data dictionary synchronization.
The operation failed.
Synchronize failed on 1 table(s)"

Este caso en concreto se soluciona dando permisos db_ddladmin sobre la base de datos en cuestión. Segun el documento oficial de instalación de Dynamics Ax 2009 el usuario de AOS debe tener los roles db_ddladmin, db_datareader, y db_datawriter  sobre su base de datos activados para que todo funcione correctamente.

Caso 2:
Puede ocurrir que en Ax2009 al añadir un campo sobre una tabla puede no reflejarse en la base de datos pero sí en el AOT
de Axapta. Si es algo que ocurre solo con ese campo, o más bien con ese tipo de campo ( Extended Data Type ) la base de datos no tiene nada que ver. El problema seguramente es que la funcionalidad de la que cuelga este tipo de campo esta deshabilitada. Esto suele pasar en una nueva instalación donde no se ha activado nada (Gracias Alejandro por la ayuda!! ).

En otro post espero comentar cuales son los pasos que sigue Ax cuando sincroniza una tabla.

Nos ponemos en situación
En nuestro entorno es posible que necesitemos disponer de dos replicas de una/s base de datos en entornos diferenciados (el clásico ejemplo sería producción y test). Para decidir como lo hacemos las preguntas más comunes que nos debemos hacer son:

-¿Este nuevo entorno será temporal? ¿Contiene bases de datos grande en cuanto a volumen y/o la carga que debe soportar es elevada(aunque sea test)?
-¿Dispongo de la versión de desarrollo de SqlServer2008? Que solo está a tu alcance si tienes una suscripción Msdn...
-¿Dispongo de un servidor adicional?

En base a estas preguntas y todas las que se le puedan a uno ocurrir se puede optar por diferentes soluciones:
-Lo más sencillo y si la base de datos más la carga a soportar son pequeñas podemos usar el mismo servidor para todas las bases de datos (creamos en el mismo servidor con nombres distintos (_Test) y Santas pascuas...). Para que no se molesten entre si podemos usar Resource Governor.
-Lo más "aseptico" si los recursos lo permiten y siempre que merezca la pena sería montarlo en servidores distintos (si disponemos de la versión de desarrollo)
-Otra opción es una mezcla de las anteriores. Montar los dos entornos en un mismo servidor pero en instancias diferentes.
-Etc...

1 Servidor de n cpu (n > 1) + 2 instancias = 2 entornos
Una opción que me gusta de las anteriores es la tercera, donde montamos dos instancias para separar los dos entornos y configuramos la afinidad de procesadores para controlar la dedicación de cada procesado a cada instancia. También debemos controlar la memoria que asignamos a cada instancia(server memory y max server memory).

Ejemplo:
En un servidor de 6 núcleos dedicamos 2 de los cuatro procesadores a dar servicio al entorno de test mientras los 6 restantes se los adjudica el entorno de producción. Para hacerlo   solo debemos abrir el SSMS y en las Propiedades del servidor:XXXX en la parte de Procesadores habilitar cada procesador manualmente (desmarcando la adjudicación automática). Ver imagen.

Es bueno saber también que podemos asignar y desasignar a conveniencia ya que se puede variar de forma dinámica para cada instancia. En caso de necesidad es bueno modificar la capacidad de aguante si se nos viene encima. Pero no es oro todo lo que reluce y debemos saber que al estar gestionando dos instancias ya estamos consumiendo más que si gestionáramos solo una.

Conceptos: affinity mask, affinity io mask

Tras la instalación de un servidor SQL Server en una máquina con Windows Server, el siguiente paso lógico es configurarlo para permitir conexiones remotas a la base de datos desde otros equipos.

Para ello hay que utilizar primero el Administrador de configuración de SQL Server para habilitar el protocolo TCP/IP sobre la dirección IP del server, y después abrir los puertos necesarios (el 1433 por defecto), desde el Firewall de Windows.

Estos son los pasos básicos que yo suelo seguir, probando a conectar desde una máquina cliente como mi PC o portátil al servidor.

Comprobar que hay conexión entre el cliente y el servidor (ip 192.168.1.116)

Nunca está de más comprobar con un simple ping que hay conectividad entre las dos máquinas abriendo una consola de comandos (cmd) desde el cliente:

C:\Users\Administrador>ping 192.168.1.116

Pinging 192.168.1.116 with 32 bytes of data:
Reply from 192.168.1.116: bytes=32 time<1ms TTL=128
Reply from 192.168.1.116: bytes=32 time<1ms TTL=128
Reply from 192.168.1.116: bytes=32 time<1ms TTL=128
Reply from 192.168.1.116: bytes=32 time<1ms TTL=128

(Pruebo todo directamente con la IP para descartar posibles problemas de resolución de nombres de servidor)

Comprobar con telnet que no esté abierto ya el puerto

Telnet se utiliza también desde la linea de comandos. Si tienes windows 10 y no lo tienes activado consulta aqui cómo habilitarlo.

En este ejemplo utilizamos el puerto por defecto de SQL Server 1433, aunque para más seguridad es recomendable utilizar puertos diferentes, pero ese es otro tema..

C:\Users\Administrador>telnet 192.168.1.116 1433
Connecting To 192.168.1.116...Could not open connection to the host, on port 1433: Connect failed

Habilitar el protocolo TCP/IP sobre la dirección IP del server

Abrir en el servidor el programa Administrador de configuración de SQL Server (Sql Server Configuration Manager), desde el menú de aplicaciones de SQL Server, o buscando el programa en el buscador del server.

Una vez abierto, en el menú de la izquierda, en 'Configuración de red de SQL Server', Seleccionar 'Protocolos de [Nombre de la instancia]', y hacer doble click sobre el Nombre de protocolo TCP/IP para acceder a sus propiedades y seleccionar 'Si' en 'Habilitado', y en la pestaña de Direcciones IP, seleccionar también 'Si' en 'Habilitado' de la IP del server, este caso 192.168.1.116, y asegurarse de que el puerto es correcto (en este caso 1433 ya estaba bien porque es el puerto por defecto, pero si no hay que modificarlo)

Después de estas modificaciones hay que reiniciar el servicio principal de SQL Server desde la misma aplicación, en el menú 'Servicios de SQL Server'

Abrir el puerto TCP desde el Firewall de Windows Server

El siguiente paso es abrir la aplicación Firewall de Windows, desde el panel de control (Panel de control\Sistema y seguridad\Firewall de Windows), o buscando Firewall en el buscador de aplicaciones.

Una vez abierto, seleccionar 'Configuración avanzada' en el menú de la izquierda, y después, en el menú hacer click con el botón derecho sobre 'Reglas de entrada', y seleccionar 'Nueva Regla' (También se puede hacer desde la opción de menú 'Acción')

Seleccionar regla tipo 'Puerto', y después 'TCP', y escribir 1433 en la opción 'Puerto específico local'. Esta es la configuración más sencilla, y sirve si esta es la única instancia de SQL Server instalada en el Servidor Windows.

Si hubiera más instancias sería necesario abrir más puertos, activar el servicio SQL Server Browser, y abrir otro puerto UDP, y también crear una nueva regla personalizada en el firewall para el servicio.

Seleccionando las siguientes opciones (normalmente las que vienen por defecto ya van bien) se finaliza la creación de la regla del firewall, y el telnet por el puerto 1433 desde la máquina cliente ya debería responder:

C:\Users\Administrador>telnet 192.168.1.116 1433

 

Una vez que el telnet responde, ya se podrá conectar con SQL Server Management Studio u otra aplicación cliente, al menos por IP ;)

 

 

 

Igual que en servidores Linux donde tenemos bases de datos MySql y Oracle automatizamos tareas banales mediante scripts en bash, en los servidores donde tengamos Windows Server podemos apañarnos con Windows Power Shell. Como administradores de las bases de datos Sql server, puede interesar saber algo de scripting en este lenguaje para llegar más lejos que con el Agente de Sql Server y sus trabajos. También es cierto que podemos profundizar todo lo que queramos, empezando con Server Management Objects para interactuar con SQL Server (requiere conocimientos mínimos de .NET y POO) e incluso pudiendo combinarlo con WMI para consultar información relativa al sistema operativo. 

En este ejemplo de script Power Shell (extensión .ps1)  voy a hacer algo sencillo que será refrescar la base de datos del entorno de desarrollo desde una copia que haremos de la base de datos de producción. En mi caso, esto me tiene que permitir elegir si refrescar el entorno de desarrollo o el entorno de test. Lo bueno de PowerShell es que podemos incluir dentro del mismo script .ps1 comandos de la propia linea de comandos de dos.

Antes de empezar abriremos la consola de power shell. Para probar y poder ejecutar scripts desde la consola scripts seguramente hemos de permitirlo ya que por defecto la directiva deshabilita esta opción. Consultamos y modificamos el la restricción con los comandos Get-ExecutionPolicy y Set-ExecutionPolicy:

 

Este script está pensado para ejecutarlo desde el servidor de desarrollo. Para hacerlo más "universal", haré primero una consulta para saber los nombres de la bases de datos accesibles de producción, pidiendo previamente servidor al que conectarse, usuario y clave. Después permito introducir el nombre de la elegida.

*** $SQLServerOrigen = read-host "Nombre del servidor origen" $SQLUserOrigen = read-host "Usuario bbdd origen" $SQLClaveUserOrigen = read-host "Clave" $SqlQuery = "select name from master.dbo.sysdatabases order by 1 asc" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $SQLServerOrigen; User id=$SQLUserOrigen; Password=$SQLClaveUserOrigen" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() clear $DataSet.Tables[0] echo "" $BBddOrigen = read-host "Base de datos de $SQLServerOrigen a copiar" ***

Para hacer la copia, en esta primera versión uso el cliente sql de la linea de comandos (sqlcmd) y un sql a pelo montado de forma dinámica para hacer el backup de solo copia en una ruta fijada. Montaré una unidad de red temporal para hacer la copia, se copia, se desmonta la unidad y se lanza de nuevo desde sqlcmd el restore (mapeando y hardcodeando la nueva ruta por la distinta ubicación de los .mdf y .ldf). Finalmente elimino el fichero.

*** sqlcmd -S $SQLServerOrigen -U $SQLUserOrigen -P $SQLClaveUserOrigen -Q "BACKUP DATABASE [$BBddOrigen] TO DISK = N'F:\Backup\$BBddOrigen\$BBddOrigen.temp.bak' WITH COPY_ONLY" echo "\\$SQLServerOrigen\F$\Backup\$BBddOrigen\" Net Use T: \\$SQLServerOrigen\F$\Backup\$BBddOrigen Copy-Item T:\$BBddOrigen.temp.bak C:\$BBddOrigen.temp.bak Remove-Item T:\$BBddOrigen.temp.bak Net Use T: /delete $BBddDestino = read-host "Base de datos destino" SQLCMD -E -S localhost -Q "RESTORE DATABASE $BBddDestino FROM DISK='C:\$BBddOrigen.temp.bak' WITH FILE=1, MOVE N'$BBddOrigen' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\$BBddDestino.mdf', MOVE N'${BBddOrigen}_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\$BBddDestino.ldf' " #En la cadena de texto anterior, BBDDOrigen va entre {} para poder escapar el '_' acompañado del nombre de la variable y no interprete el _log como parte del nombre de la misma Remove-Item C:\$BBddOrigen.temp.bak ***

En el script anterior, en lugar de usar sqlcmd para hacer el backup, podríamos haberlo hecho usando SMO (Server Management Objects). En este caso, el equivalente al sqlcmd sería algo parecido a lo siguiente:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") #Preparamos el servidor y cargamos tb el directorio de backup $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "($SQLServerOrigen)" $backupDirectory = $server.Settings.BackupDirectory $db = $server.Databases[$dbToBackup] $dbName = $db.Name $timestamp = Get-Date -format yyyyMMddHHmmss $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup") $smoBackup.Action = "Database" $smoBackup.BackupSetDescription = "Full Backup of " + $dbName $smoBackup.BackupSetName = $dbName + " Backup" $smoBackup.Database = $dbName $smoBackup.MediaDescription = "Disk" $smoBackup.Devices.AddDevice($backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak", "File") $smoBackup.SqlBackup($server) #Obtenenmos la lista de ficheros en el directorio de backup para luego filtrar y mostrar los .bak $directory = Get-ChildItem $backupDirectory $backupFilesList = $directory | where {$_.extension -eq ".bak"} $backupFilesList | Format-Table Name, LastWriteTime

La gracia de usar los SMO es la complejidad. Seguramente segun el objeto podremos profundizar algo más y modificar parámetros y obtener información que no conseguiriamos de otra manera. Este trozo de código de arriba, a diferencia del sqlcmd está usando la ruta definida por defecto para los backups del propio sqlserver en lugar de hardcodearla. Ciertamete no es un avance pero ya vemos que la interacción es mejor. Si seguimos con SMO, el equivalente al restore con sqlcmd donde se permite elegir el nombre de la base de datos destino y se renombran los ficheros. Sería algo parecido a lo siguiente:

#Librerias... [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null $backupFile = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\test.bak' $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)" $backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File") $smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore") #Propiedades de la restauracion $smoRestore.NoRecovery = $false; $smoRestore.ReplaceDatabase = $true; $smoRestore.Action = "Database" $smoRestorePercentCompleteNotification = 10; $smoRestore.Devices.Add($backupDevice) #Nombre de la nueva bbdd $BBddDestino = read-host "Base de datos destino" $smoRestore.Database =$BBddDestino #Nuevos ficheros... $smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile") $smoRestoreLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile") $smoRestoreFile.LogicalFileName = $BBddDestino $smoRestoreFile.PhysicalFileName = $server.Information.MasterDBPath + "\" + $BBddDestino + "_Data.mdf" $smoRestoreLog.LogicalFileName = $BBddDestino + "_Log" $smoRestoreLog.PhysicalFileName = $server.Information.MasterDBLogPath + "\" + $BBddDestino + "_Log.ldf" $smoRestore.RelocateFiles.Add($smoRestoreFile) $smoRestore.RelocateFiles.Add($smoRestoreLog) #Restauracion $smoRestore.SqlRestore($server)

 

 

En conclusión...

...invirtiendo un poco de tiempo la ganancia futura puede ser mayor. Podemos ahorrar mucho tiempo en tareas repetitivas o hacer verdaderas obras de arte que permitan hacer cosas más viriles. Por ejemplo algo como desactivar el reflejo del mirror en una base de datos reflejada para tenerla preparada en pocos segundos. Recrear el mismo reflejo de forma automática (o a un solo intro). Podemos reinventar la rueda y monitorizar nosotros determinados aspectos de nuestro propio servidor y tener nuestra propia versión de los hechos (sin contar la del departamento de sistemas...).

 

 

El recopilador de datos es una parte de MS Sql Server que permite recopilar diferentes tipos de datos y métricas sobre el rendimiento y desempeño de la base de datos. Se incluye desde la versión 2008 de SqlServer hasta la 2014 y esta formado por un conjunto de trabajos que se ejecutan mediante el agente de Sql Server periódicamente o de forma continua. Su configuración esta formada por dos sencillos pasos mediante asistente. Una vez finalizados ya podemos explotar su información mediante los informes incluidos.

Configurar el almacén de administración de datos

Para configurar el Almacén de Administración de Datos nos conectamos a SQL Server mediante Management Studio con un usuario administrador. En el árbol del Explorador de Objetos, iremos a Administración>Recopilación de datos y seleccionamos Configurar almacén de administración de datos. Esto iniciará un asistente del tipo Siguiente>Siguiente...

 

En el primer paso elegiremos la primera opción. Ésta configura la base de datos "almacén" donde se guardan los datos recogidos por los recopiladores (que configuramos en un paso posterior, seleccionando la segunda opción de este mismo punto del asistente). 

 

Siguiendo con la primera opción (la de configurar el almacén), nos encontraremos primero con la elección del nombre para la nueva base de datos y el servidor donde estará. El servidor será el mismo servidor y no podemos cambiarlo, pero para la base datos podemos elegir una ya existente o podemos crear una nueva pulsando Nuevo. Elegir una existente podría tener sentido si ya tenemos una ya creada para este propósito o con temas relacionados de administración. Si creamos una nueva base de datos es interesante desactivar de por sí el seguimiento y dejarlo como simple. Hemos de pensar que dependiendo de la cantidad de datos a recopilar o la actividad sobre el servidor puede suponer más una molestia en el consumo de recursos que otra cosa.

El el siguiente paso debemos elegir que inicios de sesión pertenecen a cada rol propio de este compenente. Lo normal puede ser que usemos uno de sistema, el mismo del agente o directamente creemos uno nuevo con los permisos necesarios si nos interesa aislar la carga producida de forma rápida. El nuevo usuario lo creamos desde el botón del panel inferior. A continuación le asignamos los permisos sobre la base de datos.

 

Pulsamos siguiente y entonces nos aparecerá el resumen de la configuración que hemos hecho a modo de confirmación. Pulsamos de nuevo siguiente si estamos conformes y esperamos un resultado como este donde todo es correcto. 

 

Configurar recopilación de datos

Ahora que ya tenemos la base de datos, nos falta configurar los recopiladores. Como he comentado antes, configuramos el recopilador de datos desde el mismo punto del explorador de objetos (Administración>Recopilador de datos> Configurar el almacén de administración de datos) pero esta vez seleccionamos en el segundo paso del asistente la opción inferior: Configurar recopilación de datos.

Una vez empezado el asistente, y ya en el tercer paso elegimos donde está hospedado el almacén de datos (la base de datos que hemos creado antes). Pulsamos de nuevo siguiente y nos aparece de nuevo el resumen de configuración y de los pasos a realizar. Estos incluyen habilitar la recopilación de datos y su inicio. Con todos estos pasos ya tenemos todo configurado y en marcha. 

Recopiladores incluidos e informes incluidos

Por defecto, mediante los dos pasos anteriores conseguimos la creación de los tres recopiladores:

• Actividad del servidor: Cpu, memoria, etc...
• Estadísticas de consultas: Consultas, esperas, etc...
• Uso de disco: Evolución del espacio ocupado por cada bases de datos.

La parte más interesante son también los informes que se incluyen. Para poder acceder a ellos podemos hacerlo desde SQL Server Management Studio navegando por el árbol del Explorador de objetos, botón derecho en la base de datos almacén y seleccionamos informes del Almacén de Administración. Se abrirá una nueva pestaña y en el panel principal ya seleccionamos lo que queremos ver y el intervalo de fechas. Podemos consultar:

• Resumen de uso de disco: Este informe muestra por cada base de datos el espacio que ocupan sus ficheros de datos y registro y la tendencia. Útil para ver comportamientos anómalos o hacer estimaciones sobre la necesidad de espacio en disco.

• Historial de estadísticas de consultas: Del intervalo seleccionado muestra las consultas ordenadas de mayor a menor impacto por CPU, Duración etc... Lo vemos en un gráfico y en una tabla donde se muestra también el número de ejecuciones, duración, lecturas, escrituras.

• Historial de actividad del servidor: Vista general del servidor donde podemos ver el uso de cpu, memoria, disco y red en la parte superior. en la parte media podemos ver otro gráfico con los tipos de esperas de SQL Server. En la parte inferior vemos un resumen de actividad con las conexiones, batch request etc. De estos dos últimos podemos ver más detalle haciendo clic sobre ellos.

De estos tres informes, del que podemos sacar mas jugo es el tercero porque nos da un estado general de la base de datos en el tiempo y nos permite indagar de manera superficial que se estaba ejecutando y durante cuanto tiempo. Por ejemplo, podemos detectar picos de actividad consultando el tercer gráfico. Observando el segundo podemos ver cual puede ser el origen de la lentitud según el mayor tipo de espera que veamos en el gráfico.

 

En resumen...

... este componente puede resultar útil por falta de alternativas dentro de la misma instalación de SqlServer y complementa los cuatro informes iniciales que pueden venir por defecto. Además, supone una alternativa auto-configurada para recopilar los mismos contadores que recoge el monitor de rendimiento del sistema. Es muy interesante la posibilidad de tener una foto del estado del servidor en un intervalo de tiempo anterior y verlo de forma gráfica.

 

Además, si queremos ir más allá podemos crear nuestros propios informes para explotar esta base de datos. Lo podríamos hacer con Report Builder o Business Intelligence Developer Studio y el punto de partida pueden ser las tablas snapshots.performance_counter_instances y snapshots.performance.counter_values. Ahí podemos ver los valores recogidos que podemos ver en cualquier momento consultando el monitor de rendimiento del sistema.  

 

… para empezar a probar las nuevas características que tenemos desde la versión 2008 de SqlServer.  

Desde entonces ha llovido mucho, de hecho, la release date fue el 6 de agosto de 2008, y particularmente me interesa empezar a probar nuevas implementaciones ya sean novedad en la versión SQL Server 2014 o la anterior, la de 2012. Por ahora empiezo con esta serie de post sobre la nueva, primero con lo más básico que es como instalarlo para empezar a probar. Después en otros posts, comentaré seguramente pruebas de rendimiento con la nueva posibilidad de trabajar con tablas en memoria y cosas más bonitas o tangibles como el uso de Power View (ahora también contra ssas). 

Este paso a paso resumido está hecho con la segunda versión preliminar de SQL Server 2014. Si queréis algo más completo tenéis algo más completo en la MSDN, pero quizás es más tedioso. Otras cosas podrán cambiar en la versión definitiva, pero el modo de instalación no lo creo. Así que vamos al lío. En mi caso la instalaré en una máquina virtual con Windows Server 2008 Enterprise, y por aquí abajo os comento cuatro sobre la marcha como instalarlo:

• Una vez cargada la ISO/CD lanzamos el instalador. Nosotros queremos una nueva instancia de bbdd. Nos vamos al apartado de Instalación y agregamos una nueva instancia (captura inferior). Pulsamos Siguiente y nos pide la clave, que introducimos si toca y pulsamos siguiente de nuevo. 

• Seguimos con la licencia (que aceptamos, claro) y después seguimos con los validación de requisitos. Seguramente si probáis sobre una máquina virtual os falle la parte de “Actualización de .NET 2.0 y .NET 3,5 Service Pack 1 para...”. No pasa nada. Miramos el detalle del error y nos bajamos el paquete que nos especifica para nuestro sistema operativo. Más adelante también se comprueba si tentemos habilitado Windows PowerShell 2.0 y el NetFramework 3.5 Sp1. Lo instalamos todo y volvemos a empezar.

• Cuando lleguemos al mismo punto, se pone a buscar actualizaciones, lo dejamos si hace falta y pasamos al siguiente paso que es otra validación, en este caso de reglas (firewall etc..). Si está habilitado nos advertirá (captura inferior). Posteriormente podemos añadir los puertos de sql o deshabilitarlo si lo creyéramos preciso.

• Seguimos con el Rol de la instalación donde seleccionamos Instalación de características de SQL Server. Siguiente y ya podemos elegir lo que vamos a instalar (en mi caso para probar las tablas en memoria y Power View es lo que veis en pantalla: motores de base de datos y analisys services, conectividad de cliente y herramientas de administración).

• A continuación podemos configurar el nombre de la instancia (dejamos el valor por defecto si es la primera en este servidor). Después elegimos las cuentas de ejecución para cada servicio. Podemos dejar los cuentas por defecto o alguna otra cuenta, pero la gracia aquí es aplicar aquello de que la cuenta tenga los permisos justos y necesarios. Pulsamos siguiente.

• En la configuración de cada servicio (en mi caso el motor y analisys services) debemos recordar añadir algun usuario al grupo de administradores. Además debemos elegir si toca, el tipo de autentificación para los usuarios.

• Pulsamos siguiente y ya tenemos el resumen final de lo que vamos a instalar. Siguiente y a esperar. Cuando acabe reiniciamos y listos!

Ya tenemos nuestro SQL Server 2014 a punto para empezar a probar...

 

 

Una de las tareas básicas en la administración de bases de datos es la monitorización de nuestro servidor y nuestra base de datos. Para servidores Windows con SQL Server una de las maneras más básicas (y gratuitas) es hacerlo mediante los contadores de rendimiento del sistema.

Estos contadores de rendimiento del sistema se añaden en el Monitor de rendimiento de Windows (perfmon) y con ellos podemos visualizar valores de métricas relativas incluso a aplicaciones como Dynamics Ax que añaden sus propios contadores al instalarse en el servidor. De los contadores que elijamos, también podemos almacenar sus valores a lo largo del tiempo con la creación de conjuntos de recopiladores de datos e incluso podemos enviarnos alertas uniendo recopiladores e informes predefinidos.

 

Para monitorizar nuestro SQL Server, los principales contadores de rendimiento podrían ser (en el servidor actual y en el idioma que lo tengáis):

• Disco físico
  Escrituras en disco/s
  Lecturas de disco/s
  Longitud actual de la cola de disco
  Longitud promedio de cola de escritura de disco

Los dos primeros sirven para conocer la métrica y los valores medios por unidad o en total. El valor de la longitud promedio de cola de escritura de disco siempre debe tender a 0 en cada unidad y tampoco debería pasar de 2.

• Procesador
  % de tiempo de procesador

¿Que decir? Es el uso de la cpu (de cada procesador o mejor el _Total para ver el promedio). Mejor evitar sobrepasar el 80%. Lo siguiente sería ver que es lo que está causando esa presión sobre el procesador.

• Memoria
  Mbytes disponibles

Memoria sin asignar por el sistema. Debería ser mayor que 0 porque deberíamos contar con algo sobrante para otros procesos puntuales que puedan lanzarse en el servidor u otros servicios fijos como Analisys Services o Reporting Services.

• SQLServer: Acces Methods
  Full Scans/sec
  Index Searches/sec

Tipo de acceso. Un valor alto de Full scans a lo largo del tiempo indica la falta de indices. Si empezáramos a indizar en consecuencia deberíamos ver como compensamos con un incremento de Index Searches.

• SQLServer:Buffer Manager
  Buffer cache hit ratio
  Page life expectancy

El primer contador es el porcentaje de veces que el motor usa la caché frente al disco. Debe tender al 100%. Page Life expectancy es el tiempo en segundos que permanece una página en memoria sin tener ninguna referencia que la retenga allí. Cuanto más tiempo, mejor. Un valor bajo puede significar problemas de cache o incluso falta de memoria.

• SQLServer:General Statistics
  Processes blocked
  User connections

El primero indica los procesos bloqueados y el segundo el número de conexiones actual. Es bueno saberlos en todo momento para detectar anomalías e incluso alertarnos cuando los procesos bloqueados son mayores que 0.

• SQLServer:Memory Manager
  Target Server Memory (KB)
  Total server memory (KB)

Sirve para ver la asignación de memoria del sistema a Sqlserver por el sistema y el valor configurado dentro de Sqlserver. Un valor real menor al configurado indicaría falta de memoria.

• SQLServer:SQL Statistics
  Batch Requests/sec
  SQL Compilations/sec
  SQL Re-Compilations/sec

Peticiones por segundo. Sirve también para detectar puntas de trabajo o procesos inusuales. El segundo y tercero permiten ver problemas de cache.

• SQLServer:Wait Statistics
  Lock Waits
  Log buffer waits
  Log write waits
  Memory grant queue waits
  Network IO Waits
  Page IO latch waits
  Page latch waits

Eso son las esperas para las consultas. Con estos valores es fácil apuntar a una fuente de problemas para focalizar nuestra atención: problemas de red, de acceso a disco, de memoria. Existen otros tipos, pero sea cual sea debemos analizar en conjunto para cada contador las esperas en curso, las iniciadas por segundo y el tiempo medio de espera. Valores altos de forma continua deben llamar nuestra atención.

Además de todos los anteriores, podemos necesitar controlar otros más específicos a alguna característica o función de sql server. Por ejemplo, podemos monitorizar el tiempo de trasvase en un espejo asíncrono de cada transacción a la replica mirando el valor del contador SQLServer:Database Mirroring>Transaction Delay. Todos los contadores relativos al motor de SQL Server empiezan por SQLSERVER, los de Analisys Services empiezan por MSAS y los de Reporting Services con MSRS.

Para empezar a añadir nuestros propios contadores, abrimos el perfmon.exe, vamos al Monitor de rendimiento y seleccionamos la visualización tipo informe para trabajar más fácilmente.

A continuación, pulsamos el botón derecho del ratón sobre el área vacía del informe de color blanco y seleccionamos Agregar contadores. Ahí podemos buscar cualquiera de los anteriores para añadirlo o seleccionar el que queramos visualizar. La gracia también es que una vez seleccionados los que nos interesan podemos guardar la configuración (por ejemplo en el escritorio) para poder acceder en cualquier momento al Monitor de Rendimiento con nuestros contadores ya en la pantalla o poder elegir entre distintas configuraciones (una simple, una avanzada, etc etc).

Todos estos valores que hemos visto pueden irse recopilando a lo largo del día e incluso podemos definir valores "umbral" a partir de los cuales podemos realizar alguna acción e incluso enviarnos un correo electrónico para avisarnos del suceso. Esta es una forma de complementar o sustituir una motorización con herramientas tipo Nagios.

 

En resumen...

... es básico conocer como funciona el tema de los contadores de rendimiento, configurarlos y lo que podemos llegar a hacer con ellos. Además, casi igual de importante es conocer los valores de referencia estándares y con sus máximos y mínimos aceptados, como conocer los reales de funcionamiento de nuestro entorno. Creo que debemos saber y hasta donde tolerar, sin caer en la dejadez ni olvidarse, valores de ciertos contadores y separar lo que es normal y lo que no. Sabemos que siempre hay grandes o pequeñas aplicaciones que pueden hacer un mal uso o tener algún proceso mal planteado que a la práctica es imposible de modificar y tenemos que vivir con él.

También se debe saber que existen otras maneras de obtener esta información y tratarla. Desde el propio sql server podemos consultar la vista sys.dm_os_performance_counters que nos ofrece los valores actuales. Podemos filtrar por su columna object_name para elegir los mismos contadores por su nombre.

SELECT *
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'SQLServer:Buffer Manager'

En la versión 2014 de Sql Server introducirán un nuevo motor en la base de datos que permitirá trabajar con tablas en memoria (inmemory o hekaton, su nombre en clave). Podemos imaginar que eso puede suponer una mejora considerable en el rendimiento si sabemos elegir para este nuevo las tablas adecuadas. Su funcionamiento es lógico y es el que cabe esperar. Según la MSDN, las tablas y sus registros se mantienen principalmente en memoria y una segunda copia se mantiene en disco para disponer de los datos si reiniciásemos la instancia. Tenemos también la posibilidad de elegir el tiempo que tarda en trasladarse un cambio en los datos de la tabla en memoria a la tabla en disco. También existe la posibilidad de crear tablas on-the-fly (non-durable table) y que no se persistan en disco. En cualquier caso hemos de tener en cuenta que en caso de desastre o reinicio de servidor, cualquier dato no trasladado a disco seguro que lo perdemos. Vamos a hacer unas pruebas.

Primero de todo, para poder usar tablas en memoria tenemos crear un grupo de ficheros del tipo adecuado. En este caso, anticipándome al futuro, combinamos uno de cada tipo (el normal por que sí y luego añadimos uno para datos en memoria después):

CREATE DATABASE [InMemoryTest]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'InMemoryTest', FILENAME = 
  N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemoryTest.mdf' ,
  SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'InMemoryTest_log', FILENAME = 
  N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemoryTest_log.ldf' , 
  SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO


ALTER DATABASE InMemoryTest ADD FILEGROUP InMemoryTest_im CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE InMemoryTest ADD FILE (name='InMemoryTest_im1', 
  filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemoryTest_im1') 
  TO FILEGROUP InMemoryTest_im

GO

--*** Si os aparece un error de que el procesador no es compatible usando VBox mirar al pie de página

A continuación vamos con nuestras tablas de ejemplo por cada tipo (la de toda la vida, DURABILITY = SCHEMA_ONLY y DURABILITY = SCHEMA_AND_DATA ):

 

-- Creación de la tabla normal (en el fg por defecto)
use InMemoryTest

CREATE TABLE Ventas (
IdVenta int identity PRIMARY KEY NOT NULL
,IdCliente int NOT NULL
,Total int NOT NULL
,FechaVenta date NOT NULL
,TipoVenta char(1) NOT NULL
,INDEX Ventas_FechaVenta NONCLUSTERED (FechaVenta)
)

-- Creación de la tabla en memoria,
solo con estructura en disco
use InMemoryTest

CREATE TABLE InMemoryVentas (
IdVenta int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000)
,IdCliente int NOT NULL
,Total int NOT NULL
,FechaVenta date NOT NULL
,TipoVenta char(1) NOT NULL
,INDEX InMemoryVentas_FechaVenta
NONCLUSTERED HASH (FechaVenta) WITH (BUCKET_COUNT = 365)
) WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_ONLY)

-- Creación de la tabla en memoria con
datos y estructura en disco (DURABLE)
use InMemoryTest

CREATE TABLE InMemoryDurableVentas (
IdVenta int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000)
,IdCliente int NOT NULL
,Total int NOT NULL
,FechaVenta date NOT NULL
,TipoVenta char(1) NOT NULL
,INDEX InMemoryVentas_FechaVenta
NONCLUSTERED HASH (FechaVenta) WITH (BUCKET_COUNT = 365)
) WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA)

Seguiremos con la carga de datos. Añado el mensage de output para ver cuanto tarda en cada tabla. Cuidado porque en este caso, la segunda tabla no se persiste en disco (DURABILITY = SCHEMA_ONLY) Si reiniciamos la instancia perderemos los datos. La tercera sí la persistimos en disco pero trabajamos con la copia en memoria.

-- Carga de datos para las tres tablas
SET NOCOUNT ON;
USE InMemoryTest

DECLARE @vCont int = 0;
PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertando 1000000 en Ventas')

WHILE @vCont < 1000000
BEGIN
SET @vCont += 1;
INSERT INTO Ventas
VALUES (@vCont%11, 9999, DATEADD(DD,-@vCont%365,GETDATE()), @vCont%2)
END
PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertados 1000000 en Ventas')

SET @vCont = 0;
PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertando 1000000 en InMemoryDurableVentas')

WHILE @vCont < 1000000
BEGIN
SET @vCont += 1;
INSERT INTO InMemoryDurableVentas
VALUES (@vCont, @vCont%11, 9999, DATEADD(DD,-@vCont%365,GETDATE()), @vCont%2)
END

PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertados 1000000 en InMemoryDurableVentas')
SET @vCont = 0;

PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertando 1000000 en InMemoryVentas')
WHILE @vCont < 1000000
BEGIN
SET @vCont += 1;
INSERT INTO InMemoryVentas
VALUES (@vCont, @vCont%11, 9999, DATEADD(DD,-@vCont%365,GETDATE()), @vCont%2)
END

PRINT( CONVERT(varchar, GETDATE(), 114) + N' > Insertados 1000000 en InMemoryVentas')

En mi caso, la ejecución con de este último script de tabla me muestra que tarda cerca de 14 minutos en cargar la tabla en disco, cerca de 12 minutos la tabla en memoria persistida en disco al completo y apenas 1 solo minuto para la tabla de la que solo tenemos la estructura en disco (pero de la que al reiniciar la instancia perderemos sus datos). Esta prueba ya empieza a mostrar diferencias y sobretodo ventajas para trabajar con tablas en memoria...

 

Ahora vamos a probar y medir algunas consultas que haremos contra cada tipo de tabla. Para ello las ejecutamos previa limpieza de cache y buffers:

USE InMemoryTest;
GO

DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

PRINT(N'Sobre Ventas...')
SELECT DISTINCT IdCliente
FROM Ventas
WHERE FechaVenta = '01/01/2013';

PRINT(N'Sobre InMemoryDurableVentas...')
SELECT DISTINCT IdCliente
FROM InMemoryDurableVentas
WHERE FechaVenta = '01/01/2013';

PRINT(N'Sobre InMemoryVentas...')
SELECT DISTINCT IdCliente
FROM InMemoryVentas
WHERE FechaVenta = '01/01/2013';

Mi salida (que adjunto abajo) es la siguiente. No parece muy descriptiva pero ya vemos que el tiempo transcurrido es prometedor ya que pasamos de casi 400ms, a 1 y 0 milisegundos. Prometedor. Podéis probar de desordenar lo selects para modificar el orden si no os lo creéis y veréis que el resultado es muy similar. En mi opinión esto promete.

Sobre Ventas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Ventas'. Recuento de exámenes 3, lecturas lógicas 6302, lecturas físicas 1,

lecturas anticipadas 6225, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 250 ms, tiempo transcurrido = 397 ms.

Sobre InMemoryDurableVentas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 2 ms.

Sobre InMemoryVentas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 1 ms.

 

Aunque, en el ejemplo hay trampa, porque si miramos los planes de ejecución veremos que en el caso de la tabla de toda la vida tenemos un index scan mientras que en el caso de las tablas en memoria tenemos index seek (scan vs seek, gana seek) sobre el HASH INDEX. Eso es porque de forma inherente los indices de tablas en memoria ya son punteros directos a los datos de fila parecidos a los covering indexes. Es más, nos propone crearlo.

CREATE NONCLUSTERED INDEX Ventas_FechaVentaIncIdCliente
ON [dbo].[Ventas] ([FechaVenta])
INCLUDE ([IdCliente])

Lo crearemos, volveremos a ejecutar la consulta y el resultado debe ser como el siguiente.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Ventas'. Recuento de exámenes 1, lecturas lógicas 15, lecturas físicas 0,

lecturas anticipadas 12, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 10 ms.

Como vemos sigue ganando la tabla en memoria... de momento. Ahora cambiamos el tipo de consulta a una sin igualdad, cambiaremos a una de rango.

USE InMemoryTest;
GO

DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

PRINT(N'Sobre Ventas...')
SELECT DISTINCT IdCliente
FROM Ventas
WHERE FechaVenta >= '01/01/2013'
AND FechaVenta < '01/02/2013';

PRINT(N'Sobre InMemoryDurableVentas...')
SELECT DISTINCT IdCliente
FROM InMemoryDurableVentas
WHERE FechaVenta >= '01/01/2013'
AND FechaVenta < '01/02/2013';

PRINT(N'Sobre InMemoryVentas...')
SELECT DISTINCT IdCliente
FROM InMemoryVentas
WHERE FechaVenta >= '01/01/2013'
AND FechaVenta < '01/02/2013';

El resultado es este:

Sobre Ventas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Ventas'. Recuento de exámenes 1, lecturas lógicas 362, lecturas físicas 0,

lecturas anticipadas 125, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 78 ms, tiempo transcurrido = 71 ms.

Sobre InMemoryDurableVentas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 266 ms, tiempo transcurrido = 255 ms.

Sobre InMemoryVentas...

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Workfile'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0,

lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 250 ms, tiempo transcurrido = 252 ms.

El resultado ya no mola tanto pero esto se soluciona añadiendole un nuevo indice de rango, más adecuado si esperamos hacerle consultas del tipo. Por desgracia la única manera de hacerlo es recrear la tabla.

 

Mis conclusiones...

...son que este tipo de tablas pueden ser muy útiles pero hay que saber cuando usarlas. Me vale que podamos mantener los dos tipos de grupos de fichero (normales y preparados para persistir en memoria). También creo que en rendimiento puro no hay mucha diferencia en las tablas que están en memoria, entre la que persiste en disco y de la que solo mantenemos la estructura. En todo caso, puede ser interesante investigar el tema de DELAYED_DURABILITY para posponer la propagación de los cambios a disco.

 

 

*** The model of the processor on the system does not support creating filegroups with MEMORY_OPTIMIZED_DATA. This error typically occurs with older processors.

Este error aparece al crear el tipo de filegroup con la clausula MEMORY_OPTIMIZED_DATA si estamos usando por lo menos la versión 4.1 de Oracle VirtualBox. Actualizando la versión podríamos tener suficiente para solventar el problema. Si no ejecutamos lo siguiente en el directorio de VirtualBox:

VBoxManage setextradata [nombre_maquina_virtual] VBoxInternal/CPUM/CMPXCHG16B 1

 

Microsoft Sql Server Data Quality Services (DQS) es una herramienta, cliente-servidor, que se introdujo en Sql Server 2012 y que permite permite velar por la integridad de los datos basada en unos datos previos, la base de datos de conocimiento, que usamos para validar otros datos posteriores. Esta herramienta permite incluso limpiar datos entrantes en paquetes de SSIS. Su propósito es conseguir datos de calidad, construyendo primero una fuente de conocimiento sobre la calidad objetivo de nuestros datos, creando dominios (valores de referencia para asignar validez o no) y reglas para definir actuaciones.

Generalmente los datos incorrectos son un problema que se genera por usuarios o clientes en el momento de su introducción. Otras veces, los problemas pueden venir al unir diferentes orígenes de datos en un mismo almacén de datos destino. Con herramientas como DQS podemos, de alguna manera, validar nuestros datos o incluso corregirlos para obtener finalmente datos válidos y de mayor valor empresarial. En teoría, una de las ventajas que dice Microsoft sobre DQS es que permite a usuarios de distintos niveles (usuario final o profesional IT) el crear, ejecutar y mantener las operaciones de calidad de datos...

Antes empezar necesitaremos tener instalado en nuestro servidor lo necesario para empezar a probar. Lanzamos el instalador de SqlServer y añadimos a nuestra instancia de SqlServer Data Quality Services, bajo el motor de la base de datos y en el caso de probar con una máquina virtual también el cliente de calidad de datos.

Una vez completada la instalación activamos la parte del Servidor. Lo encontramos en el menú Inicio > Programas > Microsoft SQL Server 2014 CTP>Data Quality Server Installer 

Además de los componentes de servidor, eso también copia una base de conocimento de ejemplo, DQS Data, que incluye algunos dominios predefinidos. Superado este paso ya podemos empezar con nuestro ejemplo. Para las pruebas tendremos dos tablas de empleados como esta que usaremos primero como base para crear las reglas para nuestra base de datos de conocimiento y una vez definidos dominios con sus reglas de validación, las usaremos para probar de corregir los datos y exportarlos a otra tabla de sqlserver.

 

--Script de carga para las tablas de ejemplo. CREATE TABLE [dbo].[EmpleadosA]( [Nombre] [varchar](20) NULL, [Genero] [varchar](10) NULL, [Edad] [tinyint] NULL ) ON [PRIMARY] INSERT INTO [dbo].[EmpleadosA] VALUES ('John','Male',18) INSERT INTO [dbo].[EmpleadosA] VALUES ('Mike', 'Hombre',18) INSERT INTO [dbo].[EmpleadosA] VALUES ('Rahul', 'Ind',NULL) INSERT INTO [dbo].[EmpleadosA] VALUES ('Sara','Mujer',23) INSERT INTO [dbo].[EmpleadosA] VALUES ('Alberto','H',48) INSERT INTO [dbo].[EmpleadosA] VALUES ('Carlos','Hombre',18) GO CREATE TABLE [dbo].[EmpleadosB]( [Nombre] [varchar](20) NULL, [Email] [varchar](40) NULL, [Titulo] [varchar](20) NULL ) ON [PRIMARY] INSERT INTO [dbo].[EmpleadosB] VALUES ('John','jhon@test.com',NULL) INSERT INTO [dbo].[EmpleadosB] VALUES ('Mike','Mike test.com','Bachillerato') INSERT INTO [dbo].[EmpleadosB] VALUES ('Rahul',' ','Primaria) INSERT INTO [dbo].[EmpleadosB] VALUES ('Sara','sara@test.com','PM') INSERT INTO [dbo].[EmpleadosB] VALUES ('Alberto','alberto@test','Secundaria') INSERT INTO [dbo].[EmpleadosB] VALUES ('Carlos','test@test.com','Licenciado') GO

A continuación ya podemos abrir el Cliente de Calidad de Datos (Microsoft SQL Server 2014>Data Quality Client) para empezar a trastear. Nada más clicar nos pide el nombre del servidor (pensemos en nuestra instancia de servidor SQLSERVER). Lo primero que debemos hacer es crear una Nueva base de Conocimiento.

Lo siguiente será crear una nueva. Le ponemos Nombre y pulsamos siguiente. Cerramos y volvemos al menú principal. A continuación Lo que haremos será elegir de nuestra base de datos la "Detección de conocimiento". Esta opción permite elegir una fuente de datos (excel, sqlserver) y definir dominios a partir de columnas de datos. En mi caso me conecto a la bbdd, elijo una de los tablas de ejemplo para empezar a definir para cada columna que me interese un dominio con parámetros específicos en cada columna que lo necesite... Particularmete interesante la opción de poder elegir, en el caso de una columna de texto, el idioma del mismo, pasarle el corrector ortográfico y también formatear la salida (MAY/MIN para abreviaturas?).

El paso siguiente es el "análisis de detección de datos en el origen seleccionado". Se escanea el origen y se determina por cada columna los valores únicos, los validos y los que infringen la integridad (valores null etc). Si hemos probado sobre EmpleadosA solo vemos un error de integridad debido a un NULL en la  columna Edad. Pulsamos siguiente y podemos elegir que hacer con los nuevos valores que sean validos, así como ignorar los incorrectos marcándolos como error.

(

Ahora, antes de validar de nuevo, definiremos un dominio más complejo para los campos de email. En este caso será una regla de validación de formato. Primero vamos como a antes a la detección de dominios para definir sobre la segunda tabla el dominio sobre la columna email (como el los ejemplos anteriores). Una vez hemos creado el dominio para el campo email, la diferencia es que saldremos y entraremos en el Administrador de dominios sobre nuestra base de datos de conocimiento y modificaremos las Reglas de Dominio (fijaros también que aquí podemos explorar los Valores del Dominio que forman parte de él) para añadir una del tipo: "El valor coincide con la expresión regular": ^[_a-z0-9-]+(\.[a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$

 

Al final publicamos este último cambio de este dominio y ya estamos listos para continuar. 

El último paso ya para acabar con esta prueba es validar una tabla real, corregir los datos y exportar a una nueva con los datos que no cumplan las reglas y decidamos corregir. Para hacerlo abrimos de nuevo el Data Quality Client y creamos un nuevo proyecto de calidad de datos. Le pondremos nombre, elegimos la base de conocimiento que hemos definido antes y elegimos limpieza. Los pasos a partir de aquí son parecidos a los anteriores cuando explorábamos la tabla. En el siguiente paso elegimos la tabla a limpiar y para cada columna, si es necesario, un dominio para aplicar sobre ella. Pulsamos siguiente y escaneamos los datos. Con los datos de ejemplo, detectara dos valores para email erróneos que podremos corregir (si han sido corregidos anteriormente los auto-corrige y aparecen como "Corregido"). Finalmente, en el siguiente paso podemos elegir donde exportar nuestros datos pulidos (por ejemplo a una nueva tabla).

 

Mis conclusiones son...

... que esta es otra herramienta Microsoft bastante fácil de usar y puede que bastante útil para pulir datos maestros de cualquier base de datos o para incluir validaciones como paso intermedio en nuestras cargas para el dwh. Su sencillez (la misma prueba con el proyecto de calidad de datos) hace fácil compartir la carga de corrección manual de los datos con alguien que no sea técnico de bbdd ni programador, más bien una persona dentro de la organización con conocimiento de causa que coja los datos del origen, los interprete y los deje donde tu le digas para cargarlos en algún sitio.

 

SentryOne dispone de diferentes aplicaciones para ayudarnos en la administración y el día a día con nuestra base de datos SQL Server, dwh o como desarrolladores. SentryOne Plan Explorer permite hacer un análisis más gráfico de una consulta que lanzamos sobre SQL Server y deforma interactiva profundizar en diferentes aspectos relacionados como coste de operaciones, peso sobre el lote, índices involucrados...

Plan Explorer tiene versión free y versión PRO. La versión free podemos descargarla directamente de SentryOne donde tenemos el instalador de la aplicación y adicionalmente un addon para SQL Server Management Studio. La instalación no tiene misterio y es del estilo siguiente siguiente.

A primera vista, como otras herramientas parecidas, muestra lo que nos da el propio Management Studio cuando vemos el plan de ejecución estimado/real de una consulta. Lo que es distinto es que de forma predeterminada ya focaliza nuestra atención a las áreas problemáticas que van con nuestra consulta. Por ejemplo, en la pestaña de Plan Diagram, ya nos marca las operaciones más costosas mediante colores. Otro ejemplo es la pestaña Plan Tree que muestra los datos más concretos donde también nos marca de otro color si existe una gran diferencia entre las filas estimadas con las filas reales (estadísticas desactualizadas!).

La forma de empezar a trabajar es bastante sencilla. Basta con hacer un Copy & Paste de la consulta en la pestaña que vemos por defecto en la pantalla inicial, Command Text. A continuación ya podemos pulsar ver el Plan Estimado o el Real, como en Management Studio. Es en ese momento cuando nos pide a que servidor y base de datos conectarse.

Una vez conectados y obtenido el plan estimado de ejecución ya vemos en la parte inferior su diagrama. En el ya nos marca en % relativos al coste total las operaciones y en colores remarcadas las más costosas. A diferencia de Management Studio, si falta un indice nos aparece el símbolo warning en el SELECT en lugar de la parrafada en el encabezado de la pestaña.

Los porcentajes que vemos, por defecto corresponde a la suma de I/O+CPU, pero también podemos elegir solo por I/O o CPU según nos convenga. También podemos hacer que varíe el grueso de las lineas de datos según su volumen (todo esto en el menú contextual del área del gráfico).

En la segunda pestaña del panel inferior, Plan Tree, vemos jerarquizadas las operaciones con los datos más relevantes por defecto (Coste estimado, Coste del subarbol, filas estimadas que pasan, etc...). Podemos añadirle según nuestras necesidades o gustos mas columnas.

En la tercera pestaña, Top Operations, podemos comparar directamente mediante una tabla costes de cpu y de I/O las diferentes operaciones dentro de la consulta. Como en la pestaña anterior, podemos añadir más columnas según nuestros gustos.

En la cuarta, Query Columns, vemos los índices que se usan en la consulta y que filtros aplican. También vemos el tipo de operación (Index Seek, Clustered Index Seek, Index Scan...). En la versión PRO de Plan Explorer desde aquí podemos ver las estadísticas de estos índices e incluso crear uno nuevo.

La quinta pestaña, Join Diagram, muestra de manera directa y muy simplificada las tablas involucradas solo con los campos que usamos en la relación entre ellas.

Parameters muestra los parámetros incluidos en la consulta, incluso los no declarados explícitamente. La pestaña Expressions muestra datos adicionales por cada expresión de agregado: SUM, MIN etc... Realmente no aporta demasiado pero tampoco molesta. Por último, la pestaña Table I/O muestra los datos relativos a entrada y salida de datos: cantidad de scans, lecturas físicas, lógicas.

Una de las diferencias entre trabajar con el plan estimado (Get Estimated PLan) y el real (Get Actual Plan) es que además de las estadísticas de I/O que se muestran en la pestaña Table I/O, también se comparan las filas estimadas con las reales, algo que puede indicar que las estadísticas están desactualizadas.

Otra de las opciones, como mínimo curiosas, es la opción de menú Anonymize. Esta lo que hace es abrir una nueva ventana donde nos ha reemplazado los nombres de nuestras bases de datos, los índices, nombres de usuario, alias, parámetros... con toda la info relativa a la query. La gracia es poder compartir esta información y pedir consejo si hiciera falta.

El addon de SentryOne Plan Explorar para SQL Server Management Studio es un simple vínculo que permite desde la visualización de cualquier plan de ejecución desde el menú contextual, haciendo clic derecho en el área "View with SQL Sentry Plan Explorer". Para instalarlo basta con descargarlo de aquí y reiniciar Management Studio para que podamos usarlo. Es un requisito haber instalado antes la propia aplicación Plan Explorer. El addon de momento funciona con Management Studio incluido en SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 y SQL Server 2012.

 

En resumen...

... Me parece una herramienta útil pero no esencial. Ofrece alguna cosa más que el propio Management Studio y lo hace más bonito pero podemos vivir sin ella. Uno de los motivos por los quizás que me parece recomendable es para alguien que anda perdido y no sabe por donde empezar.

No es lo mismo enfrentarte con la ventana del plan de SSMS que con los colores y pestañas de SQLSentry Plan Explorer. Para el resto, mi conclusión es que lo que más aporta es facilidad de acceso a todos los aspectos relacionados con la query.