I got some very weird behavior of our faultline database, so hopefully someone here knows what could cause havoc on our database, whenever I try to import FASL scripts. I tried 2 methods to reach my goal (inserting more than ten custom FASL scripts) and came to a point where I think that the database is the problem.
OS: Windows 2008 R2 Standard 64-bit
RAM: 4 GB
CPU: 2x3.00 GHz
SQL Server 2008 R2, ServicePack 2
Installed Version 7.5
Installed FASL-Support with the FSUpdate tool
Import and publish around 400 FASL-Scripts to the MVM database and assign them to a vulnerability by web-interface.
Import well-formed FASL-Files with the utility program FSDBUtil.
FSDBUtil tended to fail in certain cases (although the imported FASL-scripts were well-formed) with a “Database Request Timeout” after around 40 seconds.
Write SQL-Scripts using the predefined stored procedures “Content.Script_Save”, “Content.Vuln_Save” and “Content.Script_Publish”.
FSDBUtil (Method1 ) tends to fail in certain cases (although the imported FASL-scripts were well-formed) with a “Database Request Timeout” after around 40 seconds. Usually this behavior occurs after successfully importing around 10 FASL-scripts. Restarting the MSSQL database service did not fix the problem. I thought maybe FSDBUtil is the problem, which led me to write my own SQL-Scripts (Method 2).
I attached the SQL-scripts for better understanding. During testing and debugging the code, I stumbled upon very strange behavior of the faultline-database, which probably cause problems for FSDBUtil in the first place.
In save_test.sql, I followed following workflow:
1) Collect predefined values as Admin-ID, VulnCategoryID etc.
2) Generate a Vulnerability-dataset (Content.Vuln) for the metadata by using the procedure Content.Vuln_Save (and remember the generated faultlineID)
3) Create a ScriptInDev-Dataset (Content.ScriptInDev) by using the procedure Content.Script_Save (using the generated faultlineID from step 2)
4) Publish the script (and ultimately write the dataset to Content.Script) by executing the procedure Content.Script_Publish with the mentioned faultineID and the predefined Admin-ID
5) Select the newly generated data in Content.ScriptInDev, Content.Script and Content.Vuln
As far as I know, launching this SQL-Script for around 10 times works fine. All datasets were generated from faultlineID 6000001 to 6000012 until the execution time skyrocketed to around 40 – 50 minutes for future executions. Debugging showed that Content.Script_Publish caused high latencies by executing following SQL-Statement (Line 76 in Content.Script_Publish):
, @Now --CreatedDate
, @Now --ModifiedDate
WHERE FaultlineID = @FaultlineID
It seems that an INSERT statement targeting the Content.Script table cause high execution times. Also I realized that a DELETE statement took the same amount of time as soon as there are more than 10 custom fasl scripts in this table (DELETEFROM Content.Script WHERE ScriptID >= 6000000; ). Content.Script contains around 50019 datasets.
I assume that there is some internal mechanism (index, etc.) preventing the database to efficiently inserting and deleting data from Content.Script... SELECT statements work perfectly fine. What would be a workaround to get the FSDBUtil / SQL-Scripts working in a reasonable execution time?
Don't forget, when your helpful posts earn a kudos or get accepted as a solution you can unlock perks and badges. Those aren't the only badges, either. How many can you collect? Click here to learn more.
Community Help Hub
New to the forums or need help finding your way around the forums? There's a whole hub of community resources to help you.