SQL Server 2008 Database Mirror - DB 镜像 - Some Key Learnings

It's always easier to say than to do.

Key learnings:

1. Never expect a simple piece of document help you out. There will be lots of unknown traps waiting for you. You need to figure out by yourself.

2. Log! Log! Log! A simple error code tells you nothing. Try to find the log, you might find more details to help you solve the problem.

==================================

DB mirror without witness server. Principle => Mirror

NOTE: This might not be a standard way of setup(No encryption, not secure etc.), but at least it is functioning.

== Preconditions:

1. Two servers with SQL server 2008 running. One for Principle, one for Mirror.

2. Servers already joined domain. There is one Domain account to control them, and "sa" account is also set locally.

== Setup:

1. Backup DB(Full mode) to file from Principle server

2. Restore DB and transaction Log separately to Mirror server with option: RESTORE WITH NORECOVERY. Now the DB should be in a state which you cannot use it.

3. Configure DB mirror.

　　3.1 Uncheck "Encrypt data ......" option. (I didn't make the mirror happen with encrypt option checked.)

==Errors:

There will always be errors

1. Right click DB on Principle server, Aspect=>RemoteDacEnabled set to True

2. Windows firewall. Make sure port 5022 is open(Default port is 5022)

3. Error Code 1418: This annoyed me for half a week.

　　Check the SQL server log on both current server & target mirror server. See error like:

　　　　Logon Database Mirroring login attempt by user 'XXX\YYY$.' failed with error: 'Connection handshake failed. The login 'XXX\YYY$' does not have CONNECT permission on the endpoint. State 84.'. 

　　Obviousely, the DB network is working fine, but the user account has no authority to login, so don't waste time checking the firewall, hostname, port etc. Check the user permission instead!

　　I need to double check what I did for my instance.

　　Looks like I used domain account, Administrator, sa account, all failed. But when i added a user called "XXX\YYY$" and this issue disappeared. This definitely not the correct answer but I just don't have time to do more research.

==How to make the mirror working

1. Physically disconnect Principle, the mirror needs manual setup to become a new principle.

 

转载于:https://www.cnblogs.com/anbosun/p/6725256.html