Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

SQL Server 2005 – Mirror Server

By Exforsys | on January 11, 2006 |
SQL Server 2005

SQL Server 2005 – Mirror Server

In this tutorial you will learn about Mirror Server in SQL Server 2005 – Preparing the Principal and Mirror Server, Establishing a Mirroring Session, Establishing a Witness Server, Executing Transactions, Simulating Principal Server Failure, Restarting the Failed Server, Terminating the Mirror Session and Configuring Database Mirroring.

Preparing the Principal and Mirror Server

Database mirroring is easy to set up and can be made self monitoring for automatic failover in the event of the principal server being unavailable. The first step is to configure the relationship between the principal server and the mirror server. This can be a synchronous mirroring with a witness server that provides the highest availability of the database. A drawback in this type of configuration is the need to log transactions on the mirror before such transactions being committed to the principal server may retard performance. Asynchronous mirroring with a witness server provides high availability and good performance. Transactions are committed to the principal server immediately. This configuration is useful when there is latency or distance between the principal server and the mirror. The third type of mirroring configuration is the Synchronous mirroring without the witness server. This guarantees that data on both servers is always concurrent and data integrity is of a very high order. However, automatic failover cannot occur as there are not enough servers to form a quorum decision on which server is to take the role of the principal server and which should be the mirror server.

Establishing a Mirroring Session

Database mirroring is done within a mirror session. A mirror session maintains information about the state of the databases, the mirroring partners and the witness server. The mirror server identifies the most recent transaction log record that has been applied to the mirror database and requests for subsequent transaction log records from the principal server. This phase is called the synchronizing phase.

Once synchronization is complete the principal server will transmit the transaction logs to the mirror server even as changes are made. The mirror database is continually rolled forward to match the principal database. The operating mode of the mirror database(synchronous or asynchronous) will determine whether the transaction log records are applied to the mirror database immediately or after the transactions have been recorded in the principal server.

The mirror session maintains information about the state of any witness servers. It ensures that the witness server is visible both to the principal and the mirror servers.

A mirroring session is terminated by a number of causes. There may be a communication or server failure. The principal server may fail and the mirror may become the principal server. This can happen automatically or manually depending on the operating mode. The session may also be terminated by the manual intervention of the Database Administrator using the TRANSACT-SQL ALTER DATABASE command. Mirroring may be terminated or suspended in the process

Establishing a Witness Server

A witness server is a must where the DBA wants to implement automatic failover and the configuration must be in the synchronous operating mode. The witness server is usually on a different computer from the principal and the mirror servers. However, one server can act as a witness for multiple mirror partnerships.

The ALTER Database command with the SET WITNESS clause is used on the principal server to create a witness server. The Witness server address is specified and the endpoint port is defined to act as the witness for the server_network_address parameter.

A witness server can be disabled. However, the mirroring session will continue even when the witness server is disabled. Automatic failover will no longer be possible.

Information about the witness server can be viewed in sys.database_mirroring_witnesses catalog view.

Executing Transactions

The ALTER DATABASE command has to be run on the mirror server specifying the principal server endpoint address and then the same has to be done on the principal server so that synchronization can commence. The operating mode has to then be selected. By default the operating mode is synchronous. This can be changed by running the ALTER DATABASE command with SET PARTNER SAFETY clause on either partner server. The saftety_mode parameter can be either OFF or FULL. The mirror partnership information can be viewed by running a query on sys.databases catalog view.

If the transaction safety is set to full, the principal and mirror servers operate on synchronous transfer mode. The transaction logs are hardened in the principal server and transmitted to the mirror and then the principal waits for the mirror to harden its logs and send its response. When the safety is OFF the principal does not wait for the acknowledgement of the mirror. In this instance the principal and the mirror may not be synchronized at all times.

Synchronous transfer guarantees that the mirror is a faithful image of the principal database transaction log

Simulating Principal Server Failure

A principal server failure can be simulated in test scenarios to ensure that failover is smooth. Failover implies that the mirror server takes over as the principal server and the mirror database will have to act as the principal database. The failover can be manual, automatic or forced.

Automatic failover occurs when the high availability operating mode is synchronous and the safety is FULL and a witness is part of the session. Manual occurs in high availability and high protection operating modes. Safety has to be full and the partner databases are synchronized. Forced service is used primarily in the High Performance mode with safety off.

Simulating Principal Server failure can be done by manual intervention of the DBA in an orderly way. The safety will have to be first set to FULL and the principal and the mirror databases synchronized. Manual failover can be invoked by invoking the ALTER DATABASE command on the principal server or by clicking the failover button in the Database Properties/Mirroring dialog in the Management Studio. A manual failover causes current users to be disconnected and all unfinished transactions to roll back. These transactions will then be recovered from the redo queue. The mirror assumes the role of the principal server and the two servers will negotiate a new starting point for mirroring based on their mirroring failover LNS.

If the principal server is no longer operating, and safety is OFF, forced service can be resorted to. This service causes some data loss.

Restarting the Failed Server

A failed server can be restarted and can be synchronized with the principal server or the mirror server as the case may be. Any suspending of transactions causes the log on the principal server to grow with the transactions being logged and stored. Once the mirror session is resumed, the principal transaction log is synchronized and written on to the mirror database log.

Terminating the Mirror Session

A mirror session can be manually terminated and the relationship between the servers can be ended. When a session is ended, all information about the session is removed from all servers and leaves both the principal server and the independent server with an independent copy of the database. The mirror server database will remain in the restoring state until it is manually recovered or deleted.

Configuring Database Mirroring

Configuring a mirror server includes configuring the mirror server and the database.

The server designated as the mirror must be accessible and trusted by the principal database server. Ideally both servers should belong to the same domain. The mirror server should also have sufficient memory and processing power to act as the principal server in the event of failover. It should be able to support users and applications without noticeable difference in the quality of service.

The mirror database must be created manually. The file structure must match the principal database file structure. Both databases must implement full recovery model. Once the mirror database is created, the latest full database backup of the principal database must be applied to the mirror using the RESTORE DATABASE command with the WITH NONRECOVERY clause.

The next step is to enable the communication mechanism through which the mirroring will take place. This implies creation of endpoints on both servers. The endpoint controls the Transmission Control Protocol(TCP) port on which the server listens for database mirroring messages. The endpoint also defines the role that it must perform. A server needs to have only one configured endpoint regardless of the number of mirroring sessions it participates in. However, each instance requires a unique port on which to listen.

The next step is to establish a mirroring session. The process of establishing a mirroring session has been discussed above. It involves creating a mirroring session using the ALTER DATABASE command on the mirror server first and then on the principal server. The server_network_address parameter will have to be specified. Then a partnership will have to be created on the mirror server, the operating mode will have to be changed and so on.

In this lesson we have examined the new enhanced high availability features of SQL Server 2005. We have also examined how database mirroring works and how a database can be configured for mirroring using different configuration options. In the lesson that follows we shall examine the replication features of this version of SQL Server.

« « Unit Testing: Why? What? & How?
SQL Server 2005 Replication Enhancements » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • SQL Server Management Objects

    November 1, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 Installation – Maintenance Plan Using Wizard

    November 29, 2005 - 0 Comment
  • SQL Server Architecture and Components

    September 13, 2005 - 0 Comment
  • SQL Server 2005 – Using the Sqlcmd Utility

    December 18, 2005 - 0 Comment
  • Developing Client applications in SQL Server 2005

    November 1, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 Installation – Maintenance Plan Without Using Wizard

    December 2, 2005 - 0 Comment
  • Security Features in SQL Server 2005 for the Developer

    October 15, 2005 - 0 Comment
  • SQL Server 2005 Configuration Manager

    December 18, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment
  • SQL Server 2005 – Using the Database Tuning Advisor

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Backing up a Database
  • SQL Server 2005 – Using Database Snapshots

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT