Transitioning from the CAST AIP MS SQL Server Analyzer to the SQL Analyzer extension

Summary: This page provides instructions for those that would like transition from using the “out-of-the box” MS SQL Server Analyzer provided in CAST AIP to the SQL Analyzer extension. 

(warning)Important !

  • Do not change analyzed sources, keep analyzing src files.
  • GUID of equivalent objects are migrated during transition.
  • Checksum are not migrated and that will impact transactions.  When you have multiple MS SQL Server analysis units (extractions) in the same application, you should merge them in a single one!

A new report is generated during Run extensions after snapshot step, only for CAIP >= 8.3.

Read more about equivalent objects/links/rules at: SQL Analyzer - Mapping between SQL analyzers.

Introduction

If you have been actively analyzing MS SQL Server files using MS SQL Server Analyzer (provided out-of-the-box in CAST AIP) you can do transition using the SQL Analyzer extension to analyze your MS SQL Server source code. The process of transition is described in this page. 

Step by step transition

Step 1: Backup CAST AIP triplet, Delivery/Deployment folders

To backup the CAST AIP schemas use the CSSBackup tool (located in the CSSAdmin folder in the CAST AIP installation folder) use the following scripts adapted to your environment.

Example

Backup CAST AIP schemas

CSSBackup.exe -schema msqlserver_local -password CastAIP -file c:\temp\MSSQLServer\msqlserver_local.cssdmp -log c:\temp\MSSQLServer\msqlserver_local.log
CSSBackup.exe -schema msqlserver_central -password CastAIP -file c:\temp\MSSQLServer\msqlserver_central.cssdmp -log c:\temp\MSSQLServer\msqlserver_central.log
CSSBackup.exe -schema msqlserver_measure -password CastAIP -file c:\temp\MSSQLServer\msqlserver_measure.cssdmp -log c:\temp\MSSQLServer\msqlserver_measure.log
CSSBackup.exe -schema msqlserver_mngt -password CastAIP -file c:\temp\MSSQLServer\msqlserver_mngt.cssdmp -log c:\temp\MSSQLServer\msqlserver_mngt.log

Then  ZIP the Delivery and Deployment folders for easy restoration.

Step 2: Install SQL Analyzer 3.4.0-funcrel and then load Assessment Model

Install SQL Analyzer 3.4.0-funcrel.  If you expect to have Access link, please install latest version of datacolumnaccess extension, at the same time when you install SQL Analyzer.

And then load Assessment Model, e.g.: 

Step 3: Add a new UA SQL AU, on exactly the same sources

Add a new package on the same sources. You should clone the sources, replace the MS SQL Server Package with a Files based package, on the cloned sources. Deliver and Deploy your new package. Add a new UA, and choose SQL for the AU.

Add dependencies between your new AU and client AU, if needed.

Migration log

The transition is executed during the Before Application Step, and is logged in BeforeApplicationExtensions castlog file. See attached example BeforeApplicationExtensions.castlog{.unresolved}.

Step by step example - In Progress

  • Copy MS SQL Server previous deployment folder

  • Add new version

  • Copy source code and update name and Release Date

  • Remove MS SQL Server Package

  • Add new Package

  • Paste the MS SQL Server previous copied deployment folder in the Root folder field

  • Then Deliver and Quit

  • Accept Delivery and use it as current version

  • Add new Universal Analysis Unit, then, choose SQL ans Universal language and add sources

  • Change dependency, for C/S applications

Dependencies example when application have more than one technology

How dependencies looks like for PL/SQL : And here is how it looks when move to SQL :

Step 4 : Take a new snapshot and check results

Take a new snapshot and then check results in AED. Refert to SQL Analyzer - Mapping between SQL analyzers#Rules, to understand variations on equivalent rules.

Step by step example

  • Take a new snapshot

  • Check transition in the file BeforeApplicationExtensions castlog. Please note this step could take time, from seconds to half of hour, depending on number of objects you have in the previous analysis result.

See attached BeforeApplicationExtensions castlog file for the example below : BeforeApplicationExtensions.castlog.

  • Check the Metrics Report, generated after the snapshot

After the snapshot, in the Run extensions after snapshot step, a csv report will be systematically generated, please have a look in the attached example: SQLAnalyzerMetricsReport.csv, and the log file: AfterSnapshotExtensions.castlog.  You will see: the number of the previous metric ID and the current one, the name of the current metric and also the number of violations and  the total of scanned objects before and now. 

The report covers all metrics, not only the SQL metrics. And it is generated in LISA folder.

Why we have 2 Metrics Ids in report ?

Sometimes the same Metric is implemented on both analyzers, but we may have different IDs or None. 

PreviousMetricId at None means the Metric is a new one and exists only on SQL Analyzer.

CurrentMetricId at None means the Metric exists only on MS SQL Server Analyzer.

When PreviousMetricId is the exacly the same as CurrentMetricId that means we have exactly the same Metric on the both analyzers, otherwise we have an equivalent Metric on SQL Analyzer.

Example:

PreviousMetricId;CurrentMetricId;CurrentMetricName;PreviousResult[NumberOfViolations, Total];CurrentResult[NumberOfViolations, Total]None;1101026;‘Always define column names when inserting values’;None;[2.0, 40.0]
7344;7344;‘Avoid “SELECT *” queries’;[2.0, 44.0];[0.0, 43.0]
7766;7766;‘Avoid Artifacts with High Cyclomatic Complexity’;[0.0, 44.0];[0.0, 43.0]
7768;7768;‘Avoid Artifacts with High Depth of Code’;[0.0, 44.0];[0.0, 43.0]
7130;7130;‘Avoid Artifacts with High Depth of Nested Subqueries’;[0.0, 44.0];[0.0, 43.0]
7772;7772;‘Avoid Artifacts with High Essential Complexity’;[3.0, 44.0];[3.0, 43.0]
7776;7776;‘Avoid Artifacts with High Fan-In’;[0.0, 44.0];[0.0, 43.0]
7778;7778;‘Avoid Artifacts with High Fan-Out’;[0.0, 44.0];[0.0, 43.0]
7774;7774;‘Avoid Artifacts with High Integration Complexity’;[0.0, 44.0];[0.0, 43.0]
7828;7828;‘Avoid Artifacts with High RAW SQL Complexity’;[0.0, 44.0];[0.0, 43.0]
7808;7808;‘Avoid Artifacts with SQL statement including subqueries’;[0.0, 44.0];[0.0, 43.0]
7810;None;‘Avoid Artifacts with a Complex SELECT Clause’;[2.0, 41.0];None
7126;7126;‘Avoid Artifacts with high Commented-out Code Lines/Code Lines ratio’;[0.0, 44.0];[0.0, 43.0]
7784;7784;‘Avoid Artifacts with lines longer than X characters’;[1.0, 44.0];[0.0, 43.0]
7822;1101030;‘Avoid Artifacts with queries on too many Tables and/or Views’;[0.0, 41.0];[0.0, 43.0]
7770;1101016;‘Avoid Artifacts with too many parameters (SQL)’;[0.0, 24.0];[0.0, 43.0]
7790;1101084;‘Avoid Cursors inside a loop (SQL)’;[0.0, 41.0];[0.0, 4.0]
4062;None;‘Avoid Functions and Procedures doing an Insert, Update or Delete without managing a transaction’;[7.0, 36.0];None
None;1101010;‘Avoid NATURAL JOIN queries’;None;[0.0, 43.0]
7420;None;‘Avoid SQL queries with implicit conversions in the WHERE clause’;[0.0, 38.0];None
7814;7814;‘Avoid Tables not using referential integrity’;[0.0, 15.0];[0.0, 15.0]
7856;7856;‘Avoid Tables with more than 20 columns on an OLTP system’;[0.0, 15.0];[0.0, 15.0]
8082;1101022;‘Avoid Tables without Primary Key (SQL)’;[11.0, 15.0];[11.0, 15.0]
7156;7156;‘Avoid Too Many Copy Pasted Artifacts’;[5.0, 36.0];[4.0, 36.0]
7388;7388;‘Avoid artifacts having recursive calls’;[0.0, 41.0];[0.0, 40.0]
None;1101064;‘Avoid cascading Triggers (SQL)’;None;[1.0, 4.0]
None;1101040;‘Avoid empty catch blocks (SQL)’;None;[0.0, 40.0]
8080;1101032;‘Avoid exists and not exists independent clauses (SQL)’;[0.0, 44.0];[0.0, 43.0]
None;1101070;‘Avoid explicit comparison with NULL’;None;[0.0, 43.0]
7390;7390;‘Avoid having multiple Artifacts inserting data on the same SQL Table’;[2.0, 15.0];[2.0, 15.0]
7394;7394;‘Avoid having multiple Artifacts updating data on the same SQL Table’;[0.0, 15.0];[0.0, 15.0]
7392;7392;‘Avoid having multiple artifacts deleting data on the same SQL table’;[2.0, 15.0];[2.0, 15.0]
7842;7842;‘Avoid large Artifacts - too many Lines of Code’;[0.0, 44.0];[3.0, 43.0]
None;1101056;‘Avoid large Tables - too many columns (SQL)’;None;[0.0, 15.0]
7786;7786;‘Avoid long Table or View names’;[1.0, 18.0];[1.0, 18.0]
4084;None;‘Avoid nested Stored Procedures using temporary Tables’;[1.0, 36.0];None
None;1101008;‘Avoid non-SARGable queries’;None;[3.0, 43.0]
7418;1101004;‘Avoid non-indexed SQL queries’;[2.0, 38.0];[6.0, 43.0]
7946;1101014;‘Avoid queries using old style join convention instead of ANSI-Standard joins (SQL)’;[7.0, 44.0];[10.0, 43.0]
4056;None;‘Avoid recursive Triggers’;[0.0, 4.0];None
7346;7346;‘Avoid redundant indexes’;[1.0, 15.0];[1.0, 15.0]
7348;7348;‘Avoid too many Indexes on one Table’;[0.0, 15.0];[0.0, 15.0]
7760;7760;‘Avoid triggers, functions and procedures with a very low comment/code ratio’;[7.0, 40.0];[7.0, 40.0]
7762;7762;‘Avoid undocumented Triggers, Functions and Procedures’;[7.0, 40.0];[7.0, 40.0]
7860;7860;‘Avoid unreferenced Functions’;[21.0, 36.0];[21.0, 36.0]
1634;1634;‘Avoid unreferenced Tables’;[1.0, 15.0];[0.0, 15.0]
7404;7404;‘Avoid unreferenced views’;[3.0, 3.0];[3.0, 3.0]
4070;None;‘Avoid use of “truncate table”’;[0.0, 44.0];None
7816;7816;‘Avoid using GOTO statement’;[32.0, 40.0];[16.0, 40.0]
7424;7424;‘Avoid using SQL queries inside a loop’;[4.0, 41.0];[0.0, 40.0]
None;1101024;‘Avoid using dynamic SQL in SQL Artifacts’;None;[0.0, 40.0]
None;1101020;‘Avoid using quoted identifiers’;None;[0.0, 40.0]
4076;None;‘Avoid using temporary Objects’;[3.0, 43.0];None
7806;1101018;‘Avoid using the GROUP BY clause’;[0.0, 41.0];[0.0, 43.0]
None;1101076;‘Column references should be qualified’;None;[2.0, 43.0]
None;1101034;‘DISTINCT should not be used in SQL SELECT statements’;None;[1.0, 43.0]
None;1101058;‘Do not mix ANSI and non-ANSI JOIN syntax in the same query’;None;[0.0, 10.0]
7386;1101090;‘Each table should have a clustered index’;[14.0, 15.0];[14.0, 15.0]
4064;1101092;‘Errors should be handled in stored procedures and functions’;[21.0, 36.0];[21.0, 36.0]
None;1101060;‘LIKE operator should not start with a wildcard character’;None;[0.0, 4.0]
7820;1101000;‘Never use SQL queries with a cartesian product (SQL)’;[0.0, 38.0];[0.0, 43.0]
None;1101038;‘Replace OR conditions testing equality on the same identifier in SQL WHERE clauses by an IN test condition’;None;[0.0, 43.0]
None;1101012;‘Specify column names instead of column numbers in ORDER BY clauses’;None;[0.0, 43.0]
4066;1101094;‘Stored Procedures should always RETURN a status value’;[12.0, 36.0];[12.0, 36.0]
None;1101042;‘Table naming convention - character set control (SQL)’;None;[0.0, 15.0]
None;1101074;‘Tables aliases should not end with a numeric suffix’;None;[5.0, 17.0]
None;1101072;‘Tables should be aliased’;None;[3.0, 10.0]
None;1606;‘Triggers should not directly modify tables, a procedure or function should be used instead’;None;[1.0, 4.0]
None;1101028;‘Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries’;None;[0.0, 43.0]
None;1101062;‘Use at most one statement per line (SQL)’;None;[0.0, 40.0]
None;1101044;‘View naming convention - character set control (SQL)’;None;[0.0, 3.0]