Effective Usage of SQL Server 2005 Database Mirroring

Effective Usage of SQL Server 2005 Database Mirroring by Satya SK Jayanty SQL Server MVP sqlmaster@sqlserver-qa.net Microsoft Campus, Reading UK October 06th 2007

Agenda Introduction SQL Server 2005 High Availability features Feature Overview Database Mirroring Setup (overview) Monitoring & Performance Understanding problems in Failover & Failback Conclusion Frequently Asked Questions Q & A Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Introduction – Speaker (Who Am I) • Webmaster(SQLMaster) of www.sqlserver-qa.net & www.sqloogle.co.uk • Been in the IT field over 15+ years (SQL Server Specialist Consultant) • SQL Server DBA for over 11 years (working since ver. 4.2) • Contributing Editor, Writer & Moderator for www.sql-server-performance.com • SQL Server MVP (Microsoft Most Valuable Professional) • Participation in assorted forums such as MSDN, SQL Server magazine, dbforums and so on. • Principal Consultant & Director – SSQA.net Limited (www.ssqa-net.co.uk) Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

SQL Server 2005 High Availability features • High Availability is a set of best practices and technologies designed to increase database availability through backups that help recover from catastrophic disasters, redundant database components engineered to prevent downtime, and redundant server implementation designed to minimize downtime. • What it takes? How much it cost? Can you handle a disaster? Tips & Tricks! • Failover clustering and Database mirroring both provide the Server & Database High Availability features. Constraints in Failover Clustering • Operates at the server instance scope not to a specific database • Requires certified hardware (HAL) • Has no reporting on standby (either Active/Active or Active/Passive) • Utilizes a single copy of the database • Does not protect against disk failure • High costs to implement Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

SQL Server 2005 High Availability features • Database Mirroring benefits for the constraints above: • Operates at the database scope • Uses a single, duplicate copy of the database • Uses standard servers - no HAL • Provides limited reporting on the mirror server by using database snapshots (Enterprise) • Database Mirroring - What is the advantage over Failover Clustering? • You can mix & match database mirroring with a failover clustering. • Database mirroring works easily between failover clusters and also between a failover cluster and a non-clustered host. • Configuration - High Protection & High Performance. • No licensing cost to setup witness server. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

SQL Server 2005 High Availability features Database Mirroring - What is the advantage over Failover Clustering? • Database mirroring & log shipping • This combination can be used to provide high availability for your notification applications, you can maintain a standby server that you can bring online after failures or during server maintenance. • Benefits of – increased data protection, increased availability of databases during any hardware or software patching. • It is recommended that you first learn the High Performance configuration and then move to the High Protection configuration (more about this in upcoming slides). • If using witness server, two of the three servers must be able to communicate to determine principal. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Feature Overview SQL Server 2005 Editions for Database Mirroring • Enterprise Edition - Fully supported (failover partners must use the same edition) • Standard Edition - Safety full only • Workgroup Edition, SQL Express & SQL Express with Advanced services – Only as witness • SQL Express and the Workgroup Edition can be used as a witness server, but they cannot be used as a partner server in database mirroring. • When operating synchronously, provides for zero work loss through delayed commit on the principal database. • Only one mirror (copy) can exist per DBM session for the purposes of failover. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Feature Overview Database Mirroring (DBM) features • Database mirroring transfers transaction log records directly from one server to another and can quickly fail over to the standby server. • An originating SQL Server 2005 instance continuously sends a database's transaction log records to a copy of the database on another standby SQL Server instance. • Nodependency on proprietary hardware (cost and complexity), and easy to setup. • SQL Server 2005 supportsdatabase mirroring between 64 bit & 32 bit editions. • System databases cannot be a part of mirroring (none of High Availabilityfeatures). • Fast failover- with minimal data loss has traditionally involved higher hardware cost and greater software complexity. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Feature Overview • Database Mirroring (DBM) Terms you come across: • Principal – the server that holds a copy of database that is accessible to client applications at any given time. • Mirror – the server that holds copy of database that is always in restoring state that is not accessible to the applications. • Witness – the optional server that is useful to provide an automatic failover mechanism in case of any failure on principal server. • Send Queue - a queue (principal) that builds up when the log records can’t be sent at the rate at which they are generated from principal to mirror. • Redo Queue – a queue (mirror) that builds up when the log records can’t be applied at the rate at which they are received from principal server. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Feature Overview • Database Mirroring (DBM) Terms you come across: • Endpoint: A SQL Server object that enables Principal, Mirror & Witness servers to communicate over the network. • Encryption - Data between the principal and the mirror is transferred encrypted by default. • Transaction safety level – that determines whether the changes on the principal database are applied to the mirror database synchronously or asynchronously (FULL & OFF). • Synchronous - the mirror server must synchronize the mirror database with the principal database. Speeds up the writes all of the incoming log records to disk as quickly as possible. • Asynchronous – this sets up database mirroring with high performance. With safety OFF, the transaction is committed as soon as the principal server writes the log record to the local log and sends the log record to the mirror, without waiting for an acknowledgement from the mirror server. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Database Mirroring Setup Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Database Mirroring SetupENDPOINT configuration & Security Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Database Mirroring SetupModes High Availability Supports automatic detection and automatic failover in the event of a disaster by requiring a third instance to act as a Witness server. FULL SAFETY form If the Principal Server database fails then the Witness server will co-ordinate with the Mirror server to have the Mirror database come online automatically to service client requests (normally within 10 seconds). Suitable for automatic failover scenario such as no application downtime is desired. Automatic failover requires the database is already synchronized. The failure occurs while all three server instances are connected, and the witness and mirror server remain connected. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Database Mirroring SetupModes High Protection No support for automatic detection or automatic failover in the event of a disaster and it does not require or use a Witness. • Still uses “full safety” mode (similar to High Availability configuration) only with the exception of the setup of the Witness server. • Suitable when automatic failover not required and for more control on the failover scenario. • The witness should be set only when you intend to use high-safety mode with automatic failover. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Database Mirroring SetupModes High Performance Uses an asynchronous (“full safety off”) form of mirroring, as transaction changes are not required to be sent immediately to the Mirror Database. High-performance mode, supports only one form of role switching: forced service (with possible data loss), which uses the mirror server as a warm standby server. Does not support automatic detection or automatic failover or use a Witness. That needs WITNESS property to OFF. Mode allows the Principal Database to focus on client requests (performance) without the need to immediately and synchronously send the changes. High-performance mode can be useful in a disaster-recovery scenario in which the principal and mirror servers are separated by a significant distance and where you do not want small errors to impact the principal server. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Database Mirroring Setup • Another configuration mode asynchronous mode with no witness server – although this is possible to setup it is not recommended because it combines the risk of data loss(Forced Service) and split-brain scenario. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Database Mirroring Setup • When service is forced, whatever transaction logs the principal has not yet sent to the mirror server are lost. Therefore, you should limit forced service to situations where possible data loss is acceptable and immediate database availability is critical. • Use forcing service only if you must restore service to the database immediately and are willing to risk losing data. • Standard Edition allows only the FULL transaction safety level. • It is recommended that you first learn/setup the High Performance configuration and then move to the High Protection configuration. Then, if desired, you can move to the High Availability configuration. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

DBM - Monitoring & Performance • Database Mirroring Monitor (DMM) [sp_dbmmonitorresults] – best to use: • What is the session status & role of a partner? • Determine whether the mirror database is keeping up with the principal database. • Determine how much data was lost when the principal server instance becomes unavailable during high-performance mode. • Compare current performance with past performance. Within upcoming release of SQL Server 2008:Is the mirror restoring transaction log? • How fast is log being restored? • How far is the mirror database behind? • When will the mirror database catch up? • Has the ability to configure Alerts around performance Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

DBM - Monitoring & Performance • Database state: SYNCHRONIZING, SYNCHRONIZED, SUSPENDED, PENDING_FAILOVER & DISCONNECTED. • A major component of the database failover time can be the redo phase. So the log backups after full backups is most important to reduce the redo phase time. • Another reason that the mirror could be falling behind is if the network bandwidth can not support at least double the maximum transaction log generation rate. • sys.databases - view the log_reuse_wait_desc column for why space in the log cannot be reused. (View Factors That Keep Log Records Active topic in BOL ) • Above sequence can also be performed by using Dynamic Management Views. • Also by using performance monitor (SYSMON) counters & Profiler. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

DBM - Monitoring & Performance • SYSMON counters on each partner server helps you to monitor the performance of database mirroring, few of them are important to watch: • SQLServer:Database Mirroring • Databases - Transactions/sec On Principal server Log Bytes Sent/sec Log Send Queue KB Log Bytes Flushed/sec Disk Write Bytes/sec On Mirror server Redo Bytes/sec Redo Queue KB Disk Write Bytes/sec Other Logical Disk counters Tip: When capturing counters Witness Server is best to use (if installed separately). • Transactions/Sec Perfmon counter is does not show same amount on both principal and mirror databases but mirror is not running behind. • One way to measure the actual failover time is to use SQL Server Profiler [Database Mirroring State Change event] on the principal and the mirror [Audit Database Mirroring Login event ]. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

DBM - Monitoring & Performance • Preparing (testing)the Mirror for Failover • It is better to use identical servers (in terms of CPU, memory, storage, and network capacity) as principal and mirror partners. • Same service pack and patch levels for the operating system and SQL Server on both partners. • If used Automatic Failover ensure that both servers have the same directory structure. And also all active SQL Server logins (and their permissions) on the principal server are also present/applied on the mirror server. • Ensure that all SQL Server Agent jobs, alerts, linked server definitions & SSIS packages are present in mirror server (disabled) and you may need them to enable in the event of a failover. • TCP Endpoint on each server and configure authentication on endpoint with CONNECT permission appropriately. • The above preparation lists are also applicable to the system databases between the principal and mirror servers. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

DBM - Monitoring & Performance Troubleshooting • Database mirroring does not regularly check the components on which Sqlservr.exe relies to verify whether they are functioning correctly or have failed. • Failures in databases other than the mirrored database are not detectable in a database mirroring session. Moreover, a data disk failure is unlikely to be detected, unless the database is restarted because of a data disk failure. • To determine the cause of the mirror falling behind the principal, examine the SQL Server error logs on both servers for errors and check to see if the DBM Session could be suspended. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

DBM - Monitoring & Performance Troubleshooting • If database mirroring is not working, check the following to make sure the configuration is correct: • Look at the column STATE_DESC in the catalog view sys.database_mirroring_endpoints. • Endpoints compatible encryption settings • Look at the column IS_ENCRYPTION_ENABLED in the catalog view sys.database_mirroring_endpoints • Look at the columns TYPE_DESC and ROLE_DESC respectively of the catalog view sys.database_mirroring_endpointsfor the correct type and roles. • Ensure port numbers of the endpoints are the same as the corresponding port numbers specified in the SET PARTNER statements. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

DBM - Monitoring & Performance • With and Without Witness • Witness server assists Principal or Mirror server in forming a quorum. • If Principal server cannot connect to mirror server, still the quorum consists with Witness to keep up the database in service. • In case Principal server is down then Witness initiates failover where the Mirror can take on the role of new principal server. • When the partner servers cannot see each other, the partner servers contact the witness to see whether the witness can contact the other partner and verify that a failure has occurred. • Witness is not a single point of failure. • The location of Witness is dependant upon your network availability to avoid losing two different servers, and therefore quorum, if the power system fails. • Witness is not the most important member of the database mirroring session. • General recommendation of Witness location is to accompany Principal. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Understanding problems in Failover & Failback Common Errors you would get to see • Errors 1412 and 1478 are restore errors requiring mirror database to be restored again. • Error 1416 is a restore error requiring mirror database to be restored again. The mirror database is in the standby or recovered state. • Errors 1418 and 1486 are communication errors. You will need to check both sides of the communication as the problem server can not be identified via error messages. • Verify DBM endpoints are started • Query sys.database_mirroring_endpoints DMV to confirm that all servers are using the same authentication and encryption settings. • Use Configure Security interface in SSMS to specify the startup accounts for all servers involved in the DBM session. • To use NTLM or Kerberos authentication, the SQL Server instance startup account must be running with a non-local account • Error 1456 is also a communication error when adding a witness server to a DBM session - follow same workaround as per error 1418. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Understanding problems in Failover & Failback Database Mirroring Session uses a default 10 “pings”. If they missed then it declares as failure: • Fast Failures (nearly immediate) with SQL Server instance crashes • Endpoint port closes quickly • Network retry from partner quickly fails • OS says that the port is closed • Medium Failures (~10 sec) such as Catastrophic server failure • Power supply fails, Network retry from partner waits for timeout • SQL Server “ping” will most likely fail first and then Failover begins in 10 seconds • Slow Failures (~40 sec) such as corruption of the log drive on principal database • Pending IOs to the log drive queue up though “pings” are working fine • After 20 seconds, SQL Server issues IO warning • After 40 seconds, SQL Serve declares IO failure • Failover begins 40 seconds after log drive crashed Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Understanding problems in Failover & Failback • Monitoring & Resolution • Ensure TCP/IP is enabled from SQL Configuration Manager all the instances in database mirroring. • Named pipes can also be used, but a drawback is it does not use the TCP/IP retry algorithm, in many cases, a named pipes connection attempt may time out before connecting to a mirrored database. • When Database Mirroring appears to be running smoothly – don’t let yourself get a false sense of security. Things may be running smoothly but this is when you’ll want to be on the highest alert. • The name of an endpoint is changed when the port changes. If SQL Server is listening on dynamic ports, the port can change, changing the endpoint name, and dropping the associated endpoint permissions. • To avoid this security risk, do not set custom permissions on endpoints associated with dynamic ports, and do not change the order in which a TCP/IP endpoint occurs in the registry. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Understanding problems in Failover & Failback • Monitoring & Resolution • The database might contain changes made by transactions that are uncommitted at the recovery point. So it is better to keep the transactions in smaller batches to avoid lengthy time during failover time for a mission-critical database. • If redoing fails, the mirror server pauses the session by putting the database into the SUSPENDED state. The database owner must resolve the cause of the failure before resuming the session. • Client connection string supplied by the client contains information that the data access provider uses to connect to the database. • Connection string should contain the Network attribute to specify the network protocol. This ensures that the specified network protocol persists between connections to different partners. • Recommended protocol for connecting to a mirrored database is TCP/IP {Network=dbmssocn;} Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Understanding problems in Failover & Failback Monitoring & Resolution • Account under which the SQL Server instance is running has the necessary CONNECT permissions. • If SQL Server runs under a different user on one or more of these servers, then you need to grant CONNECT permission on the endpoint of a server to the login account of the other servers. • Make sure the port that is used for database mirroring endpoints is opened through the firewall, if used. • Critical - speed of error detection and, therefore, the reaction time of the mirroring session to a failure, depends on whether the error is hard or soft. • Few network components and some IO subsystems, have their own time-outs to determine failures. • Mirroring does not protect against problems specific to client accessing the servers, such as public network adapter on any partner server. • Database mirroring failover and failback procedures are dependant on the network components and server hardware in addition to the server/database configuration. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Conclusion To monitor: • To test the automatic failover you will shutdown the principal server and watch how the mirror server automatically comes online to handle requests. • If your application consists of multiple databases, develop an alert mechanism to detect if one database fails over while others don’t. In this case you might need a manual failover of other databases. • If there are multiple databases in an instance, you need to mirror each database individually in the event of manual failover. • In some cases (High Availability mode) failover can occur automatically while in others it must be forced. • Enable ALL of the threshold warnings for BOTH the principal and the mirror instance. • To understand how the system works without the option of automatically failing over, try using database mirroring without a witness for a time. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Conclusion • High Performance is the best configuration with which to start as it has the least impact to your production workload yet still offers a secondary database that’s close to current. • The performance of database mirroring is very closely associated with the type of application, transaction safety level, and network performance. • The recommended limit of 10 databases in 1 SQL instance is only approximate and not an exact number. The limit (memory and threads) can vary depending upon your application and workload. • When used with a high-bandwidth and low-latency network, database mirroring can provide a reliable high-availability solution against planned and unplanned downtime. Across geographically distant data centers, database mirroring can provide the foundation for a disaster-recovery solution. • Do not forget to test and plan for any sort of new introduction to your High Availability ranch, even changes in Database Mirroring mode. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Conclusion • SP Rolling upgrade • Upgrade the mirror first • Failover • Upgrade the new mirror • Failover again back to original Principal • Upgrade the mirror first • Wait for a low activity window • Switch to synchronous, and wait till SYNCHRONIZED • Follow process similar to one above • Switch back to asynchronous • Various runtime and DBCC CHECK* errors, such as 605, 2511, etc. • Unit of transfer between principal and mirror databases is a transaction log block. • Database and Transaction log backups mitigate hard drive failures if they are usable meaning that they can be restored. One of the biggest mistakes is to create backups on the same drive or set of drives that make a logical partition or LUN. Asynchronous mode Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Conclusion How DBM provides the highest level of data protection? Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Frequently Asked Questions • What is the slowest network that I can use with mirroring? • What and Why don’t some technologies work seamlessly after a failover? • How to disable database mirroring for a particular database, is it possible? • What is the usage of WITNESS instance in Database Mirroring setup? • Is it recommended to use 32-bit server as Witness for a 64-bit Database Mirroring Partner server? • Why client applications are not redirected to new principal database? This only works under the following conditions: • Client application using ADO. Net or JDBC driver • Connection string contains a database that is part of a DBM Session • Connection to server never changes database context (i.e. USE <database>) • Where to look for errors for Communication errors & Failover causes? • Service Pack/Hot fix upgrade? • Depending upon the rolling upgrades the service pack levels can be different between principal and mirror, but for steady state they must be identical. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Frequently Asked Questions • Useful Catalog Views & DMVs to monitor DMM: • Sys.database_mirroring, Sys.tcp_endpoints & Sys.server_principals • Sys.database_mirroring_witnesses • Sys.database_mirroring_endpoints • Sys.dm_db_mirroring_connections • Sys.database_recovery_status • Telnet & Ping • Using Telnet for endpoint test – better for initial port test but not useful for authentication or encryption mismatches checks. • Ping – reports the FQDN name of a server • DBM across Internet – it is possible however consider factors like allowing DBM endpoint to pass through firewall and additionally may need to disable IP Security. (overhead) Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Questions & Answers • I’ll do my best to answer your questions within the available time. • If not we can discuss within the Ask The Experts (ATE) area. • Useful links: • Database Mirroring in SQL Server 2005 & FAQ http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx • Blogs http://www.sqlserver-qa.net/blogs/ha http://www.sqlserver-qa.net/blogs/tools http://www.sqlserver-qa.net/blogs/perftune http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/05/database-mirroring-part-1-performance-base-line-and-network-stuff.aspx - • Webcast Implementing Database Mirroring by Kimberly L. Tripp, SQLskills.com – look at http://msevents.com search for event id 1032290565. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Thank you and have a great day & weekend ahead. Feedback is highly appreciated. Effective Usage of SQL Server 2005 Database Mirroring - www.sqlserver-qa.net

Effective Usage of SQL Server 2005 Database Mirroring