Reports subscription in SSRS 2008

I had a request from my clients asking “Can a user subscribe to reports all by his own?”. The quick answer I said “Yes”, and I did a POC to find how it could be done and what are the limitations.

After the POC I decided to document which I shared with the client team. I thought I should share with you all on what it is all about! Although the content isn’t exhaustive but probably could help for a initial startup.

/**************************************************************************************/

Scheduling a report in Report Server (SSRS 2008)

Subscription:

A subscription of a report is an on-demand reporting which could be scheduled and the delivery of the report can be automated. A subscription is processed in the report server and the delivered report can be shared in a folder of a file server of can be sent to email addresses.

A subscribed report uses stored credentials, and the user wanting to create subscription should have permission to view the report and also creating individual subscriptions. As part of configuration at reporting server level, scheduled events and report delivery (e-mail delivery should be configured separately) should be enabled. Additional delivery extensions can also be added by installing developed custom extensions.

Types of subscriptions:

-          Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing. For each standard subscription, there is exactly one set of report presentation options, delivery options, and report parameters.

-          Data-driven subscriptions get subscription information at run time by querying an external data source that provides values used to specify a recipient, report parameters, or application format. These types of subscriptions are typically created and managed by Report Server administrators.

The limitations of using data-driven subscriptions are as following,

• Data-driven subscription functionality is not available in Standard Edition.
• For subscription data, choose a data source that can provide schema information to the report server. The supported data source types include SQL Server, Oracle, Analysis Services databases, SQL Server Integration Services package data, ODBC data sources, and OLE DB data sources.

Permissions for subscriptions:

Users can subscribe to reports through two tasks described as below.

-          The “Manage individual subscriptions” tasks will allow creating, modifying, and deleting subscriptions that are owned by a user for a specific report. This task is part of the Browser and Report builder predefined roles. Any user assigned to these roles having the above task will the user to manage only those subscriptions that are owned.

-          The other task will allows users to access and modify all subscriptions is “Manage all subscriptions”. This task is for data-driven subscriptions and is part of the predefined role – Content Manager.

Creating standard subscription:

A standard subscription can be created by individual users who want to have a report delivered through e-mail or to a shared folder. A standard subscription is always defined through the report on which it is based.  A user who creates a subscription owns that subscription. Each user can modify or delete the subscriptions that he or she owns.

Pre-requisites /Limitations of Standard subscription

Requirement 1

Permission to view the report which is chosen for subscription, which would mean the user, should be assigned to the role “Manage individual subscriptions”.

Requirement 2

The report must use stored credentials or no credentials to retrieve data at run time. A report can’t be subscribed which is configured to use the impersonated or delegated credentials of the current user to connect to an external data source. The stored credentials can be a Windows account or a database user account.

Requirement 3

If the model is used as a data source for a report contain security settings then this report can’t be subscribed.

Requirement 4

If the report requires a parameter to run during the processing time then the input parameter should be defined while scheduling the subscription.

Subscription creation

Once the subscription is created, a SQL Agent job with a system id is created with particulars like

-          Job name

-          Job description

-          Job schedule (day, hour, frequency)

-          Job running account – Local Service

-          Transact SQL referring to an event which will trigger the schedule process

When the job runs successfully an e-mail is sent and /or the scheduled report is put in the shared folder of the file server for later access.

Other details:

Subscriptions to reports create specific schedules that are defined through subscription properties, but shared schedules are easier to manage and maintain for the following reasons:

• Shared schedules can be managed from a central location, making it easier to compare schedule properties, adjust frequencies and recurrence patterns.
• If shared schedules are used, when scheduled operations occur is precisely known. This makes it easier to anticipate and accommodate server loads before performance issues occur.

/**************************************************************************************/

Let me know what you guys think?

Cheers!

 

SQL DB Backups storage area

I have been into a discussion with one of my clients on where should the backup files be stored for quick backup and restore operations. Here is what I had to talk about,

After some thinking on “Which RAID level should the SQL DB Backups (.BAK, .TRN) be stored in the system?”. I have below thoughts, and recommendations.

Here is the flow of my thoughts.

1)    Does it matter on which RAID levels the backups are taken, be it .BAK or .TRN files or any kind of backups?

Yes, it would matter in systems that have multiple VLDB (Very Large Databases), where the transactions are high, several backup schedules run on multiple databases, taking different types of backups at different timings.

Although the current environment doesn’t have multiple VLDB high transactional databases with backup schedules running during the whole day for different database, I thought we shouldn’t go by the current configurations and should look from a long term perspective which led me to the next question.

2)    How does it matter? Does it matter in terms performance of taking backups, protection of backup files, performance of restore?

Yes, it is important to configure the backups on a drive which has appropriate performance, and fault tolerance. Also it is important to have the backups restored as quickly as possible from the backup location in the system.

In terms of performance of backups of database or restore of backup files, we need to know what are the internals happening so that we could provide an appropriate environment to speed up the process.

A backup or restore operation internally has a basic operation – reading from database, pausing, writing to the media and this happens multiple times until the backup is complete. And the restore operation would be vice-versa of backup internals, reading from backup files, pausing, and writing to [In Recovery] database, until restoration is complete.

The key point in both processes is reading /writing from /to the database /backup file. So essentially we need to help SQL Server in processing this quickly, by configuring the storage devices (system drives in here) appropriately. Also, to enhance the process of backup and restore it’s not the configuration of disks that matters, but it depends on the kind of backups taken, the way the backups are taken on the storage media (drives or tapes).

With all these thoughts, and keeping the configuration of the disk as the primary point I jumped on to the next question.

3)    How do we give performance and protection together?

The SQL DB systems are currently configured to use space allocations from RAID-5 and RAID-10 level of arrays.  Because of my naïve knowledge on RAID’s, I had to refresh my thoughts on what are the different RAID levels, and what exactly it means. Here is what the RAID-5 and RAID-10 are described,

RAID-5: This type of RAID stores parity information but not redundant data and parity information can be used to reconstruct data. And when each logical read occurs, the read performance is increased since reads occur from the configured disks. Whereas when a logical write occurs there are four physical read operations, decreasing the performance of write.

RAID-10: Combining RAID-0 and RAID-1 is RAID-10, which offers higher performance and tolerance because RAID-0 has striping which has high performance output for read /write operations and RAID-1 has mirroring of disks to provide protection.

After knowing how each RAID level works, and what we need my next question was.

4)    What should be the recommendations?

Backup of the databases (any kind) should be on drives which are at RAID-10 levels. The obvious reason being that RAID-5 gives bad performance when it comes to write operations, and RAID-10 provides better read /write performance because of striping, fault tolerance because of disk mirroring.

What do you guys think about my recommendations? Let me know.

Happy reading!