SQLServerCentral / Discuss Content Posted by Mark Cook / Article Discussions / Article Discussions by Author / Achieving Server Redundancy at Remote Offices / Latest Posts

RE: Achieving Server Redundancy at Remote Offices

Mark F. Cook — Fri, 25 May 2007 20:08:00 GMT

Mikeit sounds like a good approach but one we never considered. I'll be interested in trying it at some point.thanksmark

RE: Achieving Server Redundancy at Remote Offices

netmikem — Fri, 25 May 2007 16:06:00 GMT

Hi Mark.

This topic is dear to my heart these days as I've recently been charged with BCP for our firm.

One of the guys working with me suggested the use of a CNAME DNS entry to achieve something similiar to what you did with the IP address solution. The idea is to have a generic server name that can be resolved to a real machine name and in the event of the production server going down, you simply change the CNAME entry to the secondary machine.

For example, consider the following configuration. The production database server is named PROD and the failover one is called EXTRA. You would create a CNAME DNS entry named DBSERVER and point it to PROD.

All applications would be configured to connect to DBSERVER.

If PROD ever went offline unexpectedly, you would change the CNAME DNS entry for DBSERVER from PROD to EXTRA.

We've tested it and it seems to work like a charm.

I'm interested to know if you considered this approach but decided the IP address strategy was better for some reason?

Regards,

- Mike

RE: Achieving Server Redundancy at Remote Offices

Mark F. Cook — Fri, 25 May 2007 06:27:00 GMT

There isn't any real limitation to the functionality of MSSQL with log shipping - at least not the "roll your own" kind i've used in the past. In my experience with log shipping (SQL2K), the destination database can be accessed by users in between the times when logs are restored to it. At that point, user connections have to be killed as the database needs to be in single user mode. My understanding of licensing is that if the destination server is used only for failover, a second license isn't required. However, if you allow users to connect to it, i would think a second license would be necessary.

RE: Achieving Server Redundancy at Remote Offices

Charles Tsang — Fri, 25 May 2007 04:48:00 GMT

On a course I was on, the passive server in a database mirroring setup for SQL2005 was said to be acting in unlicensed mode. This would mean that full functionality was not available (reporting/analysis).Have you found this to be the case for your situation where the passive server is unlicenced?Charles

RE: Achieving Server Redundancy at Remote Offices

Mark F. Cook — Tue, 30 May 2006 12:38:00 GMT

sorry, posted this earlier but apparently i had some issue with my connection. anyway, to be completely honest, we probably should have included it as an option. we were/are aware that its was possible to use replication in this way, but most of what we read in posts, etc steered us away from using it for failover.

RE: Achieving Server Redundancy at Remote Offices

Andy Warren — Tue, 30 May 2006 06:58:00 GMT

Did you consider replication as an option?

RE: Achieving Server Redundancy at Remote Offices

Mark F. Cook — Thu, 25 May 2006 12:16:00 GMT

depending on the availability of the server, you may be able to ship the last log, etc. if the server is completely unavailable, there's not much you can do except accept that some data will be lost. what we tried to do was to gauge how much time we could afford to lose in the event of a system failure. obviously you can configure longer or shorter time intervals, but the less time you can afford to lose, the more you need to look at something like clustering or perhaps database mirroring in SQL2K5 that provides more real-time synchronization.

RE: Achieving Server Redundancy at Remote Offices

Hoang Binh — Thu, 25 May 2006 11:04:00 GMT

A question please.When failure occurs in the primary server, how can you get data from the last synchronization to the time before the failure?

RE: Achieving Server Redundancy at Remote Offices

Mark F. Cook — Thu, 25 May 2006 09:43:00 GMT

thanks for the comments so far, all are appreciated and noted...

RE: Achieving Server Redundancy at Remote Offices

K. Brian Kelley — Thu, 25 May 2006 09:37:00 GMT

and the wan link is down, there are procedures in place that a designated person at the facility can be guided through by a DBANope, you got it. That's what I was looking for. I didn't see that in your article and this is an area some organizations miss.

RE: Achieving Server Redundancy at Remote Offices

Mark F. Cook — Thu, 25 May 2006 09:03:00 GMT

the two servers - primary and secondary are located next to each other on the local lan. In the event that there is a problem with the primary and we need to failover to the secondary ... and the wan link is down, there are procedures in place that a designated person at the facility can be guided through by a DBA....or did i miss your point entirely...

RE: Achieving Server Redundancy at Remote Offices

Mark F. Cook — Thu, 25 May 2006 08:45:00 GMT

thanks for the comments. you're correct, the setup can accommodate only a single failure in the multiple primary-single secondary situation, but our assessment was that you can go pretty far down the proverbial rabbit hole in trying to achieve redundancy, and that this was as you said a reasonable, low-cost solution....

RE: Achieving Server Redundancy at Remote Offices

K. Brian Kelley — Thu, 25 May 2006 08:41:00 GMT

How are you handling network outages?

RE: Achieving Server Redundancy at Remote Offices

Lisa Slater Nicholls — Thu, 25 May 2006 08:36:00 GMT

You're probably better off maintaining the second license if you go ahead with your strategy of having the secondary work for multiple primaries. It might be in use frequently, albeit for short periods each time.

But... if you have multiple primaries for the secondary, I don't think your switch of the secondary's IP address and alias is going to work as well. What if two primaries fail at once? It's unlikely, I'm sure, and this article is a good treatment of a reasonable, low-cost and responsible method. Thanks!

RE: Achieving Server Redundancy at Remote Offices

Mark F. Cook — Thu, 25 May 2006 08:02:00 GMT

thanks for the information. I knew only one license was needed when using Microsoft Log Shipping included in Enterprise edition, but thought that anything outside that required additional licenses. Again, thanks...

RE: Achieving Server Redundancy at Remote Offices

SQLZ — Thu, 25 May 2006 07:59:00 GMT

Good article.By the way, it sounds like you've got a couple of unused licenses. You do not need to buy a sql server or windows server license for the passive server - either in log shipping or clustering. Unless of course you're using the SQL Server for other, non-passive, purposes.Taken from Microsoft's licensing white paper:"When doing failover support, a server is designated as the passive server. The purpose of the passive server is to absorb the data and information held in another server that fails. A passive server does not need a license, provided that the number of processors in the passive server is equal or less than those of the active server. The passive server can take the duties of the active server for 30 days. Afterward, it must be licensed accordingly."

RE: Achieving Server Redundancy at Remote Offices

Tim Chapman-218780 — Thu, 25 May 2006 07:12:00 GMT

Great article. - Tim Chapman

RE: Achieving Server Redundancy at Remote Offices

Mark F. Cook — Thu, 25 May 2006 06:51:00 GMT

It depends. sounds like a microsoft answer doesn't it. But seriously, if you include the unused IP address the failed production server gets changed to then yes - 3 IP addresses will be needed. If you're only talking about normal production operation, then you only need two - one for the alias and the production server and another for the log shipping server.

RE: Achieving Server Redundancy at Remote Offices

Robert Sterbal — Thu, 25 May 2006 06:41:00 GMT

I'm not sure I follow these IP addresses:

Production Server: Name : PROD1 IP : XXX.XX.XX.223

Secondary Server: Name : LOGSHIP1 IP : XXX.XX.XX.224

We create an alias like this:

Alias : Name : PROD1V IP : XXX.XX.XX.223

shouldn't there be 3 total?

Achieving Server Redundancy at Remote Offices

Mark F. Cook — Tue, 25 Apr 2006 22:26:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcook/achievingserverredundancyatremoteoffices.asp