Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are going to set up a multi (at least 2) site configuration, and we want to achieve both HA as well as a good resource usage.

The sites will be interconnected through a high speed, dedicated fiber network, so latency will generally be very low.

However we still prefer that each site uses the nearest secondary server for read intent data.

Is this possible?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

It is possible, but it isn't an out of the box configuration. What you would need to do is setup a separate DNS named which points to all the secondary servers that you want to route requests to. Then setup the read only routing to point to that secondary DNS name. Then assuming that AD is setup correctly when people are redirected to that DNS name they'll always go to the local one.

share|improve this answer
1  
I was thinking through this question and was thinking of heading there also Denny, but that requires a different connection string for each area. But more importantly it potentially takes other read only secondaries out of availability for them right? I was wondering if this would be a good enhancement request for Connect. What do you think Denny? Sort of adding some rules on secondary routing? Maybe a preferred order for clients? Or preferred order by subnet or even some rule looking at latency between machines? –  Mike Walsh Nov 24 '12 at 1:33
2  
Nope, you use a single DNS name and a single connection string, letting the read only routing handle the handoff to the DNS name. Then the AD DNS servers direct you to the connect location based on which site you are sitting in. I'll have to write up a blog post about it, because it's pretty complex to setup. –  mrdenny Nov 24 '12 at 1:40
    
Hi Denny, this sounds like a good solution. I'm accepting the answer now because the question was it it was possible, but I hope you'll make that blog post anyway. If you do then please let me know. –  Tomas Nov 24 '12 at 1:54
    
Mrdenny, Did you ever write up this blog post? We are doing this and there is some confusion as to the DNS\AD configuration. –  JoelPizzo Jan 6 at 21:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.