Manual de Instalacion y Configuracion SQL Server 2012 - Armando Alaniz

Managua, 8 de febrero de 2015

Postgrado SQL Server 2012

Módulo III – Administración de SQL Server 2012

Docente: Lic. Denis Enrique Guido Rodríguez

Manual de Instalación y Configuración de Servicios de SQL Server 2012

Autor: Ing. Armando José Alaniz Aragón


Instalación de Microsoft SQL Server 2012 2

Contenido 1. Instalación de Microsoft SQL Server 2012 .................................................................................................... 3

1.1. Creación y configuración del grupo y la cuenta de los servicios .......................................................... 3

1.2. Instalación de Microsoft SQL Server 2012 .......................................................................................... 10

1.3. Configuración final .............................................................................................................................. 19

2. Creación de respaldos de una base de datos: completo, diferencial, transaccional ................................. 25

2.1. Creación de un backup full ................................................................................................................. 25

2.2. Creación de un backup diferencial ..................................................................................................... 28

2.3. Creación de un backup transaccional ................................................................................................. 30

3. Restauración de respaldo completo, diferencial y transaccional ............................................................... 32

3.1. Restauración de respaldo completo ................................................................................................... 32

3.2. Restauración de respaldo diferencial ................................................................................................. 36

3.3. Restauración de respaldo transaccional ............................................................................................. 40

4. Creación de FileGroup y particionamiento de tablas ................................................................................. 45

4.1. Creación de FileGroup ........................................................................................................................ 45

4.2. Creación de función de particionamiento .......................................................................................... 47

4.3. Creación de esquema de particionamiento ....................................................................................... 48

4.4. Creación de tabla particionada ........................................................................................................... 48

5. Configuración de cuenta de correo electrónico, operadores y alertas ...................................................... 53

5.1. Configuración de cuenta de Correo Electrónico................................................................................. 53

5.2. Configuración de Operador ................................................................................................................ 58

5.3. Configuración de Alerta ...................................................................................................................... 59

5.4. Creación de jobs ................................................................................................................................. 63

6. Creación de espejos .................................................................................................................................... 68

6.1. Instalación de nueva instancia de SQL Server .................................................................................... 68

6.2. Restaurar base de datos en la instancia espejo ................................................................................. 71

6.3. Configurar el espejo en la instancia principal ..................................................................................... 71

7. Configuración de Log Shipping ................................................................................................................... 78

8. Creación de Auditoría ................................................................................................................................. 88

9. Planes de mantenimiento........................................................................................................................... 95



1. Instalación de Microsoft SQL Server 2012

1.1. Creación y configuración del grupo y la cuenta de los servicios

Previo a la instalación de Microsoft SQL Server 2012, se debe realizar la creación y configuración

de la cuenta de servicio que será usada por los servicios del motor.

A continuación se describen los pasos a seguir:

Presione el botón Inicio > Herramientas administrativas > Administración de equipos.

Figura 1

En la ventana de Administración de equipos, seleccionar: Usuarios y grupos locales >

Usuarios. Luego clic derecho para agregar un usuario:



Figura 2

Ingresar los datos del usuario tal como aparecen en la siguiente figura. La contraseña de

la cuenta será uni.2015, si lo desea puede usar otra. Presionar el botón crear para

guardar la cuenta de usuario.

Figura 3

Seleccionar Grupos, clic derecho Grupo nuevo.



Figura 4

Ingresar los datos del grupo tal como aparecen en la figura. Presionar el botón Agregar

para agregar la cuenta SqlServicio como miembro de este grupo. Una vez que se haya

agregado la cuenta, presionar el botón Crear para finalizar la creación del grupo.

Figura 5



Figura 6

Se deben realizar configuraciones adicionales para el grupo que acabamos de crear.

Presione el botón Inicio > Herramientas administrativas > Directiva de seguridad local.

Figura 7

Seleccione la opción Directivas locales, Asignación de derechos de usuario, doble clic en

la directiva Iniciar sesión como servicio.



Figura 8

Agregar el grupo SQLServer a la directiva. Esto afectará automáticamente a todos los

miembros del grupo.

Figura 9

Doble clic en la directiva Bloquear páginas en la memoria.



Figura 10

Agregar el grupo SQLServer a la directiva.

Figura 11

Doble clic en la directiva Realizar tareas de mantenimiento del volumen.



Figura 12

Agregar el grupo SQLServer a la directiva.

Figura 13

Para que los cambios en las directivas locales tengan efecto, se tienen dos alternativas,

cerrar la sesión e iniciarla nuevamente o ejecutar el comando gpupdate /force en el

prompt de Windows para forzar la actualización.



1.2. Instalación de Microsoft SQL Server 2012

Se inicia la instalación de la instancia de SQL Server 2012. Seleccionar la opción

Instalación, Nueva instalación de SQL Server.

Figura 14

Verificación previa a la instalación. Si nuestro hardware y sistema operativo cumplen los

requisitos mínimos, se mostrará la siguiente pantalla. Dar clic en OK.

Figura 15



Ingresar la licencia de nuestro disco de instalación. Dar clic en Siguiente.

Figura 16

Chequear la opción de Aceptar los términos de la licencia y dar clic en Siguiente.

Figura 17

Otra verificación previa a la instalación. Si se pasan las pruebas, dar clic en Siguiente.



Figura 18

Seleccionar la primera opción: Instalación de características de SQL Server. Dar clic en

Siguiente.

Figura 19



Seleccionar las características mostradas en la siguiente imagen. Dar clic en Siguiente.

En este manual, nos enfocaremos en la instalación del motor de base de datos.

Figura 20

Otra verificación previa a la instalación. Si se pasan las pruebas, dar clic en Siguiente.



Figura 21

Especificar el nombre y el ID de la instancia de SQL Server. Se dejan los valores por

efecto. Dar clic en Siguiente.

Figura 22



Revisar la cantidad de espacio en disco duro que necesitará el instalador. Si todo está

bien, dar clic en Siguiente.

Figura 23

Especificar las cuentas de los servicios. Usar la cuenta SqlServicio que creamos al

principio de este manual, la contraseña es uni.2015 o la que usted haya definido en ese

paso. Dejar el inicio del servicio del Agente en automático y deshabilitar el inicio del

servicio de SQL Server Browser.



Figura 24

Dar clic en la pestaña Intercalación, clic en el botón Personalizar para configurar la

intercalación del motor de Base de Datos.

Figura 25

Seleccionar del cuadro combinado la opción intercalación Latin1_General y presionar

Aceptar.



Figura 26

Repetir el paso anterior para el servicio de Analysis Services.

En la pestaña de Configuración del servidor: cambiar el modo de autenticación a Mixto y

especificar la contraseña para la cuenta sa de SQL Server. Usar uni.2015 como

contraseña. Agregar la cuenta de servicio que creamos al principio de este manual como

administrador de SQL Server.

Figura 27



Dejar las opciones que están por defecto y agregar como administrador de Analysis

Services la cuenta que creamos al principio de este manual.

Figura 28

Otra verificación previa a la instalación final. Si se pasan las pruebas, dar clic en

Siguiente.

Figura 29



Resumen de las características a instalar. Dar clic en Instalar. Esperar mientras se

realiza la instalación del servidor de base de datos.

Figura 30

Instalación completada con éxito. Dar clic en Cerrar.

Figura 31

1.3. Configuración final

Presione el botón Inicio > Microsoft SQL Server 2012 > Herramientas de Configuración >

Administrador de Configuración de SQL Server.



Figura 32

En la opción Protocolos para Microsoft SQL Server, habilitar la opción Named Pipes

(Canalizaciones con nombre) como indica la figura.

Figura 33

Clic derecho en la opción TCP/IP, Propiedades. Cambiar el valor de la propiedad

Escuchar todos a NO. Esta opción indica que el motor no escuchará en todos los puertos

que trae configurado.



Figura 34

Se procede a habilitar manualmente las IPs y los puertos en los que escuchará el motor

de Base de Datos. Se debe habilitar la IP2 y la IP4.

Figura 35



Figura 36

Cambiar el puerto de la opción IPALL a 1484. Clic en Aceptar para guardar los cambios.

Figura 37

Para que los cambios tengan efecto, se debe reiniciar el servicio del motor.



Clic derecho en Protocolos de SQL Server, Propiedades.

Figura 38

Ocultar la instancia sí. Clic en Aceptar. Reiniciar el servicio de SQL Server para que los

cambios tengan efecto.



Figura 39


Creación de respaldos de una base de datos: completo, diferencial, transaccional 25

2. Creación de respaldos de una base de datos: completo, diferencial, transaccional

Después de conectarnos a la instancia apropiada del motor de base de datos, en el explorador de

objetos, clic en el nombre del servidor para expandir el árbol del servidor. Expandir Base de Datos y

en dependencia de la base de datos, podemos seleccionar una base de datos de usuario o

expandir Base de Datos de sistema y seleccionar una.

En este manual, vamos a trabajar con la base de datos de ejemplo AdventureWorks2012.

2.1. Creación de un backup full

Seleccionar la base de datos, clic derecho Tareas, Respaldar.

Figura 40

Seleccionar el tipo de respaldo Completa y presionar el botón Eliminar para remover el

archivo destino del respaldo. Luego presionar el botón Agregar.



Figura 41

Dar clic en el botón que aparece a la par de la ruta de respaldos.

Figura 42

Seleccionar la ruta donde se desea respaldar la base de datos y especificar el nombre

del archivo de respaldo. En nuestro caso, creamos una carpeta SQL_Backups en la

unidad C: y el nombre del archivo será AdventureWorks2012-Full-20150203.bak.

Presionar el botón Aceptar.



Figura 43

Una vez completada la acción, se muestra un mensaje de éxito.

Figura 44



También se puede usar T-SQL para crear los respaldos completos de una base de

datos. A continuación el script para realizar la misma tarea de los puntos anteriores:

USE AdventureWorks2012; GO BACKUP DATABASE AdventureWorks2012 TO DISK = 'C:\SQL_Backups\AdventureWorks2012-Full[20150203].bak' WITH FORMAT, COMPRESSION, NAME = 'Full Backup of AdventureWorks2012'; GO

2.2. Creación de un backup diferencial

Antes de realizar un respaldo diferencial, se deberán insertar registros de prueba en algunas tablas

de la base de datos.

Se realizan los mismos pasos del respaldo completo. Clic derecho en la base de datos,

Tareas, Respaldar. El tipo de respaldo debe ser Diferencial. Para eliminar los archivos

de destino previos, presionar el botón Eliminar y luego presionar el botón Agregar.

Figura 45

Especificar la ruta y el nombre del archivo de respaldo. Se usará la misma carpeta de

respaldos y el nombre del archivo será AdventureWorks2012-Differential.bak.




Figura 46


Figura 47



A continuación el script para realizar el respaldo diferencial:

USE AdventureWorks2012; GO BACKUP DATABASE AdventureWorks2012 TO DISK = 'C:\SQL_Backups\AdventureWorks2012-Differential[20150203].bak' WITH FORMAT, DIFFERENTIAL, COMPRESSION; GO

2.3. Creación de un backup transaccional

Se realizan los mismos pasos del respaldo completo. Clic derecho en la base de datos,

Tareas, Respaldar. El tipo de respaldo debe ser Transaccional. Para eliminar los

archivos de destino previos, presionar el botón Eliminar y luego presionar el botón

Agregar.

Figura 48

Especificar la ruta y el nombre del archivo de respaldo. Se usará la misma carpeta de

respaldos y el nombre del archivo será AdventureWorks2012-Log1.trn. Presionar el

botón Aceptar.



Figura 49


Figura 50


Restauración de respaldo completo, diferencial y transaccional 32

A continuación el script para realizar el respaldo transaccional:

BACKUP LOG [AdventureWorks2012] TO DISK = N'C:\SQL_Backups\AdventureWorks2012-Log[20150203].trn' WITH NOFORMAT, INIT, NAME = N'AdventureWorks2012-Log-Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO

3. Restauración de respaldo completo, diferencial y transaccional

3.1. Restauración de respaldo completo

Clic derecho en el nodo Base de Datos, Restaurar Base de Datos.

Figura 51

En la ficha General, seleccionar como fuente del respaldo Dispositivo y presionar el

botón de búsqueda.



Figura 52

Buscar el archivo del respaldo completo en la carpeta destinada a los respaldos.


Figura 53

En el destino escribir el nombre de la nueva base de datos, en nuestro ejemplo

AdventureWorks2015.



Figura 54

En la ficha Archivos, renombar los archivos de la base de datos, cambiar el valor 2012

por 2015 para evitar errores con la restauración (Se debe renombrar el mdf y el ldf).

Figura 55

En la ficha Opciones, dejar la base de datos operativa (la opción RESTORE WITH

RECOVERY) y presionar el botón Aceptar.



Figura 56


Figura 57



3.2. Restauración de respaldo diferencial

Es prerrequisito de la restauración de un respaldo diferencial, la restauración de un

respaldo completo, por lo tanto se deben aplicar los pasos descritos en el apartado

anterior. La única opción que cambia es el estado de la recuperación que debe estar en

RESTORE WITH NORECOVERY. Esta opción permite la aplicación de más respaldos,

ya sean diferenciales o transaccionales.

A continuación se muestra como se ve la base de datos cuando se encuentra en el

estado NORECOVERY. El motor de base de datos indica que aún se están aplicando

archivos de respaldos a la base de datos, por lo que aún no está operativa.

Figura 58



Seleccionar la base de datos, clic derecho, Tareas, Restaurar, Archivos y Grupos de

Archivos.

Figura 59

En la ficha General, seleccionar como fuente de restauración la opción Desde

dispositivo. Presionar el botón de búsqueda.

Figura 60

Buscar el archivo del respaldo diferencial en la carpeta destinada a los respaldos.




Figura 61

Chequear el punto de restauración.

Figura 62



En la ficha Opciones, renombrar los archivos. Cambiar el valor 2012 por 2015 para evitar

errores con la restauración (Se debe renombrar el mdf y el ldf). El estado de la

restauración debe ser RESTORE WITH RECOVERY, con esta opción se indica que ya

no se pueden aplicar más respaldos a la base de datos y la base de datos queda

operativa.

Figura 63


Figura 64



Así queda la base de datos una vez restaurada.

Figura 65

3.3. Restauración de respaldo transaccional

Es prerrequisito de la restauración de un respaldo transaccional, la restauración de un

respaldo completo, por lo tanto se deben aplicar los pasos descritos en ese apartado. La

única opción que cambia es el estado de la recuperación que debe estar en RESTORE

WITH NORECOVERY. Esta opción permite la aplicación de más respaldos, ya sean

diferenciales o transaccionales, igual a como se hizo con el respaldo diferencial.

Seleccionar la base de datos, clic derecho, Tareas, Restaurar, Log de Transacciones.

Figura 66



En la ficha General, seleccionar como fuente de restauración la opción Desde

dispositivo. Presionar el botón de búsqueda.

Figura 67

Buscar el archivo del respaldo transaccional en la carpeta destinada a los respaldos.


Figura 68



Chequear el punto de restauración.

Figura 69

En la ficha Opciones, el estado de la restauración debe ser RESTORE WITH

NORECOVERY, porque vamos a realizar la restauración de un segundo respaldo de log

de transacciones. La opción RESTORE WITH RECOVERY se ocupa cuando se aplica la

restauración del último respaldo y deja operativa la base de datos. Presionar el botón

Aceptar.

Figura 70



Repetir desde el paso 2 para la restauración del 2do log de transacciones. Esta vez el

estado de la restauración debe ser RESTORE WITH RECOVERY.

Figura 71

Figura 72




Figura 73


Creación de FileGroup y particionamiento de tablas 45

4. Creación de FileGroup y particionamiento de tablas

4.1. Creación de FileGroup

A continuación vamos a realizar la creación de varios FileGroup, los archivos de estos FileGroup

contendrán los registros de una tabla particionada. El ejemplo que vamos a trabajar requiere la

creación de un FileGroup por año dese el año 2005 hasta el 2015.

Antes de crear los FileGroup, necesitamos crear una carpeta donde se almacenarán los

archivos de cada FileGroup. Crear una carpeta en la unidad C: con el nombre

SQLSERVER_FG_DATA.

Aplicar el siguiente script para crear los FileGroup y archivos. Revisar la carpeta para

comprobar la creación de los archivos.

ALTER DATABASE AdventureWorks2015 ADD FILEGROUP Sales2005; USE [master] ALTER DATABASE AdventureWorks2015 ADD FILE ( NAME = Sales2005Data, FILENAME = 'C:\SQLSERVER_FG_DATA\Sales2005File.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) TO FILEGROUP Sales2005; ALTER DATABASE AdventureWorks2015 ADD FILEGROUP Sales2006; USE [master] ALTER DATABASE AdventureWorks2015 ADD FILE ( NAME = Sales2006Data, FILENAME = 'C:\SQLSERVER_FG_DATA\Sales2006File.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) TO FILEGROUP Sales2006; ALTER DATABASE AdventureWorks2015 ADD FILEGROUP Sales2007; USE [master] ALTER DATABASE AdventureWorks2015 ADD FILE ( NAME = Sales2007Data, FILENAME = 'C:\SQLSERVER_FG_DATA\Sales2007File.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) TO FILEGROUP Sales2007; ALTER DATABASE AdventureWorks2015 ADD FILEGROUP Sales2008; USE [master] ALTER DATABASE AdventureWorks2015 ADD FILE ( NAME = Sales2008Data, FILENAME = 'C:\SQLSERVER_FG_DATA\Sales2008File.ndf', SIZE = 5MB,



MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) TO FILEGROUP Sales2008; ALTER DATABASE AdventureWorks2015 ADD FILEGROUP Sales2009; USE [master] ALTER DATABASE AdventureWorks2015 ADD FILE ( NAME = Sales2009Data, FILENAME = 'C:\SQLSERVER_FG_DATA\Sales2009File.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) TO FILEGROUP Sales2009; ALTER DATABASE AdventureWorks2015 ADD FILEGROUP Sales2010; USE [master] ALTER DATABASE AdventureWorks2015 ADD FILE ( NAME = Sales2010Data, FILENAME = 'C:\SQLSERVER_FG_DATA\Sales2010File.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) TO FILEGROUP Sales2010; ALTER DATABASE AdventureWorks2015 ADD FILEGROUP Sales2011; USE [master] ALTER DATABASE AdventureWorks2015 ADD FILE ( NAME = Sales2011Data, FILENAME = 'C:\SQLSERVER_FG_DATA\Sales2011File.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) TO FILEGROUP Sales2011; ALTER DATABASE AdventureWorks2015 ADD FILEGROUP Sales2012; USE [master] ALTER DATABASE AdventureWorks2015 ADD FILE ( NAME = Sales2012Data, FILENAME = 'C:\SQLSERVER_FG_DATA\Sales2012File.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) TO FILEGROUP Sales2012; ALTER DATABASE AdventureWorks2015 ADD FILEGROUP Sales2013; USE [master] ALTER DATABASE AdventureWorks2015 ADD FILE (



NAME = Sales2013Data, FILENAME = 'C:\SQLSERVER_FG_DATA\Sales2013File.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) TO FILEGROUP Sales2013; ALTER DATABASE AdventureWorks2015 ADD FILEGROUP Sales2014; USE [master] ALTER DATABASE AdventureWorks2015 ADD FILE ( NAME = Sales2014Data, FILENAME = 'C:\SQLSERVER_FG_DATA\Sales2014File.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) TO FILEGROUP Sales2014; ALTER DATABASE AdventureWorks2015 ADD FILEGROUP Sales2015; USE [master] ALTER DATABASE AdventureWorks2015 ADD FILE ( NAME = Sales2015Data, FILENAME = 'C:\SQLSERVER_FG_DATA\Sales2015File.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) TO FILEGROUP Sales2015;

4.2. Creación de función de particionamiento

El tipo de datos de la columna que se usará para el particionamiento será un DATETIME. Ejecutar

el siguiente script para la creación de la función:

-- Crear las particiones USE AdventureWorks2015 GO CREATE PARTITION FUNCTION fnPOOrderDate(DATETIME) AS RANGE LEFT -- left significa menor o igual al valor especificado en el rango FOR VALUES ( '20051231', '20061231', '20071231', '20081231', '20091231', '20101231', '20111231', '20121231', '20131231', '20141231' ) GO



En el punto anterior, creamos 11 archivos para FileGroup, en la función debemos definir 10 valores

de comparación, en nuestro caso fechas. El motor de base de datos automáticamente almacenará

en el último archivo aquellos registros que sean mayores a 20141231, el último valor del rango.

4.3. Creación de esquema de particionamiento

Ejecutar el siguiente script para la creación del esquema de particionamiento:

USE AdventureWorks2015 GO CREATE PARTITION SCHEME schPOOrderDate AS PARTITION fnPOOrderDate TO ( Sales2005, Sales2006, Sales2007, Sales2008, Sales2009, Sales2010, Sales2011, Sales2012, Sales2013, Sales2014, Sales2015 ) GO

4.4. Creación de tabla particionada

Aplicar el siguiente script para crear la tabla particionada. El script se basa en la tabla

PurchaseOrderHeader de la base de datos AdventureWorks2012 con las modificaciones necesarias

para crearla en el esquema de partición:

USE [AdventureWorks2015] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF OBJECT_ID ('dbo.PurchaseOrderHeader') IS NOT NULL DROP TABLE dbo.PurchaseOrderHeader GO CREATE TABLE dbo.PurchaseOrderHeader ( [PurchaseOrderID] [int] IDENTITY(1,1) NOT NULL, [RevisionNumber] [tinyint] NOT NULL,



[Status] [tinyint] NOT NULL, [EmployeeID] [int] NOT NULL, [VendorID] [int] NOT NULL, [ShipMethodID] [int] NOT NULL, [OrderDate] [datetime] NOT NULL, [ShipDate] [datetime] NULL, [SubTotal] [money] NOT NULL, [TaxAmt] [money] NOT NULL, [Freight] [money] NOT NULL, [TotalDue] AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) PERSISTED NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_dbo_PurchaseOrderHeader_PurchaseOrderID] PRIMARY KEY NONCLUSTERED ( [PurchaseOrderID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON schPOOrderDate(OrderDate) GO CREATE CLUSTERED INDEX cix_PurchaseOrderHeader_OrderDate on dbo.PurchaseOrderHeader (OrderDate) --WITH (DROP_EXISTING=ON) -- ESTA INSTRUCCION DESTRUYE EL INDICE ANTERIOR ON schPOOrderDate(OrderDate) GO SET ANSI_PADDING ON GO ALTER TABLE dbo.PurchaseOrderHeader ADD CONSTRAINT [DF_dboPurchaseOrderHeader_RevisionNumber] DEFAULT ((0)) FOR [RevisionNumber] GO ALTER TABLE dbo.PurchaseOrderHeader ADD CONSTRAINT [DF_dboPurchaseOrderHeader_Status] DEFAULT ((1)) FOR [Status] GO ALTER TABLE dbo.PurchaseOrderHeader ADD CONSTRAINT [DF_dboPurchaseOrderHeader_OrderDate] DEFAULT (getdate()) FOR [OrderDate] GO ALTER TABLE dbo.PurchaseOrderHeader ADD CONSTRAINT [DF_dboPurchaseOrderHeader_SubTotal] DEFAULT ((0.00)) FOR [SubTotal] GO ALTER TABLE dbo.PurchaseOrderHeader ADD CONSTRAINT [DF_dboPurchaseOrderHeader_TaxAmt] DEFAULT ((0.00)) FOR [TaxAmt] GO ALTER TABLE dbo.PurchaseOrderHeader ADD CONSTRAINT [DF_dboPurchaseOrderHeader_Freight] DEFAULT ((0.00)) FOR [Freight] GO ALTER TABLE dbo.PurchaseOrderHeader ADD CONSTRAINT [DF_dboPurchaseOrderHeader_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO ALTER TABLE dbo.PurchaseOrderHeader WITH CHECK ADD CONSTRAINT [FK_dboPurchaseOrderHeader_Employee_EmployeeID] FOREIGN KEY([EmployeeID]) REFERENCES [HumanResources].[Employee] ([BusinessEntityID]) GO ALTER TABLE dbo.PurchaseOrderHeader CHECK CONSTRAINT [FK_dboPurchaseOrderHeader_Employee_EmployeeID] GO



ALTER TABLE dbo.PurchaseOrderHeader WITH CHECK ADD CONSTRAINT [FK_dboPurchaseOrderHeader_ShipMethod_ShipMethodID] FOREIGN KEY([ShipMethodID]) REFERENCES [Purchasing].[ShipMethod] ([ShipMethodID]) GO ALTER TABLE dbo.PurchaseOrderHeader CHECK CONSTRAINT [FK_dboPurchaseOrderHeader_ShipMethod_ShipMethodID] GO ALTER TABLE dbo.PurchaseOrderHeader WITH CHECK ADD CONSTRAINT [FK_dboPurchaseOrderHeader_Vendor_VendorID] FOREIGN KEY([VendorID]) REFERENCES [Purchasing].[Vendor] ([BusinessEntityID]) GO ALTER TABLE dbo.PurchaseOrderHeader CHECK CONSTRAINT [FK_dboPurchaseOrderHeader_Vendor_VendorID] GO ALTER TABLE dbo.PurchaseOrderHeader WITH CHECK ADD CONSTRAINT [CK_dboPurchaseOrderHeader_Freight] CHECK (([Freight]>=(0.00))) GO ALTER TABLE dbo.PurchaseOrderHeader CHECK CONSTRAINT [CK_dboPurchaseOrderHeader_Freight] GO ALTER TABLE dbo.PurchaseOrderHeader WITH CHECK ADD CONSTRAINT [CK_dboPurchaseOrderHeader_ShipDate] CHECK (([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)) GO ALTER TABLE dbo.PurchaseOrderHeader CHECK CONSTRAINT [CK_dboPurchaseOrderHeader_ShipDate] GO ALTER TABLE dbo.PurchaseOrderHeader WITH CHECK ADD CONSTRAINT [CK_dboPurchaseOrderHeader_Status] CHECK (([Status]>=(1) AND [Status]<=(4))) GO ALTER TABLE dbo.PurchaseOrderHeader CHECK CONSTRAINT [CK_dboPurchaseOrderHeader_Status] GO ALTER TABLE dbo.PurchaseOrderHeader WITH CHECK ADD CONSTRAINT [CK_dboPurchaseOrderHeader_SubTotal] CHECK (([SubTotal]>=(0.00))) GO ALTER TABLE dbo.PurchaseOrderHeader CHECK CONSTRAINT [CK_dboPurchaseOrderHeader_SubTotal] GO ALTER TABLE dbo.PurchaseOrderHeader WITH CHECK ADD CONSTRAINT [CK_dboPurchaseOrderHeader_TaxAmt] CHECK (([TaxAmt]>=(0.00))) GO ALTER TABLE dbo.PurchaseOrderHeader CHECK CONSTRAINT [CK_dboPurchaseOrderHeader_TaxAmt] GO

Migrar los datos de la tabla original a la tabla particionada:

set identity_insert dbo.PurchaseOrderHeader on; insert into dbo.PurchaseOrderHeader ( PurchaseOrderID, RevisionNumber, Status, EmployeeID, VendorID, ShipMethodID,



OrderDate, ShipDate, SubTotal, TaxAmt, Freight, ModifiedDate ) select PurchaseOrderID, RevisionNumber, Status, EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight, ModifiedDate from Purchasing.PurchaseOrderHeader; set identity_insert dbo.PurchaseOrderHeader off;

Para determinar si una tabla está particionada:

SELECT * FROM sys.tables AS t JOIN sys.indexes AS i ON t.[object_id] = i.[object_id] AND i.[type] IN (0,1) JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id WHERE t.name = 'PurchaseOrderHeader';

Para determinar los valores límites de una tabla particionada:

SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue FROM sys.tables AS t JOIN sys.indexes AS i ON t.object_id = i.object_id JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.partition_schemes AS s ON i.data_space_id = s.data_space_id JOIN sys.partition_functions AS f ON s.function_id = f.function_id LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number WHERE t.name = 'PurchaseOrderHeader' AND i.type <= 1 ORDER BY p.partition_number;



Para determinar la columna de partición de una tabla particionada:

SELECT t.[object_id] AS ObjectID , t.name AS TableName , ic.column_id AS PartitioningColumnID , c.name AS PartitioningColumnName FROM sys.tables AS t JOIN sys.indexes AS i ON t.[object_id] = i.[object_id] AND i.[type] <= 1 -- clustered index or a heap JOIN sys.partition_schemes AS ps ON ps.data_space_id = i.data_space_id JOIN sys.index_columns AS ic ON ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column JOIN sys.columns AS c ON t.[object_id] = c.[object_id] AND ic.column_id = c.column_id WHERE t.name = 'PurchaseOrderHeader'; GO

Para determinar la distribución de los datos en las particiones:

SELECT t.name AS TableName, i.name AS field, p.partition_number, r.value AS BoundaryValue , rows From Sys.Tables AS t Join Sys.Indexes AS i On t.object_id = i.object_id Join sys.partitions AS p On i.object_id = p.object_id And i.index_id = p.index_id Join sys.partition_schemes AS s On i.data_space_id = s.data_space_id Join sys.partition_functions AS f ON s.function_id = f.function_id Left Join sys.partition_range_values AS r On f.function_id = r.function_id and r.boundary_id = p.partition_number Where t.name = 'PurchaseOrderHeader' and i.type <= 1 Order By p.partition_number;


Configuración de cuenta de correo electrónico, operadores y alertas 53

5. Configuración de cuenta de correo electrónico, operadores y alertas

5.1. Configuración de cuenta de Correo Electrónico

Expandir la opción de Administración, Correo electrónico de base de datos, clic derecho,

clic en la opción Configurar correo electrónico de base de datos.

Figura 74

Figura 75

Chequear la opción de Omitir esta página en el futuro para que no vuelva aparecer esta

pantalla de bienvenida. Clic en siguiente.



Figura 76

Elegir la opción Instalar Correo electrónico de base de datos. Presionar Siguiente.

Figura 77

El motor indica que la característica no habilitada y consulta si se desea habilitar.

Presionar Sí.



Figura 78

Ingresar el nombre del perfil, para este ejemplo Perfil DBA y una breve descripción del

perfil. Presionar el botón Agregar para especificar la cuenta de correo electrónico a usar.

Figura 79

Ingresar todos los datos relacionados con la cuenta de correo electrónico. Por motivos

de aprendizaje, se puede usar alguna cuenta personal o crear una nueva cuenta con

cualquier proveedor de servicios de correo gratuito. Para este manual, creé una cuenta

en gmail. Los datos de conexión al servidor SMTP, sólo funcionan para este proveedor.



Figura 80

Una vez agregada la cuenta, presionar Siguiente.

Figura 81

Hacer público el perfil creado y especificarlo como Perfil predeterminado. Presionar

Siguiente.



Figura 82

Configuración de los parámetros del sistema de correo electrónico de base de datos.

Dejar los valores por defecto y presionar Siguiente.

Figura 83

Presionar Finalizar para crear la cuenta de correo electrónico.



Figura 84

5.2. Configuración de Operador

Para crear un operador, el servicio del Agente debe estar iniciado. Si no está iniciado, inícielo desde

el explorador de objetos de Microsoft Management Studio.

En el explorador de objetos, expandir el Agente de SQL Server, seleccionar la opción

Operadores, clic derecho, Nuevo Operador.

Figura 85

Ingresar el nombre del Operador (DBA) y el correo electrónico al cual llegará la

notificación (puede ser un grupo de correo). En este ejemplo, se usó una cuenta de

correo electrónico personal. Presione Aceptar.



Figura 86

5.3. Configuración de Alerta

Seleccione Alerta, clic derecho, Nueva Alerta.

Figura 87



En la ficha General, ingrese el nombre de la alerta, el tipo de alerta y la severidad (016 –

Error de Usuario).

Figura 88

En la ficha Respuesta, chequee la opción Notificar a los operadores, chequee el correo

electrónico del operador.



Figura 89

En la ficha Opciones, chequee la opción de incluir texto de error en la alerta y el retardo

entre respuestas dejarlo en 30 segundos. Presione Aceptar.

Figura 90



El operador y la alerta una vez creados.

Figura 91

En el Agente de SQL Server, clic derecho, Propiedades.

Figura 92

En la ficha Sistema de Alerta, chequear la opción Habilitar perfil de correo electrónico.

Presione Aceptar.



Figura 93

5.4. Creación de jobs

En el Agente de SQL Server, clic derecho Trabajos, Nuevo Trabajo.

Figura 94



En la ficha General, ingresar el nombre del trabajo, el propietario (la cuenta de servicio

del motor) y una breve descripción del trabajo.

Figura 95

En la ficha Pasos, presionar Nuevo para agregar un paso.

Ingresar el nombre del paso, el tipo, la base de datos donde se ejecutará el paso y el

código T-SQL a ejecutar. Presione Aceptar.



Figura 96

El paso una vez creado.

Figura 97



En la ficha Programaciones, presionar Nuevo para crear la programación del Trabajo.

Ingresar los datos tal como aparecen en la pantalla. Presione Aceptar.

Figura 98

En la ficha Notificaciones, chequear la opción Correo Electrónico y seleccionar la cuenta

destino de la notificación y el criterio para decidir cuándo enviar la notificación. En este

caso, cuando el Trabajo finalice con éxito. Presione Aceptar.



Figura 99


Creación de espejos 68

6. Creación de espejos

Para crear un Espejo, se requiere instalar otra instancia de SQL Server.

6.1. Instalación de nueva instancia de SQL Server

Para la instalación de la nueva instancia, los pasos son similares a los descritos en la

unidad de Instalación de SQL Server 2012.

Los pasos que difieren de una nueva instalación se describen a continuación:

Chequear la opción de Realizar una nueva instalación de SQL Server 2012. Presionar Siguiente.

Figura 100

En el espejo, únicamente se instala el motor de base de datos. Chequear la opción y presionar

Siguiente.



Figura 101

El nombre de la nueva instancia debe ser ESPEJO. Presionar Siguiente.

Figura 102



Configurar la cuenta de los servicios. Deben ser las mismas usadas en la primera instalación.

Configurar la Intercalación con los mismos valores de la primera instalación de SQL Server 2012.

Presionar Siguiente.

Figura 103

Una vez finalizada la instalación, en el explorador de objetos, conectarse a la nueva instancia de

SQL Server 2012.

Figura 104



6.2. Restaurar base de datos en la instancia espejo

Se debe realizar un respaldo completo y transaccional de la base de datos en PRODUCCION. Si se

tiene alguna duda, consultar los pasos descritos en la unidad II donde se explica la creación de

respaldos.

Restaurar estos respaldos en la instancia ESPEJO, el estado de la recuperación debe ser WITH

NORECOVERY para que la base de datos no quede operacional. Consulte la unidad III donde se

explican los pasos para restaurar una base de datos.

Durante la restauración, chequear la opción Reubicar todos los archivos, para restaurar la base de

datos en la carpeta de la instancia ESPEJO, si no se chequea esta opción, tendrá que editar

manualmente la ruta del archivo de datos y del log.

Figura 105

6.3. Configurar el espejo en la instancia principal

En la instancia principal, seleccionar la base de datos a la cual se le creará espejo, clic

derecho, Propiedades.



Figura 106

Seleccionar la ficha Espejo, presione el botón Configurar Seguridad.

Figura 107



Chequear la opción No mostrar esta pantalla de bienvenida, para que no vuelva a

aparecer. Presione Siguiente.

Figura 108

Seleccionar la opción No. Presione Siguiente.

Figura 109

Dejar la configuración tal como aparece en la pantalla. Presione Siguiente.



Figura 110

Seleccione la instancia ESPEJO. El Management Studio automáticamente intentará

conectarse al servidor ESPEJO. Ingrese las credenciales para conectarse a la instancia:

sa, uni.2015. Presione Conectar.

Figura 111



Figura 112

No especificar nada. Presione Siguiente.

Figura 113

Resumen de la configuración. Presione Finalizar.



Figura 114

Aparecerá un mensaje, presione Iniciar Espejo.

Figura 115

Presione Sí.

Figura 116

Presione Aceptar.



Figura 117

Así debe verse el explorador de objetos una vez configurado el espejo.

Figura 118


Configuración de Log Shipping 78

7. Configuración de Log Shipping

Cree en la unidad C:\ la carpeta log_shipping con la siguiente estructura: RESPALDO,

SERV_PRINCIPAL, SERV_SECUNDARIO.

Cree un respaldo completo de la base de datos AdventureWorks2012 y guárdelo en la ruta

de respaldo (log_shipping/RESPALDO) con el nombre de logshippingfull.bak.

Cree la carpeta DATOS_LOGSHIPPING en la unidad C:\, ésta será la carpeta de los

archivos de la base de datos una vez realizada la restauración.

En la instancia principal, restaure el respaldo completo que acaba de crear, cambie el

nombre de la base de datos (por ejemplo a AdventureWorksLogShipping). Recuerde

mover los archivos de datos a la carpeta DATOS_LOGSHIPPING y renombrar los archivos

de datos (tanto el mdf como el ldf para que el nombre de la base de datos sea consistente

con sus archivos).

Inserte datos de prueba en cualquier tabla de la nueva base de datos.

Realice respaldo transaccional con el nombre logshipping-log.trn.

Cree la carpeta DATOS_LOGSHIPPING_ESPEJO en la unidad C:\, ésta será la carpeta de

los archivos de la base de datos para la instancia ESPEJO.

En la instancia ESPEJO, restaure el respaldo completo de la base de datos con el estado de

recuperación en WITH NORECOVERY. Recuerde que el nombre de la base de datos debe

ser también AdventureWorksLogShipping y los archivos de datos se deben llamar igual y

deben guardarse en la carpeta de datos de la instancia ESPEJO

(DATOS_LOGSHIPPING_ESPEJO).

En la instancia ESPEJO, restaure el respaldo transaccional con el estado de recuperación

en WITH STAND BY. Presione Aceptar.

Figura 119



Figura 120

Comparta las siguientes carpetas: SERV_PRINCIPAL, SERV_SECUNDARIO, dar permisos

al usuario que corre el servicio SQL.

Figura 121



Figura 122

En la instancia principal, seleccione la base de datos, clic derecho, Propiedades. En la ficha

de Trasvase de registros de transacciones, chequear la opción Habilitar ésta como base de

datos principal. Presione el botón Configuración de copia de seguridad.

Figura 123



Ingrese la ruta del recurso compartido de la instancia principal y la ruta local de la misma

carpeta. Presione Programar, para configurar la frecuencia de ejecución de esta tarea.

Figura 124

Configurar la tarea para que ocurra cada minuto. Presione Aceptar y vuelva a presionar

Aceptar.



Figura 125

Presione Agregar para configurar una instancia de servidor secundaria.

Figura 126

Presione Conectar para conectarse a la instancia secundaria. Ingrese las credenciales para

realizar la conexión.



Figura 127

En la pestaña Copiar Archivos, ingrese la ruta del recurso compartido de la instancia

secundaria. Presione Programar.



Figura 128

Programe la tarea para que se ejecute cada minuto. Presione Aceptar.

Figura 129



En la pestaña Restaurar Registro de Transacciones, seleccione Modo de Espera (Standby)

y chequee la opción Desconectar usuarios en la base de datos cuando se restauran

respaldos. Presione Programar.

Figura 130

Configurarlo para que la tarea se ejecute cada dos minutos. Presione Aceptar y luego vuelva

a presionar Aceptar.



Figura 131

Una vez configurada la instancia secundaria, presione Aceptar.

Figura 132



En la instancia principal, cambiar el propietario de los siguientes jobs por la cuenta del

servicio de SQL Server.

Figura 133

Realice lo mismo en la instancia secundaria con los siguientes jobs:

Figura 134


Creación de Auditoría 88

8. Creación de Auditoría

Cree una carpeta en la unidad C:\ con el nombre Auditorias. En esta carpeta, se guardarán los

registros de auditoría que configuremos.

En Seguridad, clic derecho Auditoría, Nueva Auditoría.

Figura 135

En la ficha General, ingrese el nombre de la auditoria y especifique la ruta donde se

guardarán los registros de auditoría. Presione Aceptar.



Figura 136

Figura 137



En Seguridad, clic derecho en Especificación de Auditoría de Servidor, Nueva

Especificación de Auditoría de Servidor.

Figura 138

Ingrese el nombre de la especificación, seleccione la auditoría creada en el punto anterior y

de la lista de acciones predefinida, elija la que aparece en la pantalla. Presione Aceptar.

Figura 139

Se debe habilitar la especificación y la auditoría.



Figura 140

Figura 141

Para ver la auditoría en funcionamiento, realice varios intentos de conexión fallidos al motor

de base de datos. Luego, en Auditorias, seleccione la auditoria Login fallidos, clic derecho,

Ver log de auditoría.



Figura 142

Historial de los intentos fallidos. Presione Cerrar para salir de la ventana.

Figura 143



Ahora vamos a crear una especificación de auditoría en la base de datos. Primero se debe

crear otra auditoría en el servidor, en la opción Seguridad, Auditorías, usar el nombre

Auditoria_tabla (consulte los pasos anteriores para crear una auditoría). Seleccione la base

de datos AdventureWorks2012, Seguridad, Especificaciones de Auditoría de Base de Datos,

Nueva Especificación de Auditoría de Base de Datos.

Figura 144

Ingrese el nombre de la auditoría, seleccione la auditoría que acabamos de crear, y

seleccione la acción de inserción y actualización sobre la tabla Person.ContactType para

auditar esas operaciones sobre la tabla. La configuración se debe ver como la siguiente

pantalla.



Figura 145

Nuevamente habilite la auditoría y la especificación. Realice inserciones y actualizaciones

sobre la tabla y consulte el log para ver los datos que genera el motor.


Planes de mantenimiento 95

9. Planes de mantenimiento

Antes de crear un plan de mantenimiento, se debe habilitar una opción en la base de datos.

Seleccione la base de datos, clic derecho, Propiedades.

Figura 146

En la ficha Opciones, la opción Actualizar estadísticas automáticamente de forma asíncrona

debe estar en True. Presione Aceptar.

Figura 147



Para crear el plan de mantenimiento, vamos a usar el asistente. Expandir Administración,

Planes de Mantenimiento, clic derecho, Asistente para planes de mantenimiento.

Figura 148

Chequear la opción No mostrar esta pantalla de bienvenida, para que no vuelva a aparecer.

Presione Siguiente.

Figura 149

Ingrese el nombre del plan. Presione Cambiar para configurar la programación del plan.



Figura 150

Configure la frecuencia del plan de acuerdo a sus necesidades. Por motivos de ejemplo, se

configuró la ejecución del plan a una hora cercana a la elaboración de este manual.

Presione Aceptar, luego presione Siguiente.

Figura 151



Seleccione las tareas que forman esta plan de mantenimiento. Presione Siguiente.

Figura 152

Modifique el orden de las tareas si lo desea. Presione Siguiente.

Figura 153



Para cada una de las tareas, se debe configurar la base de datos donde se aplicará el

mantenimiento. Para las tres tareas, seleccione la opción Todas las bases de datos de

usuario. Presione Aceptar y Siguiente en cada paso.

Figura 154

Si lo desea puede modificar la carpeta donde se escribe el log del historial de ejecución del

plan de mantenimiento.

Figura 155



Resumen de la configuración. Presione Finalizar.

Figura 156

Así luce el plan de mantenimiento.

Figura 157

Documents

Manual de Instalacion y Configuracion SQL Server 2012 - Armando Alaniz