I have been testing out Database Mirroring in SQL Server 2008. There are numerous enhancements in 2008, mainly under the hood in the name of performance and reliability but still worth testing out. I had done this before using Windows Server 2003 SP2 but this was the first time I had tested using Windows Server 2008. It was an educational experience, I can tell you…
Database Mirroring allows automatic failover without having to invest in cluster architecture and works on a database level rather than a server level. You have a Principal Database Server with a Mirror Database Server that work together in a partnership and, optionally, a Witness Server. The Witness decides when an automatic failover should occur for the specified database. Without the Witness, you lose the automatic failover, but you can still do a manual failover.
First of all, I tested Database Mirroring using the new AdventureWorks2008 database that supports Filestream datatypes. So that’s the first question answered: yes, Filestream datatypes are supported during mirroring. Good. That’s fine for W2K3 SP2. However, when I came to test using Windows Server 2008, I ran into trouble. I followed the identical steps (honestly!). I backed up the “Principal” database (both Full and Transaction Log backups), restored to the “Mirror” database server WITH NORECOVERY and then started the Database Mirroring Security Configuration Wizard under Database Properties/Mirroring page. The wizard creates an “endpoint” on each server for communication over TCP and uses those endpoints to establish the Mirroring partnership. When I came to the final step, clicking the “Start Mirroring” button produced an error 1418 “An error occurred while starting mirroring”. The error goes on: “The server network address “TCP://HOU-SQL-02.adventureworks.com:5022” cannot be reached or does not exist”. TCP Port 5022 is the first choice for the wizard to use for database mirroring but can be changed. Changing it, however, did not change the error.
I then embarked on a series of network checks to make sure my servers could communicate with each other. DNS is always high on the list of usual suspects but in this case all was clear using FQDNs. My servers were set up under a domain so I checked out security policies on the both the servers and the domain controller. What I discovered was the “Windows Firewall with Advanced Security” feature in W2K8. Using this feature you can enable the Windows Firewall, either at the Domain level or at the Server level or both. You can also setup individual rules that block or allow traffic through the firewall on a fixed port basis or on an application basis allowing dynamic port control. I decided this must be the culprit as this was a new feature in W2K8 and the Windows Firewall was set on at the Domain Profile. However, after shutting off the Windows Firewall at the Domain and Server levels, the same error occurred.
Then I searched the net and various MSDN articles on setting up Database Mirroring. I found an interesting article on a web site from Sweden “SQL in Stockholm” – Database Mirroring Error 1418 Troubleshooter that lists a step by step approach to finding the solution to fix this error. The trouble is, many things can cause the same error so you have a long list to get through identify the problem by process of elimination.
The key point of the document that caught my eye was this:
”Have you configured your endpoints correctly?”
The article goes on: “To be specific, this means: a) Are they started? b) Are they using the same form of authentication? c) Are they using the same form of encryption? d) Does the service account have permission to use them?”
Well, I used the SSMS feature to generate the CREATE ENDPOINT statements from the existing objects created by the wizard. That showed that a, b and c were true. But “d” got me thinking – does the service account have permission to use them? Checking out the service accounts on each server, I discovered they were set up at install time with the recommended approach of using a common domain user account, in my case adventureworkssqlserver. The SQL Server setup gives the minimum permissions to the service account in order to run SQL Server services. Nothing more, nothing less. So reading Books Online I discovered that I had to grant connect permissions to the service account for each endpoint. But then I realized there is a window in the wizard that asks for service accounts for each of the servers in the mirroring partnership. Previously, we would leave those fields blank to pick up the default service accounts (assuming they had local admin and sysadmin rights) but upon further inspection that window will grant connect permissions to the accounts specified and also create a login for those accounts, if necessary. This did the trick since my service account was NOT local admin. (Giving local admin rights to the service account is like cracking a nut with a sledgehammer – it works but is much more than necessary). After that, mirroring worked just fine including synchronization, automatic failover and manual failover.
At that point I was very happy. Thanks to SQL in Stockholm!
Cheers
Brian
Recent Posts:
Microsoft Hands-On Lab Room – a view to the future?
Books Online – still the best…
