Sql Server Error 1474 State 1

SQL Server – AlwaysON Availability Group Replica is Not Synchronizing (Error: 976, Severity: 14, State: 1.) By Enrique SQL SERVER, High Availability / Disaster Recovery You can lose communication between the primary and secondary replicas for some reason.

20:24:41 Error: 1479, Severity: 16, State: 1. The mirroring connection to 'TCP://1.2.3.5:1234' has timed out for database 'MyDB' after 10 seconds without a response. Check the service and network connections.
Syntax error converting the varchar value to a column of data type int. Error Message: Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the.
Box 36, Springville, Utah - 84663, USA +1 888 900 4529.
Error: 1474, Severity: 16, State: 1. Select all Open in new window. On server B, I am getting the following errors: The mirroring connection to 'TCP://serverA:5022' has timed out for database 'DB' after 10 seconds without a response. Check the service and network connections. Error: 1479, Severity: 16, State: 1. Select all Open in new window.

Satin doll score pdf free. Troubleshooting the following Error:

What I found that many times experiencing this error in SQL Server’s Errorlog, that “Error: 17189” was only a symptom, and that many times the root cause was the line above in SQL Server’s Errorlog.

Case 1:

In the first case shown below, in SQL Server’s Errorlog listed right above the “Error: 17189”, listed were I/O stalls occurring. In this case, the Storage Administrator found that an I/O switch to the SAN was overloaded, which contributed to SQL Server freezing up. Which no logins would occur on the database instance for the next minute.

Case 2:

In this case, the memory was set to high (less of 40% of the memory was used), and the “lock pages in memory” option was not set. The following error kept appearing in SQL Server’s Errorlog :

By correcting the “max server memory” setting, and setting in the group policy editor to “lock pages in memory”, the “Error: 17189” disappeared.

Microsoft SQL mirror setup – error 1474

This blog will show you issues that may occur when working with a Microsoft SQL mirror. This blog will also show you how to solve it and why you need to make sure everything is setup exactly the same way. A Microsoft SQL mirror setup is not that hard, if you follow the basic steps you might even find it a very easy. This might change if you run into a freaky error that it may give, the error most of the time is useless and leaves you in the dark. Read on for the solution for error 1474, severity 16, state 1.

Situation

For those who read my blog about mirroring know that I build a mirror a while ago. As good practice and because the customer had to rearrange the whole datacenter and hosts I broke the mirror and was going to set it up again.

Was there a real reason to do that? perhaps not, but I tend to do that to see if the steps are correct and if the initial setup was not a accident that I can perform only once.

Environment

Let’s do a little recap and tell you about the environment.

I got three Microsoft SQL servers, two standard ones and one Express version (the Witness).

The database is used for a Citrix XenApp 7.5 environment so it has to be highly available for Citrix dumped the LHC.

The error after creating the mirror

Creating the mirror seems to go fine for a while and at the end (Read the blogpost) you can start the mirror. When I start the mirror I see that the status of the database is changed to Synchronized but the whole process seems to take longer than expected.

SQL servers mirror setup pops up a message saying;

“The ALTER DATABASE command could not be sent to the remote server instance TCP://server FQDN:5022“. hmm what;s that all about.

The mirror seems to be running fine only thing is that the witness server field is empty.

So I break the mirror again and start over, must have missed something somewhere… more coffee and let’s do it again. practice make perfect is what they say.

Again wizard, enter service accounts, all green and let’s start the mirror.

Waiting, status changes of the database, more waiting and boom error.

“Database mirroring connection error 4 The connection was closed by the remote end, or and error occurred while receiving data: ’64 ( the specified network name is no longer available.) for TCP;//server FQDN:5022“

Whaat? it worked flawlessly before, the only thing changed since last time was that the host had moved to another data center.

So first step now is to ask my good and dear friend Google to have a look in it’s vast database, surely many have encountered this and a solution is around the corner… after a 15 minute search I realize I’m alone, read many workaround and many command that I should fire off on the servers but none give me enough faith and so I do what I mostly do, dig in and try to fix it myself.

Solution

I sat back and thought about mirroring, what are the basic steps that you need to take to build a mirror?

– You need to run the SQL service with the same service account on each server (of course you can change that but that is recommended I read somewhere).

So I looked at all the three servers and checked the SQL service account used there. Remember it worked before and nothing had changed but a check is the first step.

I noted one difference, on the principal and mirror server the service account was Domain.lanservice_account and on the Witness server it was service_account@domain.lan.

Of course this should not be the issue for it’s the same user account managing that service but hey let’s change it so that it represents the other server.

All three servers now have the SQL service run with the account written Domain.lanserver_account.