As mentioned in previous disaster recovery posts and article, during my current mandate I have been tasked with what will ultimately be mirroring between redundant data centres. Mirroring has been chosen thanks to its easy setup and automatic failover option. The following is an overview of what should be taken care of to ensure a stable Mirroring setup.
Prerequisites for Mirroring are that you ensure that your database is optimised already, because mirroring an unoptimised database is just double the potential problems (in terms of file space). In this way, expected disk space growth should be analysed thoroughly. As for the build level, I am just waiting to apply SQL 2005 sp3, cu6 (cu9 now!) before running a mirrored set up in production. I figure having the same highest-available build level is the best way to start a mirroring infrastructure.
Initially there were rumours that there was a maximum value to the number of databases mirrored, but that turned out to only be applicable to 32-bit systems (which is a platform you probably wouldn't wan to have heavily used databases on still anyway), which has a limit of ten. On 64-bit database systems there is no documented limit, therefore instances that hold many databases are without issue, apart from the typical I/O, network, and processor utilisation. In 2008, there has been an improvement on the compression of the log before it is applied to the mirror, but after testing with three times the average traffic with SQL Stress and RedGate's Data Generator, e.g. pumping three million inserts across with a bunch of large selects on the worst tables, we had only a max wait time to apply the log of only 1.2 seconds as the exception, whilst the Database Mirroring Monitor reported mostly under 200ms.
Verifying Port Availability
When you are configuring the network for a database mirroring session, make sure the database mirroring endpoint of each server instance is used by only the database mirroring process. If another process is listening on the port assigned to a database mirroring endpoint, the database mirroring processes of the other server instances cannot connect to the endpoint.
Start with a general verification that the ports are open on the remote EndPoint by running from the Run dialog box/cmd line:
telnet RemoteServerName 5022
To display all the ports on which a Windows-based server is listening, use the netstat command-prompt utility. You can identify the listening ports and the processes that have those ports opened, follow these steps:
1. Obtain the process ID of the respective instance of SQL Server, connect to that instance and use the following Transact-SQL statement:
SELECT SERVERPROPERTY('ProcessID')
2. Match the process ID with the output of the following netstat command:
netstat -ano
If ever you arrive with an error like this: Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.'. [CLIENT: IPaddress...]
This is probably happening because your encryption is setup differently on the mirror/principal.
The system table sys.database_mirroring_endpoints will show different algorithms- Mirror-encryption_algorithm_desc=RC4 and Principal- encryption_algorithm_desc=NONE
Therefore it is best to issue a:
Drop Endpoint Mirroring --(where mirroring is the name of the mirroring session given)
command on both mirroring partners. Beware, this to start mirroring from scratch, and this blows away all mirroring on the endpoint. The benefit, as I was tripped up by this personally, when you try and set up mirroring again, you are not stopped by a difference regarding encryption (e.g.). If DROP ENDPOINT is not possible, meaning you have other database Mirroring established already with another server, and then the other option would have been to run ALTER ENDPOINT on both instances For more information regarding Mirroring, check out the System Tables:
select * from sys.database_mirroring_endpoints
SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t ON e.endpoint_id = t.endpoint_id
select * from sys.endpoints
select * from sys.database_mirroring where mirroring_state is not null
-- which ones are in the mirroring state
--to see who has granted Mirroring and the grantee
SELECT EP.name, SP.STATE, CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) AS GRANTOR, SP.TYPE AS PERMISSION, CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;
I shall be updating this further once I have had success in production (May 20/2010 updated).
I hope this helps, if you are aiming on taking advantage of this great functionality
References and Recommendations from MSDN: http://msdn.microsoft.com/en-us/library/ms366349(SQL.90,printer).aspx
A great step by step script-based tutorial: http://www.mssqltips.com/tip.asp?tip=1705
