March 17, 2016 at 3:16 am
we are building SQL server 2012 always on for first time.
I have set up two node always on which is working fine and connectivity with a listener. everything is working fine including failover.
My doubts are.
Existing jobs, what is the modification to be done and how, this has to run only in active server.
How to configure backup, I have set secondary to take care of this.
How read-only node auto routing to be done, Always on not supporting secondary listener for read only node at any time.
Thanks all in advance.
Regards
Durai Nagarajan
March 18, 2016 at 12:59 am
Tables where are logins and jobs are different so it is needed to create also on another server. Problem it can be logins, because you have to copy with same SID.
And SQL Agent is stopped on secondary server, I turn on SQL Agent there manualy after changing and on primary server turn off.
About backup cares backup storage team, so cannot say. But what I checked we have backups on both servers to Storage.
And I can normally read data from secondary server.
Hope that it help, another guys maybe will say more.
March 18, 2016 at 6:26 am
Hi Durai Nagarajan,
Set up your existing SQL jobs on both nodes but disable the jobs on your secondary replica. The reason for this is that in case you encounter failover, you can just enable the same SQL jobs in secondary replica while you troubleshoot the problem in your primary replica. Once your primary replica is fixed and you need to failback, disable these jobs again in the secondary replica.
Configure your backup jobs on both nodes also. It doesn’t matter if you use maintenance plan or custom backup script. Enable it on both nodes. You mentioned that when you implemented AAG, you chose the secondary replica to take the backup right? So what will happen is that both jobs will run on both nodes but the job in Node 1 will stop after a few seconds but the job in node 2 will continue run until completion.
I hope this helps.
Thank you.
Best Regards,
Ricky
March 18, 2016 at 7:09 am
Hi
"but the job in Node 1 will stop after a few seconds" what I mean is this job will successfully run too but it wont create a backup file in node 1 because you specified in your "Backup Preference" during AAG setup to "Prefer Secondary".
Best Regards,
Ricky
March 18, 2016 at 10:15 am
The best solution for Jobs
In every Job add a step-1 where it checks for the primary of the Availability Group Databases and if that is success then it goes to the next step performing the actual tasks. Ensure the step-1 on both the nodes returning success but the one considered primary only will execute the task.
Backup Jobs to be setup based on the priority of node where you need to take the backup.
For SSIS packages better use listener for connection managers or if aware of parameterization of connection managers implement that in your package jobs. This will ease the maintenance in case of password changes. Please PM me if needed more info on how to implement this.
🙂
March 21, 2016 at 3:00 pm
I've heard of others setting up jobs on a third server, pointing at the listener.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply