February 28, 2012 at 2:18 am
Hi all,
Playing at different DR options in SQL and currently on Log Shipping.
I have finally got log shipping working.. But only after giving it way to much rights.
Reading "Microsoft SQL Server 2008 High Availability" - http://www.amazon.co.uk/Microsoft-Server-2008-Availability-ebook/dp/B0057P6ADO/ref=sr_1_87?s=books&ie=UTF8&qid=1330419345&sr=1-87
I needed to give the account that runs the SQL Agent and SQL Service (Not sure on last one, i used the same domain account for both anyway) SA rights to both primary and secondary servers.
Read - Write access to the folders for backing up and moving the files to the secondary.
In the end.. the only way i got it working was to give the account SA + local admin for both machines (guessing that means i messed up the folder permissions?).
After it synced ok (No errors in the agent log). I ran a few updates on the primary box and waited for the log backup. Went to the secondary and do a select * from mytable. but the data wasnt there. I had ticked the disconnect open sessions, is it just that happened andi was still querying the old data pre update?
Do you create a new account for each service for every SQL box you have. (I currently have all the SQL boxes and all services running from the same account)
What rights do you give those accounts on the domain? just a user and permissions to the folders they need?
Thanks for any advice.
S
February 29, 2012 at 4:05 am
Could be incorrect folder permissions--you certainly don't need to give the SQL service account local admin permissions on the server for log shipping to work. Difficult to say more given what you've said.
As far as the SELECT goes, depends on the timing of things, really. There are separate jobs that perform the copy logs operation and the restore logs operation, and if they run at the same time (which they do by default, I think) then you'll have to wait for *two* log shipping intervals for the updated data to appear on the secondary server--one to do the actual copy, then the restore job will see the new file at the next log shipping interval. You could manually modify the jobs so they're out of sync to help prevent that, but if you get a really big log backup it could still take longer to copy than the gap between the two jobs, so it's not a guaranteed solution.
SQL server service accounts--it's generally considered good practice to use different ones for each server, I believe. It's certainly best practice to give these accounts the minimum rights they need to do the job!
February 29, 2012 at 4:45 am
yeah i think it has to be the shares but i have no idea why its not working.. all ive done is
Share permissions where Everyone full control
and Security permissions where the SQL Agent Service account (An AD account) with full control.
Which i thought was higher than needed as they only warranted read/write.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply