MS SQL Server

ByDr. SubraMANI Paramasivam

SQL Server Issues and the available tools

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.

IssuesVsTools

ByDr. SubraMANI Paramasivam

SQL Server 2016 Upgrade Advisor – Preview 1.2

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.

  • RUN DATABASE UPGRADE ANALYZER
  • ANALYSIS SERVICES UPGRADE ADVISOR
  • INTEGRATION SERVICES UPGRADE ADVISOR
  • REPORTING SERVICES UPGRADE ADVISOR

SQLServer2016_UpgradeAdvisor1_2

ByDr. SubraMANI Paramasivam

SQL Server 2016 CTP 3.0 Benefits on Stretch Database concept

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.

SQL2016_CTP30_Benefits

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.

ByDr. SubraMANI Paramasivam

System Resources

Below are the main system resources and are the key elements for better performing server.
CPU: We definitely require more than one CPU to perform multiprocessing and helps any program to be healthy enough with all I/O operations.
RAM: Even if you are not a DBA, you should be aware of the memory that is allocated for your SQL Server instances. Memory Sizing can be done based on the number of transactions that is going to hit the server. Any environment having high transaction should have highly allocated memory to process multiple operations in parallel.
Disk: Disk is the storage area where every single transaction stores and retrieves data from as part of the request from applications, users. Having good amount of understanding and knowledge on database & log files and their location, on these sort of storages is a must, even for non DBA’s. Monitoring and maintaining the data storage, RAID levels, etc., are some of the key activities for every single DBAs.
I/O:  Any Input and output to and from applications/users is calculated with the I/O level. As part of the user or application’s request the I/O part plays a major role and any performance issues will affect the time taken to retrieve the data.
SystemResources
ByDr. SubraMANI Paramasivam

Issues & Limitations of using Stretch database in SQL Server 2016 CTP 3.0

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.

SQL2016CTP3_Issues

ByDr. SubraMANI Paramasivam

Running Stretch Database Advisor against AdventureWorks2014 OLTP database

This article gives a quick glimpse on how to run SQL Server 2016 Upgrade Advisor Preview 1.2 against AdventureWorks2014 OLTP 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.

RunStretch_01

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

RunStretch_02

Following the analyzed database of AdventureWorks2014, 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.

RunStretch_03

In here, I am further trying to find the problem on why Person.Person was not selected and one of the reasons is because of Default Value Constraints on one or more columns cannot be part of stretch database.

RunStretch_04

You can see the same results when the stretch database advisor is run against AdventureWorksDW2008R2 OLAP database here.

ByDr. SubraMANI Paramasivam

SQL Server Version, Release Year and Codenames

In the Data Awareness Programme to students, I have shown below slide to let them know on how many versions have released, so far. As you can see below, the first ever version released was in 1989 and we are in 2014 now. SQL 2016 CTP versions are available but expected to release in beginning of 2016. There were totally 13 releases in last 25 years and the product has become very much stronger than ever. SQL 2016 is going to be a huge one with easy integration to cloud.

SQLServer_Versions_Codenames

ByDr. SubraMANI Paramasivam

SQL Server Features from 2000 to 2014

In my Data Awareness Programmes, I also spread the available SQL Server features from 2000 to 2014 in below slide formats. This will also give a quick glimpse on all SQL Server releases with available features in one single article. Note: These slides covers only a noticeable features and does not cover every single feature on it.

SS2000_Features SS2005_Features SS2008_Features SS2008R2_Features SS2012_Features SS2014_Features