SQL Server 2016 - Always On RRS feed

  • Question

  • Hi Team,
    The SQL server we are currently with 2016 clustering, based on the organization we are moving from clustering to Always On.
    Overview of our DB:
    All are OLTP databases and SSIS is running to fetch information round the clock and insert data, every time approx..2 to 5 GB of data will be pushed into SQL Server (currently 15 TB is the size of this DB), there is one reporting database which run custom rules on the DB and get analytical report out of it (due to flexibility there are tables created in this DB at runtime to store the report values and discarded periodically).
    We are using trans. replication to keep the OLTP and reporting instances in sync.
    Currently we have 4 instances and 2 are used for reporting and 2 for OLTP (depends on load this also serves for reporting).
    While migrating to alwayson we are facing the architectural issue as follows:
    Tried SSIS job in primary server, however it is taking time to replicate to the copies due to the volume of data, also since the reporting DB creates tables run time we don't want this database to be part of AG, but our org. is not allowing this DB as standalone along with alwayson DBs in that instance. So we tried keeping the reporting DB in seperate server/instance - however it is having performance issue.
    Wanted and amicable solution for all the above issue in AlwaysOn 2016.. any suggestions please?
    Tuesday, July 28, 2020 10:43 AM

All replies

  • Hi MvKalyan,

    Before the migration,you can choose to reference this firstly:prereqs-restrictions-recommendations-always-on-availability

    And in your case, how many nodes in your failover cluster?

    This may be helpful:how-to-migrate-from-failover-cluster-instances-and-database



    Wednesday, July 29, 2020 5:57 AM
  • Hi Miaoyuxi,

    Thanks for your response. Yes I have gone thru this article. In the current setup we have two - two node clusters with active passive setup.

    In alwaysOn we are planning to have 2 clusters with one sync and two async nodes.

    I would like to get the expertise for the SSIS loader and the reporting DB to sit along with the AG group or how can we architect those DBs along with alwaysOn as SSIS needs individual run on instance and reporting need standalone DB as there are tables generated based on the UI selection in the database.

    Would be great if I know how I can fit this in AlwasyOn or any alternate solution for those DBs which should not affect performance.


    Wednesday, July 29, 2020 6:37 AM
  • Hi MvKalyan,

    What my understanding is there are 2 clusters and one cluster has 2 nodes. So there are 4 servers. You can keep the 2 for report and migrate the others to Always On. Reference this to migrate from FC to Alwasys On :how-to-migrate-from-failover-cluster-instances-and-database_to_Always On



    Thursday, July 30, 2020 2:39 AM
  • Hi Mia,

    Basically I am looking for an architectural or design solution to how my SSIS and reporting database ( not SSRS - custom built report DB with run time report creation happening in this) to be part of AG or a solution to build.

    I am looking for a solution to this. Thanks.

    Thursday, July 30, 2020 11:48 AM