Series temporales en SQL

A propósito del post que ha escrito Jose A Bautista, Series temporales en SQL, este post es complementario (o casi idéntico y entonces se podría considerar un plagio) para terminar de entender cómo calcular las series temporales, o expresado en un lenguaje más llano, crear intervalos.

El problema al que nos enfrentamos (y resolvió, quiero insistir, Jose A Bautista de forma efectiva) era el siguiente:

Saber que productos han tenido stock pero no ventas, en un periodo determinado.

El problema del problema (un metaproblema por así decirlo) no es averiguar lo anterior, para eso no es necesario crear series temporales, sino trabajar con un gran volumen de datos y que la consulta siga ejecutándose en un tiempo razonable, por eso crear intervalos ofrece una solución porque, presumiblemente (y depende mucho de la dispersión de los datos), reducirá el conjunto de filas sobre el que se ejecuta la consulta (asumiendo también que crear esta tabla intermedia tiene un coste, tanto en tiempo como en almacenamiento).

El set de datos inicial es el siguiente:

CREATE TABLE [dbo].[Sales](
	[ProductId] [int] NOT NULL,
	[PointOfSaleId] [int] NOT NULL,
	[Date] [date] NOT NULL,
	[Quantity] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (1, 1, CAST(N'2020-01-01' AS Date), 1)
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (1, 1, CAST(N'2020-01-02' AS Date), 2)
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (1, 1, CAST(N'2020-01-03' AS Date), 3)
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (1, 1, CAST(N'2020-01-10' AS Date), 10)
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (1, 1, CAST(N'2020-01-11' AS Date), 11)
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (1, 1, CAST(N'2020-01-20' AS Date), 20)
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (2, 1, CAST(N'2020-01-01' AS Date), 1)
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (2, 1, CAST(N'2020-01-02' AS Date), 2)
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (2, 1, CAST(N'2020-01-05' AS Date), 5)
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (2, 2, CAST(N'2020-01-15' AS Date), 15)
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (2, 2, CAST(N'2020-01-16' AS Date), 16)
GO
INSERT [dbo].[Sales] ([ProductId], [PointOfSaleId], [Date], [Quantity]) VALUES (2, 2, CAST(N'2020-01-25' AS Date), 25)

Finalmente tenemos los siguientes datos:

ProductId	PointOfSaleId	Date	Quantity
1	1	01-01-2020	1
1	1	01-02-2020	2
1	1	01-03-2020	3
1	1	01-10-2020	10
1	1	01-11-2020	11
1	1	01-20-2020	20
2	1	01-01-2020	1
2	1	01-02-2020	2
2	1	01-05-2020	5
2	2	01-15-2020	15
2	2	01-16-2020	16
2	2	01-25-2020	25

El siguiente paso es entender la función ROW_NUMBER

-- Sin PARTITION, simplemente tenemos un contador
SELECT ROW_NUMBER() OVER (ORDER BY ProductId) AS rn, * FROM Sales

rn	ProductId	PointOfSaleId	Date	Quantity
1	1	1	01-01-2020	1
2	1	1	01-02-2020	2
3	1	1	01-03-2020	3
4	1	1	01-10-2020	10
5	1	1	01-11-2020	11
6	1	1	01-20-2020	20
7	2	1	01-01-2020	1
8	2	1	01-02-2020	2
9	2	1	01-05-2020	5
10	2	2	01-15-2020	15
11	2	2	01-16-2020	16
12	2	2	01-25-2020	25

-- PARTITION BY cambia, así que se reinicia el contador
    SELECT ROW_NUMBER() OVER (PARTITION BY ProductId, PointOfSaleId ORDER BY ProductId) AS rn, * FROM Sales    

rn	ProductId	PointOfSaleId	Date	Quantity
1	1	1	01-01-2020	1
2	1	1	01-02-2020	2
3	1	1	01-03-2020	3
4	1	1	01-10-2020	10
5	1	1	01-11-2020	11
6	1	1	01-20-2020	20
1	2	1	01-01-2020	1
2	2	1	01-02-2020	2
3	2	1	01-05-2020	5
1	2	2	01-15-2020	15
2	2	2	01-16-2020	16
3	2	2	01-25-2020	25

Ahora que ya sabemos como funciona ROW_NUMBER, vamos a centranos en lo que queremos conseguir, el resultado final, los intervalos:

ProductId	PointOfSaleId	StartDate	EndDate	Quantity
1	1	01-01-2020	01-03-2020	6
1	1	01-10-2020	01-11-2020	21
1	1	01-20-2020	01-20-2020	20
2	1	01-01-2020	01-02-2020	3
2	1	01-05-2020	01-05-2020	5
2	2	01-15-2020	01-16-2020	31
2	2	01-25-2020	01-25-2020	25

Para llegar del conjunto inicial de datos al resultado final, la SQL necesaria es:

WITH sales_cte AS
    (SELECT ProductId,
            PointOfSaleId, 
            Date,
            SUM(Quantity) AS Quantity
     FROM Sales
     GROUP BY ProductId,
              PointOfSaleId,
              Date),
       groups_cte AS
    (SELECT ROW_NUMBER() OVER (ORDER BY date) AS row_number,
      DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY ProductId, PointOfSaleId ORDER BY Date), Date) AS [group],
          ProductId,
          PointOfSaleId , 
          Date,
          Quantity
     FROM sales_cte)   
  SELECT ProductId,
         PointOfSaleId,
         MIN(Date) AS StartDate,
         MAX(Date) AS EndDate,
         SUM(Quantity) AS Quantity
  FROM groups_cte
  GROUP BY ProductId,
           PointOfSaleId,
           [group]  

La clave de la SQL está en -ROW_NUMBER(), vamos a ver el resultado intermedio y entender así que los días consecutivos están creando un grupo que podremos usar posteriormente:

SELECT 
	ROW_NUMBER() OVER (ORDER BY date) AS row_number,
	ProductId,
    PointOfSaleId,
	Date,
	Quantity,
	-ROW_NUMBER() OVER (PARTITION BY ProductId, PointOfSaleId ORDER BY Date) AS negative_row_number,
    DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY ProductId, PointOfSaleId ORDER BY Date), Date) AS [group]
INTO #groups
FROM (SELECT ProductId,
          PointOfSaleId, 
		  Date,
		  SUM(Quantity) AS Quantity
   FROM Sales   
   GROUP BY ProductId,
            PointOfSaleId,
            Sales.Date) as T;
SELECT * FROM #groups;

row_number	ProductId	PointOfSaleId	Date	Quantity	negative_row_number	group
1	1	1	01-01-2020	1	-1	12-31-2019
3	1	1	01-02-2020	2	-2	12-31-2019
5	1	1	01-03-2020	3	-3	12-31-2019
7	1	1	01-10-2020	10	-4	01-06-2020
8	1	1	01-11-2020	11	-5	01-06-2020
11	1	1	01-20-2020	20	-6	01-14-2020
2	2	1	01-01-2020	1	-1	12-31-2019
4	2	1	01-02-2020	2	-2	12-31-2019
6	2	1	01-05-2020	5	-3	01-02-2020
9	2	2	01-15-2020	15	-1	01-14-2020
10	2	2	01-16-2020	16	-2	01-14-2020
12	2	2	01-25-2020	25	-3	01-22-2020

Después de esto ya podemos agrupar por el grupo creado (la columna [group]) y usar funciones de agregado para sacar el resto de los datos:

SELECT ProductId,
       PointOfSaleId,
	   [group],
       MIN(Date) AS StartDate,
       MAX(Date) AS EndDate,
	   SUM(Quantity) AS Quantity
FROM #groups
GROUP BY ProductId,
         PointOfSaleId,
         [group]

ProductId	PointOfSaleId	group	StartDate	EndDate	Quantity
1	1	12-31-2019	01-01-2020	01-03-2020	6
1	1	01-06-2020	01-10-2020	01-11-2020	21
1	1	01-14-2020	01-20-2020	01-20-2020	20
2	1	12-31-2019	01-01-2020	01-02-2020	3
2	1	01-02-2020	01-05-2020	01-05-2020	5
2	2	01-14-2020	01-15-2020	01-16-2020	31
2	2	01-22-2020	01-25-2020	01-25-2020	25

Por acabar con algún dato del mundo real, para el cliente en el que estamos haciendo algunas pruebas la reducción de filas ha sido la siguiente:

	Original rows	Intervals rows
Stock	560.089.291	12.236.634
Sales	22.981.825	16.794.972

Un saludo!

Tooling en SQL

No es ningún secreto que la programación con SQL no dispone de un tooling a su altura. Si bien es cierto que tenemos una excelente herramienta como SSMS, por el contrario, si estás acostumbrado al desarrollo en C#, javascript o cualquier otro lenguaje, tarde o temprano querrás imitar el escenario al que estás acostumbrado, esto es, linting, guías de estilo de código, integración continua, etc.

Lógicamente, si usamos SQL sólo de forma esporádica o siempre embebido en C#, el asunto sería distinto, pero si te toca lidiar con un montón de procedimientos almacenados, funciones y demás objetos de SQL Server, en mi opinión se le empiezan a ver las costuras al flujo de desarrollo.

Con sinceridad, tampoco es que la solución propuesta aquí sea la panacea, pero es un intento de poner algo de orden y evitar que lo inevitable suceda. Además, personalmente descarto (por ahora) el uso de herramientas profesionales como redgate, ApexSQL o devart, pero por otro lado, son una incuestionable fuente de inspiración para intentar copiar o imitar (que suena mejor) algunas funcionalidades que pueden ser muy útiles.

El propósito del post será llegar a tener un linter, un formateador y un git hook, todo ello en el contexto de código T-SQL.

Para el linter (y habiendo descartado SonarQube, porque el analizador de T-SQL es de pago) nos quedan pocas opciones. Lo mejor que hemos encontrado es una extensión de VSCode llamada tsqlint. Dentro de VSCode funciona muy bien, aunque la única pega que le pongo es que el fichero de configuración donde activamos o desactivamos reglas (.tsqllintrc) tiene que estar en %USERPROFILE%, eso hace un poco más difícil el “clonar el repo y listo”, pero bueno, no habiendo más oferta, doy las gracias por esta herramienta.

Aunque tengamos el linter en VSCode, parece mejor opción instalar globalmente el paquete vía npm con npm install tsqllint -g. Ahora podemos crear el fichero .tsqllintrc con tsqllint --init. Y lo más importante, ahora podemos usar el linter desde línea de comandos (lo que abre la puerta a integración continua).

Para los ejemplos voy a usar pubs (que aunque me dicen es antigua, es sencilla y hasta un niño la entiende).

Probemos con una instrucción sencilla:

SELECT j.job_id,
    j.job_desc,
    e.emp_id,
    e.fname,
    e.lname,
    e.job_id,
    e.hire_date
FROM jobs j
INNER JOIN employee e ON j.job_id = e.job_id
ORDER BY j.job_desc,
    e.fname,
    e.lname

Y al pasar el linter

C:\Temp\test>tsqllint example.sql
example.sql(8,7): error schema-qualify : Object name not schema qualified.
example.sql(9,13): error schema-qualify : Object name not schema qualified.
example.sql(12,16): warning semicolon-termination : Statement not terminated with semicolon.

Linted 1 files in 0,2461205 seconds

2 Errors.
1 Warnings

tssqlint permite crear plugins de una forma sencilla y además usa C#, así que no hay excusa.

Nuestro plugin lo que hará es buscar la palabra clave UNION y luego ya darlo como warning, error o no reportarlo, según queramos, eso ya es configuración de cada uno.

El código del plugin (lo relevante) es este:

    public class MyTSqlLintUnionPlugin : IPlugin
    {
        public void PerformAction(IPluginContext context, IReporter reporter)
        {
            string line;
            var lineNumber = 0;

            var reader = new StreamReader(File.OpenRead(context.FilePath));

            while ((line = reader.ReadLine()) != null)
            {
                lineNumber++;
                var regex = new Regex(@"\s*UNION\s*", RegexOptions.IgnoreCase);
                var match = regex.Match(line);
                if (match.Success)
                {
                    var column = match.Index;
                    reporter.ReportViolation(new RuleViolation(
                        context.FilePath,
                        "union",
                        "UNION is forbidden",
                        lineNumber,
                        column,
                        RuleViolationSeverity.Warning));
                }
            }
        }
    }

Si ahora pasamos el linter a este SQL, ¡tenemos un UNION no permitido!

SELECT 1
UNION
SELECT 2;

C:\Temp\test>tsqllint sergio.sql
Loaded plugin: 'MyTSqlLintPlugin.MyTSqlLintUnionPlugin', Version: '1.0.0.0'
sergio.sql(2,0): warning union : UNION is forbidden.

Linted 1 files in 0,2557418 seconds

0 Errors.
1 Warnings

Visto esto, el único pero que le saco a la extensibilidad (o a mi implementación, mejor dicho) es el uso de expresiones regulares, lo mismo hoy son una solución, pero ya se sabe que mañana…

En cualquier caso, aceptamos barco, y además integrar tsqllint en SSMS también es posible como una herramienta externa.

Llegados aquí, ¡tenemos linter!, pero si queremos ir un paso más allá y en vez de trabajar con expresiones regulares, usar el parser que usa el propio SQL Server (para por ejemplo, dar un warning en un DELETE sin WHERE, que es una de las reglas de SonarQube, por cierto) podemos hacerlo con Microsoft.SqlServer.DacFx.x64.

var parser = new TSql140Parser(true);
            using (var reader = new StringReader(@"
SELECT * FROM authors;
DELETE jobs --WHERE min_lvl > 10
;"))
            {
                var result = parser.Parse(reader, out var errors) as TSqlScript;
                foreach (TSqlBatch batch in result.Batches)
                {
                    foreach (var statement in batch.Statements.OfType<DeleteStatement>())
                    {
                        if (statement.DeleteSpecification.WhereClause == null)
                        {
                            Console.WriteLine("¡DELETE sin WHERE, insensato!");
                        }
                        break;
                    }
                }
            }

Si probamos a habilitar o deshabilitar el comentario con la condición del DELETE vemos que funciona. Lógicamente, habría que hacer una herramienta de línea de comandos con un código de salida para poder usarla en la build, pero por ahora con saber que se puede hacer (y como un señor con un parser potente) parece suficiente y posibilita un futuro lleno de oportunidades.

En cuanto al formateo de SQL, es decir, una guía de estilo, tampoco hay mucho donde elegir. Si ves lo que tiene por ejemplo devart, https://sql-format.com/, es flipante, sin más, por eso viven de ello. Pero alternativas open-source no hay muchas, o al menos no hay muchas que admitan cierto grado de configuración. En nuestro caso, no hemos decantado por http://poorsql.com/, tiene muy buena pinta, pero la verdad es que el proyecto parece un poco abandonado. No obstante, es lo mejor que hemos encontrado y no parece que formatear SQL sea algo que esté cambiando todos los días.

Cabe mencionar que hemos descartado el formateo que hace el plugin oficial de SQL Server en VSCode, por eso, porque sólo lo hace en VSCode. Tiene competencia con este otro, pero ninguno es invocable por línea de comandos (o al menos yo no sé).

Con el tema del formateo tengo la sensación de que vamos a tener que contentarnos con lo que haya y no pedir peras al olmo.

Usando poorsql pasaríamos de esto (escrito por una persona con muy mala baba, la verdad sea dicha).

IF (1 =1    ) BEGIN
 SELECT j.job_id, j.job_desc,
  e.emp_id,  e.fname,
  e.lname,e.job_id,
  e.hire_date
 FROM jobs j  INNER 
 JOIN employee e ON 
 j.job_id = e.job_id
 ORDER BY j.job_desc,
    e.fname, e.lname END

a esto otro

IF (1 = 1)
BEGIN
 SELECT j.job_id,
  j.job_desc,
  e.emp_id,
  e.fname,
  e.lname,
  e.job_id,
  e.hire_date
 FROM jobs j
 INNER JOIN employee e ON j.job_id = e.job_id
 ORDER BY j.job_desc,
  e.fname,
  e.lname
END

Aunque podríamos integrarlo como comando en VSCode y en SSMS como herramienta externa, para finalmente poner todo en orden, usaremos node y los scripts de npm (que además sería equivalente a lo que sucedería en el servidor de integración continua).

Para el git hook de pre-commit vamos a usar husky.

"husky": {
    "hooks": {
      "pre-commit": "node index.js ./**/*.sql"
    }
  }

Lo que pasará ahora es que cada vez que hagamos un commit, se va a ejecutar el fichero index.js con un glob pattern para todos los ficheros .sql.

index.js es el encargado de ver en que ficheros .sql ha habido cambios y están en zona de staging, para entonces pasar el linter y el formateador a cada uno de ellos y si algo falla, abortar el commit.

const { execSync } = require("child_process");
const glob = require("glob");

// 0: C:\Program Files\nodejs\node.exe
// 1: C:\Temp\test\index.js
var args = process.argv.slice(2);
args.forEach(function(arg) {
  const files = getFiles(arg);
  files.forEach(function(file) {
    if (!formatFile(file)) {
      process.exit(1);
    }
    if (!lintFile(file)) {
      process.exit(1);
    }
    stageFile(file);
  });
});

function getFiles(pattern) {
  var files = glob.sync(pattern);
  var stagedFiles = getStagedFiles();
  return files.filter(file => stagedFiles.includes(file));
}

function formatFile(file) {
  console.log(`formatting ${file}`);
  return executeCommand(`npm run sqlformat -- -f ${file} -g ${file}`);
}

function lintFile(file) {
  console.log(`linting ${file}`);
  return executeCommand(`npm run tsqllint -- ${file}`);
}

function stageFile(file) {
  console.log(`adding ${file} to index`);
  return executeCommand(`git add ${file}`);
}

function executeCommand(command) {
  try {
    execSync(command);
    return true;
  } catch (error) {
    console.log(`stderr ${error.stdout.toString()}`);
    return false;
  }
}

function getStagedFiles() {
  var output = execSync(
    `git diff --cached --name-only --diff-filter=ADMR`
  ).toString();
  return output;
}

La verdad es que explicado por escrito parecen muchas operaciones a seguir, pero bien montado puede suponer una pequeña mejora en el flujo de trabajo con SQL (aquí tienes el código subido en github).En cualquier caso, me frustra que algo tan transversal como SQL no tenga (al menos yo no he encontrado nada) otros mecanismos para poder mirar de igual a igual a otras tecnologías en cuanto a tooling se refiere.

Gestión de errores en SQL Server

Si te toca escribir “algo” de lógica de negocio en T-SQL, y te agobia la gestión de errores (como a mi), espero que después de este post tengamos los 2 las cosas un poco más claras.

La primera opción que para tratar errores es con el estilo old-school, es decir, con @@ERROR, que devuelve un número de error si la última sentencia T-SQL ejecutada dio algún error, devolverá 0 si no hubo ningún error.

Para todos los ejemplos vamos a usar una tabla con una sola columna.

    CREATE TABLE Table1 (Id INT PRIMARY KEY)
    

Usando @@ERROR

    DELETE FROM Table1;
    GO
    INSERT INTO Table1 VALUES (1);
    INSERT INTO Table1 VALUES (1);
    IF @@ERROR <> 0
        PRINT 'There was an error';
    INSERT INTO Table1 VALUES (2);
    GO
    SELECT COUNT(*) FROM Table1;
    

Lo más relevante de este código es que, finalmente, la tabla tiene 2 registros, es decir, a pesar del error de la línea 4, el resto del script se ha seguido ejecutando. Este comportamiento de seguir ejecutando el script es el predeterminado, pero ¿qué pasa si no quiero que sea así? Pues podemos usar XACT_ABORT, además @@ERROR no parece una técnica muy segura.

Si XACT_ABORT es ON, en caso de haber un error, se acaba la ejecución inmediatamente del lote y se revierte, si la hubiera, la transacción explícita. Si es OFF, el valor predeterminado, pues funciona como el anterior script, la ejecución sigue y no se revierte automáticamente ninguna transacción explícita (sólo la implícita que es la propia sentencia).

        SET XACT_ABORT ON;
        DELETE FROM Table1;
        GO
        INSERT INTO Table1 VALUES (1);
        INSERT INTO Table1 VALUES (1);
        INSERT INTO Table1 VALUES (2);
        GO
        SELECT COUNT(*) FROM Table1;    
        

Es decir, la instrucción 6 no se ejecuta porque se aborta la ejecución del lote, por eso finalmente, sólo hay 1 registro en la tabla destino.

Antes de ver como XACT_ABORT ON revierte automáticamente una transacción explícita, es importante conocer la función XACT_STATE. Esta función nos devuelve un valor que indica si hay o no una transacción explícita y en que estado está.

• 1. Hay transacción.
• 0. No hay transacción.
• -1. Hay transacción, pero un error hizo que la transacción no se pueda confirmar. La única operación válida es deshacer toda la transacción.

Las diferencias entre @@TRANCOUNT y XACT_STATE es que @@TRANCOUNT permite saber si hay transacciones anidadas y XACT_STATE permite saber si la transacción es confirmable.

        SET XACT_ABORT ON;
        GO
        DELETE FROM Table1;
        GO
        BEGIN TRAN;
        INSERT INTO Table1 VALUES (1);
        INSERT INTO Table1 VALUES (1);
        INSERT INTO Table1 VALUES (2);
        COMMIT TRAN;
        GO
        PRINT XACT_STATE();
        PRINT @@TRANCOUNT;
        IF XACT_STATE() = 1
            COMMIT TRAN;
        IF XACT_STATE() = -1
            ROLLBACK TRAN;
        GO
        SELECT * FROM Table1;    
        

Como la línea 7 da un error y XACT_ABORT es ON, pasa lo siguiente:

• Se aborta la ejecución del lote, no se ejecuta la línea 8.
• Automáticamente se revierte la transacción explícita. Luego XACT_STATE y @@TRANCOUNT pasan a valer 0.

Si comentáramos la línea 7, XACT_STATE valdría 1 y se ejecutaría COMMIT TRAN.

La línea 16 hace ROLLBACK TRAN si por algún motivo la transacción se volvió no confirmable.

De nuevo, antes de seguir es necesario entender otro concepto, como maneja los timeouts de cliente SQL Server. Un timeout de cliente es como si pulsáramos “Cancel Executing Query” en SSMS, el botón Stop, vamos. Por ejemplo, creamos un procedimiento almacenado como el siguiente:

        CREATE PROCEDURE Foo
        AS
        PRINT 'Sergio';
        WAITFOR DELAY '00:00:10';
        PRINT 'panicoenlaxbox';
        END    
        

Si lo ejecutamos y antes de que pasen 10 segundos pulsamos Stop, sólo veremos la salida 'Sergio', es decir, se deja de ejecutar el script y no vemos 'panicoenlaxbox'.

¿Desde una aplicación cliente funcionará igual?

Lo primero es poder ver PRINT en el SQL Server Profiler, esto no es necesario para comprobar esto, pero me parece útil poder ver PRINT en el Profiler, lo he sacado de aquí. Creamos el procedimiento almacenado que hace la magia:

        CREATE PROCEDURE PrintTrace1
        @Text nvarchar(max) 
        AS
        BEGIN
        DECLARE @UserData BINARY(8000) = 0
        DECLARE @UserInfo NVARCHAR(256) = SUBSTRING(@Text,1,256)
        PRINT   @Text
        EXEC sp_trace_generateevent 82, @UserInfo, @UserData
        END    
        

Y modificamos el anterior procedimiento para lo use:

    ALTER PROCEDURE Foo
    AS
    EXEC PrintTrace1 'Sergio';
    WAITFOR DELAY '00:00:10';
    EXEC PrintTrace1 'panicoenlaxbox';    
    

Por último, cuando abramos el Profiler será necesario marcar el evento UserConfigurable:0 para ver la salida de PrintTrace1.

Ahora nuestro código cliente:

    using System;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace ConsoleApp1
    {
        class Program
        {
            static void Main(string[] args)
            {
                try
                {
                    using (var connection = new SqlConnection(@"Server=(LocalDB)\MSSQLLocalDB;Database=Sergio;Trusted_Connection=True;"))
                    {
                        connection.Open();
                        using (var command = connection.CreateCommand())
                        {
                            command.CommandTimeout = 5;
                            command.CommandType = CommandType.StoredProcedure;
                            command.CommandText = "Foo";
                            command.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);                
                }
                Console.ReadKey();
            }
        }
    }    
    

Con CommandTimeout 5 y WAITFOR DELAY '00:00:10' el timeout está garantizado, devolviendo el error típico al cliente Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding, y confirmando que igualmente deja de ejecutar el resto de script en el servidor:

¿Y todo esto del timeout por qué? Pues porque ahora vamos a ver TRY…CATCH de SQL Server, manejado estructurado de errores, buena cosa, pero era necesario tener los anteriores conceptos claro para poder hablar sobre ellos.

De TRY…CATCH el ejemplo típico es el siguiente, donde al igual que pasaba con XACT_ABORT ON, ahora cuando se sucede un error dentro del bloque TRY, la ejecución no continua, sino que salta al bloque CATCH.

        DELETE FROM Table1;
        GO
        BEGIN TRY
            BEGIN TRAN;
                INSERT INTO Table1 VALUES (1);
                -- RAISERROR with severity 11-19 will cause execution to jump to the CATCH block.  
                --RAISERROR ('Error raised in TRY block.', -- Message text.  
                --		   16, -- Severity.  
                --		   1 -- State.  
                --		   ); 
                INSERT INTO Table1 VALUES (1);
                INSERT INTO Table1 VALUES (2);
            COMMIT TRAN;
        END TRY
        BEGIN CATCH
                SELECT
                ERROR_NUMBER() AS ErrorNumber
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_PROCEDURE() AS ErrorProcedure
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_MESSAGE() AS ErrorMessage;
            IF XACT_STATE() <> 0 
            BEGIN
                PRINT 'ROLLBACK TRAN';
                ROLLBACK TRAN;
            END
        END CATCH
        GO
        SELECT * FROM Table1;   
        

¿Cómo se llevará TRY…CATCH con un timeout?

Si hay un error de timeout no se ejecutará el CATCH, es decir, no creas que siempre que si hay un TRY…CATCH el CATCH siempre está asegurado.

Modificando el procedimiento anterior y cancelando la ejecución desde SSMS vemos este comportamiento.

    ALTER PROCEDURE Foo
    AS
        DELETE FROM Table1;
        BEGIN TRY
            BEGIN TRAN;
                INSERT INTO Table1 VALUES (1);
                EXEC PrintTrace1 'waitfor...';			
                WAITFOR DELAY '00:00:10';
                EXEC PrintTrace1 'continue...';
                INSERT INTO Table1 VALUES (1);
                INSERT INTO Table1 VALUES (2);
            COMMIT TRAN;
        END TRY
        BEGIN CATCH
            EXEC PrintTrace1 'catch...';
            IF XACT_STATE() <> 0 
            BEGIN
                PRINT 'ROLLBACK TRAN';
                ROLLBACK TRAN;
            END
        END CATCH 
    

Y ahora la pregunta es: Si no puedo garantizar la ejecución del bloque CATCH, ¿debería activar siempre XACT_ABORT para garantizar que la transacción explícita siempre se rechazara automáticamente? Pues parece que sí, porque quien defiende XACT_ABORT lo hace porque si no está activo, un timeout de cliente podría dejar la conexión con recursos bloqueados hasta que la transacción se cancele o la conexión se cierre, y asumiendo que hay pool de conexiones, un mal código de cliente podría arruinar el servidor, incluso en un comentario del mismo post queda clara la jugada.

Por otro lado, si activamos XACT_ABORT y además hay un TRY…CATCH, el CATCH seguirá ejecutándose, pero XACT_STATE valdrá -1 (la única operación válida es deshacer la transacción) y además @@TRANCOUNT seguirá valiendo lo que valía, es decir, un -1 en XACT_STATE no rechaza automáticamente la transacción explícita.

Y antes de llegar a nuestro snippet definitivo para la gestión de errores, hablemos de transacciones anidadas en SQL Server. Poder se puede:

        BEGIN TRAN
            BEGIN TRAN
            PRINT @@TRANCOUNT --2
            COMMIT TRAN
            PRINT @@TRANCOUNT --1
        COMMIT TRAN
        PRINT @@TRANCOUNT --0    
        

Además, aparece el concepto de salvar una transacción que lo que permite es deshacer partes concretas de una transacción. Cabe mencionar que SAVE TRAN no incrementa @@TRANCOUNT y, por ende, ROLLBACK TRAN <nombre> tampoco lo decrementa.

        BEGIN TRAN
            BEGIN TRAN
            PRINT @@TRANCOUNT --2
            SAVE TRAN st1
                --Do something that can be rolled back
                PRINT @@TRANCOUNT --2
                ROLLBACK TRAN st1
            COMMIT TRAN
            PRINT @@TRANCOUNT --1
        COMMIT TRAN
        PRINT @@TRANCOUNT --0        
        

Un ROLLBACK TRAN (sin nombre) deshace todas las transacciones (anidadas también si las hubiera) y decrementa @@TRANCOUNT a 0. ROLLBACK TRAN <nombre> sólo es válido si <nombre> es un SAVE TRAN o un BEGIN TRAN <nombre> siendo esa transacción la más externa (no siendo anidada).

Un COMMIT TRAN (sin nombre) confirma la transacción actual según su nivel de indentación, aunque es válido un COMMIT TRAN <nombre> refiriéndose tanto a una transacción anidada como a una externa.

Como resumen, con transacciones anidadas podemos o bien rechazar todas las transacciones (ROLLBACK TRAN o ROLLBACK TRAN <nombre_de_la_más_externa>) o bien rechazar partes de una transacción anidada (SAVE TRAN <nombre> y ROLLBACK <nombre>).

Y en este momento, es cuando vemos la plantilla de un procedimiento almacenado que he sacado de este post donde le agregamos XACT_ABORT ON para que un timeout de cliente no nos de guerra.

        CREATE PROCEDURE [ProcedureName]
        AS
        BEGIN
            SET NOCOUNT ON;
            SET XACT_ABORT ON;
            DECLARE @trancount INT;
            SET @trancount = @@TRANCOUNT;
            BEGIN TRY
                IF @trancount = 0
                    BEGIN TRANSACTION;
                ELSE
                    SAVE TRANSACTION ProcedureName;
                -- Do something...	
                
                IF @trancount = 0	
                    COMMIT;
            END TRY
            BEGIN CATCH
                DECLARE @errorNumber INT, @message NVARCHAR(4000), @xact_state INT;
                SELECT @errorNumber = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xact_state = XACT_STATE();
                IF @xact_state = -1
                    ROLLBACK;
                IF @xact_state = 1 AND @trancount = 0
                    ROLLBACK;
                IF @xact_state = 1 AND @trancount > 0
                    ROLLBACK TRANSACTION ProcedureName;
        
                RAISERROR('ProcedureName: %d: %s', 16, 1, @errorNumber, @message) ;
            END CATCH
        END   
        

Ahora sí, podemos escribir un “poco” de lógica de negocio en T-SQL con una estrategia clara de gestión de errores.

JSON en SQL Server 2016

Seguro que ya estabas enterado, pero en mi caso ha sido recientemente cuando he descubierto que a partir de SQL Server 2016 se puede trabajar con JSON.

Aunque hay una excelente documentación al respecto en JSON Data (SQL Server), de una forma resumida y con ejemplos que pueda recordar fácilmente, me gustaría contarte que posibilidades tenemos para trabajar con JSON en SQL Server.

Lo primero es que no hay un tipo json, en realidad trabajaremos con nvarchar y todo la magia ocurrirá a través de nuevas clausulas y funciones.

Para organizar el post, voy a plantear un escenario donde, primero importaremos datos desde un fichero .json, a continuación formatearemos en JSON la salida de una consulta SQL, para después hacer consultas SQL sobre una columna que guarda JSON y acabar, finalmente, con un consejo sobre índices para mejorar el rendimiento.

El script SQL necesario para todas las pruebas es el siguiente:

CREATE TABLE [dbo].[OrderLines](
	[Id] [int] NOT NULL,
	[Units] [int] NOT NULL,
	[Price] [decimal](18, 2) NOT NULL,
	[ProductId] [int] NOT NULL,
	[OrderId] [int] NOT NULL
 CONSTRAINT [PK_dbo.OrderLines] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))
GO
CREATE TABLE [dbo].[Orders](
	[Id] [int] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[Comment] [nvarchar](250) NULL
 CONSTRAINT [PK_dbo.Orders] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))
GO
CREATE TABLE [dbo].[Products](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](250) NULL
 CONSTRAINT [PK_dbo.Products] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))
GO
ALTER TABLE [dbo].[OrderLines]  WITH CHECK ADD  CONSTRAINT [FK_dbo.OrderLines_dbo.Orders_OrderId] FOREIGN KEY([OrderId])
REFERENCES [dbo].[Orders] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[OrderLines] CHECK CONSTRAINT [FK_dbo.OrderLines_dbo.Orders_OrderId]
GO
ALTER TABLE [dbo].[OrderLines]  WITH CHECK ADD  CONSTRAINT [FK_dbo.OrderLines_dbo.Products_ProductId] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Products] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[OrderLines] CHECK CONSTRAINT [FK_dbo.OrderLines_dbo.Products_ProductId]

Para importar datos desde un fichero .json podemos usar la función OPENROWSET con el parámetro SINGLE_CLOB, que lee y devuelve su contenido como una única fila y columna BulkColumn de tipo nvarchar(max). Además, tendremos que usar la función OPENJSON que convierte JSON en filas y columnas.

Nuestro fichero .json será como sigue

[
  {
    "Id": 1,
    "CreatedDate": "2017-05-29T00:00:00.000",
    "OrderLines": [
      {
        "Id": 1,
        "Units": 1,
        "Price": 1.25,
        "Product": {
          "Id": 1,
          "Name": "Product 1"
        }
      },
      {
        "Id": 2,
        "Units": 2,
        "Price": 2.5,
        "Product": {
          "Id": 2,
          "Name": "Product 2"
        }
      }
    ]
  },
  {
    "Id": 2,
    "CreatedDate": "2017-05-29T00:00:00.000",
    "Comment": "A brief but useful comment",
    "OrderLines": [
      {
        "Id": 3,
        "Units": 3,
        "Price": 3.75,
        "Product": {
          "Id": 1,
          "Name": "Product 1"
        }
      }
    ]
  }
]

Ahora podemos usar OPENROWSET y OPENJSON

SELECT BulkColumn, [key], [value], [type]
FROM OPENROWSET (BULK 'C:\panicoenlaxbox\data.json', SINGLE_CLOB) AS T
CROSS APPLY OPENJSON(BulkColumn)

OPENROWSET devuelve la columna BulkColumn, OPENJSON devuelve las columnas key, value y type.

Si en fichero .json tuviera un sólo objeto en vez de un array, la salida nos ayudaría a entender mejor como funciona OPENJSON

{
  "Id": 1,
  "CreatedDate": "2017-05-29T00:00:00.000",
  "OrderLines": [
    {
      "Id": 1,
      "Units": 1,
      "Price": 1.25,
      "Product": {
        "Id": 1,
        "Name": "Product 1"
      }
    },
    {
      "Id": 2,
      "Units": 2,
      "Price": 2.5,
      "Product": {
        "Id": 2,
        "Name": "Product 2"
      }
    }
  ]
}

OPENJSON tiene la clausula WITH con la que podemos, de forma explícita, establecer la estructura del resultado devuelto. Por ejemplo, para conseguir un conjunto de filas y columnas sobre la que poder trabajar directamente, ejecutaríamos la siguiente consulta

SELECT
	Orders.Id AS OrderId
   ,Orders.CreatedDate
   ,Orders.Comment
   ,OrderLines.Id AS OrderLineId
   ,OrderLines.Units
   ,OrderLines.Price
   ,Product.Id AS ProductId
   ,Product.[Name] AS ProductName
INTO #Table1
FROM OPENROWSET(BULK 'C:\panicoenlaxbox\data.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn)
WITH (
	Id INT,
	CreatedDate DATETIME '$.CreatedDate',
	Comment NVARCHAR(MAX),
	OrderLines NVARCHAR(MAX) AS JSON
) AS Orders
CROSS APPLY OPENJSON(Orders.OrderLines)
WITH (
	Id INT,
	Units INT,
	Price DECIMAL(18, 2),
	Product NVARCHAR(MAX) AS JSON
) AS OrderLines
CROSS APPLY OPENJSON(OrderLines.Product)
WITH (
	Id INT,
	[Name] NVARCHAR(MAX)
) AS Product    

Ahora ya sí podemos insertar estos datos leídos del fichero .json en nuestras tablas

INSERT INTO Products SELECT DISTINCT ProductId, ProductName FROM #table1;
INSERT INTO Orders SELECT DISTINCT OrderId, CreatedDate, Comment FROM #table1;
INSERT INTO OrderLines SELECT DISTINCT OrderLineId, Units, Price, ProductId, OrderId FROM #table1;    

Si hablamos ahora de formatear consultas, tendremos que usar la clausula FOR JSON

SELECT * FROM Orders O
INNER JOIN OrderLines OL ON OL.OrderId = O.Id
INNER JOIN Products P ON OL.ProductId = P.Id
FOR JSON AUTO

Que devuelve

    [{
    "Id": 1,
    "CreatedDate": "2017-05-29T00:00:00",
    "OL": [{
        "Id": 1,
        "Units": 1,
        "Price": 1.25,
        "ProductId": 1,
        "OrderId": 1,
        "P": [{
            "Id": 1,
            "Name": "Product 1"
        }]
    }, {
        "Id": 2,
        "Units": 2,
        "Price": 2.50,
        "ProductId": 2,
        "OrderId": 1,
        "P": [{
            "Id": 2,
            "Name": "Product 2"
        }]
    }]
}, {
    "Id": 2,
    "CreatedDate": "2017-05-29T00:00:00",
    "Comment": "A brief but useful comment",
    "OL": [{
        "Id": 3,
        "Units": 3,
        "Price": 3.75,
        "ProductId": 1,
        "OrderId": 2,
        "P": [{
            "Id": 1,
            "Name": "Product 1"
        }]
    }]
}]

Como probablemente este resultado no nos satisfaga, tendremos que tomar el control con FOR JSON PATH. Por ejemplo, la siguiente consulta devuelve exactamente lo mismo que tiene el fichero .json que usamos al comienzo para importar los datos

SELECT
	O.Id
   ,O.CreatedDate
   ,O.Comment
   ,(SELECT
			OL.Id
		   ,OL.Units
		   ,OL.Price
		   ,OL.ProductId AS 'Product.Id'
		   ,P.[Name] AS 'Product.Name'
		FROM OrderLines OL
		INNER JOIN Products P
			ON OL.ProductId = P.Id
		WHERE OL.OrderId = O.Id
		FOR JSON PATH)
	AS OrderLines
FROM Orders O
FOR JSON PATH

En cuanto a que podemos hacer para consultar datos JSON almacenados en una columna, encontramos varias funciones:

• ISJSON
• JSON_VALUE
• JSON_QUERY
• JSON_MODIFY

ISJSON valida que el texto es JSON válido

JSON_VALUE extrae un valor desde JSON

JSON_QUERY extrae como texto, un objeto u array desde JSON

JSON_MODIFY permite modificar JSON y devuelve el resultado

Lo más sencillo será agregar una nueva columna para poder jugar con ella

ALTER TABLE Orders
ADD SecurityContext NVARCHAR(MAX);
GO
UPDATE Orders SET SecurityContext = '{"Enabled":true,"Roles":["Salesman","Customer"],"Worflows":[{"Name":"Approval","Priority":1},{"Name":"Rejection","Priority":2}]}'
WHERE Id = 1;
UPDATE Orders SET SecurityContext = '{"Enabled":false}'
WHERE Id = 2;    

Ahora podemos ejecutar las siguientes consultas

--ISJSON valida si es JSON
SELECT ISJSON(SecurityContext) FROM Orders;

--JSON_VALUE extrae un valor desde JSON
--'$.Roles[1]' y 'lax $.Roles[1]' son lo mismo, por defecto es lax
SELECT JSON_VALUE(SecurityContext, '$.Roles[1]') FROM Orders;

--Con strict tendremos una excepción porque el segundo registro no tiene valor
--Property cannot be found on the specified JSON path.
--SELECT JSON_VALUE(SecurityContext, 'strict $.Roles[1]') FROM Orders;

--JSON_QUERY devuelve un objeto o un array
SELECT JSON_QUERY(SecurityContext, '$.Worflows') FROM Orders;
--[{"Name":"Approval","Priority":1},{"Name":"Rejection","Priority":2}]

DECLARE @json NVARCHAR(MAX)
SELECT @json = SecurityContext FROM Orders WHERE Id = 1;
--Modificar una propiedad
SELECT JSON_VALUE(JSON_MODIFY(@json, '$.Enabled', 'false'), '$.Enabled');
--Modificar un elemento de un array
SELECT JSON_QUERY(JSON_MODIFY(@json, '$.Roles[1]', 'Administrador'), '$.Roles');
--["Salesman","Administrador"]

--Agregar un elemento a un array
SELECT JSON_QUERY(JSON_MODIFY(@json, 'append $.Roles', 'Agent'), '$.Roles');
--["Salesman","Customer","Agent"]

Por último, sólo mencionar como podemos crear un índice para que JSON_VALUE lo use. Primero veremos el plan de ejecución de una consulta sin el índice y después como cambia cuando lo incluimos

SELECT JSON_VALUE(SecurityContext, '$.Enabled') FROM Orders 
WHERE JSON_VALUE(SecurityContext, '$.Enabled') = 'true'

Ahora agregamos el índice (bueno, en realidad agregamos una columna virtual y después el índice) y ejecutamos de nuevo la consulta para confirmar que lo está usando

ALTER TABLE Orders
ADD SecurityContextEnabled AS JSON_VALUE(SecurityContext,'$.Enabled')
GO
CREATE INDEX IX_Orders_Enabled
ON Orders(SecurityContextEnabled)  
GO
SELECT JSON_VALUE(SecurityContext, '$.Enabled') FROM Orders 
WHERE JSON_VALUE(SecurityContext, '$.Enabled') = 'true'

Un saludo!

Restaurar y renombrar una base de datos

Restaurar una base de datos desde una copia de seguridad es una práctica habitual pero que, puede resultar en alguna descoordinación entre los nombres de las bases de datos, ficheros físicos y ficheros lógicos que, a posteriori, seguro nos generará cierta frustración por no tener todo a nivel.

Por ello, escribir como hacerlo bien en MS SQL Server (o cómo hacerlo como a mí me funciona), parece una buena copia de seguridad (valga la redundancia) de cómo llevar a cabo el proceso.

Con la siguiente instrucción, podemos ver los nombres lógicos y la ubicación de los ficheros físicos que hay dentro de un backup y que nos servirán después para la instrucción RESTORE.

RESTORE FILELISTONLY FROM DISK = 'C:\Users\sergio.leon\Sergio.bak'

Ahora es momento de restaurar la base de datos (los nombres lógicos ‘Sergio’ y ‘Sergio_log’ es información que nos dio la anterior instrucción):

RESTORE DATABASE [panicoenlaxbox]
FROM DISK = 'C:\Users\sergio.leon\Sergio.bak'
WITH REPLACE,
MOVE 'Sergio' TO 'C:\Users\sergio.leon\panicoenlaxbox.mdf',
MOVE 'Sergio_log' TO 'C:\Users\sergio.leon\panicoenlaxbox_log.ldf'

Sin mover los ficheros, el nombre de los mismos sería el que estuviera asociado en el fichero .bak, que serían Sergio.mdf y Sergio_log.ldf. Además, de no coincidir los nombres de los ficheros con el nombre de la base de datos, podría ser (como en el caso de este ejemplo) que Sergio.mdf y Sergio_log.ldf ya existieran, luego es importante especificar la ubicación y nombre de los ficheros con MOVE.

En este punto, el único problema es que el nombre lógico de los ficheros de la base de datos ‘panicoenlaxbox’ no coincide con el nombre de los ficheros físicos. Para cambiar el nombre lógico:

ALTER DATABASE [panicoenlaxbox] MODIFY FILE (NAME=N'Sergio', NEWNAME=N'panicoenlaxbox')
ALTER DATABASE [panicoenlaxbox] MODIFY FILE (NAME=N'Sergio_log', NEWNAME=N'panicoenlaxbox_log')

Otro problema común (al menos en mi caso) es tener que renombrar una base de datos.

Con el siguiente comando podemos hacerlo, pero el problema es que sólo se renombra la base de datos, no así los ficheros físicos ni los nombres lógicos.

sp_renamedb 'Sergio', 'SergioLeon'

Si hay conexiones abiertas no podrás hacer esto, con el siguiente script matarás estas conexiones:

USE [master];
GO

DECLARE @database SYSNAME = 'Sergio'
DECLARE @kill NVARCHAR(MAX) = '';

SELECT
    @kill = @kill + 'kill ' + CONVERT(VARCHAR(5), session_id) + ';'
FROM
    sys.dm_exec_sessions
WHERE
    database_id = DB_ID(@database);

--PRINT @kill
EXEC sys.sp_executesql @kill;

Para los nombres lógicos ya hemos visto como renombrarlos, pero ¿cómo hacerlo con los ficheros físicos? Pues básicamente, hacer un detach de la base de datos, cambiar el nombre de los ficheros y hacer un attach.

sp_detach_db 'SergioLeon'
--Renombrar los ficheros manualmente en disco
CREATE DATABASE [SergioLeon] ON 
( FILENAME = N'C:\Users\sergio.leon\SergioLeon.mdf' ),
( FILENAME = N'C:\Users\sergio.leon\SergioLeon_log.ldf' )
FOR ATTACH

Un saludo!

Basado en hechos reales: Cómo proteger tu SQL Server en Internet.

Recientemente hemos sufrido un ataque informático contra nuestra base de datos SQL Server 2008 R2 expuesta en Internet.

Por casualidad nos pusimos a mirar el log de errores y descubrimos que había más de 2 millones y medio de intentos de inicio de sesión erróneos del usuario sa desde hace más de 2 meses.

El cómo no nos dimos cuenta en todo ese tiempo de que estábamos siendo atacados por fuerza bruta contra el usuario sa daría para otro post, pero ahora lo más importante es intentar incrementar drásticamente la seguridad de nuestro servidor SQL Server en Internet y dejar de buscar culpables… sobre todo porque yo mismo soy el principal culpable!

“Cabe resaltar que este post asume un escenario con un servidor VPS donde se tienen plenos derechos de administración accediendo por escritorio remoto, es decir, no estoy hablando de Windows Azure ni de un hosting compartido”

La medida más efectiva para evitar ataques contra tu servidor SQL Server en Internet es NO exponerlo a Internet. Esto parece de perogrullo, pero a veces no es tan obvio. ¿Por qué expones tu servidor SQL Server en Internet? Normalmente será porque los desarrolladores argumentan que necesitan acceder a la base de datos para consultar datos, realizar cambios, etc. Yo creo que los desarrolladores (entre los que yo me incluyo) deberíamos pensar que un sistema en producción no es nuestra banco de pruebas personal y que si realmente necesitamos acceder a los datos del servidor hay otras herramientas para darnos acceso sin comprometer la seguridad del servidor. Por ejemplo, escritorio remoto, una VPN, etc.

La verdad es que para un sistema SQL Server en producción no se me ocurre ninguna buena razón para exponerlo directamente en Internet.

Antes de ver que medidas tomamos para garantizar la seguridad de nuestro servidor, la pregunta es ¿Cómo me doy cuenta de que estoy siendo atacado? Pues mirando el log de errores de SQL Server. El log de errores de SQL Server lo podemos consultar de 2 formas distintas:

• De forma manual, accediendo a Administración > Registros de SQL Server
• A través de T-SQL, con el comando xp_ReadErrorLog

En cualquiera de los 2 casos podrás ver una información como la siguiente (aquí optamos por el comando xp_ReadErrorLog que es más directo)

xp_ReadErrorLog 0, 1, 'failed'

LogDate	ProcessInfo	Text
2012-02-23 10:39:25.710	NULL	Login failed for user 'sa'. Motivo: la contraseña no es válida para el inicio de sesión proporcionado. [CLIENTE: <local machine>]

Ahora imagina este registro pero multiplicado por 2.5 millones de veces con distintas IP y desde hace 2 meses…y 2.5 GB de log de regalo… ¡terror!

Lo primero es cortar de raíz el acceso al servidor SQL Server desde Internet, y esto es tan sencillo cómo deshabilitar las siguientes reglas del firewall (puede que en tu equipo no se llamen igual, pero básicamente responderán al mismo propósito):

• MS SQL over TCP protocol. Puerto 1433 sobre TCP.
• MS SQL Probe. Puerto 1434 sobre UDP.

En este momento ya estamos seguros porque NO exponemos nuestro servidor SQL Server a Internet, pero ¿Qué pasa si tuviéramos necesidad de hacerlo por algún motivo que ahora mismo se me escapa?

Pues te cuento que según mis investigaciones en google y con la ayuda del sentido común (el menos común de los sentidos por otra parte) las medidas que vamos a tomar son las siguientes:

1. Asegurarnos que todas las claves de los login de SQL Server responden a una política de contraseñas fuerte que impiden poner nombres como “Pepe” o “God”. Esto es porque así los diccionarios de ataques por fuerza bruta no encontrarán nuestra contraseña a la primera de turno y además, cuando empiecen a generar contraseñas de forma aleatoria, les cueste más… Gracias a esto después de 2 meses, nuestro servidor no ha sido hackeado (esta es la única triste medalla que podemos lucir a día de hoy).

A este respecto podemos instruir a SQL Server para garantizar una política de contraseñas e incluso forzar la expiración de los login de SQL Server. Más info aquí. Yo por mi parte no voy a activar esta configuración pero prometo que mi contraseña será de muuuchos caracteres, con números, mayúsculas y minúsculas y algún carácter especial como la arroba o el ampersand.

2. Cambiar el puerto por defecto donde escucha SQL Server. Por defecto, SQL Server atiende al puerto 1433 (puerto bien conocido o como dicen en inglés, well known port). Esto significa que cualquier escáner de puertos que detecte que esté abierto el puerto 1433 automáticamente nos dirá que tenemos a SQL Server a la escucha, luego seremos una victima pidiendo a voces un verdugo.

Una lista completa de estos puertos “bien conocidos” la puedes encontrar en la Wikipedia, que por ejemplo nos dice que los siguientes puertos son “bien conocidos”:

• 1433/tcp Microsoft-SQL-Server
• 1434/tcp Microsoft-SQL-Monitor
• 1434/udp Microsoft-SQL-Monitor

Primero vamos a hacer un ejemplo con los puertos abiertos para ver que información nos da un escáner de puertos on-line como http://www.internautas.org/w-scanonline.php

Con los puertos abiertos (sin haber desactivado las reglas del firewall):

Con los puertos cerrados (habiendo desactivado las reglas del firewall):

Aunque en este post nos estamos centrando en los puertos 1433 TCP (puerto por defecto para SQL Server) y 1434 UDP (puerto por defecto para el servicio SQL Server Browser), hay otros puertos menos habituales pero también relacionados con SQL Server que también podrían estar abiertos. Puedes encontrar más información sobre estos puertos aquí.

Además y aunque no es el propósito de este post, si utilizamos instancias con nombre podríamos estar utilizando puertos dinámicos. Un excelente post que habla sobre ello y lo deja muy claro SQL Server runs on which port? Y otro post más que indica como averiguar que puerto concreto está utilizando SQL Server si estamos utilizando los puertos dinámicos How to Find the Dynamic Port reserved by SQL Server?

En cualquier caso, nosotros no tenemos instancias con nombre y la instancia por defecto utiliza el puerto 1433, así que lo cambiaremos a cualquier otro puerto en el rango 49152-65535 (este es el rango para puertos personalizados que seguro no nos darán problemas mañana con la instalación de otros programas). Imaginemos que lo cambio por ejemplo al puerto 50215. El procedimiento para cambiar el puerto está muy bien explicado aquí.

Después de esto ya sólo nos queda crear una regla en el firewall de Windows, en la que incluso podríamos acotar que direcciones IP podrán conectar (yo por mi parte sólo daré acceso a la IP pública de mi oficina).

3. Deshabilitar la cuenta del usuario sa. Esta medida responde a que es por todos conocidos que cualquier instalación de SQL Server tendrá un usuario sa con privilegios administrativos sobre el servidor. Pero claro está que si deshabilitamos esta cuenta, nuestro atacante no sabrá con que login de SQL Server llevar a cabo su ataque. Lógicamente, antes de deshabilitar la cuenta sa estate seguro de que has creado otro login con los mismos privilegios que el usuario sa o que tienes agregada alguna cuenta de usuario de Windows al role de servidor sysadmin.

Hasta aquí hemos llegado con el propósito de intentar poner las cosas un poco más difíciles a nuestros queridos “amigos de lo ajeno”.

Un saludo!

Cambio masivo del valor de un campo con T-SQL

Hoy me ha surgido la necesidad de cambiar el valor de un código de producto en toda las tablas de la base de datos. Es decir, donde antes tenía el código X ahora debería de tener el código Y.

Lógicamente podría ejecutar manualmente una sentencia de actualización por cada tabla de la base de datos donde se encuentra el campo, pero coincidiremos ambos en que esto no parece una solución muy elegante. Además, no tengo la seguridad de saber exactamente en que tablas está mi campo y no quiero dejar el proceso a medias.

Como me parece que esta situación se podría repetir en un futuro, tanto para el código del producto como para cualquier otro código, he resuelto escribir un script que automatice la tarea en la medida de lo posible.

A grandes rasgos, el script que te muestro a continuación lleva a cabo los siguientes pasos:

• Declarar el nombre de campo que queremos actualizar (aquí cabe mencionar que una base de datos normalizada ayudaría al proceso, es decir, si en una tabla has llamado a tu código de producto “ProductID” y en otra “IDProducto”, pues estamos fastidiados…)
• Declarar el valor que queremos sustituir y el nuevo valor que queremos utilizar.
• Recorrer las tablas en las que aparece el nombre de campo y deshabilitar todas sus restricciones (imagina que sino las restricciones del tipo ‘foreign key’ impedirían el proceso de actualización masivo)
• Ejecutar una consulta de actualización por cada tabla donde aparezca el campo.
• Volver a habilitar las restricciones previamente deshabilitadas.

Lógicamente el script no es perfecto y requiere cambiar algunos ajuste manuales como el tipo de los parámetros, tanto en la declaración inicial como en la sentencia de actualización con sp_executesql.

Además, el script presupone que las ‘foreign key’ declaradas en tus tablas están todas activadas, porque sino y en el último paso, siempre las activará incondicionalmente con independencia de como estuvieran inicialmente.

A continuación el script y espero te sea útil:

 DECLARE @columnName SYSNAME 
 -- Nombre de campo
 SET @columnName = N'ProductID' 
 DECLARE @OldValue NVARCHAR(20)
 -- Valor antiguo
 SET @OldValue = N'1701291'
 DECLARE @NewValue NVARCHAR(20)
 -- Valor nuevo
 SET @NewValue = N'1701291__MODIFICADO'
	
 DECLARE @tableSchema NVARCHAR(128)
 DECLARE @tableName SYSNAME
    
 DECLARE @noCheckStatement NVARCHAR(4000) 
 DECLARE @checkStatement NVARCHAR(4000) 
 SET @noCheckStatement = N''
 SET @checkStatement = N''
 -- Tablas que contienen el nombre de campo
 DECLARE cTables CURSOR READ_ONLY
 FOR
    SELECT  T.TABLE_SCHEMA ,
            T.TABLE_NAME
    FROM    INFORMATION_SCHEMA.TABLES T
            INNER JOIN INFORMATION_SCHEMA.COLUMNS C 
            ON T.TABLE_SCHEMA = C.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAME
    WHERE   TABLE_TYPE = N'BASE TABLE'
            AND COLUMN_NAME = @columnName
		
 OPEN cTables
 FETCH NEXT FROM cTables INTO @tableSchema, @tableName
 WHILE ( @@fetch_status <> -1 ) 
    BEGIN
        IF ( @@fetch_status <> -2 ) 
            BEGIN          
		-- Generar sentencias para deshabilitar y habilitar restricciones 
                SET @noCheckStatement = @noCheckStatement + N'ALTER TABLE '
                    + @tableSchema + '.' + @tableName
                    + ' NOCHECK CONSTRAINT ALL;'
                SET @checkStatement = @checkStatement + N'ALTER TABLE '
                    + @tableSchema + '.' + @tableName
                    + ' WITH CHECK CHECK CONSTRAINT ALL;'
            END
        FETCH NEXT FROM cTables INTO @tableSchema, @tableName
    END
 CLOSE cTables
 
 -- Deshabilitar restricciones
 EXECUTE sp_executesql @noCheckStatement
 
 DECLARE @statement NVARCHAR(4000) 
 DECLARE @params NVARCHAR(4000)   
		
 OPEN cTables
 FETCH NEXT FROM cTables INTO @tableSchema, @tableName
 WHILE ( @@fetch_status <> -1 ) 
    BEGIN
        IF ( @@fetch_status <> -2 ) 
            BEGIN          
		-- Generar sentencia de actualización
                SET @statement = N'UPDATE ' + @tableSchema + '.' + @tableName
                    + ' SET ' + @columnName + ' = @NewValue WHERE '
                    + @columnName + ' = @OldValue'
                SET @params = N'@NewValue NVARCHAR(20), @OldValue NVARCHAR(20)'                
                -- Actualizar
                EXECUTE sp_executesql @statement, @params, @NewValue,
                    @OldValue
            END
        FETCH NEXT FROM cTables INTO @tableSchema, @tableName
    END
 CLOSE cTables
 DEALLOCATE cTables
 
 -- Habilitar restricciones
 EXECUTE sp_executesql @checkStatement