SansSQL

Monday, June 20, 2016

List all Shared Data Sources

Here is a handy script to list all the Shared Data Sources from the ReportServer Database.
WITH BasicData AS 
(
SELECT CONVERT(VARBINARY(MAX),Content) AS Content,
[Catalog].Name,[Path],ConnectionString FROM [Catalog] INNER JOIN DataSource
ON [Catalog].ItemID=DataSource.ItemID
WHERE [Type]=5 -- Type=5, Filters Shared Data Sources
)
,DataWithXML AS
(
SELECT 
Name, [Path], CONVERT(XML,Content) AS ContentXML 
FROM BasicData
)
SELECT Name, [Path], ConnectionString.value('(text())[1]','nvarchar(max)') AS ConnectionString
FROM DataWithXML 
CROSS APPLY DataWithXML.ContentXML.nodes('//*:ConnectString') AS ConnectionStrings(ConnectionString)

Related Posts Widget For Blogger with ThumbnailsBlogger Templates

Monday, March 21, 2016

An Introduction to Microsoft Power BI

Microsoft Power BI transforms your company's data into rich visuals for you to collect and organize so you can focus on what matters to you. Stay in the know, spot trends as they happen, and push your business further.

What is Power BI

Power BI is a suite of business analytics tools to analyse data and share insights. Monitor your business and get answers quickly with rich dashboards available on every device.
Power BI dashboards provide a 360-degree view for business users with their most important metrics in one place, updated in real time, and available on all of their devices. With one click, users can explore the data behind their dashboard using intuitive tools that make finding answers easy.
Dashboards can be created from over 50 plus types of sources like Microsoft Excel, SSAS, Access, Azure, Facebook, etc… and can access the data and reports from anywhere with the Power BI Mobile apps.
Using the Power BI gateways, you can connect to your organisations on premise SQL Server databases, Analysis Services models, and many other data sources to your same dashboards in Power BI.

Architecture of Power BI

The high level Power BI architecture look like below with the different components connecting with each other to give you the power of data visualisation.

Thursday, February 18, 2016

Configuring AlwaysOn Availability Groups - Part 2

In my previous post we have learnt on how to configure the failover clustering. This is the first step and an important pre-requisite in configuring AlwaysOn Availability Groups.
In this post we will learn how to Enable and Configure the AlwaysOn Availability Groups using SQL Server 2016.

Before we start configuring the AlwaysOn, we need to enable this Feature.

Open "SQL Server configuration Manager"
Right-Click on “SQL Server” Service and click on “Properties”

Go to “AlwaysOn High Availability” Tab and Check the box “Enable AlwaysOn Availability Groups” and Click OK.


To Create new Availability Group
Open SSMS and connect to the SQL DB Engine.
Expand “AlwaysOn High Availability”
Right-Click on “Availability Groups” and click on “New Availability Group Wizard”


This is will open up the New Availability Group Wizard


Give a name to the Availability Group and choose the required options and click Next.


In this page, choose the databases that you may want to be part of this Availability Group.
Additional Databases can be added later as well.


In the next page, Add the Replica SQL Server instances and configure Endpoints, Backup Preferences and Listener for this Availability Group



In the next page, specify the Synchronization preference.


In the next step, the wizard will validate the configurations done so far


Review the Summary page and Click Finish to start the Availability Group Configuration



Successful completion of this wizard will create the new Availability Group with the specified databases, endpoints and the listener.