locked
Sql Server 2008 Replication RRS feed

  • Question

  •  Hi,

     I have a Sql server 2008 which I want to replicate to two other sites.

     What I want is that all sites are independent fro each other.

     Meaning if home office goes down, the two other sites are not effected .

     I  guess I need a domain controlled on each site so everyone is able to login.

     So when the home office becomes active again  everything is replicated.

     Is it possible to do this on SQL 2008 Standard?

     Thanks

     


    Erro

    Monday, February 20, 2017 12:06 PM

Answers

  • Based on the scenario you describe, you probably want to implement transactional replication, which is supported in SQL Server 2008 Standard Edition.

    Please review this article to determine if it fits your requirement:
    Integrating Data from Multiple Sites (Server)

    Here's a broader description to help you evaluate suitability of other types of replication:
    Selecting the Appropriate Type of Replication

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by Erró Wednesday, September 13, 2017 6:17 AM
    Monday, February 20, 2017 1:18 PM

All replies

  • Based on the scenario you describe, you probably want to implement transactional replication, which is supported in SQL Server 2008 Standard Edition.

    Please review this article to determine if it fits your requirement:
    Integrating Data from Multiple Sites (Server)

    Here's a broader description to help you evaluate suitability of other types of replication:
    Selecting the Appropriate Type of Replication

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by Erró Wednesday, September 13, 2017 6:17 AM
    Monday, February 20, 2017 1:18 PM
  • Hello Erro,

    Are you looking for an automatic failover? Do you want your users to be able to connect to the database server when the "home office" goes down?

    Or do you just want to replicate / copy your databases to have backups?


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Monday, February 20, 2017 3:18 PM
  •   Hi Ekrem and thanks for your reply,

       yes I want the users to be able to connect to the database when "home office" goes down and

       their work on the database to be replicated when "home office" is online again.

       brgds,

       


    Erro

    Monday, February 20, 2017 7:23 PM
  • Your task is hardly achieved by the transactional replication since it is one direction (from publisher to subscriber). When all of your servers are active, the home office database server is only used which the other two are not, right?

    A Fan of SSIS, SSRS and SSAS

    Monday, February 20, 2017 9:26 PM
  • You can do it with 2 hosts - use bi-directional transactional replication. If you want to use the enterprise edition you can use p2p replication.

    If your writes only occur on your home office - use transactional replication and SQL Authentication. If all servers are in the same domain windows authentication will work. You should also be able to use pass through authentication where the same windows accounts and passwords local machine accounts on all servers.

    Monday, February 20, 2017 9:49 PM
    Answerer
  • If other two sites do not need to be writable, and are readable only at certain points in time (snapshots), in Standard Edition of SQL 2008 you could use Database Mirroring to replicate entire database to one site, and log shipping to replicate it to another site. Or log shipping of office to both other sites. Domain is not required to do that. It requires a good setup and some scripting work to have good failover process. Test your failover procedure well. Transactional and other "classic" replications are more for object-level data replication. Log shipping, mirroring and availability groups are for database-level data replication, and clustering for instance-level data replication.
    Monday, February 20, 2017 11:23 PM
  •   Hi,

      well  what  the customer wants is probably not possible. This is for two Warehouses, and the SQL is rather slow when there some action going on in the warehouses. The SQL server is used for accounting and for the Warehouses. My customer is asking, if it is possible that all the db's can be used at the same time.

    brgds


    Erro

    Tuesday, February 21, 2017 6:23 AM
  •    Hi,

       To understand you correctly. I can do this with SQL 2008 ENT? by using two hosts and one subscriber? My    customer wants to use all the db's at the same time so, all 3 should be writable. Can this be handled any better  in  SQL 2012?

      brgds


    Erro

    Tuesday, February 21, 2017 6:31 AM
  • Options are limited when it comes to Standard Edition, also it makes it even harder using an older version of SQL Server in this scenario.

    Take a look at what Hilary suggested, with that, there's no automatic failover with Replication and please be aware that you'll still need to synchronize your server level objects (logins, SQL Server Agent jobs etc.) manually, yourself.

    And with replication they will not be "independent". When one of the sites goes down, the logs in the Transaction Logs will be hold at your other sites to be marked as "replicated". So this threatens other sites in terms of transaction log managibility.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Tuesday, February 21, 2017 6:42 AM
  • You could also do this by creating a GEO cluster between the 3 sites. Then you should be able to install your SQL Server to CSV on each side specifying that it is another instance for the main SQL Server. This way you will get 3 way replication of your SQL Server as well as automatic failover while still sitting on the Standard. Here is the link to a pdf that proofs you can do that on 2008 with Standard license (page 35).

    BTW, how far away are the sites from each other, and how good is the connection between them? 

    • Edited by NickSim Tuesday, February 21, 2017 3:46 PM
    Tuesday, February 21, 2017 3:45 PM
  • With a GeoCluster - only one node will be writable.

    If you can use the Enterpise Edition - P2P is the best fit. However you have stated that you are not. If you need all sites to be writeable and readable, you will need to use merge replication (available in the standard edition), but this is not that scalable. There are other issues with it, and if your head office is offline for an extended time period you will run into issues with metadata cleanup, expiration and identity pool exhaustion.

    Ekrem is correct in that there will be no automatic client redirection. This may or may not be a factor with you. if you have a remote distributor you will need to be concerend about log growth while the distributor is offline.

    Tuesday, February 21, 2017 4:00 PM
    Answerer
  •   Hi NickSim,

     the distance between is no more than 40km. I do not think that the connection between is good enough. It is ok

     when using RDP but the sites are connected to local network so that all traffic between is on private ip's. At the moment I am not sure the sites are connected using mpls  or vpn tunnel.

     brgds

      


    Erro

    Wednesday, February 22, 2017 6:43 AM