February 1, 2021 at 5:21 pm
I am hoping someone can help with an issue I am having while trying to add very large database to SQL 2014 Server Availability Groups
I understand there is a wizard that will perform the task and there is a manual process of backup, restore and then join, but I wanted to see if there was a more efficient way to perform the action when the database is very large
The problem is that I am working in SSMS using RDP and the session will time out and disconnect after a set period of time and that causes the work to stop. To deal with this I create an Agent jobs to execute the backup on the primary and another agent job on the secondary to restore of the database and then join the database to AG. The result is that if the RDP sessions times out I am not going to lose any work
So when using the Wizard we have the opportunity to script out the add database task and I am required to run the script in SQLCMD mode. That is nice, but still my time out problem will happen because I am running that script is a query window (SQLCMD Mode)
I tried to save the script that the wizard generates to a file and then used an agent job to run it. I used a job step type called Operating System (CMDEXEC) and in the command section I put the path to the file.
I encountered two problems, but one might be unrelated to the other
When I executed the agent job it gave an error %1 is not a valid Win32 application.
so when I trying to troubleshoot I noticed that the primary replica cluster service shut down and Availability Group went into a resolving state. I got the cluster service started again and AG went back to normal. I quit trying - its a test box, but I did not want to cause any problems.
My question - what is the best way to get these large database into AG
A: Is the only method for large database to manually add them
B: How could I get the SQLCMD Script to run successfully in an agent job
C: Would it be best to just connect to the server using VCenter, which never times out, and just run it that way
Any help is appreciated.
Jeff
February 1, 2021 at 7:44 pm
If you were on SQL Server 2016 or higher you could try automatic seeding - but since you are still on 2014 your only options are backup, copy and restore.
I am a bit confused though - an RDP session shouldn't 'time' out and cause an issue. If you get disconnected from an RDP session - that session should still be active but in a disconnected state and any processes running should still be running. Trying to manage that through agent jobs isn't going to work very well - because you need to join immediately after the latest transaction log backup has been applied - or you end up having to restore additional transaction log backups until the databases are at the same point in time.
Instead of trying to figure out a workaround - I would focus on why the RDP session is timing out and killed. If you can resolve that issue then you don't have to figure out a workaround.
BTW - how long is it taking to restore the database? I have a 3TB+ database and I can restore that database in about an hour.
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
February 8, 2021 at 3:30 pm
Thank you very much for the feedback
The RDP time out is by design from the Server team and they tell me to use a VCenter console if I do not want it to time out
Based on your feedback I will just use the FULL backup from Sunday night and just restore it to the secondary on Monday morning and then join. The backup takes 7 hours to complete and probably 2-3 hours to restore if not more.
Again thank you
Jeff
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply