Conceptos de seguridad en SQL Server

El manejo de la seguridad en SQL SERVER 2005 se basa en tres entidades, Principales, Permisos y asegurables

Los principales son todos aquellos objetos a los cuales se les colocan permisos para acceder a los asegurables.

Ejemplos de principales son : Grupos de windows, Cuentas de dominio, usuario locales, Logins de sql, roles de servidor, roles de base de datos, role de aplicación y usuarios

Los asegurables son aquellos objetos que son utilizados por los principales. Existen tres tipos de niveles de asegurables.

Nivel de servidor
*Logins
*Endpoints
*Databases

Nivel de Base de datos
*Usuarios
*Roles
*Roles de aplicación
*Certificados
*Llaves simetricas
*Llaves asimetricas
*Ensamblados
*Catálogos de Full-test
*Eventos DDL
*Esquemas

Nivel de esquema
*Tablas
*Vistas
*Funciones
*Procedimientos
*Tipos
*Sinonimos
*Agregados

Noten que por ejemplo los Roles son principales y asegurables a la vez.

Los permisos controlan el acceso de los principales a los asegurables, estos se pueden otorgar, revocar o denegar

Siempre el permiso denegar tiene mayor preferencia que los otros permisos, con excepción cuando se deniega el permiso sobre un tabla pero se otorga en una columna

Otorgar permisos

GRANT ALL [ PRIVILEGES ] permission [ ( column [ ,...n ] ) ] ON [ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ] TO [ ,...n ] [ WITH GRANT OPTION ] [ AS ]

La opción ALL otorga los siguientes permisos teniendo en cuenta ANSI-92

*Permisos de función escalar: EXECUTE y REFERENCES.
*Permisos de función con valores de tabla: DELETE, INSERT, REFERENCES, SELECT y UPDATE.
*Permisos de procedimiento almacenado: EXECUTE, SYNO
NYM, DELETE, INSERT, SELECT y UPDATE.
*Permisos de tabla: DELETE, INSERT, REFERENCES, SELECT y UPDATE.
*Permisos de vista: DELETE, INSERT, REFERENCES, SELECT y UPDATE.

La opción PRIVILEGES se coloco por compatibilidad con versiones anteriores

La opción WHIT GRANT OPTION indica que el principal también podrá otorgar el mismo permiso a otros principales

Ejemplo: Asignar al usuario julio permiso sombre la tabla empleados en la base de datos prueba

USE Prueba
GRANT SELECT ON OBJECT::DBO.EMPLEADOS TO JULIO
GO

Asignar al usuario julio permiso de lectura sobre el campo EmployeeID de la tabla empleados con WHIT GRANT OPTION

USE Prueba
GRANT SELECT (EmployeeID) ON OBJECT::DBO.EMPLEADOS TO JULIO WITH GRANT OPTION
GO

Denegar permisos

DENY ALL [ PRIVILEGES ] permission [ ( column [ ,...n ] ) ]ON [ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ] TO [ ,...n ] [ CASCADE ] [ AS ]

La opción CASCADE indica que también se denegara el permiso a las entidades a las cuales el principal le otorgo el permiso

Ejemplo: Denegar al principal julio el permiso de insertar sobre la tabla empleados de la BD prueba

USE Prueba
DENY INSERT ON OBJECT::DBO.EMPLEADOS TO JULIO CASCADE
GO

Revocar permisos

REVOKE [ GRANT OPTION FOR ] [ ,...n ] ON [ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ] { FROM TO } [ ,...n ] [ CASCADE ] [ AS ]

Ejemplo: Quitar el permiso de ejecución del procedimiento almacenado ActualizarSalario de la BD Prueba al usuario julio

USE Prueba
REVOKE EXECUTE ON OBJECT::dbo.ActualizarSalario FROM julio
GO

Los permiso que se pueden otorgar, revocar y denegar a un asegurable son:

*Control: Da al principal todos los privilegios que tiene el propietario sobre el asegurable
*Alter: Tiene todos los privilegios con excepción de tomar propiedad de un asegurable
*Take Ownership: tomar propiedad del asegurable
*Create: Permite al principal crear asegurables
*View definition: permite ver la sentencias T-SQL con las cuales fueron creadas los asegurables
*Select: Permite al principal leer datos sobre el asegurable
*Insert: Permite al principal insertar datos sobre el asegurable
*Update: Permite al principal modificar datos sobre el asegurable
*Delete: Permite al principal borrar datos sobre el asegurables
*Reference:
*Execute: Permite al principal ejecutar asegurables

Service Broker

Es una plataforma para construir servicios seguros, confiable y escalables. Utilizan mensajes para intercambiar información entre servicios por medio de TCP IP

Es útil cuando se necesita procesamiento asíncrono o distribuir trabajos en diferentes computadores

Para habilitarlo es necesario ejecutar la siguiente sentencia T-SQL

ALTER DATABASE AdventureWorks SET ENABLE_BROKER

Para configurar la arquitectura del Service Broker contamos con 4 objectos.

Message Type: Define el nombre del mensaje y el tipo de informacion que el mensaje contiene. Estos mensajes deben ser creados en ambas partes de la conversacion

CREATE MESSAGE TYPE message_type_name
[AUTHORIZATION owner_name]
[VALIDATION =
{NONE EMPTY WELL_FORMED_XML
VALID_XML WITH SCHEMA COLLECTION schema_collection_name}]

Los posibles valores para VALIDATION son:
NONE: no se realiza ninguna validación
EMPTY: El cuerpo del mensaje debe tener valor NULO
WELL_FORMED_XML: Debe contener un XML bien formado
VALID_XML WITH SCHEMA COLLECTION: El contenido del XML debe cumplir con el XML SCHEMA señalado

Para modificarlo necesitamos utiliza la sentecia ALTER

ALTER MESSAGE TYPE message_type_name
VALIDATION =
{NONE EMPTY WELL_FORMED_XML
VALID_XML WITH SCHEMA COLLECTION schema_collection_name}]

Y para eliminar

DROP MESSAGE TYPE message_type_name

Contract: Define los tipos de mensajes que un servicio puede utilizar en una conversación y la dirección en que los mensajes pueden ser enviados

CREATE CONTRACT contract_name
[ AUTHORIZATION owner_name ]
( { message_type_name SENT BY { INITIATOR TARGET ANY }
[ DEFAULT ] } [ ,...n] )

Los posibles valores para SENT BY son:
INITIATOR: indica que solo el iniciador puede enviar dicho tipo de mensaje
TARGET: Indica que solo el destino puede enviar dicho tipo de mensaje
ANY: Indica que tanto el iniciador y el destino pueden enviar dicho tipo de mensaje

Para eliminar un contrato la sintaxis es la siguiente:

DROP CONTRACT contract_name

Queue: Define la ubicación donde se almacenaran los mensajes hasta que un servicio este disponible para atender los mensajes

CREATE QUEUE [database_name.[schema_name].schema_name.] queue_name
[ WITH
[ STATUS = { ON OFF } [ , ] ]
[ RETENTION = { ON OFF } [ , ] ]
[ ACTIVATION (
[ STATUS = { ON OFF } , ]
PROCEDURE_NAME = stored_procedure_name,
MAX_QUEUE_READERS = max_readers ,
EXECUTE AS { SELF 'user_name' OWNER }
) ]
]
[ ON { filegroup [ DEFAULT ] } ]

STATUS: Especifica si la cola esta habilitada. Cuando esta en OFF ningún servicio podría retirar mensajes de la cola.

RETENTION:Indica si la cola debe mantener todos los mensajes hasta que la conversación finalice

ACTIVATION STATUS: Indica si se debe activar el procedimiento almacenado cuando llegue un mensaje a la cola

MAX_QUEUE_READERS: Indica la cantidad máxima de servicio que correrán simultáneamente

EXECUTE AS: especifica la cuenta de usuario con que correrá el servicio

Para modificar esta es la sintaxis

ALTER QUEUE [database_name.[schema_name].schema_name.] queue_name
[ WITH
[ STATUS = { ON OFF } [ , ] ]
[ RETENTION = { ON OFF } [ , ] ]
[ ACTIVATION (
[ STATUS = { ON OFF } , ]
PROCEDURE_NAME = stored_procedure_name,
MAX_QUEUE_READERS = max_readers ,
EXECUTE AS { SELF 'user_name' OWNER }
] DROP })
]

Para eliminar una cola la sintaxis es:

DROP QUEUE [database_name.[schema_name].schema_name.] queue_name

SERVICE: Relaciona las colas con los contractos

CREATE SERVICE service_name
[ AUTHORIZATION owner_name ]
ON QUEUE [ schema_name. ]queue_name
[ ( contract_name [DEFAULT] [ ,...n ] ) ]

Para modificar el servicio utilice la siguiente sintaxis:

ALTER SERVICE service_name
[ON QUEUE [schema_name].queue_name]
[(ADD CONTRACT contract_nameDROP CONTRACT contract_name)]


ON QUEUE: Especifica la nueva cola para el servicio y mueve todos los mensajes de la cola vieja a la nueva

ADD CONTRACT: Añade un contrato a la colección de contratos asociados a este servicio

DROP CONTRACT: Especifica los contratos que se eliminaran del servicio. En caso de que alguno se este ejecutando mostrara un mensaje de error.

Para comenzar a usar los servicios se deben establecer una conversación. Estos son los pasos para establecer una conversación.

Crear la variable que identificara de manera única la conversación.

DECLARE @dialog_handle uniqueidentifier

Iniciar la conversación

BEGIN DIALOG [ CONVERSATION ] @dialog_handle
FROM SERVICE initiator_service_name
TO SERVICE 'target_service_name'
[ , { 'service_broker_guid' 'CURRENT DATABASE' } ]
[ ON CONTRACT contract_name ]
[ WITH
[ { RELATED_CONVERSATION = related_conversation_handle
RELATED_CONVERSATION_GROUP = related_conversation_group_id } ]
[ [ , ] LIFETIME = dialog_lifetime ]
[ [ , ] ENCRYPTION = { ON OFF } ] ]

RELATED_CONVERSATION o RELATED_CONVERSATION_GROUP relaciona un nuevo dialogo con una conversacion existente

LIFETIME= tiempo en segundo en la que será valido el dialogo

Enviar mensaje

SEND
ON CONVERSATION conversation_handle
[ MESSAGE TYPE message_type_name ]
[ ( message_body_expression ) ]

El destino recibe el mensaje

[ WAITFOR ( ]
RECEIVE [ TOP ( n ) ]
[ ,...n ]
FROM
[ INTO table_variable ]
[ WHERE { conversation_handle = conversation_handle
conversation_group_id = conversation_group_id } ]
[ ) ] [ , TIMEOUT timeout ]

WAITFOR: especifica que la clausula RECEIVE espera un mensaje

RECEIVE: lee los mensajes de la cola y los elimina en caso de que la opción RETENTION de la cola este desactivada.

TOP: Indica cuantos mensajes se van a leer de la cola, sino se especifica se leerán todos los mensajes

INTO: Ingresa todos los mensajes en una tabla para ser tratados después

WHERE: especifica la conversación o grupo de conversaciones para los mensajes leídos

TIMEOUT: Especifica el tiempo en milisegundos en que la instrucción espera un mensaje.

Para terminar la conversación

END CONVERSATION conversation_handle
[ [ WITH ERROR = failure_code DESCRIPTION = 'failure_text' ]
[ WITH CLEANUP ]

Ejemplo de todo el proceso

CREATE MESSAGE TYPE TicketRequest AUTHORIZATION dbo
VALIDATION = WELL_FORMED_XML

CREATE MESSAGE TYPE TicketStatus AUTHORIZATION dbo
VALIDATION = WELL_FORMED_XML

CREATE QUEUE SenderQUEUE

CREATE QUEUE ReceiverQUEUE

CREATE CONTRACT Ticketservicescontract
(TicketRequest SENT BY INITIATOR,
TicketStatus SENT BY TARGET)

CREATE SERVICE SendTicketingService ON
Queue SenderQUEUE
(Ticketservicescontract)

CREATE SERVICE ReceiveTicketingService ON
Queue ReceiverQUEUE
(Ticketservicescontract)

declare @message xml
declare @conversationhandle UNIQUEIDENTIFIER
set @message =
'
dandy@ilikesql.com
5
Sybex SQLTraining
' '

BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE SendTicketingService
TO SERVICE 'ReceiveTicketingService'
ON CONTRACT Ticketservicescontract
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE TicketRequest
(@message);
END CONVERSATION @conversationHandle

CREATE PROCEDURE STP_ProcessQueue
AS
BEGIN
DECLARE @conversation_handle UNIQUEIDENTIFIER,
@message_body XML,
@message_type_name NVARCHAR(128);
RECEIVE TOP(1)
@conversation_handle = conversation_handle,
@message_type_name = message_type_name,
@message_body = message_body
FROM [dbo].[ReceiverQUEUE]
-- DO SOMETHING with the message
if @message_type_name = 'TicketRequest'
BEGIN
-- EXEC stp_processticketrequest @message_body
SELECT @message_body
END
END CONVERSATION @conversation_handle ;
END
GO

ALTER QUEUE ReceiverQUEUE
WITH ACTIVATION (STATUS = ON,
PROCEDURE_NAME = STP_ProcessQUEUE,
EXECUTE AS OWNER)
GO

EXEC STP_ProcessQUEUE

XML

SQL SERVER 2005 incluye el manejo de datos de tipo XML en su base de datos, la cual proporciona diferentes herramientas para consultar, modificar Y validar datos XML.

Dentro de la posibilidades de manejo de XML en SQL SERVER están:

Almacenamiento nativo de datos XML
Conversión de datos relacionales a estructuras XML por medio de la Clausula FOR XML
Realizar consultas por medio de la sentencia OPENXML
Usar expresiones XQUERY
Creación de Indexes XML
Validación de estructuras XML por medio Esquemas XML

Clausula FOR XML

Esta clausula sirve para transformar información relacional en documentos XML.

Sintaxis

FOR XML
{
{ RAW [ ( 'ElementName' ) ] AUTO }
[

[ , { XMLDATA XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ]
[ , ELEMENTS [ XSINIL ABSENT ]
]
EXPLICIT
[

[ , XMLDATA ]
]
PATH [ ( 'ElementName' ) ]
[

[ , ELEMENTS [ XSINIL ABSENT ] ]
]
}
::=
[ , BINARY BASE64 ]
[ , TYPE ]
[ , ROOT [ ( 'RootName' ) ] ]

Para los siguientes ejemplos utilizaremos la siguiente consulta a la BD AdventureWorks

El significado de los parámetros es el siguiente:

*RAW: Transforma cada fila en un elemento XML que tiene un identificador genérico.
Si se especifica RAW (indentificador) el resultado seria el siguiente:

*AUTO: Devuelve un XML anidado donde la información de cada tabla utilizada en la consulta se convierte en un elemento

*EXPLICIT: especifica el formato de XML

La siguiente consulta devuelve un formato parecido a resultado del ejercicio anterior.

*PATH: Especifica un formato XML de una manera mas sencilla que utilizando la opción explicit
















*ELEMENTS: Devuelve las columnas como elementos en vez de atributos, aplica para las opciones AUTO y RAW

Ejemplo de auto con elements

*ROOT: coloca un elementos raíz en el resultado de la consulta

*TYPE: Devuelve los resultados como tipo de datos XML

*XMLDATA: Incluye en los resultados el schema (XDR)

*XMLSCHEMA: incluye en los resultados el schema (XSD)

*XSINIL: crea elementos para los valores nulos

OPEN XML

OPEN XML Transforma datos XML en datos relacionales.

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]])
[WITH (SchemaDeclaration TableName)]

Los siguientes ejemplos se realizaran basados en el siguiente XML

Para comenzar debemos declara las variable que almacenara el XML y el puntero al documento XML preparado

Después preparamos el documento para que nos pase el puntero que necesita el OPEN XML para realizar la consulta

Como podrán observar a la instrucción se le paso la variable @Hdoc que tiene el puntero a la representación del documento XML, se especifico la ruta @ '/ROOT/Customers/Orders/Order_Details' para tenerlo como referencia de búsqueda en los valores

Las variables que están en el Xpath se especifican con una @ mas el nombre del atributo, los que están en elementos superiores deben especificar tantos ../ necesarios para ubicarse en el elemento respectivo y los que están en elementos inferiores deben especificar la ruta .

Si se quiere extraer el contenido de un elemento se puede realizar especificando el xpath y en ves de utilizar @NombreVariable se coloca Text()

XQUERY

Es una herramienta que se utiliza para realizar consultas sobre fuentes de datos XML y esta implementado en los tipos de datos XML de sql server 2005

Posee 5 métodos:

*Query
*Value
*Exist
*Node
*Modify

Para la explicación vamos a utilizar el siguiente documento XML

Metodo Query

Sirve para extraer fragmentos XML por medio de expresiones XQUERY

Metodo Value

Sirve para devolver un único valor

Metodo Exist

Sirve para determinar la existencia de un nodo en el documento, devuelve 1 si encuentra alguna coincidencia en caso contrario devuelve 0






Metodo Modify (XML DML)

Sirve para insertar, modificar y eliminar nodos en un XML

Clausula insert

insert Expression1 (
{as first as last} into after before
Expression2 )

Clausula replace

replace value of Expression1 with Expression2

Clausula delete

delete Expression

Metodo Nodes

Extrae los datos XML en formato relacional

Este método también se puede utilizar en columnas tipo XML de la siguiente manera

Inserciones masivas

INSERT INTO Tbl_orders(Xmldata)
SELECT * FROM OPENROWSET(
BULK 'c:\Sybex\OpenRowsetXmldata.xml',
SINGLE_BLOB) AS x

Se utiliza Single_Blob ya que admite mas codificaciones de Windows

SINGLE_CLOB: lee los datos com ASCII
SINGLE_NLOB: lee los datos como Unicode

Índices XML

Para crear indices XML debe cumplirse los siguientes requerimientos

*La tabla que contiene la columna XML debe tener una llave primaria Closterado
*Si cambias la llave primaria debes eliminar todas los índices XML y volver a créalos después de realizar al cambio en la llave primaria
*Puedes tener un primary index xml por columna
*No se pueden tener un xml índice y un no xml índice con el mismo nombre en la mimas tabla
*No puedes usar las opciones IGNORE_DEP_KEY y ONLINE cuando creas el índice
*No puedes crear índices XML en vistas, Variable tipo tabla ni variables XML
*Debes eliminar un índice XML si quiere cambiar el tipo de índice
*Debes activar la opción ARITHABORT cuando creas un índice XML o cuando realizas alguna acción DML en la columna XML

Los índices XML están compuesto por un índice primario y un secundario

El índice XML primario realiza una representación B-tree delos nodos XML. La sintaxis de creación es la siguiente

CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
ON Production.ProductModel (CatalogDescription)

El Índice XML secundario es un índice no Closterado del índice primario. Existen 3 tipos.

Índice PATH : mejora el rendimiento de consultas que utilizan PATH y VALUE para seleccionar datos, como por ejemplo la consultas que utilizan el método exist en el where de una consulta. /ItemList/Item[@ProductID="1"]

CREATE XML INDEX XMLPATH_ProductModel_CatalogDescription
ON Production.ProductModel(CatalogDescription)
USING XML INDEX PXML_ProductModel_CatalogDescription
FOR PATH

Indice VALUE: mejora el rendimiento de consultas que utilizan VALUES para seleccionar datos. Ideal para busquedas de un valor que puede aparecer en diferentes partes del xml. //Item[@ProductID="1"]

CREATE XML INDEX XMLVALUE_ProductModel_CatalogDescription
ON Production.ProductModel(CatalogDescription)
USING XML INDEX PXML_ProductModel_CatalogDescription
FOR VALUE

Indice PROPERTY: mejora el rendimiento de consultas que utilizan VALUES para extraer datos-(/ItemList/Item/@ProductID)[1]

CREATE XML INDEX XMLPROPERTY_ProductModel_CatalogDescription
ON Production.ProductModel(CatalogDescription)
USING XML INDEX PXML_ProductModel_CatalogDescription
FOR PROPERTY

Snapshot

Una Snapshot es la representación de una base de datos en un momento determinado.

Esta base de datos es de solo lectura y debe estar en el mismo servidor donde esta la base de datos original. No se puede realizar un backup ni mucho menos restaurarla.

La única manera de crear un Snapshot es por código T-SQL

Una Snapshot solo almacena copias de paginas de datos que fueron modificadas después de realizarse el Snapshot, de esta manera cuando se realiza un consulta sobre el Snapshot el motor de la BD realiza la búsqueda en las paginas de datos de la BD y en las de el Snapshot y de esta manera devuelve los resultados de la fecha correspondiente al Snapshot

La sintaxis es la siguiente

CREATE DATABASE Snapshotname ON
( NAME = Logicalfile , FILENAME =
'Physicalfilename' )
AS SNAPSHOT OF SnapshotThisDatabase;

Ejemplo:

CREATE DATABASE Prueba_10AM ON( NAME = Prueba, FILENAME = 'C:\BD\Prueba_10AM.ss' )AS SNAPSHOT OF Prueba;

Después de creado el Snapshot este puede ser utilizado como método para recuperar información perdida o dañada accidentalmente.

Existen 4 escenarios donde se puede recuperar información de in Snapshot

Registros borrados

INSERT INTO Ventas
SELECT *
FROM
Prueba_10AM.Ventas

Registros modificados

UPDATE ventas
SET Total = (
SELECT Total
FROM
Prueba_10AM.Ventas
WHERE anio = 2009)
WHERE anio = 2009

Objetos borrados

Se genera el script de creación del objeto, se corre en la base de datos y se rellena la tabla con la información del Snapshot

Restaurar todo el Snapshot

RESTORE DATABASE Prueba
FROM DATABASE_SNAPSHOT = 'Prueba_10AM'

Log Shipping

Log Shipping es una técnica de alta disponibilidad en la cual las transacciones realizadas sobre un servidor principal son restauradas periódicamente en otro servidor dejando al segundo servidor disponible para realizar consultas. Esta tecnología no realiza automatic Fail over

Entre mas frecuente sean la realización de los backups de los log, el servidor de contingencia estará mas actualizado.

Opcionalmente tu puedes crear un monitor server, que vigilara el comportamiento del log shipping. Este servidor debería estar en otro servidor diferente al principal y el de contingencia

Como prerrequisito para configurar el log shipping tu debes realizar lo siguiente:

Crear una carpeta compartida donde se ubicaran los backups de los logs. Preferiblemente esta carpeta debería estar en otro servidor que no sea el principal para que los servidores secundarios puedan acceder a esta aunque el servidor principal este caído

Crear una carpeta compartida en cada uno de los servidores secundario, para que el log shipping copie los archivos allí


Los Procedimiento almacenados para configurar manualmente el log shipping son

sp_add_log_shipping_primary_database: Configura el Log shipping en el servidor primario incluyendo el Job del backup y el monitor
sp_add_jobschedule: Crea un nuevo Programa de trabajo
sp_add_log_shipping_alert_job: Crea un job de alertas
sp_add_log_shipping_secondary_primary: Configura la información del servidor primario, añade los monitores y copia y restaura jobs en el segundo servidor
sp_add_log_shipping_secondary_database: Configura el servidor secundario para log shipping
sp_add_log_shipping_primary_secondary : Añade servidores secundarios al principal

Para intercambiar los roles se deben realizar los siguientes pasos

*Copiar los backups de la carpeta compartida del servidor principal a la carpeta compartida del secundario
*Si el servidor primario esta disponible , se debe realizar un backup de log con la opción NORECOVERY
*Restaure todos los logs en el servidor secundario con la opción RECOVERY
*Deshabilite los job del logshipping en el servidor Primario y deshabilite los jobs de restauración en el secundario
*Reconfigurar el Servidor secundario como primario.

Database Mirroring

Database mirroring es una solución de alta disponibilidad implementada en hardware comunes.

En una solución de Database mirroring pueden actuar tres participantes con diferentes roles.

Principal Server: Es el servidor que mantiene la copia activa de la BD que están utilizando los clientes. Cada transacción es realizada al tiempo en el servidor y en el espejo
Mirror Server : Mantiene la copia de la Bd principal sincronizada
Witness Server: Es un componente adicional (Instancia o equipo) que monitoriza el estado del los servidores y en caso de presentarse una falla en el principal, habilita al Mirror como Principal

Existen tres tipos de modos de configuración de Database Mirroring

Alta Disponibilidad: Garantiza la consistencia transaccional entre el servidor principal y el espejo y ofrece Automatic Failover por medio de un Testigo
Alta Protección: Garantiza la consistencia transaccional entre el servidor principal y el espejo
Alto Rendimiento: Aplica la transacciones en el Servidor Espejo de manera asíncrona ocasionando mejoras significativas en el rendimiento del servidor principal pero no garantiza que dichas transacciones se hallan realizado de manera exitosa en el espejo.

Para configurar un el Data base Mirroring tu debes realizar los siguientes pasos

*Crear los mirroring Endpoints y logins.
*Configurar el modo de recuperación FULL
*Realizar un Backup de la BD principal y restaurarla en el espejo
*Copiar cualquier recurso adicional en el servidor espejo

La comunicación entre los servidores se hace por medio de Endpoints

Un ejemplo de un EndPoints seria:

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 4099 )
FOR DATABASE_MIRRORING
(
AUTHENTICATION = WINDOWS KERBEROS,
ENCRYPTION = SUPPORTED,
ROLE=ALL);

Estos Endpoint deberían ser creados en cada uno de los componentes de la solución especificando su respectivo role

Los roles pueden ser ALL, PARTNER o WITNESS

Para que los participantes sepan quienes son los otros componentes es necesario configurar la base de datos para que los conozca

En el servidor Principal debemos ejecutar esta instrucción para que el conozca quien es su compañero

ALTER DATABASE PRUEBA SET PARTNER ='TCP://MIRROR.DOMINIO.com:5022'

En el Servidor espejo debemos ejecutar esta instrucción

ALTER DATABASE PRUEBA SET PARTNER = 'TCP://PRINCIPAL.DOMINIO.com:5022'

Y si tenemos un testigo debemos ejecutar la siguiente instrucción en ambos servidores

ALTER DATABASE PRUEBA SET PARTNER = 'TCP://TESTIGO.DOMINIO.com:5023'

Existen otras configuraciones que se pueden realizar con ALTER DATABASE como son:

Safety: Esta opción puede tomar dos valores FULL o OFF. Cuando esta configurada como FULL las transacciones se realizan sincrónicamente y se garantiza el Fails over automático. Cuando esta en OFF las transacciones se realizan asíncronamente

ALTER DATABASE Prueba SET PARTNER SAFETY FULL

Failover: Realiza manualmente un fail over del principal al espejo. Esto se ejecuta en el Principal


ALTER DATABASE Prueba SET PARTNER FAILOVER

Force_Service_Allow_Data_Loss: Forza al espejo a tomar el rol de principal. Esta se utiliza cuando el principal no esta disponible y el testigo esta Off

ALTER DATABASE Prueba SET PARTNER
FORCE_SERVICE_ALLOW_DATA_LOSS

Servidores Linkiados

Un Servidor linkiado te permite realizar consultas sobre servidores remotos. Deben ser configurados cuando se utilizan frecuentemente

La sintaxis es la siguiente:

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] [ , [ @provider= ] 'provider_name' ] [ , [ @datasrc= ] 'data_source' ] [ , [ @location= ] 'location' ] [ , [ @provstr= ] 'provider_string' ] [ , [ @catalog= ] 'catalog' ]

Ejemplo: Linkiar BD Access

EXEC sp_addlinkedserver @server = 'SEATTLE Mktg', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'

Linkiar archivo de Excel

EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:\MyData\DistExcl.xls', NULL, 'Excel 5.0'

Para asociar un inicio de session a una cuenta remota en n linkserver se utiliza este procedimiento

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' [ , [ @useself = ] 'TRUE' 'FALSE' 'NULL'] [ , [ @locallogin = ] 'locallogin' ] [ , [ @rmtuser = ] 'rmtuser' ] [ , [ @rmtpassword = ] 'rmtpassword' ]

Si se especifica la variable @locallogin como NULL, esto significa que todos los inicios de sesión que no estén mapeados seguirán dicha configuración

Ejemplo

Asociar el usuario julio al servidor remoto Externo con el usuario julioexterno

EXEC sp_addlinkedsrvlogin 'Externo', 'false', 'julio', 'julioexterno', 'qdwe98hg'

Para quitar la asignacion utilizamos el siguiente código

EXEC sp_droplinkedsrvlogin 'Externo', 'julio'

Para listar los servidores linkiados se utilizar el procedimiento almacenado sp_linkedservers .