Post reply

Suggestions/Guidance for SQL 2016 Always on Datawarehouse

  • March 29, 2018 at 4:36 am

    #336839

    Forum,

    Currently we are implementing SQL Server 2016 Always On for our DataWarehouse, i have few doubts on the implementation for my current scenario. 

    We are moving from SQL 2008 to SQL 2016 with Always On feature with 2 nodes and NOT- shared resources . 

    We have our SSIS packages on file system so whenever we have a new release we update packages or add new packages , i'm planning on using a shared disk for the 2 nodes to have the SSIS packages accessible for both the nodes, just in case the jobs fails due to cluster failover. What is the best practice to implement in situations like that.

    Node A -- Primary  |  Node B --Secondary 

    1>What if the job fails due to cluster failure , if the node b becomes primary and the job is partially executed on Node A .. . How do we implement this kind of situation. 
    This is what im looking into 
    https://dba.stackexchange.com/questions/45137/sql-server-agent-jobs-and-availability-groups
    Will the SSIS Catalog be helpful in this scenario ?

    2>What is the best backup strategy for Always On SQL 2016 we are planning on Node B backups using Ola Hallengren maintenace solution 

    Thanks in advance 

  • March 29, 2018 at 11:15 am

    #1985011

    a- your packages need to understand how to handle partial execution and restart. The job won't move. If the cluster fails over because you move it, not a termination of hardware, the agent is still running on node A, so the job execution continues.  However, the connection to SQL is broken, as clients must reconnect to the listener. Your job is essentially dealing with a SQL server that disappears and reappears. What happens depends on your coding. If it's a single load stream, it's cancelled. If it's batched, some batches are there.

    b - backup strategy is what it is. Make backups according to your RPO/RTO requirements. You can run these on the secondary to reduce resource usage, but make sure you understand how backups from replicas work - https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server

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

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