Post reply

AlwaysON and dataloading

  • July 19, 2018 at 6:39 am

    #407211

    Hi all,
    I am looking for some advice on system architecture.

    I work for a company where we have the following situation:

    A production instance on a failover cluster Windows/SQL2016
    Every night certain databases are loaded with new data from third party vendors via import jobs(SSIS). This causes locks in the database and serverload.

    Two separate instances, each on a separate server, who receive copies of certain production databases at different times during the day. This is done by database restore with CommVault.
    Lets call these instances 1 and 2
    We have a 24/7 online system that uses these instances. With the help of a custom switching mechanism the application is switched from one instance to the other, after which the instance that is not used, is restored with the newer databases from  the production instance.

    This is what I want to do:

    The databases on the production instance are placed in a AlwaysOn AG. The secondary replica of the group is on the separate instance(only 1)
    The application works on the separate instance.
    When the data import on the production instance start, the data movement of the AG is suspend by way of a command issued by the import job.
    So the separate instance is not affected by the data import.

    After the import is done, the application is switched to the production instance and the data movement is resumed and the application is temporarily switched to the production instance.
    The databases on the separate instance are synchronized with the production databases and the application is not affected.

    After the sync the application is switched back.

     What do you guys think? Would this work?
    Is it a bad idea or maybe not?

    Cheers,
    Cor

  • July 25, 2018 at 9:52 am

    #1998959

    Hello,

    I can't give you a complete answer as I haven't used SSIS or had your exact scenario before. However the following might be useful:

    As far as I know the AG functionality is as follows:

    With SQL Standard you can only access one copy of the database, within the AG at once. I think with your planned setup you would want to access both?
    Even with Enterprise you can only read from the secondary, you can't write to it. The documentation quite often refers to it as 'read-only routing'. I don't know if you'd want to update the instance that isn't currently involved in the data load?

    The information at the following links might be useful:

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-2017
    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-2017

    Thanks

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

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