Role:
We have to specify the endpoint’s role in the Database mirroring option.Role can be Partner,Witness or All.Using the ALL keyword as the role specifies that the mirroring endpoint can be used for witness as well as for a partner in the database mirroring scenario.
We can inspect the database mirroring endpoints on a server by querying the sys.database_mirroring_endpoints catalog view:
SELECT *
FROM sys.database_mirroring_endpoints;
2.Creating the Mirror Database
To create a mirror database,we have to restore the full backup of a principal including all other types of backup(transactional logs) created on the principal before establishing a session.The NORECOVERY option has to be used when restoring from backup so that the mirrored database will remain in nonusable state.The mirror database needs to have the same name as the principal database.
3.Establishing a mirror session
The next step in setting up database mirroring is to set up the mirror session on the database by identifying the mirroring partners.We have to identify the partners involved in the mirroring process on the principal database and on the mirror database.
Let us consider an example.
We will take AdventureWorks as the sample database.This database has simple recovery model by default. To use database mirroring with this database, we must alter it to use the full recovery model.
USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO
We have two server instances which act as partners(Principal and Mirror) and one server instance which acts as witness.These three instances are located on different computers. The three server instances run the same Windows domain, but the user account is different for the example’s witness server instance.
1.Create an endpoint on the principal server instance
CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO
–Partners under same domain user; login already exists in master.
–Create a login for the witness server instance,
–which is running as XYZwitnessuser:
USE master ;
GO
CREATE LOGIN [XYZwitnessuser] FROM WINDOWS ;
GO
— Grant connect permissions on endpoint to login account of witness.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [XYZwitnessuser];
GO
2.Create an endpoint on the mirror server instance
CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO
–Partners under same domain user; login already exists in master.
–Create a login for the witness server instance,
–which is running as XYZwitnessuser:
USE master ;
GO
CREATE LOGIN [XYZwitnessuser] FROM WINDOWS ;
GO
–Grant connect permissions on endpoint to login account of witness.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [XYZwitnessuser];
GO
3.Create an endpoint on the witness server instance
CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO
–Create a login for the partner server instances,
–which are both running as Mydomaindbousername:
USE master ;
GO
CREATE LOGIN [Mydomaindbousername] FROM WINDOWS ;
GO
–Grant connect permissions on endpoint to login account of partners.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomaindbousername];
GO
4.Create the mirror database. Refer step 2 in the “Preparing for Mirroring” block.
5.Configure the principal as the partner on the mirror.
ALTER DATABASE <Database_Name>
SET PARTNER =
<server_network_address>
GO
The syntax for a server network address is of the form:
TCP :// < system-address> : < port>
where,
< system-address> is a string that unambiguously identifies the destination computer system. Typically, the server address is a system name (if the systems are in the same domain), a fully qualified domain name, or an IP address.
< port> is the port number used by the mirroring endpoint of the partner server instance.
A database mirroring endpoint can use any available port on the computer system. Each port number on a computer system must be associated with only one endpoint, and each endpoint is associated with a single server instance; thus, different server instances on the same server listen on different endpoints with different ports. In the server network address of a server instance, only the number of the port associated with its mirroring endpoint distinguishes that instance from any other instances on the computer.
Example:
ALTER DATABASE AdventureWorks
SET PARTNER =
‘TCP://PARTNERHOST1.COM:7022’
GO
6. Configure the mirror as the partner on the principal.
ALTER DATABASE AdventureWorks
SET PARTNER = ‘TCP://PARTNERHOST5.COM:7022’
GO
7.On the principal server, set the witness
ALTER DATABASE AdventureWorks
SET WITNESS =
‘TCP://WITNESSHOST4.COM:7022’
GO
Switching Roles
When the principal server fails,we have to switch roles over to the mirror and from then on specify that the mirror should become the principal database.This concept is called role switching.The three options for role switching are:
1.Automatic failover :- When the witness server is present in the database mirroring session,automatic failover will occur when the principal database becomes unavailable and
when the witness server confirms this.During the automatic failover,the mirror will be automatically promoted to principal,and whenever the principal comes back on,it will automatically take the role of mirror.
2.Manual Failover :- The user can perform manual failover only if both the principal and mirror are alive and in synchronized status.DBAs use this operation most frequently to perform maintenance tasks on the principal.The failover is initiated from the principal and later the roles are reverted after the database maintenance job is done.
The statement used to switch database roles(manual failover) is shown below:
ALTER DATABASE AdventureWorks SET PARTNER FAILOVER
3.Forced Service :- When the witness server is not used and if the principal database goes down unexpectedly,then the user has to initiate manual failover to the mirror.In asynchronous mode of operation,user does not have any idea whether the transaction that have got commited on the principal have made it to the mirror or not.In this scenario,when the user wants to switch roles,there is possibility of losing data.
To achieve this,we need to invoke an ALTER DATABASE statement as shown below:
ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
Conclusion
Thus we had a look at the Database mirroring feature and how this feature can be explored for maintaining high availability of the databases.
]]>