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.common.remotedatabase.ExportDataJob.execute(ExportDataJob.java:91) ERROR DefaultQu
artzScheduler_Worker-8 Acs.MGMT.ACSVIEW Export Data : exception org.springframework.jdbc.UncategorizedSQLException: SqlMapCli
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.exportDataToRemoteDB-InlineParameterMap.
--- 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.exception.NestedSQLException:
--- The error occurred in RemoteDatabaseMap.xml.
--- The error occurred while applying a parameter map.
--- Check the RemoteDatabaseMap.exportDataToRemoteDB-InlineParameterMap.
--- 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.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:121)
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.j
ava:322)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.insert(SqlMapClientTemplate.java:397)
        at com.cisco.nm.acs.view.dbms.impl.RemoteDatabaseDAOImpl.exportTables(RemoteDatabaseDAOImpl.java:458)
        at com.cisco.nm.acs.view.dbms.impl.RemoteDatabaseDAOImpl.exportDataToRemoteDatabase(RemoteDatabaseDAOImpl.java:203)
        at com.cisco.nm.acs.view.common.remotedatabase.ExportDataJob.execute(ExportDataJob.java:84)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in RemoteDatabaseMap.xml.
--- The error occurred while applying a parameter map.
--- Check the RemoteDatabaseMap.exportDataToRemoteDB-InlineParameterMap.
--- 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.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:447)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:82)
        at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:399)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
        ... 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.IIPreparedStatement.execute(Native Method)
        at ianywhere.ml.jdbcodbc.IPreparedStatement.execute(IPreparedStatement.java:243)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
        at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:81)
        at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
        at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
        ... 10 more
LVL 4
KendzastAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> uncategorized SQLException for SQL []; SQL state [HY000]; error code [-660];
>> Server 'IP address': [FreeTDS][SQL Server]Write to the server failed

Above error indicates that error is not generated or caused by SQL Server.
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..
If you have any patches / updates for ACS SE 5.1, then apply those and try once..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KendzastAuthor Commented:
Latest patch is already applyed. The problem is somewhere on SQL. I have changed database collation from SQL Latin to no SQL and it's working. But onather problem occured :) The backup session hangs up (sleeping). Not all data from local database were tranfered to SQL and have to kill manually session. I have scheduled backup to 1 hour and have following error on ACS.

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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

KendzastAuthor Commented:
>> 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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
0
KendzastAuthor Commented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
0
KendzastAuthor Commented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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.
0
KendzastAuthor Commented:
Will write back to you on friday because I'm out  of office.
0
KendzastAuthor Commented:
>> 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 :)
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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'
0
KendzastAuthor Commented:
How to solve this? Why is this delete query executed?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
KendzastAuthor Commented:
Do not understand. What shall I do?
0
KendzastAuthor Commented:
I know all attributes. Whichone you need? This query is executed by ACS and I can't modify it.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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
0
KendzastAuthor Commented:
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'
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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
0
KendzastAuthor Commented:
And why deleting this data?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
0
KendzastAuthor Commented:
No. I created the tables. Downloaded file from ACS where was complete query for creating tables and executed it on SQL.
Finally solved the problem. In ACS I could define just 5 fields (define remote database) to export data. Couldn't change the query. I thought that problem must be somewhere on SQL server. At the begginig I had the SQL server on virtual Xen server. Yesterday I moved it to DL380 because I thought there could be the problem - fail. Patched MS SQL 2005 -fail. Moved to SQL 2008 - fail. Have looked couple times complete query in SQL Profiler but couldn't understand why sometimes the "BatchJob" wasn't completed.
When the backup process failed I saw in ACS Logs  Exception >> Creating a job::: Another Export Job Instance is runnng. Thought it was caused by SQL hanging job. It was true. Another export job instance was running :) Prewiously I was backing up logs with CIFS remote share and FTP. Full backup every month and incremental every day at midnight. So the backup failed everyday at midnight and that's thy the backup process hangs up on SQL. 2 backup jobs at the same time. Thought that ACS is a little bit smarter when using different methods for backup. The sollution is to use just one backup method.
Thanks for your help and time rrjegan17. SQL Profiler is a cool thig.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome and glad to see you have it resolved..
0
KendzastAuthor Commented:
Solved by my self with help of rrjegan17.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.