Solved

SQL Mirroring

Posted on 2009-07-14
6
387 Views
Last Modified: 2012-05-07
While attempting to mirror two sql servers without a witness server, the following error kept coming through right at the end:

The server network address "TCP://MOSSSQL-B.mka.compute-1.internal: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. (Microsoft SQL Server, Error:1418)

I checked that both endpoints were created and on/started.  I can ping both servers from the other server.  Both servers are running SQL Standard 2005 ver 9.0.3077.  All servers are in Amazon's cloud and apart of the "local" domain: MKA (mka.compute-1.internal).  

In trying to fix this issue, a forum, http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22896060.html?sfQueryTermInfo=1+1418+2005+cannot+doe+error+exist+mirror+reach+sql, suggested to run the SQL processes under a domain admin account rather than the local admin account.  The first server switched over fine, but the second server now gives this error:

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log.  If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code -2146885628.

1. Does the SQL server need to be run by a Domain admin for cloning to be successful?
2. What else should be done to get these two machines cloning successfully?
0
Comment
Question by:ncalcaterra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 24860267
You created a plain domain user account like MyDomain\MyDBAdmin?

Then on both servers added the MyDomain\MyDBAdmin to the local servers Administrators Group (or gave them appropriate permissions to the SQL Server files and folders)?

Then in the Services console (Start --> Run --> services.msc) changed the Log On As to all the SQL Services (especially the SQL Server and SQL Server Agent services) MyDomain\MyDBAdmin?

Then did a restart on the services?
0
 

Author Comment

by:ncalcaterra
ID: 24861743
We created a domain user and gave that user admin rights on the SQL server through AD.  When trying to change the SQL services to run under this user, the second server throws up the second error (Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log.  If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code -2146885628.) when trying to restart the SQL service.

The only way the SQL service can be restarted is to change the account user back to the local admin (non-domain) user.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24862119
Server2 is not a member of the domain? If not then there is no really good way to do this.

>> user admin rights on the SQL server through AD...

Go to the Computer Management console (Start --> Run --> compmgmt.msc) on the SQL Server. Drill down Computer Management --> Local Users and Groups --> Groups and select the Administrators Group. Add or verify the MyDomain\MyDBAdmin user id is in there.

Then drill down to the Services and Applications --> Services and scroll down to SQL Server (MSSQLSERVER). Change the Log On and password as shown, then restart the services. Repeat for the other services.
SQL-Server-Service-Logon.jpg
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:ncalcaterra
ID: 24862908
Both SQL servers are on the same domain (mka.compute-1.internal).  The MKA domainu user, mossadmin, was given Administrative rights in AD to both of the servers.  Therefore, it should not be necessary to add the user to the local administrators group on each of the machines.
I can logon to both of the sql machines with the admin user, mossadmin, and gain full rights to all folders, etc.  When I try to change the user the SQL service runs under (just as your pic shows), it allows me to hit OK and gives me a prompt to reset the service.
But when I click "Re-start", it then gives the above error.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24863255
Try going into the SQL Management Studio, and adding the user mossadmin user from the domain to securtiy and make him a SysAdmin user.

Then reset the services.
0
 

Author Closing Comment

by:ncalcaterra
ID: 31603467
The service needed to be run under a domain account with local admin rights.  The experienced problem was with the instance (server), not the methodology.
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question