Help setting up Mirroring (Error: 1418)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Replication (2012)

Help setting up Mirroring (Error: 1418)

Author

Topic

lkerznowski
Starting Member

22 Posts

Posted - 2014-07-17 : 12:17:46

Firstly, yes I'm aware it's a "very common issue" that's been discussed to death. I've spent the last 2 days researching EVERYTHING I could on the issue, and I'm still having problems. Don't tell me to Google it; I'm way past that.

I want to set up Mirroring between two servers, so that we have a fallback in case of emergencies. I'll walk through all the steps that I've done, and I'm hoping someone can see where I went wrong, and why I still get Error 1418.

Both servers are using Microsoft Server 2008 R2 Enterprise, and both are using Microsoft SQL Server 2012 - 11.0.2218.0

In our company domain, I have created XXXXXXXXX\SQL_USER to be used as the account.

For both servers, I went into Services, and changed the "log on as" for SQL Server and SQL Server Agent to XXXXXXXXX\SQL_USER

For both servers, I went into SQL Server Configuration Manager. Here I checked that the services were running and logged on correctly. I also went into SQL Server network Configurations -> Protocols for <server name>, and made sure that Shared Memory, Named Pipes, and TCP/IP were enables.

I went into the Primary Database and changed the Recovery model to Full. Next, I ran a Full Backup and Transaction Log Backup. Both of which got saved to \\XXXXXX-DB2\xxxxxxdb_backups.

Next, I went onto the Mirror Database, and restored both the Full and Transaction Log backups. For both, I made sure to set them to NORECOVERY. Also, I pointed the file location to \\XXXXXX-DB2\xxxxxxdb_backups as well. Once restored, I made sure the new database copy was in RECOVERING mode.

On both of the Databases, I ran this:

CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 7022 )
FOR DATABASE_MIRRORING
(ENCRYPTION = DISABLED, ROLE=ALL)
GO

Finally, I went to the Primary database and selected Tasks -> Mirror...

Status at bottom:
"This database has not been configured for mirroring"

I then went through the Mirroring Security Wizard:

I did not include a witness server (not yet, I wanted to make it work first). The Principal Server Instance is set to XXXXXX-DB1\xxxxxxtest [correct], and the Mirror Server Instance is set to XXXXXX-DB2\xxxxxxtest [correct as well]. For both of those, the Listener Port / Endpoint name / Encryption settings were grayed out since they were already set before.

For the Service Accounts, I left them both blank.

The conformation screen confirms everything above.

After this "Succeeds", I still have the Status at bottom:
"This database has not been configured for mirroring".

Attempt to start mirroring produces Error 1418.

---------------------------------------------------------------------------

Most of my research has lead me to either not being restored correctly, not in RECOVERING mode, or the USER setup. The first two should be fine already. As per the users:

The account is on our business domain.
It is set up in the Services (to my knowledge) correctly.
The account has access to the backup file location.
In both servers, under security -> logins, the account has all "Server Roles" checked, and it is "User Mapped" to all the databases (with all membership options)

If anyone has ANY idea what may be wrong please let me know. Our business has a Cooperate version of Kaspersky, but that is not installed on either server, so that should not be blocking anything.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-17 : 12:48:13

Try granting the login local admin in Windows on both servers.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lkerznowski
Starting Member

22 Posts

Posted - 2014-07-17 : 13:04:07

Alright. I added the XXXXXXXXX\SQL user as a local admin as well on both servers. I then logged out and in, and did the steps again (I'm using a test database as the primary not the live, and this test is small enough to run quickly)... no change. Still having the same issue.

I also tried logging into the server using the SQL username (as I was using my personal Admin before). No difference.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-17 : 13:46:53

Lets't try via T-SQL rather than the wizard. You can use these steps even though the article is for 2005: http://weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx

This is just to complete the test, not your final config as this one sets things to async. If you don't have Enterprise edition (I can't remember if 2012 allows async for non-Enterprise edition), you can use sync by changing "SAFETY OFF" to "SAFETY FULL".

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lkerznowski
Starting Member

22 Posts

Posted - 2014-07-17 : 14:12:24

quote:
Originally posted by tkizer

Lets't try via T-SQL rather than the wizard. You can use these steps even though the article is for 2005: http://weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx

This is just to complete the test, not your final config as this one sets things to async. If you don't have Enterprise edition (I can't remember if 2012 allows async for non-Enterprise edition), you can use sync by changing "SAFETY OFF" to "SAFETY FULL".

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

I get to:

Set partner and setup job on mirror server
ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'
EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute

At this stage, I'm on the db2 server (the mirror one). The query I'm using is:

alter database xxxxxxdb set partner = N'TCP://xxxxxxtest.<domain>.com:7024'
go

This comes with the error:

Mes 1431, Level 16, State 4, Line 1
Neither the partner nor the witness server instance for database "xxxxxxdb" is available. Reissue the command when at least one of the instances becomes available.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-17 : 14:13:50

Is the port open between the two servers? Do a telnet test each way to verify. You may need to add telnet to your servers due to your OS.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lkerznowski
Starting Member

22 Posts

Posted - 2014-07-17 : 14:38:22

I'm guessing not. Sorry, I'm just starting to learn in depth SQL, and I know very very little about TelNet. From what I figured out how to use...

From DB1 (Primary):
open XXXXXX-db2.xxxxxx.xxxxxx-xxxx.com 7024

Connection fails.

From DB2 (Mirror):
open XXXXXXTEST.xxxxxx.xxxxxx-xxxx.com 7024

Connection fails.

I tried them both without a port (defaulting to 23), and those failed as well. How do I go about opening them up?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-17 : 15:07:30

Switch "open" to "telnet". If it tells you telnet is unavailable (very likely as it's not the default starting with 2008 I believe), you'll need to add that feature to both servers. Adding the feature does not require a reboot.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lkerznowski
Starting Member

22 Posts

Posted - 2014-07-17 : 15:22:11

Both failed; tried with a port, and with default.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-17 : 15:24:14

That port is blocked then. You'll need to determine why, such as a firewall or Windows firewall. Work with a network engineer/server administrator on this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lkerznowski
Starting Member

22 Posts

Posted - 2014-07-17 : 15:59:55

I'm not completely sure how the ports would be blocked in the first place.

Right now, I'm using a test server instance off the main server (db1) and a second server (db2) we made to test this out. Both of them do not have Kaspersky installed, have their firewalls turned completely off, and at the moment are connected by just a switch between them. They should be both open?

The only other thing we have is a sonicwall, but that does not come into play until leaving the network (which this isn't).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-17 : 16:03:48

I can't help you with that, but the telnet test proves (as long as the test was run properly) the ports are not open between the two servers.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lkerznowski
Starting Member

22 Posts

Posted - 2014-07-17 : 16:16:35

Maybe you can help with this. I started looking at the event viewer on both servers.

On the first there was a lot of "port listening" "port disconnected" which makes since from testing everything, but on the second server I'm getting something weird.

There are a huge number of "Login failed for user 'TRACEYONE\TRACEY-DB2$'. Reason: Could not find a login match the name provided [CLIENT: <local machine>]"

TRACEYONE is the domain, and TRACEY-DB2 is the both the computer name and the server name (well, it's a default server so MSSQLSERVER but it's accessed by DB2.

What is trying to log in using that?

The Services in both servers have correct with the Log-on of TRACEYONE/SQL, but for some reason there is a generic TRACEYONE/TRACEY-DB2 trying to sing on somewhere during the testing. Any ideas on that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-17 : 17:09:27

TRACEY_DB2$ is the machine account actually (servername+$). I believe that's used by the local system account.

What do you mean by "both the computer name and the server name"? How is a computer different than a server in this context?

What is trying to login with that? I have no idea.

But these errors are meaningless to the issue right now as the port needs to be open between the two servers. Could you do a screenshot of the telnet test that includes the command you use and the output?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lkerznowski
Starting Member

22 Posts

Posted - 2014-07-18 : 09:56:17

My apolagies. I guess I explained that weird.

On the DB1, we have four different sql instances there. Thus, they are accessed by <server>/<instance name> (ie, TRACEY-DB1\EXTENDTEST. The DB2 only has the testing server set up and the instance is defaulted to MSSQLSERVER. However, because only that one exists, it's accessed by <server> only (ie, just TRACEY-DB2).

TRACEY-DB1 and TRACEY-DB2 are also the Computer Name, so I did not know if that made a difference.

TRACEY-DB1 --> TRACEY-DB2

I tried to connect to the Mirror Server TRACEY-DB2 using both just the Server Name, and then <server name>.<domain>. I also tried without a requested port (so it used 23), and both of those produced the same errors as well.

TRACEY-DB2 --> TRACEY-DB1

This one was trying to connect to the Primary Server TRACEY-DB1. I attempted to connect through:
<server name>
<server name>.<domain>
<server name>\<instance>
<instance>

The only one that produced change was <server name> no domain. It was the first one, and did not show on the window. After entering

telnet TRACEY-DB1 5022

It went to a blank screen. After pressing about 15 characters, it went back to where you see now.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-18 : 12:44:46

The blank screen is good! That means that port is open. The issue is you are telnet testing with port 5022, but your endpoints are using 7022 and your ALTER DATABASE commands are using 7024. For the telnet test, we only care about the mirroring port that you are going to use. And please specify the same port in your ALTER DATABASE command as in the endpoint.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lkerznowski
Starting Member

22 Posts

Posted - 2014-07-18 : 13:20:58

Oh sorry. I never mentioned. I've been looking up a lot of information trying to fix this error. The first site was using 5022 so I was trying that; another site later on tried using 7022; while another was using 7024.

I've been kind of switching back and forth between them, but always making sure both were updated together. I was just testing with 5022 because that was the latest update I made using those. To my knowledge the actual port should not matter?

Either way then, I have an open 5022 from DB2 -> DB1 then, but it's not open the other way?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-18 : 13:40:22

Yes the port matters, in the sense that it can't be in use by another app and the port must be open between the two servers. Each server can use a different port, but the port must be open from that server to the other server.

Yes you'll need to get 5022 opened from DB2 to DB1, if you plan on using 5022.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lkerznowski
Starting Member

22 Posts

Posted - 2014-07-18 : 14:36:28

Alright, well I think I figured out a* problem. When creating the endpoints, I think* they need to be different names. Every example had something like "Mirroring", so I was using that both times. I also use a different port for each one. I just tried the setup:

TRACEY-DB1

create endpoint Mirror_db1
state = started
as tcp (listener_port = 5022, listener_ip = ALL)
for DATABASE_MIRRORING
(encryption = disabled, role = all)
go

TRACEY-DB2

create endpoint Mirror_db2
state = started
as tcp (listener_port = 5023, listener_ip = ALL)
for DATABASE_MIRRORING
(encryption = disabled, role = all)
go

After executing these and restarting the instances... the telnet's both went to the blank screen where I had to hit around 15 characters:

DB1:
telnet TRACEY-DB2 5023

DB2:
telnet TRACEY-DB1 5022

I also double checked, and using "netstat -an" I saw that both were LISTENING on the correct ports, and were both TCP.

However, attempting to activate Mirroring on the Primary server still produces:

The server network address "TCP://TRACEY-DB2.<domain>.com:5023" can not be reached or does not exist (Error: 1418).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-18 : 14:42:28

The mirroring endpoints do not have to be different. We always used "Mirroring" on all servers. For simplicity, we used the same port too.

For your telnet test, also try:

DB1:
telnet TRACEY-DB2.<domain>.com 5023

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-18 : 14:45:30

Before you got that error, did you successfully run this on the mirror: ALTER DATABASE Blah SET PARTNER = N'TCP://TRACEY-DB1.<domain>.com:5022'? The order of steps is important.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources