I’m sad to say it took me so long to understand the problem. Make sure new servers and settings are configured properly before trying to fix things that weren’t broken before the new stuff existed. I then fixed the permissions for the shared backup directory back to the original setup. I restarted each with the correctly corresponding service and account. SQL Server was running using the SQL Agent service account. SQL Agent was running using the SQL Server service account. When the server was setup, someone switched the accounts. I logged into the new server and loaded up SQL Configuration Manager… Now everything failed except this new server. The new server could read the backup directory! So I took the normal account out of the permissions. What was wrong?!?Īt this point, I just decided to throw a hammer at it and throw in extra service accounts until hopefully one worked. Eventually I added the SQL Agent account. Everything worked perfectly except on this new server. It looked fine in AD, it looked fine on the Shared folder. This account had every SQL service account as a member. Just make sure your service account has permission here.ĭrilling down through Active Directory, I found the account used for sharing the folder. Sharing a folder makes the file path easier. Every time I’ve had an issue with the permissions of xp_dirtree reading servers, it’s been because the service account in the shared folder was wrong, so my next stop was checking out Active Directory. The folder where all the files are stored is a shared folder, so I went into the Properties of the folder and verified the account that was shared. Policy Based Management and xp_dirtree use this account. The account was in SQL and it had all the proper permissions. The account in question was the SQL Server service account. This was a new server, so I assumed it would be something simple like that. I verified the logins were all present in SQL by reviewing Security -> Logins through Management Studio. That meant that there was something wrong with the permissions, the problem was, what? So it wasn’t the backups or copy failing. My first idea was, “Well, there’s no files!” Easy! Problem solved! Or not…the files were in the folder, and the number was even correct. The new server wasn’t processing the policy correctly. There aren’t a lot of examples on the web, so I try to provide as many as I can. This isn’t exactly how we do it, but it gets my point across and it provides a good example of how a moderately complex policy works using ExecuteSQL. Each folder should have a backup file for each database, except for TempDB. The dumbed-down policy checks a backup server, which has a folder named for each SQL server, to see if it has the correct count of backups. INSERT _dirtree result for easy policy evaluation 1=Pass if( # files = # dbs ) Insert DirTree results into a table variable to return a numeric resultĭECLARE TABLE (SubDirectory VARCHAR(250), Depth BIT, BIT) SELECT = ''\\BACKUPSERVER\BACKUP\'' CONVERT(VARCHAR(100),SERVERPROPERTY(''MachineName'')) SELECT = COUNT(*) FROM sys.databases WHERE database_id <> 2In case you would like to implement something like this on your system, the dumbed-down condition of the policy is below: Simple enough, but in the past, this policy has been prone to permissions errors. The policy verifies that every SQL server has a backup file stored on a remote directory. Today we had a policy failure on a newly built SQL server.
0 Comments
Leave a Reply. |