Database Mirroring Tips

Database Mirroring is becoming a popular feature of SQL Server 2005. By following a few simple steps either by using the SQL Server Management Studio or by running a few T-SQL commands you can easily establish database mirroring for one or more of your databases. One of the configuration options for Database Mirroring is the High Availability mode. With this option three servers are put in place the principal, mirror and witness servers. This is the only option that allows for automatic failover. One of the things that I have noticed is that when there are periodic network issues a failover occurs even though there are no issues with the Principal server. Are there any options to delay the failover?

In a recent tip (Digging into Database Mirroring in SQL Server 2005) you outlined the architecture for SQL Server 2005's Database Mirroring. This technology seems like a great advancement over log shipping but how do I incorporate the connectivity into my front end applications? I assume that ADO.NET has a part to play in ensuring the application will connect to the correct SQL Server instance. What are some of the items that I should be aware of when using SQL Server 2005 Database Mirroring? Can you also provide me with a example of the ADO.NET connection string and explain the components?

In a previous tip on Database Mirroring Automating Failover for Multiple SQL Server Databases, you've seen how you can failover all of the databases in a mirroring session if one database fails over. However, what happens if you don't have a witness server and your principal server goes, how do you make your mirror server the principal and bring all your databases online?

I have a need to setup SQL Server Database Mirroring in my environment. I understand it can be complicated to setup. Can you provide an example on setting up SQL Server Database Mirroring? Check out this tip for a basic look at how to setup this SQL Server feature.

Database Mirroring was released with SQL Server 2005 and is becoming a popular choice for a failover solution. One of the big issues with Database Mirroring is that the failover is tied to one database, so if there is a database failure the mirrored server takes over for this one database, but all other databases remain functional on the primary server. The drawback is that more and more applications are being built where multiple databases make up the application, so if only one of the databases fails over the other database will still be functional on the principal server, but the application may not work. How can I be notified when this happens and make all of the databases failover?

Your company is considering a disaster recovery solution for SQL Server 2005 and 2008 instances. You have been asked to analyze the various options and report in detail which method is the best for your company. Over the course of the analysis you are also asked to find out if the DR solution can also be used for reporting purposes. Let's see how SQL Server Database Mirroring resolves these needs in a simple question and answer format.

I want to implement database mirroring on my SQL Server 2005 databases, but both the principal and mirror servers are not members of the domain or are members of different Active Directory domains that do not have trust relationships. How do I go about it?

I have configured Database Mirroring successfully between 2 SQL Server 2005 instances. My application is connecting to SQL Server using a SQL Server login, and is built using ADO and the SQL Native Client. My connection strings/connection settings specify the correct information, including the appropriate failover partner. I have also created all the same logins on the mirrored server as on the principal server. Upon testing a database failure, the mirror successfully assumes the principal role and everything looks correct on the SQL Server. I can even connect successfully to the mirror using my Windows login. However, the application reconnect fails with the following error: Cannot open database "" requested by the login. The l

The number of Database Mirror pairs in our enterprise is increasing at an alarming rate. With the ease of setup, resilience and ability to seamlessly failover and failback, database mirroring has taken over from log shipping as the DR solution of choice. We need a basic script that we can execute on all servers participating in mirroring that will alert us if any Principals or Mirrors are in an abnormal state. The script needs to work on both Principal and Mirror server.

In an earlier tip (Login failures connecting to new principal after failover using Database Mirroring) you mentioned using Database Mirroring as one of the native SQL Server high availability\disaster recovery\fault tolerant solutions. I am interested in learning more about this technology to see if it is something that I can use in my environment. Getting background information on the technology would be a good place for me to start. In addition, understanding how and when it can be used would be helpful. Where should I begin?

Many people deploy performance monitoring solutions in a "one-size-fits-all" manner. That is, they tend to build a solution that can be easily deployed to multiple servers and capture basic information from each server. The trouble is that not every server is identical, not even within the same shop. For example, not every server may have database mirroring deployed, which means your performance monitoring solution may be missing some critical pieces of information with regards to monitoring database mirroring.

I have a need to setup SQL Server Database Mirroring in my environment. I understand it can be complicated to setup. Can you provide an example on setting up SQL Server Database Mirroring? Check out this tip for a basic look at how to setup this SQL Server feature.

Database Mirroring was released with SQL Server 2005 and is becoming a popular choice for a failover solution. One of the big issues with Database Mirroring is that the failover is tied to one database, so if there is a database failure the mirrored server takes over for this one database, but all other databases remain functional on the primary server. The drawback is that more and more applications are being built where multiple databases make up the application, so if only one of the databases fails over the other database will still be functional on the principal server, but the application may not work. How can I be notified when this happens and make all of the databases failover?

I want to implement database mirroring on my SQL Server 2005 databases, but both the principal and mirror servers are not members of the domain or are members of different Active Directory domains that do not have trust relationships. How do I go about it?

Many people deploy performance monitoring solutions in a "one-size-fits-all" manner. That is, they tend to build a solution that can be easily deployed to multiple servers and capture basic information from each server. The trouble is that not every server is identical, not even within the same shop. For example, not every server may have database mirroring deployed, which means your performance monitoring solution may be missing some critical pieces of information with regards to monitoring database mirroring.

I have configured Database Mirroring successfully between 2 SQL Server 2005 instances. My application is connecting to SQL Server using a SQL Server login, and is built using ADO and the SQL Native Client. My connection strings/connection settings specify the correct information, including the appropriate failover partner. I have also created all the same logins on the mirrored server as on the principal server. Upon testing a database failure, the mirror successfully assumes the principal role and everything looks correct on the SQL Server. I can even connect successfully to the mirror using my Windows login. However, the application reconnect fails with the following error: Cannot open database "" requested by the login. The l

In an earlier tip (Login failures connecting to new principal after failover using Database Mirroring) you mentioned using Database Mirroring as one of the native SQL Server high availability\disaster recovery\fault tolerant solutions. I am interested in learning more about this technology to see if it is something that I can use in my environment. Getting background information on the technology would be a good place for me to start. In addition, understanding how and when it can be used would be helpful. Where should I begin?

In a previous tip on Database Mirroring Automating Failover for Multiple SQL Server Databases, you've seen how you can failover all of the databases in a mirroring session if one database fails over. However, what happens if you don't have a witness server and your principal server goes, how do you make your mirror server the principal and bring all your databases online?

The number of Database Mirror pairs in our enterprise is increasing at an alarming rate. With the ease of setup, resilience and ability to seamlessly failover and failback, database mirroring has taken over from log shipping as the DR solution of choice. We need a basic script that we can execute on all servers participating in mirroring that will alert us if any Principals or Mirrors are in an abnormal state. The script needs to work on both Principal and Mirror server.

In a recent tip (Digging into Database Mirroring in SQL Server 2005) you outlined the architecture for SQL Server 2005's Database Mirroring. This technology seems like a great advancement over log shipping but how do I incorporate the connectivity into my front end applications? I assume that ADO.NET has a part to play in ensuring the application will connect to the correct SQL Server instance. What are some of the items that I should be aware of when using SQL Server 2005 Database Mirroring? Can you also provide me with a example of the ADO.NET connection string and explain the components?

Database Mirroring is becoming a popular feature of SQL Server 2005. By following a few simple steps either by using the SQL Server Management Studio or by running a few T-SQL commands you can easily establish database mirroring for one or more of your databases. One of the configuration options for Database Mirroring is the High Availability mode. With this option three servers are put in place the principal, mirror and witness servers. This is the only option that allows for automatic failover. One of the things that I have noticed is that when there are periodic network issues a failover occurs even though there are no issues with the Principal server. Are there any options to delay the failover?

I have a need to setup SQL Server Database Mirroring in my environment. I understand it can be complicated to setup. Can you provide an example on setting up SQL Server Database Mirroring? Check out this tip for a basic look at how to setup this SQL Server feature.

Many people deploy performance monitoring solutions in a "one-size-fits-all" manner. That is, they tend to build a solution that can be easily deployed to multiple servers and capture basic information from each server. The trouble is that not every server is identical, not even within the same shop. For example, not every server may have database mirroring deployed, which means your performance monitoring solution may be missing some critical pieces of information with regards to monitoring database mirroring.

The number of Database Mirror pairs in our enterprise is increasing at an alarming rate. With the ease of setup, resilience and ability to seamlessly failover and failback, database mirroring has taken over from log shipping as the DR solution of choice. We need a basic script that we can execute on all servers participating in mirroring that will alert us if any Principals or Mirrors are in an abnormal state. The script needs to work on both Principal and Mirror server.

I want to implement database mirroring on my SQL Server 2005 databases, but both the principal and mirror servers are not members of the domain or are members of different Active Directory domains that do not have trust relationships. How do I go about it?

In a previous tip on Database Mirroring Automating Failover for Multiple SQL Server Databases, you've seen how you can failover all of the databases in a mirroring session if one database fails over. However, what happens if you don't have a witness server and your principal server goes, how do you make your mirror server the principal and bring all your databases online?

Your company is considering a disaster recovery solution for SQL Server 2005 and 2008 instances. You have been asked to analyze the various options and report in detail which method is the best for your company. Over the course of the analysis you are also asked to find out if the DR solution can also be used for reporting purposes. Let's see how SQL Server Database Mirroring resolves these needs in a simple question and answer format.

Database Mirroring is becoming a popular feature of SQL Server 2005. By following a few simple steps either by using the SQL Server Management Studio or by running a few T-SQL commands you can easily establish database mirroring for one or more of your databases. One of the configuration options for Database Mirroring is the High Availability mode. With this option three servers are put in place the principal, mirror and witness servers. This is the only option that allows for automatic failover. One of the things that I have noticed is that when there are periodic network issues a failover occurs even though there are no issues with the Principal server. Are there any options to delay the failover?

Database Mirroring was released with SQL Server 2005 and is becoming a popular choice for a failover solution. One of the big issues with Database Mirroring is that the failover is tied to one database, so if there is a database failure the mirrored server takes over for this one database, but all other databases remain functional on the primary server. The drawback is that more and more applications are being built where multiple databases make up the application, so if only one of the databases fails over the other database will still be functional on the principal server, but the application may not work. How can I be notified when this happens and make all of the databases failover?

In a recent tip (Digging into Database Mirroring in SQL Server 2005) you outlined the architecture for SQL Server 2005's Database Mirroring. This technology seems like a great advancement over log shipping but how do I incorporate the connectivity into my front end applications? I assume that ADO.NET has a part to play in ensuring the application will connect to the correct SQL Server instance. What are some of the items that I should be aware of when using SQL Server 2005 Database Mirroring? Can you also provide me with a example of the ADO.NET connection string and explain the components?

In an earlier tip (Login failures connecting to new principal after failover using Database Mirroring) you mentioned using Database Mirroring as one of the native SQL Server high availability\disaster recovery\fault tolerant solutions. I am interested in learning more about this technology to see if it is something that I can use in my environment. Getting background information on the technology would be a good place for me to start. In addition, understanding how and when it can be used would be helpful. Where should I begin?