Which Reports Aren't Inherting Permissions?

Managing security settings in Reporting Services through the Report Manager website is usually pretty easy. However I've found that sometimes things go astray. Today I found a few reports which weren't inheriting permissions from their parent directory. Easy enough to fix, you jump into the security settings for that report and set "Revert to Parent Security". However, I have dozens of reports in each of a dozen directories; if I suspect this problem is going on in other reports, I'm not keen on a "needle in a haystack" search.

Here's the code for identifying which reports are no longer inheriting permissions from their parent:

USE ReportServer
SELECT
         [Path],
         [Name]
   FROM
         [dbo].[Catalog]
   WHERE
         [PolicyRoot] = 1

Another Standards Discussion: Views and Business Rules

Let’s say that the business you work for has also been around for many years, and in that time loads of little rules about the data have appeared. Market A works differently to market B. This car shouldn’t be included in this list of products. When pulling together a list of shoppers, we only care about those that have credit cards.

Every time a new project comes up, a new part of a public facing website or a new module in an in-house application, new stored procs are developed. Often, these procs are written by people who weren’t around when the DB schema involved was designed, or they haven’t got all of those little rules I discussed above memorized. Who does?

This is where Views can come in handy. Instead of building each of those annoying little rules into each new Stored Procedure that’s developed, build them into Views, and point all Stored Procs towards Views instead of Tables.

And I mean ALL Stored Procs. As a standard. If you follow this standard, you can be sure that when it’s time to implement a new business rule, you can find the appropriate View, alter some code, and all the dependant Stored Procedures will now follow that new business rule.

For example, you have a table called Lotto.Entry. It records who has entered a lottery, and how old they are. Supplying the website, there are three hundred stored procedures which access data from this table. The CEO at your company decides that it’s no longer ethical to be reporting on people under 17 who have entered the lottery. If all those 300 Stored Procedures are pointing towards the table, you’re faced with moving data around, or changing all of those procs. If the procs are instead pointing towards Lotto.vEntry, that view can have a “WHERE Entry.Age > 17” clause added. Other Views should point towards this View instead of the underlying Table, so that the business rules are “inherited” across the system.

Another advantage is that the business rules are centralized. You know to look towards the basic view of a table if you want to know how its data is handled.

Pretending to delete data is another thing that can be done in views. Let’s say you want the users of the website/UI to feel like they’re deleting data, but you want an audit trail on that data, and you simply want to hide it from the users. Add an “IsDeleted” field to your table, and a “WHERE IsDeleted = 0” clause to the base view of that table. The data is then easily “undeleted” in cases of errors.

Views are a centralized, transparent and predictable way of implementing rules in which your data is accessed.

Object Naming Standards

Having a look through the new features of SQL2008, I was intrigued to find the new Declarative Management Framework. In short, it's a way to enforce various policies on your environments.

Without going too far into it (in this post), it made me think about what kinds of policies or standards I like to enforce as a Team Leader.

One particular area is in object naming conventions. This is always going to be an idiosyncratic/personal aspect of programming, but I’ve found that if one person takes the initiative, other people will follow. An agreed standard makes it easy to predict what an object will be called, cutting down on searching and guessing when you’re not familiar with a schema that someone else has designed.

Tables:
- No tables have a plural name, i.e. “Lottery.Entry”, not “Lottery.Entries”

Stored Procedures
- have the prefix “s”
- wherever possible include the main object they reference, what they are doing to the object, and by what parameters (if any, and only when there’s only one or two, otherwise use “filters” or “various” etc.) in the format “s[OBJECTNAME][ACTION]by[PARAMETERS]”. The main point is the object name and the action. This is open to a fair bit of interpretation, but here’s some examples
o Lottery.sEntrySelectByID
o Lottery.sEntryInsert
o Lottery.sResultSelectByFilters
o Lottery.sPredictionUpdate

- no underscores. One of my bugbears is guessing whether or not an underscore might separate one part of an object name from another. The simplest answer is to not use them

Views
- have the prefix “v”
- if they return fields from one table only, then they are named “v[TableName]”
- it they return fields mostly from one table, and reference other tables/views in order to bring back names etc,. for IDs in the original table, then they are named “v[TableName]Overview”
- no underscores

Triggers
- prefixed with “trig”
- suffixed with “AfterInsert”, “InsteadOfInsert” etc
- example: Lottery.trigEntryAfterInsert

Functions
- same rules as for stored procedures, but prefixed with “f”

Keys
- prefixed with “pk” for primary, “fk” for foreign, “uq” for unique
- perhaps “uk” would be more consistent for uniques? Hmmm I may have stuffed that one up over the last few years….
- reference the object name and the fields: e.g. fkEntry_LotteryID
- hey what’s that underscore doing there? I’m fine with underscores in objects that are rarely referenced which writing code off the top of your head. I think they’re fine in keys, indices etc., anything that’s not a view, proc or table

Indices
- ixTABLENAME_FieldName1_FieldName2 etc
- no need to reference INCLUDEd columns

Constraints
- “df” prefix for default constraints, e.g. dfEntryName
- “ck” prefix for Check Constraints

I’m sure there are plenty of people who would vehemently disagree with some of the choices I’ve made above. That’s great, and I’d love to hear why! My main point though is that having standards helps save time by making it easy for fellow developers/DBAs to predict what your objects are called when they’re searching for them. In a database like the main one I manage at the moment, where there are now over 10,000 objects, this can save some of the precious sanity we have left.

It's been a while!

Excuses? Work, Vietnam, Cambodia, Malaysia, music composition duties. It's been a while since I've blogged. Well I haven't forgotten about SQL, in fact it's high on the priority list.

I've just received some new SQL books in the mail. It's time to have a go at some certifications again. 70-432 and 70-433, Database Implementation and Maintenance, and Database Design, respectively. Flicking through the books, there's so much I already know, but I've never actually been near SQL2008, so there's also some huge chunks that are absolute news to me.

I'm also starting a Masters of Information Technology at the University of New England, part-time by "distance education". My aim there is to broaden my knowledge of IT, being a person who "fell into" IT at work rather than doing an IT based degree first. The degree I completed in 1995 was a Bachelor of Science, majoring in microbiology and genetics. Now I'm a DBA. Go figure! Apparently it's not such a rare thing for your degree to not have a huge bearing on your career path anymore.

Well, anyhow, just wanted to let y'all know I'm still here. Catch you soon.

Database Performance Monitoring on the Cheap Part 2

I was pretty happy to see that my last post "Database Performance Monitoring on the Cheap" got a bunch of hits on the intertubes. This somewhat balanced out my shame that the procedure I provided for querying the recorded data needed to be a bit smarter to be truly helpful.
Turns out that the stats provided by the sys.dm_os_performance_counters DMV are not all straightforward to interpret.
Some of them are either ratios (where one counter needs to be divided by another), or they are cumulative (where the counter needs to be compared to a previous value, and divided by the number of seconds elapsed).
Thankfully, there's an easy way to tell which counters are which, by checking the cntr_type field. When it comes to ratios, the numerator type is 537003264, and the denominator (or base) is 1073939712 (and has the word "base" at the end of the counter_name). The cumulative fellows are of type 272696576.

So without further ado, here's a smarter way to query that data which we've had collecting:

CREATE PROCEDURE [Monitoring].[sPerformanceCountersSelectByDateRange]
   @FormerDate DATETIME,
   @LatterDate DATETIME
         AS
   BEGIN
   SELECT
            DateTimeID
            ,DATETIME
            ,OBJECT_NAME
            ,counter_name
            ,instance_name
            ,cntr_value
            ,cntr_type
      FROM
      (
      SELECT
               DateTimeID
               ,DATETIME
               ,OBJECT_NAME
               ,counter_name
               ,instance_name
               ,cntr_value
               ,cntr_type
         FROM
               Monitoring.PerformanceCounters
         WHERE
               NOT PerformanceCounters.cntr_type IN (272696576,537003264,1073939712) --Cumulative and Ratio counters
         UNION
      SELECT --Cumulative Counters
               CurrentPerformanceCounters.DateTimeID
               ,CurrentPerformanceCounters.DATETIME
               ,CurrentPerformanceCounters.OBJECT_NAME
               ,CurrentPerformanceCounters.counter_name
               ,CurrentPerformanceCounters.instance_name
               ,(CurrentPerformanceCounters.cntr_value-PreviousPerformanceCounters.cntr_value)/
               DATEDIFF(ss,PreviousPerformanceCounters.DATETIME,CurrentPerformanceCounters.DATETIME) cntr_value
               ,CurrentPerformanceCounters.cntr_type
         FROM
               Monitoring.PerformanceCounters CurrentPerformanceCounters
               JOIN
               Monitoring.PerformanceCounters PreviousPerformanceCounters
               ON CurrentPerformanceCounters.counter_name = PreviousPerformanceCounters.counter_name
            AND CurrentPerformanceCounters.instance_name = PreviousPerformanceCounters.instance_name
            AND CurrentPerformanceCounters.DateTimeID
         = PreviousPerformanceCounters.DateTimeID
               + CASE RIGHT(CurrentPerformanceCounters.DateTimeID,2)
                  WHEN 00 THEN 45
         ELSE 5
         END
         WHERE
               CurrentPerformanceCounters.cntr_type = 272696576
         UNION
      SELECT --Ratio Counters
               NumeratorPerformanceCounters.DateTimeID
               ,NumeratorPerformanceCounters.DATETIME
               ,NumeratorPerformanceCounters.OBJECT_NAME
               ,NumeratorPerformanceCounters.counter_name
               ,NumeratorPerformanceCounters.instance_name
               ,NumeratorPerformanceCounters.cntr_value/CAST(DenominatorPerformanceCounters.cntr_value AS FLOAT) cntr_value
               ,NumeratorPerformanceCounters.cntr_type
         FROM
               Monitoring.PerformanceCounters NumeratorPerformanceCounters
               JOIN
               Monitoring.PerformanceCounters DenominatorPerformanceCounters
               ON DenominatorPerformanceCounters.cntr_type = 1073939712
            AND NumeratorPerformanceCounters.DateTimeID = DenominatorPerformanceCounters.DateTimeID
            AND NumeratorPerformanceCounters.instance_name = DenominatorPerformanceCounters.instance_name
         WHERE
               NumeratorPerformanceCounters.DATETIME BETWEEN @FormerDate 
            AND @LatterDate
            AND NumeratorPerformanceCounters.cntr_type = 537003264
      ) AllResults
      WHERE
            AllResults.DATETIME BETWEEN @FormerDate 
         AND @LatterDate
   END

Database Performance Monitoring on the Cheap

The system views are (to many) hidden gems in SQL. One in particular I'd like to address today is [sys].[dm_os_performance_counters], available in SQL2005 (and I assume 2008).

A quick SELECT * FROM [sys].[dm_os_performance_counters] will show you all that's available; a whole range of performance counters that you'd often go to Perfmon to check out.

The advantage of having this all so simply queryable from SQL is that we can start recording this every x minutes/hours, and start charting it out, and get some ideas about some of the weaknesses of our DBs, when they're under load etc.

First we need somewhere to store the data:

   ----------------------------------------------------------------------------------------
   --
   -- Create a schema
   --
   ----------------------------------------------------------------------------------------
CREATE SCHEMA [Monitoring];
   
   ----------------------------------------------------------------------------------------
   --
   -- Create a table for the stats to live in
   --
   ----------------------------------------------------------------------------------------
CREATE TABLE [Monitoring].[PerformanceCounters](
            [DateTimeID] [bigint] NOT NULL,
            [DateTime] [datetime] NOT NULL,
            [object_name] [nvarchar](128) NOT NULL,
            [counter_name] [nvarchar](128) NOT NULL,
            [instance_name] [nvarchar](128) NULL,
            [cntr_value] [bigint] NULL,
            [cntr_type] [int] NULL,
            CONSTRAINT [pkPerformanceCounters] PRIMARY KEY CLUSTERED
      (
               [DateTimeID] ASC,
               [counter_name] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
   ) ON [PRIMARY]
         

I've used a DateTimeID field as I don't like using Datetimes as part of primary keys, and also they can be useful in any Analysis Services you might want to do down the road.

Now for a proc to record some counters (feel free to pick and choose which counters you think are appropriate, and please let me know if you've any suggestions):

   ----------------------------------------------------------------------------------------
   --
   -- Create a procedure which will query the DMVs and store the results for reporting
   --
   ----------------------------------------------------------------------------------------
CREATE PROCEDURE [Monitoring].[sPerformanceCountersInsert]
         AS
   BEGIN
   INSERT INTO
               [Monitoring].[PerformanceCounters]
      SELECT
            CAST(DATEPART(YEAR, GETDATE()) AS bigint)*100000000+
            DATEPART(MONTH, GETDATE())*1000000+
            DATEPART(DAY, GETDATE())*10000+
            DATEPART(hour, GETDATE())*100+
            DATEPART(minute, GETDATE()),
            GETDATE(),
            [dm_os_performance_counters].[object_name],
            [dm_os_performance_counters].[counter_name],
            [dm_os_performance_counters].[instance_name],
            [dm_os_performance_counters].[cntr_value],
            [dm_os_performance_counters].[cntr_type]
      FROM
            [TheDBYouWantToMonitor].[sys].[dm_os_performance_counters]
      WHERE
      (
               OBJECT_NAME = 'SQLServer:Buffer Manager'
            AND (
                  counter_name = 'Page life expectancy'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:General Statistics'
            AND (
                  counter_name = 'User Connections'
               OR counter_name = 'Processes blocked'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:Databases'
            AND instance_name = 'TheDBYouWantToMonitor'
            AND (
                  counter_name = 'Transactions/sec'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:Access Methods'
            AND (
                  counter_name = 'Full Scans/sec'
               OR counter_name = 'Range Scans/sec'
               OR counter_name = 'Index Searches/sec'
               OR counter_name = 'Page Splits/sec'
               OR counter_name = 'Table Lock Escalations/sec'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:SQL Statistics'
            AND counter_name = 'SQL Re-Compilations/sec'
      )
         OR (
               OBJECT_NAME = 'SQLServer:Memory Manager'
            AND counter_name = 'Memory Grants Outstanding'
      )
         OR (
               OBJECT_NAME = 'SQLServer:Transactions'
            AND counter_name = 'Transactions'
      )
   END

This is all pretty DB centric stuff, it's unfortunate that more server level stats aren't available with this method, such as memory and disk usage.

Now we just need to set up a SQL Server Agent job to fire off that job every x minutes/hours. That'll depend on how busy your DB is, on my main prod DB I've got this guy running every 5 minutes, and a similar proc recording Data and Log file sizes on a daily basis.

Once you've got enough historical data, it's a simple matter to query:

CREATE PROCEDURE [Monitoring].[sPerformanceCountersSelectByDateRange]
   @FormerDate DATETIME,
   @LatterDate DATETIME
         AS
   BEGIN
   SELECT
            [DateTimeID]
            ,[DateTime]
            ,[object_name]
            ,[counter_name]
            ,[instance_name]
            ,[cntr_value]
            ,[cntr_type]
      FROM
            [Monitoring].[PerformanceCounters]
      WHERE
            [DateTime] BETWEEN @FormerDate 
         AND @LatterDate
   END

I'm hooking this proc up into a Reporting Services report at the moment, charting each counter seperately. It's quite interesting to see the shapes of the charts, and spurring me into understanding more about what each one really means. It's great stuff to show to mgt as well, giving them a bit of a look into the black box that is their SQL Server.

Warning: Don’t play with Reporting Services’ SQL Agent Jobs! Or How to Tell Which Job is Doing What

Having a look through SQL Server Agent’s list of jobs yesterday, I got a bit upset. About half the jobs in there are named things like “0343229B-0642-4E38-B7A5-C603C1F45976”. They’re Reporting Services Subscription jobs. Once again RS looks like a half-arsed product.

So I decide to go about renaming them, figuring that RS’ subscriptions will still be able to recognize the jobs as I’m only changing their names, not their IDs. Bad move. RS uses the names, can’t find the jobs when you restart the service, and recreates all those jobs with new GUIDs. Worse yet, I started getting “Only members of sysadmin role are allowed to update or delete jobs owned by a different login” errors whenever I tried to update subscriptions through the Report Manager, forcing me to have to play around with login permissions and job owners. A nightmare!

The script I wrote to help me recognize which job fires which subscription is below. It’s now more useful than ever:

NB: The CASE statement which transforms the “DaysOfWeek” int figure into actual days of the week doesn’t cover ever possible case, but it covered my needs. For a rundown of how this int works, see “Toolman’s” post at http://www.sqlservercentral.com/Forums/Topic501408-150-1.aspx

Also thanks to “stevefromOZ” from whose post at http://www.sqlservercentral.com/Forums/Topic254010-150-1.aspx I nabbed the email address part of the code below.

USE ReportServer
SELECT
         sysjobs.name,
   'RS - '
         + Catalog.Name
         + ' ['
         + CASE
            WHEN DaysOfMonth IS NOT NULL
            THEN CAST(DaysOfMonth AS VARCHAR(10)) + ' Day of Month'
            WHEN DaysOfWeek = 1 THEN 'Monday'
            WHEN DaysOfWeek = 2 THEN 'Tuesday'
            WHEN DaysOfWeek = 4 THEN 'Wednesday'
            WHEN DaysOfWeek = 8 THEN 'Thursday'
            WHEN DaysOfWeek = 16 THEN 'Friday'
            WHEN DaysOfWeek = 32 THEN 'Saturday'
            WHEN DaysOfWeek = 64 THEN 'Sunday'
            WHEN DaysOfWeek = 62 THEN 'Monday - Friday'
            WHEN DaysOfWeek = 120 THEN 'Wednesday - Saturday'
            WHEN DaysOfWeek = 126 THEN 'Monday - Saturday'
            WHEN DaysOfWeek = 127 THEN 'Daily'
   END
         + ' '
         + CAST(DATEPART(hh,Schedule.StartDate)AS VARCHAR(2))
         + CASE
            WHEN LEN(CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))) = 1
            THEN ':0' + CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))
   ELSE ':' + CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))
   END
         + ']' [NewName]
   FROM
         msdb.dbo.sysjobs
         JOIN
         dbo.ReportSchedule
         ON sysjobs.name = CAST(ReportSchedule.ScheduleID AS VARCHAR(255))
         JOIN
         dbo.Schedule
         ON ReportSchedule.ScheduleID = Schedule.ScheduleID
         JOIN
         dbo.Catalog
         ON ReportSchedule.ReportID = Catalog.ItemID
   ORDER BY
         Catalog.name

Querying ReportServer Database

Here's a script I knocked up which gives you some insights into your Reports Catalog in Reporting Services.

I got frustrated with the Reports Manager site and its inability to give you a wholistic view of subscriptions, and the script blew out a little from there as I had a scrounge around the ResportServer database structure.

I might hook this up to an RS report at some stage, if that's not akin to "crossing the streams".

Note that info about the number of executions and last execution time are based on the execution log, which only keeps records for the last 60 days by default.

USE ReportServer
SELECT
         CatalogParent.Name ParentName,
         Catalog.Name ReportName,
         ReportCreatedByUsers.UserName ReportCreatedByUserName,
         Catalog.CreationDate ReportCreationDate,
         ReportModifiedByUsers.UserName ReportModifiedByUserName,
         Catalog.ModifiedDate ReportModifiedDate,
         CountExecution.CountStart TotalExecutions,
         ExecutionLog.InstanceName LastExecutedInstanceName,
         ExecutionLog.UserName LastExecutedUserName,
         ExecutionLog.Format LastExecutedFormat,
         ExecutionLog.TimeStart LastExecutedTimeStart,
         ExecutionLog.TimeEnd LastExecutedTimeEnd,
         ExecutionLog.TimeDataRetrieval LastExecutedTimeDataRetrieval,
         ExecutionLog.TimeProcessing LastExecutedTimeProcessing,
         ExecutionLog.TimeRendering LastExecutedTimeRendering,
         ExecutionLog.Status LastExecutedStatus,
         ExecutionLog.ByteCount LastExecutedByteCount,
         ExecutionLog.[RowCount] LastExecutedRowCount,
         SubscriptionOwner.UserName SubscriptionOwnerUserName,
         SubscriptionModifiedByUsers.UserName SubscriptionModifiedByUserName,
         Subscriptions.ModifiedDate SubscriptionModifiedDate,
         Subscriptions.Description SubscriptionDescription,
         Subscriptions.LastStatus SubscriptionLastStatus,
         Subscriptions.LastRunTime SubscriptionLastRunTime
   FROM
         dbo.Catalog
         JOIN
         dbo.Catalog CatalogParent
         ON Catalog.ParentID = CatalogParent.ItemID
         JOIN
         dbo.Users ReportCreatedByUsers
         ON Catalog.CreatedByID = ReportCreatedByUsers.UserID
         JOIN
         dbo.Users ReportModifiedByUsers
         ON Catalog.ModifiedByID = ReportModifiedByUsers.UserID
         LEFT JOIN
   (
   SELECT
            ReportID,
            MAX(TimeStart) LastTimeStart
      FROM
            dbo.ExecutionLog
      GROUP BY
            ReportID
   ) LatestExecution
         ON Catalog.ItemID = LatestExecution.ReportID
         LEFT JOIN
   (
   SELECT
            ReportID,
            COUNT(TimeStart) CountStart
      FROM
            dbo.ExecutionLog
      GROUP BY
            ReportID
   ) CountExecution
         ON Catalog.ItemID = CountExecution.ReportID
         LEFT JOIN
         dbo.ExecutionLog
         ON LatestExecution.ReportID = ExecutionLog.ReportID
      AND LatestExecution.LastTimeStart = ExecutionLog.TimeStart
         LEFT JOIN
         dbo.Subscriptions
         ON Catalog.ItemID = Subscriptions.Report_OID
         LEFT JOIN
         dbo.Users SubscriptionOwner
         ON Subscriptions.OwnerID = SubscriptionOwner.UserID
         LEFT JOIN
         dbo.Users SubscriptionModifiedByUsers
         ON Subscriptions.ModifiedByID = SubscriptionModifiedByUsers.UserID
   ORDER BY
         CatalogParent.Name,
         Catalog.Name 

The Command Line

I didn't go to school to learn about computers, LIFE taught me about computers. I learned SQL on the job (well, maybe there's lots of study that's gone on since).

As a consequence, while I can write T-SQL better than I can write complete sentences in english, there's at least a few little basics that have escaped me along the line. So yesterday when I was trying to work out how to bring up a remote failed cluster node onto which I could no longer remote desktop, it was a revelation to me when someone told me how to remotely reboot a computer from the command line.

I thought "how do people know this black magic"?

And then I found the goods: An A-Z Index of the Windows XP command line

which looks quite similar to the TechNet article, but I'm not complaining.