Microsoft Technologies On SQL Programming

What is SQL Server
is a standardized programming language used for managing relational databases and performing various operations on the data in them.
It is a software, developed by Microsoft, which is implemented from the specification of RDBMS. It is also an ORDBMS. It is platform dependent.

It is both GUI and command based software.

It supports SQL (SEQUEL) language which is an IBM product, non-procedural, common database and case insensitive language.

Uses of MYSQL Server
1) IT is used to create databases.
2)Very capable of  maintaing databases.
3)It's used to analyze the data through SQL Server Analysis Services (SSAS).
4)To generate reports through SQL Server Reporting Services (SSRS).
5)Lastly to carry out ETL operations through SQL Server Integration Services (SSIS).

Versions of SQL Server
below are the diffrent generations of SQL server

 Components of SQL Server
SQL Server works in client-server architecture, hence it supports two types of components − (a) Workstation and (b) Server.

Workstation components are installed in every device/SQL Server operator’s machine. These are just interfaces to interact with Server components. Example: SSMS, SSCM, Profiler, BIDS, SQLEM etc.

Server components are installed in centralized server. These are services. Example: SQL Server, SQL Server Agent, SSIS, SSAS, SSRS, SQL browser, SQL Server full text search etc.

Instance of SQL Server
An instance is an installation of SQL Server.
An instance is an exact copy of the same software.
If we install 'n' times, then 'n' instances will be created.
There are two types of instances in SQL Server a) Default b) Named.
Only one default instance will be supported in one Server.
Multiple named instances will be supported in one Server.
Default instance will take the server name as Instance name.
Default instance service name is MSSQLSERVER.
16 instances will be supported in 2000 version.
50 instances will supported in 2005 and later versions.

To install different versions in one machine.
To reduce cost.
To maintain production, development, and test environments separately.
To reduce temporary database problems.
To separate security privileges.
To maintain standby server.

SQL Server is available in various editions.
Enterprise − This is the top-end edition with a full feature set.

Standard − This has less features than Enterprise, when there is no requirement of advanced features.

Workgroup − This is suitable for remote offices of a larger company.

Web − This is designed for web applications.

Developer − This is similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation.

Express − This is free entry level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB.

Compact − This is free embedded database for mobile application development. The maximum size of the database is 4 GB.

Data center − The major change in new SQL Server 2008 R2 is Datacenter Edition. The Datacenter edition has no memory limitation and offers support for more than 25 instances.

Business Intelligence − Business Intelligence Edition is a new introduction in SQL Server 2012. This edition includes all the features in the Standard edition and support for advanced BI features such as Power View and PowerPivot, but it lacks support for advanced availability features like AlwaysOn Availability Groups and other online operations.

Enterprise Evaluation − The SQL Server Evaluation Edition is a great way to get a fully functional and free instance of SQL Server for learning and developing solutions. This edition has a built-in expiry of 6 months from the time that you install it.

SQL Server supports two types of installation

2.)Cluster based

Check RDP access for the server.
Check OS bit, IP, domain of server.
Check if your account is in admin group to run setup.exe file.
Software location.

Which version, edition, SP and hotfix if any.
Service accounts for database engine, agent, SSAS, SSIS, SSRS, if any.
Named instance name if any.
Location for binaries, system, user databases.
Authentication mode.
Collation setting.
List of features.

Pre-requisites for 2005
Setup support files.
.net framework 2.0.
SQL Server native client.

Pre-requisites for 2008&2008R2
Setup support files.
.net framework 3.5 SP1.
SQL Server native client.
Windows installer 4.5/later version.

Pre-requisites for 2012&2014
Setup support files.
.net framework 4.0.
SQL Server native client.
Windows installer 4.5/later version.
Windows PowerShell 2.0

Client − Where the request initiated.

Query − SQL query which is high level language.

Logical Units − Keywords, expressions and operators, etc.

N/W Packets − Network related code.

Protocols − In SQL Server we have 4 protocols.

Shared memory (for local connections and troubleshooting purpose).

Named pipes (for connections which are in LAN connectivity).

TCP/IP (for connections which are in WAN connectivity).

VIA-Virtual Interface Adapter (requires special hardware to set up by vendor and also deprecated from SQL 2012 version).

Server − Where SQL Services got installed and databases reside.

Relational Engine − This is where real execution will be done. It contains Query parser, Query optimizer and Query executor.

Query Parser (Command Parser) and Compiler (Translator) − This will check syntax of the query and it will convert the query to machine language.

Query Optimizer − It will prepare the execution plan as output by taking query, statistics and Algebrizer tree as input.

Execution Plan − It is like a roadmap, which contains the order of all the steps to be performed as part of the query execution.

Query Executor This is where the query will be executed step by step with the help of execution plan and also the storage engine will be contacted.

Storage Engine  It is responsible for storage and retrieval of data on the storage system (disk, SAN, etc.,), data manipulation, locking and managing transactions.

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. 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.

Checkpoint Process − Checkpoint is an internal process that writes all dirty pages (modified pages) from Buffer Cache to Physical disk. Apart from this, it also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also known as Hardening of dirty pages.

It is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process for each database individually. Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crash\Failure.

Checkpoints in SQL Server
In SQL Server 2012 there are four types of checkpoints −

Automatic − This is the most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval − Server Configuration Option.

Indirect − This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected, this will override the Recovery Interval specified for the server and avoid automatic checkpoint on such DB.

Manual − This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual checkpoint runs for your current database only. You can also specify the Checkpoint_Duration which is optional - this duration specifies the time in which you want your checkpoint to complete.

Internal − As a user you can’t control internal checkpoint. Issued on specific operations such as

Shutdown initiates a checkpoint operation on all databases except when shutdown is not clean (shutdown with nowait).

If the recovery model gets changed from Full\Bulk-logged to Simple.

While taking backup of the database.

If your DB is in simple recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.

Alter database command to add or remove a data\log file also initiates a checkpoint.

Checkpoint also takes place when the recovery model of the DB is bulk-logged and a minimally logged operation is performed.

Lazy Writer Process  Lazy writer will push dirty pages to disk for an entirely different reason, because it needs to free up memory in the buffer pool. This happens when SQL server comes under memory pressure. As far as I am aware, this is controlled by an internal process and there is no setting for it.

SQL server constantly monitors memory usage to assess resource contention (or availability); its job is to make sure that there is a certain amount of free space available at all times. As part of this process, when it notices any such resource contention, it triggers Lazy Writer to free up some pages in memory by writing out dirty pages to disk. It employs Least Recently Used (LRU) algorithm to decide which pages are to be flushed to the disk.

If Lazy Writer is always active, it could indicate memory bottleneck.
Microsoft Technologies On SQL Programming Microsoft Technologies On SQL Programming Reviewed by Joseph on May 11, 2018 Rating: 5

No comments:

Thanks foor reading this post. Kindly share to others on Facebook, Twitter and Google-Plus.

Use the comment box below to drop your thoughts and suggestions. Have a nice day!

Powered by Blogger.