Troubleshooting Database Mirroring Error 1418 Updated

Troubleshooting Database Mirroring Error 1418 Updated Last night I was helping someone with a database mirroring setup problem via email. It has been a long time since I came across a new cause for the infamous 1418 error for database mirroring. This time the error was accompanied by an error in the SQL Log on the mirror partner that said teh following: Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not
read more

Auditing Users in a Failover Partner Database

Auditing Users in a Failover Partner Database I read a question on a discussion list today asking if it is possible to audit SELECT queries on a log shipping secondary in stand-by mode. His attempts to set it up had obviously failed because the database is read-only and you cannot create a database audit in the database (nor anything else). Wanting to modify the secondary database is actually a pretty common question with a simple solution though I had never
read more

T-SQL Tuesday #21 – Bringing Ugly Back

T-SQL Tuesday #21 – Bringing Ugly Back This blog entry is participating in T-SQL Tuesday #21, hosted this month by Adam Machanic (Blog|@AdamMachanic). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Your Crap Code. The truth is, we all write crap code from time to time. Over time, we get better (hopefully).
read more

Misleading Error 1475 While Setting Up Database Mirroring

Misleading Error 1475 While Setting Up Database Mirroring I was doing some practice work yesterday testing out some mirroring interoperability scenarios when I encountered an error I had not seen before. The error message seemed to indicate that the database I was trying to mirror had some bulk-logged changes that had not been backed up yet. I can easily reproduce this error resulting in the same error message. I’m blogging about it here because the solution isn’t obvious. Msg 1475,
read more

3 Log Shipping Techniques

3 Log Shipping Techniques My new article for SQL Server Magazine titled 3 Log Shipping Techniques has been posted online and will appear in an upcoming print issue. This article is a look at some very interesting techniques that use log shipping in interesting ways. Mastering these tehcniques can save you a great deal of time and frustration. You will learn how log shipping can be a very useful tool. I wrap it up with a tale of how I
read more

SQLU HA/DR Week – Database Mirroring Performance Counters

SQLU HA/DR Week – Database Mirroring Performance Counters Database mirroring debuted in SQL Server 2005 and had eleven performance counters for monitoring mirroring. There were ten new performance counters released with SQL Server 2008. We now have twenty-one performance counters to use for monitoring database mirroring. A list with a brief description of each can be found in Books Online or online at http://msdn.microsoft.com/en-us/library/ms189931.aspx. Many people find themselves without a clear understanding of what the counters mean and how to
read more

When Does Trustworthy Get Reset on a Mirror?

When Does Trustworthy Get Reset on a Mirror? When I’ve done sessions on automating database mirroring processes and in my book, I talk about incorporating steps to set properties that get reset during the restore process in the mirroring setup such as database owner and the Trustworthy property. When you perform the initial restores of the database to set up mirroring, the database owner is set to the account of the person performing the restore, and if Trustworthy is enabled,
read more

T-SQL Tuesday #15: Database Mirroring Automation

T-SQL Tuesday #15: Database Mirroring Automation This blog entry is participating in T-SQL Tuesday #15, hosted this month by Pat Wright (Blog|@SqlAsylum). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Automation. Automation is one of my favorite topics. For my part in this month’s T-SQL Tuesday, I am going to combine it
read more

Does a Mirroring Failover Clear the Procedure Cache?

Does a Mirroring Failover Clear the Procedure Cache? I saw a question on the MSDN forums asking what happens to the procedure cache (also known as the plan cache) on the principal and mirror when a failover occurs. My reply was starting to get long, so I decided to move it out to a blog post. The original question poses the theory that the procedure cache is flushed on both mirror and principal when a failover occurs. How Mirroring Transfers
read more

Troubleshooting Database Mirroring Error 1418

Troubleshooting Database Mirroring Error 1418 Error 1418 is a common, hard-to-troubleshoot error. It is so common that it has been given its own page dedicated to the error in Books Online: MSSQLSERVER_1418. Unfortunately, that page only lists a few troubleshooting steps. Over the years, I’ve compiled a longer list of troubleshooting steps for this error. I’ve encountered every one of these issues at some point. The server network address “%.*ls” can not be reached or does not exist. Check the
read more