Thank you, everyone, to stay along with me on this journey.

I started this blog for the self repository of SQL Server knowledge articles, Later I made it public to connect with you all.

I had also started 2nd part of MSSQLFUN, YouTube Channel “Decode ITeS”. I like to request to subscribe and provide the same support to “Decode ITeS”.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Subscribe YouTube Channel Decode ITES

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

Tweets by Rgarg86

Other Linked Profiles:-

http://social.msdn.microsoft.com/Profile/rohitgarg

https://www.linkedin.com/in/rohitgarg1986/

Hyper-V is a Microsoft feature to create a virtualized environment. Hyper-V is available in your windows OS and you need to enable it from “Turn On or Off Windows Feature”. You can enable Hyper-V and create your own virtual environment. This helps in running multiple operating systems on a single physical computer.

Note: Hyper-V option will not be available in Windows 10 Home edition. In this case, you can plan to use other virtualized software like VMWare or VirtualBox.

Step 1: Go to Control Panel

Step 2: Go to Programs and Features > Turn Windows features on or off

Step 3: Select Hyper-V Option

Step 4: It will ask for a reboot. Reboot your system to get this enabled.

Step 5: After reboot, you can search Hyper-V from the taskbar search.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Subscribe YouTube Channel Decode ITES

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles:-

http://social.msdn.microsoft.com/Profile/rohitgarg

https://www.linkedin.com/in/rohitgarg1986/

SQL Server Architecture

SQL Server is Microsoft RDMS that works on a client-server architecture. Here, End-user sends a request and SQL Server accepts, process, and share the result with the user.

This seems a very simple process from frontend however there are multiple processes runs in the background to fulfill this request. Microsoft SQL Server Architecture is a very complex internal mechanism with 3 major components.

1. Network Protocols (SNI – SQL Server Network Interface)
2. Database Engine

1. Storage Engine
2. Relation Engine

SQLOS

1. Network Protocols (SNI – SQL Server Network Interface): SQL Server Network Interface is a network protocol layer to connect DB instance. SQL Server Supports 3 protocols for network connectivity and VIA is a hardware-based obsolete protocol. You will found VIA in new SQL versions. You can enable the required protocol from the SQL Server configuration manager.

Shared Memory: Shared Memory is the simplest protocol and required ZERO configuration. This works on the same machine on which SQL Server is installed, no client-server communication using share memory is possible.

TCP/IP: TCP/IP is the most widely used protocol for client-server connectivity. You need to enable TCP/IP protocol from the SQL Server Configuration Manager. By default, SQL Server runs on 1433 TCP port however you can change as and when required.

Named Pipes: Names Pipe protocol is designed for LAN (Local Area Network). You need to enable Named Pipes from the SQL Server Configuration Manager. Names Piped can be used for local and remote systems on the same LAN. SQL Server can be connected using named pipe by mentioning for the default SQL Server instance is \\.\pipe\sql\query, and \\.\pipe\MSSQL$<instancename>\sql\query .

2. Database Engine: Database Engine is the core of SQL Server architecture, this provides a layer between Client connecting using the available protocol and SQL OS (the core internal of SQL). DB engine represents the logical architecture of the SQL server which consists of or works with physical architecture and relation engine to execute user requests.

Logical architecture is Tables, Indexes, Views, Store Procedures, Functions, Triggers, and other logical entities that group data for user representation.

1. Storage Engine: Storage Engine consists of Physical Database architecture along with access and buffer manager. A storage engine is responsible for physical data storage and access as and when needed.

• Physical Database architecture is a description of how actual data is stored in SQL. This has 2 layers. In Layer 1, Data in SQL server stored in a database that resides on files at the OS files system. Database files are divided into data and log files. Data files consist of data\ index\ views and log file consist of transaction-level details for recovery perspective.

Types of Database files:-

Primary– The primary data file contains the startup information for the database and points to the other files in the database. User data and objects can be stored in this file or secondary data files. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.

Secondary– Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow. The recommended file name extension for secondary data files is .ndf.

Transaction Log– The transaction log files hold the log information that is used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf.

Each file has 2 names, a logical name used by SQL Server to identify the file and OS file name which is the physical file name on OS drive. Both can be identical or different.

In Layer 2, This is the smallest layer which represents Pages and Extents storage of database files at the disk level.

Extents are a combination of 8 pages (8X8KB) of size 64KB. This is the initial storage unit assign to table\index. Extents are of 2 types, Uniform extends which assign to single objects and Mixed extends which is shared by multiple objects(maximum by 8).

Pages are the smallest storage unit in SQL Server of size 8KB. Each page reserved 96 bytes for system information and the rest of the data. We have 9 types of pages in SQL Server.

• Data – Saves only Data excluding ntext, text and images
• Index − Index Details
• Text\Image − ntext, text, and images
• GAM – GAM means Global allocation Map saves Information for allocated extents
• SGAM − Shared Global Allocation Map saves Information for allocated mixed extents
• Page Free Space (PFS) – Saves Information about free pages
• Index Allocation Map (IAM) – Details of extents used for table or index.
• Bulk Changed Map (BCM) – BCM pages used to save extent details modified by bulk operations since last log backup
• Differential Changed Map (DCM) − DCM pages used to save extent details modified since last differential backup

• Access Methods: Access methods are ways to access data in SQL Server. In Technical terms, Access methods are the type of commands used to access data and desired results.

DDL: Data Definition Language to create, alter and drop DB objects

DML: Data Definition Language to select, insert, modify and delete data

• Buffer Manager: Buffer Manager is one of the critical aspects of Database system performance. Any user request or data processing cannot work directly on storage. Data needs to move in the buffer for any changes and access. Buffer consist of:-

1. Plan Cache – It saves executing plans of executed queries for future references
2. Data Pages – Data Pages available in buffer for user requests. This can unmodified pages and dirty pages. Dirty pages are one which got modified in the buffer but did not write back to disk.
3. Buffer Pool – Cache used for data processing and query execution

• Transaction Services: Transaction Services works between data storage and a transaction log file(.ldf). It controls transaction details and modified data logging to ensure recovery by dealing with transaction file (.LDF)

• Lock Manager: Each data access and modification required adequate lock on data. These data lock needs to be upgraded to the next level or released as and when required by the system based on user request. Lock Manager governs this process and helps in maintaining consistency and isolation.

2. Relation Engine: Relation engine is responsible for evaluating user requests \ SQL Commands and performs execution. As described in the image, any SQL Query needs to go from multiple processes for execution.

1. Parser – Parse the query or command for syntax and T-SQL
2. Algebrizer – Resolve all object names and bind them
3. Query Optimizer – Generate execution plans on the basis on stats and chose best one for execution
4. Query Execution – Actual query execution. Actual execution plan may change now and same will be stored in the plan cache for future reference
5. Query output – Output will be shared with the user

3. SQL OS: SQL OS was introduced n SQL 2005. Before SQL 2005, SQL Server was preferred only for small and medium load applications. Microsoft enhances SQL server to handle High-end enterprise DB level with SQL 2005. SQL OS is a layer between the SQL Server DB engine and the Windows Operating system.

Why Microsoft feel the need for SQL OS?

We know Windows OS is the layer with end-user and hardware. OS is an abstract layer with convert user requests in hardware singles and makes execution. SQL OS is similar to Windows OS. Microsft feels the SQL engine needs a layer between the SQL engine and OS to deals with Memory Management. scheduling, threading, NUMA in a more controlled way with windows OS.

Primary Functions of SQL OS:

• Thread Scheduler: SQL OS is responsible for scheduling CPU thread for SQL operations

• Synchronization Services: SQL is a multithreaded application. SQL OS governs synchronization between threads

• I/O Manager: I/O is time taking process as it depends on disk speed and type. SQL OS keeps monitoring I/O operations and signal threads when completed

• External API: SQL Server provides a feature of controls and working with CLR (DLLs) and MDAC (Open Data Source Connections). SQL OS is responsible to manage it.

• Thread Management: SQL OS Control Lazy write process to maintain free pages in buffer. This is the primary function for buffer management. SQL OS also responsible for Monitor, Detect, and Management of deadlocks.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Subscribe YouTube Channel Decode ITES

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles:-

http://social.msdn.microsoft.com/Profile/rohitgarg

https://www.linkedin.com/in/rohitgarg1986/

SQL Server editions:-

· Enterprise – Enterprise edition is the topmost edition with all available features. This is best suited for mission-critical Tier 1 applications. Enterprise edition is the capabilities of tremendous hardware scaleup.

· Standard – Standard edition is mostly used in economic edition with limitations of hardware support and advance features. In case your application database is not using any advanced features, you are good to use this edition.

· Developer – Developer edition is similar to enterprise edition will all features. The developer edition is FREE and introduced by Microsoft for development, testing, and demo. You can upgrade it to Enterprise edition anytime without reinstallation.

· Express – Express edition is free of cost setup with very limited hardware and features support. Express edition is best suited for web and mobile applications. You can sustain up to 10GB of database only.

· Enterprise Evaluation – Enterprise evaluation is similar to the Enterprise edition. This is free of cost edition and a great way to test and develop your application against enterprise edition features. You can upgrade it to enterprise edition anytime using an edition upgrade option with a valid key. This edition comes up with an expiry of 6 months and you should ensure to upgrade it before expiry to keep your system running.

SQL Server Components:-

SQL Server components can be divided into client \ workstation and server types.

· Client components are installed in a client machine or machine from which you want to connect and access SQL Server. Client Components are:-

• SSMS: SQL Server Management Studio gives you a GUI interface to connect available SQL instances.
• Profiler: SQL Profiler is a client tool to monitor transactions running on the server. This is one useful tool for auditing and performance checks.
• DTA: Database Tuning Advisor is a DBA tool that helps you to analyze query performance and it can suggest possible indexes and other changes.
• BIDS: BIDS is a Bussiness Intelligence Development Studio, a powerful BI tool. You can develop SSRS reports, SSAS Cubes, and SSIS ETL packages using BIDS.
• Etc.

· Server components are installed on the primary server. Server Components are:-

• SQL Server: SQL Server is the primary server to run SQL instance and using SQLServer.exe in the background.
• SQL Server Agent: SQL Server agent is the dependent server of SQL Server. It’s used for scheduling activities and uses sqlagent.exe in the background.
• SQL Server Browser: SQL Server browser is important to service from security and accessibility points. When enabled, It helps incoming connections to map SQL Server running port. It used sqlbrowser.exe in the background.
• SQL Server Full-Text Search: Full Text is one of cool search feature which gives possibilities to search for the test from mentioned columns. It uses fdlauncher.exe in the background.
• SQL Server Analysis Services (SSAS): SSAS is an OLAP feature which helps in data mining and analytics. It uses msmdsrv.exe.
• SQL Server Reporting Services (SSRS): SSRS issued to develop reports and report subscriptions. It uses ReportingServicesService.exe executable.
• SQL Server Integration Services (SSIS): SSIS is ETL (Extract Transform and Load) tool. You can develop in-house ETL using BIDS and execute them using SSIS.

Find this on our YouTube Channel Decode ITES: https://youtu.be/Eq_5yu8_Ms4

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Subscribe YouTube Channel Decode ITES

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles:-

http://social.msdn.microsoft.com/Profile/rohitgarg

https://www.linkedin.com/in/rohitgarg1986/