Configurar SQL en un IaaS de Azure para acceso remoto

Estoy en un proyecto precioso en donde estamos realizando un piloto para poder migrar dos instancias de SQL 2008 a una sola en Azure, en modo IaaS, que utilice SQL 2014.

La creación de la máquina virtual es tan sencilla, que no merece la pena ni describirla. Siguiendo los asistentes de Azure, la montas en un pis-pas.

Pero acceder desde mi SQL Manager en local a la base de datos de la VM en Azure, no es directo ni trivial. Así vamos a ir desde fuera hacia adentro, en la configuraciones necesarias para el correcto funcionamiento.

Lo primero es entrar en el portal de Azure y seleccionar la máquina virtual donde tenemos la SQL.

Y aquí me voy a la pestaña de extremos. En donde veo que están habilitados los puertos para acceso vía PowerShell y Remote Desktop; y en donde doy de alta un nuevo acceso llamado MSSQL (que escojo de la lista y me configura de forma automática el puerto 1433).

 

Vale, hasta aquí lo que hay que hacer en el panel de Azure. Ahora nos vamos a la máquina virtual por medio del Escritorio Remoto y nos vamos a configurar la base de datos.

Lo primero es modificar la seguridad de la base de datos, y permitir conectarse por medio de login/password; ya que por defecto solo permite por identidad de windows.

Después debo de dar de alta un nuevo “login” para el usuario con quien me voy a conectar de forma remota, lo cual a estas alturas deberías controlar al dedillo. Sobre todo el tema de los permisos mínimos necesarios.

Por defecto las conexiones TCP están habilitadas en las plantillas de máquinas virtuales que nos ofrece Azure, por lo cual – si te apetece – solo es necesario revisar que estén activas y habilitadas.

Pero aún falta un paso más, y es abrir el puerto en el Firewall del servidor. Que por defecto, como buen server, está todo cerrado y hay que definirlo de forma específica por medio de dar de alta una nueva regla.

Para ello abrimos el Firewall y seleccionamos “Inbound Rules”. A la derecha veremos un menú vertical en donde puedo seleccionar “New Rule…”.

Y aquí sigo la siguiente secuencia para dar de alta el nuevo puerto 1433 por TCP:

Y ahora si, viendo el nombre DNS que Azure nos ha dado para la máquina virtual (<el_nombre_de_tu_maquina_virtual>.cloudapp.net), puedo usar los datos de conexión desde mi SQL Manager para conectar de forma remota a mi SQL en formato IaaS en Azure.

En resumen:

• Primero abre el puerto en Azure para que pueda escuchar la máquina virtual.
• Configura la SQL para que te puedas logar con Usuario/contraseña.
• Crea un usuario de SQL para poder conectarte, y dale permisos adecuados.
• Añade una regla al Firewall del servidor virtual para que permita abrir el puerto 1433.
• Conéctate al servidor a la dirección DNS que te ha asignado Azure: xxxx.cloudapp.net.

Espero que te sea útil.

Los límites de smalldatetime

SQL es para mí una fuente de contradicciones constante. Por una parte es indudable que es el lenguaje perfecto para su objetivo: manipular datos de la forma más eficiente posible. Pero, como desarrollador en lenguajes de programación, la lógica me parece enrevesada y muchas veces confusa.

Así tenemos el objeto smallDateTime. Un objeto de fecha normal y corriente. ¿No? Pues no. Tiene márgenes temporales más reducidos que el tipo DateTime. Exactamente del 01/01/1900 al 06/06/2079. Me imagino que la literatura del porqué de estos valores será variada y completa. Pero la limitación de 1900 convierte un error de traducción en una excepción de la base de datos.

Asique, en mi opinión, no utilices smallDateTime y utiliza siempre DateTime.

Un error con vistas SQL

En el proyecto en el que estoy actualmente, estamos en la fase de garantía y evolutivo. Es decir, ya tenemos la aplicación en producción y ahora estamos corrigiendo los fallos encontrados, que a causa de haber echo pocos test han sido demasiados, y evolucionando según los nuevos requisitos y nuevas funcionalidades que nos pide el cliente.

Ayer, puse en producción una nueva actualización y me llevé una negativa sorpresa al saltar las alarmas en varios informes y en una página que daba un error que ni en el servidor de desarrollo, ni en el de preproducción, ni en uno de producción (son varios servidores implicados en el proyecto), se podía reproducir. Y que nos indicaba que no podía encontrar dos columnas en la tabla solicitada!!

Desesperado ante la inminente avalancha de llamadas por el bloqueo, nos revisamos el código y no localizábamos el error hasta que nos dimos cuenta que se producía en la carga de un grid por medio de un SqlDataAdapter que utilizaba un SQL que realizaba el FROM de una vista que hacia la unión entre dos tablas.

Aunque en ese momento me relaje un poquitito, ya que el problema no era de mi equipo por lo cual tampoco era nuestra responsabilidad directa, me senté igualmente con el administrador de base de datos del cliente a revisar qué le podía pasar a la vista.

La cosa se volvió extraña cuando vimos que la vista existía y era algo tal que así:

SELECT * FROM tablaA
UNION
SELECT * FROM tablaB

Revisando el historial nos dimos cuenta que habíamos modificado las tablas para añadir dos campos, justamente los que faltaban, hacia ya unas semanas. Pero mirando la vista, parecía que todo estaba correcto… pero no es así. Lanzando la vista en uno de los servidores donde fallaba nos encontramos que no incluía las columnas nuevas y las columnas de replicación (es un sistema de bases de datos sincronizadas).

A lo cual, hallamos por fin la solución al problema: BORRAS la vista –no vale con modificarla- y la vuelves a crear. Y, voala!!, todo funcionando como un tiro.

No lo he buscado en MSDN , pero supongo que SQL compila y cachea las vistas y puede, en este caso tan especial, dar este tipo de problemas.

Comparaciones de fechas en SQL.

Una de esas cosas que en código son tan simples, en SQL parecen espantosamente complicadas hasta que encuentres a quien lo ha resuelto de forma elegante y sencilla.

En este caso aquí hay una perfecta solución a un problema engorroso de comparación de fechas. Ya que el formato DateTime incluye información horaria y hay que complicar la comparación delimitandola desde  las 0:00:00 hasta las 23:59:59 para evitar errores en el filtro. Típico pedir todos los registros del dia XX y que descarte todos aquellos que sean posteriores a las 00:00:00.

En el post de Richard Chamorro se hace referencia a la solución de Anatoly Lubarsky La cual es, simplemente brillante.

DATEADD(dd, 0, DATEDIFF(dd, 0, CAMPO_FECHA))

De esta forma se descarta la parte horaria de los valores de las fecha y así, por ejemplo, para comprobar que una fecha está dentro del día de hoy simplemente realizo la siguiente comprobación:

DATEADD(dd, 0, DATEDIFF(dd, 0, @Fecha)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Espero que os sea tan util como a mi.

Los límites de smalldatetime

SQL es para mí una fuente de contradicciones constante. Por una parte es indudable que es el lenguaje perfecto para su objetivo: manipular datos de la forma más eficiente posible. Pero, como desarrollador en lenguajes de programación, la lógica me parece enrevesada y muchas veces confusa.

Así tenemos el objeto smallDateTime. Un objeto de fecha normal y corriente. ¿No? Pues no. Tiene márgenes temporales más reducidos que el tipo DateTime. Exactamente del 01/01/1900 al 06/06/2079. Me imagino que la literatura del porqué de estos valores será variada y completa. Pero la limitación de 1900 convierte un error de traducción en una excepción de la base de datos.

Asique, en mi opinión, no utilices smallDateTime y utiliza siempre DateTime.

SQL. Llamar a tablas en distintas bases de datos

Una entrada pequeñita y con poca importancia.

Tengo dos bases de datos en un mismo motor de SQL y debo acceder a una tabla en cada una de ellas.

Es tan facil como utilizar ...

Osea que para obtener una suma de conjuntos de ambas tablas:

select resultado1.*, resultado2.*
from bd01..tabla as resultado1, bd02..tabla as resultado2
where resultado01.id = resultado02.id

Espero que os sea tan util como a mi.

SQL. Recorrer y actualizar ciertos registros de una tabla. Cursores.

A veces, mi ignorancia en el lenguaje SQL que hace verme forzado a trabajar en horas intempestivas como hoy (terminar a las 02:00 para reiniciar a las 06:00).

El problema es que tengo una tabla de cientos de miles de registros con un campo expediente que tiene que ser único. Es decir, que no admita duplicados. Y en dicho campo, 290 mil registros tienen un valor que es vacio, es cero o es directamente null.

El quid del asunto es localizar dichos registros y actualizarme el campo expediente con una id construido.

Después de demasiadas horas perdidas, la madrugada y un reparador mini sueño, me trajo la respuesta en forma de Cursores.

Primero declaremos las variables que voy a utilizar en las operaciones.

-- Id del registro
-- Campo expediente a modificar
-- Contador, campo numérico autoincremental que vamos a almacenar en el campo expediente
declare @id as nvarchar(16)
declare @expediente as varchar(30)
declare @contador as int

A continuación declaramos el cursor, para manipular los datos solamente sobre los expedientes que cumplen la condición, en vez de sobre los cientos de miles de toda la tabla.

declare CURSORVC cursor for
  select id, expediente  from Tabla
  Where
  expediente = '0'
  OR expediente = ''
  OR expediente = null
  OR expediente IS NULL
  Order by id

Inicializamos el contador.

SET @contador = 10

Y abrimos el cursor, recuperando la primera fila. Aquí lo único que hay que tener cuidado es que las variables en las que cargamos los datos del cursor sean correspondientes a los campos obtenidos en la select de la declaración del cursor.

open CURSORVC
  fetch next from CURSORVC into @id, @expediente

A continuación iniciamos el bucle

while @@fetch_status = 0
    begin

Actualizamos los datos del campo expediente cuando el id sea el del registro que nos ha traido el cursor

update Tabla set expediente =  CAST(@contador As varchar(10)) + ' -2010'
    where id=@id
    -- Avanzamos otro registro
    fetch next from CURSORVC into @id, @expediente

-- Avanzamos el contador en uno
    set @contador = @contador + 1
   end

Y fínalmente cerramos el cursor y lo eliminamos de memoria

      close CURSORVC
deallocate CURSORVC

Y así, lo que por código tardaba horrores, pero horrores de los malos, ahora he actualizado los casi trescientos mil registros en menos de tres minutos.

P.D. Besitos al Borjus que me recordó que con experiencia un cursor como este se hace en unos 10 minutos, contra las cinco horas que me ha costado el primero mío :)

Listado de registros duplicados de una tabla. SQL.

Tengo una tabla que estoy migrando de base de datos que contiene un registro que contiene un id de expediente que debe ser único para poder ponerle la clave  principal.

Y la verdad que el lenguaje SQL puede conmigo… pero tengo un equipo que vale oro y en una mini reunión me llevaron de la mano al siguiente código:

SELECT expediente, count(expediente)
FROM [tabla]
group by expediente
having count(expediente)  > 1

El cual me devuelve un listado de todos los expedientes que, al menos, estén duplicados.

Y después de corregir la tabla, ya puedo hacer clave primaria este campo.

Copia de seguridad de tablas. Rápidamente.

Quiero hacer una copia de seguridad de una tabla que voy a modificar y no quiero liarme con el backup de la base de datos entera:

SELECT * INTO <nombre de la nueva tabla> FROM <tabla original>

Con esta sentencia guardamos toda la tabla original en una idéntica con el nuevo nombre, que se crea automáticamente.

Moooola!!

Conversion failed when converting the nvarchar value <string> to data type int.

Bonito error cuando me estoy trayendo, para cargar un GridView, el nombre, primer y segundo apellido de una persona y le añado por delante el identificador.

La cadena que yo esperaba que me funcionara era:
QM_Conductores.id + ' - ' + QM_Conductores.nombre + ' ' + QM_Conductores.apellido1 + ' ' + QM_Conductores.apellido2 AS Conductor

Y la que funciona bien es:
CAST (QM_Conductores.id AS VARCHAR(3)) + ' - ' + QM_Conductores.nombre + ' ' + QM_Conductores.apellido1 + ' ' + QM_Conductores.apellido2 AS Conductor

¿El truco? La necesidad de realizar la conversión explicita del tipo entero a varchar para entonces poder utilizar el operador de concatenación (+).

Suerte y al toro!!

Database is in transition...Error 952

Hay cosas que no entiendo porqué funcionan, y que cuando no funcionan  cascan el SQL Server 2005.

A causa de un USE mal puesto en un script le hice un DROP a varias bases de datos…!! A lo cual me dispuse a restaurarla, a lo cual me dice el servidor que nanai, que esa base de datos tiene conexiones activas.

A lo cual le digo que la ponga offline… a lo cual el servidor se vuelve loco y ni la deja offline ni la deja de ninguna forma. Simplemente no hay forma de acceder a ella:

Database is <database> in  transition...Error 952

Con el equipo mirando las musarañas y las paredes, corro y hago un restore en otra base de datos y, mientras, busco en San Google alguna solución. Y en los foros de SQL Server de MSDN la encontré:

Subes al nivel del motor de base de datos y abres una nueva pestaña de script. Lo primero es encontrar cuales son los procesos abiertos en la SQL:

EXEC sp_who2;

En el listado de procesos hay que buscar el que esté en estado SUSPEND y, comprobando que está relacionado con nuestra base de datos o el usuario de conexión, le buscamos el identificador o PID.

A continuación le decimos que mate el proceso con:

KILL <pid>;

Y deberíamos acceder de forma inmediata a nuestra base de datos. (hacer un backup).

Migrar esquema MySql a Base de datos SQL 2008

Buena, en un nuevo e interesante proyecto tengo que migrar un esquema de MySql a una base de datos MS SQL 2008 Server.

Esto lo he realizado de la forma larga que es instalando un MySQL, importando el script del esquema, y utilizando la herramienta SSMA 2008 for MySQL de Microsoft.

Este post está centrado en las dudas y problemas que he tenido en realizar los pasos antes descritos.

Paso nº 1.

• Instalar el MySQL 5.1.34. Y darle al usuario root una contraseña de la que me acuerde.
• Instalar las gui tools 5.0. r17.

En la instalación por defecto, el MySQL queda arrancado como un servicio windows.

Aquí me encuentro el primer “problema” . Que es cuando abro el MySql Administrator y me pide el nombre del host al que me quiero conectar…

… la solución es tan sencilla como inesperada para los que estamos acostumbrados a la excelente gestión automática de servidores de SQL Server. El nombre de la instancia es localhost y el usuario root. Más tarde podrás añadir los usuarios que necesites.

Una vez conectado seleccionas en Tools –> My Sql Query Browser, que es en donde gestionas los scripts de los esquemas.

Me ha encantado la función Drag & Drop en donde arrastrando una tabla de un esquema al editor de script, automáticamente te escribe la Select.

A continuación, en una ventana limpia cargo el script del esquema que quiero recuperar. Para ello selecciono File –> Open Script y selecciono el fichero .sql que más me convenga.

Y aquí llegamos al segundo impedimento que me encontré. El script, al lanzarlo, me devolvía un error indicando que no sabía que  base de datos utilizar. A lo que, utilizando lo de Drag & Drop, le señalé el esquema en donde desplegarlo. Poniéndome el solito la sentencia Use que necesitaba al principio del script.

Ya tengo mi esquema completito y toca migrarlo a SQL Server. Para ello busco en San Google y me encuentro una herramienta de Microsoft llamada SSMA 2008 for MySql que hace lo que yo quiero. La bajo y la instalo. Llegando a la siguiente detención en el proceso.

El instalador no encuentra ningún conector MySql en el sistema, pero me da la oportunidad de bajármelo de la Web de MySql. Entro en el Site y selecciono el fichero mysql-connector-net-6.2.2.zip y lo descargo sin registrarme (aunque debiera).

Por si las moscas, instalo el conector completo. Pero, por la ley de Murphy y por la manía de no leer las instrucciones, me he instalado el driver para .Net y lo que me pide la aplicación es un driver ODBC por lo cual me vuelvo a ir a la web de MySql y me bajo Connector-ODBC 5.1.6.

Cierro la instalación de SSMA y vuelvo a iniciarla encontrándome, ahora sí, el conector odbc. Como anteriormente, lo instalo completo no vaya a necesitar esa función extraña que nadie usa .

Siguiente impedimento: extrañado veo que el programa me pide que registre una licencia. Abro el enlace, y me valido con mi liveId, abriéndose un formulario de registro.

Cuando pulso el botón aceptar, la página me remite un fichero .license que supongo debo situar en el directorio que me señala la aplicación SSMA

Pulso el botón Refresh License y voala!! ya tengo mi aplicación registrada… (que curioso, no lo había tenido que hacer anteriormente más que en las licencias de pago)

Por fin se abre la ventana de Migración y creamos un nuevo proyecto de migración indicándole que el destino es una base de datos SQL Server.

Acto seguido me conecto a las dos bases de datos. Primero a la MySQL y segundo a la SQL 2008. Y aquí me encuentro con impedimento más: no tengo la posibilidad de realizar una búsqueda de las instancias de sql 2008, por lo cual tengo que abrir el SQL Manager y copiar el nombre de la instancia.

Y entonces también me doy cuenta que me obliga a meter la base de datos de destino… lo cual me recuerda que no la tengo echa!!. Asiqué aprovecho que tengo abierto el Sql Manager y me creo la base de datos destino para poder introducirla, después, en  la ventana de conexión del SSMA.

Y aquí viene un GRAN impedimento… no funciona con SQL 2008 Express :(

Pero para eso están las máquinas virtuales y más de una y más de dos tengo con algún SQL Server instalado… pero otra vez la Ley de Murphy aparece en toda su extensión y dos horas después aún me estoy pegando para que tener acceso a alguna instancia de un SQL 2008 server.

… unas horas más tarde aún me sigo pegando con las instancias de sql2008 a las que quiero tener acceso…

Bueno, finalmente he conseguido tener mi instancia de Sql 2008 server en funcionamiento y la cosa es tan simple como que hay dos únicos pasos.

1. Convertir el esquema, que pasa la estructura de la base de datos MySql a Sql Server.

2. Migrar datos.

Y con esto y un bizcocho, tengo replicada la base de datos MySql en Sql 2008 server.