June 13, 2012 at 2:36 am
Hi,
On my stand-alone machine Win7 64bit, i have installed two instances (Default, instance2) of SQL2008R2. Both services were running as network service and also tested and now running as local system account but of no use.
I have performed the following steps:
created database and table for testing, on default instance.
took full backup of database this database.
inserted few more rows.
took transaction log backup.
restored full backup followed by transactional log backup with NORECOVERY on instance2.
Started mirroring wizard on default instance to configure security.
principal connected as default instance "DbServer" with default port 5022
mirror connected as DBServer/instance2 on default port 5023
I can telnet both as "DbServer 5022" and "DbServer 5023" without any problem.
On mirroring dialogue both under "Server network address" it is showing as
Princial: "DbServer:5022"
Mirror: "DbServer:5023" ....... where as it is a named instance ("DbServer\Instance2"). is this correct? This cannot be changed as its greyed out.
The state_desc of sys.database_mirroring_endpoints is started on both instances. The results of sys.endpoints and sys.tcp_endpoints also seems OK.
Firewall is off.
For both instances, shared memory and TCP are enabled.
Started mirroring without FQDN, because its a standalone local machine.
However, as usual and as it is quite common, it is giving error 1418 as "DbServer:5023" for instance2.
Please tell what is missing.
Do i need to create any alias OR what else?
Thanks.
June 13, 2012 at 4:14 pm
Hi,
Finally i found a solution myself.
All above steps are correct plus following using SSCM
On the same machine, i have given different IP to both instance and different post
DefaultInst = 192.168.0.22 port 1443
Instance2 = 192.168.0.23 port 1444
then i have also restarted the "SQL Browser" service as "local system". Please also make sure in advanced properties of this service that it is Active = Yes as well.
Then i have restarted all the services again.
Both Principal and Mirror will show same name, as i have asked above .... so no change need there.
Simply now press "Start Mirroring" and it worked for me right away.
In general, these issues are not problem of real and well configured environment e.g. Production where you have domain and separate server for such things. However, local DBA testing of few thing before real implementation brought such problems in attention.
Anyway i studied lots of material today and learned so much as well.
Now i can sleep well, as i have solved this issue today myself.
Thanks everyone for reviewing this thread.
June 13, 2012 at 4:21 pm
Just be aware that if your SQL Server instances are running under the local server account on SEPARATE servers that you would need to do one of two things:
1) change the account that SQL Server runs under to a domain account, grant connect to the endpoints for the domain account
or
2) use certificates to establish the mirror session between servers.
June 15, 2012 at 9:57 am
Changing the IP address didn't fix the issue. Something else did. Probably something that was fixed by rebooting (like maybe a "poisoned" service broker queue for one of the partners). Everything that could cause this error can be found here: http://www.sqlsoldier.com/wp/sqlserver/troubleshooting-atabasemirroringerror1418
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply