SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Moving the SQL, Agent, and full-text logs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/04/2013 :  14:08:25  Show Profile  Reply with Quote
Ok, during the installation of SQL Server 2008 R2 (Enterprise x64) I changed the "Data root directory" thinking it would only affect the system databases (as I wanted them on our NAS). Evidently this also installed the following folders on the NAS:

  • FTData (full-text language files)

  • Data (system databases)

  • Jobs (sql agent job logs)

  • Log (sql server logs, sql agent logs, full-text logs)


I need to move FTData, Jobs, and Log back to where they would have been originally installed. Actually, FTData is moved since it was just a registry key that had to be changed and copy the folder over. But when attempting to move Jobs and Log, when I restart SQL Server I get "access denied" messages on the folder C:\Program Files\Microsoft SQL Server\MSSQL10_50.<myinstance>\MSSQL\Log\.

I know how to change the configurations to tell SQL Server where these files should be, but is there an easy way (other that uninstall/reinstall) to identify/grant the permissions needed for SQL Server to access these locations?

To give a little background on the accounts being used. Each service (sql server, sql agent, full-text) run under different accounts. SQL Server and SQL Agent are both domain accounts, while full-text runs under LOCALSERVICE.

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/04/2013 :  15:33:11  Show Profile  Visit russell's Homepage  Reply with Quote
You wanted your system databases on a NAS? Why? That is a bad idea from the get-go.

Seems your SQL Agent service account doesn't have permissions on the path in question.

Also may want to have a look at the undocumented procedure sp_set_sqlagent_properties -- which really just writes the registry values for you.

Edited by - russell on 04/04/2013 15:35:10
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/04/2013 :  15:44:19  Show Profile  Reply with Quote
Just curious why you think putting the system database files on our NAS is a bad idea? I've always followed that practice and have never had a bad experience. Ok, I lied. Back in the day when it was an iSCSI SAN... but that led to issues with all databases. Another story. Our NAS right now is NetApp that is replicated to a DR site. I'm perfectly comfortable with keeping the system databases on the NetApp. But that doesn't mean there aren't other concerns I simply am unaware of.

But back to the permission issue. I was hoping someone knew of an easy way to identify the permissions that are on the current target so I can duplicate them to the new location. Other than trying to eyeball it and leaving it suspect to human error.

Note: the above statement applies to both the sql server logs as well as sql agent...

Edited by - tfountain on 04/04/2013 15:45:48
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/05/2013 :  10:07:07  Show Profile  Visit russell's Homepage  Reply with Quote
You mean SAN. not NAS. Big difference.

Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/05/2013 :  21:14:53  Show Profile  Reply with Quote
quote:
Originally posted by russell

You mean SAN. not NAS. Big difference.





haha, yes I do. I must go have my doctor test me for dyslexia! lol
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/06/2013 :  21:37:12  Show Profile  Visit russell's Homepage  Reply with Quote
Make sure the SQL Server and SQL Agent accounts have full control permissions on the directory. That really ought to solve the problem.

Also make sure the drive, directory, and files aren't marked read only.

BTW -- we use NetApp appliances as well and have several. We use Snap Mirror and Flex Clone to maintain our DR site and development environments which are geographically remote from the production data center, and we maintain a huge data store using both Snap Mirror and Flex Clone as well, which we use for reporting and for a business analyst sandbox. We do more with the technologies than NetApp themselves envisioned and are occasionally spotlighted by them for potential new customers.
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/09/2013 :  14:16:04  Show Profile  Reply with Quote
Russel, thanks for the input. What company are you referring to? I looked at your profile and only saw the link for the NE Ohio Pyro. I'm actually in NE Ohio myself.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/09/2013 :  15:31:06  Show Profile  Visit russell's Homepage  Reply with Quote
You should come to some of our events then!

Shoot an email or leave a voice mail on the NEOPG line and I'll get back to ya.

For now, I'd rather not answer that question here in the forum, which is why it's not in my profile
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/09/2013 :  16:10:47  Show Profile  Reply with Quote
FYI - Found my savior: http://support.microsoft.com/kb/323007
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/09/2013 :  16:14:18  Show Profile  Reply with Quote
God, I really must proof read better - I'm in NW Ohio lol.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/10/2013 :  11:14:13  Show Profile  Visit russell's Homepage  Reply with Quote
LOL.

O-H-I-O
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/11/2013 :  13:53:30  Show Profile  Reply with Quote
The real question is, are you a Browns fan or a Steelers fan? :)
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/11/2013 :  15:09:56  Show Profile  Visit russell's Homepage  Reply with Quote
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/12/2013 :  00:09:39  Show Profile  Reply with Quote
I'm so sorry :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000