Moving the SQL, Agent, and full-text logs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Moving the SQL, Agent, and full-text logs

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2013-04-04 : 14:08:25

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

5072 Posts

Posted - 2013-04-04 : 15:33:11

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.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2013-04-04 : 15:44:19

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...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-05 : 10:07:07

You mean SAN. not NAS. Big difference.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2013-04-05 : 21:14:53

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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-06 : 21:37:12

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.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2013-04-09 : 14:16:04

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.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-09 : 15:31:06

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