Category Archives: Sql Server

Exportar base de datos SQL SERVER a un BLOB en Azure como backup

Decir que Azure, la plataforma Cloud de Microsoft, es una de las más potentes del mercado pese a su corto tiempo de vida no es algo nuevo aunque, precisamente por su juventud, nos encontramos con algunas carencias que hacen algo más complicado las gestiones típicas de un sistema en producción.

Una de estas carencias es la posibilidad de automatizar backups (copias de seguridad) de nuestras bases de datos SQL SERVER algo que necesitamos tener cuando nuestro proyecto pasa el estado de desarrollo.

 

La única posibilidad que nos ofrece Azure en este momento (25/06/2013) es exportar/importar nuestras bases de datos a un blob desde el panel de control a petición pero no podemos programar esta tarea para que se ejecute periódicamente.

 

Esta opción genera en uno de nuestros containers un blob con extensión .bacpac con la copia de seguridad de la base de datos escogida para poder importarla en cualquier momento.

La pregunta es, ¿cómo podemos hacer esto automáticamente? La respuesta, con una aplicación que llame periódicamente a la misma API REST que usa Azure para estas tareas.

 

Azure Sql Server API REST

Azure Sql Server expone un API REST con algunas tareas básicas que podemos invocar (con las debidas credenciales, claro está) para exportar o importar nuestras bases de datos contra Azure Storage.

Cada una de las regiones de Azure Sql Server tiene su propia url del servicio por lo que deberás asegurarte de usar la url correcta en función de en que región se encuentre tu base de datos:

 

 

DataCenter Endpoint
North Central US https://ch1prod-dacsvc.azure.com/DACWebService.svc/
South Central US https://sn1prod-dacsvc.azure.com/DACWebService.svc/
North Europe https://db3prod-dacsvc.azure.com/DACWebService.svc/
West Europe https://am1prod-dacsvc.azure.com/DACWebService.svc/
East Asia https://hkgprod-dacsvc.azure.com/DACWebService.svc/
Southeast Asia https://sg1prod-dacsvc.azure.com/DACWebService.svc/
East US https://bl2prod-dacsvc.azure.com/DACWebService.svc/
West US https://by1prod-dacsvc.azure.com/DACWebService.svc/

 

Las dos tareas expuestas por este servicio son “Export” e “Import” (para exportar e importar respectivamente) por lo que, por ejemplo, para solicitar una exportación de una base de datos alojada en el Norte de Europa deberíamos hacer un POST contra la url: https://db3prod-dacsvc.azure.com/DACWebService.svc/Export

 

Solicitando Exportación desde código

Para automatizar el proceso de exportación podemos programar un pequeño Worker Role de Azure, un servicio de Windows que se ejecute en uno de nuestros servidores locales o incluso que lo implemente nuestra propia web de administración. En mi caso dispongo de un Worker Role propio con varias tareas de mantenimiento que se ejecutan cada cierto tiempo por lo que me fue muy fácil incluir una nueva tarea para hacer este backup.

Para hacer esta exportación, como hemos comentados antes, basta con hacer un POST contra la [url del servicio] + “/Export” incluyendo en el cuerpo de la petición un xml con los datos necesarios para la tarea.

 

Este xml que tenemos que postear tiene el siguiente formato:

[code language=”xml”]

<ExportInput
xmlns="http://schemas.datacontract.org/2004/07/Microsoft.SqlServer.Management.Dac.ServiceTypes"
xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<BlobCredentials
i:type="BlobStorageAccessKeyCredentials">
<Uri>[URL del Blob a crear]</Uri>
<StorageAccessKey>[Clave de acceso del Azure Storage]</StorageAccessKey>
</BlobCredentials>
<ConnectionInfo>
<DatabaseName>[Nombre de la base de datos a exportar]</DatabaseName>
<Password>[Password del usuario de la base de datos]</Password>
<ServerName>[Nombre del servidor en el que se encuentra la base de datos]</ServerName>
<UserName>[Usuario de acceso a la base de datos]</UserName>
</ConnectionInfo>
</ExportInput>

[/code]

Los parámetros que debes rellenar son:

  • [URL del Blob a crear]
    • Url completa del blob que vas a crear con “.bacpac” al final.
      • Ej: https://mistorageenazure.blob.core.windows.net/bak/backup-2013-06-25_09-49.bacpac
  • [Clave de acceso del Azure Storage]
    • La “Primary Access Key” de nuestro Storage en Azure. La podemos encontrar en el panel de administración de Azure > Storage > [el storage al que queremos acceder] > Manage Access Keys
  • [Nombre del servidor en el que se encuentra la base de datos]
    • Nombre completo del servidor de SQL SERVER en el que se encuentra la base de datos a exportar (debe terminar en “database.windows.net”)
      • Ej: server.database.windows.net
  • Datos de la BD
    • Nombre, usuario y contraseña de la base de datos a exportar

 

Con este xml generado ya solo necesitamos hacer un POST contra la url del servicio para solicitar la tarea de exportación.

Este servicio nos devuelve un GUID para la posterior comprobación del estado de la tarea. Podemos guardarlo si queremos para poder saber si ha terminado con éxito o no.

Aquí un ejemplo con el código completo:

[code language=”csharp”]
private Guid RequestExport(string databaseName, string containerUrl, string storageAccessKey, string blobName)
{
// Método que devolvería la URL del Servicio en función de la Región. Yo le paso un tipo enum propio por comodidad
var managementUrl = GetAzureManagementUrlByRegion(AzureRegions.NorthEurope);

var request = WebRequest.Create(managementUrl + "/Export");
request.Method = "POST";

var dataStream = request.GetRequestStream();
// Método que genera el XML que se le pasa como Body con el formato comentado anteriormente
var body = GetExportToBlobBodyRequest(blobUrl, storageAccessKey, databaseName);
var utf8 = new UTF8Encoding();
var buffer = utf8.GetBytes(body);
dataStream.Write(buffer, 0, buffer.Length);

dataStream.Close();
request.ContentType = "application/xml";

// La respuesta HTTP contiene el GUID que representa la tarea serializado como XML
using (var response = request.GetResponse())
{
var encoding = Encoding.GetEncoding(1252);
using (var responseStream = new StreamReader(response.GetResponseStream(), encoding))
{
using (var reader = XmlDictionaryReader.CreateTextReader(responseStream.BaseStream, new XmlDictionaryReaderQuotas()))
{
var serializer = new DataContractSerializer(typeof(Guid));
return (Guid)serializer.ReadObject(reader, true);
}
}
}
}
[/code]

¡Listo! Nuestra tarea de exportación ya se está lanzando y, si todo funciona como debe, en breve se creará un nuevo blob con el contenido de nuestra base de datos para poder importarlo en caso de catástrofe o para replicarlo en otra base de datos distinta para hacer pruebas.

Comprobando el estado de la tarea

Si queremos saber si todo ha ido bien o ha fallado algo podemos hacer GET a otro método del API REST de Azure llamado “Status” indicando el GUID obtenido en la petición de la tarea. Para ello basta con hacer GET de la siguiente url:

[Url del servicio API REST de tu Región]/Status?servername=[servidor de base de datos completo]&username=[usuario de la base de datos]&password=[password de la base de datos]&reqId=[GUID de la tarea]

 

Esta url nos debe devolver un XML con información del estado de la tarea.

 

¡Hecho! Con un simple cliente HTTP podemos hacer exportaciones e importaciones periódicas y automáticas de nuestra base de datos y así protegernos ante cualquier problema que podamos tener.

Como siempre para cualquier duda ya sabéis dónde podéis encontrarme.

 

¡Un saludo y nos vemos Compilando!

Configuración del Formato de Fechas en SQL SERVER

 

Aunque lo ideal es encapsular las variables de fecha en parámetros ya formateados en ocasiones (sobretodo en proyectos viejos) te encuentras con algunas consultas con las fechas directamente en string como el típico:

SELECT * FROM Pedidos WHERE FechaPedido >= '30/01/2011'

Es posible que esta consulta funcione correctamente en tu servidor de SQL SERVER local pero al llevarlo a producción te encuentres en ocasiones con el típico error:

La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo / The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Este error ocurre cuando el servidor intenta comparar una columna con formato “DateTime” (como es el campo “FechaPedido” de nuestro ejemplo) con un String pasado como parámetro y, al intentar convertirlo se desborda.

¿Por qué ocurre esto? Porque el idioma del servidor le indica que debe interpretar las fechas de otro modo y al convertir el String en Date da un valor incorrecto (lo más clásico es que lo intente convertir en MM/DD/YYYY y como el mes 30 no existe pasa lo que pasa…).

 

Hay varias formas de solucionar este problema aunque, como he dicho al principio del post, lo ideal es pasar la fecha en una variable ya formateada como Date usando tecnologías de acceso a datos como Entity Framework, Linq o con los DataSets de ADO.NET.

 

Como SysAdmin: Modificar la configuración global de idioma del servidor

Si queremos modificar el idioma predeterminado de TODO el servidor para TODAS las bases de datos podemos hacer los siguiente:

Como “sa” y contra “master” ejecutamos lo siguiente:

SP_CONFIGURE 'default language', 5

RECONFIGURE;

Dónde el 5 pasado como parámetro es el idioma “Español” con formato de fecha “dd/mm/yyyy”.

Si queremos establecer otro idioma y no sabemos su identificador usad esta consulta:

USE master
SELECT * FROM syslanguages

De este modo todo nuevo login contra el servidor de base de datos usará este idioma por defecto y afectará a toda conversión de fecha que se solicite (como en la SELECT del ejemplo, un DATEDIFF o un DATENAME para obtener el nombre del día o mes).

 

Como SysAdmin: Modificar la configuración de idioma de un login en concreto

Si no queremos cambiar la configuración de idioma de todo el servidor (cosa lógica si hay otras bases de datos funcionando y no queremos modificar su comportamiento) podemos modificar sólo la configuración de idioma de un login en concreto para que cualquier consulta contra cualquier base de datos que lance dicho login utilice el idioma indicado.

La consulta para cambiar el idioma de un login es:

EXEC sp_defaultlanguage 'nombre del login', 'idioma'

Dónde “idioma” es el nombre del idioma que queremos establecer que podemos obtener del campo “name” de la tabla “master.syslanguages” tal y cómo hemos explicado antes.

 

Al igual que con el otro método es necesario que el usuario vuelva a reconectarse para hacer uso de la nueva configuración.

 

Como desarrollador: Establecer el idioma antes de hacer una consulta

Si no tienes más remedio que hacer una consulta pasando fechas como string puedes asegurarte de escoger el formato antes de realizar la consulta. Para ello basta con añadir la siguiente clausula ANTES de hacer la consulta:

SET LANGUAGE 'idioma'

Con lo que podríamos hacer que nuestra consulta de ejemplo funcione siempre en cualquier servidor sea cual sea el idioma del mismo haciendo la consulta así:

SET LANGUAGE 'español';

SELECT * FROM Pedidos WHERE FechaPedido >= '30/01/2011';

De este modo nos aseguramos que la conversión de la fecha siempre sea con formato del idioma “español” que es dd/mm/yyyy

 

Depende del escenario que tengas deberás usar un método u otro aunque, lo siento por repetirme, lo mejor es controlar el formato de la fecha a nivel de aplicación y no ir modificando la configuración del servidor.

 

 

Nos vemos compilando!!

Code Snippet: LastIndexOf en SQL SERVER

Uno de los mótivos que me impulsó a abrir el blog es para usarlo también como “bloc de notas” e ir anotando pequeños apuntes de código que me han parecido interesanto o que me han resulto un problema. 

Estrenamos esta categoría llamada “Code Snippet” con una pequeña consulta en T-SQL para SQL SERVER que sirve para obtener la última aparación de un caracter en concreto en un string; vamos, lo que ha hecho toda la vida el método LastIndexOf() de .NET pero que SQL no incluye por defecto.

 

 

LEN(STRING) - CHARINDEX('CHAR TO SEARCH', REVERSE(STRING)) + 1

 

 

Básicamente le doy la vuelta al string y obtengo la primera aparición del caracter a buscar.

Un ejemplo de uso para buscar, por ejemplo, la posición de última “/” en un string sería así:

 

SELECT LEN('C:/Dir/File.txt') - CHARINDEX('/', REVERSE('C:/Dir/File.txt')) + 1;

 

Esta consulta devolvería “11”. Si queremos quedarnos con lo que hay después (como ha sido mi caso, que necesitaba el “File.txt”), la consulta quedaría así:

 

SELECT RIGHT('C:/Dir/File.txt', CHARINDEX('/', REVERSE('C:/Dir/File.txt')) - 1);

 

Espero que os sea de ayuda.

 

Nos vemos Compilando!!