Database Mirroring Config Problem

Question

Database Mirroring Config Problem

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

nano2nd Hall of Fame Points: 3073 More actions · Answer 1

SQL Authentication is definitely an issue. There is some resistance here to using domain accounts because a different group is responsible for passwords etc. However, because of some other issues I am beginning to think that I need to convince some others that we need to change to domain accounts

Sudeepta SSCommitted Points: 1666 More actions · Answer 2

Then you can go with certificate based configuration. check my previous post for the link. Am never deployed with certificate but yes, planning to learn it... 🙂

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

nano2nd Hall of Fame Points: 3073 More actions · Answer 3

I think the link is broken, but I think I have found the article through google.

@SQLNumpty SSCrazy Points: 2940 More actions · Answer 4

Below are the principle and mirror scripts I have used in the past. I don't have the witness script to hand but will upload it if you cannot fill in the missing bits or spot what you need to get you going. Originally for 2005 but should be okay on 2008 😉

I do not have the URL so cannot credit the original author(s). This saved me no end of work so a big thank you for this ... 😎

Hope you find what you are looking for.

Nearly forgot ... Best practice is to use names, not addresses even if these only exist in your hosts file.

Principle.

-- Step 1: Create encryption key, certificate and end-points on Principal Instance

/* Execute this against the Principal Instance */

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '2g8p4rESPbx6'

GO

CREATE CERTIFICATE HOST_PRIN_cert

WITH SUBJECT = 'HOST_PRIN certificate',

START_DATE = '01/05/2010'

GO

CREATE ENDPOINT End_Mirroring

STATE = STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = CERTIFICATE HOST_PRIN_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4,

ROLE = ALL

)

GO

BACKUP CERTIFICATE HOST_PRIN_cert

TO FILE = 'E:\certificate\HOST_PRIN_cert.cer'

GO

-- Step 3: Create login, user and associate certificate with user on Principal Instance

/*

* Execute this against the Principal Instance. The HOST_MIRR_cert.cer

* needs to be copied on the Principal Server.

*/

USE MASTER

GO

/*

* We are creating a SQL Login here. For Windows logins,

* use the Grant Login instead of Create Login

*/

CREATE LOGIN HOST_MIRR_login WITH PASSWORD = '7FTrL2J6CS8g'

GO

CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login

GO

CREATE CERTIFICATE HOST_MIRR_cert

AUTHORIZATION HOST_MIRR_user

FROM FILE = 'E:\certificate\HOST_MIRR_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]

GO

-- Step 6: Create login, user and associate certificate with user on Principal Instance

/*

* Execute this against the Principal Instance. The HOST_WITT_cert.cer

* needs to be copied on the Principal Server.

*/

USE MASTER

GO

/*

* We are creating a SQL Login here. For Windows logins,

* use the Grant Login instead of Create Login

*/

CREATE LOGIN HOST_WITT_login WITH PASSWORD = '9ELzU8qPj5dK'

GO

CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login

GO

CREATE CERTIFICATE HOST_WITT_cert

AUTHORIZATION HOST_WITT_user

FROM FILE = 'E:\certificate\HOST_WITT_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]

GO

-- Step 9A: Create the Mirrored Database on the Mirror Server using backups from the Principal Server

/*

* Execute this against the Principal Instance.

*/

USE MASTER

GO

BACKUP DATABASE AdventureWorks

TO DISK = 'E:\Backups\AdventureWorks_FullBackup.bak'

GO

BACKUP LOG AdventureWorks

TO DISK = 'E:\Backups\AdventureWorks_LogBackup.trn'

GO

/*

* Copy MirrorDB_FullBackup.bak and MirrorDB_LogBackup.trn to the

* Mirror Server.

*/

-- Step 10B: Setup Mirroring

/*

* Execute this against the Principal Instance.

*/

ALTER DATABASE AdventureWorks

SET PARTNER = 'TCP://sec:5023'

GO

ALTER DATABASE AdventureWorks

SET WITNESS = 'TCP://wit:5024'

GO

Mirror

-- Step 2: Create encryption key, certificate and end-points on Mirror Instance

/* Execute this against the Mirror Instance */

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '7FTrL2J6CS8g'

GO

CREATE CERTIFICATE HOST_MIRR_cert

WITH SUBJECT = 'HOST_MIRR certificate',

START_DATE = '01/06/2010'

GO

CREATE ENDPOINT End_Mirroring

STATE = STARTED

AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = CERTIFICATE HOST_MIRR_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4,

ROLE = ALL

)

GO

BACKUP CERTIFICATE HOST_MIRR_cert

TO FILE = 'E:\certificate\HOST_MIRR_cert.cer';

GO

-- Step 4: Create login, user and associate certificate with user on Mirror Instance

/*

* Execute this against the Mirror Instance. The HOST_PRIN_cert.cer

* needs to be copied on the Mirror Server.

*/

USE MASTER

GO

/*

* We are creating a SQL Login here. For Windows logins,

* use the Grant Login instead of Create Login

*/

CREATE LOGIN HOST_PRIN_login WITH PASSWORD = '2g8p4rESPbx6'

GO

CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login

GO

CREATE CERTIFICATE HOST_PRIN_cert

AUTHORIZATION HOST_PRIN_user

FROM FILE = 'E:\certificate\HOST_PRIN_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]

GO

-- Step 7: Create login, user and associate certificate with user on Mirror Instance

/*

* Execute this against the Mirror Instance. The HOST_WITT_cert.cer

* needs to be copied on the Mirror Server.

*/

USE MASTER

GO

/*

* We are creating a SQL Login here. For Windows logins,

* use the Grant Login instead of Create Login

*/

CREATE LOGIN HOST_WITT_login WITH PASSWORD = '9ELzU8qPj5dK'

GO

CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login

GO

CREATE CERTIFICATE HOST_WITT_cert

AUTHORIZATION HOST_WITT_user

FROM FILE = 'E:\certificate\HOST_WITT_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]

GO

-- Step 9B: Create the Mirrored Database on the Mirror Server using backups from the Principal Server

/*

* Copy MirrorDB_FullBackup.bak and MirrorDB_LogBackup.trn to the

* Mirror Server.

* Execute this against the Mirror Instance.

*/

USE MASTER

GO

RESTORE DATABASE AdventureWorks

FROM DISK = 'E:\Backups\AdventureWorks_FullBackup.bak'

WITH NORECOVERY

GO

RESTORE LOG AdventureWorks

FROM DISK = 'E:\Backups\AdventureWorks_LogBackup.trn'

WITH NORECOVERY

GO

-- Step 10A: Setup Mirroring

/*

* Execute this against the Mirror Instance.

*/

ALTER DATABASE AdventureWorks

SET PARTNER = 'TCP://pri:5022'

GO

nano2nd Hall of Fame Points: 3073 More actions · Answer 5

Thanks. I've got two sets of scripts to try and they are very similar. I'll sleep better this weekend if I can get one of them to work.

nano2nd Hall of Fame Points: 3073 More actions · Answer 6

I followed Randall Neth's scripts but I still get the same error. "...can not be reached...".

When I telnet to the principal and mirror, I don't get an error, I get a flashing underscore and after hitting enter a few times I am returned to the dos prompt. So, I think the ports are ok, firewall is turned off

This whole concept of mirroring certainly doesn't reflect well on my technical abililties.

I guess I will delete all that I have done and start over from scratch one more time.

Sudeepta SSCommitted Points: 1666 More actions · Answer 7

Are you using domain user account for SQL Server Services? If yes, its better you configured Mirroring with Windows Authentication. The only thing you need to do is to add the SQL Server Database Engine service accounts to each instance. Then grant connect to permission on the endpoints.

If you are using Domain user account for SQL Server services, then there is no harm in configuring Database Mirroring with Windows Authentication.

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 8

Don't get down bud, first time I did mirroring I was pulling my hairs out also.

It is a strange problem you are getting for two hosts not be to be able to communicate following issues can exist ..

1) Security - SQL Server permissions on Endpoint; you confirmed this as correct (or have tried using certificates)

2) Firewall - You confirmed no firewall.

3) Communication - You are able to access the End-point ports from each server.

The other thing I was thinking, I don't see it mentioned in this post yet. The configuration of end point, what is the ROLE parameter set to? Since you did it using Wizard it should be correct but just another idea.

Have a look at these scripts; they will help you set up mirroring from scratch without wizard. But you'll need to modify them for your environment, the paths of data, log, and backups etc.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Sudeepta SSCommitted Points: 1666 More actions · Answer 9

The other thing you may need to re-check before configuring Database Mirroring is the DNS lookup. Make sure your instances get resolved from the DNS server, and both the instances can reach each other through PING.

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

After reading all the posts I'm back to what I had said earlier, add a login for the Service Account that SQL Server is running under then grant CONNECT permissions to the DB Mirroring endpoint. This needs to be done on each server. The login only needs public permission at the server level.

You will need to lookup in BOL how to accomplish the GRANT.

nano2nd Hall of Fame Points: 3073 More actions · Answer 11

First problem...I have no hair left to pull out....

For this last attempt I used the script samples presented by Randall Neth.

Firewall is disabled

It appears that I can establish a telnet connection between the servers.

ROLE = ALL

RandallNeth Grasshopper Points: 11 More actions · Answer 12

Did you ever get this working? I'll be glad to assist. Never posted on here before but I am pretty active elsewhere. Looks like I need to start looking here more often.

The mirroring script referenced works but there may be some underlying issues we can work through.

andydicken Newbie Points: 9 More actions · Answer 13

Make sure you restore the db on the mirror and leave it in the restoring state. ie, restore database [dbname] with norecovery

rajaster Ten Centuries Points: 1228 More actions · Answer 14

I encountered a similar problem and had some fun and thought it may help others.

When checking the endpoints check the names on both servers(or add the witness or any other mirrors as you require) and make sure they are the same.

e.g.

-- Verify the Database Mirroring Endpoint Status

SELECT * FROM sys.database_mirroring_endpoints

GO

IF they are not or if you continue having problems run the below on both servers.

Drop Endpoint [EndpointName]

GO

Then use the mirror security configuration and set them up again or run:

CREATE ENDPOINT [Mirroring]

AUTHORIZATION [ServerName\DBInstanceName]

STATE=STARTED

AS TCP (LISTENER_PORT = 7028, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE,

ENCRYPTION = REQUIRED ALGORITHM RC4)

GO

Note that if running on the Principal change the [ServerName\DBInstanceName] to the mirror DB instance name or if running on the mirror change the [ServerName\DBInstanceName] to the Principal DB instance name.

I do think that as long as the firewall is allowing routing to the specified ports the rest should work.

Please also note that if you use the scripts then also give grants as shown in:

http://sudeeptaganguly.wordpress.com/2010/07/25/how-to-configure-database-mirroring-in-an-active-directory-environment-using-sql-server-2008/

It was a very useful link and helped me to get to the bottom of my problems.

give me a shout and be useful to see if this helps others :cool:.