0

Database Compatibility Levels – Introduction

Hello friends, let’s have a little idea about database compatibility levels and impact. I’ve phrased few common questions and answered those. Let’s have a look into those and feel free to ask in case of any confusion.

What is Database Compatibility Level?

Database compatibility level is just a number which indicates what all features your database still supports. This is used for backward compatibility. The compatibility level of a database dictates how certain language elements of the database function as it relates to an earlier version of SQL Server.  In a nutshell, this offers up partial “backward compatibility” to an earlier version. 

If you are having a database which is designed in Sql server 2000. In this case, compatibility level of your database will be 80.

Now your organization want to upgrade to Sql server 2005. You upgraded Sql server version and restored your old database to this newer version.

Perhaps you didn’t notice that there are few features which are there in Sql server 2000. You are using those features and now Sql server 2005 is not having those features. You may expect that those DB object will not work which are using old features. But you are wrong. Those features still work in newer version of Sql server. But how?

This is because of compatibility level. When you restored your database to newer Sql version then compatibility level of your database is still 80. This indicates to Sql server 2005 that there are still few features of Sql server 2000 which you may be using so those should not break.

So things will not break immediately. You have some time to find out these old features and replace those with new ones.

You can check compatibility level of your database from management studio.

Right click on your database and click on properties. Go to options tab and you’ll be able to see Compatibility Level.

In addition to this, you can check compatibility level of all databases using below query.

SELECT name, compatibility_level FROM sys.databases;

 

Who sets Compatibility Level?

Database compatibility level is initially set by Sql server. Following are few scenarios to understand this.

  1. You are creating a new database in Sql server 2012, then compatibility level of this database will be 110.
  2. If you are having a database which is created in Sql 2000 and now you are restoring this backup into Sql 2005 then compatibility level of this database will be 80.
  3. If you are having a database which is created in Sql 2000 and you created scripts for all DB objects. You created a new database in Sql server 2005 and run all scripts to make it replica of your old database. Then this new database will have compatibility level 90. Note: In this case your old features will not work as your database is having compatibility level according to Sql 2005.

Below is the chart of default compatibility levels of Sql server versions.

database compatibility levels

What is the Impact of this?

Let’s see this with an example. You have a database which is designed in Sql server 2000. So the compatibility of this database would be 80. Now suppose that your database is having few queries, stored procedures which are having non-ANSI joins.

select a.name,a.empno,b.loc
from tab a, tab b
where a.deptno *= b.deptno;

From above query you can see that we are using *= in query. This is kind of Left Outer Join which is supported in Sql version 2000 and lower.

Now you upgraded your Sql server to 2005 and restored your database. When you run your stored procedure which is having non-ANSI join, it’ll work fine because of compatibility level. Your database compatibility level is still 80.

If you change your database compatibility level to 90 then you may break things. So make sure that you replace all old features with new ones before upgrading compatibility level of your database.

Can I change it?

Of course, you can change it but now you understand impact of this. If you are using old features and changing compatibility level to new then you can break few stored procedure or other Db objects. So before changing compatibility level, make sure you have replaced all old features with new one.

Here is an example of how to set the database compatibility level in T-SQL:

ALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 120;

You can change it using management studio also. Just select compatibility level according to your case.

database compatibility levels change

With SQL Server 2014 and newer, the database compatibility level also controls whether the new cardinality estimator or the legacy cardinality estimator is used. The new cardinality estimator gives equivalent or better query performance for many to most queries, but it sometimes has very significant performance regressions. It is very important to do as much testing as possible before you change the compatibility level to 120 or higher.

Note:

  1. Don’t assume that this setting will let you restore the database backward to an older version. You cannot restore new database backup to older version. There are certain rule for downgrading which you can check here. Sql Server Downgrades-Rules & Restrictions
  1. Don’t think that compatibility level is same as database version. Both are different properties with different concept behind. The database version is an internal versioning system that defines what version of SQL Server the database was a recent resident of. Will cover more details about database version in upcoming articles.

Upgrade Advisor

We have an option to help with upgrades i.e. Upgrade Advisor. SQL Server Upgrade Advisor helps you prepare for upgrades to SQL Server 2014. Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade.

You can get more information about upgrade advisor here Upgrade Advisor

Feels free to experiment new things and connect to me.

2

Sql Server Native Client (SNAC) – Introduction

Hello friends, Today we are going to cover an important aspect of Sql server i.e. connectivity. In this article we’ll cover some basic details of SNAC i.e. Sql Server Native Client.

In this article I’ve used few terms like 

Windows Data Access Components, WDAC, Microsoft Data Access Components, MDAC. Please note that all are same. These are synonyms.

What is Sql Server Native Client?

SNAC

SQL Server Native Client is a stand-alone data access application programming interface (API), used for both OLE DB and ODBC. This was introduced in SQL Server 2005. SQL Server Native Client combines the SQL OLE DB provider and the SQL ODBC driver into one native dynamic-link library (DLL).

It also provides new functionality above and beyond that supplied by the Windows Data Access Components (Windows DAC, formerly Microsoft Data Access Components, or MDAC). SQL Server Native Client can be used to create new applications or enhance existing applications that need to take advantage of features introduced in SQL Server 2005. These new features are multiple active result sets (MARS), user-defined data types (UDT), query notifications, snapshot isolation, and XML data type support.

Note: We’ll discuss about these new features in upcoming articles.

The SQL Server Native Client ODBC driver is always used in conjunction with the ODBC Driver Manager supplied with Windows data access components. The SQL Server Native Client OLE DB provider can be used in conjunction with OLE DB Core Services supplied with Windows data access components, but this is not a requirement. The choice to use Core Services are not depends on the requirements of the individual application (for example, if connection pooling is required).

While SQL Server Native Client uses components in Windows DAC, it is not explicitly dependent on a particular version of Windows DAC. You can use SQL Server Native Client with the version of Windows DAC that is installed with any operating system supported by SQL Server Native Client.

Latest version of native client is 11.

Why we need it?

When deciding whether to use SQL Server Native Client as the data access technology of your application, you should consider several factors.

For new applications, if you’re using a managed programming language such as Microsoft Visual C# or Visual Basic, and you need to access the new features in SQL Server, you should use the .NET Framework Data Provider for SQL Server, which is part of the .NET Framework.

If you are developing a COM-based application and need to access the new features introduced in SQL Server, you should use SQL Server Native Client. And if you don’t need access to the new features of SQL Server, you can continue to use Windows Data Access Components (WDAC).

In case you are upgrading existing or developing new COM-based (or native) applications that will target the new features of SQL Server 2005 then you’ll need SNAC.

If you don’t need any of the new features of SQL Server 2005, then you don’t need to use SQL Native Client, your existing OLE DB and ODBC code will work just fine.

Of course, if you have or are planning on moving to a managed code base for data access, then the ADO.NET data access classes of the .NET Framework is what you should use.

How can we deploy?

When deploying an application that is dependent on SQL Native Client, you will need to redistribute SQL Native Client with your application. SQL Native Client is a component of SQL Server 2005. Therefore, it is important to install SQL Native Client in your development environment and redistribute SQL Native Client with your application.

SQL Native Client redistributable installation program name is sqlncli.msi. This is available on the installation media and is available as one of the SQL Server 2005 Feature Pack components on the Microsoft Download site. Below are links for that.

Below are the links to download SQL Server 2012 Native Client

x86 Package
x64 Package 

This was very basic introduction of SNAC. We’ll cover more aspects of connectivity in upcoming sessions.

0

Sql Server Licensing & Differences

Hi Friends, as we’ve already discussed about various Sql server versions and editions, it’s time to discuss about various sql server licenses available and what the difference among those is.

You can check various Editions here.

There are two type of licensing options available with Sql server 2012:

  1. Core based licensing
  2. Server+CAL licensing model

Core Based Licensing

Physical Machine:

In previous versions, sql server licenses were based on per processor based. Means if you are having one processor and this processor is having two cores then one license is enough to meet your needs.

But this is not the case in Sql 2012. Now we have core based licensing. That means if we are having a processor with 2 cores then we’ll need 2 Sql licenses.

Core-based licenses are sold in two-core packs with a minimum of 4 core licenses required for each physical processor. This means that at a minimum you need to purchase two of these two-core packs for each physical processor occupying a processor socket.

Below is the matrix for license required according to cores. We know that 2 double core license is minimum purchase so one will have to purchase license for 4 cores at minimum. As number of cores increases, number of sql server licenses also increase accordingly.

You can notice, even if you license a single or dual core processor, you still have to buy enough two-core packs to license the minimum of four cores. With this four-core minimum license per physical processor requirement of SQL Server 2012, it makes more sense to have a single four-core processor than two dual-core processors.

The  Per Core licensing option allows for unlimited users or devices to access SQL Server (both internally and externally). The major benefit of this option is that there is no requirement to try to quantify the number of users or devices accessing the SQL Server, be that direct or via indirect means using in between layers e.g. business logic layer.

Only Enterprise and Standard Editions of SQL Server 2012 are available for core-based licensing. 

Virtual Machine:

Let’s discuss how licensing will be required for virtual machines. Each virtual machine is treated as different machine and each core in these virtual machines will be considered as separate core.

Let’s take an example of one server:

These is one server. This server is having 2 VMs. One VM is having 2 Cores and second VM is having 6 cores.

  • Server-1
    • VM-1
      • Core-1
      • Core-2
    • VM-2
      • Core-1
      • Core-2
      • Core-3
      • Core-4
      • Core-5
      • Core-6

Now we know that for VM-1 we need sql server licenses for 2 cores but we know that we need to purchase minimum two 2-core packs i.e. we need to purchase licenses for 4 cores minimum.

So for VM-1 we need 4 sql server licenses.

In same way we need three 2-core packs for VM-2 which will give us 6 licenses. So to cover both VMs we need 10 licenses i.e. five 2-core packs licenses. 

Host server or server farm core licensing:

The total number of cores available on a host server or server farm can be licensed to maximize virtualization capabilities and take advantage of full computing power. To fully license a host server or server farm, Enterprise Edition core licenses along with Software Assurance are required.

There are different type of volume licencing options available:

Open Value License Program is the recommended program if you have a small to midsize organization with five or more desktop PCs and want to simplify license management, manage software costs, and get better control over your investment.

Open License Program is a Microsoft service that allows corporate, academic, charitable, or government organizations to obtain volume licenses for Microsoft products. It is ideally suited for companies with between 2 – 250 personal computers, but can accommodate organizations with up to 750 computers.

Select License Program was designed specifically for medium and large organizations with 250 desktop PCs or more that have mixed software requirements and want a simple, flexible, and affordable way to purchase the latest Microsoft technology on a “pay as you go” basis.

EA/SA (Enterprise Agreement/Software Assurance) Program is a volume licensing package offered by Microsoft. It primarily targets large organizations that have 500 or more personal computers. 

This type of licensing allows for an unlimited number of virtual machines and allows dynamic provisioning and de-provisioning of virtual processors.

Server + CAL Licensing

Physical Machine:

As name specified, in this model there will be one server license for server which will host Sql server. CAL is client access license which will be required for each and every user who is trying to connect to the Sql server.

These users may be connecting to Sql server directly by application installed on their desktops or these user can give call to any intermediate layer and this intermediate layer intern connect to the server. In both the cases same number of CALs are needed.

Now question may arise, why we need this licensing model.

The Server + CAL option is often the preferred licensing option when:

  1. Smaller SQL Server implementations exist and SQL is the supporting database for another application or smaller Intranet; and
  2. The number of users or devices accessing SQL Server can be quantified and Server + CAL is more cost effective than the Per Core model

Server + CAL Licensing for SQL Server 2012 is only available for the Standard and Business Intelligence Editions. Users need a CAL that is the same version or newer than the version of the licensed SQL Server they need to access. A SQL Server 2012 CAL can be used to access multiple licensed SQL Servers, including SQL Server 2012 Standard and Business Intelligence Editions. These new CALs can also be used for previous versions of SQL Server, including the Enterprise Editions of 2008 and 2008R2, for which the Server + CAL licensing model was still available.

Virtual Machine:

Now let’s discuss how Server+CAL licensing will be required for virtual machines. In this case also each virtual machine is treated as different machine but licenses are required for each VM, not for each core.

Let’s take above example once again:

These is one server. This server is having 2 VMs. One VM is having 2 Cores and second VM is having 6 cores. There are 20 users who are accessing these servers.

  • Server-1
    • VM-1
      • Core-1
      • Core-2
    • VM-2
      • Core-1
      • Core-2
      • Core-3
      • Core-4
      • Core-5
      • Core-6

So now there will be 2 sql server licenses required for both VMs. And 20 CALs are required for users who are accessing these servers.

Below is the matrix which shows which license is available with which edition.

Edition

Server + CAL

Core Based

Enterprise

NA

Yes

Business Intelligence

Yes

NA

Standard

Yes

Yes

Now you can think from your organization’s perspective, which one suite you.

 

0

SQL Server Downgrades – Rules & Restrictions

Hello Friends, Today we will discuss about rules according to which Sql server downgrades are possible. Like upgrades, these operations can be edition specific or version specific.

What is the need to downgrade?

But what is the need to downgrade? Well there is. Let’s say you have an application which is working fine with current edition\version of Sql server and then you decided to upgrade to new edition\version. You upgraded your database edition\version and then you saw that there are many issues exposed which could be new features related to security, availability etc. of new edition\version. And your application is not ready to handle those features without additional work.

Now to get things working, you’ll have to downgrade your Sql edition\version back to previous one and then decide upon the strategy to follow for future.

Again it is not possible to downgrade any edition, version without any rules or restrictions. There are some set of rules.

Let’s see how this works.

Edition Downgrades

If we want to downgrade Sql server edition then we need to first uninstall the higher edition and then install the lower edition of Sql server. There is no standard tool which can provide this downgrade out of the box. Uninstallation is the only option.

Now we may have few databases which were there in higher editions. What about those databases?

There are following steps to get these databases working on lower editions:

  1. To get those databases in lower editions, first we need to check if these databases are using any higher version specific features or not. A database created in SQL Server 2012 Enterprise Edition that uses enterprise edition features, such as Table Partitioning or Transparent Data Encryption (TDE), cannot be restored or attached to the SQL Server 2012 Standard Edition without first disabling these edition-specific features. To check this we can use below query.
SELECT [feature_name] FROM [sys].[dm_db_persisted_sku_features]

The [sys].[dm_db_persisted_sku_features] dynamic management view may return rows containing any of the following Enterprise or Developer Edition features:

  • Compression
  • Partitioning
  • Transparent Data Encryption
  • Change Capture

If no edition-specific features have been enabled in the user database, the [sys].[dm_db_persisted_sku_features] dynamic management view does not return any rows.

  1. Once we have list of all these edition specific features then we need to disable all these.
  2. Once we disabled all these features then we can back up the database.
  3. After installation of lower edition, we can restore\attach our database to lower edition and it should work.

Version Downgrades

It is same as edition downgrade, if we want to downgrade Sql server version then we need to first uninstall the higher version and then install the lower version of Sql server. There is no standard tool which can provide this downgrade out of the box. Uninstallation is the only option.

But there is one issue here. You cannot restore\attach those backups which were created in higher versions. The only option to get your database is, you’ll have to create new database in old version Sql and then run all DDL scripts, Data scripts in this newly created database to get everything back.

Following are the steps:

  1. In new version database, create DDL scripts for all objects i.e. tables, SPs, functions etc.
  2. Create all data scripts.
  3. Uninstall current version.
  4. Install older version which is required.
  5. Create new database and run all DDL scripts, DML scripts.

By using above flow we should be able to get our database in lower version.

Below are few points to consider and few points to understand while downgrading.

Database Compatibility Level:

Let’s discuss this with an example. Let’s say you are running your application in Sql server 2008 and now you decided to upgrade you Sql server to version 2012.  Did the upgrade and then you came to know that your application was using some features of Sql server 2008 which are no longer supported in sql server 2012.

Now in this case you need to change the application code so that it should stop using these un supported features of Sql server 2008 and start its alternative features of Sql server 2012. But by the time you make these changes into you application, you want your application to working. You don’t want your application to be in this broken mode.

In this case you will set compatibility level of your new database to Sql server 2008 compatibility level. This compatibility level ensures that it will support all those old features which were there in Sql server 2008. This way your application will not break and you’ll get time to upgrade application code so that it can start using new features of new version.

To see the compatibility level of each database, right click the database in SQL Server Management Studio and select Properties, then click the Options tab. See highlighted field below:

In the image above, I’m running a SQL Server 2008 database, which has compatibility level of 100, on a machine that is running an instance of SQL Server Express 2014 (see the Connection section on the bottom left, my instance name is SQLEXPRESS2014).

Another way to look at the compatibility level of each database on your server is to query the compatibility level column of the sys.databases table:

USE YourDatabaseName;
GO
SELECT compatibility_level
FROM sys.databases WHERE name = 'YourDatabaseName';
GO

The main reason for having compatibility levels is to allow for Backward Compatibility. Each compatibility level has its own set of features. You must program against those features when developing an application that uses the database. If you want to move your database to a newer instance of SQL Server but at the same time have it backwards compatible, you need to make sure the compatibility level stays the same. 

Below is the chart of compatibility levels of different Sql versions.

SQL Server 2012 supports only databases at compatibility levels 90, 100, and 110. Meaning it supports databases going back to SQL Server 2005 only.

Database Version:

Database compatibility levels are not the same as database version numbers. The database version number is an internal number associated with a specific structure of a database’s system tables containing metadata about various objects such as tables, columns, indexes, allocations, and details about the relational and physical structure of the database.

Below is the chart of different versions of Sql server databases.

To identify the version number of a specific SQL Server database, you can query the master.sys.sysdatabases table as follows

SELECT version
FROM master.sys.sysdatabases
WHERE name='AdventureWorks2012'

All databases attached\restored to a newer version of a SQL Server instance, upgrade automatically to the database version that the newer SQL Server instance supports. In the case of SQL Server 2012, all databases created, attached, or restored in a SQL Server 2012 instance convert to database version number 700.

0

SQL Server Upgrades – Rules & Restrictions

Hello Friends, Today we will discuss about rules according to which Sql server upgrades are possible. These operations can be edition specific or version specific.

It is not possible to upgrade any edition, version without any rules or restrictions. There are some set of rules.
Let’s see how this works.

Edition Upgrades

Most of the time organization make decisions about edition of Sql server based on the requirements which it has at that point of time. It chooses the edition which is minimum to fulfill all the requirements. But later in time when organization grows, it needs to expand its business, data and Sql server edition also to get more features.

You can get more information about features which are available in different editions here.

However this is considered a wise decision to install lower edition based on the current set of requirements but sometime it has its downsides also. When organization choose to upgrade to higher edition then it may need to upgrade its infrastructure also to get the full benefits of all the features which higher edition offers. Due to this additional cost sometime organizations opt to install higher edition at the first step to avoid any additional cost in between.

You can upgrade all editions of SQL Server 2012 except for the Compact Edition to higher tier SQL Server editions. For example, you can upgrade Express, Web, and Workgroup editions to Standard, Enterprise, or Data Center editions. You can upgrade the Standard Edition to only the Enterprise or Data Center editions.

Edition Upgrade Matrix

  • Evaluation edition can be upgraded to:
    • Web
    • Developer
    • Standard
    • Enterprise
  • Developer edition can be upgraded to:
    • Web
    • Standard
    • Enterprise
  • Standard edition can be upgraded to:
    • Enterprise
  • Web edition can be upgraded to:
    • Standard
    • Enterprise
  • Express edition can be upgraded to:
    • Web
    • Developer
    • Standard
    • Enterprise

According to above matrix you can find out that is it possible for the edition upgradation you are looking for. Based on this you can actually make decision on which edition you should go for. You should choose the edition so that in future you should be able to upgrade easily which any major problems.

Version Upgrade

When it comes to upgrade Sql server version then it can come with two flavors.

  1. Either you want to upgrade only version not the edition. E.g. you want to upgrade Sql server 2008 standard edition to Sql 2012 standard edition.
  2. Or you want to upgrade both e.g. you want to upgrade Sql server 2008 standard edition to Sql 2012 enterprise edition.

There are again set of upgrade which are possible. Below is the list of those:

Version Upgrade Matrix

Upgrade from

Supported upgrade path

SQL Server 2008 SP4 Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2008 SP4 Developer

SQL Server 2017 (14.x) Developer

SQL Server 2008 SP4 Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 SP4 Small Business

SQL Server 2017 (14.x) Standard

SQL Server 2008 SP4 Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2008 SP4 Workgroup

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 SP4 Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express

SQL Server 2008 R2 SP3 Datacenter

SQL Server 2017 (14.x) Enterprise 

SQL Server 2008 R2 SP3 Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2008 R2 SP3 Developer

SQL Server 2017 (14.x) Developer

SQL Server 2008 R2 SP3 Small Business

SQL Server 2017 (14.x) Standard

SQL Server 2008 R2 SP3 Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 R2 SP3 Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2008 R2 SP3 Workgroup

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 R2 SP3 Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express

SQL Server 2012 (11.x) SP2 Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2012 (11.x) SP2 Developer

SQL Server 2017 (14.x) Developer 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2012 (11.x) SP2 Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2012 (11.x) SP1 Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2012 (11.x) SP2 Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express 

SQL Server 2012 (11.x) SP2 Business Intelligence

SQL Server 2017 (14.x) Enterprise 

SQL Server 2012 (11.x) SP2 Evaluation

SQL Server 2017 (14.x) Evaluation 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Developer

SQL Server 2014 (12.x) Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2014 (12.x) Developer

SQL Server 2017 (14.x) Developer 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2014 (12.x) Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2014 (12.x) Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2014 (12.x) Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express 

SQL Server 2017 (14.x) Developer

SQL Server 2014 (12.x) Business Intelligence

SQL Server 2017 (14.x) Enterprise 

SQL Server 2014 (12.x) Evaluation

SQL Server 2017 (14.x) Evaluation 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Developer

SQL Server 2016 (13.x) Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2016 (13.x) Developer

SQL Server 2017 (14.x) Developer 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2016 (13.x) Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2016 (13.x) Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2016 (13.x) Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express 

SQL Server 2017 (14.x) Developer

SQL Server 2016 (13.x) Business Intelligence

SQL Server 2017 (14.x) Enterprise 

SQL Server 2016 (13.x) Evaluation

SQL Server 2017 (14.x) Evaluation 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Developer

SQL Server 2017 (14.x) release candidate*

SQL Server 2017 (14.x) Enterprise

SQL Server 2017 (14.x) Developer

SQL Server 2017 (14.x) Enterprise

 

So one can make decisions of which edition\version it should buy so that it could be upgraded easily to new version\edition.

0

Database Extensibility With Agile

Hi Friends, Today we are going to discuss about database extensibility and how this can be fit in business scenario for agile development.

What is Database Extensibility?

Database extensibility is the factor that how easy a database can adopt new changes without any major schema refactor or side effects.

If database schema is created in such a way that a small change in any table is causing you major changes in stored procedures, functions and client code to get data then this can be considered as a poor database design.

Database objects like tables, stored procedures, functions, views, triggers etc. should be written in such a way that it should be able to adopt new changes very easy with minimum or no code changes.

There are few guidelines to make database more extensible.

  • Tables should be normalized properly.
  • Generalization should be there to extend specific domain entities.
  • Data duplicity should not be there in multiple tables.
  • One table should hold specific domain entity requirement.
  • Do not use a table to hold non relevant data with various nulls in columns.

What is Agile Development Methodology?

Being developers we very well aware of waterfall method. Waterfall is a development methodology in which we have a major part of a project goes sequential. Following are the steps:

  1. We do requirement gathering of a major project or major part of any project.
  2. We prepare designs, get approvals.
  3. Code the entire solution in one go.
  4. Testing
  5. Control

These stages are sequential stages but the main problem is volume of work. However this methodology work fine but when it comes to larger projects then each stage takes significant amount of time and efforts. And when we achieve final implementation, till that time there could happen many business\requirement changes. These changes can create issues or again initiate a new waterfall process to tackle those changes.

In waterfall model, one has to wait till the completion of final code and product is visible.

To cater this issue, agile methodology came into picture. This is iterative approach having 15\21\30 days of sprints. Each sprint deliver a working code which can be demonstrated to client and any requirement changes can be incorporated at early stage of development.

This way one can save lots of rework and client\team can see work in progress and validate it at the same time against requirements.

Why we need database extensibility?

As we know that agile works through small iterations. As we cycle through iterations, an extensible database absorbs new business requirements with less refactoring.

If database design is poor then in further stages\iteration it will be messy to get everything fit into the database. So one should make decision at the earlier stages of designing and make database as much extensible.

Benefit of good schema

There are several benefits of good database design. Below are few.

Set Based Queries

SQL Server is designed to handle data in sets. SQL is a declarative language e.g. query describes the problem, and the Query Optimizer generates an execution plan to resolve the problem as a set.

Iterative T-SQL code is code that acts upon data one row at a time instead of as a set. The implementation generally happens via cursors. This forces the database engine to perform thousands of wasteful single-row operations, instead of handling the problem in one larger, more efficient set.

This is why set-based queries, based on an obvious physical schema, are so critical to database performance.

Indexing

A sound indexing strategy identifies a handful of queries that represent 90 percent of the workload. With judicious use of clustered indexes and covering indexes, solves the queries without expensive lookup operations.

An elegant physical schema, well-written set-based queries, and excellent indexing reduce transaction duration. And this implicitly improves concurrency and sets up the database for scalability.

Concurrency

A database with an excellent physical schema, well-written set-based queries, and the right set of indexes will have tight transactions hence perform well with multiple users.

When a poorly designed database displays symptoms of locking and blocking issues, transaction isolation level tuning only partially alleviates the problem. The sources of the concurrency issue are the long transactions and additional workload caused by the poor database schema. Concurrency tuning cannot overcome the deficiencies of a poor database design.

Scalability

The database component is the principle factor determining the overall monetary cost of the database. A well-designed database minimizes hardware costs. It simplifies data access code and maintenance jobs. It significantly lowers both the initial and the total cost of the database system.

Lower maintenance cost leads to scale systems easily.

You can read more about database design principals here.

We can see that a good database schema is the back bone of overall system. It can give countless direct\indirect benefits. While a poor designed database can create issues unexpectedly.

0

Database Design Principles

Hello friends. As we are in process of exploring good database architecture and concepts so it is very important to look into few Database Design Principles of a good database architecture. These database design principles are basis for any good database and should be kept in mind always while designing database systems.

Before going into principle part, let’s first discuss about what is information. Information is an valuable organizational asset and according to its value and scope, it must be organized, stored, secured, and should be readily available in a usable format for daily operations and analysis by individuals, groups, and processes, both today and in the future.

Everything is an information which is valuable to someone and can be used in productive or destructive ways. As there could be many dangerous impact of information if it is in wrong hands so it is very important to organize the information in secure way.

Looking into all above key attributes of information, let’s discuss now good information management system.

There are six main objectives which must be fulfilled effectively by a good database.

  1. Usability
  2. Extensibility
  3. Data Integrity
  4. Performance
  5. Availability
  6. Security

Let’s discuss a little in detail.

Usability

Any information which we are storing in any organization should be meaningful for that organization. If we are storing those factors which are actually not fit with organization’s requirement then this is just waste of resources.

Primary objective of any information system should be to meet organization requirements. Following are few points to consider while going to start an architecture.

  1. Properly get details about requirements.
  2. See how information can be fit with requirement.
  3. Trace requirement matrix to capture mapping of information architecture and requirements.
  4. Organize it simple.
  5. Decide upon format of data so that could be easily converted to meaningful representation.

Extensibility

As we know that everyday new business requirements come up and every day there is a need to change or enhance information system to capture new requirements. So information design should be extensible so that it can adopt new requirements without much efforts or without major breaking changes.

If your initial design is too much complex or unorganized then it may create trouble for you to adopt new things effectively.

Following are few points to consider when thinking of extensibility.

  • Normalization and correct handling of optional data.
  • Generalization of entities when designing the schema.
  • Data-driven designs that not only model the data but also enable the organization to store the behavioral patterns or flow which can be hooked up in different stages of information processing.
  • A well-defined abstraction layer that decouples the database from all client access, including client apps, middle tiers, ETL, and reports.
  • Extensibility is also closely related to simplicity. Complexity breeds complexity. A simple solution is easy to understand and adopt, and ultimately, easy to adjust later.

 

Data Integrity

Now at this point we understand that information is very much important for any organization. Based on the historic information, every organization makes different strategies, decisions for growth. One small mistake in data can lead to major issues with any organization’s key decision and hence a big risk for growth.

When we are designing a good information system then we must keep in mind about integrity, correctness of data. Our system should be smart enough to handle incorrect, missing data attributes and based on that it should either take corrective actions or straightaway reject the data. Incorrect data should not be present in system or at least should not exposed to individuals creating misunderstanding.

Data integrity can be of many types.

Entity Integrity

Involves the structure (primary key and its attributes) of the entity. If the primary key is unique and all attributes are scalar and fully dependent on the primary key, then the integrity of the entity is good. In the physical schema, the table’s primary key enforces entity integrity.

Domain Integrity

It defines that data should be of correct type and we should handle optional data in correct way. We should apply Nullability to those attributes which are optional for organization. We can define proper data types for different attributes based on organization’s requirement so that correct format data should present in system.

Referential Integrity

This defines if any entity is dependent on another one then parent entity should be there in the system and should be uniquely identifiable. We can do this by implementing foreign keys.

Transactional Integrity

This defines that transaction should have its ACID properties. Any transaction should be atomic, consistent, durable and isolated. The quality of a database product is measured by its transactions’ adherence to the ACID properties:

Atomic — all or nothing

Consistent — the database begins and ends the transaction in a consistent state

Isolated — one transaction does not affect another transaction

Durable — once committed always committed 

User defined integrity

There are few business rules which we cannot validate just by primary keys, foreign keys etc. There has to be some mechanism so that we can validate complex rules for integrity. We can implement these rules in following ways:

  • Check Constraints
  • Triggers & Stored Procedures
  • Queries to identify incorrect data and handle in correct way.

 

Performance

As we know that information should be readily available as requested. Performance of the system should be up to the mark. As data in increasing day by day so at some time there will be impact on performance if database design is poor or we’ll not take any actions to improve performance.

Following could be few strategies which we can implement when there is need as data increases.

  • A well-designed schema with normalization and generalization
  • A sound indexing strategy, including careful selection of clustered and nonclustered
  • Tight, fast transactions that reduce locking and blocking
  • Partitioning, which is useful for advanced scalability

 

Availability

The availability of information refers to the information’s accessibility when required regarding uptime, locations, and the availability of the data for future analysis. Disaster recovery, redundancy, archiving, and network delivery all affect availability.

Following are the factors which impact availability:

  • Quality, redundant hardware
  • SQL Server’s high-availability features
  • Proper DBA procedures regarding data backup and backup storage
  • Disaster recovery planning

Security

For any organizational asset, the level of security must be secured depending on its value and sensitivity. Sometime organizations has suffered a lot because of data leaks which results in loss of faith and tends to business risk. So security is one of the most important aspect of good database design.

We can enhance Security by the following:

  • Physical security and restricted access of the data center
  • Defensively coding against SQL injection
  • Appropriate operating system security
  • Reducing the surface area of SQL Server to only those services and features required
  • Identifying and documenting ownership of the data
  • Granting access according to the principle of least privilege, which is the concept that users should have only the minimum access rights required to perform necessary functions within the database
  • Cryptography — data encryption of live databases, backups, and data warehouses
  • Metadata and data audit trails documenting the source and veracity of the data, including updates

Based on above principles, one should start designing databases and architectures.

0

Database Core Components & Basic Introduction

Hi Friends, Today we are going to discuss about Database Core Components\Tools of Sql server and purpose for those. It’s a very basic and introductory information which I am going to provide here. But we’ll discuss each component in details in upcoming sessions.

So let’s start.

Database Engine

The Database Engine component of SQL Server is the core service for storing, processing, and securing data. This is the component which actually execute queries and process the data. This component secure data by storing it in various data files.

By installing database engine, you can create new databases on that instance and store your data. We cal this as relational engine also. Sql is a descriptive language so it passes the query to the engine and then engine is responsible to handle all relational work.

Whenever we connect to any database using tools like management studio for queries, we actually connect to database engine. 

SQL Server Agent

This is the component which use to take care of all automated tasks and jobs. It maintains list of all Sql jobs, their tasks and alerts. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand.

Sql server agent is a window service which runs in background. We can configure this service to start on windows startup.

SQL Server Agent Components:

  • Jobs:job is a specified series of actions that SQL Server Agent performs. 
  • Schedules:schedule specifies when a job runs.
  • Alerts: An alert is an automatic response to a specific event. 
  • Operators: An operator defines contact information for an individual responsible for the maintenance of one or more instances of SQL Server. 

SQL Server can notify operators of alerts through one or more of the following:

  • E-mail
  • Pager (through e-mail)
  • net send 

Database Mail

Database mail component is specifically for sending mails. There could be need of sending mails as alerts in case of any scheduled task failure. So one can configure SMTP and database mails, so that it can send automated alerts.

Not only automated alerts but one can send mails through T-Sql code also. The mail can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.

MSDTC

MSDTC (aka Microsoft Distributed Transaction Control) is a software or we can say it’s a windows service which is used by SQL Server and other applications when these want to make a distributed transaction between more than one machine\servers. This uses dual phase commit protocol.

When one server starts a transaction and this transaction include few statements which needs to be executed on another server and we want to secure this complete batch by one transaction, then MSDTC comes into picture. It tracks all success\failure of all statements and commits the entire batch if all statements are succeeded.

Business Intelligence

Business intelligence (BI) is a package or set of tools that enable the management of data for the purpose of analysis, reporting, mining, and visualization.

SQL Server includes three services designed for BI: Integration Services (IS, sometimes called SSIS for SQL Server Integration Services), Reporting Services (RS), and Analysis Services (AS).

SSIS

This is integration service which one can use for integration of data from various sources. It has a visual interface which we can use to design packages.

We can say SSIS as ETL i.e. Extract-Transform-Load tool. We can design package where it can fetch data from other source, file etc. and then perform the transformation according to requirement and then load to another system\file.

As it has visual interface so it becomes very easy to perform all these transformations\load rather than T-Sql. 

SSRS

Reporting Services (RS) for SQL Server 2012 is a full-featured, web-based, managed reporting solution. RS reports can be exported to PDF, Excel, or other formats with a single click and are easy to build and customize.

One can schedule these to pre-created and cached for users, e-mailed to users, or generated by user on-the-fly with parameters

There is new functionality in SSRS 2012 with the addition of Power View. This is a SharePoint integrated feature that provides for rich drag and drop visualization and data exploration. It is one of the hottest new features in SQL 2012.

SSAS

The Analysis Services service hosts two key components of the BI tool set: Online Analytical Processing (OLAP) hosts multidimensional databases where data is stored in cubes, whereas Data Mining provides methods to analyze datasets for nonobvious patterns in the data.

Building cubes in a multidimensional database provides a fast, pre-interpreted, flexible analysis environment. One can include robust calculations in a cube for later query and reporting. We can use these results to reporting or pivot table later.

One can use data mining to explore non trending patterns and specific patterns based on various algorithms. This can be very useful to perform data analysis so that business can get information about specific data patterns and make business strategies according to that analysis.

 

In addition to above components, there are few tools which are also very popular and very much used to worth discussing about.

Management Studio

This is a tool which gives you visual interface for servers, databases, tables, schema etc. One can connect to any database engine and query.

This is very useful tool which is used by almost all those who involves in database work either developer or DBA.

 

Configuration Manager

We can use this tool to start and stop any server, set the start-up options, and configure the connectivity. One can launch it from the Start menu or from Management Studio. It can show you all the services and servers running on a particular server.

It looks something like below image.

Profiler

Sql profiler is a visual tool which can track almost all events on any server. You can check queries, execution reports etc. One can use it for debugging\troubleshooting and performance purpose.

Profiler is being deprecated in favor of extended events.

Performance Monitor

When we install Sql server, it installs many performance counters. We can see these counters in Windows’s administrative tools. One can use these counters to check Sql server performance in terms of memory usage, I\O etc.

 

DB Engine Tuning Wizard

The Database Engine Tuning Adviser analyzes a batch of queries (from Profiler) and recommends index and partition modifications for performance. Database Engine Tuning Adviser examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning.

 

Command Line Utilities

Sql provides few command line utilities to perform specific tasks through command. Following are few examples.

SqlCmd:

One can run this command and provide query to execute on database. Database connection will be provided as parameters in this utility.

sqlcmd -d AdventureWorks2012 -q "SELECT TOP 5 FirstName FROM Person.Person;SELECT TOP 5 LastName FROM Person.Person;"

BCP:

The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. One can use BCP utility to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. 

Below is the sample to export query data into file.

bcp "SELECT FullName, PreferredName FROM WideWorldImporters.Application.People ORDER BY FullName" queryout D:\BCP\People.txt -t, -c –T

Above is very basic information about major tools and components. In upcoming sessions we’ll discuss more details about each of these.

0

SQL Server 2012 Editions & Differences

Hi Friends, Today we are going to discuss SQL server 2012 editions and various features which these editions support or not. However, I tried to compile all major features here but there is a huge list so trying to cover only major points.

So let’s start

SQL Server 2012 Editions

Below is the list of SQL server 2012 editions.

Principal Editions of SQL Server:

  1. Enterprise
  2. Business Intelligence
  3. Standard

Specialized Editions of SQL Server 2012:

  1. Web

Breadth Editions of SQL Server 2012:

Breadth editions of SQL Server are engineered for specific customer scenarios and are offered FREE or at a very nominal cost. 

  1. Developer 
  2. Express
  3. Evaluation

Features Support

Let’s understand each feature and see what all SQL server 2012 editions support the feature.

Maximum Compute Capacity Used by a Single Instance

This is the computing power which Sql server instance is going to consume. As we know that these days servers are having multiple sockets. Each socket is filled with multi-core units. Each unit have hyper threading enabled. So there is too much processing power a single server can have. It is important to know that what is the maximum processing power, a single Sql server instance can use.

Database Engine:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Limited to lesser of 4 Sockets or 16 cores

Limited to lesser of 4 Sockets or 16 cores

Limited to lesser of 4 Sockets or 16 cores

Operating system maximum

Limited to lesser of 1 Socket or 4 cores

Operating system maximum

 Analysis Services, Reporting Services:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Operating system maximum

Limited to lesser of 4 Sockets or 16 cores

Limited to lesser of 4 Sockets or 16 cores

Operating system maximum

Limited to lesser of 1 Socket or 4 cores

Operating system maximum

Maximum Memory Utilized (per instance of SQL Server)

These day servers can too much memory by installing high capacity or multiple RAM. But when it comes to use this memory by Sql server then it have few limitations. Let’s see what these limitations are.

Database Engine:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

64 GB

64 GB

64 GB

Operating system maximum

1 GB

Operating system maximum

 Analysis Services:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Operating system maximum

64 GB

NA

Operating system maximum

NA

Operating system maximum

 Reporting Services:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Operating system maximum

64 GB

64 GB

Operating system maximum

4 GB

Operating system maximum

Maximum Relational Database Size

As name implies, this is the maximum database size. This take disk space.

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

524 PB

524 PB

524 PB

524 PB

524 PB

10 GB

524 PB

Log Shipping

SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations.

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

NA

NA

NA

Database Mirroring

SQL Server database mirroring is a disaster recovery and high availability technique that involves two SQL Server instances on the same or different machines. One SQL Server instance acts as a primary instance called the principal, while the other is a mirrored instance called the mirror. In special cases, there can be a third SQL Server instance that acts as a witness. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes (Safety Full Only)

Yes (Safety Full Only)

Witness only

Yes

Witness only

Yes

 Safety Full Only:

Data is written and committed on the principal and mirror databases synchronously. Only after committing on both databases, the database application can continue with activity.

 Witness Only:

The witness is the one that brings the third vote into the mix in order to create a quorum. Principal and mirror each has one vote, so they can tie 1:1 and no decision can be made. With a witness there can be a quorum established 2:1 or 1:2 (or o:2 if the principal is unreachable) and therefore a decision can reached who should be the new principal.

AlwaysOn Failover Cluster Instances

As part of the SQL Server AlwaysOn offering, AlwaysOn Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI). An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes (Node support: Operating system maximum)

Yes (Node support: 2)

Yes (Node support: 2)

NA

Yes (Node support: Operating system maximum)

NA

Yes (Node support: Operating system maximum)

Online Index

In ONLINE mode the new index is built while the old index is accessible to reads and writes. Any update on the old index will also get applied to the new index. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

NA

NA

NA

Yes

NA

Yes

Online Schema Change

Another feature that can increase uptime is the ability to perform online schema changes where you can change a table’s schema and that table can be continuously available. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

NA

NA

NA

Yes

NA

Yes

Table and Index Partitioning

The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database. Partitioning can make large tables and indexes more manageable and scalable. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

NA

NA

NA

Yes

NA

Yes

Auditing

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

Yes

Yes

User Defined Roles

SQL Server 2012, you can create user defined server roles and add server level permissions to the user defined server roles. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Replication

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes (Subscriber only

Yes

Yes (Subscriber only

Yes

Various Tools

These are various tools available with Sql Server.

SQL Management Objects (SMO), SQL Configuration Manager, SQL CMD (Command Prompt tool): 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

Yes

Yes

SQL Profiler: 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

No

Yes

No

Yes

SQL Server Agent: 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

No

Yes

These are few major features which I listed. I’ll keep on adding other features in this article.

0

SQL Query Execution & Components Involved

Hi Friends, Today we are going to discuss about SQL query execution steps and all components which participates from start to end while performing this execution.

SQL Query Execution

Below is a very high level flow of information and control.

SQL Query Execution

 

There are following components which participate:

  1. Parser
  2. Algebrizer
  3. Query optimizer
  4. Query engine
  5. Storage engine
  6. Buffer manager
  7. Lock manager
  8. Resource monitor
  9. SQLOS

Let’s discuss one by one about role of each of above listed components. However every component in itself is a bible but I am trying to give little detail of each.

Parser

Whenever we write and submit query for execution, then parser is the first component which starts the work. During the parsing stage SQL Server performs basic checks on the code i.e. looks for invalid SQL syntax, incorrect use of reserved words, column and table names, and so on.

If parsing detects any errors, the process stops and the errors are returned.

If parsing completes without errors, it generates a parse tree. Parser passes this tree onto the next stage of query processing i.e. Algebrizer. The parse tree is an internal representation of the query.

Algebrizer

It does following tasks:

Step 1: Name resolution:

The algebrizer resolves all the names of the various objects, tables and columns in the query. It confirms that all objects exist and are visible in the security context of the user. This is where it checks the table and column names to ensure that they exist and that the user has access to them.

This step is important because the query may have aliases or synonyms, names that don’t exist in the database, that need to be resolved. When objects don’t exist in the database, SQL Server returns an error from this step, defining the invalid object name.

Step 2: Type derivation:

The algebrizer identifies, at the individual column level, all the data types for the objects being accessed. Determines the final type for each node in the parse tree.

Step 3: Aggregate binding:

It also determines the location of aggregates (such as GROUP BY, and MAX) within the query. This process is aggregate binding.

The algebrizer outputs a binary tree i.e. query processor tree. It passes this tree to the query optimizer. The algebrizer’s output includes a hash, a coded value representing the query. The optimizer uses the hash to determine whether there is already a plan generated and stored in the plan cache. If there is a plan there, the process stops here and that plan is used. This reduces all the overhead required by the query optimizer to generate a new plan.

Query Optimizer

The SQL Server Query Optimizer is a cost-based optimizer. It analyzes various execution plans for a given query, estimates the cost of each of these plans and then selects the plan with the lowest cost. Query Optimizer cannot consider every possible plan for every query so it actually has to do a cost-based balancing act, considering both the cost of finding potential plans and the costs of plans themselves. Therefore, it is the SQL Server component that has the biggest impact on the performance of databases. 

Query Engine

The query engine (or query processor) takes the query plans which query optimizer finalized and, no surprise here, executes them. There are many functions which query engine performs, some of them being:

  • It acts as a dispatcher for all commands in the sql query execution plan.  It iterates through all the commands in the plan until the batch is complete.
  • And it interacts with the storage engine to retrieve and update data from tables and indexes.

Storage Engine

In a database engine, the Storage Manager (sometimes called a Storage Engine or Storage System) is responsible for all aspects of storage, transactional requirements, low-level concurrency, and indexing.  It usually contains something like the following modules:

  • The Buffer Manager/Buffer Pool
  • The Lock Manager (for non-metadata locks)
  • Low-level I/O Subsystem
  • The Transaction and Recovery Manager
  • Index Manager(s)
  • The Base Table Manager
  • Top-level APIs

Buffer Manager

The SQL Server Buffer manager monitors how SQL Server uses memory to store data pages. It monitors the physical Input/output as SQL Server reads and writes database pages. It also monitors the buffer cache by using fast non-volatile storage such as solid-state drives (SSD). Buffer manager pre-fetch data pages to improve sql query execution.

Lock Manager

Lock managers implement the storage engine’s notion of locking.  They are usually Table-level locks, Page-level locks (rare nowadays), or Row-level locks.

Resource Monitor

SQL Server Resource monitor was introduced in SQL Server 2008. This feature controls the consumption of the available resources, by limiting the amount of the CPU, Memory and IOPS for incoming sessions. It tries to prevent performance issues that are caused by resources high consumption.

The Resource monitor simply differentiates the incoming workload and allocates the needed CPU, Memory and IOPS resources based on the predefined limits for each workload. In this way, the SQL Server divides resources  among the current workloads reducing the possibility of consuming all resources by single workload type.

SQLOS

SQLOS performs the following critical functions for SQL Server:

Scheduler and IO completion:

The SQLOS is responsible for scheduling threads for CPU consumption. Most threads in SQL Server run in cooperative mode. It means that the thread is responsible for yielding so that other threads can obtain CPU time. Most IO is asynchronous.  The SQLOS is responsible for signaling threads when IO is completed.

Synchronization primitives:

SQL server is a multi-threaded application, so SQLOS is responsible for managing thread synchronizations.

Memory management:

Different components within SQL Server, example plan cache, CLR, lock manager etc request memory from the SQLOS. Therefore, the SQLOS can control how much memory a component within SQL Server is consuming.

Deadlock detection and management of the same.

Exception handling framework.

Hosting services for external components such as CLR and MDAC:

SQL Server will run threads that are associated with external component in preemptive mode. Preemptive mode allows the SQLOS to prevent runaway threads (threads which will not yield and allow other threads to get CPU execution). Also the SQLOS can keep track of the memory, these external components consume. For example, for CLR the SQLOS can invoke garbage collection if the CLR process is taking up too much memory.

As you can see, SQLOS seems to be a great resource management capability as it makes sure the SQL Server instance can be always up and running.

 

Above is very high level top view of each mentioned components involved in sql query execution. We’ll dive deep into these in upcoming articles.