Money no object, what would be your ideal sql server production solution / environment???

  • so in an ideal world where your boss came in and said you can any sql server solution (including DR) you want, what would you go for? Load Balanced? 2 or more SAN's with snapshop (SAN) replication? Active-Active cluster?

    Or keep it simple with log shipping / mirroring?

    I realise this is very much a "it depends" based on the environment you're designing for but just interested in peoples thoughts and ideas on this......

    _________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

  • I'd go with mirroring myself, but I'd supplement it with log shipping to recover from non-critical errors (like table deletes), maybe with a 2 hour delay on the restores.

    Geo-clustering would handle the whole server, but it's more complicated than I know how to deal with, so I'd avoid that. SAN snapshots are interesting, but again, out of my league. I prefer simpler, better understood solutions.

  • has anyone on here had any experience of SAN replication?

    what i'd like to have is 2 sites, each with its own load balanced (active/active) clustered environment and SAN (FC or iSCSI - not decided yet). The DR site kept up to date with SAN replication or possibly mirroring / shipping, as you say steve, its easier to stick with what you know. I understand the cost implication of this but i'd be looking for maximum performance, availability and resiliance.

    Does anyone have a better solution to achieve this?

    _________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

  • Thanks so much for sharing the post.:-D

    credit auto[/url]

  • Swirl80 (6/15/2009)


    so in an ideal world where your boss came in and said you can any sql server solution (including DR) you want, what would you go for? Load Balanced? 2 or more SAN's with snapshop (SAN) replication? Active-Active cluster?

    Or keep it simple with log shipping / mirroring?

    I realise this is very much a "it depends" based on the environment you're designing for but just interested in peoples thoughts and ideas on this......

    Oh great. I asked this only a little while ago, then I got told by SSC regulars that I should shut up as I was asking a rubbish question. Now I see that Steve has responded immediately with an answer!

    Seriously, I've never been able to get a good answer on ANY questions about server sizing for SQL Server. I even asked what sort of figures I'd need to know before I could get an estimate on what server is required. I'd have thought in these economic times that it would be a topic of interest... evidently not it seem.

    Random Technical Stuff[/url]

  • Swirl80 (6/17/2009)


    has anyone on here had any experience of SAN replication?

    what i'd like to have is 2 sites, each with its own load balanced (active/active) clustered environment and SAN (FC or iSCSI - not decided yet). The DR site kept up to date with SAN replication or possibly mirroring / shipping, as you say steve, its easier to stick with what you know. I understand the cost implication of this but i'd be looking for maximum performance, availability and resiliance.

    Does anyone have a better solution to achieve this?

    SAN replication - an expensive form of database mirroring. What it gives you is slightly less chance of any data loss, and it doesn't matter how long the other server is down you can resynch without having to do a database backup\copy \restore, so if the databases(s) are very large it has benefits.

    If you just have the basic database engine it works well and you can also include the system databases in the replication, which is cool. However start introducing replication, SSIs,SSAS, SSRS it gets more complex and requires careful thought. SSRS in particular can be a problem with the encryption as the key is based on computer name and user name. SAN replication actually fits better with sQL 2000, as 2005 has got more complex and introduced more server specific items, such as the local windows groups SQL creates on install.

    I have not tried it yet but it looks to me like SAN replication would work best and have most benefit if you also boot from SAN, then failover should be quick and fairly painless. the reason for this is SQL will always install items to the OS drive, no matter where you specify in the install process. This proves to be a pain if you attempt to replicate any more than user database data and log files.

    If you use windows 2008 you can use multi site clustering, the nodes can be in different data centres connected to different SANS kept in synch by SAN replication, that way all bases are covered.

    ---------------------------------------------------------------------

  • ta.bu.shi.da.yu (7/3/2009)


    Swirl80 (6/15/2009)


    so in an ideal world where your boss came in and said you can any sql server solution (including DR) you want, what would you go for? Load Balanced? 2 or more SAN's with snapshop (SAN) replication? Active-Active cluster?

    Or keep it simple with log shipping / mirroring?

    I realise this is very much a "it depends" based on the environment you're designing for but just interested in peoples thoughts and ideas on this......

    Oh great. I asked this only a little while ago, then I got told by SSC regulars that I should shut up as I was asking a rubbish question. Now I see that Steve has responded immediately with an answer!

    Seriously, I've never been able to get a good answer on ANY questions about server sizing for SQL Server. I even asked what sort of figures I'd need to know before I could get an estimate on what server is required. I'd have thought in these economic times that it would be a topic of interest... evidently not it seem.

    for what its worth I've added a reply to your post , might spark a round of answers.

    I can't see where anyone told you to shut up though, just that the subject was a bit esoteric and one of those 'it depends' questions.

    I have posted questions before that have not had a great response, sometimes you are just unlucky, and remember no-one gets paid for doing this. Or maybe our questions are so advanced few people can tackle them! 😀

    ---------------------------------------------------------------------

  • I'd go with mirroring myself, but I'd supplement it with log shipping to recover from non-critical errors (like table deletes), maybe with a 2 hour delay on the restores.

    Isn't there a limitation on the number of databases that can be mirrorred on SQL Server 2005? I've read somewhere the limitation for 32 bit platform is 10 databases and twice as many for a 64 bit platform.

  • I'm not aware of any hard limit, only ones imposed by available resources.

    To answer the original question, each has its advantages and disadvantages - you would have to choose the most appropriate one for the situation. Unlimited budget just means that you aren't constrained in which options are available.

  • I'm not aware of any hard limit, only ones imposed by available resources.

    To answer the original question, each has its advantages and disadvantages - you would have to choose the most appropriate one for the situation. Unlimited budget just means that you aren't constrained in which options are available.

  • Sorry for double post - site is giving a .net error message.

    Steve Jones, if you read this, this is a detailed .net message that should be trapped before it's sent to a remote user.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply