Post reply

Need help configuring replication at the Instance not DB level

  • December 8, 2020 at 8:31 pm

    #3818363

    Is it possible to create replication at the Instance level and not just the DB level?

    We have a third party software package that creates new databases due to certain criteria being met. We need any new databases that will be created to also be replicated. I found an article on MS's website and tried to post a link but it was removed by the system here thinking it was SPAM, I am not sure why. I just need to be able to replicate at the Instance level so any newly created DBs will be replicated as well.

    Thank in advance for your help with this matter.

  • December 8, 2020 at 9:04 pm

    #3818371

    I cannot think of something out of the box that does this, third part or not.

    i think you have to do this manually, and it will be a bit of work;

    I am assuming you always replicate all table objects in a given database  from one specific server to another specific server.

    you need to create a job that periodically checks  and performs the following:

    compare the current databases, minus exceptions, to whatever is currently in the distribution database;

    if the database is not featured in replication, create one or more subscriptions for the groups of tables that exist in the target database.

    That's a fair bit of scripting to do, I just started googling for it myself, If I find anything that looks like it might fit, I will post back.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 8, 2020 at 9:27 pm

    #3818373

    What replication technology are you using now?  Is this an availability group - or something else?

    If availability group, then you would need to add each new database to the availability group when it is created.  I think that could be handled by a trigger...but probably would be much safer and easier to create an agent job and code a solution to add the database after it has been added to the instance.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • December 8, 2020 at 10:08 pm

    #3818389

    Lowell - Yes, we would want to replicate all databases contained within an instance. Being in way over my head at this point, I hope that you have better luck than I did trying to find a solution that does that.

    Jeffrey - We are using availability groups. I am not at a high enough level to know how to do that using triggers or an agent job. Any help that can be offered would be great!

    Thank you both.

    DCL

  • December 8, 2020 at 10:22 pm

    #3818392

    For AG's - you need to build a script that adds the database to the availability group.  This may be something that can be done using automatic seeding - depends on whether that was enabled or not and if it can be enabled.  Here are a couple of articles/documentation on automatic seeding:

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatic-seeding-secondary-replicas?view=sql-server-ver15

    https://www.sqlshack.com/automatic-seeding-in-always-on-availability-groups/

    Using this - you can create an agent job to detect a new database - and if found, add it to the availability group.  Once added - automatic seeding will create the database on the secondary and synchronize everything for you.

    This should work on new databases fairly quickly since there shouldn't be a lot of data in that database to be streamed to the secondary system(s).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • December 8, 2020 at 10:24 pm

    #3818393

    DCL wrote:

    Jeffrey - We are using availability groups. I am not at a high enough level to know how to do that using triggers or an agent job. Any help that can be offered would be great!

    If you do not have the level of permissions to add databases to the AG - then you need to work with the DBA that does have that level of access to setup and manage this process.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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