Create user for all existing databases

USE master;

DECLARE
    DBNames CURSOR
FOR
    SELECT
        NAME
    FROM sysdatabases

OPEN DBNames

DECLARE @Name varchar(50)

FETCH NEXT FROM DBNames
INTO @Name

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    Declare @SQL varchar(255)
    Print 'Processing: ' + @Name
    BEGIN TRY
       Select @SQL = ' USE ' + @Name + ' CREATE USER [ldbamonitor] FOR LOGIN ["LOGINNAME HERE"] EXEC sp_addrolemember N''db_datareader'', N''ldbamonitor'''
       EXEC(@SQL)
    END TRY
    BEGIN CATCH
        select ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    FETCH NEXT FROM DBNames    INTO @Name
END

CLOSE DBNames
DEALLOCATE DBNames

Database status is shutdown, normal mode

SQL Database stats Shutdown,Normal

If you got your "database status is shutdown, normal mode".

Run the below query for reset the database status to Normal mode.

ALTER DATABASE dbname SET AUTO_CLOSE OFF

Now the database should be Normal mode.

Cannot connect to WMI provider.You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.

Step 1:- Verify which version of sql server 2005/2008
Step 2:- Go to Shared folder on C drive "C:\Program Files\Microsoft SQL Server\90\Shared"
Step 3:- Verify the following file present in the folder
sqlmgmproviderxpsp2up.mof

Step 4:- If missing copy from other instance folder
Step 5:- Register as shown below,

C:\>mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared
\sqlmgmproviderxpsp2up.mof"


SOLVED Problem.

Check the network address name and that the ports for the local and remote endpoints are operational.(Microsoft SQL Server, Error: 1418)

 Change the SQL Server from local service to Service Sccount.

http://www.youtube.com/watch?v=9v0OW2WfoIo

RESTORE,BACKUP PERCENTAGE

SELECT command, s.text, start_time, percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG','DbccFilesCompact')
OPTION (RECOMPILE);

SSRS Interview Questions

 

What is SQL Server Reporting Services?

Reporting Services helps business to Transform raw data (table data) into graphical like chart, histogram etc. to add meaning to raw data.

For example, the table below having a data but the moment that data was presented in chart, that added more business meaning to data.

SQL Server Reporting Services can query and display data from any of the multiple databases and represent in a way which is more understandable to users.

Quick VIDEO – SQL Server Reporting Services

What are the tools available in market as an Alternative to SQL Server Reporting Services / (Business Intelligence tools) ?

Market is full of business intelligence tools here are few listed of them,

Actuate	Hyperion (BRIO)	SIEBEL-CRM
BusinessObjects	Oracle Express OLAP	Qlikview
Cognos	Informatica Power Analyzer	Proclarity
IntelliView	Dundas Chart for .NET	MS-Excel
SAS	MicroStrategies	Pentaho

Open Source solutions

• Jasper Reports
• JFreeReport
• BIRT (Business Intelligence Reporting Tools)
• OpenReport
• DataVision
• Pentaho

What is reporting lifecycle ?

Reporting Services has three mainly three phases

1. Development of Reports (Developer) – First of all a report needs to be design which is primarily done by report developer
2. Management of Reports (DBA) – Once the Report is being developed, DBA need to ensure
1. Security – Only authorized user should access the report
2. Execution – How the report will be executed to optimize data sources performance
3. Scheduling of reports – so that report are executed on scheduled timings
3. Report Delivery (DBA+Developer) – Once the report is being developed and executed now the report should be reached to final recipients (business users) who are going to understand / analyze report data. if any changes, we again go back to development stage.

What is Reporting Services Architecture ?

Reporting Services is a .NET framework-based platform that includes a comprehensive tools that we can use to integrate reporting solution into any centrally-managed environment. CLICK HERE(http://goo.gl/LwBRj) for more details

What can we do with SQL Server Reporting Services ?

Reporting Services can help you in building and deploying fully interactive scorecards, dashboards, and enterprise reports.

SQL Server Reporting Services Feature

• Design ad-hoc dashboards and reports quickly
• Data is interactive and available everywhere even on your IPAD / IPHONE.
• This is bundle product with SQL Server database services, so there is additional cost for this service.
• .Net integrated for rapid development for report even in few clicks by using wizards.

By using Reporting Services, we can build up a solution which is kind of Google maps, where we can list our all business locations.

Check out video post to learn, “How to Integrate Map with SQL Server Reporting Services”

What are the New Features of SQL Server 2008 R2 Reporting Services ?

Lot of new features were added in SQL Server 2008 R2, like

• New Report Types – Table, Matrix, List, Chart, Sub report
• New Tools added to report designer Toolbox
• Report Data Panel -  built in page numbers
• Report Builder 3.0

For details for new feature of SQL Server 2008 R2, please check my previous post, “SQL Server Reporting Services R2 – New Features”

What are the new features are introduced in SQL Server 2012 reporting services?

SQL Server 2012 has introduced a couple of new features like

• Power View - interactive data exploration
• SQL Server 2012 is fully integrated with SharePoint
• Introduction to Data Alerts, data alerts are a data-driven alerting solution that informs you about changes in report data that are of interest to you, and at a relevant time
• SQL Server Data tool
• new rendering extensions supports MS Office 2010
• Project Crescent is being introduced

DBA (Production Support) Prospective

What are the tasks which are mainly performed by a production DBA on a SQL Server Reporting Services ?

Operational support DBA are mainly responsible for

• Install and Configure reporting services
• Backing up Reporting Services (including databases, config files and encryption keys)
• Deploying new reports
• create and manage shared data sources
• Optimizing reports – Enable caching of reports
• Authorize users for reports
• Create and deploy security roles.
• Check Reporting Services Error Logs for errors
• Maintain response time of reporting services

Can we run Reporting Services with SQL Server Express Edition, which is a free version of SQL Server ?

Yes, SQL Server Express Edition with Advanced Services support Reporting Services. we can use SQL Server Express Edition for deploying reporting Services.

What are the limitations in Reporting Services on SQL Server Express Edition ?

Microsoft offers reporting services free as part of SQL Server Express with Advance Services edition but following restrictions will be applicable to this edition.

• SQL Server Management Studio Express cannot be used to administer report server
• Report Models will not be available
• Other features like Caching, History and Delivery of Report is not available.
• Report Builder is not available
• Local SQL Server is a only option, which can be used as Report Data Source.
• We cannot store the report server database on a remote server, it has to be local only.
• Reports can be rendered only in Excel, PDF, Image formats only
• No SQL Server agent is available, so no scheduling is possible.
• Reporting Services will not be able to use more than 1 GB of RAM
• No Subscriptions (Standard and Data Driven) can be made
• Can not be integrated with Share Point
• Only named instances is supported
• Scale-out Report Servers will not be available
• Can not implement Role based security.

How to Install SQL Server Express with Reporting Services ?

For Step by Step instructions, Please check my previous post, “How to Install SQL Server Express with Reporting Services”

What are the parameters which should be considered for Reporting Services Disk storage requirement ?

There are various factors to be considers as a primary I would consider

1. Number of reports to be hosted server
2. Report size and frequency of reports
3. Number of snapshots to be saved

Can I configure SQL Server Reporting Services on a Windows cluster for High availability ?

Please check my previous post, “Installing & Configuring SQL Server Reporting Services on Cluster Environment for High Availability” to understand how reporting services works on cluster.

What is ReportServer and ReportServerTempDB ?

Reporting Services uses two SQL Server databases for storage by default, the databases are named ReportServer and ReportServerTempdb.

ReportServer is a main database, which store all internal configuration and report meta data where as ReportServerTempdb is used to store temporary data, session information, and cached reports.

How to backup SQL Server Reporting Services ?

There are mainly three things, which should be backed up as part of reporting services backup

1. Report Server Databases, which can be backed up by SQL server backup and restore method. Check my previous Video post, “HOW to BACKUP SQL”
2. SQL Server Reporting Services Configuration, SQL Server Reporting Services Configuration is saved in config files, which can be copied as part of backup. look for other to know config files and there location.
3. Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.

What is encryption key ?

Encryption keys are used by the report server so that items such as connection strings are maintained securely. these keys are required in case you want to perform restoration of report server databases.

How to backup encryption key ?

Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.

Can we install a 32-bit version of SQL Server Reporting Services on a computer that is running a 64-bit version of Windows?

YES, we can install SQL Server 32 bit on Windows 64 bit version. SQL Server Reporting services are part of SQL Server.

Where the SQL Server Reporting Configuration is saved, is that in registry ?

NO, all SQL Server configuration is saved in configuration files (.xml files)

What are the key configuration files for SQL Server Reporting Services ?

Mostly all Configuration files located at Install Directory\Microsoft SQL Server
\\Reporting Services\ReportServer and  ReportManager

• RSReportServer.config stores configuration settings for feature areas of the Report Server service: ReportManager, the Report Server Web service, and background processing.
• RSSrvPolicy.config stores code access security policies for the server extensions.
• RSMgrPolicy.config stores code access security policies for Report Manager
• ReportingServicesService.exe.config stores configuration settings that specify the trace levels and logging options for the Report Server service.

Other Files

• RSReportDesigner.config contains settings for Report Designer and this file is located in the ..\Program Files\Visual Studio 9.0\Common7\IDE\PrivateAssemblies
• RSPreviewPolicy.config stores server extensions used during report preview and this file is located in ..\Program Files \Microsoft SQL Server\100\Tools \ReportDesigner

What are the mostly used PERFMON Counters for monitoring SQL Server Reporting Services?

This is bit tricky and everyone has his own answer, but I personally choose the following at least

• ReportServer: Service Performance Object \ Active Connections
• ReportServer: Service Performance Object \ Report Executed per second
• ReportServer: Service Performance Object \ Total Cache Hits
• ReportServer: Service Performance Object \ Total Requests
• Web Service \ Deliveries per second
• Web Service \ Total processing failures

What all should be backed as part of reporting Services ?

1. Report server databases (ReportServer and ReporterverTempdb)
2. Encryption keys and
3. Configuration Files (xml files)
1. RSReportServer.config,
2. RSSrvPolicy.config,
3. RSMgrPolicy.config,
4. ReportingServicesService.exe.config,
5. RSReportDesigner.config and
6. RSPreviewPolicy.config

How to backup up Encryption Keys?

Use SQL Server Reporting Services Configuration tool to backup symmetric keys

Questions for Developer as well as Developers (depends on profile)

What are the tools which are being offered by Microsoft SQL Server for Developers?

• Report Designer, used to develop reports in Business Intelligence Development Studio (BIDS) and then publish them to a Report Server
• Model Designer, used create a report model that Report Builder users uses to help them build on-demand reports.
• Report Builder – used to build reports without having to understand the underlying data source structures in the database

Note – These are built in tools with Microsoft SQL Server, there is no additional cost for using these tools.

What are the tools which are being offered by Microsoft SQL Server for DBA’s?

• Report Manager – a web-based report access and management tool to browse existing reports, upload new reports, and other properties like execution properties, security, and subscriptions.
• Reporting Services Configuration –used to configure a report server like report server databases, SMTP server and services properties.
• SQL Server Management Studio (SSMS) -  an alternative to Report Manager, which is primarily being used by DBA’s for managing reports.

Note – These are built in tools with Microsoft SQL Server, there is no additional cost for using these tools.

How to deploy reports ?

Reports can be deployed by using BIDS (Business Intelligence development Studio ) or by Report Manager which is web based application. Please see previous post, “How to deploy report to SQL Server Reporting Services”

Can I deploy a report at multiple servers at one go ?

NO, native SQL Server Reporting services doesn’t supports this functionality but we can deploy reports directly to multiple servers at one go but this functionality is not built in, we need to buy some third party tools.

What is Report Builder ?

Report builder is used to create small reports and it a define interface. You can’t change the report interface in report builder it pre designed. You can just drag columns in the report.

Report builder creates reports on database objects available with report model project. Please check my previous Video post, ”How to Install Report Builder 3.0”

What is report rendering ?

Exporting a report data with design o different type of file types is knows as Report rending. SQL Server Reporting Services supports multiple rendering extensions like Word, Excel, CSV, PDF, HTML etc.

Can I disable | restrict SQL Server Reporting Services Export Formats (Rendering Formats) ?

YES, we can restrict Reporting services export formats.

SQL Server Reporting Services (SSRS) supports export to different formats like XML, CSV, HTML, PDF, etc., when you view the report.  This export option is available at report viewer Export drop-down list.

We can control this via report services server configuration file which is available at “C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\Report Server\ rsreportserver.config“.

For More details, please check my previous post “How to Disable/restrict SSRS Export format ?”

How to migrate SQL Server 2008 Reporting Services to another computer?

If you are trying to migrate only SQL Server reporting services to new server, then we have an option to install reporting services on new server and while configuring report server, we can point report server services to old report database database.

Or incase, you are trying to migrate report server databases and reporting service together, the install reporting services on new instance and restore configuration files  and restore report server databases and reconfigure the report server services.

How to check how frequent report is being called ?

Check Report history either from SSMS or Report manager. In report manager, go to report and select history tab. check here for more details

What is Report Caching ?

Report caching is a performance enhancing technique in Reporting Services that saves copies of reports for faster viewing. Cached instances have an expiration time set to force a refresh of the data set used
for the report.

In case you have filters in your report, when filters will be applied in Cached Report instance ?

Filters are applied when a report is rendered, Filters will not create a new cached instance on the Report Server.

What is report snapshot ?

Snapshot means a instance of a report for future reference, that means a copy of report (data is freezed) will be saved on a report server for future reference.

What is Data Driven Subscription ?

Subscriptions are used to deliver rendered reports to business users at specified schedule. For example, a sales report should go to sales manager daily at 7:00 AM for last day report. This is kind of pushing reports on a schedule. By creating subscription, we can send reports to users in e-mail or Deliver to a file share
or can put report in Microsoft Office SharePoint server

A data-driven subscription can deliver a report in many rendered formats to many destinations. For example, USER1 wants report in XML format and USER 2 wants in a pdf format and user 3 wants in word but that too on a shared drive.

To meet these objective we can use Data-driven subscriptions as data driven  subscription require a database table to store all these report values so that these values can be used during processing.

How to upgrade report from SQL Server 2005 to SQL Server 2008?

SQL Server Setup is used to upgrade SQL Server Reporting Services. Run the SQL Server 2008 setup on existing SQL Server 2005 and opt for upgrade.

Developer (Report Designer) Prospective

Any Idea, how to to build / design Report using Visual Studio?

Building Reports using Visual Studio is quite easy, Please check my previous Video POST, “How to create a report using Visual Studio 2008?” which describes

1. how to build a report using Visual Studio 2008
2. deploy report to SQL Server Reporting Services
3. How to create an shared data sources
4. How to design a report
5. How to add interactive sorting Change
6. Run the report in SSRS

What is Parameterized Reports in SSRS ?

You often need more advanced reporting such as drop down lists and the ability to use parameters when working in Sql Server Reporting Services. Please check VIDEO Tutorial, “How to make Parameterized Reports in SSRS”  which describes,

• How to use variables that have not been declared in SSRS vs TSQL
• How to create report with auto-Parameterization
• How to View Report Parameters
• How to create a drop-down list

What are different types of reports are available ?

There is as such not any type of of reports, but we can categories them as

Tabular Report – where we represent data in rows and columns format. (Now this is non as Tablix which is mixture of table and a matrix)

CHART REPORTS – where we represent the data in graphical format mainly in charts?

further more these reports can fetch data either from relational database (TSQL REPORTS category) or from CUBES (MDX Reports)

What sort of query we can write in SSRS, is that just SQL / TSQL or is there anything else too?

• MDX – for OLAP / CUBE data sources
• DMX – for data mining

Can you edit a report that an end user created by using Report Builder in BIDS?

Yes, if an end user created a report by using Report Builder in BIDS and saved the report definition file, you can open the file in BIDS and edit it.

What is Sub report ?

SUB report is very helpful when the detail of a report’s data region is so complex that a separate report is a better option then using other tools.

Can sub report data source be different from that of the parent report ?

YES, sub report data source can be different from that of the parent report.

What are the various ways to deploy reports ?

Reports can be deployed to report server by using any of the following method

• BIDS
• Computer Management console
• .nET START command
• SSMS
• Report Manager

but normally BIDS  and Report managers are mostly used.

Can we use Analysis Services Database as a data source with Model Designer ?

NO, Model Designer doesn’t access data from Analysis Services cubes.

What is report subscription?

Subscriptions are standing requests to deliver report data to requested recipients. Once the report is being subscribed and subscriber will get updates from report server on scheduled interval.

What is the RDL file?

RDL stands for Report Definition Language, when we design a report using any tool like BIDS and when we save the report, it’s saved as .rdl file, RDL file is an kind of xml code, which stores the design of the report.

This RDL file is being used  for deploying report to report server.

Can you use a stored procedure to provide data to an SSRS report?

Yes, you can use a stored procedure. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.

How to deploy Reports on SQL Server Reporting Services ?

Please check my previous VIDEO post to learn, Step by Step Learning, How to deploy report to SQL Server Reporting Services 2008

How can I build my first report which is ready to deploy in SQL Server Reporting Services ?

Please check my previous blog post, Introduction to SQL Server Reporting Services, for a brief video tutorial for SQL Server Report designer.

Solution Architect prospective

IT Manager prospective

Can we run Reporting Services with SQL Server Express Edition, which is a free version of SQL Server ?

Yes, SQL Server 2005 Express Edition with Advanced Services support Reporting Services. we can use SQL Server Express Edition for deploying reporting Services. For more details to know SQL Server Express Limitations, please check my previous post, “What are the limitations in Reporting Services on SQL Server Express Edition”

Is there any free tools to build reports ?

YES, there are log of open source code solution are available, which can used to design reports, here are few listed.

• Jasper Reports
• JFreeReport
• BIRT (Business Intelligence Reporting Tools)
• OpenReport
• DataVision
• Pentaho

What are the product alternatives to SQL Server Reporting Services ?

Market of full of tools and here are few of them

Actuate	Hyperion (BRIO)	SIEBEL-CRM
BusinessObjects	Oracle Express OLAP	Qlikview
Cognos	Informatica Power Analyzer	Proclarity
IntelliView	Dundas Chart for .NET	MS-Excel
SAS	MicroStrategies	Pentaho

How many instances of Reporting Services can be managed by a single DBA ?

This is pretty tricky questions, and there is not any single formula to decide this. This all depends on what sort of monitoring tools you have and what sort of automation you have achieved.

But as per my understanding and calculations a single 3-7 year experience DBA without any third party tool can easily deploy 25-35 report in a day, which is one time activity for most of the organizations and DBA can easily monitor / provide production support to 25-40 instances of SQL Server, which included daily

• Monitor Event Viewers
• Check SQL Server Errors
• Check Reporting Services Logs
• Monitor Perfmon counters
• Backup all databases
• check schedule sql jobs etc.

but I believe, without any tool (just using manual / Script) we can assume 1 DBA for every 25 servers is ideal number to focus more on preventive actions rather then corrective actions.

Is my data is safe, What all we are backing up for SQL Server Reporting Services ?

There are mainly three things, which should be backed up as part of reporting services backup

1. Report Server Databases, which can be backed up by SQL server backup and restore method. Check my previous Video post, “HOW to BACKUP SQL”
2. SQL Server Reporting Services Configuration, SQL Server Reporting Services Configuration is saved in config files, which can be copied as part of backup. look for other to know config files and there location.
3. Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.

What is the licensing cost for SQL Server Report Server?

SQL Server Reporting Services is bundled with SQL Server. So in case you have already purchased SQL Server Standard edition for your business then there is no additional cost for using this SQL Server Reporting Services.

Do I need to buy separate licenses for users who view reports ?

NO, there is no separate licenses for report delivery or viewing report on demand, i’’s all bundelled in SQL Server with once license cost.

Is there any free edition where I can use SQL Server Reporting Services ?

Yes, SQL Server 2005 Express Edition with Advanced Services support Reporting Services but there are several limitation, check my post,”What are the limitations in Reporting Services on SQL Server Express Edition“ for these limitations.

Thanks to :-  post