3

Scenario: We want to use SQL Server 2005 Standard's version of DB mirroring along with a witness server in an Active Directory domain environment. The database is fed from a 3rd party app server that cannot be modified apart from the DB connection string.

Two questions related to this:

  1. How can we make the failure somewhat invisible (or at least, require less manual work) to our 3rd party app server spending as little money as possible? My thinking is set up DNS "sqlserver.ourdomain" that points to the primary and use that in the connection string. Then should it fail, we change DNS to point to the mirror and re-start the app server. Not invisible or automatic but is cheap and seems like it ought to work.
  2. How do we push transactions from the secondary back to the primary after failure and re-promote the primary? This process must take into account "undoing" the failover mode in #1. Obviously if we go with the DNS option above we'd re-point DNS to the primary but how do I get the system back as before -- with the primary up to date from the mirror and the mirror demoted back to mirror?
Matt Rogish
  • 1,512
  • 6
  • 25
  • 41

2 Answers2

8

Question 1: You're overthinking it. SQL 2005's database mirroring specifications are built into the connection string. You specify both the primary server and the mirror right in the connection string, and the driver tries the servers in order. That's one of the beauties of database mirroring - no client app changes are required.

More info here: http://msdn.microsoft.com/en-us/library/5h52hef8.aspx http://msdn.microsoft.com/en-us/library/ms366348.aspx

Question 2: You said you're using a witness server, so I assume you're using automatic failover. SQL 2005's database mirroring does this stuff automatically - more info here:

http://msdn.microsoft.com/en-us/library/ms189590.aspx

If you're not using automatic failover, then it's still pretty easy: you just right-click on the database, go into Database Mirroring, and you can fail the databases back and forth from primary to secondary and vice versa. They automatically get back in sync after outages like reboots.

Brent Ozar
  • 4,425
  • 17
  • 21
  • Thanks. I think #2 will work fine (yes automatic failover). Unfortunately the app server doesn't "really" have a connection string -- I specify the hostname of the DB server – Matt Rogish May 06 '09 at 15:38
  • And in re: #2 when the primary comes back online, I must tell SQL Server to promote it thru the interface (the link you provided says it will rejoin just fine but not promote itself)? – Matt Rogish May 06 '09 at 15:43
  • OK, yeah, then in that case, you'll have to do the DNS switcheroo. Just make sure to set your TTL times really low on the DNS record. If you leave the defaults, it could be minutes/hours/days before the clients redirect to the right server. Also be aware that if the app expects the SQL Server's internal name (like @@servername or system tables) to reflect the DNS name, you can have problems. – Brent Ozar May 06 '09 at 15:43
  • Right, you don't want it to promote itself because that'll kill any outstanding connections on the secondary server. You're better off doing that in a graceful manner instead of breaking transactions. – Brent Ozar May 06 '09 at 17:15
  • Magic! I love SQL Server :D – Matt Rogish May 06 '09 at 18:01
  • When the original principal comes back online, it will be as the mirror - it can never promote itself as the principal again and force a failover - you always have to do that manually. – Paul Randal May 06 '09 at 18:59
2

One other thing to bear in mind (unrelated to your questions, but something people don't think about sometimes) - if the original principal is going to be down for a while, you should remove mirroring to prevent the transaction log on the new principal growing out of control. Otherwise, the transaction log all must be kept around because mirroring assumes the old principal will come back online at some point.

Thanks

Paul Randal
  • 7,184
  • 1
  • 35
  • 45