Connection Strings: Database Mirroring Failover Partner Setup & Sample Backup/Restore Preparation Script

 

After having setup my first SQL 2008 R2 Enterprise (build level 10.50.1702) database mirror as High Safety with automatic failover, meaning synchronous commit on both servers (see operating modes), there was an obvious question that followed from those who need to employ this redundant database – which/what server do Users/Apps connect to? Sharepoint 2010 high-availability can be attained with a combination of mirroring also by the way, as documented here. 
Please note, this is a follow up to a previous post on resolving issues associated with Mirroring and taking advantage of the 64-bit benefit. This time around, since the version upgrade is two generations later, where last work on Mirror set was for SQL 2005, with 2008 only in testing, I am interested now to dig down and find out if the Log compression is actually running between the two instances (it is a new feature of SQL 2008), but first the connection string:

Server=tcp:DB01.RestOfFullyQualifiedDomain;Database=MirrorTest;Connection Timeout=30;Integrated Security=SSPI;Failover Partner=DB02.RestOfFullyQualifiedDomain

I set a 30-second timeout since this is geographically redundant database mirroring.
SQL default connection port is 1433, and mirroring uses 5022 to communicate between the quorum

MirrorTest will simply be replaced with whatever the actual database name one will be using.

Back to the Mirror Setup
Below is the script used for the database setup, since the previous post has details for full script setup.  This was followed by testing the push of four million rows, through direct 1M inserts into the four largest database tables; I noticed a maximum delay of around 3.5 seconds before the logs were all applied on the Mirror at a speed of 3-5MB/sec. And on another server that was Virtual Machine based, I did much the same test but with only 20ms commit overhead reported.

After some research around the web, I discovered the best way to avoid error: 1478 , after checking all of Pinal Dave’s useful list of pre-Mirroring checks also, was to do a as is shown below in the script.

n  I have Partner Principal=DB01 and Partner Mirror=DB02 and
Witness=WDB01 in the Full Mirroring quorum.

USE [master]-—on DB01
GO
DBCC CheckDB(MirrorTest) -– always double-check a DB before mirroring
GO
ALTER DATABASE [MirrorTest] SET RECOVERY SIMPLE WITH NO_WAIT
GO -– we force this to ensure we do not run into error: 1478
ALTER DATABASE [MirrorTest] SET RECOVERY FULL WITH NO_WAIT
GO
BACKUP DATABASE [MirrorTest] TO  DISK = N'Drive:\Backups\DB_converted.bak' WITH NOFORMAT, INIT,  NAME = N'MirrorTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP LOG [MirrorTest] TO  DISK = N'Drive:\Backups\DB.trn' WITH NOFORMAT, no_truncate, INIT,  NAME = N'MirrorTest-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

---NOW COPY DB BAK/TRN files to DB02 (or UNC shared folder references could work too)

RESTORE DATABASE [MirrorTest] FILE = N'db' FROM  DISK = N'Drive:\Backups\DB_converted.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO
RESTORE LOG [MirrorTest] FROM  DISK = N'Drive:\Backups\DB.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

Last Steps
Once your backup/restore are okay, run the Start Mirroring Command from the GUI (after setting up with the domain account that has Admin rights on all three servers), and make sure to start the mirror with Fully Qualified Domain Names. You should find the Status now as Synchronised.

Tried to Break the Mirror While Doing Stress Testing – But Still Fine J
Once the mirror was all setup nicely, and the stress test had worked out fine, I then began to and see what would happen if I shut down the Principal Server node via a SQL Server DB Engine service shutdown at the same time as a large stress-testing insert was happening. Instantly the Mirror became the Principal thanks to the Witness and the Database Mirror  Monitor stated there was a Mirroring State Disconnection, followed by a large couple of hundreds megabytes queuing in the Unsent Log from the history of the DB Mirror Monitor. Then I restarted the former Principal (now Mirror) DB Engine and the Unsent log disappeared while the new data was applied at 5MB/sec over the space of 45 seconds.
Once all was synchronised without traffic, I simply set through the GUI a Failover Mirror command to return DB01 as the Principal. The change was instant, although I was not inserting millions of records at the time. Cannot wait to see how this all pans out, in terms of Administrative intervention, over time.

 

Vermont SQL Server User Group Presentation on Database Mirroring (Official PASS Chapter for Vermont)

To celebrate the Victoria Day Holiday in Canada I drove down to the U.S.A. with 'ol Volvo Wagon (not Smart this time, since 3k of maintenance awaits it today :) to present on Database Mirroring on SQL Server 2008.

The meeting when well and I met some very interesting people at the Competitive Computing’s Mountain View offices in Burlington, VT. Roman Rehak helped out very much in the meeting, by not only hosting it for the PASS Vermont SQL Server User Group (thanks to MyWebGrocer.com for the Free Pizza and Drinks! btw), but asking all the right questions to enrich our understanding of this great SQL Server Feature.

Here are the updated presentation slides. I was able to setup a Synchronous Mirror, demonstrate a manual failover, and simulate high amounts of traffic with Data Generator (I’ll try SQLStress one day too, I hope).

I’m going to have to ensure I do a fresh install of Entreprise version before I can demonstrate an Automatic Failover with Witness since my version seems to have a registry problem. Both machines involved in the Partnership were SQL 2008 Entreprise…but one was a reinstall, so I am assuming the registry is still messed, and will sort that out this coming week with my first Windows 7 install on the XPS M1530.  Cannot wait for the second life of my SSD!

Roman’s great suggestion was that if we placed the Witness on a clustered server, then it would be a much optimised high-availability solution.
I will propose that for our future Mirroring Quorum setups.

Last years' talk at the Vermont User Group was given on Database Compression for those following this PASS Chapter..

 

Troubleshooting SQL Server Database Mirroring Problems with Best Practices: Requirements, and the 64-bit Benefit

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(?) before running a mirrored set up in production. I figure having the same highest-available build level is the best way to start a mirroing 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 woudn'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 improvment 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.

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

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.

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 
command on both mirroring parters (to start from scratch, beware, this blows away all mirroring on the endpoing), so that when you try and set up mirroring again, you are not stopped by a difference regarding encryption.  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 EPWHERE SP.major_id = EP.endpoint_idORDER BY Permission,grantor, grantee;

  I shall be updating this further once I have had success in production. 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

 Dell’s guide to Mirroring