How do I automatically respond to mirrored database failovers?

Automatic Failover – an incomplete solution Automatic failover with Database Mirroring is great, but what about all of the other things that you need to happen when the database fails over? There is no built-in support in Database Mirroring for anything external to the database. This is where automatic activation in the Service Broker comes to the rescue. Service Broker and Automatic Activation The Service Broker can be used to automatically activate a stored procedure when certain events occur. The
read more

CDC Interoperability with Mirroring and Recovery

CDC Interoperability with Mirroring and Recovery The following email came in to a discussion group last week asking several questions about CDC including the interoperability of CDC and database mirroring. The email: Dear experts Here are some questions regarding CDC in SQL Server 2008: What will happen to CDC if we truncate or backup database log files? Will the operation wait for CDC log parser to complete all of the transactions? If disaster occurs, what will happen to CDC and
read more

T-SQL Tuesday #011 – Top 5 Myths of Database Mirroring

T-SQL Tuesday #011- Top 5 Myths of Database Mirroring This blog entry is participating in T-SQL Tuesday #011, hosted this month by Sankar “@SankarReddy13” Reddy(Blog|Twitter). 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: Misconceptions in SQL Server. For my part in this month’s T-SQL Tuesday, I will be discussing my top 5
read more

Transferring Logins to a Database Mirror

I recently discovered that my book (Pro SQL Server 2008 Mirroring) has an older version of the script to copy logins to the mirror partner for database mirroring. The final version of the script handles SQL logins as well as Windows groups and users. I’m really disappointed that the newer script didn’t make it into the book because I put a lot of work into it to make sure it handled SQL logins. There are two main issues with transferring
read more

The Toilet Analogy … or Why I Never Recommend Increasing Worker Threads

Lately I’ve noticed an increasing number of people recommend increasing worker threads. The reasons for the recommendations include things as simple as a troubleshooting step for reports of general system slowness or as complex as a means to mirror very large number of databases on a single server. In one instance, someone successfully convinced another person on a discussion group that they could recommend to their client that it is okay to mirror 400 to 600 databases on a single
read more

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future. I have not tried this, need mirroring setup and was hoping to get a
read more

SQLSaturday 26 Session Files

SQLSaturday 26 Session Files: 10/3/2009 in Redmond, WA Thanks to everyone that attended my sessions at SQLSaturday 26 in Redmond, WA on 10/3!! This was my first SQLSaturday event. I was granted the opportunity to be a last minute replacement speaker and gave two presentations. This was my first time speaking in front of a large audience at an event. It was a thoroughly enjoyable experience, and I hope to speak at future events as well. As promised in my
read more

Database Mirroring FAQ: If the principal fails while running in high performance mode, what options do I have for bringing the mirror online?

Question: If the principal fails while running in high performance mode, what options do I have for bringing the mirror online? This question was sent to me via email. My reply follows. Quick question on DB mirroring client redirection after Principal fails … Consider a simple scenario with Asynchronous mirroring: We are implementing asynchronous [high performance] mirroring without witness. The client application connects to the mirroring session using below connection string:Source={PrincipalServerName};Initial Catalog={DBName};Integrated Security=SSPI;Failover Partner= {MirrorServerName} Now due to some disaster,
read more

My Kingdom for a Port

One of the steps in setting up database mirroring is to assign a port to the endpoint. It’s common practice to simply give it port 5022, but what if that port is already being used? The endpoint creation doesn’t verify the availability of a port and will be created successfully. You won’t know that the port is already being used until you get a communication error when trying to start the mirroring session. Then you have to go through troubleshooting
read more