SQL SERVER – Beginning of SQL Server Architecture – Terminology – Guest Post

SQL Server Architecture is a very deep subject. Covering it in a single post is an almost impossible task. However, this subject is very popular topic among beginners and advanced users.  I have requested my friend Anil Kumar who is expert in SQL Domain to help me write  a simple post about Beginning SQL Server Architecture. As stated earlier this subject is very deep subject and in this first article series he has covered basic terminologies. In future article he will explore the subject further down. Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

In this Article we will discuss about MS SQL Server architecture.

The major components of SQL Server are:

  1. Relational Engine
  2. Storage Engine
  3. SQL OS

Now we will discuss and understand each one of them.

1) Relational Engine: Also called as the query processor, Relational Engine includes the components of SQL Server that determine what your query exactly needs to do and the best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.

Different Tasks of Relational Engine:

  1. Query Processing
  2. Memory Management
  3. Thread and Task Management
  4. Buffer Management
  5. Distributed Query Processing

2) Storage Engine: Storage Engine is responsible for storage and retrieval of the data on to the storage system (Disk, SAN etc.). to understand more, let’s focus on the concepts.

When we talk about any database in SQL server, there are 2 types of files that are created at the disk level – Data file and Log file. Data file physically stores the data in data pages. Log files that are also known as write ahead logs, are used for storing transactions performed on the database.

Let’s understand data file and log file in more details:

Data FileData File stores data in the form of Data Page (8KB) and these data pages are logically organized in extents.

Extents: Extents are logical units in the database. They are a combination of 8 data pages i.e. 64 KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc (multiple objects). On the other hand, Uniform extents are dedicated to only one type (object).

Pages: As we should know what type of data pages can be stored in SQL Server, below mentioned are some of them:

  • Data Page: It holds the data entered by the user but not the data which is of type text, ntext, nvarchar(max), varchar(max), varbinary(max), image and xml data.
  • Index: It stores the index entries.
  • Text/Image: It stores LOB ( Large Object data) like text, ntext, varchar(max), nvarchar(max),  varbinary(max), image and xml data.
  • GAM & SGAM (Global Allocation Map & Shared Global Allocation Map): They are used for saving information related to the allocation of extents.
  • PFS (Page Free Space): Information related to page allocation and unused space available on pages.
  • IAM (Index Allocation Map): Information pertaining to extents that are used by a table or index per allocation unit.
  • BCM (Bulk Changed Map): Keeps information about the extents changed in a Bulk Operation.
  • DCM (Differential Change Map): This is the information of extents that have modified since the last BACKUP DATABASE statement as per allocation unit.

Log File: It also known as write ahead log. It stores modification to the database (DML and DDL).

  • Sufficient information is logged to be able to:
    • Roll back transactions if requested
    • Recover the database in case of failure
    • Write Ahead Logging is used to create log entries
      • Transaction logs are written in chronological order in a circular way
      • Truncation policy for logs is based on the recovery model

SQL OS: This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. It is a highly configurable operating system with powerful API (application programming interface), enabling automatic locality and advanced parallelism. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure. Other services include exception handling, hosting for external components like Common Language Runtime, CLR etc.

I guess this brief article gives you an idea about the various terminologies used related to SQL Server Architecture. In future articles we will explore them further.

Guest Author 

The author of the article is Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQLAuthority News – Technical Review of Learning at Koenig Solutions

Yesterday I finished my 3 days fast track in person learning of course End to End SQL Server Business Intelligence at Koenig Solutions. You can read my previous article over here regarding why am I learning SQL Server. Yesterday I blogged about my experience of arriving to Training Center and my induction with the center.

The Training Days

I had enrolled for three days training so my routine each of the three days was very much same. However, the content every day was different as I was learning something new every day. Let me describe a few of the interesting details of my daily routine.

A Single Student Batch

The best part of my training was that in my training batch, I am single student. Koenig is known to smaller batches and often they have single student batches as well. I was very much delighted to know that I will have dedicated access and attention from my trainer in my batch as I will be single student in my batch. In most of the labs I have observed there are no more than 4 students at any time.

Prakash and Pinal

Prakash and Pinal

7:30 AM Breakfast Talk

We all students gather at 7:30 in breakfast area. The best time of the day. I was the only Indian student in the group. The other students were from USA, Canada, Nigeria, Bhutan, Tanzania, and a few others from other countries. I immediately become the source of information and reference manual. Though the distance between Delhi and Bangalore is 2000+ KM I was considered as a local guy.

8:30 AMHeading to Training Center

Every day without fail at 8:30 the van started from our accommodation to the training center. As mentioned in an earlier blog post the distance is about 5 minutes and we were able to reach at the location before 8:45. This gave us some time settle in before our class starts at 9:00 AM.

9:00 AM Order Lunch Food

Well it may sound funny that we just had breakfast 30 minutes but the first thing everybody has to do is to order lunch as soon as the class starts. There is an online training portal to order food for the day. Everybody has to place their order early during the day so the food arrives on time during lunch time. Everybody can order whatever they want to order using an online ordering system. The options are plenty and everybody can order what they like.

9:05 AM Learning Starts

After deciding the lunch we started the learning. I was very fortunate to have a very experienced trainer – Prakash Chheatry. Though I have never met him before I have heard a lot about Prakash. He is known as the top most SQL Server Trainer in India. His student list contains some of the very well known SQL Server Experts of the world and few of SQL Server “best seller” book authors.

Learning continues till 1:00 PM with one tea-coffee break in between.

1:00 PM Lunch

The lunch time is again the fun time. We all students get together in the afternoon and tell the stories of the world. Indeed the best part of the day beside learning new stuff.

4:55 PM Ready to Return

We stop at 4:55 as at precisely 5:00 PM the van stops by the institute which takes us back to our accommodation. Trust me seriously long long day always but the amount of the learning is the win of the day.

7:30 PM Dinner Time

After coming back to the accommodation I study till 7:30 and then rush for dinner. Dinner is world cuisine and deserts are really delicious. After dinner every day I have written a blog and retired early as the next day is always going to be busier than the present day.

What did I learn

As I mentioned earlier I know SQL Server fairly well. I had expressed the same in my conversation as well. This is the reason I was assigned a fairly senior trainer and we learned everything quite quickly. As I know quite a few things we went pretty fast in many topics. There were a few things, I wanted to learn in detail as well practice on the labs. We slowed down where we wanted and rush through the concepts where I was very comfortable.

Here is the list of the things which we covered in action pack three days.

  • Introduction to Business Intelligence (Intro)
  • SQL Server Analysis Service (Theory and Lab)
  • SQL Server Integration Service  (Theory and Lab)
  • SQL Server Reporting Service  (Theory and Lab)
  • SQL Server PowerPivot (Lab)
  • UDM (Theory)
  • SharePoint Concepts (Theory)
  • Power View (Demo)
  • Business Intelligence and Security (Discussion)

Well, I was delighted that I was able to refresh lots of concepts during these three days. Thanks to my trainer and my friend who helped me to have a good learning experience. I believe all the learning  will help me in my growth and future career.

With this I end my this experience. I am planning to have another online learning experience later this month. I will blog about my experience as I begin it.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Learning Trip – Traveling to Learn SQL Server

I am currently traveling to Delhi to learn SQL Server in person from my friend. You can read more details about why am I learning SQL Server.  I have signed up for the course End to End SQL Server Business Intelligence at Koenig Solutions. Yesterday I blogged about my registration experience and today I am going to write about my  experience once I arrived at Delhi.

From Ahmedabad to Delhi

I stay with my wife and daughter in Bangalore (IT Hub of India), my hometown is Ahmedabad. My parents stay in city nearby Ahmedabad. I decided to spend few days with my folks before I sign up for 3 days of solid learning. I had selected an early morning flight to Delhi. I landed at 8:30 AM in Delhi.

As soon as I checked email in my mobile I was really glad that I had received details of my pick up vehicle from Koenig. I walked out of the airport and I noticed that a driver was waiting with a placard with my name and photo associated with it. He was in Koenig uniform so there was no chance to make mistakes. In minutes of landing in Delhi I was in my transport heading to the Koenig Training Center. After the quick introduction driver handed me a bag (to be precise Eco friendly bag).

The bag contained following items:

  • My registration form
  • All necessary documents in print which I had received earlier
  • A Printed Book of the course next day
  • INR 1000 (What?)

I was glad to receive the bag but I was very confused with the Rs 1000. I decided to figure this out once I reach to the training center.

Arriving at Koenig Inn Deluxe

Koenig registration fees include all the stay and meals. I had opted for Koenig Inn Deluxe as my stay as it was recommended by my friend as well it was the right economical choice for me. When I reached to my accommodation, they were well aware of my arrival and was immediately led to my spacious room. The room is well equipped with all the amenities (hot water, air condition, coffee table, munching snacks,  and free internet) and the staff is very friendly. I immediately got ready as I had to go to Koenig Training Center to meet Center Head for a quick introduction.

Koenig Inn Delux

Koenig Inn Delux

Koenig Training Center

The training center is within five minutes of distance from the accommodation. I was lead to center head right away and had a very meaningful conversation with Ms Hema regarding my learning goals. She gave me a quick tour of the training center. I was amazed with the numbers of lab rooms they have in the center. The labs are spacious and give the most needed hand’s on experience to the users. I was led to the lab where I was suppose to learn my class the very next day as well I was provided my trainer’s profile.

Mystery of Rs 1000

Well, after all this I have still not forgotten why I was provided Rs 1000 when arrived at the airport. When I asked about that I was told that because many students comes from foreign places and they may not have Indian Currency when they land at airport. This was for their immediate consumption till they arrive at the training center. Later on they can get their currency converted to local currency at Koenig Travel Desk. My curiosity was satisfied but I had not expected this answer. I am amazed at the attention to the details.

Koenig Travel Desk

When I heard about Koenig Travel Desk, I remembered that I have few friends in Delhi and Gurgaon. I had completed all of the formalities so I had reset of the day on my hand. I requested the travel desk if they can arrange a day cab for me so I can visit my friends in Guragon. Within 10 minutes I was on my way to Gurgaon.

Telerik India Office Visit

What did I do in Guragaon? I met my friends Abhishek Kant, Dhananjay Kumar and Amit Chowdhary. I visited Telerik India office and we had an excellent conversation on various aspects of technology and community. The Telerik India office is very spacious and Abhishek Kant (Telerik India Country Manager) gave us a quick tour of the office. We had an excellent lunch and dinner. One thing is for sure – the day was well spent.

Pinal Dave, Dhananjay Kumar and Abhishek Kant

Pinal Dave, Dhananjay Kumar and Abhishek Kant

Later evening I returned to my accommodation and decided to read up a few of the topics which I was going to learn next day. In tomorrow’s blog post I will discuss about my learning experience.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Getting Ready to Learn SQL Server

If you have read my earlier blog post you must be aware of how I am always eager to learn new things. I have signed up for three days learning course at Koenig Solutions for End to End SQL Server Business Intelligence. You may wonder why I sign up for the course on SQL Server when it seems that I know a lot about it.

Well, the belief is incorrect that I know a lot. I think there are plenty of things which I have been dreaming to learn.

Why am I learning SQL Server?

First of all – I do not know everything and second it is always a good idea to learn more. No matter how old we get or how much we think we know – there are always details which we can learn and refresh few concepts.

Learning is never ending process philosophically but it is true as well in reality. SQL Server 2012 is already released earlier this year and there are plenty of enhancements released. Recently I was going over the list of the all the new feature and enhancement and I realized that there few things about SQL Server 2008 R2 I never got a chance to have a hand’s on experience and we have entered into the era of SQL Server 2012. I feel a bit bad about it and I decided to make it a priority for me to learn all the missing experiences.

Quick Action – Registration

The very same day I called up my friend who owns Koenig Solution and expressed my concern and requested his help. During my early career when I was a SQL Server Trainer, we had some good synergy between us and now they are very successful offshore training company by having a physical location in Delhi,  Goa, Dahradun, Shimla, Goa and Bangalore. I quickly visited their Bangalore Center and paid my fees for learning SQL Server Business Intelligence course. Very next second I got call from my friend suggesting that I learn this course from Delhi instead of Bangalore. As per him I should travel to Delhi and learn the course how other students are learning “Away from Home”. This made sense as I stay in Bangalore and if I return home after a long day of learning, I will be not able to practice for the next day as there will be “sweet distraction” of the family. Well I opted for Delhi.

What Registration Fees Included

I learned from registration processes that the following were included in the fees.

  • 3 meals every day (hearty breakfast, lunch from premium restaurants and home cooked like dinner)
  • Airport pick up and drop
  • Hotel Stay
  • Internet at hotel and at learning institute
  • Unlimited coffee and snacks at learning institute
  • Printed Learning Material
  • Certification Fees (if applicable)
  • Learning material
  • … And of course classroom training

I thought registration process was over when I paid fees. Well, I was in for a very nice surprise.

Registration Experience – Bliss!

Within few hours I received emails from Center Manager of Delhi with all the necessary details I need to know about my learning experience. The email contained following information in detail and it blew me away.

  • Details of the pick up from airport – driver information
  • Details of Delhi and important information
  • List of all the important people and emergency contact details
  • Internet connection details
  • Detail of the trainer and all the training details
  • and lots of other relevant information

Well so far everything looks great. Tomorrow I will reach to Delhi and I will share how things go on. Any suggestion for things to do in Delhi?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Weekend Project – Visiting Friend’s Company – Koenig Solutions

I have decided to do some interesting experiments every weekend and share it next week as a weekend project on the blog. Many times our business lives and personal lives are very separate, however this post will talk about one instance where my two lives connect.

This weekend I visited my friend’s company. My friend owns Koenig, so of course I am very interested so see that they are doing well.  I have been very impressed this year, as they have expanded into multiple cities and are offering more and more classes about Business Intelligence, Project Management, networking, and much more. Koenig Solutions originally were a company that focused on training IT professionals – from topics like databases and even English language courses.  As the company grew more popular, Koenig began their blog to keep fans updated, and gradually have added more and more courses.

I am very happy for my friend’s success, but as a technology enthusiast I am also pleased with Koenig Solutions’ success.  Whenever anyone in our field improves, the field as a whole does better.  Koenig offers high quality classes on a variety of topics at a variety of levels, so anyone can benefit from browsing this blog. I am a big fan of technology (obviously), and I feel blessed to have gotten in on the “ground floor,” even though there are some people out there who think technology has advanced as far as possible – I believe they will be proven wrong.  And that is why I think companies like Koenig Solutions are so important – they are providing training and support in a quickly growing field, and providing job skills in this tough economy.

I believe this particular post really highlights how I, and Koenig, feel about the IT industry.  It is quickly expanding, and job opportunities are sure to abound – but how can the average person get started in this exciting field?  This post emphasizes that knowledge is power – know what interests you in the IT field, get an education, and continue your training even after you’ve gotten your foot in the door.

Koenig Solutions provides what I feel is one of the most important services in the modern world – in person training.  They obviously offer many online courses, but you can also set up physical, face-to-face training through their website.  As I mentioned before, they offer a wide variety of classes that cater to nearly every IT skill you can think of.  If you have more specific needs, they also offer one of the best English language training courses.  English is turning into the language of technology, so these courses can ensure that you are keeping up the pace. Koenig Solutions and I agree about how important training can be, and even better – they provide some of the best training around.  We share ideals and I am very happy see the success of my friend.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Four Tutorial for SQL Server 2012 New Features

One of the very common question I receive on my facebook is that if there is any tutorial for SQL Server 2012 new enhanced features and solutions. I see this demand a bit increasing as the SQL Server 2012 is more and more being adopted. Here is the list of four tutorial which is specifically created for SQL Server 2012 by Microsoft.

Multidimensional Modeling (Adventure Works Tutorial)
This tutorial teaches you how to develop and deploy an Analysis Services project that enables the employees of Adventure Works Cycles to analyze various aspects of their business.

Tabular Modeling (Adventure Works Tutorial)
This tutorial teaches you how to create a SQL Server 2012 Analysis Services tabular model that enable sales and marketing teams to easily analyze internet sales data in the AdventureWorksDW2012 data warehouse. You will build the tabular model in SQL Server Data Tools.

Tutorials and Demos for Power View
Create Power View reports and explore Power View features. View demos, videos, and tutorials that help you get started quickly with Power View and successfully build reports with interactive filters and visualizations such as bubble charts, tiles, and cards.

Tutorial: Using the hierarchyid Data Type
This tutorial is intended for users who are experienced with Transact-SQL, but are new to the hierarchyid data type. In this tutorial, you convert an existing table to a hierarchical structure, and you also create a new table to store and manage hierarchical data efficiently.

Note: The description of the course is taken from the original course description. You will need to install SQL Server 2012 AdventureWorks for all this tutorial.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – An Inspiring Personal Story – Movie from The Book – Video Course – SQL Server Questions and Answers – Pluralsight

Nov 3, 2011 – Visit to Grandma

When our SQL Server Interview Questions and Answers book got published I ran to my grandma with a copy of the book for her blessings. Well, just like every grandma, she loves me, her grandson, unconditionally. She is not into the technology domain (obviously), but she loved the book. She read the first few pages where I was mentioned and read about my co-author Vinod Kumar. After reading the introduction she looked at me and said “When I was young we used to read books, now all those good books are converted into the movies, when do you think this book will be converted into a movie?

Silence. A very long silence.

I just could not answer her right away.  As a matter of fact, I went into a deep thought. I never thought that she could actually ask me such question. It may be a very simple question, but I felt that it was very important to me. I was just stunned and speechless. I returned home from my grandma’s place, with her blessings and an interesting thought of a “Movie from the Book”. I just could not get this out of my mind.

Jan 24, 2012 – Movie from Book

I talked to my grandma very often, but since the last conversation my wife called her often and I did not talk to her at all. There was no reason to talk to her maybe because I constantly felt that she is with me. Her blessings were with me. I could not get her question out of my mind, still. I was extremely motivated and inspired from my last conversation with her. I called her early morning on January 24. Her very first questions were how I was doing and how was her great granddaughter. I told her that regarding our last conversation, there is actually a movie which is inspired from the book. Her very first reaction was that she knew this would happen as she was confident that my book would reach milestones. We talked for long hours.

Today – Fast Forward

Well, the book was an inspiration for the course, but the video course was developed using totally different angle. We made sure that we’d take the broader idea from the book but wanted present something unique and novel. Vinod Kumar and I thought about it for a long time. We wanted to decide on the format of our course. We spent hours talking about the course – how we could make it unique and give a different experience to every attendee. We wanted to keep the subject light enough to digest, yet deep enough to satisfy everyone’s learning eagerness. After spending some days and efforts for the preparation of the unique content and exceptional demos, the course has become a reality for us.

SQL Server Questions and Answers

This course is designed to help you have a better understanding on how to use SQL Server effectively. The course presents many of the common misconceptions about SQL Server, and then carefully debunks those misconceptions with clear explanations and short but compelling demos, showing you how SQL Server really works. This course is for anyone who works in SQL Server databases, and basically for those who want to improve their knowledge and understanding of this complex platform.

In this course, each topic starts with a question and ends with an answer which is supplemented with a demo. Every question has an exercise file and a demo associated with it. Additionally, in the very last session, we have covered lots of SSMS tips and tricks. Here are the topics we have covered in this course:

  • Introduction
  • Retrieving IDENTITY value using @@IDENTITY
  • Concepts Related to Identity Values
  • Difference between WHERE and HAVING
  • Order in WHERE clause
  • Concepts Around Temporary Tables and Table Variables
  • Are stored procedures pre-compiled?
  • UNIQUE INDEX and NULLs problem
  • Locks and Duration of Transactions
  • Nested Transaction and Rollback
  • Understanding Date/Time Datatypes
  • Differences between VARCHAR and NVARCHAR datatypes
  • Precedence of DENY and GRANT security permissions
  • Identify Blocking Process
  • NULLS usage with Dynamic SQL
  • Appendix Tips and Tricks with Tools

Today we are very happy that our course is out. The journey from the book to the Training Course took 80 days and the experience was wonderful. In a future post, I will share the experience of writing book and building the training course.

In the end, we just want to thank YOU, our dear reader, who made this experience possible.

Reference: Pinal Dave (http://blog.sqlauthority.com)