I have setup a performance monitoring of my SQL server system using typeperf.exe to store the values directly to SQL Server.
I went through the following article, that explains howto use typperf.exe with correct parameters and a counter configuration file to store the results into a SQL server database, after a connection to this SQL server has been configured in a local System-DSN:
I use a command as article explains like to this one:
TYPEPERF -f SQL -s SqlServer1 -cf “C:\Counters.txt” -si 15 -o SQL:SQLServerDSN!log1 -sc 4
After that I have the monitored values stored in my SQL server (as configured in the SystemDSN) and can easily query those (without having to bother with the different counter types and calculation of correct average values because of accumulated values)... e.g. like this:
SELECT MachineName , CounterName , InstanceName , CounterValue , CounterDateTime , DisplayString FROM dbo.CounterDetails cdt INNER JOIN dbo.CounterData cd ON cdt.CounterID = cd.CounterID INNER JOIN DisplayToID d ON d.GUID = cd.GUID
It works fine - except one detail: the tables are created in the dbo schema. I would like to have them in another schema. Anyone knows if this is possible and how?
Edit: I tried to use Windows Authentication because it always fails using SQL Auth and I found this article explaining that SQL Auth is kind of broken in DSN: http://forums.hexus.net/windows/178393-windows-server-2008-perfmon-logging-database.html
So changed to Windows Auth, I figured out the Account SQLAgent is running on, setup a new Job that runs the Batch and to be sure ECHOed out the user name in a log. It's the user the SQL Agent is running with.
This user has only the server role PUBLIC and is mapped to
msdb. So I mapped it to the destination DB (the DB that I have defined in the SystemDSN where I want the PerfCtr tables in) with default schema I want the tables to be in and granted him permission to ALTER the schema and assigned
datawriter role for the db.
After the JobAgent ran the job, the PerfMon tables successfully have been created -> but now they are in dbo schema ?
- How can this user create objects in dbo, that from my perspective it does not have permission to ?
- Why is the default database setting for this user not used by the connection defined in the system DSN?