Beginning of SQL Server Architecture – Terminology

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.  Here Anil Kumar who is expert in SQL Domain to help Pinal 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 File: Data 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

3) 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: http://blog.sqlauthority.com

Web Application Projects versus Web Site Projects in Visual Studio

Ref.: MSDN

In Visual Studio you can create web application projects or web site projects. You create or open a web application project by choosing New Project or Open Project in the Visual Studio File menu. You create or open a web site project by choosing New Web Site or Open Web Site in the File menu.

Each type of project has advantages and disadvantages, and it is helpful to understand the differences between them in order to select the best project type for your needs. You must select the appropriate project type before you create a project, because it is not practical to convert from one project type to the other.

Note

For some scenarios, you do not have a choice. For example, if you want to create an ASP.NET MVC application, you must use a web application project.

This topic contains the following sections:

• Scenarios
• Summary of Differences
• Project File Structure
• Compilation
• Deployment

Scenarios

---

Scenarios in which web application projects are the preferred choice include the following:

• You want to be able to use the Edit and Continue feature of the Visual Studio debugger.
• You want to run unit tests on code that is in the class files that are associated with ASP.NET pages.
• You want to refer to the classes that are associated with pages and user controls from standalone classes.
• You want to establish project dependencies between multiple web projects.
• You want the compiler to create a single assembly for the entire site.
• You want control over the assembly name and version number that is generated for the site.
• You want to use MSBuild or Team Build to compile the project. For example, you might want to add prebuild and postbuild steps.
• You want to avoid putting source code on a production server.
• You want to use the automated deployment tools that are available in Visual Studio.

Scenarios in which Web site projects are the preferred choice include the following:

• You want to include both C# and Visual Basic code in a single web project. (By default, a web application is compiled based on language settings in the project file. Exceptions can be made, but it is relatively difficult.)
• You want to open the production site in Visual Studio and update it in real time by using FTP.
• You do not want to have to explicitly compile the project in order to deploy it.
• If you do precompile the site, you want the compiler to create multiple assemblies for the site, which can include one assembly per page or user control, or one or more assemblies per folder.
• You want to be able to update individual files in production by just copying new versions to the production server, or by editing the files directly on the production server.
• If you precompile the site, you want to be able to update individual ASP.NET web pages (.aspx files) without having to recompile the entire site.
• You like to keep your source code on the production server because it can serve as an additional backup copy.

Summary of Differences

---

The following table summarizes the main differences.

Area	Web application projects	Web site projects
Project file structure	A Visual Studio project file (.csproj or .vbproj) stores information about the project, such as the list of files that are included in the project, and any project-to-project references.	There is no project file (.csproj or .vbproj). All the files in a folder structure are automatically included in the site.
Compilation	You explicitly compile the source code on the computer that is used for development or source control. By default, compilation of code files (excluding .aspx and .ascx files) produces a single assembly.	The source code is typically compiled dynamically (automatically) by ASP.NET on the server the first time a request is received after the site has been installed or updated. You can precompile the site (compile in advance on a development computer or on the server). By default, compilation produces multiple assemblies.
Namespaces	Explicit namespaces are added to pages, controls, and classes by default.	Explicit namespaces are not added to pages, controls, and classes by default, but you can add them manually.
Deployment	You copy the assembly to a server. The assembly is produced by compiling the application. Visual Studio provides tools that integrate with Web Deploy (the IIS web deployment tool) to automate many deployment tasks.	You copy the application source files to a computer that has IIS installed on it. If you precompile the site on a development computer, you copy the assemblies produced by compilation to the IIS server. Visual Studio provides tools for deployment, but they do not automate as many deployment tasks as the tools available for web application projects.

Project File Structure

---

Web application projects use Visual Studio project files (.csproj or .vbproj) to keep track of information about the project. This makes it possible to specify which files are included in or excluded from the project, and therefore which files are compiled during a build.

For web site projects, all files in a folder structure are automatically considered to be included in the web site. If you want to exclude something from compilation, you must remove the file from the web site project folder or change its file-name extension to an extension that is not compiled and is not served by IIS.

An advantage of using project files in web application projects is the following:

• It is easy to temporarily remove files from the site but still make sure that you do not lose track of them, because they remain in the folder structure. For example, if a page is not ready to be deployed, you can temporarily exclude it from the build without deleting it from the folder structure. You can deploy the compiled assembly, and then include the file in the project again. This is especially important if you are working with a source control repository.

An advantage of using folder structure without project files in Web site projects is the following:

• You do not have to manage the project's structure exclusively in Visual Studio. For example, you can copy files into the project or delete them from the project by using File Explorer.

Compilation

---

For web application projects, you typically build the project in Visual Studio or by using the ASP.NET batch compiler on a computer that is not the production IIS server. All code-behind class files and standalone class files in the project are compiled into a single assembly, which is then put in the web application project's Bin folder. (The .aspx and .ascx files are compiled dynamically in a manner similar to what is done for web site projects.)

For web site projects, you do not have to manually compile the project. web site projects are typically compiled dynamically by ASP.NET (on both the development computer and the production IIS server). You can choose between batch compilation mode, which typically produces one assembly per folder, and fixed compilation mode, which typically produces one assembly for each page or user control.

Advantages of the compilation model for web application projects include the following:

• You can use MSBuild to create a custom batch-compilation process.
• It is easy to specify assembly attributes such as name and version.
• Compiling in advance makes sure that users do not have to wait while the site compiles on the production server. (If the site is very large, dynamic compilation of a web site project might take a noticeable amount of time. Dynamic compilation occurs when a request for a site resource is received after an update to the site, and the request that triggers compilation might be delayed while the required resources are compiled. If the delay is unacceptable, you can precompile the site. However, then some of the advantages of dynamic compilation are lost.)
• You have complete control over where you put code files in the project folder structure, and how you how classes in the project refer to each other. (Dynamic compilation requires that the source code for any classes that are used throughout the site must be in the App_Code folder. You cannot refer to a page or user control class from a class in App_Code.)

Advantages of the compilation model for Web site projects include the following:

• You can test specific pages regardless of the state of other pages. This is because running an individual page does not require that the whole site compile successfully, only the page and any components it depends on, such as code in the App_Code folder or the Global.asax file. (In a web application project, if there are compilation errors anywhere in the site, you cannot create the assembly and therefore cannot test even the pieces of the site that compile.)
• It is easy to update a Web site in production. You can update individual source code files on the production server without having to explicitly recompile the site. You can update individual files that are ready for deployment even if other files are not ready due to compile errors. You can also open the Web site on the production IIS server directly in Visual Studio and update the Web site in real time.
• Precompiling to multiple assemblies can have a performance advantage in some scenarios. A typical example is a site that has many pages with lots of code written for them. Most of the pages are rarely requested and only some are used frequently. If you compile a site like this into multiple assemblies, the production server can load only the assemblies that are required for the current requests. If a page is not requested, its corresponding assembly is not loaded.

Note

There is no difference in performance between a web site project and a web application project. The only significant exceptions are the ones that have already been noted, and as a practical matter they apply only to very large sites. The first request to the web site might require the site to be compiled, which can result in a delay. And if the web site is running on an IIS server that is short on memory, including the entire site in a single assembly might use more memory than would be required for multiple assemblies.

Deployment

---

To deploy a web application project, you copy the assembly that is created by compiling the project to an IIS server. In contrast, to deploy a web site project, you typically copy the project source files to an IIS server.

Advantages of the deployment strategy for web application projects include the following:

• You can avoid deploying source code to the IIS server. In some scenarios, such as shared hosting environments, you might be concerned about unauthorized access to source code on the IIS server. (For a web site project, You can avoid this risk by precompiling on a development computer and deploying the generated assemblies instead of the source code. However, in that case you lose some of the benefits of easy site updates.)
• Deployment often involves other tasks in addition to copying assemblies or code to a server. For example, database scripts might have to run in production, and connection strings in the Web.config file might have to be changed for a production server. Visual Studio provides tools such as one-click publish that work with web application projects to automate many of these tasks. These tools are not available for web site projects.

Advantages of the deployment strategy for web site projects include the following:

• If you make a small change to a web site, you do not have to redeploy the whole site. Instead, can copy just the changed file or files to the production IIS server. You can also edit files directly on the production server. (Because a web application project's code files are compiled into a single assembly file, you must deploy the whole site even for small changes, unless the only change is to an .aspx or .ascx file.) 

RAID in Linux

From: NextStep4IT

The term RAID is an acronym for the phrase, Redundant Array of Independent Disks. RAID is a way of combining the storage available across multiple disks and supplying users a single, unified virtual device.
RAID can be used to provide:

• data integrity
• fault tolerance
• improved performance
• greater storage capacity

Hard disks are mechanical devices involving moving parts and unfortunately tend to fail over time. There are also physical limits to the speed at which data can be read and/or written to disks. RAID helps mitigate this risk by protecting data stored on hard disks and improving disk performance by writing the data to multiple
physical locations according to several different schemas, known as "RAID Levels". Furthermore, RAID can be provided by either dedicated, specialized hardware or by the operating system at a virtual layer.

Hardware RAID vs. software RAID?

Hardware RAID solutions exist that operate as dedicated devices, usually as PCI expansion cards or directly on the motherboard. The independent disks attach to the hardware interface. In a true hardware RAID, the operating system simply writes data to the hardware RAID controller which handles the multiplicitous reads and writes to the associated disks. Other so−called hardware RAIDs rely on special drivers to the operating system; these act more like software RAIDs in practice. With current technology, hardware RAIDconfigurations are generally chosen for very large RAIDs.

Additionally, some operating systems, including Linux®, provide RAID functionality within a software layer.RAID partitions are logically combined and a virtual device appears to higher layers of the operating system in place of the multiple constituent devices. This solution is often a high−performance and inexpensive alternative available for RAID users.

 

RAID levels

There are many RAID levels. It will be impossible to list them all here. Here we mention the most common & most important RAID types, all of which are fully supported by Linux.

 

RAID0 (Striping)

 

This level is achieved by grouping 2 or more hard disks into a single unit with the total size equaling that of all disks used.

Practical example: 3 disks, each 80GB in size can be used in a 240GB RAID0 configuration.

RAID0 works by breaking data into fragments and writing to all disk simultaneously. This significantly improves the read and write performance.

On the other hand, no single disk contains the entire information for any bit of data committed. This means that if one of the disks fails, the entire RAID is rendered inoperable, with unrecoverable loss of data.

RAID0 is suitable for non-critical operations that require good performance, like the system partition or the /tmp partition where lots of temporary data is constantly written. It is not suitable for data storage.

Usable Space in Raid level0 = (smallest disk) * (no. of disks)

RAID1 (Mirroring)

 

This level is achieved by grouping 2 or more hard disks into a single unit with the total size equaling that of the smallest of disks used.

 

This is because RAID1 keeps every bit of data replicated on each of its devices in the exactly same fashion, create identical clones. Hence the name, mirroring. Practical example: 2 disks, each 80GB in size can be used in a 80GB RAID1 configuration.

 

Usable space of Raid level1= smallest-disk

On a side note, in mathematical terms, RAID1 is an AND function, whereas RAID 0 is an OR. Because of its configuration, RAID1 reduced write performance, as every chunk of data has to be written n times, on each of the paired devices. The read performance is identical to single disks.Redundancy is improved, as the normal operation of the system can be maintained as long as any one disk is functional.

RAID 1 is suitable for data storage, especially with non-intensive I/O tasks.

 

 

RAID5 

 

This is a more complex solution, with a minimum of three devices used. Two or more devices are configured in a RAID0 setup, while the third (or last) device is a parity device. If one of the RAID 0 devices malfunctions, the array will continue operating, using the parity device as a backup. The failure will be transparent to the user, save for the reduced performance.

RAID 5 improves the write performance, as well as redundancy and is useful in mission-critical scenarios, where both good throughput and data integrity are important. RAID 5 does induce a slight CPU penalty due to parity calculations.

Usable Space for Raid Level5 = smallest-disk*(no of disk -1)

 

Linear RAID

 

This is a less common level, although fully usable. Linear is similar to RAID0, except that data is written sequentially rather than in parallel. Linear RAID is a simple grouping of several devices into a larger volume, the total size of which is the sum of all members. For instance, three disks the sizes of 40, 60 and 250GB can be grouped into a linear RAID the total size of 350GB.

 

Linear RAID provides no read/write performance, not does it provide redundancy; a loss of any member will render the entire array unusable. It merely increases size. It's very similar to LVM. Linear RAID is suitable when large data exceeding the individual size of any disk or partition must be used.

Now, move backup to tape or other server:

 

Nested RAID Levels

 

RAID0+1

 

Exapmle of RAID Level 0+1

 

A RAID 0+1 (also called RAID01), is a RAID level used for both replicating and sharing data among disks.The minimum number of disks required to implement this level of RAID is 3 (first, even numbered chunks on all disks are built – like in RAID0 – and then every odd chunk number is mirrored with the next higher even neighbour) but it is more common to use a minimum of 4 disks.

 

The difference between RAID0+1 and RAID 1+0 is the location of each RAID system — RAID0+1 is a mirror of stripes although some manufacturers (e.g. Digital/Compaq/HP) use RAID0+1 to describe striped mirrors, consequently this usage is now deprecated so that RAID0+1 and RAID1+0 are replaced by RAID10 whose definition correctly describes the correct and safe layout, i.e. striped mirrors.

RAID1 + 0

A RAID1+0, sometimes called RAID1&0 or RAID10, is similar to a RAID0+1 with exception that the RAID levels used are reversed — RAID10 is a stripe of mirrors.

Hard disks are mechanical devices involving moving parts and unfortunately tend to fail over time. There are also physical limits to the speed at which data can be read and/or written to disks. RAID helps mitigate this risk by protecting data stored on hard disks and improving disk performance by writing the data to multiple