Transcript Database mirroring

DATABASE MIRRORING
WITH SQL SERVER
DBA
José de Jesús Castillo Rodriguez (Chuy)
QUIEN ES CHUY?
 Licenciado en Informática egresado de la Universidad




de Guadalajara 98 -2002 (CUCEI)
Maestría en Administración de empresas en la
Universidad de Guadalajara 2004-2007(CUCEA)
14 Años de experiencia en el Ramo de IT
Experiencia Laboral :
Jefe de Sistemas en PyMes
Administrador de Aplicaciones en :
Jabil Circuit de Mexico.
Administrador de Bases de Datos en:
HCL Technologies de Mexico
AGS Nasoft (Actual)
Certificaciones:
MCTS SQL Server 2008 R2
# E294-3245
MCSA SQL Server 2012/2014 # F680-8720
MCTS Windows Server® 2008 Applications
Infrastructure, Configuration
#E294-3244
WHAT IS DATABASE MIRRORING?
• Database mirroring is a strategy for increasing the availability of a
database.
• Database mirroring is the process of creating and maintaining an always
up-to-date copy of a database on another SQL Server instance.
• Transactions applied to the database on the principal instance are also
applied to the database on the mirrored instance
WHAT IS DATABASE MIRRORING?
• Clients interact with the database hosted on the principal server.
• Database mirrors are paired copies of a single database that are hosted
on separate instances of SQL Server.
“Although it is possible to use mirroring with instances installed on the
same hosts.”
Note -- Mirroring Future: Microsoft intends to remove mirroring in a
future version of SQL Server. Plan on implementing AlwaysOn availability
groups.
MIRRORING PREREQUISITES
• The primary and the mirror instances must run the same SQL Server edition, whereas
the witness instance must run only an edition of SQL Server that supports witnessing.
■ Enterprise Supports high-performance, high-safety, and witness modes
■ Business Intelligence Supports high-safety and witness modes
■ Standard Supports high-safety and witness modes
■ Web Supports witness mode only
■ Express (all versions) Supports witness mode only
MIRRORING PREREQUISITES
• You can mirror only user databases. It is not possible to mirror the master, msdb,
•
•
•
•
•
tempdb, or model databases.
You cannot rename mirrored databases.
You cannot configure database mirroring for a database that contains FILESTREAM
filegroups.
You cannot create FILESTREAM filegroups on the principal server.
Cross-database and distributed transactions are not supported for mirrored databases.
You must configure the database to use the full recovery model before you can
mirror it
MIRRORING MODES
•
Depending on how you configure mirroring, the mirror instance functions in either one of the below modes:
■ High-safety mode :
•
Enables failover to occur without data loss from committed transactions.
•
Hot standby is possible when you are using the high-safety mode. In high safety mode, transactions are committed on
both partners after they are synchronized.
•
The drawback of high-safety mode is an increase in transaction latency. If a witness Server is present, you can enable
automatic failover with this mode.
■ High-performance mode:
•
Enables failover to occur, but data loss is possible. In high performance mode, the primary instance does not wait for
the mirror instance to confirm that it has received the log record.
ROLE SWITCHING AND FAILOVER
Depending on the mode that the mirrored session is configured to use, three types of role switching are available:
■ Manual failover
Manual failover is available if the mirroring session is configured in high-safety mode. You can initiate manual failover
when the mirroring session is synchronized. Database administrators often perform manual failover when they need to
apply updates or service packs that require a restart
USE master;
ALTER DATABASE DatabaseName SET PARTNER FAILOVER;
■ Automatic failover
Automatic failover If a witness is present and the mirroring session is configured for high-safety mode, automatic failover
can occur when the witness and mirror are still connected, but the principal server cannot be contacted.
ROLE SWITCHING AND FAILOVER
■ Forced service (with possible data loss)
Forced service can be used in high-safety mode when no witness is present or in high-performance mode. In this mode,
the administrator forces the mirror to become the principal when the original principal instance becomes unavailable
ALTER DATABASE DatabaseName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
MIRRORING CONFIGURATIONS
Based on Security , you can configure Mirroring using the below options:
 Mirroring with Windows Authentication:
You can configure mirroring with Windows-based authentication if the SQL Server service accounts on the principal,
mirror, and witness are members of the same Active Directory domain or trusted domains
 Mirroring with Certificate Authentication:
You use certificate-based authentication when configuring mirroring between instances when a domain-based account
is not used as the service account for SQL Server. You cannot configure certificate-based authentication for mirroring
by using SQL Server Management Studio you must perform this operation by using Transact-SQL
DEMO
CONFIGURING MIRRORING WITH WINDOWS
AUTHENTICATION
GRACIAS