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.
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.
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.
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
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
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.
SQL Server Version, Release Year and Codenames
In the Data Awareness Programme to students, I have shown below slide to let them know on how many versions have released, so far. As you can see below, the first ever version released was in 1989 and we are in 2014 now. SQL 2016 CTP versions are available but expected to release in beginning of 2016. There were totally 13 releases in last 25 years and the product has become very much stronger than ever. SQL 2016 is going to be a huge one with easy integration to cloud.
SQL Server Features from 2000 to 2014
In my Data Awareness Programmes, I also spread the available SQL Server features from 2000 to 2014 in below slide formats. This will also give a quick glimpse on all SQL Server releases with available features in one single article. Note: These slides covers only a noticeable features and does not cover every single feature on it.
SQL Server Editions from 2000 to 2014
In my Data Awareness Programmes, I also spread the available SQL Server Editions from 2000 to 2014 in below slide formats. This will also give a quick glimpse on all SQL Server releases with available Editions in one single article.
All about Storage
In DBA Best practise training, I also cover the storage bits. This plays a vital role in IO and performance of every single application or user’s ad-hoc querying. As we know a database should have one MDF (Main Database File), 1 LDF (Log Database File) and can have more than 1 NDF (Secondary Database File). These files when stored across various drives improves the performance of the applications in one way. All data stored in tables are actually getting stored in these files. SQL Server gives flexibility, to store these data in both physical and logical components.
As you can see in below slide, the basic unit of storage is stored in these MDF, NDF via File Group concept, where you dedicate one filegroup location for one database file. Also this slide covers the concept of extents and its types (Mixed & Uniform).
