November 15, 2016 at 12:29 pm
My employer has a OLTP database that's on a SS2014 Standard Edition server that's been overwhelmed by ad hoc queries, reports, and data mining queries.
I have attempted to offload all the non-OLTP work by implementing a log shipping solution (to provide a read-only copy of the database on another server, a SS2016 Standard Edition server).
Everything I've read about log shipping says that log shipping from an older version of SQL Server (the primary) to a more current version of SQL Server (the secondary) will work well.
I've not been able to make it work, either by:
1) having the Log Shipping setup GUI executed on the primary server, backup and seed the read-only database to the secondary server, or
2) by seeding the database on the secondary server myself.
What is working:
1) The initial, full backup of the OLTP database is successful.
2) The subsequent transaction log backups (produced by a Log Shipping (SQL Agent) job) is working.
What is not working: Nothing beyond the 2 items that work. The full copy of the database backup file is never copied nor restored to the secondary server. That step must happen for anything else to work.
The messages in the failure report, verbatim:
1) SQL Server Management Studio restore database "TestLogShipping" (Microsoft SQL Server Management Studio)
Additional Information:
2) An exception occurred while executing a Transact-SQL statement or batch (SqlManagerUI).
3) Cannot open backup device "\\serverName\TestLogShipping\TestLogShipping.bak"
4) Operating system error 5(Access is denied.).
5) RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201).
I've had 3 other people including another DBA and a Windows sysadmin involved to ensure proper share and permissions implementations. We all agree that they've been implemented correctly.
Has anyone experienced this(these) problem(s) and either solved it(them), or determined that what Iām attempting to do, cannot be done?
Could the problem be as simple as this cannot be done, from SS2014 to SS2016?
November 15, 2016 at 12:38 pm
IIRC the SQL Server AGENT service login account must have the permissions to the network share, not the SQL Server service login.
BTW, did you try a test restore (or even filelistonly restore) from the other SQL Server using a tsql script via SSMS?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 15, 2016 at 12:54 pm
Gail Wanabee (11/15/2016)
3) Cannot open backup device "\\serverName\TestLogShipping\TestLogShipping.bak"4) Operating system error 5(Access is denied.).
Those two lines right there indicate a permissions problem.
As Kevin indicated, make sure the SQL Agent service account on the SQL2016 server has access to that share and the file system within the share.
November 15, 2016 at 1:27 pm
Thanks to both of you for responding.
To answer your questions and suggestions, I'll explain the accounts, shares, and permissions in place and maybe that will produce the right information. After I do this, I'll answer an question, try anything you suggest.
(Server names changed for security reasons.)
We have a primary server, an OLTP, 2014 server, named "SQL". It has the "NT Service\MSSQLSERVER" and "NT Service SQLSERVERAGENT" accounts implemented as local accounts. They have "full control" permissions on every folder/share involved in Log Shipping on server SQL. They have no access to any folders on the secondary server.
We have a secondary server, a 2016 server, named "SQL02". It has "NT Service\MSSQLSERVER" implemented as a local account. This account has "full control" permissions on every folder/share involved in Log Shipping on server SQL02.
Also on the secondary server, we have a SQL Agent account "DomainAccount\SQLServerAgent" implemented as an Active Directory account across our entire domain. It has "full control" permissions on every folder/share involved in Log Shipping on both servers, SQL and SQL02.
Share on server SQL: We have the folder "B:\Backups\TestLogShipping" shared with full control access by the SQL Agent account on server SQL02. This folder is where primary server generated, log shipping generated, database backup files will reside.
Share on server SQL02: We have the folder "B:\Backups\TestLogShipping" shared and full control accessible by the SQL Agent account on server SQL02.
I've used identical locations and naming conventions for folders and shares on both servers for consistency. For the shares, the only difference is the server prefix ("\\servername") in the share name.
Does this information help?
November 15, 2016 at 1:33 pm
Kevin,
In response to your question:
"BTW, did you try a test restore (or even filelistonly restore) from the other SQL Server using a tsql script via SSMS?"
I didn't use a script. I performed a file copy of the full backup file from server SQL to server SQL02, then proceeded to use the SSMS GUI to restore it in STANDBY state.
Then, I copied a transaction log backup file to server SQL02 and attempted to restore it. This failed. The error was that it could not be restored the transaction log until the database was "upgraded". I investigated. To upgrade the database to SS2016 will require that I restore the full backup WITH RECOVERY, which will make it impossible to restore transaction logs.
November 17, 2016 at 8:26 am
Kevin is correct it's the agent service that requires the access to the remote share to pull the backups locally to the secondary before then attempting top restore them.
Are these servers and the user account the services run under, members of a domain or standalone?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" š
November 17, 2016 at 11:02 am
Gail Wanabee (11/15/2016)
I have attempted to offload all the non-OLTP work by implementing a log shipping solution (to provide a read-only copy of the database on another server, a SS2016 Standard Edition server).Everything I've read about log shipping says that log shipping from an older version of SQL Server (the primary) to a more current version of SQL Server (the secondary) will work well.
Keep in mind that while you can log ship from SQL 2014 to SQL 2016, the DB has to be restore WITH NORECOVERY to restore additional logs when the secondary is a higher version, which makes the DB inaccessible, which kinda defeats the point of using it for offloading work.
Logshipping up-version is mostly for a rolling upgrade scenario.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2016 at 12:55 pm
Thanks for your response, Gail.
From all the documentation I've read, I thought the database on the secondary could be restored in STANDBY mode allowing it to be accessible for reading.
From Books Online:
"You can reduce the load on your primary server by using a secondary server for read-only query processing. To do this, the secondary database must be in STANDBY mode. You will not be able to run queries if the database is in NORECOVERY mode. There are two options for configuration when you place the secondary database in STANDBY mode:
(a) You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. Disconnection will happen on the schedule you set for the restore job.
(b) You can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database."
I had planned to implement option (b).
I think I understand you correctly, just want to be certain.
What Books Online recommends is possible if I log ship from SS2014 (primary) to SS2014 (secondary).
Correct?
What you're telling me is that the problem I'm having is that by using SS2016 as the secondary, restoring log backups in STANDBY mode is not possible.
Correct?
If I've misunderstood you, and knowing what I'm attempting to accomplish, what would you recommend as a better solution?
November 17, 2016 at 1:50 pm
Gail Wanabee (11/17/2016)
What Books Online recommends is possible if I log ship from SS2014 (primary) to SS2014 (secondary).
Yes, you can restore the logs WITH STANDBY and hence have the secondary database readonly if the two servers are the same version
What you're telling me is that the problem I'm having is that by using SS2016 as the secondary, restoring log backups in STANDBY mode is not possible.
Log backups can't be restored to a database that was restored WITH STANDBY if the server the log backups came from is a lower version than the server the log backups are being restored to.
If the secondary is SQL 2016 and the primary is SQL 2014, only a restore WITH NORECOVERY allows for further log backups to be restored.
what would you recommend as a better solution?
Have the primary and secondary the same version.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2016 at 2:12 pm
Thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply