ByDr. SubraMANI Paramasivam

SQL Server 2016 CTP 3.1 is out now

Microsoft keeps improving the SQL Server 2016 in every CTP version and the latest CTP 3.1 has been released and can be downloaded in this link.

SQL Server 2016 Technical documentation is available here.

As usual, the downloads are available both in ISO & CAB formats.


ByDr. SubraMANI Paramasivam

Enable, Disable Stretch Database

You can now easily enable & disable stretch database both in GUI via SSMS and also using T-SQL. Below screens provides you on each step involved in enabling the Stretch for the database.


Enable_StretchDB02 Enable_StretchDB03 Enable_StretchDB04 Enable_StretchDB05 Enable_StretchDB06 Enable_StretchDB07 Enable_StretchDB08 Enable_StretchDB09

You can easily disable stretched database using below T-SQL.


ByDr. SubraMANI Paramasivam

Microsoft SQL Server 2016 Upgrade Advisor released

Microsoft have now released its latest SQL Server 2016 Upgrade Advisor Preview 1.3 and you can access it here.

This tool is not part of SQL Server 2016 CTP3.0 and have to be done additionally. This tool advises to upgrade below scenarios.

  1. Stretch Database
  2. Database upgrade analyzer
  3. Analysis Services Upgrade advisor
  4. Integration services upgrade advisor
  5. Reporting services upgrade advisor.
ByDr. SubraMANI Paramasivam

Resource Governor – Default settings

How does the resource governor default settings look like? Below is a simple screenshots taken from SSMS and also from the resource governor properties.

RG_Default_01 RG_Default_02

How does the resource governor would look like after configuring a successful resource governor? This can be seen from the below screenshot, which shows four resource pools and workload groups configuration, which has dedicated lanes for Applications, Reporting systems, Developers and Business users.




ByDr. SubraMANI Paramasivam

Linked reports in SQL Server Reporting Services

Linked reports is a great feature and helps in easy maintenance. You can easily avoid any duplicity of the report just for the sake of security. For example if a report has got 5 regions like Europ, NA, Asia, China, SA. You don’t need 5 different versions of report and instead you can have one single report with report parameters pointing to region and easily grant access to relevant AD groups to access their own regional reports. Any single change made in the main RDL file will immediately reflect in other regions as well and no need to make the same change in each regions. Imagine if you have 50 departments and if you are in this situation, linked reports concept a real handy one.

A quick demo is available here.


ByDr. SubraMANI Paramasivam

SSRS – Report Builder a closer look

Report builder is a browser based report authoring tool to enable any end users to start building / modifying existing reports (if they have enough permissions) without the need of any installation of BIDS or SSDT. This tool is available via local installation of Report manager. This is a light weight tool that will enable you to definitely build a decent report and you can deploy it directly to the report manager. You can start building this report against SQL Server database, SQL Azure, Oracle and others. Below image also explains more on Report builder tool and also try watching a quick video on this here.



ByDr. SubraMANI Paramasivam

Tiny bits about Resource Governor and of course my favorite

Resource Governor helps sorry saves DBA’s life in terms of performance improvement by deriving proper lanes on what applications or which service accounts should have high horse power when it comes to CPU & I/O usage. This feature was introduced in SQL Server 2008 and made available in Enterprise, Developer and Evaluation editions only. This has got the ability to classify connections and route the workload to specific already derived lanes by DBA as per the business requirements. This is limited to SQL Server Engine only and self contained and instant specific only. This has got 3 main below concepts.

  • Resource Pool
    • Represents physical resource
  • Workload Group
    • Categorizes the sessions and routes to relevant resource pool
  • Classification
    • A function to decide the sessions to allocate the relevant group

Below picture explains the available system views, DMVs, Performance counters that are specific to Resource Governor only.


ByDr. SubraMANI Paramasivam

Running Stretch Database Advisor against AdventureworksDW2008R2 OLAP database

This article gives a quick glimpse on how to run SQL Server 2016 Upgrade Advisor Preview 1.2 against AdventureWorksDW2008R2 OLAP database. Once you shoot the Upgrade Advisor, go to Scenarios and then select the first option “Run the Stretch Database Advisor”. Following this, you will get below screen and then click on Select Databases to Analyze. This will then shoot the blade option to select the databases but requires you to connect to which instance the database is located. In the below example I am connecting to my YSMLab01\SQL2016 instance.


After choosing the instance, I am selecting AdventureWorksDW2008R2 database and clicking on Run button.


Following the analyzed database of AdventureWorksDW2008R2, it has identified the database size and total number of tables being analyzed. By clicking on this result set it further displays the list of tables with status on eligibility to cloud or not. If it has errors then it gives out the reasons on why it is not selected in blade format. From below screenshot, you can see that the first table has been identified to be ready for stretch with a green tick mark, where as the next table is not eligible.




You can see the same results when the stretch database advisor is run against AdventureWorks2014 OLTP database here.