Introduction to SQL Server 2016

Hello Guys,

As many of you must be aware under Microsoft flagship, they have announced SQL Server 2016 in May’15 during Microsoft Ignite conference. So, the latest available version of MS SQL Server 2016 is CTP 2.1 (Community Technology Preview) from Jun’16. Soon (Somewhere in 2016; not yet confirm) the general version RTM (Release To Manufacture) would be available.  Also the code name for this version is yet to release.

Tester can download SQL Server 2016 CTP 2.1 from official Microsoft.

Introducing SSMS for SQL Server 2016

Following is a brief note on the how the Software is released in phases:

• CTP > It stands for Community Technology Preview. CTP version is released before a newly developed software is roll out in the market. It is releases to improve the software or to fix bugs present.
• RTM > It stand for Release To Manufacture. This is the first official version which released to the customers or clients for use. 
• CU > It stands for Cumulative Updates which keep on releasing after RTM or SP is released. Its main purpose is to fix the bug found in the product.
• SP > It stands for Service Pack. Bunch of CU forms a SP and it is cumulative. That means say there are two SP’s are released SP1 and SP2. You can directly apply SP2 no need to apply SP1 and then SP2.

Click here to find more on release date and the version number for overall Microsoft product available from SQL Server 7.0 till SQL Server 2016.

With this information now we will see what new features are available in MS SQL Server 2016. The following diagram will give you an idea about some of the new feature in SQL Server 2016.

New Features of SQL Server 2016

We will see in detail about these new features of SQL Server 2016 on next post here. Till then........

Keep Learning and Enjoy Learning!!!

Thank you.

MS SQL Server Along With Hadoop

Thank you Guys!!!

Your appreciation and encouragement on my post of "BIG DATA" under the title "Apart From SQL Server...".

From now onward we will learn both "SQL Server" and "Hadoop" Administration under this same blog of mine. So definitely first I'll update my blog title to "All about MS SQL Server And Hadoop Administrator" rather than "All about MS SQL Server DBA".
Here I will share my real world experiences about MS SQL Server as well as my learning experience about Hadoop Administrator.

We saw a tremendous revolution in past decade around us with the help of IT and of course there is much more in coming years. If you look back there were hardly or you can count the e-commerce sites in your finger tips but today there are dozens of e-commence going around. These e-commerce business will generate bunch of Structure as well as unstructured data. Structure data's can be easily managed by RDBMS but to maintain and improve these unstructured  data we need a large set of Cluster structure Servers which will handle these type of data. Most of us today are not aware to this technology or just have heard the term "BIG DATA".

I've already posted here that how I came to know about this technology. Now, here is the reason why I'm keen to learn Hadoop.

As a MS SQL Server DBA I always wanted to learn some different related technology as well. Since I'm having knowledge on RDBMS and learning Oracle which is also again an RDBMS. So thought of learning Non-RDBMS product and I landed to learn Hadoop.

So those you want to learn SQL Server or Hadoop or both can bookmark my link. I'm afraid as a new beginner to Hadoop I might go wrong sometimes but trust me I'll try to avoid such mistakes by understand the topic well before writing any blog on Hadoop.

Feel free to correct me if I'm wrong on any part not just with Hadoop but also with MS SQL Server posts.

So guys, let's start learning a new upcoming technology along with our own MS SQL Server.

Keep Learning and Enjoy Learning!!!

Thank you!!!

Auto Growth Option in SQL Server Database Part-2

Good Morning Friends,

This post is the continuation of Auto Growth Part 1.

Part 1 we have seen the options available of Auto Growth in SQL Database and the parameters available. Now here we will see how to select Parameters.

For "Parameter 2" easily we can set to "Unlimited" because no one will stop the growth of there Database. So it is set to "Unlimited" (It will grow until your disk get full or in simple terms you can say limited to your Drive size).

For "Parameter 1" best I will select "Growth in MB". Following are the cases I'll try to explain while selecting this option:

Case 1:

1. Suppose I've a Database Clis3, which has one Data and one Log File with size is 50 and 10 GB   respectively.
2. Growth for Log file is set to 100 MB. Recovery model in Full recovery mode.
3. Suppose there is a Bulk transaction in the Clis3 Database. Obviously the Log file will capture each and every transaction. SO the log file will start growing.
4. First it will start utilizing all the available VLF (Virtual Log File) up to 10 GB. Once it is full it will add 100 MB to Log file to write the transactions.
5. Now total Log size is 10.10 GB.
6. Once this 100 MB is also full it will add 100 MB more and so on. It will be done until the transaction is completed.
7. So now total Log size is 10.20 GB.

Here SQL Server is adding a defined amount of space (i.e. 100 MB) to the log file. This will indirectly control the growth of Log file.

Case 2:

1. Suppose for same Database Clis3, which has one Data and one Log File with size is 50 & 10   GB respectively.
2. Growth for Log file is set to 10 percent. Recovery model in Full recovery mode.
3. Suppose there is a Bulk transaction in the Clis3 Database. Obviously the Log file will capture each and every transaction. SO the log file will start growing.
4. First it will start utilizing all the available VLF up to 10 GB. Once it get full it will add 1024 MB (10% of 10 GB) to Log file to write the transactions.
5. Now total Log size is 11 GB.
6. Once this 1 GB is also full it will add 1127 MB more (10% of 11 GB) and so on. It will be done until the transaction is completed.
7. So now total Log size is 12.1 GB.

Here SQL Server is adding 10 percent of the current Log Size. Which means the size will vary depending upon the current Log File.

Ideal Setting for Auto Growth

From the above image ideally for large Databases we can set for Auto Growth.

Case study for the above two cases:

1. Log File in Case 1 will have 10.20 GB at the end whereas;
2. Case 2 will have 12.10 GB at the end.
3. That means Log file will utilize 1.9 GB of unnecessary space from the disk which is unwanted.
4. Hence proves for larger Database it is good to define "Parameter 1" to "Growth in MB's" as it will avoid unnecessary utilization of disk space.

Keep in touch... and Happy Learning....

I appreciate and thank you for reading this post. :) 

Auto Growth Option in SQL Server Database Part-1

Hi Guys,

Thanks a lot friends for your wishes for my certification completion Blog here.

There was a discussion couple of days back with one of my colleague DBA about the "Auto growth" option in SQL Server Database.

This topic came into consideration when one of my re-indexing job got failed due to less disk space on the log drive. As we are aware of rebuild requires  sufficient amount of disk space for the log file growth (We will discuss on separate post "Behind the scene- while Re-indexing").This will be a two part series.

We can check the "Auto growth" option under 

Right Click on Database, Next Properties ,Next Files, Next Autogrowth / Maxsize Column. 

Auto- Growth Properties

Here you will see depending upon total number of Data and Log Files you have in your Database. 

By default, it will inherited the value from Model Database but the Log growth will limit up to 2,097,152 MB (2 TB). So now the question is should we limit this to by default value? or we should change the setting? 

The answer is "It Depends" (Just like most of the answers in SQL Server). Well, it depends up to scenario to scenario. There are many factors come into consideration before changing the value in Production environment.

1.  How frequently is my log growing?

2.  What are the operations are happening in my Database?

3.  I have multiple Log files in different Drive. Still my log file will grow?

4.  Do we take Log backups?

What triggers to grow the Log file rapidly:

a. If your recovery model is on "Full" and there is a bulk operation such as BCP  it will capture each       and  every transaction in log. 

b. Even in rebuilding operations the log files will grow rapidly.

c. Recovery model is "Full" and there is no Log Backups taken regularly.   

So as you can see and as I said it depends upon many factors. So before changing anything in Production environment you should be well aware of all the scenarios.

Following are the parameters we need to set in "Auto Growth" for a Database?

1. We can set this option for both Data and Log File by two types:

a. In Percent

b. In Megabytes

2. We can set the Maximum File Size to

a. Limited

b. Unlimited

With the help of below query we can find out this setting for all the Databases:

--auto growth percentage for data and log files

Select DB_NAME(files.database_id) database_name, files.name logical_name, 

CONVERT (numeric (15,2) , (convert(numeric, size) * 8192)/1048576) [file_size (MB)],

[next_auto_growth_size (MB)] = case is_percent_growth

    when 1 then CONVERT(numeric(18,2), (((convert(numeric, size)*growth)/100)*8)/1024)

    when 0 then CONVERT(numeric(18,2), (convert(numeric, growth)*8)/1024)

end,

is_read_only = case is_read_only 

    when 1 then 'Yes'

    when 0 then 'No'

end,    

is_percent_growth = case is_percent_growth 

    when 1 then 'Yes'

    when 0 then 'No'

end, 

physical_name

from sys.master_files files

where files.type in (0,1)

and files.growth != 0

Now as a DBA needs to set these parameters after knowing the behavior of the Database in given environment. By default it will inherit the values from your Model Database.

We will continue in the next post Part 2 the cases which best suits for parameter 1. 

Just to share with you last year on 22nd June'14 I started blogging and it's now 1 Year. Special Thanks to Akhilesh Humbe for the guidance and Thank you friends for reading my post. 

If you have any suggestion to improve this blog feel free to comment. Suggestion are most and always welcome. 

Keep in touch... And Happy Learning....

I appreciate and thank you for reading this post.