December 2, 2009 at 9:32 am
I have 2 Virtual Machines running SQL Server 2005 and have the same DB as I have run a back on the principal and restored it on the server I want as my mirror.
I keep getting an error when trying to start the mirroring
Alter Failed for the database ‘DBName’
The server network address “TCP://MyServer.myip.net: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. Error1418
I have run ping through command prompt to see if it will ping each server. This works ok.
How can I get the DB to start mirroring?
December 2, 2009 at 1:07 pm
Do you have a firewall (windows or otherwise) between the two preventing communications on 5022 or 5023?
What version SQL server are both principal and mirror server?
When you ping the servers, were you pinging by hostname or by IP?
Are DNS and AD accessible in your virtual environment?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 2, 2009 at 1:09 pm
A couple more questions:
Are you setting up via GUI? Or are you setting up via TSQL Script?
http://weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 2, 2009 at 1:37 pm
They are internal VM machines no internet connections apart from internal ones, would that still need a firewall? if so then I'll check with my IT dept and find that out.
They both use SQL Server 2005 exactly the same disc
When I ping I have used both the hostname and the IP address
How do I find out about DNS & AD as not sure what they are?
I was setting this up with GUI in Management Studio
Just reading the link you added
Create initial database on mirror server
The first thing you need to do when setting up Database Mirroring is perform a full backup followed by a transaction log backup on the principal server. You then must restore these to the mirror server using the WITH NORECOVERY option of the RESTORE command.
What does this mean - transaction log backup ?
Also when I restored the mirror db I didnt select WITH NORECOVERY. I'll have another go now with that option selected and find out what happens
December 2, 2009 at 2:00 pm
I've just been reading that link posted a bit further,
If I want to quiery from a NORECOVERY database then it wont work as it will be in a locked state,
Can I still use Restore with Full Recovery to mirror?
If not what is a snapshot DB?
What I need to do is have a mirror db that is as live as the live db so that I can query this db for reporting purpose.
December 2, 2009 at 2:11 pm
Ok, so the servers are having trouble connecting to each other. It could be the port is blocked but most likely it is credentials.
do this on the mirror:
Lets check the credentials, right click on my computer and go to manage, open up event viewer, select application. Now try setting up the mirror, when it fails refresh the event viewer. Let me know if you get something like this:
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 192.168.104.56]
December 2, 2009 at 2:28 pm
I have done that and this is what it said afterwards
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:1474
Date:02/12/2009
Time:21:24:21
User:N/A
Computer:MyServerName
Description:
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://ThePrincipalServerName.MyCompanyName.net:5022'.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: c2 05 00 00 10 00 00 00 Â.......
0008: 10 00 00 00 4a 00 41 00 ....J.A.
0010: 4e 00 49 00 54 00 4f 00 N.I.T.O.
0018: 52 00 52 00 45 00 50 00 R.R.E.P.
0020: 4f 00 52 00 54 00 49 00 O.R.T.I.
0028: 4e 00 00 00 07 00 00 00 N.......
0030: 6d 00 61 00 73 00 74 00 m.a.s.t.
0038: 65 00 72 00 00 00 e.r...
December 2, 2009 at 2:30 pm
jez.lisle (12/2/2009)
They are internal VM machines no internet connections apart from internal ones, would that still need a firewall? if so then I'll check with my IT dept and find that out.They both use SQL Server 2005 exactly the same disc
When I ping I have used both the hostname and the IP address
How do I find out about DNS & AD as not sure what they are?
I was setting this up with GUI in Management Studio
Just reading the link you added
Create initial database on mirror server
The first thing you need to do when setting up Database Mirroring is perform a full backup followed by a transaction log backup on the principal server. You then must restore these to the mirror server using the WITH NORECOVERY option of the RESTORE command.
What does this mean - transaction log backup ?
Also when I restored the mirror db I didnt select WITH NORECOVERY. I'll have another go now with that option selected and find out what happens
Firewall is not needed - it adds one more thing to check if it is present. Often times Windows XP (and Server2003) has the Windows Firewall service turned on and configured.
DNS is a Name Service for networking computers. AD is Active Directory.
Transaction Log Backup - If you are running your database in full recovery, you will need to backup the transaction logs on a regular interval. The transaction log is where the transactions are stored for your database. Transaction log introduction: http://msdn.microsoft.com/en-us/library/ms190925.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 2, 2009 at 2:32 pm
jez.lisle (12/2/2009)
I have done that and this is what it said afterwardsEvent Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:1474
Date:02/12/2009
Time:21:24:21
User:N/A
Computer:MyServerName
Description:
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://ThePrincipalServerName.MyCompanyName.net:5022'.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: c2 05 00 00 10 00 00 00 Â.......
0008: 10 00 00 00 4a 00 41 00 ....J.A.
0010: 4e 00 49 00 54 00 4f 00 N.I.T.O.
0018: 52 00 52 00 45 00 50 00 R.R.E.P.
0020: 4f 00 52 00 54 00 49 00 O.R.T.I.
0028: 4e 00 00 00 07 00 00 00 N.......
0030: 6d 00 61 00 73 00 74 00 m.a.s.t.
0038: 65 00 72 00 00 00 e.r...
Are you using the localsystem account for the database services?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 2, 2009 at 2:40 pm
A couple more resources with step by steps:
http://wiki.cdyne.com/wiki/index.php?title=Mirroring_SQL_Server_2005
Servers not in a domain (workgroup model as an example)
http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx
Can you telnet to the Fully Qualified Domain Name of the Mirror partner on port 5023?
http://htmlgoodies.earthweb.com/beyond/webmaster/article.php/3456041
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 2, 2009 at 2:43 pm
this error looks like what you get when you are looking at the principle, what does the error on the mirror say?
December 3, 2009 at 2:46 am
Are you using the localsystem account for the database services?
Yes
December 3, 2009 at 3:46 am
jmessersmith-1145599 (12/2/2009)
this error looks like what you get when you are looking at the principle, what does the error on the mirror say?
This was done on the on the Mirror server.
I have tried it the opposite way to use the principal server as the mirror instead and I get same error.
December 3, 2009 at 7:42 am
I think you need to do this with scripts, it is a lot easier to see whats going on.
create the endpoints on both of the server and grant permissions:
Run this on the Principal
CREATE ENDPOINT [Principal]
AUTHORIZATION [NT AUTHORITY\SYSTEM]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
go
use master
go
create login [NT AUTHORITY\ANONYMOUS LOGON]FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
grant connect on endpoint::[Principal] to [NT AUTHORITY\ANONYMOUS LOGON]
Run this on the mirror
CREATE ENDPOINT [Mirror]
AUTHORIZATION [NT AUTHORITY\SYSTEM]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
go
use master
go
create login [NT AUTHORITY\ANONYMOUS LOGON]FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
grant connect on endpoint::[Mirror] to [NT AUTHORITY\ANONYMOUS LOGON]
backup the database from the principal
BACKUP DATABASE Testdb
TO DISK = 'c:\Testdb.Bak'
WITH FORMAT
BACKUP LOG Testdb
TO DISK = 'c:\Testdblog.trn'
restore the database to the mirror you will have to more the files to the other server:
RESTORE DATABASE Testdb
FROM DISK = 'c:\Testdb.Bak'
WITH NORECOVERY, NOUNLOAD, REPLACE
RESTORE LOG Testdb
FROM DISK = 'c:\Testdblog.trn'
WITH FILE=1, NORECOVERY
On the mirror setup the partner:
ALTER DATABASE Testdb SET PARTNER ='tcp://principal.domain.com:5022';
and connect the principal to the mirror:
ALTER DATABASE Testdb SET PARTNER ='tcp://mirror.domain.com:5022';
read up on what a principal and a mirrors roles are if you are confused.
December 3, 2009 at 9:46 am
jez.lisle (12/3/2009)
Are you using the localsystem account for the database services?
Yes
When using the localsystem account for the database services, you will need to establish a certificate for the mirror in order for the two servers to talk. Or you will need to change the localsystem account to a domain account with adequate permissions for both servers.
The links I provided cover both of those options.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply