Using DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2

DBCC CLONEDATABASE is a new DBCC command introduced in SQL Server 2014 SP 2 which is used for creating the clone of a specified user database which helps in troubleshooting the performance issues related to the query optimizer.

When a clone of the database is created using DBCC CLONEDATABASE, it will create a schema and statistics only copy of the specified database and does not contain any copy of the data.

Creating the clone is as simple as passing the source database name and clone database name to the DBCC command.

DBCC CLONEDATABASE ('SansSQL', 'SansSQL_Clone') 

Output of the DBCC Command

Once the cloning is completed, the cloned database will be in Read-Only mode.

SELECT name, database_id, is_read_only  
FROM sys.databases 
WHERE name in ('SansSQL', 'SansSQL_Clone') 

So what actually happens when we issue the DBCC CLONEDATABASE command on a database?
It will start with few validations before the clone is created, The following validations are performed by DBCC CLONEDATABASE. The command fails if any of the validations fail.

• The source database must be a user database. Cloning of system databases (master, model, msdb, tempdb, distribution database etc.) isn't allowed.
• The source database must be online or readable.
• A database that uses the same name as the clone database must not already exist.
• The command isn't in a user transaction.

If all the validations succeed, DBCC CLONEDATABASE will do the following operations:

• Creating primary data file and log file
• Adding secondary dataspaces
• Adding secondary files

The destination database files will inherit the size and growth settings from the model database and the file names of the destination database will follow the source_file_name _underscore_random_number convention. 

SELECT database_id, file_id, type_desc, name, physical_name 
FROM sys.master_files 
WHERE DB_NAME(database_id) in ('SansSQL', 'SansSQL_Clone') 

Then the DBCC CLONEDATABASE will do a Internal Database Snapshot with the following steps

• Validate the source database
• Get S lock for the source database
• Create snapshot of the source database
• Create a clone database (this is an empty database which inherits from model)
• Get X lock for the clone database
• Copy the metadata to the clone database
• Release all DB locks

Using the below command, we can check if a database is a clone or a normal database.

SELECT DATABASEPROPERTYEX('SansSQL','isClone') AS SansSQL_DB_CloneStatus 
      ,DATABASEPROPERTYEX('SansSQL_Clone','isClone') AS SansSQL_CloneDB_CloneStatus

Reference: https://support.microsoft.com/en-in/kb/3177838

SQL Backup Recovery Tool to Repair Damaged SQL Backup - Product Review - A guest Post by Daniel Jones

It has been commonly observed that many users are inclined to store the important and crucial data in SQL Server due to its reliable and consistent data storage feature. All the data is stored efficiently in data files i.e. MDF and NDF files. Unfavourable situations like server breakdown, system corruption etc can arise at any moment. The smart users prefer to take the backup of desired database, which can be restored during unexpected conditions. Many cases have been reported where the users are completely hopeless as their backup files (.bak) got damaged or corrupted due to reasons like virus attack or malware etc. No provision is provided by the server or operating system to recover data from such backup files. However, various third party tools promise to repair damaged SQL backup files without any data loss. One such well-known automated tool is SQL Backup Recovery Tool, which empowers to repair full database from corrupted/damaged backup file. Our data recovery experts have undergone through the software and tested it under the following environment:

• Operating System- Windows 8
• Mounted RAM- 2 GB
• Processor Used-2.5 GHz

The testing has been done in such a way that all of its features can be evaluated to determine the SQL backup recovery tool on the basis of quality, reliability, performance, security etc. The following section is focused to provide a transparent review of the tool on the basis of expert’s experience.

Functional Features of SQL Backup Recovery Tool

While working with the recovery tool in different working environments, the experts encountered several amazing features which differentiates it from other tools available in industry. Some of the major features, which makes the tool excel in recovery field are mentioned below:

Complete Backup File Recovery

The tool facilitates the recovery of all MDF and NDF files from the corrupted backup file i.e., various database components such as tables, views, functions, triggers, keys, indexes get recovered from the chosen backup file.

Dual File Scan Mode

The tool has been designed to offer scanning of damaged backup files in the following two modes:

• Quick Scan - This mode scans the backup file with minimum corruption
• Advance Scan - This mode scans the backup file with major corruption issues (highly damaged files)

The user can perform scanning in any of the mode, depending on file corruption extent.

Multiple Backup File Recovery

SQL backup recovery tool provides Multiple Backup File Options to let the user add multiple backup files or folder for data recovery. So if user faces an issue where multiple backup files got corrupted, this multiple backup option can be chosen to recover all data.

Preview Repaired SQL Data

Once the complete backup file is scanned, the tool provides preview of all repaired database components. The user can choose to preview all database component like tables, views, triggers, columns, functions, keys etc.

Batch export Recovered Data

The tool allows the user to export selected database components from the scanned data. The user can make selective choice to transport the required database objects from the backup file.

Multiple Export Options

The users are offered to export the repaired data in any of the following way:

• Export To SQL Server Database - It allows to export selected repaired data directly into SQL Server by providing database credentials.
• Export as SQL Server Compatible Script - It generates SQL script, which is compatible for any SQL Server version.

Export Schema Options

SQL backup recovery tool provides two export modes for the users. Depending on requirement, the user can choose to export data as:

• With only schema - It allows to export only with database schema for the backup file
• With schema & data - It allows to extract both data and schema for the backup file

Primary/Foreign Key Recovery

Along with the data recovery, the tool also helps to recover both primary and foreign keys of all the tables. So, we can say that the tool ensures to maintain data integrity of the data recovered from the backup file.

Perks of The Tool

In addition to above discussed features, the tool offers some additional features like auto-detection of SQL Server, no database size restriction, interactive user interface etc.

Specifications of SQL Backup Recovery Tool

The tool has been designed to work efficiently under the following hardware and software specifications:

• Operating System - It supports Windows 8.1 & all below versions
• Processor Requirement - Minimum 1 GHz is mandatory for tool installation. The experts suggest to use 2.4 GHz processor for faster processing.
• Mounted RAM - 512MB should be allocated at minimum. For larger storage, 1 GB can be used depending on requirement.
• SQL Server - It repair damaged SQL backup file of SQL Server 2014 & all the below versions

Available Versions of SQL Backup Recovery Tool

The tool to repair damaged SQL backup file can be availed in two versions:

• Demo Version - Users can download the free version from company’s official website. It offers scan and preview of the backup files.
• Licensed Version - The paid version of the tool can be purchased from company’s website. It allows to scan, preview and export the selected backup file

PROS

• The tool provides recovery of backup files even without SQL Server installation on the system.
• The tool offers the recovery of highly corrupted backup files using advance scan.

CONS

• The generated scanned files cannot be saved on the local system, which creates need for rescanning in future use.

Conclusion

After working on various aspects of the software, we can conclude that SQL Backup Recovery tool fulfils all the expectations to repair damaged SQL backup file. The users can go for this tool for an efficient bak file recovery within few easy steps. The tool can be rated as 9.8 on the scale of 10 as it provides an excellent approach to repair the corrupted or damaged backup files using its interactive user interface.

About Daniel Jones

Daniel Jones is a SQL Server DBA and contributor at SQL Tech Tips. Having 2 + years of experience in SQL recovery and system infrastructure.

Social Media Profiles
Facebook: https://www.facebook.com/danieljones05
LinkedIn: https://www.linkedin.com/in/daniel-jones-5bb87a115 

How to use SSRS reports as Data Source in Excel

As we all know SSRS is a server-based reporting platform that allows to create and manage a wide variety of reports, and deliver them in a range of formats, but many of them are still addicted to the excel for reporting as it is more convenient and easy to use.

In this post, let us learn how to use SSRS report as Data Source for Excel Reporting.
First of all, we need to enable Data Analysis in Excel and then continue importing data from SSRS into Excel.

Go to "Data" tab and Click "manage Data Model".

On the pop-up click "Enable" to enable the data analysis add-ins.

In the Power Pivot for excel pop-up, click on "Get External Data" and choose "From Other Sources".

Choose "Report" in the "Connect to a Data Source" pop up.

Next, Choose the Report Path which you want to use as the source.

In the Name box, type in the ReportServer URL and choose "Open".

Then Navigate to the rdl file.

Choose the rdl file and click "Open".

Once the rdl file is chosen, you can preview the report and click next.

Then choose the data table/view under the report and click "Finish".

Click Close on the successful completion of the import.

This is now import the data into the excel for further reporting.

Now go back to the excel and choose "Pivot Table" from the "Insert" tab.

And choose the Report connection that we previously imported.

Then choose where to create the new Pivot table.

Now you can create your own reports out of this data using the excel which is more convenient for you.

Embed SSRS Report into PowerPoint Slides

Presentations are one major activity in each and everyone's career. There are different ways of presenting your information, some does it just by showing the static data and some wants to do it with the Live data. We are the people who are of type 2, presenting the live data. But when it comes to presenting live data, it is through reports.
How beautiful it will be if we have the option to interact with the Live data from a PowerPoint Presentation?
In this post, we shall learn how to embed SSRS report into PowerPoint Slides.

Before we begin, we need

1. A Web Viewer add-in for PowerPoint
2. Report URL to embed in the PowerPoint

Open a PowerPoint Presentation, go to "Insert" Tab and Click on "Store"

Now search for "web viewer" add-in and Click "Add". This is an add-in from Microsoft.

Once you click "Add", this is add the add-in into your slide and it looks like this

Now Insert your SSRS Report Server URL into the URL box and hit "Preview". 

This was my URL https://sql01.sanssql.com/ReportServer/Pages/ReportViewer.aspx?%2fAdventureWorks+Sample+Reports%2fCompany+Sales&rs:Command=Render&rc:Toolbar=False 

And that's it, Now your Slide will Load the data from SSRS Report.

Now you can interact (Drill Down or Drill Through, etc...) with the data depending on the report type. 

If you want to show a static image, then click on the small left arrow on the top right of the add-in and choose "Show as Saved Image" and this is display the data in the image format.

Now your presentations will be more fun with the Live Data.

How to Enable Remote Errors in SSRS

Enabling Remote Errors includes external error information (for example, error information about report data sources) with the error messages that are returned for users who request reports from remote computers. By default the remote errors in SSRS is set to false, which means error messages in a multi-server environment are not detailed.

Enable Remote Errors by using the Report Server Properties

1. Open SQL Server Management Studio (SSMS) and connect to the Report Server
2. Right-Click on Report Server and Choose "Properties"
   
3. Navigate to the "Advanced" Page and set the "EnableRemoteErrors" to "True" and Click "OK"
   

Enable Remote Errors by Modifying the ConfigurationInfo table

USE ReportServer
GO
UPDATE ConfigurationInfo
SET [Value] = 'True'
WHERE Name = 'EnableRemoteErrors'

Note: If you modify the setting in the database, you need to restart the Reporting Services service before the changes take effect.

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)

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.

Reference: https://sqldusty.com/2016/01/06/download-the-power-bi-architecture-diagram/

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.