Tuesday, March 31, 2015
Tuesday, March 24, 2015
SSRS: Form based Authentication
Refer Source: SSRS 2012 Forms Authentication
Single sign on to ssrs reports from wpf application.
Reports would be deployed to Report Server, WPF application can access reports without asking for logon.
Forms authentication set up:
ssrs supports windows authentication mode. To access other domain need forms authentication.
Important:
Make backup copies , before making changes.
<InstallationLocation> is "C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services"
Report Server Files Modification:
To modify RSReportServer.config file:
"RSReportServer.config" file can be found in <InstallationLocation>\ReportServer.
1.Locate <AuthenticationType> element, change as mentioned in the source article.
2.Within <Extensions> element, locate <Security> and <Authentication> , change as mentioned in the source article.
3.Locate <UI> element
To modify "RSSrvPolicy.config":
Add a Code group for custom security extension that grants "FullTrust" permission to your extension.
Located in <InstallLocation>\ReportServer directory.
To modify the Web.config file for Report Server :
Located in <InstallLocation>\ReportServer directory.
In ssrs authentication begins when request connect to report server is required also occurs when report server itself want to access application database server or any external data source.
To modify the Web.config file for Report Server :
Located in <InstallLocation>\ReportServer directory.
CREATING USER ACCOUNTS IN DATABASE:
....
Fig1. authentication when SSRS running in native mode.
Client:
Client ->
custom security
Client authentication
SSRS:
SSRS
Windows Integrated Security
User-Supplied Credentials
Stored Credentials
No Credentials ( none attended credential account)
Windows Sql Server Service Account
Reporting Services
Service Authentication
External Data Sources:
External data source authentication
Client Authentication ( Native Mode ):
User activities that require connection to report server
User connect to report server by opening a browser & connects to report manager to view report or manage server properties for report.
User may need to deploy report using BIDS or Server administration using SSMS or to use
command line utility and report rendering using url access.
If a custom application can access the report server using reporting services web service.
Before getting access to report server, it requires "client" to authenticate the user or process
which wants to have access.
Client passes the information to report server using HTTP authentication request.
Report server in turn send the request to windows authentication extension ( which is default ) or custom security extension.
If authentication fails it shows the message 401 access denied to client application.
Windows Authentication Extension:
User need to have a windows account as a local account or a trusted domain user
account and that need to be in report server as an individual account having access to report server or it can be part of a group account that has access to report server.
The client application should support the windows integrated security.
Windows authentication extension supports many types of authentication, SSRS it is
specified in the RSReportServer.config file, under ReportServer in the installation directory.
RSWindowsNegotiate:
If you set up windows service account for the report server to networkservice or
localsystem in the reporting services manager, RSWindowsNegotiate is added to
the the RSReportServer.config file as the default setting.
....refer source
Saturday, March 21, 2015
Top 20 exciting features of SQL Server 2012
Refer Source:Top 20 exciting features of SQL Server 2012 – Part 1
Feature number 1 (Revolution):- Column store indexes
Feature number 3 (Revolution):- Pagination
There are instances when you want to display large result sets to the end user. The best way to display large result set is to split them i.e. apply pagination. So developers had their own hacky ways of achieving pagination using “top”, “row_number” etc. But from SQL Server 2012 onwards we can do pagination by using “OFFSET” and “FETCH’ commands.
...
Feature number 4 (Revolution):- Contained database
This is a great feature for people who have to go through pain of SQL Server database migration again and again. One of the biggest pains in migrating databases is user accounts. SQL Server user resides either in windows ADS or at SQL Server level as SQL Server users. So when we migrate SQL Server database from one server to other server these users have to be recreated again.
...
Feature number 5 (Evolution):- Error handling
Columnstore Indexes
SQL SERVER – Fundamentals of Columnstore Index
Refer Source: Columnstore Indexes
...
A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. SQL Server supports both clustered and nonclustered columnstore indexes. Both use the same in-memory columnstore technology, but they do have differences in purpose and in features they support.
Benefits
Columnstore indexes work well for mostly read-only queries that perform analysis on large data sets. Often, these are queries for data warehousing workloads. Columnstore indexes give high performance gains for queries that use full table scans, and are not well-suited for queries that seek into the data, searching for a particular value.
Columnstore Index benefits:
- Columns often have similar data, which results in high compression rates.
- High compression rates improve query performance by using a smaller in-memory footprint. In turn, query performance can improve because SQL Server can perform more query and data operations in-memory.
- A new query execution mechanism called batch-mode execution has been added to SQL Server that reduces CPU usage by a large amount. Batch-mode execution is closely integrated with, and optimized around, the columnstore storage format. Batch-mode execution is sometimes known as vector-based or vectorized execution.
- Queries often select only a few columns from a table, which reduces total I/O from the physical media.
Columnstore Versions
SQL Server 2012, SQL Server 2012 Parallel Data Warehouse, and SQL Server 2014 all use columnstore indexes to accelerate common data warehouse queries. SQL Server 2012 introduced two new features: a nonclustered columnstore index and a vector-based query execution capability that processes data in units called "batches." SQL Server 2014 has the features of SQL Server 2012 plus updateable clustered columnstore indexes.
...
DBCC FREEPROCCACHE (Transact-SQL)
Refer Source: DBCC FREEPROCCACHE (Transact-SQL)
..
Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.
...
..
Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.
...
Upgrading DTS Packages to SSIS - 2012
Upgrading DTS Packages to SSIS
DTS packages
-------------------
Option1:
------------------
To have dts packages backward compactability with 2005,2008 , so that the dts can run. The components that need to be installed
Step 1: ->
Microsoft Download Center:
Feature Pack for Microsoft Sql Server 2005
search on : Backward Compatiability
Scroll down on it.
Microsoft 2005 Backward compatibility components.
This one will install the backward components for 2005,2008.
Here the 90% work is done.
Step 2:->
Now need the DTS package Designer. This will allow to open the DTS packages in 2005, 2008 Sql Server.
This only works in 32 bit mode.
Microsoft 2000 Sql Server DTS Designer Component.
If your machine is 64 bit machine or 2008 machine it has issues
This is one is only for 32 bit machine and for 2005.
This ddl is copying files to wrong directory i.e. /80/ for it to work for 2008 & 2008 R2 copy to /100/
Go to SSMS of sql server & right click then you can open packages.
It would error out saying you do not have sql server 2008 R2 backward compatibility.
The copy script copy for 2008 R2 it need to copy to 105 not /100/.
Right click on DTS -> Import Packages ->
-------------------
Option2:
------------------
Upgrade with Microsoft Package Upgrade Wizard.
-Built Into Sql Server 2005/2008.
-Pros:
-Free
-Work on Simple Packages
-Cons:
- Does not handle ODBC
- Only handles a few types of text file use cases
- No Dynamic Properties Task
- No UDL or legacy database support in data pump
-Packages only have about a 20% chance of working
Demo how it works:
In BIDs, in the solution explorer, right click on SSIS packages,
"Migrate DTS 2000 Package"
Wizard will prompt to choose
Sql Server
or " Structured Storage File"
-There are some changes like the Script window neatly done
- But another ,
Sample Upgrade Project Plan:
-Scope and Number of Packages
-How long will it take you to migrate each type of task
-Use a tool to migrate
-Upgrade the Activex Script Task logic
-Test, test and test
Source: Free Sql Training - Upgrading DTS Packages to SSIS
DTS vs SSIS Engine Speed Test
- On 32 bit dual core machine
-Pulling 1 million rows out and writing to SQL table
with no transformation
-SSIS 65% + faster than DTS
-Adding transformation would add more SSIS advantage
DTS SSIS SQL Server SSIS OLE DB
Average Runtime Destination Destination
(Seconds) 33.2 s 11.3 s 12.3 s
Designing Packages:
DTS Designer
DTS 2000 Package Designer
Create Table -
There is not whole lot to it. You have connection,Task.
For SSIS package, In BIDS , it's in Microsoft Visual Studio and whole lot of features to it.
It's all XML , so you can view it. You may not want to change it.
Comparing DTS to SSIS Objects
-Transform Data Task
Here just from a file you put into the destination , there is nothing much
to transformation. Seems you have to write Activex to do that.
But in SSIS, there is totally a "Data Flow Task" where you have lot
transformation and no need to write Activex.
Connections:
DTS Connections:
Every time you want to grab the data from a file to destination you have
to every time specify the connection.
In SSIS it's changed.
Connection Managers:
You can put your connection & you can call these connections through out entire package.
To see DTS package, you need to log on to "SSMS" & choose "Management" then "Legacy" then "Data Transformation Services"
open it &
just can see the file & the connection & Destination but can not
interact with them.
DTS Migrations
DTS Package Profiler Promo
Free SSIS Training - Upgrading DTS Packages to SSIS
DTS Package Profiler by Pragmatic Works
-Test, test and test
Source: Free Sql Training - Upgrading DTS Packages to SSIS
DTS vs SSIS Engine Speed Test
- On 32 bit dual core machine
-Pulling 1 million rows out and writing to SQL table
with no transformation
-SSIS 65% + faster than DTS
-Adding transformation would add more SSIS advantage
DTS SSIS SQL Server SSIS OLE DB
Average Runtime Destination Destination
(Seconds) 33.2 s 11.3 s 12.3 s
Designing Packages:
DTS Designer
DTS 2000 Package Designer
Create Table -
There is not whole lot to it. You have connection,Task.
For SSIS package, In BIDS , it's in Microsoft Visual Studio and whole lot of features to it.
It's all XML , so you can view it. You may not want to change it.
Comparing DTS to SSIS Objects
-Transform Data Task
Here just from a file you put into the destination , there is nothing much
to transformation. Seems you have to write Activex to do that.
But in SSIS, there is totally a "Data Flow Task" where you have lot
transformation and no need to write Activex.
Connections:
DTS Connections:
Every time you want to grab the data from a file to destination you have
to every time specify the connection.
In SSIS it's changed.
Connection Managers:
You can put your connection & you can call these connections through out entire package.
To see DTS package, you need to log on to "SSMS" & choose "Management" then "Legacy" then "Data Transformation Services"
open it &
just can see the file & the connection & Destination but can not
interact with them.
DTS Migrations
DTS Package Profiler Promo
Free SSIS Training - Upgrading DTS Packages to SSIS
DTS Package Profiler by Pragmatic Works
Friday, March 13, 2015
Reporting Services (SSRS) - WiseOwl Tutorials
.rdl - extension for report
sql server BIDs - Sql Server 2008,2008R2 - Visual Studio 2008
sql server Data Tools - Sql Server 2012 - Visual Studio 2010
some basic formatting in this lesson.
Refer Source:
Reporting Services (SSRS) Part 2 - Data Sources and Datasets
While creating a report , one need it to point to the Source of the data
i.e. the "Data Source" , then in the report we show column values
that is the "Data Set".
Shared Data Source:
create a shared data set , specify the "Server" &
the "Database" where data resides.
Shared Data Set:
Here you specify the "Shared Data Source" name which gives the connection to "Server" & "Database"
Here one can specify a "Stored Procedure" name, "Query" or use the "Query Builder" where one sees the Table graphically and build the query.
Reporting Services (SSRS) Part 2 - Data Sources and Datasets
1. Defining "Data Source" & "Data Set".
While creating a report , one need it to point to the Source of the data
i.e. the "Data Source" , then in the report we show column values
that is the "Data Set".
Shared Data Source:
create a shared data set , specify the "Server" &
the "Database" where data resides.
Shared Data Set:
Here you specify the "Shared Data Source" name which gives the connection to "Server" & "Database"
Here one can specify a "Stored Procedure" name, "Query" or use the "Query Builder" where one sees the Table graphically and build the query.
2.
Right hand side in the "Report Data"
Data Set: Add Data Set, specify the shared data source name , then specify the shared data set
, then data set elements will appear i.e the column names , in the report designer, put a control i.e. Table then one
can drag those fields and format & create the desired report
Note: While using Shared Data Source , if anything is changed it changes for all report using it.
3."Embedded Data Source" & "Data Set"
If one want to have the data source defined in the report, in the Report Item side , right click on
Data Source and add a Data Source , one can point to the above Shared Data Source.
Similarly in the Data Set , specify the above embedded Data Source name.
Refer Source:
Reporting Services (SSRS) Part 3 - Working
with Tables
Interactive Sorting:
At the runtime if you need to set up sorting by user, in the report design
view, click on the column & right click & choose "Textbox Properties" then choose "Interactive Sorting".
Filter:
If one adds filter to the DataSet on the Report Data , all reports using that
Dataset will be affected.
Rather in design on that report , choose fields highlighted then click on the top square box clicking will show Tablix property add a filter to that specific report.
Page Header to show in each page:
Row Groups Column Groups
First in the Column Groups - click on "Advanced Settings" , under "Row Groups" it would appear
"static","static" and each of these corresponds to a heading.
Row Groups Column Groups->"Advanced Settings"
Static
Static
Details
Click on "Static" choose the property "RepeatOnNewPage" to true.
Refer Source:
Static - Repeat On New Page (True)
Row Group
while scrolling to show the header:
Static - Repeat On New Page (True) & FixedData- True, back ground color ( white).
Refer Source:
Reporting Services (SSRS) Part 5 - Controlling the number of rows per pageIn the report to show the Row Number , in a column right click choose
"Expression" , =RowNumber(Nothing)
This report will show no. of rows as needed. But to configure to show a
desired one, click on Report menu-> here can change the "height".
Controlling the number of rows in a accurate way:
Row Groups Column Groups
Details -> Add Group -> Parent Group
In the "Group By" dialog box , instead of a field name use an "Expression",
Ceiling(RowNumber(nothhing)/10)
10 - number of rows one want to see in a page.
1/10 - 0.1 ( nearest integer is 1 , that ceiling function gives so till 10 rows
reaches it gives 1, it shows 10 recs. in one page.)
By doing grouping it adds a sort, clicking on Row Groups-> choosing "Group1" - deleting the sorting in it removes the error message.
In Design just Delete the column Group not the Group.
Then to have a "Page Break", choose the "Group1" properties choose
"Page Break".
If you want user to specify how many recs. it want to see
Using Parameter:->
Under "Report Data" , create a "Parameter" , give a name, can set a "Default value"
Next need to associate it to group as group expression has 10, that
need to point to this parameter value.
Ceiling(RowNumber(nothhing)/Parameters!RowNumber)
Refer Source:
Reporting Services (SSRS) Part 6 - Grouping in Tables
So grouping can be done by under Row Groups in details by "Adding Groups"
Row Groups Column Groups
Details -> Add Group
In the design window "Table" control is added and it shows the group by
column name and the other columns that is dragged from dataset.
Now in design window it shows a bracket and = sign for all those 3 rows.
Row Groups Column Groups
Director Name
Details -> properties ( Hidden - True , ToggleItem - DirectorName1)
Row Groups Column Groups-> Advanced Option(True)
Director Name
Static-> properties ( Hidden - True , ToggleItem - DirectorName1)
Details -> properties ( Hidden - True , ToggleItem - DirectorName1)
Static-> properties ( Hidden - True , ToggleItem - DirectorName1)
Create Nested
Row Groups Column Groups
Director Name-> Add a Group ->Parent Group->Country Name
Static
Details
Static
Refer Source:
Reporting Services (SSRS) Part 7 - Calculated Fields
Data Set -> right click ->Calculated Column ->
Refer Source:
window, choose "BackGround" Color, in their below "Expression" is there.
There is Iff,Switch
Alternate Row coloring:
Using the above same "Expression" , under Misc "RowNumber" function
Mod func., will find the even number of rows, if the number leaves a remainer of 0)
Refer Source:Reporting Services (SSRS) Part 9 - Indicators
Toolbox , choose "Indicator" & drag to an empty cell, then one need to specify which
number it indicates too. When you click on the Indicator , it shows the "Gauge Data"
Panel, choose "Unspecified", it shows the field names then pick the field.
In the "Indicator" , if you right click choose "Properties"
Refer Source:
Refer Source:
Reporting Services (SSRS) Part 17 - Linked Reports
Parameter
Report - Action
Refer Source:
Reporting Services (SSRS) Part 23 - Report Variables and Group Variables
Instead of now() function , the report variable will hold the time and show the value a specific value unlike now() giving
the exact value of time.
Report variable - Report ,properties
Group variable - create a group - properties
Refer Source:
Reporting Services (SSRS) Part 24 - Report Templates
To copy the template file: from : project folder
To:
Program file or x86...
if sql server 2008r2 reporting services, Microsoft Visual Studio 9.0
if sql server 2012 then Microsoft Visual Studio 10.0
\Program file\rightversnofvisualstudio\common7\IDE\PrivateAssemblies\ProjectItems\ReportProject\template.rdl
Now it will so in the folder from where you choose Report Project.
Refer Source:
Refer Source:
Reporting Services (SSRS) Part 26 - Creating a Custom Assembly
Visual Studio 2010 - project type - Installed Template - Visual Basic - Class Library
.NET Framework 3.
.NET Framework 4.0 ( is not supported)
Get syntax, by first coding in visual stuido then pasting in "
Report properties - code"
Refering the code start with "code."
Make a template having this code and copy to same location as it's mentioned for Template.
Refer Source:
search: Visual Studio Express
Express 2013 for windows Desktop
or
Visual Studio 2010 Express
Create Class Library project:
Template: Visual Basic
Class Library
(Use: NET Frame Work 3.5)
After creating a function which formats a date , the project is compiled & by that a DLL is created.
The same function is created in C# & vb.
DLL location:
Project-properties->compile ( output path: bin\debug\)
copy the file.
Paste location can be for Report Designer or when the report deployed the location of that.
Report designer location : c:\Program Files\Visual Studio 10\Common7\IDE\PrivateAssemblies\
Copy to the Report Server : C:\Program Files\ Microsoft Sql Server 2012\MSRS11.SQL2012\Reporting Services\ReportServer\bin\
For testing:
1) In the designer:
Create a new report , right click in the "References" , specify the dll location & in the report put
text field, check the expression
2) In the Report Server: Deploy the report by clicking on the "Deploy" option in the designer.
i.Navigate Report Manager url, click on the project and then click on the report and the result shows right.
Note:
While building the functions in the visual studio 2012 , instead of copying the dll to the deginer location & report server folder , it can be automated in the visual studio function project , compile,
"PostEvent" , there specify the copy /y "$target" specify the location to copy to, so every time with
successful compile it will copy it.
Get syntax, by first coding in visual stuido then pasting in "
Report properties - code"
Refering the code start with "code."
Make a template having this code and copy to same location as it's mentioned for Template.
Refer Source:
Reporting Services (SSRS) Part 26 - Creating a Custom Assembly
search: Visual Studio Express
Express 2013 for windows Desktop
or
Visual Studio 2010 Express
Create Class Library project:
Template: Visual Basic
Class Library
(Use: NET Frame Work 3.5)
After creating a function which formats a date , the project is compiled & by that a DLL is created.
The same function is created in C# & vb.
DLL location:
Project-properties->compile ( output path: bin\debug\)
copy the file.
Paste location can be for Report Designer or when the report deployed the location of that.
Report designer location : c:\Program Files\Visual Studio 10\Common7\IDE\PrivateAssemblies\
Copy to the Report Server : C:\Program Files\ Microsoft Sql Server 2012\MSRS11.SQL2012\Reporting Services\ReportServer\bin\
For testing:
1) In the designer:
Create a new report , right click in the "References" , specify the dll location & in the report put
text field, check the expression
2) In the Report Server: Deploy the report by clicking on the "Deploy" option in the designer.
i.Navigate Report Manager url, click on the project and then click on the report and the result shows right.
Note:
While building the functions in the visual studio 2012 , instead of copying the dll to the deginer location & report server folder , it can be automated in the visual studio function project , compile,
"PostEvent" , there specify the copy /y "$target" specify the location to copy to, so every time with
successful compile it will copy it.
Subscribe to:
Posts (Atom)