Author Archives: Dr. SubraMANI Paramasivam

ByDr. SubraMANI Paramasivam

What is XACT_ABORT in terms of implicit or explicit transactions?

When it comes to data modification statements for OLE DB systems, the XACT_ABORT option with ON and OFF functions helps the Developer/Administrator to decide based on the requirements from the business.

For example lets take a stored procedure that will complete fully, only if the UPDATE statements are completed. In this case the XACT_ABORT option should be set to ON like below.

SET XACT_ABORT ON;

GO

BEGIN TRANSACTION

By setting this XACT_ABORT option to ON, it does not affect trapping of any errors using TRY-CATCH, but it helps in a scenario where the commit or rollback was successful or not.

 

 

ByDr. SubraMANI Paramasivam

Report server modes

There are 2 types of report server modes and given in below screen with available options in SQL 2012. The 2 modes are Native & SharePoint. With native mode you get all the features available to operate your reporting services. But with SharePoint mode you get to manage the SSRS environment using your local SharePoint. By operating via sharepoint mode, you can enable all your intranet users to get to access to SSRS environment within the same application but it does have its own downside like it does not support linked reports, my reports, my subscriptions.

ReportServerModes

ByDr. SubraMANI Paramasivam

SSRS Report Subscriptions

Subscription option with SSRS Reporting tool enables a user to schedule a report to deliver to a mail box as an attachment or to a network shared drive on a regular interval like everyday at 9am or every Monday at 10am or every month, every quarter, etc., based on the business requirements. This subscription is a great feature which enables a user to still see the report and data on that specific date and time no matter if the report date falls on a public holiday or a week end. So we don’t need a manual intervention to run the report like a robot, but instead the subscription option deliver the robotic service.

As this is alternative to running reports on demand, this also gives a great option to execute and deliver a report dynamically based on a condition derived with Dynamic Subscription option, like if the data exists or if the user exists. Below picture explains some of the options available in subscription option. A quick demo on subscription and dynamic subscription is available here.

SSRS_Subscriptions

 

ByDr. SubraMANI Paramasivam

Can I override Reportserver DB?

Has anyone of you ever wondered there are additional 2 system databases available once SSRS environment is added? These 2 database names are ReportserverDB & ReportserverTempDB. These databases stored all information related to your users, security, deployed reports, schedules, report history, etc.,

For those of you who are already aware of these databases, has anyone tried changing this? If yes, then you must have known the database a bit better. But have you ever wondered whether it is good to make changes to the system database? The answer is strictly NO, however at very rare situations, for those who are very well aware of what to change and if it is safe to do so have done some changes like me. Here is one of the scenarios that I have worked on, which can give you bit more understanding on this.

I had a consultant working for the company who used to create schedules to deliver a report to secured shared drive. After he left the company that job failed after 3 months. While analysing such issue, identified the company had policy to change the password of old employees for first 3 months and then remove them permanently after that. So after 3 months once the AD account no longer existed, the jobs started failing. During this time, I cannot raise a IT helpdesk to create the user again but raise the helpdesk ticket to have a system AD account for schedules and then went to the ReportServer DB and modified the schedules to totally move the owner name of the schedules to be the new system account. That’s it job done.

Below is also a screenshot for you to understand on Reportserver DB and what it can support.

Override_SSRS_DB

ByDr. SubraMANI Paramasivam

Securing your Report Manager easily with AD Groups

If you have already practised your SSRS environment locally, you might have played with less then 3 users and couple of AD groups, but what about a situation given like below image with more than 300 reports, 100+ users? How it will enable a DBA to maintain the security based on business requirements. You can add all users individually to the SSRS Report manager and grant them access to the relevant folder with all related hierarchy folders which is a big hassle. Here is where, the windows AD groups jumps in with magic wands to enable even non DBA’s like IT support guys to maintain the users list (of course with proper approval process) on who can access what reports across various departments or within the same department. You simply have to do the above exercise once with the AD group and SSRS Report manager automatically picks the relevant AD Group from Windows Active Directory. This intelligent tool will then enable a IT support guy to easily add / remove the users based on the requirements. You can also have a quick demo on security with AD here.

User_AD_Groups_Security

ByDr. SubraMANI Paramasivam

Report Manager in SSRS Box

What features do we have in terms of making use of the full installation of SSRS environment? Report manager does have features like viewing the available reports, search for any specific report, print and subscribe to deliver at required intervals.

ReportManagerOverview

It also enables (if allowed) to create folders, reports, upload your own word/excel/ppt documents, secure, maintain and organize them again. You can enable a user to login to SSRS browser based tool and you can let them browse the report in your own organized way, enable them to manage their own contents in a specific folder(s), to create their own reports and store in their “My reports” folder, publish their changes to the reporting application directly. Also you can let them give access to Report builder tool, which will enable them to start creating / modifying the reports using browser based application and the BIDS is not required.

You can also see other cool features such as History of running the reports, parametrized reports, grating access to specific entities like AD Groups, etc.,

And a video is available here for you to take a closer look.

ByDr. SubraMANI Paramasivam

How to enable/disable Resource Governor

A simple screenshot below shows you how you can easily enable/disable resource governor. By using SQL Server Management studio, connect to the instance where you need to enable Resource Governor. Then navigate down to Management and expand to see resource governor. There you can simply right click and choose Enable from the pop up menu. You can also enable by using T-SQL by simply passing below command.

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

EnableDisableRG

To disable via GUI, using your SSMS navigate to the management again and then right click on Resource Governor and choose Disable from the pop-up box. Alternatively, you can pass below T-SQL command to disable.

ALTER RESOURCE GOVERNOR DISABLE;

GO

 

ByDr. SubraMANI Paramasivam

Evolution of data warehouses

Evolution in organization use of data warehouses

Organizations generally start off with relatively simple use of data warehousing. Over time, more sophisticated use of data warehousing evolves. The following general stages of use of the data warehouse can be distinguished:

Off line Operational Databases

Data warehouses in this initial stage are developed by simply copying the data of an operational system to another server where the processing load of reporting against the copied data does not impact the operational system’s performance.

Off line Data Warehouse

Data warehouses at this stage are updated from data in the operational systems on a regular basis and the data warehouse data is stored in a data structure designed to facilitate reporting.

Real Time Data Warehouse

Data warehouses at this stage are updated every time an operational system performs a transaction (e.g., an order or a delivery or a booking.)

Integrated Data Warehouse

Data warehouses at this stage are updated every time an operational system performs a transaction. The data warehouses then generate transactions that are passed back into the operational systems.