Advertisements

Archive

Archive for the ‘Database Mirroring’ Category

Troubleshooting Database Mirroring in MS SQL Server

May 13, 2016 5 comments

Database Mirroring ensures availability of SQL Server database in scenarios of data disasters. It is implemented in SQL databases that use Full Recovery model. It maintains two copies of a particular database, which reside in different locations. When the primary server is unavailable due to any reason, the secondary server comes into action and proceeds with the functioning.

In this blog, we will illustrate some of the measures to troubleshoot database mirroring in SQL Server. Below is the list of some factors that need to be considered whilst troubleshooting:

Accounts

  • It is to be noted that the accounts, which are facilitating the SQL server to run, should be correctly configured. In case the accounts run on the same domain accounts, there are less chances of misconfiguration.
  • However, in case the accounts are running on different domains, the login of one of the accounts should be created on the other computer in master. In addition to this, the login should be granted CONNECT permission in the endpoint.
  • In case SQL Server is running as SaaS and is using the local system account, certificates must be used for authentication purposes.

Endpoints

Following key points should be considered to make sure that the endpoints are configured correctly:

  • Make sure that all the server instances- principal, mirror and witness server should possess database mirroring endpoint. Database Mirroring Endpoint can be created either by using Windows Authentication or by using certificates.
  • Make sure that all the port numbers are correct. In order to identify the port number associated with database mirroring endpoint use the following catalog views:
    • sys.database_mirroring_endpoints
    • sys.tcp_endpoints
  • Check whether the status of the endpoints is STARTED. For each server instance, make use of the following Transact-SQL command:
SELECT state_desc
  FROM sys.database_mirroring_endpoints
  • For starting an endpoint use the following command:
ALTER ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = <port_number>)
FOR DATABASE_MIRRORING (ROLE = ALL);
  • In order to check whether the ROLE is correct or not, use the following command:
SELECT role
  FROM sys.database_mirroring_endpoints;

System Address

For naming the server instance in the database mirroring, any name, which identifies the system can be used. If the systems reside in the same domain, the system name can be used as the server name. In addition to this, a static IP address or a fully qualified domain name can also be used as the server names.

Network Access

If the server instances are in different domains, it is necessary for every server instance to access the ports of all the server instances or the instances over the TCP. This puts a stop to the communication between the instances of the server.

Mirror Database Preparation

The most important thing that is to be made sure is that the database is prepared for mirroring, no matter if the mirroring is done again or for the first time. It is to be noted that whilst creating a mirror database on the mirror server, restoration of principal database backup with the same name of the database is to be done. Moreover, all the log backups created prior to backup creation should also be applied. Both the things should be done WITH NORECOVERY.

It is recommended to store the mirror database at the same location as of the principal database. However, if the file paths are different, MOVE option should be included with RESTORE statement.

In case the mirroring was stopped earlier and is being restarted, all the backups taken afterwards should be applied to the mirror database.

Fixing Failed Create-File Process

In order to add a file without having any impact on the mirroring session, the path of the file should exist on both the servers. Due this very fact, if the database files are moved while mirror database creation, the add-file operation might fail and the mirroring can be suspended.

In order to fix the issue, follow the below-mentioned steps:

  1. The complete mirroring session should be removed and the full backup of the file group containing the added file must be restored.
  2. The log containing the add-file operation should be then backed up. Moreover, the log backup should be restored manually by using WITH NORECOVERY and WITH MOVE. This will create a file path on the mirror server and the file will be restored to a new location.

Conclusion

Care should be taken while troubleshooting database mirroring in SQL server irrespective of the fact that the mirroring has been previously done or not. With the help of the above-mentioned measures, the users can easily fix the issues while creating database mirroring.

Advertisements

Mirroring FAQ

March 14, 2009 Leave a comment

I plan to update this as I run across questions; I really like mirroring. Comparing to Clustering it is cheaper to setup also. I like clustering too together they can make a great solution for availability and reliability by building on each others strength. Anyhow this is just quick Q & A guide and reference links for Database Mirroring only.

Do the databases require to be on the same physical path on both the principle and mirror server?
No. Database mirroring is on database level with in SQL Server not on the file level.

Is it possible to set up mirroring on one computer?
Yes. For production system not recommend because you are defeating the purpose of mirroring; but for testing and development it is possible. You’ll have to make sure to give each end point different port on the same computer.

Is it possible to set up database mirroring on servers that are across domain?
Yes. Please reference link.

What changed for mirroring between SQL 2008 and SQL 2005?
SQL 2008 supports log compressor for Mirroring, Ref Link.
More to come later …

How do I force a manual failover?

ALTER DATABASE Test
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

But please be aware doing this will cause data loss; only do this if getting the services up is more important then losing some data. There are some conditions that much match and this can only be done on the mirror server.

  • The principal server is down.
  • WITNESS is set to OFF or witness is connected to the mirror server.

Is it possible to set up database mirroring and replication together?
Yes. Please reference, Link for 2008 and Link for 2005.

Is it possible to setup database mirroring and clustering?
Yes. Please reference Link for 2008 and Link for 2005.

I don’t have a domain can I still use mirroring?
Yes. You can use certificates to authenticate between servers; same technique must be used when setting up mirroring across domain. Please reference link to understand how to use certificates with mirroring.

The Transaction Log files keeping getting last on the mirrored server? I can’t back it up how do I deal with it? (Link)
You should be backing up the transaction log on your principal server; any changes taking place there will be replicated over to mirrored partner. If your transaction file grew on both principal and mirrored server because you forgot to set up Transaction log backups you can backup transaction log and use DBCC shrinkfile on Principal server it will be replicated on to Mirror server.

Is it possible to setup mirroring with instances running on SQL 2005 and SQL 2008?
No. Database mirroring between partners and witness servers must be of same version. In addition the mirror partners must also be of the same edition. (Ref Links 2005 and 2008).

Mirroring Known Issues?
KB926900, The SQL Trace entries may be incorrect when you trace an instance of SQL 2005 that is configure to use database mirroring, Link.
KB937531, The shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005, Link.
KB940254, Error message when you try to set a witness in a database mirroring session in SQL Server 2005: “The ALTER DATABASE command could not be sent to remote server instance ‘TCP://<servername>:<port>”, Link.
KB946036, FIX: Error message when you run a maintenance plan that contains a Back Up Database task in SQL Server 2005: “Failed:(-1073548784) Executing the query ‘BACKUP DATABASE”, Link.

This is not an exclusive list; just articles I ran into.

Database Mirroring References?
Paul S. Randal, MVP, Link.
Using Database Mirroring with Share Point, Link.
MSDN SQL 2005 Database Mirroring, Link.
MSDN SQL 2008 Database Mirroring, Link.
Cluster or Mirroring, Link.
Database Mirroring (2005) White Paper, Link.
Robert Davis, Pro SQL Server 2008 Mirroring; anther FAQ on Mirroring, Link.
Troubleshooting Database Mirroring Deployment (SQL 2005), Link.
Troubleshooting Database Mirroring Deployment (SQL 2008), Link.

Categories: Database Mirroring

Mirroring Scripts

March 13, 2009 Leave a comment

When I setup mirroring between servers on SQL Server 2005 I use following scripts:

These scripts rely are designed and rely on following settings:

  • All servers exist in the same domain.
  • The service account that is used will be domain account that has access to all three nodes.
  • Scripts are designed for High Availability Mode.
  • They require a central backup location to backup the database and log file to for restore.

If you need to know how to setup security using Certificates please look at Microsoft MSDN page, link.

If you need to know how to setup Mirroring accross domains, please look at MSSQL Tips article, link.

Database Mirroring: Fully Quanlified Domain Name (FQDN) Error

November 16, 2008 8 comments

If you are trying to setup Database Mirroring, it prefers to see a FQDN for the servers when entering partner information. If you don’t you’ll get following error message:

“One or more of server network addresses lack a fully qualified domain name (FQDN). Specify the FQDN for each server, and click Start Mirroing again …”.

This message will not go away if you are setting up mirroring on Standalone or Workgroup computer because you cannot qualify your computer name. When setting up mirroring using the GUI interface and when you reach the following dialogbox:

(I edited the image to take out server name and such; but in the princple network address, it will have TCP://ServerName:Port, as same for Mirror and Witness box, when not fully qualified.)

In this dialog box click ‘DO NOT START MIRRORING’. Once you do that you will return to the Database Mirroring options dialog in which following three fields need to be edited:

The three boxes in the red will have the TCP://ComputerName:Port there; replace those with the IP address instead. So if everything is running on one computer, change principal address to TCP://127.0.0.1:5200, mirror address to TCP://127.0.0.1:5201, and witness address to TCP://127.0.0.1:5202 and then click ‘Start Mirroring’.

WIP: Mirroring Issues

December 27, 2007 1 comment

We have a SQL Server 2005 EE 64x mirror enviornment set up; but even though the SQL Servers looks like they are performing their duties properly. The application doesn’t seem to want to a SQL Server in one senerio.

Configuration:
SQLServerA (Principle)
SQLServerB (Mirror)
SQLServerC (Witness)

Connection String:
Data Source=SQLServerA; Failover Partner=SQLServerB; Initial Catalog=MyDatabase; Integrated Security=True;

All three servers are up and working, with the connection string above system works properly as expected. We manually failover . . .

Configuration:
SQLServerA (Mirror)
SQLServerB (Principle)
SQLServerC (Witness)

Connection String:
Data Source=SQLServerA; Failover Partner=SQLServerB; Initial Catalog=MyDatabase; Integrated Security=True;

Both servers still up, the application hits SQLServerA, gets failed login. And after refreshing it goes to SQLServerB and works without any issue. But now if I take down the servers for SQL Server on SQLServerA. So only SQLServerB and SQLServerC are up the application stops working.

Talking to Mike at Microsoft, he gave me article that he found in their knowledge base
http://support.microsoft.com/Default.aspx?kbid=912151 and said the clients that ran this patch it worked for them.

So we did this, and now the server works even with SQLServerA being down; but we have to refresh twice. So we are in middle of writing a quick test page that catches the expection and auto reconnects to see if that will work.

%d bloggers like this: