Cómo crear una base de datos SQL en múltiples servidores usando múltiples scripts

En este artículo describiremos cómo crear una base de datos SQL a través de múltiples SQL Servers desde múltiples scripts usando PoweShell, la utilidad SQLCMD, SQL Server Management Studio y ApexSQL Build.

En el siguiente ejemplo, tres scripts representan las áreas funcionales de una base de datos SQL de muestra que va a ser creada en múltiples instancias de SQL Server:

El script CreateDatabase.sql para crear una base de datos SQL en una instancia de servidor:

/* Create database*/
USE master;
GO
CREATE DATABASE TestEnvironment
GO

El script CreateTables.sql para crear 200 tablas en la base de datos:

/* Create 200 tables*/
DECLARE @tblNum VARCHAR(max);
DECLARE @intNum INT;
SET @intNum = 1;

WHILE @intNum <= 200
BEGIN
SET @tblNum = 'CREATE TABLE [dbo].[Test' + CAST(@intNum AS VARCHAR(10)) + '](
[col_1] INT IDENTITY(1,1) PRIMARY KEY NOT NULL, 
[col_2] [varchar](10) NOT NULL,
[col_3] [varchar](10) NOT NULL,
[col_4] [varchar](10) NOT NULL,
[col_5] [varchar](10) NOT NULL
) ON [PRIMARY]';
SET @intNum = @intNum + 1;
 EXEC (@tblNum);
END;

El script InsertData.sql para insertar 100,000 en cada tabla:

/* Insert 100K rows into the tables */
USE TestEnvironment
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @i INT
SET @i = 1
WHILE @i <= 100,000
BEGIN
  INSERT INTO dbo.Test1 VALUES (N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata')

  INSERT INTO dbo.Test2 VALUES (N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata')

  INSERT INTO dbo.Test3 VALUES (N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata') 

…

  INSERT INTO dbo.Test200 VALUES (N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata')

SET @i = @i + 1
END
COMMIT TRANSACTION

Propagar scripts en múltiples servidores usando PowerShell

Windows PowerShell® puede ser usado para correr múltiples scripts a través de múltiples instancias SQL Server. En el siguiente ejemplo, tres scripts serán corridos a través de los servidores con las siguientes sentencias:

Invoke-Sqlcmd -InputFile «c:\ScriptFolder\CreateDatabase.sql» -serverinstance «WIN-TTLMDSEJOGD\LENOVO» -Username SpockLog -Password test -Database master;
Invoke-Sqlcmd -InputFile «c:\ScriptFolder\CreateTables.sql» -serverinstance «WIN-TTLMDSEJOGD\LENOVO» -Username SpockLog -Password test -Database master;
Invoke-Sqlcmd -InputFile «c:\ScriptFolder\InsertData.sql» -serverinstance «WIN-TTLMDSEJOGD\LENOVO» -Username SpockLog -Password test -Database master;
Invoke-Sqlcmd -InputFile «c:\ScriptFolder\CreateDatabase.sql» -serverinstance «MCA\LENOVO» -Database master;
Invoke-Sqlcmd -InputFile «c:\ScriptFolder\CreateTables.sql» -serverinstance «MCA\LENOVO» -Database master;
Invoke-Sqlcmd -InputFile «c:\ScriptFolder\InsertData.sql» -serverinstance «MCA\LENOVO» -Database master;

El cmdlet Invoke-Sqlcmd ejecuta los scripts:

Si todos los scripts están contenidos en la misma carpeta, el código puede ser acortado ejecutando todos los scripts en la carpeta:

Get-ChildItem -Path «C:\ScriptFolder» -Filter «*.sql» | % {Invoke-Sqlcmd -InputFile $_.FullName}
foreach ($f in Get-ChildItem -path «C:\ScriptFolder» -Filter *.sql)
{
Invoke-Sqlcmd -serverinstance «WIN-TTLMDSEJOGD\LENOVO» -Username SpockLog -Password test -Database master -InputFile $f.fullname
Invoke-Sqlcmd -serverinstance «MCA\LENOVO» -Database master -InputFile $f.fullname
}

El comando Get-ChildItem retorna todos los archives en el directorio «C:\ScriptFolder». En el caso cuando un directorio no contiene sólo scripts SQL, usar el comando -Filter *.sql sólo retornará los archivos con la extensión especificada:

Propagar scripts en múltiples servidores usando SQLCMD

La utilidad SQLCMD es introducida en SQL Server 2005. Los scripts pueden ser ejecutados uno por uno ejecutando los comandos en la Línea de Comandos:

Invocar SQLCMD muchas veces puede ser evitado escribiendo un archivo .bat y usando el comando :r. El comando :r en un script le dice a la utilidad SQLCMD que incluya los scripts referenciados en el archivo. En este ejemplo, el archivo incluye los tres scripts para realizar la creación de la base de datos, la creación de tablas y las inserciones de datos.

Grabe los siguientes comandos como un archivo Remote.bat:

SELECT @@SERVERNAME, SERVERPROPERTY(‘servername’)
GO
:Connect WIN-TTLMDSEJOGD\LENOVO -U SpockLog -P test
SELECT @@SERVERNAME, SERVERPROPERTY(‘servername’)
GO
:r C:\ScriptFolder\CreateDatabase.sql
:r C:\ScriptFolder\CreateTables.sql
:r C:\ScriptFolder\InsertData.sql
PRINT ‘COMPLETED’
GO
SELECT @@SERVERNAME, SERVERPROPERTY(‘servername’)
GO
:Connect MCA\LENOVO
SELECT @@SERVERNAME, SERVERPROPERTY(‘servername’)
GO
:r C:\ScriptFolder\CreateDatabase.sql
:r C:\ScriptFolder\CreateTables.sql
:r C:\ScriptFolder\InsertData.sql
PRINT ‘COMPLETED’
GO

Ejecute el archivo Remote.bar corriendo el siguiente comando SQLCMD:

SQLCMD -E -d master -i C:\Remote.bat
PAUSE

La opción –E significa que el comando usa una conexión confiable, la opción –d consigna una sentencia USE <nombre_de_base_de_datos> que especifica la base de datos inicial, y la opción –i consigna el parche del archivo que contiene un archivo .bat.

Propagar scripts en múltiples servidores en SQL Server Management Studio

Para propagar scripts en múltiples servidores usando Central Management Servers:

En el menú View, seleccione el comando Registered Servers.
Haga clic derecho en Central Management Servers y seleccione el comando Register Central Management Server y registre el servidor de administración central.
Note que la instancia SQL Server elegida para ser un servidor de administración central debería ser un servidor que no es necesitado para consultar porque el servidor en el servidor de administración central no puede tener el mismo nombre que cualquiera de los servidores registrados. Tratar de registrar el servidor de administración central en un grupo de servidores producirá el siguiente error:
Haga clic derecho en el servidor de administración central y seleccione el comando New Server Group:
Haga clic derecho en el grupo del servidor de administración central y seleccione el comando New Server Registration:
En el diálogo New Server Registration, registre las instancias de SQL Server.
Para ejecutar un script contra todos los servidores en un grupo, haga clic derecho en el directorio del servidor y seleccione el comando the New Query:
Pegue un script en la ventana de consultas y ejecútelo.

Repita los pasos 6. Y 7. para todos los scripts que necesitan ser ejecutados.

Las desventajas de los Servidores de Administración Central son que sólo usa Windows Authentication, múltiples carpetas no pueden ser seleccionadas dentro de Central Management System, y consultar contra dos grupos requeriría crear un nuevo directorio o ejecutar un script o una consulta grupo por grupo.

Usar Servidores de Administración Central para ejecutar scripts en múltiples instancias de SQL Server puede ser evitado usando Local Server Groups.

Para propagar scripts contra múltiples servidores usando un grupo de servidores local:

En el menú View, seleccione el comando Registered Servers
Haga clic derecho en Local Server Groups y seleccione el comando New Server Group.
Añada el grupo de servidores.
Haga clic derecho en el grupo creado y seleccione el comando New Server Registration:
Registre, pruebe y grabe todos los servidores a los cuales se propagarán los scripts:
Para ejecutar un script en todos los servidores registrados, haga clic derecho en Local Server Groups y seleccione el comando New Query.
Pegue un script en la Ventana de Consultas y ejecútelo:

Repita los pasos 6. Y 7. para todos los scripts que necesitan ser ejecutados.

Ambos métodos en SQL Server Management Studio requieren una apertura manual y pegar todos los scripts. Los scripts pueden ser propagados sólo en servidores registrados que también necesitan ser añadidos manualmente.

Propagar scripts en múltiples servidores usando ApexSQL Build

Para propagar scripts sin codificación adicional y sin la necesidad de abrir y pegar todos los scripts, use ApexSQL Build, una herramienta de despliegue de bases de datos SQL Server que puede crear una base de datos SQL o actualizar una existente desde scripts SQL, carpetas de scripts SQL, instantáneas de bases de datos o scripts bajo control de versiones.

Para crear nuevas bases de datos en múltiples servidores usando ApexSQL Build, inícielo y seleccione la opción Execute scripts on multiple databases:

Execute Scripts On Multiple Databases

Haga clic en el botón Add y elija la opción Add all scripts from folder:

Si todos los scripts no están en el mismo directorio, seleccione la opción Add scripts para añadir scripts manualmente.

Establezca la acción On error y abra el diálogo Edit database deployment list:

Las opciones disponibles en el menú On error:

On error dropdown list

En el diálogo Edit database deployment list, añada múltiples instancias SQL Server y grabe la lista:

Adding multiple SQL Server instances

En este ejemplo, la opción Show system databases es seleccionada para ejecutar scripts en el dominio de la base de datos master para crear una copia fresca de la base de datos desde el script.

Después de establecer todos los parámetros, haga clic en el botón Execute: