Introducción

El principal objetivo de muchas organizaciones hoy es reducir costos mientras que se mantiene un alto grado de estabilidad y eficiencia. Para este fin, deberíamos pensar fuera de la caja acerca de cómo podemos ayudar a lograr esto como DBAs. Los enfoques incluyen:

• Centralización
• Reducción del almacenamiento SAN
• Reducir la licencia de Microsoft para Windows y SQL Server

La Migración de Bases de Datos es una posible solución para lograr este objetivo. De todos modos, algunos DBAs no tienen una visión clara de los requerimientos, y los pasos reales para cómo lograr esto con los mínimos riesgos y con nada de tiempo de inactividad.

En este artículo, listaré los pasos comunes para la migración de bases de datos, incluyendo lo siguiente:

• Requerimientos de Migración
• Migración de Servicios de Reporte (SSRS)
• Preparación de la Migración del Motor de Bases de Datos
• Proceso de Migración del Motor de Bases de Datos
• Recomendación de Migración
• Conclusión
• Referencias

Requerimientos de Migración

En esta fase deberíamos tener un buen conocimiento acerca de los servicios de Microsoft SQL Server y cómo podemos manejarlos para poder recolectar la información siguiente:

• ¿Cuántas instancias de SQL Server? Si vamos a crear un proyecto de migración, deberíamos listar todas las instancias incluidas en este proceso para preparar nuestros scripts y abrir el Puerto 445 de carpetas compartidas para poder realizar una copia de seguridad y restaurar en esta carpeta compartida.

• ¿Cuántas Bases de Datos necesitan ser migradas? Esta es información muy importante para poder saber cómo distribuiremos las bases de datos entre las nuevas instancias a las que migraremos.

• ¿Tamaño de las Bases de Datos? Esta información es requerida para poder diseñar el diagrama de discos requerido para esta base de datos.

Migración de Servicios de Reporte (SSRS)

Para mover los reportes RDL desde un servidor a otro, tenemos dos opciones:

1. Respaldar/Restaurar las dos bases de datos {ReportServer, ReportServerTemp} desde la antigua instancia de servicio de reporte a la nueva

2. Mover los archivos DRL y la fuente de datos usando una herramienta de terceros como RS ScripterThis, que puede agarrar todos los RDL y fuentes de datos desde el destino y desplegarlos al nuevo servidor de reportes. Para más información acerca de esta herramienta, revise este enlace.

Más información puede ser encontrada en este artículo – Moviendo las Bases de Datos del Servidor de Reportes a Otra Computadora (Modo Nativo de SSRS)

Migración de Bases de Datos

En esta etapa, listaremos todos los pasos uno por uno, lo que nos llevará a la migración óptima sin tiempo de inactividad, y sin perder nada. Estos pasos están divididos en dos fases, Preparación y Migración, las cuales serán cubiertas a continuación:

Migración del Motor de Bases de Datos

• Servidor de Aplicación: El equipo de Desarrollo debería estar listo para cambiar la cadena de conexión al nuevo nombre de instancia SQL Server. De todas maneras, a veces ellos no podrán hacer esto, y en tal caso necesitamos enrutar el alias para direccionar cualquier intento de conexión desde el servidor de aplicación al Antiguo SQL Server.

  Luego el alias lo direccionará a nuestro nuevo SQL Server.

  Para hacer esta acción:

  • Abra RUN
  • Escriba Cliconfg
  • Seleccione el Alias y TCP/IP

    

    Figura 2 Añadiendo Alias

• Configuración de servidor: Codifique todas as configuracionesactuales del servidor como [CPU, IO, Memoria, el umbral de paralelismo, Máximo grado de paralelismo, CLR]

• RCSI (Read Committed Snapshot Isolation): Liste todas las bases de datos con la opción RCSI habilitada, esta configuración grabada en MSDB y cuando usted mueva la Base de Datos por respaldo/restauración este tipo de configuración, usted lo perderá.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  Select name, ' EXEC sp_resetstatus ''' + name + ''' GO ALTER DATABASE ' + name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE go ALTER DATABASE ' + name + ' SET READ_COMMITTED_SNAPSHOT ON; go ALTER DATABASE ' + name + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE go ' as 'Run on Destination Server.' from sys.databases where is_read_committed_snapshot_on = 1 and database_id>4 and name <>'distribution'

• Service Broker: Descubra las bases de datos con la opción service bróker habilitada.

  1 2 3 4 5 6 7 8 9 10 11

  Select name,' USE master ; GO ALTER DATABASE [' + name + '] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE ' + name + ' SET ENABLE_BROKER ; GO ALTER DATABASE [' + name + '] SET  MULTI_USER WITH ROLLBACK IMMEDIATE GO' from sys.databases where is_broker_enabled = 1 and database_id>4 and name <>'distribution'

• Migración de Objeto No Contenido: Aquí necesitamos lo siguiente: Servidores Enlazados, Usuarios, Alertas de Privilegios, Operador, la configuración de Correo Electrónico, trabajos de Negocios y los objetos de usuario de la Base de Datos de Sistema.

  Para cubrir todo lo de arriba, yo estoy usando un script PowerShell muy útil desde DBATools, el cual cubre muchos casos.

  Para más información acerca de este script de PowerShell, revíselo aquí: DBATools; algunos ejemplos desde el comando de PowerShell:

  • Copy-SqlLogin -Source “Nombre del servidor”-Destination “Nombre del servidor”-Force
  • Sync-SqlLoginPermissions -Source “Nombre del servidor”-Destination “Nombre del servidor”
  • Copy-SqlJob -Source “Nombre del servidor”-Destination “Nombre del servidor”-Force
  • Copy-SqlOperator -Source “Nombre del servidor”-Destination “Nombre del servidor”-Force
  • Copy-SqlAlert -Source “Nombre del servidor”-Destination “Nombre del servidor”-Force
  • Copy-SqlDatabaseMail -Source “Nombre del servidor”-Destination “Nombre del servidor” -Force
  • Copy-SqlLinkedServer -Source “Nombre del servidor”-Destination “Nombre del servidor”–Force
  • Copy-SqlSysDbUserObjects -Source “Nombre del servidor”-Destination “Nombre del servidor.”

• SYNONYMS: es como el servidor enlazado que necesitamos para listarlo, luego cambie la cadena de conexión con el nuevo servidor:
  1 2 3 4 5 6

  Declare @DBname Nvarchar(500)=(Select DB_NAME()) Select @DBname Select   ' Use ['+@DBname+'] GO Create SYNONYM dbo.[' + Name + '] For ' + Base_Object_name   FROM SYS.SYNONYMS

Proceso de migración del motor de base de datos

Estos cuatro puntos previos son pre-preparación para el proceso de migración, y ahora comencemos la migración real.

• Copia de Seguridad Completa desde el servidor fuente: La Copia de Seguridad es el paso más importante del que depende todo el proceso de migración. Por tanto, yo estoy usando uno de mis procedimientos almacenados personalizados: (DMV_backup_Database), es un procedimiento almacenado muy sofisticado para conducir todos los tipos de respaldo (COMPLETO, DIFERENCIAL, DE REGISTROS) usando el dispositivo de copia de seguridad, parámetros de tecnología dinámicos, tipos de Tablas para eliminar algunas de las bases de datos de la copia de seguridad. Este procedimiento almacenado requerirá crear dos carpetas compartidas en el servidor destino para poder tomar la copia de seguridad directamente desde la fuente al disco SAN destino.

  Para más información acerca de este procedimiento almacenado, incluyendo parámetros y script, vea el Apéndice A.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

  USE [msdb] GO   CREATE TYPE [dbo].[Exceptionlist] AS TABLE( [list] [int] NULL ) GO   USE [msdb] GO DECLARE @return_value int declare @Exceptionlist_value Exceptionlist insert into  @Exceptionlist_value Select 2 UNION SELECT 6 UNION SELECT 9--- DB's ID Excluded from backup process EXEC @return_value = [dbo].[DMV_BackupAll] @start_DB_ID = 5, @END_DB_ID = 200, @FULLTapeBackupLocation = N'\\DestinationServer\Backup\FULL\', @FULLBackup = 1, @FULLoverwrite =1 , @DIFFTapeBackupLocation = N'\\DestinationServer\Backup\DIFF\', @DiffBackup= 0 , @DIFFoverwrite = 0, @LOGBackupLocation = N'\\DestinationServer\Backup\LOG\', @LOGBackup = 0, @LOGoverwrite = 0, @COMPRESSION = 1, @Exceptionlist_DBs = @Exceptionlist_value, @job_name='DMV_UserDB_Full_Backup', @p_recipients = 'SQLGULF-DBConsultant@MostafaElmasry.com'   SELECT 'Return Value' = @return_value

• Crear un Dispositivo de Respaldo para una Copia de Seguridad Completa: codifique todos los dispositivos de respaldo creados en la fuente después de que la copia de seguridad se procesa exitosamente y créelos en el servidor destino para poder hacer la restauración usando este dispositivo de respaldo.

  1 2 3 4 5 6 7 8 9

  USE [master] GO Select 'USE [master] GO EXEC master.dbo.sp_addumpdevice  @devtype = N''disk'', @logicalname = N'''+name+'_FULLtape'', @physicalname = N''\\DestinationServer\Backup\FULL\'+name+'_FULLtape.back'' GO ' from Sys.Databases where state_desc ='online' AND Database_id > 4

• Restaurar la Copia de Seguridad Completa en el servidor destino: El segundo paso es restaurar la Copia de Seguridad COMPLETA en el nuevo servidor con la opción No recovery para hacer que las bases de datos en el modo de restauración puedan sobrescribir la Copia de Seguridad Diferencial.

• Base de Datos en estado de Sólo Lectura: En el servidor fuente, necesitamos actualizar el estado de todas las bases de datos para que estén en el estado de Sólo Lectura, para asegurarnos de que ningún cambio ocurrirá al nivel de la Base de Datos. El siguiente script generará un script T-SQL para alterar cada base de datos para que estén en modo de Sólo Lectura mode.

  1 2 3 4 5 6 7 8 9 10

  select 'EXEC sp_resetstatus '''+NAME+''' GO ALTER DATABASE ['+NAME+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE ['+NAME+'] SET  READ_ONLY WITH ROLLBACK IMMEDIATE GO ALTER DATABASE ['+NAME+'] SET MULTI_USER ' from sys.databases where database_id>4 and name <>'distribution' and state_desc = 'online'

• Copia de Seguridad Diferencial desde el servidor fuente Ahora que las Bases de Datos están en modo de Sólo Lectura, tomar una copia de seguridad diferencial cubrirá todos los cambios que han pasado en la base de datos desde la última Copia de Seguridad completa. Aquí, también, usaremos el mismo procedimiento almacenado “DMV_backup_Database” que usamos en la copia de seguridad completa anteriormente.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

  USE [msdb] GO DECLARE @return_value int declare @Exceptionlist_value Exceptionlist insert into  @Exceptionlist_value Select 2 UNION SELECT 6 UNION SELECT 9--- DB's ID Excluded from backup process EXEC @return_value = [dbo].[DMV_BackupAll] @start_DB_ID = 5, @END_DB_ID = 200, @FULLTapeBackupLocation = N'\\DestinationServer\Backup\FULL\', @FULLBackup = 0, @FULLoverwrite =0 , @DIFFTapeBackupLocation = N'\\DestinationServer\Backup\DIFF\', @DiffBackup= 1 , @DIFFoverwrite = 1, @LOGBackupLocation = N'\\DestinationServer\Backup\LOG\', @LOGBackup = 0, @LOGoverwrite = 0, @COMPRESSION = 1, @Exceptionlist_DBs = @Exceptionlist_value, @job_name='DMV_UserDB_DIFF_Backup', @p_recipients = 'SQLGULF-DBConsultant@MostafaElmasry.com'

• Crear Dispositivo de Respaldo para copia de seguridad diferencial: Codifique los dispositivos de copia de seguridad relacionados a la copia de seguridad diferencia desde el servidor fuente y créelo en el servidor destino.

  1 2 3 4 5 6 7 8 9

  USE [master] GO Select 'USE [master] GO EXEC master.dbo.sp_addumpdevice  @devtype = N''disk'', @logicalname = N'''+name+'_DIFFtape'', @physicalname = N''\\DestinationServer\Backup\FULL\'+name+'_DIFFtape.back'' GO ' from Sys.Databases where state_desc ='online' AND Database_id > 4

• Restaure la Copia de Seguridad Diferencia en el servidor destino: Restaure la copia de seguridad diferencia con la opción Recover y sobrescriba.

• Cambiar el estado de la Base de Datos a estado Lectura-Escritura: Cuando restauramos la copia de seguridad diferencial en el servidor destino, encontraremos todas las bases de datos en modo de sólo lectura porque tomamos la copia de seguridad diferencial desde la fuente después de actualizar las bases de datos a modo de sólo lectura.

  1 2 3 4 5 6 7 8 9 10

  select 'EXEC sp_resetstatus '''+NAME+''' GO ALTER DATABASE ['+NAME+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE ['+NAME+'] SET  READ_WRITE WITH ROLLBACK IMMEDIATE GO ALTER DATABASE ['+NAME+'] SET MULTI_USER WITH ROLLBACK IMMEDIATE ' from sys.databases where database_id>4 and name <>'distribution' and state_desc = 'online'

• Migrar el Inicio de Sesión desde el servidor fuente: Ejecute el comando de PowerShell de nuevo para mover los usuarios de SQL Server para garantizar que no se pierden usuarios.

  • Copy-SqlLogin -Source “Nombre del servidor”-Destination “Nombre del servidor”-Force

  • Sync-SqlLoginPermissions -Source “Nombre del servidor”-Destination “Nombre del servidor.”

• Deshabilitar el inicio de sesión en el servidor fuente: Ahora todas nuestras bases de datos son movidas al nuevo servidor. Para saber si la aplicación está funcionando bien, deshabilite los inicios de sesión en la antigua instancia.

  1 2 3 4 5 6 7 8 9

  USE master; GO SELECT 'ALTER LOGIN ['+name+'] DISABLE;' FROM sys.syslogins where name not like '%##MS%' and name not like '%NT AUTHORITY%' and name not like '%NT SERVICE%' and name not in ('SQLSHACK','SA')

• Revisar la compatibilidad de la Base de Datos Si migramos desde una versión antigua a una nueva versión como Example 2016, necesitamos actualizar la compatibilidad de la Base de Datos a 140.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  select name, ' EXEC sp_resetstatus ''' + name + ''' GO ALTER DATABASE ' + name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE go USE [master] GO ALTER DATABASE [' + name + '] SET COMPATIBILITY_LEVEL = 140 go ALTER DATABASE ' + name + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE go ' from sys.Databases where Database_Id > 4 AND COMPATIBILITY_LEVEL <> 140

• Revisar Inicio de sesión fallido en ambos servidores: Los inicios de sesión fallidos son una de las cosas que deberíamos monitorear para saber si alguna aplicación aún se conecta al antiguo SQL Server. Aquí están los pasos para crear una auditoría de Inicios de Sesión fallidos en SQL Server.

  • Cree una carpeta en el almacenamiento SAN local.

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

    USE Master; GO SET NOCOUNT ON -- 1 - Variable declaration) DECLARE @LogPath nvarchar(500) DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT) SET @LogPath = 'C:\ServerAuditing\' ---------------+ @DBName -- 5 - Remove all records from @DirTree DELETE FROM @DirTree -- 6 - @LogPath values INSERT INTO @DirTree(subdirectory, depth) EXEC master.sys.xp_dirtree @LogPath -- 7 - Create the @LogPath directory IF NOT EXISTS (SELECT 1 FROM @DirTree ) ----------WHERE subdirectory = @DBName) EXEC master.dbo.xp_create_subdir @LogPath SET NOCOUNT OFF GO

  • Cree las especificaciones de autoría del servidor

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

    USE [master] GO IF  EXISTS (SELECT * FROM sys.server_audits WHERE name = N'FAILED_LOGIN_GROUP') ALTER SERVER AUDIT [FAILED_LOGIN_GROUP] WITH (STATE = OFF); GO IF  EXISTS (SELECT * FROM sys.server_audits WHERE name = N'FAILED_LOGIN_GROUP') DROP SERVER AUDIT [FAILED_LOGIN_GROUP] GO USE [master] GO /****** Object:  Audit [FAILED_LOGIN_GROUP]    Script Date: 10/25/2010 13:21:58 ******/ CREATE SERVER AUDIT [FAILED_LOGIN_GROUP] TO FILE ( FILEPATH = N'C:\Server Auditing' ,MAXSIZE = 100 MB ,MAX_ROLLOVER_FILES = 2 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '8f5c8e0b-6ba3-4964-8da5-d7184220f9ca' ) GO ALTER SERVER AUDIT [FAILED_LOGIN_GROUP] WITH (STATE = ON); GO IF  EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'FAILED_LOGIN_GROUP') ALTER SERVER AUDIT SPECIFICATION [FAILED_LOGIN_GROUP] WITH (STATE = OFF) GO IF  EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'FAILED_LOGIN_GROUP') DROP SERVER AUDIT SPECIFICATION [FAILED_LOGIN_GROUP] GO GO CREATE SERVER AUDIT SPECIFICATION [FAILED_LOGIN_GROUP] FOR SERVER AUDIT [FAILED_LOGIN_GROUP] ADD (FAILED_LOGIN_GROUP) WITH (STATE = ON) GO

Recomendación de migración

En esta coyuntura, hay algunos puntos recomendados que deberíamos considerar; ellos son los elementos clave de cualquier proyecto de migración exitoso:

• La base de datos master debería estar hospedada en un disco separado (200 GB, SAS, RAID 5)

• Las bases de datos temporales deberían estar hospedadas en un disco separado (150 GB, SSD-, RAID 1/0)

• Los archivos MDF para las bases de datos del usuario deberían estar hospedadas en un disco separado (SAS, RAID 5)

• Los registros de transacciones (LDF) deberían hospedarse en un disco separado (150 GB, SAS, RAID 5)

• Los archivos de bases de datos temporales deberían ser configurados basados en el conteo de procesadores lógicos; si tales procesadores son menos o iguales que 8, configure su base de datos temporal con el conteo de procesadores lógicos. Si son más de 8 procesadores lógicos, configure su base de datos temporal en 8 archivos. Para más información, revise larecomendaciónde Microsoft..

Conclusión

Una migración exitosa de bases de datos no es una misión imposible. Pero es importante enfocarlo sistemáticamente. Uno sólo necesita tener consciencia de qué hacer y los requerimientos para llevarlo adelante exitosamente. Y, como siempre, asegúrese de que cada paso es documentado.

Apéndice A – Procedimiento almacenado DMV_BackupAll

Usted puede descargar el script aquí.

References:

• Moviendo las Bases de Datos del Servidor de Reportes a Otra Computadora
• Cómo mover bases de datos entre computadoras que están corriendo SQL Server
• Recomendaciones para reducir la contención de la asignación en bases de datos temporales SQL Server
• Cómo transferir inicios de sesión y contraseñas entre instancias de SQL Server

• Acerca de
• Últimas entradas

Mustafa EL-Masry

Mustafa EL-Masry is a Senior database consultant and one of the experts in Database performance tuning in the Middle East.

Currently, he is working as a Senior consultant production DBA and Development DBA in many projects in multiple government sectors. He is a Top SQL Server blogger in the Middle East, founder of the community mostafaelmasry.com, and is the second Arabic author on Microsoft MSDN in SQL Server.

Based on his current position, he solved fairly interesting problems on fairly large databases and highly sensitive performance cases.

View all posts by Mustafa EL-Masry

Últimas entradas de Mustafa EL-Masry (ver todo)

• Procedimientos recomendados de migración de bases de datos SQL Server para un bajo riesgo y bajo tiempo de inactividad - April 21, 2017