ACS SE 5.1 backup to remote database
Hi experts,
Trying to backup Cisco ACS SE 5.1 internal database to remote SQL server. Only inputs in ACS web GUI which I can insert is:
-server, port, username, password and database type (MS SQL or Oracle) and time period.
Account under which backup is running is DB owner (full rights). SQL database is 2005 (cisco recommendation) and has no instances. Have to say that I'm not SQL expert :)
I can connect to this database with any SQL client so it looks like credentials are ok. When ACS try to make backup I see on MS SQL in Activity monitor that prosess has :
- status "sleeping"
- command "awaiting command"
I don't know if MS SQL can give me some debbug info about the session or any other info so I can determine what's going on on SQL server. Have the followig debug from ACS server. Not sure what it menas:
Apr 04 2011 08:30:00 com.cisco.nm.acs.view.comm
artzScheduler_Worker-8 Acs.MGMT.ACSVIEW Export Data : exception org.springframework.jdbc.U
ent operation; uncategorized SQLException for SQL []; SQL state [HY000]; error code [-660];
--- The error occurred in RemoteDatabaseMap.xml.
--- The error occurred while applying a parameter map.
--- Check the RemoteDatabaseMap.exportDa
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Server 'IP address': [FreeTDS][SQL Server]Write to the s
erver failed; nested exception is com.ibatis.common.jdbc.exc
--- The error occurred in RemoteDatabaseMap.xml.
--- The error occurred while applying a parameter map.
--- Check the RemoteDatabaseMap.exportDa
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Server 'IP address': [FreeTDS][SQL Server]Write to the s
erver failed
at org.springframework.jdbc.s
at org.springframework.jdbc.s
ava:322)
at org.springframework.orm.ib
at org.springframework.orm.ib
at com.cisco.nm.acs.view.dbms
at com.cisco.nm.acs.view.dbms
at com.cisco.nm.acs.view.comm
at org.quartz.core.JobRunShel
at org.quartz.simpl.SimpleThr
Caused by: com.ibatis.common.jdbc.exc
--- The error occurred in RemoteDatabaseMap.xml.
--- The error occurred while applying a parameter map.
--- Check the RemoteDatabaseMap.exportDa
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Server 'IP address': [FreeTDS][SQL Server]Write to the s
erver failed
at com.ibatis.sqlmap.engine.m
at com.ibatis.sqlmap.engine.i
at com.ibatis.sqlmap.engine.i
at org.springframework.orm.ib
at org.springframework.orm.ib
... 6 more
Caused by: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Server 'IP address': [FreeTDS][SQL Server]Write to the s
erver failed
at ianywhere.ml.jdbcodbc.IIPr
at ianywhere.ml.jdbcodbc.IPre
at org.apache.commons.dbcp.De
at com.ibatis.sqlmap.engine.e
at com.ibatis.sqlmap.engine.m
at com.ibatis.sqlmap.engine.m
... 10 more
Trying to backup Cisco ACS SE 5.1 internal database to remote SQL server. Only inputs in ACS web GUI which I can insert is:
-server, port, username, password and database type (MS SQL or Oracle) and time period.
Account under which backup is running is DB owner (full rights). SQL database is 2005 (cisco recommendation) and has no instances. Have to say that I'm not SQL expert :)
I can connect to this database with any SQL client so it looks like credentials are ok. When ACS try to make backup I see on MS SQL in Activity monitor that prosess has :
- status "sleeping"
- command "awaiting command"
I don't know if MS SQL can give me some debbug info about the session or any other info so I can determine what's going on on SQL server. Have the followig debug from ACS server. Not sure what it menas:
Apr 04 2011 08:30:00 com.cisco.nm.acs.view.comm
artzScheduler_Worker-8 Acs.MGMT.ACSVIEW Export Data : exception org.springframework.jdbc.U
ent operation; uncategorized SQLException for SQL []; SQL state [HY000]; error code [-660];
--- The error occurred in RemoteDatabaseMap.xml.
--- The error occurred while applying a parameter map.
--- Check the RemoteDatabaseMap.exportDa
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Server 'IP address': [FreeTDS][SQL Server]Write to the s
erver failed; nested exception is com.ibatis.common.jdbc.exc
--- The error occurred in RemoteDatabaseMap.xml.
--- The error occurred while applying a parameter map.
--- Check the RemoteDatabaseMap.exportDa
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Server 'IP address': [FreeTDS][SQL Server]Write to the s
erver failed
at org.springframework.jdbc.s
at org.springframework.jdbc.s
ava:322)
at org.springframework.orm.ib
at org.springframework.orm.ib
at com.cisco.nm.acs.view.dbms
at com.cisco.nm.acs.view.dbms
at com.cisco.nm.acs.view.comm
at org.quartz.core.JobRunShel
at org.quartz.simpl.SimpleThr
Caused by: com.ibatis.common.jdbc.exc
--- The error occurred in RemoteDatabaseMap.xml.
--- The error occurred while applying a parameter map.
--- Check the RemoteDatabaseMap.exportDa
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Server 'IP address': [FreeTDS][SQL Server]Write to the s
erver failed
at com.ibatis.sqlmap.engine.m
at com.ibatis.sqlmap.engine.i
at com.ibatis.sqlmap.engine.i
at org.springframework.orm.ib
at org.springframework.orm.ib
... 6 more
Caused by: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Server 'IP address': [FreeTDS][SQL Server]Write to the s
erver failed
at ianywhere.ml.jdbcodbc.IIPr
at ianywhere.ml.jdbcodbc.IPre
at org.apache.commons.dbcp.De
at com.ibatis.sqlmap.engine.e
at com.ibatis.sqlmap.engine.m
at com.ibatis.sqlmap.engine.m
... 10 more
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> The problem is somewhere on SQL.
I would have agreed if you are not able to connect to SQL Server via SSMS..
Since you are able to connect successfully, I am doubting ACS
>> I have changed database collation from SQL Latin to no SQL and it's working. But onather problem occured :)
Changing a database collation needs to be planned and implemented carefully.
Can you kindly let me know what exactly you tried so that I can get some clues on why it started failing after that..
I would have agreed if you are not able to connect to SQL Server via SSMS..
Since you are able to connect successfully, I am doubting ACS
>> I have changed database collation from SQL Latin to no SQL and it's working. But onather problem occured :)
Changing a database collation needs to be planned and implemented carefully.
Can you kindly let me know what exactly you tried so that I can get some clues on why it started failing after that..
ASKER
>> I would have agreed if you are not able to connect to SQL Server via SSMS..
Since you are able to connect successfully, I am doubting ACS
But SSMS uses different ODBC driver as ACS (linux). Here can be problem with implementation.
>> Changing a database collation needs to be planned and implemented carefully.
I have my own SQL database for testing this scenario at the moment. Have googled the error message from ACS (SQL state [HY000]) and found on SQL forum that collation have to be changed. It has something to do with char data type.
Everything that I have tried is written in this article.
Since you are able to connect successfully, I am doubting ACS
But SSMS uses different ODBC driver as ACS (linux). Here can be problem with implementation.
>> Changing a database collation needs to be planned and implemented carefully.
I have my own SQL database for testing this scenario at the moment. Have googled the error message from ACS (SQL state [HY000]) and found on SQL forum that collation have to be changed. It has something to do with char data type.
Everything that I have tried is written in this article.
>> Everything that I have tried is written in this article.
Seems like you have missed posting the article link..
Can you kindly post that link here so that I can understand what ACS has to do with SQL Server for the problem you have encountered..
Seems like you have missed posting the article link..
Can you kindly post that link here so that I can understand what ACS has to do with SQL Server for the problem you have encountered..
ASKER
http://www.cisco.com/en/US/docs/net_mgmt/cisco_secure_access_control_system/5.1/user/guide/viewer_sys_ops.html#wp1059460
My scenario is described in firt post ID:26931183 at the top. There is nothing special what I can set in ACS for remote database logging.
My scenario is described in firt post ID:26931183 at the top. There is nothing special what I can set in ACS for remote database logging.
The link which you have posted above didn't say anything about changing Server/database collation which is what I am interested on knowing about as you mentioned earlier that you started facing issues after collation change..
Kindly clarify..
Kindly clarify..
ASKER
Will ask SQL guy tomorrow. Can you tell me where else could be the problem? It looks like the backup session can not be terminated. Some data are sent to the database from ACS but not everytime. I can not force ACS to send data to SQL on demand. The lowest time period is 1 hour so it's not easy to laborate for me :)
I can see in SQL Profiler that:
- backup takes from 2 to 5 seconds
- SQL is doing backup until the value count is more than zero (@count>0). I'm not sure what exactly is this value but it decreases.
- session is not terminated
- backup process is in sleeping state
If I don't kill the sleeping session manually the next backup will fail.
I can see in SQL Profiler that:
- backup takes from 2 to 5 seconds
- SQL is doing backup until the value count is more than zero (@count>0). I'm not sure what exactly is this value but it decreases.
- session is not terminated
- backup process is in sleeping state
If I don't kill the sleeping session manually the next backup will fail.
>> It looks like the backup session can not be terminated.
What error you are obtaining when you are trying to terminate the session.
Are you trying to do it from ACS or through SQL Server Activity Monitor.
>> Some data are sent to the database from ACS but not everytime.
Kindly let me know what data it is so that I can explain it in more detail.
>> backup takes from 2 to 5 seconds
What is the size of your database since it completes in 2 to 5 seconds. Hope you know how to find database size in SSMS or let me know the backup file size(*.bak file)
>> SQL is doing backup until the value count is more than zero (@count>0).
Seems like to be a logic internally maintained by ACS to do the backup activity.
>> backup process is in sleeping state
If I don't kill the sleeping session manually the next backup will fail.
Then either the session is not closed properly from ACS or connections are not properly managed.
What error you are obtaining when you are trying to terminate the session.
Are you trying to do it from ACS or through SQL Server Activity Monitor.
>> Some data are sent to the database from ACS but not everytime.
Kindly let me know what data it is so that I can explain it in more detail.
>> backup takes from 2 to 5 seconds
What is the size of your database since it completes in 2 to 5 seconds. Hope you know how to find database size in SSMS or let me know the backup file size(*.bak file)
>> SQL is doing backup until the value count is more than zero (@count>0).
Seems like to be a logic internally maintained by ACS to do the backup activity.
>> backup process is in sleeping state
If I don't kill the sleeping session manually the next backup will fail.
Then either the session is not closed properly from ACS or connections are not properly managed.
ASKER
Will write back to you on friday because I'm out of office.
ASKER
>> What error you are obtaining when you are trying to terminate the session. Are you trying to do it from ACS or through SQL Server Activity Monitor.
I kill the sleeping process through SSMS.
In SQL Profilerthere is foloowing log:
EventClass > SQL:BatchStarting
DELETE FROM "acs".."nadaaastatus" FROM "acs".."nadaaastatus" t1 WHERE t1."Timestamp" >= '2011-04-04 17:30:00.000' AND t1."Timestamp" <= '2011-04-05 18:30:00.000' `
This is backup log from SQL profiler at 18:30. I'm not sure what this query means and why is this happening. What is t1 ? Alis or what? Data between this time period is stored in database so I'm not sure why he is trying to delete this tables because data are stored in database event if this query is executed. When I change time period for backup on ACS to 2 hours he will check in SQL query in 2 hours perion.
Now if I don't get EventClass > SQL:BatchCompleted the process hangs up.
The backup runs for ferw hours and then it crashes. Sometimes after 8 hours. sometimes after 2 hours. Now I'm really confused where is the problem :)
I kill the sleeping process through SSMS.
In SQL Profilerthere is foloowing log:
EventClass > SQL:BatchStarting
DELETE FROM "acs".."nadaaastatus" FROM "acs".."nadaaastatus" t1 WHERE t1."Timestamp" >= '2011-04-04 17:30:00.000' AND t1."Timestamp" <= '2011-04-05 18:30:00.000' `
This is backup log from SQL profiler at 18:30. I'm not sure what this query means and why is this happening. What is t1 ? Alis or what? Data between this time period is stored in database so I'm not sure why he is trying to delete this tables because data are stored in database event if this query is executed. When I change time period for backup on ACS to 2 hours he will check in SQL query in 2 hours perion.
Now if I don't get EventClass > SQL:BatchCompleted the process hangs up.
The backup runs for ferw hours and then it crashes. Sometimes after 8 hours. sometimes after 2 hours. Now I'm really confused where is the problem :)
>> Now if I don't get EventClass > SQL:BatchCompleted the process hangs up
Yes, I got it..
Seems like someone is trying to execute this statement in your database:
DELETE FROM "acs".."nadaaastatus" FROM "acs".."nadaaastatus" t1 WHERE t1."Timestamp" >= '2011-04-04 17:30:00.000' AND t1."Timestamp" <= '2011-04-05 18:30:00.000'
Since DELETE statements places an exclusive lock in your database, your backup statements are getting delayed..
And regarding the DELETE statement, they are trying to delete all records between the time '2011-04-04 17:30:00.000' and '2011-04-05 18:30:00.000'
Yes, I got it..
Seems like someone is trying to execute this statement in your database:
DELETE FROM "acs".."nadaaastatus" FROM "acs".."nadaaastatus" t1 WHERE t1."Timestamp" >= '2011-04-04 17:30:00.000' AND t1."Timestamp" <= '2011-04-05 18:30:00.000'
Since DELETE statements places an exclusive lock in your database, your backup statements are getting delayed..
And regarding the DELETE statement, they are trying to delete all records between the time '2011-04-04 17:30:00.000' and '2011-04-05 18:30:00.000'
ASKER
How to solve this? Why is this delete query executed?
Can you check that from the Profiler trace because the profiler event will have all those values like Application name and user name who executed that query..
Workaround:
If that query is being run at all the times, then we can schedule ACS backup to run at some other point of time.
Workaround:
If that query is being run at all the times, then we can schedule ACS backup to run at some other point of time.
ASKER
Do not understand. What shall I do?
ASKER
I know all attributes. Whichone you need? This query is executed by ACS and I can't modify it.
>> This query is executed by ACS and I can't modify it.
If it is from ACS, then we can't modify the query but we can delete records earlier from the table acs..nadaaastatus manually so that the DELETE won't take longer time and ACS backup will complete sooner..
And in the meanwhile, can you kindly check the no. of records present in your table acs..nadaaastatus by running this query:
SELECT count(*)
FROM acs..nadaaastatus
If it is from ACS, then we can't modify the query but we can delete records earlier from the table acs..nadaaastatus manually so that the DELETE won't take longer time and ACS backup will complete sooner..
And in the meanwhile, can you kindly check the no. of records present in your table acs..nadaaastatus by running this query:
SELECT count(*)
FROM acs..nadaaastatus
ASKER
acs..nadaaastatus is just one of approx. 50 tabes in database. Query like this one is executed on every table every time that backup is in proccess (every hour). It will hang on table with latest record. In this example acs..nadaaastatus. So manually deleting records every time is not good :)
And let mey ask again. What is the t1 parameter?
DELETE FROM "acs".."nadaaastatus" FROM "acs".."nadaaastatus" t1 WHERE t1."Timestamp" >= '2011-04-04 17:30:00.000' AND t1."Timestamp" <= '2011-04-05 18:30:00.000'
And let mey ask again. What is the t1 parameter?
DELETE FROM "acs".."nadaaastatus" FROM "acs".."nadaaastatus" t1 WHERE t1."Timestamp" >= '2011-04-04 17:30:00.000' AND t1."Timestamp" <= '2011-04-05 18:30:00.000'
>> And let mey ask again. What is the t1 parameter?
Missed replying in the earlier comment.
t1 is the alias name assigned to your table acs..nadaaastatus
Missed replying in the earlier comment.
t1 is the alias name assigned to your table acs..nadaaastatus
ASKER
And why deleting this data?
I'm not sure on why ACS is trying to delete data from your table..
I believe nadaaastatus table would have been created by ACS itself and some internal logics are done over there..
I believe nadaaastatus table would have been created by ACS itself and some internal logics are done over there..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Welcome and glad to see you have it resolved..
ASKER
Solved by my self with help of rrjegan17.
ASKER
Exception >> Creating a job::: Another Export Job Instance is runnng.
Kindly run SQL Server Profiler when ACS SE 5.1 is trying to connect to SQL Server for backup to see what script is exactly run on SQL Server machine..
I'm not a SQL guy. Cisco is my homesite :) Will post your comment to SQL admins.