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.