Database need to be replicated

  • Hi,

    I need to create a database that can be acceded by other people. This database needs to be up-to-date (with the source database) on a regular basis.

    This database will be on a different server in relation to the source database.

    What is the best thing to do?

  • As told, the two database will be on different servers.

    Maybe the best solution is to create a linked server on the destination server. Then on the destination database create a view like servername.database.dbo.table.

    This way users can query this view on the destination database without having permissions on the source database.

    Question is:

    Can I create an HTTP linked server?

  • If both servers share a SAN, some SANs have what I call a "SAN Snapshot" that will very nearly instantaneously create a copy of an existing database on the "other" box. We had a half TB database that we did that to in a previous company and the total time it took was just several seconds. The only warning that I have is that is IS a replacement and not an update Anything new you have in the "other" machine will be wiped out.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I don't have SANs. The SQL Server Instances are on two different servers (each server is a standalone server)

    Servers are on different physical places.

    Can I create http linked server?

  • river1 (12/20/2013)


    I don't have SANs. The SQL Server Instances are on two different servers (each server is a standalone server)

    Servers are on different physical places.

    Can I create http linked server?

    At this point, I'd recommend "just" using Replication of some form. If it's critical that the "other" server be up to date as soon as possible, consider "Merge Replication". If a delay is tolerable, consider "Log Shipping".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

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