Troubleshooting Database Mirroring Configuration

From my last post, http://coffeeandsql.com/2013/11/10/70-457-reviewer-15-implement-database-mirroring/, you were able to successfully setup a database mirroring. For further monitoring and checking if everything is OK, you may use the following T–SQL statements.

To check the principal id the database mirroring is using;

SELECT name, principal_id FROM sys.database_mirroring_endpoints;

This principal id should be the grantor when you run the query below

SELECT EP.name, SP.STATE, CONVERT(nvarchar(38),suser_name(SP.grantor_principal_id)) AS GRANTOR, SP.TYPE AS PERMISSION, CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) AS GRANTEE FROM sys.server_permissions SP ,sys.endpoints EP WHERE SP.major_id = EP.endpoint_id ORDER BY Permission,grantor, grantee;

You need to find the domain account your SQL Server service is using here. If not you will need to transfer this ownership to the SQL Server service account. To do so, you may run the T-SQL statement below

USE master
GO
ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO [domain\serviceaccount]
GO

Your configuration should be completed at this point and running smoothly. For more on troubleshooting other issues with your setup, you may visit http://technet.microsoft.com/en-us/library/ms189127.aspx

 

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman

Just another WordPress.com site

Paul Turley's SQL Server BI Blog

from the world of Microsoft SQL Server BI and reporting

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We will improve your knowledge

SQL Studies

 Live, Learn, Share

Hemlock Time

The Pursuit of Personal Freedom | #TeamAPE

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Stevespevack's Blog

IT Recruitment

SQL Problems and Solutions

Sharing the knowledge & experience of SQL, DBA & BI

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

Algonquin Studios Blog

Ideas and opinions on software development, web design, customer service, sales and marketing...and food

SutoCom Solutions

Success & Satisfaction with the Cloud

Follow

Get every new post delivered to your Inbox.

Join 47 other followers

%d bloggers like this: