replicate my database on the same machine

  • marchello

    Old Hand

    Points: 355

    Hi all,

    I worked with many databases like Oracle, MySql, Postgres...

    And SQL Server as well, long time no see.

    Basically, I would need my testdb database to be replicated into testdb_read database on the same machine. Any example for my case, please?

    Found this link, but example below is not clear

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addsubscription-transact-sql?view=sql-server-2017

    What are correct values to substitute placeholders in my case?

    This time I'm under linux and using Azure Data Studio, so no UI like SSMS available.

    Please advise.

    • This topic was modified 1 year, 4 months ago by  marchello.
  • andreas.kreuzberg

    SSCertifiable

    Points: 6055

    Hi,

    if you like powershell, take a look at this page:

    https://docs.dbatools.io/#Copy-DbaDatabase

    Your are able to copy a single database to a new one, with one simple script.

    Or do you need real time replication?

    Kind regards,

    Andreas

  • ChrisM@Work

    SSC Guru

    Points: 186107

    It depends on what you want to do with the copy, how "fresh" you need it, and what downtime is acceptable to you for refresh. Can you provide a few more details about the usage of this database copy? The stored procedure you linked is a part of Replication. You might also want to look into Snapshots.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • marchello

    Old Hand

    Points: 355

    The copy database should be as much real-time as possible.

    This is assignment for learning purpose.

    Looking into transactional replication.

    First of all, I found out that replication distributor is not available on linux version of sql server, so I'm installing it on windows for now and will continue there...

    • This reply was modified 1 year, 4 months ago by  marchello.

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

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