June 20, 2009 at 10:56 pm
Hey everyone,
I have hit a wall and just keep banging my head against it. So, needless to say, I need some assistance.
I am trying to setup our first mirroring system. We have 3 different severs setup.
Principal, Mirror, Witness.
I initially tried setting up the Mirroring without Certificates, but through numerous error messages, found out that I needed certificates. So I set them up. Code is at end.
As near as I can tell, everything is setup correctly. However, after running the ALTER DATABASE (again at the end) statement to connect the Principal to the Mirror it fails. I get the following error message in the Mirror server Error Log:
Length specified in network packet payload did not match number of bytes read; the connection has been close. Please contact the vendor of the client library [CLIENT: 192.168.100.60]
The only thing that I can find about the error is that they think someone is trying to hack my system. I know that isn't the case as it only happens when I run the ALTER DATABASE statement to initialize the mirroring. So what the heck is going on.
Thanks for all of the help.
Fraggle
/**********************************************
set up outbound certificates
**********************************************/
Create Certificate Mirror
with subject 'certificate for mirror access'
create endpoint Mirror_test
state = started
as tcp (listener_port = 7022
, listener_ip = all
)
for database_mirroring
(authentication = certificate Mirror
, Encryption = required algorithm AES
, role = all
)
backup certificate Mirror to file 'c:\mirror_cert.cer'
/**********************************************
set up inbound certificates
**********************************************/
CREATE LOGIN Mirror_Login with Password = '1234Mirror$
CREATE USER usrMirror for Login Mirror_Login
Create Certificate Principal
authorization usrMIrror
from file = 'c:\principal_cert.cer'
grant connect on endpoint::Mirror to Mirror_Login
-- I recreate this 2nd process for the witness server
alter database Mirror_test
set partner = 'tcp://Principal.test.local:7022
I have the same code to crate certificates and logins on the witness and principal servers.
Below is the code that is run on the principal after the above code to alter the database is run
ALTER DATABASE Mirror_test
set partner = 'TCP://Mirror.test.local:7022'
I get the standard error line message of
the server network address "TCP://Mirror.test.loccal:7022' can not be reached or does not exist. Check the network address name and thatthe ports for the local and remote endpoints are operation.
The error message at the top of this post is what is seen on the Mirror server.
Error message on Principal states
Database mirroring has been terminated for database Mirror_test'. this is an informational message only. no user action is required (YEA RIGHT!)
June 22, 2009 at 7:00 am
TCP://Mirror.test.loccal:7022 <--- is that a typo?
June 22, 2009 at 7:38 am
i had similar issues. I tried setting up with GUI (configure security) and saw that authentication mode was 'negotiate' and it worked perfectly. I tried using certificates but no help.
my mirror n principle was on same server (diff instances)
June 22, 2009 at 9:15 pm
oded.raz (6/21/2009)
Why do you need certificates.Does all of the server connected to the same domain.
Take a look at this tutorial - http://www.dbsnaps.com/articles/sql_mirror_1/[/url]
Oded
Oded,
Nice little demo. However, I have two issues.
1) I have tried this method and it didn't work. Hence the reason I tried certificates.
2) The only thing that I didn't do was setup the login/password from the built-in account to 'this account'. I really don't have the option to do this as I have to restart the server, and that is a production server that must be available unless there is a reason to take it down. And since there didn't appear to be any sound, I don't know if this was required.
Thanks
Fraggle
June 22, 2009 at 9:20 pm
oded.raz (6/21/2009)
Why do you need certificates.Does all of the server connected to the same domain.
Take a look at this tutorial - http://www.dbsnaps.com/articles/sql_mirror_1/[/url]
Oded
Oded,
Nice little demo. However, I have two issues.
1) I have tried this method and it didn't work. Hence the reason I tried certificates.
2) The only thing that I didn't do was setup the login/password from the built-in account to 'this account'. I really don't have the option to do this as I have to restart the server, and that is a production server that must be available unless there is a reason to take it down. And since there didn't appear to be any sound, I don't know if this was required.
In answer to your other question, all of the servers are located in the same domain. For that matter, they are inches away from each other.
Thanks
Fraggle
June 22, 2009 at 9:22 pm
oded.raz (6/21/2009)
Why do you need certificates.Does all of the server connected to the same domain.
Take a look at this tutorial - http://www.dbsnaps.com/articles/sql_mirror_1/[/url]
Oded
Oded,
Nice little demo. However, I have two issues.
1) I have tried this method and it didn't work. Hence the reason I tried certificates.
2) The only thing that I didn't do was setup the login/password from the built-in account to 'this account'. I really don't have the option to do this as I have to restart the server, and that is a production server that must be available unless there is a reason to take it down. And since there didn't appear to be any sound, I don't know if this was required.
In answer to your other question, all of the servers are located in the same domain. For that matter, they are inches away from each other.
Thanks
Fraggle
June 22, 2009 at 9:25 pm
I have also copied 2 files with this reply if you want to follow (its without a certificate and works fine .You need to add the witness steps if you follow those scripts which is simple)
STEPS TO FOLLOW :-
=================
1. Log in to both boxes as local Admin.
2. Change SQL Server startup account to use "Network Service" Or “Local System Account”
3. Add NT AUTHORITY\NETWORK SERVICE to the admin group for both servers
4. On server A ( Principal server ) execute the following
It will Configure Outbound Connections
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!';
GO
-- USE master
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A certificate',
START_DATE = '04/10/2007'
GO
-- CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT=5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_A_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer';
GO
5. Now copy HOST_A_cert.cer to server B (C:\HOST_A_cert.cer) and Copy HOST_A_cert.cer to server C (C:\HOST_A_cert.cer)
6. On server B (Mirror server) Execute the following
It will Configure Outbound Connections
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
GO
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT = 'HOST_B certificate for database mirroring',
START_DATE ='05/10/2007'
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_B_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer';
GO
7. Now copy HOST_B_cert.cer to server A (C:\HOST_B_cert.cer) and Copy HOST_A_cert.cer to server C (C:\HOST_A_cert.cer)
8. On server A ( Principal server ) Execute the following
It will Configure Inbound Connections
USE master;
CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'C:\HOST_B_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO
9. On server B ( Mirror server ) Execute the following
It will Configure Inbound Connections
USE master;
CREATE LOGIN HOST_A_login WITH PASSWORD = '=Sample#2_Strong_Password2';
GO
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'C:\HOST_A_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO
10.On server C ( Witness server ) execute the following
It will Configure Outbound Connections
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!';
GO
USE master
CREATE CERTIFICATE HOST_C_cert
WITH SUBJECT = 'HOST_C certificate',
START_DATE = '04/10/2007'
GO
-- CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT=5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_C_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = Witness
);
GO
BACKUP CERTIFICATE HOST_C_cert TO FILE = 'C:\HOST_C_cert.cer';
GO
11. Now copy HOST_C_cert.cer to server B (C:\HOST_C_cert.cer) and Copy HOST_C_cert.cer to server A (C:\HOST_C_cert.cer)
12. Execute the following steps one by one …
On Principal Server
{
USE master;
CREATE LOGIN HOST_C_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
CREATE USER HOST_C_user FOR LOGIN HOST_C_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_C_cert
AUTHORIZATION HOST_C_user
FROM FILE = 'C:\HOST_C_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO
}
--
On Mirror Server
{
USE master;
CREATE LOGIN HOST_C_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
CREATE USER HOST_C_user FOR LOGIN HOST_C_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_C_cert
AUTHORIZATION HOST_C_user
FROM FILE = 'C:\HOST_C_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO
}
---
13.On server C ( Witness server ) Execute the following
It will Configure Inbound Connections
USE master;
CREATE LOGIN HOST_A_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
CREATE USER HOST_A_user FOR LOGIN HOST_B_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'C:\HOST_A_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO
On server C ( Witness server ) Execute the following
It will Configure Inbound Connections
USE master;
CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'C:\HOST_B_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO
14. Take a Full & Transaction log Backup of the Database (on Principal) and Restore on Mirror With NoRecovery.
15. On Mirror Server B Execute :
Alter Database
Set Partner =’TCP://:5022’
Go
16. On Principal Server A Execute :
Alter Database
Set Partner =’TCP://:5022’
Go
13. On Principal Server A Execute :
Alter Database
Set Witness=’TCP://:5023’
Go
è Test the DBM works, by shutting down the service on principal. Once we did this this mirror assumed the role of the principal.
è Automatic Failover will happen only if we have a witness server. Otherwise manual failover will have to be done.
RESOLUTION
============
We setup the database mirroring on a workgroup including the witness server following the above steps.
USEFUL KNOWLEDGE BASE ARTICLES
===================================
Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms191140.aspx
Monitoring Mirroring Status
http://msdn2.microsoft.com/en-us/library/ms365781.aspx
Using Database Mirroring
http://msdn2.microsoft.com/en-us/library/ms131373.aspx
Connecting Clients to a Mirrored Database
http://msdn2.microsoft.com/en-us/library/ms175484.aspx
sys.database_mirroring
This view displays the database mirroring metadata for each mirrored database in a server instance. For more information, see sys.database_mirroring.
sys.database_mirroring_endpoints
The sys.database_mirroring_endpoints catalog view displays information about the database mirroring endpoint of the server instance.
sys.database_mirroring_witnesses
This catalog view displays the database mirroring metadata for each session in which a server instance is the witness.
sys.dm_db_mirroring_connections
This a dynamic management view returns a row for each database mirroring network connection.
HYPERLINK "http://blogs.msdn.com/sharepoint/default.aspx"Microsoft SharePoint Products and Technologies Team Blog
http://blogs.msdn.com/sharepoint/archive/2007/03/06/configuring-database-mirroring-failover-for-sharepoint-products-technologies.aspx
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 22, 2009 at 9:28 pm
I have also copied 2 files with this reply if you want to follow .You need to add the witness steps if you follow those scripts which is simple.
STEPS TO FOLLOW :-
=================
1. Log in to both boxes as local Admin.
2. Change SQL Server startup account to use "Network Service" Or “Local System Account”
3. Add NT AUTHORITY\NETWORK SERVICE to the admin group for both servers
4. On server A ( Principal server ) execute the following
It will Configure Outbound Connections
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!';
GO
-- USE master
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A certificate',
START_DATE = '04/10/2007'
GO
-- CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT=5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_A_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer';
GO
5. Now copy HOST_A_cert.cer to server B (C:\HOST_A_cert.cer) and Copy HOST_A_cert.cer to server C (C:\HOST_A_cert.cer)
6. On server B (Mirror server) Execute the following
It will Configure Outbound Connections
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
GO
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT = 'HOST_B certificate for database mirroring',
START_DATE ='05/10/2007'
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_B_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer';
GO
7. Now copy HOST_B_cert.cer to server A (C:\HOST_B_cert.cer) and Copy HOST_A_cert.cer to server C (C:\HOST_A_cert.cer)
8. On server A ( Principal server ) Execute the following
It will Configure Inbound Connections
USE master;
CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'C:\HOST_B_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO
9. On server B ( Mirror server ) Execute the following
It will Configure Inbound Connections
USE master;
CREATE LOGIN HOST_A_login WITH PASSWORD = '=Sample#2_Strong_Password2';
GO
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'C:\HOST_A_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO
10.On server C ( Witness server ) execute the following
It will Configure Outbound Connections
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!';
GO
USE master
CREATE CERTIFICATE HOST_C_cert
WITH SUBJECT = 'HOST_C certificate',
START_DATE = '04/10/2007'
GO
-- CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT=5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_C_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = Witness
);
GO
BACKUP CERTIFICATE HOST_C_cert TO FILE = 'C:\HOST_C_cert.cer';
GO
11. Now copy HOST_C_cert.cer to server B (C:\HOST_C_cert.cer) and Copy HOST_C_cert.cer to server A (C:\HOST_C_cert.cer)
12. Execute the following steps one by one …
On Principal Server
{
USE master;
CREATE LOGIN HOST_C_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
CREATE USER HOST_C_user FOR LOGIN HOST_C_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_C_cert
AUTHORIZATION HOST_C_user
FROM FILE = 'C:\HOST_C_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO
}
--
On Mirror Server
{
USE master;
CREATE LOGIN HOST_C_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
CREATE USER HOST_C_user FOR LOGIN HOST_C_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_C_cert
AUTHORIZATION HOST_C_user
FROM FILE = 'C:\HOST_C_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO
}
---
13.On server C ( Witness server ) Execute the following
It will Configure Inbound Connections
USE master;
CREATE LOGIN HOST_A_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
CREATE USER HOST_A_user FOR LOGIN HOST_B_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'C:\HOST_A_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO
On server C ( Witness server ) Execute the following
It will Configure Inbound Connections
USE master;
CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
--Associate the certificate with the user.
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'C:\HOST_B_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO
14. Take a Full & Transaction log Backup of the Database (on Principal) and Restore on Mirror With NoRecovery.
15. On Mirror Server B Execute :
Alter Database
Set Partner =’TCP://:5022’
Go
16. On Principal Server A Execute :
Alter Database
Set Partner =’TCP://:5022’
Go
13. On Principal Server A Execute :
Alter Database
Set Witness=’TCP://:5023’
Go
è Test the DBM works, by shutting down the service on principal. Once we did this this mirror assumed the role of the principal.
è Automatic Failover will happen only if we have a witness server. Otherwise manual failover will have to be done.
RESOLUTION
============
We setup the database mirroring on a workgroup including the witness server following the above steps.
USEFUL KNOWLEDGE BASE ARTICLES
===================================
Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms191140.aspx
Monitoring Mirroring Status
http://msdn2.microsoft.com/en-us/library/ms365781.aspx
Using Database Mirroring
http://msdn2.microsoft.com/en-us/library/ms131373.aspx
Connecting Clients to a Mirrored Database
http://msdn2.microsoft.com/en-us/library/ms175484.aspx
sys.database_mirroring
This view displays the database mirroring metadata for each mirrored database in a server instance. For more information, see sys.database_mirroring.
sys.database_mirroring_endpoints
The sys.database_mirroring_endpoints catalog view displays information about the database mirroring endpoint of the server instance.
sys.database_mirroring_witnesses
This catalog view displays the database mirroring metadata for each session in which a server instance is the witness.
sys.dm_db_mirroring_connections
This a dynamic management view returns a row for each database mirroring network connection.
HYPERLINK "http://blogs.msdn.com/sharepoint/default.aspx"Microsoft SharePoint Products and Technologies Team Blog
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 22, 2009 at 9:32 pm
Abhay,
Thanks for the detail reply. I did almost everything you did, but there are a couple I didn't. I will give it a try and let you know in the next day or two.
Thanks,
Fraggle
June 23, 2009 at 10:41 pm
Abhay,
It worked!!!!!!!!
Funny thing is, the only thing that you did differently than I did was the certificate start_dates. I wonder why that made a difference?
Oh well, maybe the certificate was expired. Regardless, nice walk through. I did catch one type on the thing, but I wasn't worry about it.
CREATE USER HOST_A_user FOR LOGIN HOST_B_login;
This probably should have been HOST_A_Login. Atleast that is what I put in. This was under the Witness Configuration section.
Thanks for the assistance everyone. 😀
Fraggle.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply