none
Is replication what i need RRS feed

  • Question

  • Hi Everyone,
        My appoligies in advance if I have asked a question in the wrong forum.  I am trying to figure out what version of SQL server I need to address an implementation we are trying to achieve.  We currently have a single web server with Cold Fusion running on the server that access a SQL server.  We would like to have one server setup in one location with CF and SQL running and then another server setup in a geographically different location perhaps on the other side of the US.  We would like for these servers to be load balanced between the two locations therefore I believe I would need either replication or some other type of SQL server process running to keep both SQL servers in sync.  I have used SQL server for a long time but have never had to build something like this so I am very new int trying to figure this out.  Again I appologize if this is the wrong forum or if these questions are too basic in nature.  The SQL website has so many options its a little difficult to understand what versions and licenses one would need.
    Please let me know what your thoughts are and I appreciate your assistance in advance!
    Monday, February 22, 2010 6:51 PM

Answers

  • SQL Server does not really support load balancing. Ideally your web servers will be load balanced and they will hang off a single back end sql server. However as these are geographically dispersed this will not be an option.


    What may work is bi-directional transactional replication as long as your latency is not too great. Ideally transactions originating on Server A will stick with Server A and on failover the web/application server will redirect them to Server B.

    This can be achieved with the web edition of SQL Server.



    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, February 22, 2010 11:05 PM
    Moderator
  • If you have enterprise edition of SQL Server 2005 or 2008, you can have peer-2-peer replication.
    Blog: http://dineshasanka.spaces.live.com
    Thursday, February 25, 2010 8:25 AM
  • That's another option - however bi-directional transactional replication is faster than peer to peer - and it does not need the Enterprise Edition.

    You will need to set it up manually and it will not automatically replicate schema changes.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, February 25, 2010 2:02 PM
    Moderator

All replies

  • SQL Server does not really support load balancing. Ideally your web servers will be load balanced and they will hang off a single back end sql server. However as these are geographically dispersed this will not be an option.


    What may work is bi-directional transactional replication as long as your latency is not too great. Ideally transactions originating on Server A will stick with Server A and on failover the web/application server will redirect them to Server B.

    This can be achieved with the web edition of SQL Server.



    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, February 22, 2010 11:05 PM
    Moderator
  • If you have enterprise edition of SQL Server 2005 or 2008, you can have peer-2-peer replication.
    Blog: http://dineshasanka.spaces.live.com
    Thursday, February 25, 2010 8:25 AM
  • That's another option - however bi-directional transactional replication is faster than peer to peer - and it does not need the Enterprise Edition.

    You will need to set it up manually and it will not automatically replicate schema changes.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, February 25, 2010 2:02 PM
    Moderator