MS SQL Server

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

ByDr. SubraMANI Paramasivam

SQL Server Editions from 2000 to 2014

In my Data Awareness Programmes, I also spread the available SQL Server Editions from 2000 to 2014 in below slide formats. This will also give a quick glimpse on all SQL Server releases with available Editions in one single article.

SS2000_Edition SS2005_Edition SS2008_Edition SS2008R2_Edition SS2012_Edition SS2014_Edition

ByDr. SubraMANI Paramasivam

All about Storage

In DBA Best practise training, I also cover the storage bits. This plays a vital role in IO and performance of every single application or user’s ad-hoc querying. As we know a database should have one MDF (Main Database File), 1 LDF (Log Database File) and can have more than 1 NDF (Secondary Database File). These files when stored across various drives improves the performance of the applications in one way. All data stored in tables are actually getting stored in these files. SQL Server gives flexibility, to store these data in both physical and logical components.

AllAboutStorage_01

As you can see in below slide, the basic unit of storage is stored in these MDF, NDF via File Group concept, where you dedicate one filegroup location for one database file. Also this slide covers the concept of extents and its types (Mixed & Uniform).

AllAboutStorage_02

 

AllAboutStorage_03

ByDr. SubraMANI Paramasivam

Performance Analysis

When a bottleneck occurs due to performance problem, every DBA should start with initial analysis on why and where the Performance issue have triggered. I cover these topics in my DBA Best Practises training. This process should have Monitoring Tools, Monitoring Plan and then the Performance Analysis like it says in below slide.

PerfAnalysis_01

 

In the Monitoring tools section, I cover the following as it says in the below slide with SQL Trace, Profiler, Statistics IO, Trace Flags, System Stored Procedures, DMV & DMF, DBCC, DTA, Performance / Activity monitors.

PerfAnalysis_02

In Monitoring Plan, I cover mainly with a performance baseline, where a DBA should have clear understanding on the environment, client applications, database applications, network architecture, available system resources, etc.,

PerfAnalysis_03

ByDr. SubraMANI Paramasivam

SQL Server Instance Maintenance

What does every single installed SQL Server Instance Maintenance covers? Below slide gives information on Monitoring Disk Space usage, Adding/Removing users, Moving objects for better performance, Reviewing and verifying system / error catalogs, to check database integrity, Server monitoring, adding / removing servers, Apply patches, etc.,

Instance_Maintenance

ByDr. SubraMANI Paramasivam

Create a new job from already existing job

You can create a brand new job by duplicating already existing job within the SQL Server instance. Open SSMS and then go down to SQL Server Agent and expand to see the JOBS and again expand to see all the available Jobs. From the below screen, I have chosen a maintenance plan job and then right click on it to see the available options on it and mouse over “Script Job as” and then further mouse over to “CREATE To” and then choose the relevant options for you. I would suggest to choose “New Query Editor Window” and this gives all the settings that is available for this job. You can choose this and rename wherever required and you can execute the same by creating a new job accordingly.

CreateScript_fm_Job

ByDr. SubraMANI Paramasivam

Automation of taking backup of all databases within single SQL Server instance

Below scripts will enable you to take backup of every single database except Model & TempDB databases, by using cursors on it. Strongle advise not to run this with very large databases or in OLTP environments where the systems are heavily used.

 

DECLARE @dbName VARCHAR(50)
DECLARE @backupPath VARCHAR(256)
DECLARE @backupFileName VARCHAR(256)

SET @backupPath =
‘F:\’

DECLARE All_Backup_Cursor CURSOR FOR
SELECT Name FROM sys.databases WHERE Name NOT IN (‘Model’,’TempDB’);
OPEN All_Backup_Cursor
FETCH NEXT FROM All_Backup_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @backupFileName = @backupPath + @dbName
BACKUP DATABASE @dbName TO DISK = @backupFileName
FETCH NEXT FROM All_Backup_Cursor INTO @dbname
END
CLOSE All_Backup_Cursor
DEALLOCATE All_Backup_Cursor