Microsoft SQL Agent Backups
We recently began to finally use the SQL agent to perform our backups as opposed to doing daily SQL dumps that are then picked up nightly by NBU...
This is not so much an issue as I know the fix, but I am still seeking the reasoning for the behavior and the best way to deal with it.
Our master server runs on AIX and then we have a 5230 appliance and the client in this case is a SQL server running on a Windows 2008 R2 VM. The VM itself is backed up nightly using a vmware intelligent policy. As a result of this method, the client name is upper case. For all of our client side backups (physical/non-vmware) we list the clients in the policy as lower case and because of this, when we made the SQL agent policy, we added the clients as lower case.
Essentially what I see is this:
1. physical server, added to policy as lowercase, backed up normal. SQL agent policy, client added as lower case, SQL admin script has client as upper case, backup runs and everything completes successfully. In the activity log, the SQL agent backup appears all lower case as well. Life is good...
2. VM, intelligent policy backs up the server as upper case. SQL agent policy has the client as lower case, SQL admin script is upper case. When the backup runs, it fails saying the server does not exist in the policy and lists the server as upper case. in the activity log, the parent job lists the client as upper case. If I change the server in the policy from lower case to upper case, it backs up fine.
So, what I am trying to determine is why the SQL agent policy behaves different for the 2 cases. why would it look at one server and put it in the activity log as lower case, but then look at the other as upper case. They are both in the SQL agent policy as lower case. They are both in the SQL backup script as upper case.
The fix is to simply add the client to the policy as upper case and then it backs up fine, but why would it behave differently for each client.