August 13, 2010 at 5:41 am
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
August 13, 2010 at 5:47 am
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
August 13, 2010 at 5:48 am
I think the link is broken, but I think I have found the article through google.
August 13, 2010 at 6:03 am
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
August 13, 2010 at 6:08 am
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.
August 13, 2010 at 8:55 am
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.
August 13, 2010 at 9:01 am
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
August 13, 2010 at 9:03 am
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.
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]
August 13, 2010 at 9:03 am
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
August 13, 2010 at 9:27 am
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.
August 13, 2010 at 9:31 am
October 10, 2010 at 10:39 pm
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.
MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
August 26, 2011 at 5:29 am
Make sure you restore the db on the mirror and leave it in the restoring state. ie, restore database [dbname] with norecovery
December 7, 2012 at 3:15 am
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:
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:.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply