Prev: trigger on select
Next: Waitstats Performance on sql 2005
From: Sveta on 30 Nov 2009 07:07
Hi,

That is fantastic artical. I did it all step by step and still get the error message 1418, The server network address "TCP://MLCMirror.csgroup.local:1051" can not be reached or does not exist. I cannot solve this problem for a good few weeks now. Tried a quet few combinations, but still getting the same error. Is there any way to check if trust was established in between two servers? I will be really greatfull if you could help.



bass_player wrote:

It's really not a good idea to hst your SQL Server on a domain
25-Feb-09

It's really not a good idea to hst your SQL Server on a domain controller

Several things you need to consider when doing database mirroring:
1) since you are connecting a domain controller to another domain
controller, isn't there a trust relationship between the two domains (i am
asking this question not to encourage you to still run SQL Server on a
domain controller)? If there is, you can use a service account for the SQL
Server instances on both servers that is a domain account. Then, give this
account permissions on the endpoint
2) If there are no trust relationships between the two domains, you would be
in a workgroup scenario. Your best bet is to use certificates. Follow the
steps outline below which is also available from
http://msdn.microsoft.com/en-us/library/ms186384(SQL.90).aspx If the two
servers are not available on your DNS servers, you will have to update your
hosts file to reflect the hostnames with the corresponding IP address
==========================================================
--STEP 1: Run this on testServer1
USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';
GO
--STEP 2:
CREATE CERTIFICATE testServer1_cert WITH SUBJECT = 'testServer1 certificate
for database mirroring'
GO

--STEP 3
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
AS TCP(LISTENER_PORT = 7024, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE testServer1_cert,
ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

--STEP 4
BACKUP CERTIFICATE testServer1_cert TO FILE = 'C:\testServer1_cert.cer';
GO

--STEP 5: Copied C:\testServer1_cert.cer to testServer2 on C:\ drive
--===========================================================
--STEP 1: Run this on testServer2
USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';
GO
--STEP 2:
CREATE CERTIFICATE testServer2_cert WITH SUBJECT = 'testServer2 certificate
for database mirroring'
GO

--STEP 3
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
AS TCP(LISTENER_PORT = 7024, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE testServer2_cert,
ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

--STEP 4
BACKUP CERTIFICATE testServer2_cert TO FILE = 'C:\testServer2_cert.cer';
GO

--STEP 5: Copied C:\testServer2_cert.cer to testServer1 on C:\ drive

--============================================================
--STEP 1: Configure testServer1 for Inbound connections. Run this on
testServer1
USE master

CREATE USER user1_mirroring
FOR LOGIN user1_mirroring
GO

--Associate the certificate with the user
CREATE CERTIFICATE testServer2_cert
AUTHORIZATION user1_mirroring
FROM FILE = 'C:\testServer2_cert.cer'
GO


--STEP 2:
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [user1_mirroring];
GO

--=====================================================================
--STEP 1: Configure testServer2 for Inbound connections. Run this on
testServer2
USE master

CREATE USER user1_mirroring
FOR LOGIN user1_mirroring
GO

--Associate the certificate with the user
CREATE CERTIFICATE testServer1_cert
AUTHORIZATION user1_mirroring
FROM FILE = 'C:\testServer1_cert.cer'
GO

--STEP 2:
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [user1_mirroring];
GO

--=======================
--Prepare for mirroring. Run this on the MIRROR (testServer2) first before
doing it on the PRINCIPAL (testServer1)
USE master
GO
ALTER DATABASE MRI SET PARTNER = 'TCP://testServer2.yourDomain.local:7024';
GO

--=======================
--Prepare for mirroring. Run this on the PRINCIPAL (testServer1) after
running the previous script on the MIRROR (testServer2)
USE master
GO
ALTER DATABASE testDB SET PARTNER =
'TCP://testServer1.yourDomain.local:7024';
GO






"Holydon via SQLMonster.com" <u46718(a)uwe> wrote in message
news:923dc1217b26b(a)uwe...

Previous Posts In This Thread:

On Wednesday, February 25, 2009 10:58 AM
Holydon via SQLMonster.com wrote:

sql server 2005 Mirroring problems
Hello

Please help me solve mirroring issue.
I have 2 servers. 1 is live and clients are connected to it while the other
is connected to eachother (Peer by Peer) by the available 2nd LAN port. Both
of them are domain controller. Server1 FQDN is serv.domain.com, while Server2
is just serv (Becauseit is a standalone Domain controller) i.e. No Clients
are connected to it and it is not added to Server1 domain.Both have Ip 10.0.0.
2 and 10.0.0.3 ips
They can ping each other by FQDN (added that to hosts file). All ports are
open.
I am trying to configure mirror.
server1 is principle and server2 mirror server.
I have backuped database and transaction logs on server1 and restored to
server2 with NO
RECOVERY option. Database in srv2 now is with "in recovery" status.
Then i try to configure mirror on srv1.
Using port 5022 on both servers.
Service accounts for instances: leave empty.
Configuring endpoints finished with "Success" status.
But when i try to start mirroring i get this error:

Alter failed for Database 'database'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The server network address "TCP://ser.domain:5022" can not be reached or does
not exist. Check the network address name and that the ports for the local
and remote endpoints are operational.

What can be wrong?

Thank you for help in advance

--
HolyDon

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200902/1

On Wednesday, February 25, 2009 1:56 PM
bass_player wrote:

It's really not a good idea to hst your SQL Server on a domain
It's really not a good idea to hst your SQL Server on a domain controller

Several things you need to consider when doing database mirroring:
1) since you are connecting a domain controller to another domain
controller, isn't there a trust relationship between the two domains (i am
asking this question not to encourage you to still run SQL Server on a
domain controller)? If there is, you can use a service account for the SQL
Server instances on both servers that is a domain account. Then, give this
account permissions on the endpoint
2) If there are no trust relationships between the two domains, you would be
in a workgroup scenario. Your best bet is to use certificates. Follow the
steps outline below which is also available from
http://msdn.microsoft.com/en-us/library/ms186384(SQL.90).aspx If the two
servers are not available on your DNS servers, you will have to update your
hosts file to reflect the hostnames with the corresponding IP address
==========================================================
--STEP 1: Run this on testServer1
USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';
GO
--STEP 2:
CREATE CERTIFICATE testServer1_cert WITH SUBJECT = 'testServer1 certificate
for database mirroring'
GO

--STEP 3
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
AS TCP(LISTENER_PORT = 7024, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE testServer1_cert,
ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

--STEP 4
BACKUP CERTIFICATE testServer1_cert TO FILE = 'C:\testServer1_cert.cer';
GO

--STEP 5: Copied C:\testServer1_cert.cer to testServer2 on C:\ drive
--===========================================================
--STEP 1: Run this on testServer2
USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';
GO
--STEP 2:
CREATE CERTIFICATE testServer2_cert WITH SUBJECT = 'testServer2 certificate
for database mirroring'
GO

--STEP 3
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
AS TCP(LISTENER_PORT = 7024, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE testServer2_cert,
ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

--STEP 4
BACKUP CERTIFICATE testServer2_cert TO FILE = 'C:\testServer2_cert.cer';
GO

--STEP 5: Copied C:\testServer2_cert.cer to testServer1 on C:\ drive

--============================================================
--STEP 1: Configure testServer1 for Inbound connections. Run this on
testServer1
USE master

CREATE USER user1_mirroring
FOR LOGIN user1_mirroring
GO

--Associate the certificate with the user
CREATE CERTIFICATE testServer2_cert
AUTHORIZATION user1_mirroring
FROM FILE = 'C:\testServer2_cert.cer'
GO


--STEP 2:
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [user1_mirroring];
GO

--=====================================================================
--STEP 1: Configure testServer2 for Inbound connections. Run this on
testServer2
USE master

CREATE USER user1_mirroring
FOR LOGIN user1_mirroring
GO

--Associate the certificate with the user
CREATE CERTIFICATE testServer1_cert
AUTHORIZATION user1_mirroring
FROM FILE = 'C:\testServer1_cert.cer'
GO

--STEP 2:
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [user1_mirroring];
GO

--=======================
--Prepare for mirroring. Run this on the MIRROR (testServer2) first before
doing it on the PRINCIPAL (testServer1)
USE master
GO
ALTER DATABASE MRI SET PARTNER = 'TCP://testServer2.yourDomain.local:7024';
GO

--=======================
--Prepare for mirroring. Run this on the PRINCIPAL (testServer1) after
running the previous script on the MIRROR (testServer2)
USE master
GO
ALTER DATABASE testDB SET PARTNER =
'TCP://testServer1.yourDomain.local:7024';
GO






"Holydon via SQLMonster.com" <u46718(a)uwe> wrote in message
news:923dc1217b26b(a)uwe...

On Thursday, February 26, 2009 10:55 AM
Holydon via SQLMonster.com wrote:

Thank you so much .......man...I appreciate your help.....
Thank you so much .......man...I appreciate your help.....

bass_player wrote:

--
HolyDon

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200902/1


Submitted via EggHeadCafe - Software Developer Portal of Choice
Using Server-Side IFRAMES to show Ads
http://www.eggheadcafe.com/tutorials/aspnet/4c4c5f62-678e-4e05-87fd-4477a0d1987c/using-serverside-iframes.aspx
 | 
Pages: 1
Prev: trigger on select
Next: Waitstats Performance on sql 2005