September 4, 2012

Example of an optimized nested SELECT statements to CASE statements in SQL (using SQLite)

Filed under: sql, SQLite — lancevermilion @ 6:26 pm

I had written a webpage that needed to do a single SQL query and return the information. The information had a key column named ‘AssetnameFindingDetailURL’ and computations needed to be done based on a combination of a few other columns ‘StatusDisplayOverride’ and ‘Severity’. To achieve this I originally just did a HUGE (1110 lines) SQL with nested SELECT statements. This proved to be very slow and almost impossible to understand. I decided it was possible to shorten it so i spent about 1 hour having a go at using a CASE statement. The results were quite good. I shortened the execution time from .4 of a second down to .1 of a second and from 1110 lines of SQL to 110 lines of SQL. I am sure this can be shortened even more but it is beyond my interest at the moment to make it shorter without any procedures/etc. Hopefully someone will find this usefule.

How the Syntax changed

ROUND
( 
  (
    (
      (
        SELECT 
          COUNT(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') 
      )*100.0
    )/COUNT(severity)
  ),2
) AS 'Total_%_Done'

Was turned into

ROUND
(
 COUNT
  (
    CASE 
      WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') THEN 
        Severity 
    END
  )*100.00 / COUNT(Severity), 2 
) AS CatAPercent,

Example output

A|54.48|525|286|100.0|64|64|0|0|29|35|53.21|389|207|47|128|60|147|21.43|70|15|36|18|12|3|0.0|2|0|2|0|0|0
B|73.91|23|17|100.0|3|3|0|0|3|0|76.92|13|10|0|2|10|0|57.14|7|4|3|0|4|0||0|0|0|0|0|0
C|41.67|12|5|100.0|5|5|0|0|5|0|0.0|3|0|3|0|0|0|0.0|3|0|3|0|0|0|0.0|1|0|1|0|0|0
D|12.5|16|2|100.0|1|1|0|0|1|0|0.0|6|0|6|0|0|0|12.5|8|1|7|0|1|0|0.0|1|0|1|0|0|0
E|87.5|8|7|100.0|1|1|0|0|1|0|85.71|7|6|0|1|6|0||0|0|0|0|0|0||0|0|0|0|0|0
F|12.5|8|1|100.0|1|1|0|0|1|0|0.0|4|0|4|0|0|0|0.0|3|0|3|0|0|0||0|0|0|0|0|0
G|100.0|13|13|100.0|2|2|0|0|2|0|100.0|9|9|0|0|9|0|100.0|2|2|0|0|2|0||0|0|0|0|0|0
H|81.4|43|35|100.0|12|12|0|0|4|8|81.48|27|22|0|5|17|5|25.0|4|1|2|1|1|0||0|0|0|0|0|0
I|37.5|48|18|100.0|11|11|0|0|3|8|25.0|28|7|18|3|2|5|0.0|9|0|9|0|0|0||0|0|0|0|0|0
J|54.81|312|171|100.0|18|18|0|0|6|12|54.28|269|146|0|117|16|130|28.0|25|7|0|17|4|3||0|0|0|0|0|0
K|40.48|42|17|100.0|10|10|0|0|3|7|30.43|23|7|16|0|0|7|0.0|9|0|9|0|0|0||0|0|0|0|0|0

Here is the Short version of the SQL (CASE statement).

SELECT
  'ALL DEVICES',
  ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') THEN Severity END)*100.00 / COUNT(Severity), 2 ) AS CatAPercent,
  COUNT(Severity) AS CatAT,
  COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted') THEN Severity END ) AS CatAD,
  ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='1-Category I' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ), 2 ) AS Cat1Percent,
  COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ) AS Cat1T,
  COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted') AND Severity='1-Category I' THEN Severity END ) AS Cat1D,
  COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='1-Category I' THEN Severity END ) AS Cat1O,
  COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='1-Category I' THEN Severity END ) AS Cat1PR,
  COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1TBS,
  COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1S,
  ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='2-Category II' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ), 2 ) AS Cat2Percent,
  COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ) AS Cat2T,
  COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='2-Category II' THEN Severity END ) AS Cat2D,
  COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='2-Category II' THEN Severity END ) AS Cat2O,
  COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='2-Category II' THEN Severity END ) AS Cat2PR,
  COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2TBS,
  COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2S,
  ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='3-Category III' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ), 2 ) AS Cat3Percent,
  COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ) AS Cat3T,
  COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='3-Category III' THEN Severity END ) AS Cat3D,
  COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='3-Category III' THEN Severity END ) AS Cat3O,
  COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='3-Category III' THEN Severity END ) AS Cat3PR,
  COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3TBS,
  COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3S,
  ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='4-Category IV' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ), 2 ) AS Cat4Percent,
  COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ) AS Cat4T,
  COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='4-Category IV' THEN Severity END ) AS Cat4D,
  COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='4-Category IV' THEN Severity END ) AS Cat4O,
  COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='4-Category IV' THEN Severity END ) AS Cat4PR,
  COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4TBS,
  COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4S
FROM
  vms
UNION ALL
SELECT
  UPPER(AssetNameFindingDetailURL) as 'Asset Name',
  CatAPercent,
  CatAT,
  CatAD,
  Cat1Percent,
  Cat1T,
  Cat1D,
  Cat1O,
  Cat1PR,
  Cat1TBS,
  Cat1S,
  Cat2Percent,
  Cat2T,
  Cat2D,
  Cat2O,
  Cat2PR,
  Cat2TBS,
  Cat2S,
  Cat3Percent,
  Cat3T,
  Cat3D,
  Cat3O,
  Cat3PR,
  Cat3TBS,
  Cat3S,
  Cat4Percent,
  Cat4T,
  Cat4D,
  Cat4O,
  Cat4PR,
  Cat4TBS,
  Cat4S
FROM
  (
    SELECT
      AssetNameFindingDetailURL,
      ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') THEN Severity END)*100.00 / COUNT(Severity), 2 ) AS CatAPercent,
      COUNT(Severity) AS CatAT,
      COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted') THEN Severity END ) AS CatAD,
      ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='1-Category I' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ), 2 ) AS Cat1Percent,
      COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ) AS Cat1T,
      COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='1-Category I' THEN Severity END ) AS Cat1D,
      COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='1-Category I' THEN Severity END ) AS Cat1Percent,
      COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='1-Category I' THEN Severity END ) AS Cat1O,
      COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='1-Category I' THEN Severity END ) AS Cat1PR,
      COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1TBS,
      COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1S,
      ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='2-Category II' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ), 2 ) AS Cat2Percent,
      COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ) AS Cat2T,
      COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='2-Category II' THEN Severity END ) AS Cat2D,
      COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='2-Category II' THEN Severity END ) AS Cat2O,
      COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='2-Category II' THEN Severity END ) AS Cat2PR,
      COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2TBS,
      COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2S,
      ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='3-Category III' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ), 2 ) AS Cat3Percent,
      COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ) AS Cat3T,
      COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='3-Category III' THEN Severity END ) AS Cat3D,
      COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='3-Category III' THEN Severity END ) AS Cat3O,
      COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='3-Category III' THEN Severity END ) AS Cat3PR,
      COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3TBS,
      COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3S,
      ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='4-Category IV' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ), 2 ) AS Cat4Percent,
      COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ) AS Cat4T,
      COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='4-Category IV' THEN Severity END ) AS Cat4D,
      COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='4-Category IV' THEN Severity END ) AS Cat4O,
      COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='4-Category IV' THEN Severity END ) AS Cat4PR,
      COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4TBS,
      COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4S
    FROM
      vms
    GROUP BY
      AssetNameFindingDetailURL
  ) AS ABC;

Here is the Long version of the SQL (nested SELECT statements).

SELECT
  '*ALL DEVICES*' as 'Asset Name',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
      )*100.0
    )/count(severity)
  ),2) as 'Total_%_Done',
  count(severity) as Total,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
  ) as 'Total_Done',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '1-Category I'
  )
  ),2) as '%_Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I'
  ) as Total_Cat1s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
  ) as 'Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat1,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '2-Category II'
  )
  ),2) as '%_Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II'
  ) as Total_Cat2s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
  ) as 'Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat2,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '3-Category III'
  )
  ),2) as '%_Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III'
  ) as Total_Cat3s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
  ) as 'Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat3,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '4-Category IV'
  )
  ),2) as '%_Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV'
  ) as Total_Cat4s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
  ) as 'Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat4
FROM
  vms
UNION ALL
SELECT
  UPPER(AssetNameFindingDetailURL) as 'Asset Name',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/count(severity)
  ),2) as 'Total_%_Done',
  count(severity) as Total,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Total_Done',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat1s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat1,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat2s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat2,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat3s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat3,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat4s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat4
FROM
  vms as vw
GROUP BY AssetNameFindingDetailURL): no such column: vv.AssetNameFindingDetailURL

Array
(
    [0] => Array
        (
            [file] => /var/www/html/itdb/php/reports.php
            [line] => 811
            [function] => db_execute
            [args] => Array
                (
                    [0] => PDO Object
                        (
                        )

                    [1] => SELECT
  '*ALL DEVICES*' as 'Asset Name',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
      )*100.0
    )/count(severity)
  ),2) as 'Total_%_Done',
  count(severity) as Total,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
  ) as 'Total_Done',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '1-Category I'
  )
  ),2) as '%_Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I'
  ) as Total_Cat1s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
  ) as 'Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat1,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '2-Category II'
  )
  ),2) as '%_Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II'
  ) as Total_Cat2s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
  ) as 'Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat2,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '3-Category III'
  )
  ),2) as '%_Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III'
  ) as Total_Cat3s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
  ) as 'Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat3,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '4-Category IV'
  )
  ),2) as '%_Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV'
  ) as Total_Cat4s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
  ) as 'Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat4
FROM
  vms
UNION ALL
SELECT
  UPPER(AssetNameFindingDetailURL) as 'Asset Name',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/count(severity)
  ),2) as 'Total_%_Done',
  count(severity) as Total,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Total_Done',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat1s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat1,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat2s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat2,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat3s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat3,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat4s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat4
FROM
  vms as vv
GROUP BY AssetNameFindingDetailURL

October 13, 2011

Perl to Microsoft SQL Server 2008 Standard via ODBC using FreeTDS Drivers

Filed under: microsoft, ODBC, perl, sql — lancevermilion @ 2:30 pm

Perl to Microsoft SQL Server 2008 Standard via ODBC using FreeTDS

Today I had a need to have a perl script (running on CentOS 5.4) connect to a MSSQL Server 2008 Std server so i had to do a little research and playing around. The solution to getting everything working with “Free/OpenSource” stuff was not difficult if you don’t follow everything that everyone publishes.

I have provided the steps I used below to get everything working.

Updated step 5 on Sep 11, 2012 with a note that reflects the findings of user xtruthx.

Pre-requisites

Before you start you need to ensure you know how to satisfy all the RPMs in the pre-requisites.
Location from where you can get the RPMs is listed in parentheses ().

RPMs Needed/used:

perl - 5.8.8-10 (Yum)
perl-DBI - 1.52-1 (Yum)
freetds - 0.64-11 (Yum)
unixODBC-devel - 2.2.11.7.1 (Yum)
unixODBC - 2.2.11.7.1 (Yum)
perl-DBD-ODBC - 1.23-1 (http://pkgs.repoforge.org/perl-DBD-ODBC/perl-DBD-ODBC-1.23-1.el5.rf.i386.rpm)

Steps to get MSSQL,ODBC and Perl working as one happy product.

How did I get ODBC setup?
Install freetds, unixODBC, unixODBC-devel via Yum
```
sudo yum install freetds unixODBC unixODBC-devel
```
Then install the perl, perl-DBI if you don’t have them installed already.
```
sudo yum install perl perl-DBI
```
If you haven’t downloaded the perl-DBD-ODBC module you can do so using wget/curl/etc. I prefer wget.
Then install the perl, perl-DBI if you don’t have them installed already.
```
wget http://pkgs.repoforge.org/perl-DBD-ODBC/perl-DBD-ODBC-1.23-1.el5.rf.i386.rpm
```
Then install the perl-DBD-ODBC module. I am assuming the perl-DBD-ODBC is in the same directory you are working in. If not you will need to provide a complete path to the rpm.
```
sudo rpm -ivh perl-DBD-ODBC
```

DBD::ODBC We used DBD::ODBC. You can use similar methods as above to determine if DBD::ODBC is installed and to see what version you have:To check you have the DBD::ODBC module installed:

perl -e 'use DBD::ODBC;'

If you have not got DBD::ODBC installed you can build it via CPAN or download the RPM and go from there. I just downloaded it, see the RPM list at the top of the article.To show the DBD::ODBC version:

perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'

e.g.

perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
1.23

To show all drivers DBI knows about and their versions:

perl -MDBI -e 'DBI->installed_versions;'

e.g.

perl -MDBI -e 'DBI->installed_versions;'
  Perl            : 5.008008    (i386-linux-thread-multi)
  OS              : linux       (2.6.9-42.0.3.elsmp)
  DBI             : 1.52
  DBD::Sponge     : 11.10
  DBD::Proxy      : install_driver(Proxy) failed: Can't locate RPC/PlClient.pm in @INC
  DBD::ODBC       : 1.23
  DBD::File       : 0.35
  DBD::ExampleP   : 11.12
  DBD::DBM        : 0.03

What ODBC drivers have I got?
You can find out what ODBC drivers are installed under unixODBC with:
```
odbcinst -q -d
```
e.g.
```
odbcinst -q -d
[PostgreSQL]
```
For unixODBC, drivers are installed in the odbcinst.ini file. You can find out which odbcinst.ini file unixODBC is using with:
```
odbcinst -j
```
e.g.
```
odbcinst -j
unixODBC 2.2.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
```
Here, /etc/odbcinst.ini defines the ODBC drivers. Configure unixODBC to use FreeTDS as the Driver for the MSSQL connection.
First we need to locate the driver file.
```
sudo updatedb
locate libtdso
```
e.g.
```
sudo updatedb
locate libtdso
/usr/lib/libtdsodbc.so.0
/usr/lib/libtdsodbc.so.0.0.0
```
Now we know where the source files are so lets create a driver template to use for driver installation.
You can use vi if you want, but to make sure people can copy/paste as much as possible I have provided an echo command below.
Adjust the path and possibly filename for “/usr/lib/libtdsodbc.so.0”.
Note: Only use the driver that has a single 0 if there is more than one returned like my example.
```
echo "[FreeTDS]
Driver = /usr/lib/libtdsodbc.so.0" >> tds.driver.template
```
Now lets install the driver from the template. This will install the driver so anyone can use this driver.
```
sudo odbcinst -i -d -f tds.driver.template
```
e.g.
```
sudo odbcinst -i -d -f tds.driver.template
odbcinst: Driver installed. Usage count increased to 1.
    Target directory is /etc
```
If you check to see what drivers are available to ODBC you will now see “FreeTDS” has been added.
```
odbcinst -q -d
```
e.g.
```
odbcinst -q -d
[PostgreSQL]
[FreeTDS]
```
Configure ODBC with MSSQL parameters.
To do this we need to modify the /etc/odbc.ini file.
Your odbc.ini file could be in a different location. use odbcinst -j to location your ini file.
You need to adjust the values accordingly for the echo command to work for your install.
You will want to be root to do the file modification. I jump to root by way of sudo su -.
Note:You will need a SQL user account setup in MSSQL to allow you to connect remotely with the way I am showing you.Note: A user (xtruthx) has reported that “TDS Version” needed an underscore in order to work for his setup. The user used “TDS_Version = 8.0” instead of what is in the example below.
```
sudo su -
echo "[MSSQL]
Driver = FreeTDS
Address = IPADDRESSOFMSSQL
Port = 1433
TDS Version = 8.0
Database = MYDATABASENAME" >> /etc/odbc.ini
```
e.g.
```
sudo su -
echo "[MSSQL]
Driver = FreeTDS
Address = 192.168.10.1
Port = 1433
TDS Version = 8.0
Database = Northwind" >> /etc/odbc.ini
```

Test the ODBCconnect to your MSSQL database.

isql -v -s MSSQL SQLUSERNAME SQLUSERPASSWORD
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

If the step above was successful then lets try using a Perl script.
Note: Below I specify DSN=MSSQL, that should match the name in [ ] in your odbc.ini file.
You will also want to change SQLUSERNAME and SQLUSERNAMEPASSWORD to your username/password needed to connect to MSSQL using SQL username/password.
This is NOT windows authentication.

#!/usr/bin/perl
use DBI;
use strict;
use DBI;
my @dsns = DBI->data_sources('ODBC');
foreach my $d (@dsns)
{
  print "$d\n";
}
  my $dbh = DBI-> connect('dbi:ODBC:DSN=MSSQL;UID=SQLUSERNAME;PWD=SQLUSERPASSWORD') or die "CONNECT ERROR! :: $DBI::err $DBI::errstr $DBI::state $!\n";
if ($dbh)
{
  print "There is a connection\n";
  my $sql = q/SELECT * FROM dbo.users/;
  my $sth = $dbh->prepare($sql);
  $sth->execute();
  my @row;
  while (@row = $sth->fetchrow_array) {  # retrieve one row at a time
    print join(", ", @row), "\n";
  }
  $dbh->disconnect;
}

External Reference links used to get everything working.

http://www.unixodbc.org/doc/FreeTDS.html
http://www.martin-evans.me.uk/node/20
http://www.easysoft.com/developer/languages/perl/dbi_dbd_odbc.html
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html
http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html
http://www.easysoft.com/developer/languages/perl/tutorial_data_web.html
http://www.easysoft.com/developer/languages/perl/dbi-debugging.html

Comments (10)

August 12, 2011

How to setup MSSQL Server 2008 R2 for High Availability using mirroring.

Filed under: database, microsoft, sql — lancevermilion @ 4:40 pm

Contents

Document Overview: 4

Assumptions. 4

Installing Microsoft MSQQL Server 2008 R2. 5

Background on Database Mirroring. 22

Database Mirroring Overview.. 22

Database mirroring is a simple strategy that offers the following benefits: 22

How Database Mirroring Works. 22

Transaction Safety and Operating Modes. 23

Operating Modes. 23

Transaction Safety. 23

Topology – Synchronous High-Safety Mode without Automatic Failover. 24

Role Switching. 24

Configuration of Database Mirroring. 25

Microsoft SQL Server Management Studio. 25

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}. 27

Restoring a Backup Copy of the Principal Server’s Database on the Mirror Server. 29

Using Microsoft SQL Server Management Studio. 29

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}. 33

Configuring Database Mirroring. 35

Using Microsoft SQL Server Management Studio. 35

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}. 46

Administering MSSQL. 48

Managing Logins and Jobs After Role Switching. 48

Mirroring States. 49

How to Manually Failover the Database between Principal and Mirror. 50

Using Microsoft SQL Server Management Studio. 50

Using Microsoft Transact-SQL (T-SQL). 52

How to: Force Service in a Database Mirroring Session. 53

Using Microsoft Transact-SQL (T-SQL). 54

References: 54

Document Overview:

Microsoft MSSQL Server 2008 R2 can be setup in a High Availability model in a few different ways. The simplest way to this with two server is to use database mirroring. This document will only cover how to setup database mirroring with manual failover in synchronous high-safety mode between two server running Microsoft MSSQL Server 2008 R2.

Assumptions

· A server is already installed with Microsoft Windows Server 2003 or 2007

· Microsoft Server 2008 R2 Software to perform the install.

· The two servers are in a workgroup and NOT a domain.

· The two servers are on the same IP network.

· Remote access has been setup for Administrator.

· Access to the administrator account.

Installing Microsoft MSQQL Server 2008 R2

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

1. Make sure you have Microsoft .NET Framework 3.5 installed before you proceed.

2. Create the Service Accounts that will be used for each MSSQL process.

a. Start->Control Panel->Administrative Tools->Computer Management->Local Users and Groups

b. Use the table below for an example of the user names that need to be created for each SQL Service Account and what check boxes should be checked. You can use different passwords for each or the same one. Make sure you uncheck “User must change password at next logon”.

c. Right click “Users” under “Computer Management (Local)”->”System Tools”->”Local Users and Groups” and left click “New User”.

d. Below is a table that provides some recommend SQL Service Account names and the groups which they should belong to. The assignment to each group will be done during installation. If you don’t do it at installation you can do it afterwards, but it is a bit more complicated.

Service Account	Service Account Description	Group Membership	General Settings
sqlsa	SQL Server Agent Service Account (mssql agent)	· SQLServerSQLAgentUser$<servername>$<instancename>· Users	· Password never expires· User cannot change password
sqlsde	SQL Server Database Engine Service Account (mssqlserver)	· SQLServerMSSQLUser$<servername>$<instancename>· Users	· Password never expires· User cannot change password
sqlsas	SQL Server Analysis Services Service Account	· SQLServerMSASUser$<servername>$<instancename>· Users	· Password never expires· User cannot change password
Sqlsrs	SQL Server Reporting Services Service Account	· SQLServerReportServerUser$<servername>$<instanceid>· Users	· Password never expires· User cannot change password
Sqlffdl	SQL Full-text Filter Daemon Launcher Service Account	· SQLServerFDHostUser$<servername>$<instancename>· Users	· Password never expires· User cannot change password

3. The group membership bit you will not be able to do at this stage since these groups get created when you install Microsoft MSSQL Server 2008. You can see the memberships that should exists after installation if you look at the table above.

Let’s begin the install now we have SQL Service Accounts.

1. Locate the “Setup” file and double click it.

2. It is recommended to run “System Configuration Checker” before going forward, but not required.

3. This is the output from the “System Configuration Checker”.

4. After you have verified you meet all the requirements for “System Configuration Checker” click “Installation” on the left. On the right side click “New installation and add features to an existing installation”.

5. The first thing that happens is the install does some checks.

6. Click “Install”.

7. Some more checks. Click “Next”.

8. Enter your “Product key” and click “Next”.

9. Check “I accept the license terms” and click “Next”.

10. Choose “SQL Server Feature Installation” and click “Next”.

11. Click “Select All” and then click “Next”.

12. Yet another set of checks. Click “Next”.

13. Select “Default instance” unless you want to specifically name your instance.

14. Disk Space check is preformed. If you have enough space click “Next”.

15. This is where we use the SQL Service Accounts we created earlier. Refer to the table at the beginning of the section to properly populate these account names.

16. Choose “Mixed Mode (SQL Server authentication and Windows authentication)”, click “Add Current User”, and then click “Next”.

17. Click “Add Current User”, and then click “Next”.

18. Make sure “Install the native mode default configuration” and click “Next”.

19. Click “Next”.

20. Yet again we have more checks done. Click “Next”.

21. Click “Install” to do begin the actual installation process.

22. At the end of “Installation Process” click “Next”.

23. If everything went good you should have a similar screen. Click “Close” to finish.

Background on Database Mirroring

Database Mirroring Overview

Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Note

You cannot mirror the master, msdb, tempdb, or model databases.

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

Database mirroring is a simple strategy that offers the following benefits:

Increases data protection.
Database mirroring provides complete or almost complete redundancy of the data, depending on whether the operating mode is high-safety or high-performance.
Increases availability of a database.

In the event of a disaster, the database administrator has the alternative of forcing service (with possible data loss) to the standby copy of the database.

· Improves the availability of the production database during upgrades.

How Database Mirroring Works

The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

Transaction Safety and Operating Modes

Operating Modes

A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.

There are two mirroring operating modes.

One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.

The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded. This mode requires MSSQL Server 2008 Enterprise.

This document will only cover how to setup/administer the synchronous high-safety mode.

Transaction Safety

Whether an operating mode is asynchronous or synchronous depends on the transaction safety setting. If you exclusively use SQL Server Management Studio to configure database mirroring, transaction safety settings are configured automatically when you select the operation mode. Since SQL Server Management Studio is used in this document the end result will be synchronous.

If you use Transact-SQL to configure database mirroring, you must understand how to set transaction safety. Transaction safety is controlled by the SAFETY property of the ALTER DATABASE statement. On a database that is being mirrored, SAFETY is either FULL or OFF.

If the SAFETY option is set to FULL, database mirroring operation is synchronous, after the initial synchronizing phase.
If the SAFETY option is set to OFF, database mirroring operation is asynchronous. The session runs in high-performance mode.

Topology – Synchronous High-Safety Mode without Automatic Failover

All database mirroring sessions support only one principal server and one mirror server. The following figure shows the configuration of synchronous high-safety mode without automatic failover. The configuration consists of only the two partners. This configuration is shown in the following illustration.

When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data). If the principal server is lost, the mirror is suspended, but service can be forced to the mirror server (with possible data loss).

Note

Establishing a new mirroring session requires that all involved server instances run the same version of SQL Server.

Role Switching

Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server. In role switching, the mirror server acts as the failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly.

The following three forms of role switching exist.

· Automatic failover

This requires high-safety mode and the presence of the mirror server and a witness. The database must already be synchronized, and the witness must be connected to the mirror server.

The role of the witness is to verify whether a given partner server is up and functioning. The witness role is not discussed further in this document.

· Manual failover

This requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized.

· Forced service (with possible data loss)

Under high-performance mode and high-safety mode without automatic failover, forcing service is possible if the principal server has failed and the mirror server is available.

In any role-switching scenario, as soon as the new principal database comes online, the client applications can recover quickly by reconnecting to the database.

Configuration of Database Mirroring

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

To configure mirroring you will need to have two databases that are identical. If you have not preformed a backup of your database you will want to follow these short steps.

Microsoft SQL Server Management Studio

1. Right click on your database in the “Object Explorer”, go to “Tasks”, and click on “Backup…”.

2. In the “Source” section select your “Database” in the dropdown.

In the “Backup set” section give the backup a name and provide a description if you like.

In the “Destination” section click “Add” and pick a location for your backup to be saved to.

Note

The backup file name needs to end in “.bak”.

Click “Ok” to start the backup.

3. Repeat Steps 1-2 but in the “Source” section change the “Backup Type” to “Transactional” and in the “Destination” section use the same file you used for the Full backup (the default backup setting is “appended to the backup file”).

4. Copy the backup file you just made or created before to the server that will start out as the “mirror server”.

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

1. Create a database/transactional log backup on the Principal server.

Note

The default database recovery model is “Simple” but we want “Full”.

On the Principal server set the recovery type for each database you wish to mirror.

USE master;
GO
ALTER DATABASE <DatabaseName>
SET RECOVERY FULL;
GO

Example:

PRINCIPAL SERVER:

USE master;
GO
ALTER DATABASE TempDB
SET RECOVERY FULL;
GO

2. Backup the Database on the Principal Server.

BackupType:

Full = Full Database Backup

Log = Transaction Log Backup

To do a Full Database Backup:

BACKUP DATABASE <DatabaseName>
TO DISK = 'C:<DatabaseName-BackupType>.bak'
WITH FORMAT
GO

Example:

PRINCIPAL SERVER:

BACKUP DATABASE TempDB
TO DISK = 'C:TempDB-Full.bak'
WITH FORMAT
GO

Backup the log database too.

BACKUP DATABASE TempDBLog
TO DISK = 'C:TempDBLog-Full.bak'
WITH FORMAT
GO

To do a Transactional Log Backup:

BACKUP LOG TempDB
TO DISK = 'C:TempDB-Log.bak'
GO

Backup the log database too.

BACKUP LOG TempDBLog
TO DISK = 'C:TempDBLog-Log.bak'
GO

Copy the backup file over to the Server2 that we want to be the Mirror server.

Restoring a Backup Copy of the Principal Server’s Database on the Mirror Server.

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

Using Microsoft SQL Server Management Studio

1. Open “Microsoft SQL Server Management Studio” on the “mirror server”, right click “Databases”, and left click “Restore Database…”.

2. In the “Destination for restore” section populate the “To database:” box with the exact same database name that you preformed the backup on, on the principal server.

In the “Source for restore” section click the radio button “From device:” and click the button on the far right.

3. You should get a “Specify Backup” window. Click “Add”, locate the backup file you copied to this server, and click “Ok”. Click “Ok” one more time.

4. You should be back to the “Restore Database – <database name>” window. If you are you need to check the box in the restore column for the file you just finished added.

On the left hand window under “Select a page” click “Options”.

Make sure you select the transactional log and the full backup.

5. In the “Recovery state” section click the radio button for “Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)” and click “Ok”.

6. You should now see your databases in a state of “Restoring”.

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

1. Restore database/transactional log backups.

Assumption:

We are going to assume you have copied it over to Server2 and have it in the root of the C: drive.

BackupType:

Full = Full Database Backup

Log = Transaction Log Database Backup

2. To Restore a Full Database Backup:

RESTORE DATABASE <DatabaseName>
FROM DISK = 'C:<DatabaseName-Full>.bak'
WITH NORECOVERY
GO

Restore Full Database Backup Example:

RESTORE DATABASE TempDB
FROM DISK = 'C:TempDB-Full.bak'
WITH NORECOVERY
GO

Restore the log database too.

RESTORE DATABASE TempDBLog
FROM DISK = 'C:TempDBLog-Full.bak'
WITH NORECOVERY
GO

3. To Restore a Transactional Database Backup:

RESTORE LOG <DatabaseName>
FROM DISK = 'C:<DatabaseName-Log>.bak'
WITH FILE=1, NORECOVERY
GO

Restore Transactional Log Backup Example:

RESTORE DATABASE TempDB
FROM DISK = 'C:TempDB-Log.bak'
WITH FILE=1, NORECOVERY
GO

Restore the log database too.

RESTORE DATABASE TempDBLog
FROM DISK = 'C:TempDBLog-Log.bak'
WITH FILE=1, NORECOVERY
GO

Restore Transactional Log Backup Example:

Configuring Database Mirroring

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

Using Microsoft SQL Server Management Studio

1. If you are not already on the Principal server login now. Open “Microsoft SQL Server Management Studio” and click on the plus symbol next to “Databases”. The database(s) we are going to be mirroring should be visible. Right click on the database you want to setup database mirroring. Go to “Tasks” and then to “Mirror…”.

2. Click on “Configure Security”.

3. Click “Next”.

4. Choose the radio button “No” because we will not be using a witness server. Click “Next”.

5. The dropdown “Principal server instance” is likely already selected to be the name of the server you are connected to and if not make sure it is.

I used the default port “5022” for the “Listener port” but you can use a different port. If you have a firewall enabled on your servers that will be the Principal/Mirror server you will want to disable it first to make sure everything comes up. After everything is working you can enable it again and troubleshoot your rules by allowing the proper ports (i.e. 5022,etc).

Check “Encrypt data sent through this endpoint”.

The “Endpoint name” is a friendly name so name is so it makes sense to you. I normally name it like so <mirror server hostname> <DatabaseToMirror> Mirroring (i.e. Server2 TempDB Mirroring).

Click “Next”.

6. The dropdown “Mirror server instance” needs to be set to the name of the mirror server. After setting it click connect.

Check “Encrypt data sent through this endpoint”.

The “Endpoint name” is a friendly name so name is so it makes sense to you. I normally name it like so <PrincipalServerHostname> <DatabaseToMirror> Mirroring (i.e. Server1 TempDB Mirroring).

Click “Next”.

7. Leave all of these boxes blank and click “Next”.

8. Click “Finish”.

9. MSSQL will create a Data Mirroring object under “Server Objects”->”Endpoints”->”Database Mirroring” on the Principal and Mirror servers and make the necessary database changes. If those changes are done successfully you should have a screen similar to what I have below.

Click “Close”.

10. You will receive a warning similar to the one shown below. Click “Start Monitoring” to tell MSSQL to attempt to initiate/complete the mirroring connection.

11. Click “Yes”. MSSQL is just warning you that you are not using a Fully Qualified Domain Name (FQDN). To use a FQDN you need to connect to everything using a FQDN via “Microsoft SQL Server Management Studio”. This also means you need to have a domain setup. This guide is intended for setup outside a domain.

12. If mirror was setup successfully you wil see the status window now says “Synchronized: the databases are fully synchronized.” If you have the same status I have described you can click “Ok”. If you do not you will need to trouble shoot the issue.

13. You should now see the following status “Principal, Synchronized” on the “Principal Server”.

14. You should now see the following status “Mirror, Synchronized / Restoring…” on the “MIrror Server”.

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

1. Create endpoints on both servers

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

Example:

PRINCIPAL SERVER:

CREATE ENDPOINT “Server2 TempDB Mirroring”
STATE=STARTED AS TCP(LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

MIRROR SERVER:

CREATE ENDPOINT “Server1 TempDB Mirroring”
STATE=STARTED AS TCP(LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

2. Set partner and setup job on mirror server

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring

Note

The Database monitoring default interval is 1 minute.

Example:

MIRROR SERVER:

ALTER DATABASE TempDB SET PARTNER = N'TCP://Server1:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO
ALTER DATABASE TempDBLog SET PARTNER = N'TCP://Server1:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO

3. Set partner and setup job on principal server

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring

Note

The Database monitoring default interval is 1 minute.

Example:

PRINCIPAL SERVER:

ALTER DATABASE TempDB SET PARTNER = N'TCP://Server2:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO
ALTER DATABASE TempDBLog SET PARTNER = N'TCP://Server2:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO

Administering MSSQL

Managing Logins and Jobs After Role Switching

Only content within the principal database is mirrored. Associated information in the master or msdb system databases cannot be mirrored. Such associated information includes jobs set up against the principal database and logins that are added to the principal server.

If such information is important to supporting role switching, the information should be duplicated at the mirrored site. If it is possible, after roles are switched it is best to programmatically reproduce the information on the new principal database. The most common issues are logins and jobs.

· Logins

For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.

The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.

· Jobs

Jobs, such as backup jobs, require special consideration. Typically, after a role switch, the database owner or system administrator must re-create the jobs for the new principal database.

When the former principal server is available, you should also delete the original jobs from the new mirror database. Jobs on the mirror database fail because it is in the RESTORING state and so it is unavailable.

Note

The partners might be configured differently, with different tape drive letters or such. The jobs for each partner must allow for any such differences.

Mirroring States

During a database mirroring session, the mirrored database is always in a specific state (the mirroring state). The state of the database reflects the communication status, data flow, and the difference in data between the partners. The database mirroring session adopts the same state as the principal database.

Throughout a database mirroring session, the server instances monitor each other. The partners use the mirroring state to monitor the database. With the exception of the PENDING_FAILOVER state, the principal and mirror database are always in the same state.

The possible mirroring states of the database are as follows:

Mirroring state	Description
SYNCHRONIZING	The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
SYNCHRONIZED	When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.
SUSPENDED	The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session.SUSPENDED is a persistent state that survives partner shutdowns and startups.
PENDING_FAILOVER	This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
DISCONNECTED	The partner has lost communication with the other partner.

How to Manually Failover the Database between Principal and Mirror

To manually failover the database between the principal and mirror server you will need do the following.

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

Using Microsoft SQL Server Management Studio

1. If you are not already on the Principal server login now. Open “Microsoft SQL Server Management Studio” and click on the plus symbol next to “Databases”. The database(s) we are mirroring. Right click on the database you want to failover to the mirror server. Go to “Tasks” and then to “Mirror…”.

2. If you simply click the button “Failover” the failover will happen. If you are mirroring the log database for your database you will want to failover that database first (i.e. database = TempDB, database log = TempDBLog).

Using Microsoft Transact-SQL (T-SQL)

When the mirrored database is synchronized (that is, when the database is in the SYNCHRONIZED state), the database owner can initiate manual failover to the mirror server. Manual failover can be initiated only from the principal server.

On the principal server first we will set the database context to the master database and then initiate an immediate transition of the mirror server to the principal role.

Note

On the former principal, clients are disconnected from the database and in-flight transactions are rolled back (meaning lost).

USE master
GO
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER
GO

Example:

PRINCIPAL SERVER:

Failover any <DatabaseName>Log databases first.

ALTER DATABASE TempDBLog SET PARTNER FAILOVER

Failover the main database after you have successfully failed over the Log database.

ALTER DATABASE TempDB SET PARTNER FAILOVER

How to: Force Service in a Database Mirroring Session

Caution

Forced service is strictly a disaster recovery method. Forcing service may involve some data loss. Therefore, force service only if you are willing to risk losing some data in order to restore service to the database immediately. If forcing service risks losing significant data, we recommend that you stop mirroring and manually resynchronize the databases.Forcing service suspends the session and starts a new recovery fork. The effect of forcing service is similar to removing mirroring and recovering the former principal database. However, forcing service facilitates resynchronizing the databases (with possible data loss) when mirroring resumes.

In high-performance mode and high-safety mode without automatic failover, if the principal server fails while the mirror server is available, the database owner can make the database available by forcing service to fail over (with possible data loss) to the mirror database. This option is available only under all the following conditions:

Or said differently

We are going to assume Server1 (Principal Server) is dead and now we want to change Server2 (currently in a mirror mode) into a “Principal Server”. To do this we will have to execute a SQL Query because I cannot find any documentation on how to do it within “Microsoft SQL Server Management Studio”.

Using Microsoft Transact-SQL (T-SQL)

To check the current failover state and Safety Settings:

SELECT SD.name, SD.database_id, SD.state_desc, SDM.mirroring_state_desc, SDM.mirroring_role_desc, SDM.mirroring_safety_level_desc, SD.recovery_model_desc
FROM sys.database_mirroring AS SDM
JOIN sys.databases AS SD
ON (SDM.database_id = SD.database_id)
WHERE mirroring_guid IS NOT NULL

Example output (Principal Server):

Example output (Mirror Server):

To force service in a database mirroring session

Connect to the mirror server and issue the following statement:

ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The mirror server immediately transitions to principal server, and mirroring is suspended.

Example:

MIRROR SERVER:

ALTER DATABASE TempDB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
ALTER DATABASE TempDBLog SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

References:

Technical Information:: http://technet.microsoft.com/en-us/library/ms187875.aspx

November 3, 2008

SQL commands

Filed under: mysql, sql — Tags: 10346, 4419 — lancevermilion @ 1:40 pm

mysql> select table2.name, phone_number, email_address, count(t1.field1_id) from table1 t1 right outer join table2 t2 on (t1.field1_id = t2.field1_id) group by t2.name;

This will create an output similar to this.

The “Table2 Name” is just a distinct list from the name field of table2 table.
The “# of entries per name in table2” is just a result from count( where field1_id(from
table1 table) = field1_id (from table2 table) )

+————————————-+————————————————–+
| Table 2 Name               | # of entries per name in table2 |
+————————————-+————————————————–+
| xxxx.xxx.1.S000018      |                                          43 |
| x.xxx.xxxx.1.s000032    |                                           5 |
| xxxx01                        |                                           13 |
| xxxx03                        |                                           15 |
| xxx.xxx.xx.1.s000031   |                                           17 |
| xxx.x.xxxx.s00017       |                                           28 |
| xxx.xxx.s000003         |                                           47 |
| xxx.xxx.aaaabbb         |                                            0 |
+————————————-+————————————————–+

September 4, 2012

How the Syntax changed

Example output

Here is the Short version of the SQL (CASE statement).

Here is the Long version of the SQL (nested SELECT statements).

October 13, 2011

Perl to Microsoft SQL Server 2008 Standard via ODBC using FreeTDS

Pre-requisites

Steps to get MSSQL,ODBC and Perl working as one happy product.

External Reference links used to get everything working.

August 12, 2011

Let’s begin the install now we have SQL Service Accounts.

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

November 3, 2008

Recent Posts

Archives

Pages