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.
Below picture explains the available system views, DMVs, Performance counters that are specific to Resource Governor only.
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.
Below is the cheat chart, which will give you a quick glimpse on most of the possible issues, that you face in your day to day SQL Server life. This chart also provides you with available SQL Server Admin tools from left to right in the top, to tackle the issues that are relevant to your problems. This also explains a good understanding on available SQL Server admin tools that can be used to tackle problems that you may be facing in your work environment. At least now you know where to start if you hit with any performance or other issues.
SQL Server 2016 Upgrade Advisor 1.2 allows you to run Stretch Database Advisor to find the tables within the selected database, whether qualified for database stretch or not. This SQL Server 2016 upgrade advisor also allows you to run below.
As we are very much excited with the improved version of Microsoft SQL Server CTP 3.0 release, below are some of the main benefits of this release towards the Stretch Database concept.
With the stretch enabled database, there are no updates required for any applications which we believe the change is required as the data has completed moved to Azure. Though the data has been moved to cloud, the table reference still has to the same local table.
As the data is moved to the cloud, you don’t have to keep expanding your local storages and can save money.
Though there is a bit of delay in accessing the data from the cloud, the performance has been still improved.
No more hassles in backing up and restoring huge databases which stores data locally. Now the data stretched to the cloud, you will only backup what is available in the local database files.
You can now easily manage Database stretch in GUI format within SSMS to pause, disable, enable, reconfigure, monitor, etc.,
CTP 2.0 did not allow connecting to already existing server in the cloud and this has been fixed in later releases.
Snapshots is a great feature within Microsoft SQL Server Reporting services, to see any of your reports at that point in time. The same report cosmetics might have changed recently as part of the change request and the data will be changing everyday. How did the report looked in terms of look and feel and the data that was latest same time last year? For this question a simple answer is the snapshots. Another simple example is your photo that was taken today and comparing the same with your own photo that was taken 5 years ago. Now do you see the difference? Yes, this is an amazing option we have within Microsoft SQL Server Reporting services for your reports.
By running these snapshots of your reports, it reduces the load on the databases directly, but any amazing feature may have its own negative side as well. It is always recommended to keep less than 8 to 10 snapshots for a report in the SSRS and download the rest as a PDF and delete the previous snapshots as the data and the layout are storing in the Report Server DB and this will unnecessarily bloat your SSRS system databases. Below is also a screenshot that explains more on Snapshots. You can also view a quick demo on snapshots in this link.
As you can see from the below slide, these are the identified issues and limitations that we have with the Stretch database within SQL Server 2016 CTP 3.0 release. A table that has more than 1023 columns or 998 indexes is not eligible to stretch it to Azure. Also the constraints like Check, Foreign Key, Default are also a huge restrictions placed on OLTP schema systems. This Stretch database concept highly works with OLAP based systems and /or if any table that is been isolated without any relations to anyother table. You can also check this article to identify whether any tables within your database is qualified for stretch or not.