Archive for the ‘Database Mirroring’ Category

How to remove Database Mirroring?

Due to recent changes, your Network Engineering team has decided to modify the network with segmentation changes. Unfortunately due to their workload, they were too busy to notify you that the Mirror server will be located in a separate network. Yes. That’s correct. Your Principal server will no longer be able to communicate with your Mirror server. You have been alerted via the alerts that you had set up for Database Mirroring monitoring in addition to the log space alert that you had setup that the mirroring state description is DISCONNECTED and your log space utilization for the Principal database is almost or 100% full. You had thought about switching it to high performance mode by setting the safety option to off for database mirroring so that it will be changed from synchronous mode to asynchronous mode so that the transactions doesn’t need the Mirror server to commit before committing in the Principal server. But, that doesn’t work because the Principal server can’t communicate the Mirror server as it is a requirement for the switching the safety options. What can you do?

In this scenario, you have to remove Database Mirroring and then set it up again when a Mirror server is made available. When you remove Database Mirroring, each partner server instance will be left with a separate copy of the database. The mirror database will be left in the restoring state. To drop the mirror database, you must first restore it using the WITH RECOVERY option. The following are the overview steps before we move on to the implementation steps:

1. Connect to the Principal or the Mirror server.
2. Remove Database Mirroring.
3. In the mirror server, recover the mirrored database to bring it out of the restoring state.
4. Drop the former mirrored database.

Implementation steps:

1. Connect to the Principal or the Mirror server.
2. Remove Database Mirroring.

 -- To remove database mirroring, connect to either partner and then set the partner off
 ALTER DATABASE AdventureWorks -- replace your with your Principal or Mirror database here
 SET PARTNER OFF
 

3. In the Mirror server, recover the mirrored database to bring it out of the restoring state.

 -- Connect to the mirror server, bring the formerly mirrored database out of restoring mode
 RESTORE DATABASE AdventureWorks -- replace your with your Mirror database here 
 WITH RECOVERY

4. Drop the former mirrored database.

-- Drop the formerly mirrored database
 DROP DATABASE AdventureWorks -- replace your with your Mirror database here 

REFERENCES:

Removing Database Mirroring
http://msdn.microsoft.com/en-us/library/ms180801(v=sql.105).aspx

How to: Remove Database Mirroring
http://msdn.microsoft.com/en-us/library/ms189112(v=sql.105).aspx

How to query a mirrored database using a snapshot?

If you looked at the mirrored database in the Mirror server, you will find that the mirrored database has the keywords “(Mirror, Synchronized / Restoring…)” appended to it. As we all know, if a database is restoring, you can’t read from it. Then, how can you query against it if you wanted to run reports against or just to check if the updates from the Principal database did actually get mirrored over to the mirrored database? Yes. You can if you first create a database snapshot and then query against the snapshot.
The keyword snapshot may have given away or hinted on what it means. A database snapshot is a read-only copy of a database that is taken at the point of time when the snapshot is taken. It is similar to taking a photo of yourself when you were twenty-two years old. You wish you were always twenty-two whenever you looked at the photo of which remind you of your fond memories of your younger days. But, again, it is only a photo, you have aged since then. The hard-partying days are over for you. Let’s get focused on SQL Server. Here are some facts about the database snapshots:

  • Available only for the Enterprise and Developer editions of SQL Server 2005+
  • Must be created in the same instance as the source
  • You can create multiple database snapshots for a database
  • Usually used for reporting, restore point, and just for testing purposes
  • You must use T-SQL to create a database snapshot
  • Read-only copy of the source
  • Use sparse files to store data

Sparse files are a feature of the NTFS file system. When created initially, a spare file takes up little disk space. It grows as changes in the source database increases. When you create a snapshot, the data pages are not copied over to the snapshot, when the data pages become dirty or changes occurred then the data pages will get copied over to the database snapshot. It is similar to the photo you had taken when you were twenty-two. You were young and looked great on the photo. But, it is only a snapshot. As you age, you gain more experience and more mileage on your body. Let’s just say things also changed, shall we?

In the following steps, we will create a database snapshot for the mirrored database and then query against it. Here are the overview steps before the implementation steps as always:
1. Query against the system view sys.master_files to view the logical file name of your mirrored database.
2. Make sure you have enough disk space in your server.
3. Create the snapshot.
4. Query against the snapshot.
5. Drop the snapshot.

Implementation steps:
1. Query against the system view sys.master_files to view the logical file name of your mirrored database.

-- Find the logical file name for the data file for the mirrored database
-- Make a note of it as it will be need for the creation of the snapshot
SELECT name AS LogicalName
, type_desc
, physical_name
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks' -- replace with your mirrored database here

2. Make sure you have enough disk space in your server.

-- Make sure you have enough disk space
XP_FIXEDDRIVES

3. Create the snapshot.

-- Create the snapshot
CREATE DATABASE AdventureWorks_dbss01242013 ON -- replace with your mirrored database here
(
NAME = AdventureWorks_Data
, FILENAME = 'C:\Snapshot\AdventureWorks_dbss01242013.ss'
)
AS SNAPSHOT OF AdventureWorks -- replace with your mirrored database here
GO

4. Query against the snapshot.

-- You can query the database snapshot similar to how you query a regular database
USE AdventureWorks_dbss01242013
GO
SELECT *
FROM HumanResources.Employee
GO

5. Drop the snapshot.

-- Drop a database snapshot
USE master
GO
DROP DATABASE AdventureWorks_dbss01242013

REFERENCES:

How to: Create a Database Snapshot
http://msdn.microsoft.com/en-us/library/ms175876(v=sql.105).aspx

Understanding Sparse File Sizes in Database Snapshots
http://msdn.microsoft.com/en-us/library/ms175823(v=sql.105).aspx

Drop a Database Snapshot
http://msdn.microsoft.com/en-us/library/ms190220(v=sql.105).aspx

How to monitor Database Mirroring?

To view the current status of the Database Mirroring, you can open the Database Mirroring Monitor by right-clicking the Principal database > Tasks > Launch Database Mirroring Monitor… You can see an example of the screen-shot here. In the Database Mirroring Monitor, you can see the Principal and Mirror servers, mirroring state, unsent log, unrestored log, and other valuable information about the Database Mirroring status. Wouldn’t it be nice, instead of having to get in front of your laptop and open up the Database Mirroring Monitor to check the Database Mirroring status, you will only get an email notification only if there are issues that will require your manual intervention to troubleshoot? There are a number of ways to monitor the status of Database Mirroring. Please do share with me if you have better or alternative solutions. The possible alternatives can be WMI events, threshold events that can be set in the Database Mirroring Monitor, extended events, and/or powershell.

Here’s an overview of the two possible options:

1. Query the system view master.sys.database_mirroring for the mirroring state description for the databases that are in the principal or mirror roles. If their states are not in synchronized or synchronizing, then send out alert. You can schedule a SQL Server Agent job to run on an hourly basis or at a specific time interval as per your requirements.

2. Query the system view master.sys.dm_os_performance_counters for the Unsent log and Unrestored log if either one of them is greater than 100 MB, then send out alert. You can schedule a SQL Server Agent job to run on an hourly basis or at a specific time interval as per your requirements.

Implementation steps with plain vanilla HTML report to be sent via database mail. You can add cosmetic works with coloring and formatting if needed:

1. Query the system view master.sys.database_mirroring for the mirroring state description for the databases that are in the principal or mirror roles. If their states are not in synchronized or synchronizing, then send out alert. You can schedule a SQL Server Agent job to run on an hourly basis or at a specific time interval as per your requirements.

-- Querying the system view sys.database_mirroring 
-- for the principal or mirror databases that are not synchronized or synchronizing
IF EXISTS ( 
	SELECT @@SERVERNAME as InstanceName
	, DB_NAME(dbm.database_id) as DatabaseName
	, dbm.mirroring_role_desc
	, mirroring_state_desc 
	FROM  master.sys.database_mirroring dbm
	WHERE  dbm.mirroring_role_desc IN ('PRINCIPAL','MIRROR') 
		AND dbm.mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')
)		
BEGIN
	-- declare the variables for the database mail and message formatting
	DECLARE @IsDBMailEnabled BIT
	, @MailSubject VARCHAR(123)
	, @MailProfile VARCHAR(123)
	, @Subject VARCHAR (123)
	, @Recipients VARCHAR (123)
	, @TableHTML VARCHAR(MAX)
	
	SET @Recipients = 'email@company.com' -- replace with your recipient(s) here
	
	SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use)) 
	FROM  sys.configurations
	WHERE name LIKE 'Database Mail XPs'

	IF @IsDBMailEnabled = 0
	RAISERROR('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

	ELSE		
		BEGIN
			SELECT @TableHTML = 
			'<html><table border = "1">' +
			'<tr>
				<th>Instance Name</th>
				<th>Database Name</th>
				<th>Mirroring Role</th>
				<th>Mirroring State Desc</th>
			 </tr>'
			 
			 SELECT @TableHTML = @TableHTML + 			
				'<tr>' +
			    '<td>' + @@SERVERNAME + '</td>' +
		        '<td>' + DB_NAME(dbm.database_id) + '</td>' +
				'<td>' + dbm.mirroring_role_desc + '</td>' + 
				'<td>' + mirroring_state_desc + '</td></tr>'
			FROM  master.sys.database_mirroring dbm
			WHERE  dbm.mirroring_role_desc IN ('PRINCIPAL','MIRROR') 
				AND dbm.mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')
				
			SELECT @TableHTML = @TableHTML + '</table></HTML>'		
	
			/*Sending mail*/
			SELECT @MailSubject = 'Database Mirroring Status: ' + CONVERT(VARCHAR(50),@@servername)

			-- Find the database mail profile name
			create table #dbmail_profile
			(
			profileid int null,
			profilename varchar(125) null,
			accountid int null,
			accountname varchar(125) null,
			sequencenumber int null
			)
			insert #dbmail_profile
			EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

			select @MailProfile = profilename
			from #dbmail_profile
			where sequencenumber = 1

			drop table #dbmail_profile

			EXEC msdb.dbo.sp_send_dbmail
				@profile_name = @MailProfile,
				@recipients = @Recipients,
				@subject = @MailSubject,
				@body_format = 'HTML',
				@body = @TableHTML;														
		END	
END

2. Query the system view master.sys.dm_os_performance_counters for the Unsent log and Unrestored log if either one of them is greater than 100 MB, then send out alert. You can schedule a SQL Server Agent job to run on an hourly basis or at a specific time interval as per your requirements.

-- Querying the sys.dm_os_performance_counters for database mirroring status
-- Log Send Queue KB: In the Database Mirroring Monitor, it is the Unsent log
-- Redo Queue KB: In the Database Mirroring Monitor, it is the Unrestored log
-- Redo Bytes/sec: In the Database Mirroring Monitor, it is the Current restore rate
IF EXISTS (
	SELECT pc.instance_name as DatabaseName -- principal database name
	, pc.counter_name
	, pc.cntr_value
	FROM master.sys.dm_os_performance_counters pc
	WHERE pc.object_name like '%Database Mirroring%'
		AND pc.instance_name in (	SELECT DB_NAME(dbm.database_id) as DatabaseName
									FROM  master.sys.database_mirroring dbm
									WHERE  dbm.mirroring_role_desc IN ('PRINCIPAL','MIRROR') ) 
		AND ( (pc.counter_name = 'Log Send Queue KB' AND pc.cntr_value >= 102400) -- Unsent log >= 100 MB
			OR (pc.counter_name = 'Redo Queue KB' AND pc.cntr_value >= 102400)) -- Unrestored log >= 100 MB
		AND (pc.counter_name = 'Redo Bytes/sec')
)	
BEGIN
	-- declare the variables for the database mail and message formatting
	DECLARE @IsDBMailEnabled BIT
	, @MailSubject VARCHAR(123)
	, @MailProfile VARCHAR(123)
	, @Subject VARCHAR (123)
	, @Recipients VARCHAR (123)
	, @TableHTML VARCHAR(MAX)
	
	SET @Recipients = 'email@company.com' -- replace with your recipient(s) here
	
	SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use)) 
	FROM  sys.configurations
	WHERE name LIKE 'Database Mail XPs'

	IF @IsDBMailEnabled = 0
	RAISERROR('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

	ELSE		
		BEGIN
			SELECT @TableHTML = 
			'<HTML><table border = "1">' +
			'<tr>
				<th>Database Name</th>
				<th>Counter Name</th>
				<th>Counter Value</th>
			 </tr>'			
			
			SELECT @TableHTML = @TableHTML + '<tr>' +
			    '<td>' + pc.instance_name + '</td>' +
		        '<td>' + pc.counter_name + '</td>' +
				'<td>' + convert(varchar(25), pc.cntr_value) + '</td></tr>'  
			FROM master.sys.dm_os_performance_counters pc
			WHERE pc.object_name like '%Database Mirroring%'
				AND pc.instance_name in (	SELECT DB_NAME(dbm.database_id) as DatabaseName
											FROM  master.sys.database_mirroring dbm
											WHERE  dbm.mirroring_role_desc IN ('PRINCIPAL','MIRROR') )
				AND ( (pc.counter_name = 'Log Send Queue KB' AND pc.cntr_value >= 102400) -- Unsent log >= 100 MB
					OR (pc.counter_name = 'Redo Queue KB' AND pc.cntr_value >= 102400)) -- Unrestored log >= 100 MB
				AND (pc.counter_name = 'Redo Bytes/sec')

			SELECT @TableHTML = @TableHTML + '</table></HTML>'				
	
			/*Sending mail*/
			SELECT @MailSubject = 'Database Mirroring Monitor Status: ' + CONVERT(VARCHAR(50),@@servername)

			-- Find the database mail profile name
			create table #dbmail_profile
			(
			profileid int null,
			profilename varchar(125) null,
			accountid int null,
			accountname varchar(125) null,
			sequencenumber int null
			)
			insert #dbmail_profile
			EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

			select @MailProfile = profilename
			from #dbmail_profile
			where sequencenumber = 1

			drop table #dbmail_profile

			EXEC msdb.dbo.sp_send_dbmail
				@profile_name = @MailProfile,
				@recipients = @Recipients,
				@subject = @MailSubject,
				@body_format = 'HTML',
				@body = @TableHTML;														
		END	
END

REFERENCES:

sys.database_mirroring
http://msdn.microsoft.com/en-us/library/ms178655(v=sql.105).aspx

sys.dm_os_performance_counters
http://technet.microsoft.com/en-us/library/ms187743(v=sql.105).aspx

How to failover to the mirrored database?

To test or if you need to failover to the partner in Database Mirroring, you can follow the steps below. As always let’s understand the steps involved before getting our hands dirty. Please do read my explanation on how to setup Database Mirroring if needed.

Steps required:

1. Query the current Database Mirroring information such as the mirroring status, role description, safety level, partner name and instance name.
2. Alter the database to set the safety to full if needed then issue the failover command. Role reversal will happen. The Principal will become the Mirror and the Mirror will become the new Principal.
3. On the new Principal server, set the safety to off if needed (if the distance is far away in between the Principal and Mirror server).
4. Validate the role reversal.
5. Make sure all the required logins and users’ access privileges and SQL Server Agent jobs are transferred over to the Mirror server.
6. Collaborate with the application team to redirect the application to connect to the new Principal database.

Implementation steps:
1. On the Principal server, query the current Database Mirroring information such as the mirroring status, role description, safety level, partner name and instance name.

-- Querying the database mirroring status, role desc, safety level, partner name and instance
SELECT @@SERVERNAME as InstanceName
, DB_NAME(dbm.database_id) as DatabaseName
, dbm.mirroring_state_desc
, dbm.mirroring_role_desc
, dbm.mirroring_safety_level_desc
, dbm.mirroring_partner_name
, dbm.mirroring_partner_instance
FROM sys.database_mirroring dbm
WHERE dbm.mirroring_guid IS NOT NULL

2. Alter the database to set the safety to full if needed then issue the failover command. Role reversal will happen. The Principal will become the Mirror and the Mirror will become the new Principal.

-- To failover to the mirror database
-- Set the safety to full if necessary prior to failover
USE master
GO
ALTER DATABASE AdventureWorks -- replace your principal database name here
SET SAFETY FULL -- set safety to full if necessary
GO
ALTER DATABASE AdventureWorks -- replace your principal database name here
SET PARTNER FAILOVER -- failover to the partner
GO

3. On the new Principal server, set the safety to off if needed (if the distance is far away in between the Principal and Mirror server).

-- On the new principal server
-- Set the safety off to change database mirroring to high performance if the site distance is far apart
USE master
GO
ALTER DATABASE AdventureWorks -- replace your principal database name here
SET SAFETY OFF

4. Validate the role reversal. On the new Principal server, run the following query:

-- Querying the database mirroring status, role desc, safety level, partner name and instance
SELECT @@SERVERNAME as InstanceName
, DB_NAME(dbm.database_id) as DatabaseName
, dbm.mirroring_state_desc
, dbm.mirroring_role_desc
, dbm.mirroring_safety_level_desc
, dbm.mirroring_partner_name
, dbm.mirroring_partner_instance
FROM sys.database_mirroring dbm
WHERE dbm.mirroring_guid IS NOT NULL

5. Make sure all the required logins and users’ access privileges and SQL Server Agent jobs are transferred over to the Mirror server.

6. Collaborate with the application team to redirect the application(s) to connect to the new Principal database.

REFERENCES:

sys.database_mirroring
http://msdn.microsoft.com/en-us/library/ms178655(v=sql.105).aspx

How to setup Database Mirroring?

Similar to Log Shipping, Database mirroring is a form of high availability technology that’s per database as opposed to per instance in clustering. It is faster than log shipping and has options for automatic failover for the available editions and if a witness server is available. Data transfer is encrypted if both the principal and mirrored servers are in the same domain. Let’s get familiarized with the terminologies and concepts and understand the pre-requisites before getting our hands dirty on the actual setup steps.

Database mirroring requires the database to be mirrored to be in full recovery model as it is essentially a process of forwarding the transaction log from the principal database over to the mirrored database to be written in a separate server. Database mirroring comprises of two mandatory server roles and an optional third server role:

  • Principal server (mandatory): This is the source of the mirroring.
  • Mirror server (mandatory): This is the standby server that will receive the transaction logs from the principal server.
  • Witness server (optional): You will need this server only if you will be configuring automatic failover. It will continuously check to see if there are any failures with the principal server and fail over to the mirror server. You can use the free version of SQL Server: SQL Server Expression for this server role.

There are 3 operating modes that you must be aware of as the selection will have a dramatic influence on the performance of database mirroring and the protection level against disaster. We must first, however, need to understand the difference between synchronous and asynchronous operations:

  • Synchronous operations: Transactions can only be committed on the principal server after they are committed in the mirror server and after the mirror server sent acknowledgement back to the principal server. This is considered as a high-safety mode.
  • Asynchronous operations: Transactions can be committed on the principal server without waiting for the mirror server to receive and write the log to disk. This is considered as a high performance mode.

The 3 operating modes are as follows:

  • High safety with automatic failover mode (high availability with a witness server)
  • High safety without automatic failover mode (high protection without a witness server)
  • High performance mode

To communicate between the principal server and the mirror server, SQL Server requires endpoints for the communication process over the network. The database mirroring endpoint controls the port on which the server listens for database mirroring messages from other servers.

Pre-requisites:

  1. Identify your principal, mirror, and the witness server (if setting up automatic failover).
  2. Check to make sure your principal and mirror servers are at the same service pack.
  3. Identify the database to have database mirroring and make it is in full recovery model.
  4. Each of the server instances will require a database mirroring endpoint.
  5. Make sure the port numbers for the endpoints are open. Speak to your security administrator if necessary.
  6. Make sure the service accounts for database mirroring “cross-exist” if they are different. For example, a valid login from the principal server exists in the mirrored server and a valid login from the mirrored server exists in the principal server. In addition, you will need to grant the CONNECT permission to the database mirroring endpoint of the server instances. If you are using the SQL Server Agent account as the service accounts for database mirroring, you will be fine.

In the following example, we will be setting up database mirroring in a High safety without automatic failover mode (high protection without a witness server). Here are the overview steps:

  1. Create the endpoint for database mirroring in the principal server.
  2. Create the endpoint for database mirroring in the mirror server.
  3. Take a full backup of the principal database.
  4. Restore the full backup to the mirror server with no recovery.
  5. Backup the transaction log of the principal database.
  6. Restore the transaction log backup to the mirror server with no recovery.
  7. On the mirrored server, alter the database to set its partner to the principal server.
  8. On the principal server, alter the database to set its partner to the mirrored server.

Implementation steps:

  1. Create the endpoint for database mirroring in the principal server.
  2. -- On the principal server
    
    -- Check the database mirroring endpoint
    -- You can drop it using the DROP ENDPOINT endpointname statement
    SELECT name
    , role_desc
    , state_desc
    FROM sys.database_mirroring_endpoints
    
    -- To find the port number for Database Mirroring
    select name
    , type_desc
    , port
    from sys.tcp_endpoints
    
    -- Create the database mirroring endpoint if not exist
    CREATE ENDPOINT DB_Mirroring
        STATE=STARTED 
        AS TCP (LISTENER_PORT=5022) 
        FOR DATABASE_MIRRORING (ROLE=PARTNER)
    GO
    
  3. Create the endpoint for database mirroring in the mirror server.
  4. -- On the mirror server
    
    -- Check the database mirroring endpoint
    -- You can drop it using the DROP ENDPOINT endpointname statement
    SELECT name
    , role_desc
    , state_desc
    FROM sys.database_mirroring_endpoints
    
    -- To find the port number for Database Mirroring
    select name
    , type_desc
    , port
    from sys.tcp_endpoints
    
    -- Create the database mirroring endpoint if not exist
    CREATE ENDPOINT DB_Mirroring
        STATE=STARTED 
        AS TCP (LISTENER_PORT=5023) 
        FOR DATABASE_MIRRORING (ROLE=PARTNER)
    GO
    
  5. Take a full backup of the principal database.
  6. -- Backup the principal database
    -- For example:
    BACKUP DATABASE AdventureWorks -- replace with your principal database name here
    TO DISK = 'C:\SQLBACKUPS\AdventureWorks.bak' -- replace with your backup path here
    WITH COMPRESSION
    
  7. Restore the full backup to the mirror server with no recovery.
  8. -- Restore the principal database to the mirror server with NORECOVERY
    -- For example:
    
    -- view the logical file names 
    RESTORE FILELISTONLY
    FROM DISK = 'C:\SQLBACKUPS\AdventureWorks.bak' -- replace with your backup path here
    
    -- restore the database with NORECOVERY
    RESTORE DATABASE AdventureWorks -- replace with your mirror database name here
    FROM DISK = 'C:\SQLBACKUPS\AdventureWorks.bak' -- replace with your backup path here
    WITH MOVE 'AdventureWorks_Data' TO 'C:\AW\AdventureWorks_Data.mdf',
    MOVE 'AdventureWorks_Log' TO 'C:\AW\AdventureWorks_Log.ldf',
    NORECOVERY
    
  9. Backup the transaction log of the principal database.
  10. -- Backup the transaction log of the principal database
    -- For example:
    BACKUP LOG AdventureWorks -- replace with your principal database name here
    TO DISK = 'C:\SQLBACKUPS\AdventureWorks.trn' -- replace with your backup path here
    WITH COMPRESSION
    
  11. Restore the transaction log backup to the mirror server with no recovery.
  12. -- Restore the transaction log backup to the mirror server
    -- For example:
    RESTORE LOG AdventureWorks -- replace with your mirror database name here
    FROM DISK = 'C:\SQLBACKUPS\AdventureWorks.trn' -- replace with your backup path here
    WITH NORECOVERY
    
  13. On the mirrored server, alter the database to set its partner to the principal server.
  14. -- On the mirror server, alter the database to set its partner to the principal server
    -- For example:
    ALTER DATABASE AdventureWorks -- replace with your principal database name here
    SET PARTNER = 'TCP://fully qualified principal server name:5022' -- replace with your fully qualified principal server name and port number for the endpoint
    
  15. On the principal server, alter the database to set its partner to the mirrored server.
  16. -- On the principal server, alter the database to set its partner to the mirror server
    -- For example:
    ALTER DATABASE AdventureWorks -- replace with your mirror database name here
    SET PARTNER = 'TCP://fully qualified mirror server name:5023' -- replace with your fully qualified mirror server name and port number for the endpoint
    

To see the Database Mirroring status, role description, safety level, partner name and instance name, you can run the following query on either the Principal or the Mirror server:

-- Querying the database mirroring status, role desc, safety level, partner name and instance
SELECT @@SERVERNAME as InstanceName
, DB_NAME(dbm.database_id) as DatabaseName
, dbm.mirroring_state_desc
, dbm.mirroring_role_desc
, dbm.mirroring_safety_level_desc
, dbm.mirroring_partner_name
, dbm.mirroring_partner_instance
FROM sys.database_mirroring dbm
WHERE dbm.mirroring_guid IS NOT NULL

REFERENCES:

Setting Up Database Mirroring
http://msdn.microsoft.com/en-us/library/ms190941(v=sql.105).aspx

Database Mirroring Endpoint
http://msdn.microsoft.com/en-us/library/ms179511(v=sql.105).aspx