17/07/2018

SQL Server Part 1

Client-Server System

If you move in the Past, you’ll get to know that in the early 1980s. You’ll see the applications running on PCs that access the local data files. This creates a problem because multiple users won’t able to share the data. As things evolved, databases installed to be on Servers this enable the multiple users to share the data on the network. And we need something to manage the data this was the relational database system come into play. They do so with the Structure Query Language (SQL). In this simple text, they get passed to the server from the client application. The DBMS on turns take care of all the file known necessary to perform the operation the users requested which could be inserted, update, delete, retrieve, read, modify data etc.

SQL Server Part 1

N-Tier Systems

Now, these client-server systems evolved in the complicated scenarios architecturally. We found the need to break the part of the business rules into components and hosts those on servers. Those servers are called the App Servers (they provide the place to host application logic through components). Now we have the request coming from the client PC up through the Application Server. The business logic gets supplied return Data Access Component running on the App Server will formulate the SQL queries going up to the relational database engine which brings the result set back through the App Server formulating the response comes back to the Client PC.

SQL Server Part 1

This evolved even further with the Internet Application. And here the Web App Server returns the HTML or JS to the Client PC.

SQL Server Part 1

Relational Database

  • Relational Database break data into multiple different tables.
  • Tables are made up of Columns and Rows.
  • Column defines datatypes like varchar, nvarchar, integers, images etc.
  • Rows are uniquely identified by the Primary Key Column.
  • Rows in one table are related to rows in another table using a special Foreign Key Column. The foreign key almost like a lookup.
  • This approach reduces redundant data storage.
  • You can imagine if we trying to store all of the information about a customer and orders in a single spreadsheet. We would wind up duplicating for each order with its place.
  • You don’t have to worry about file input-output yourself. All this is handled by RDBMS.
  • They even have indexes which can apply to different columns. This makes the dramatically speed up retrieval of data. You can feel the importance of Index with the help of a single Book. If we use the Index to find something in a book then it will be so much easy otherwise we have to read out the complete book to find something from the book.
  • There are also many popular Relational Databases like Oracle, DB2 (IBM), MySQL (Open Source), SQL Server.

Structured Query Language

Whenever we talk about the SQL, we need to understand it is an ANSI Standard used to communicate with the database. So even though there are multiple different Relational Database they always follow in some of the ANSI fashion standards. The type of the SQL is called Declarative Language that means we explain to the database what we want, we don’t tell the database how to go data retrieving.

  • All Relational Databases Use Common Sub Language Keywords:
    • Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE)
    • Data Definition Language (CREATE, ALTER, DROP)
    • Data Control Language (GRANT, DENY, REVOKE)

It allows us to manage the Permissions for Users in the Database to access objects

GRANT: To Allow the User to Do Something

DENY:   To Explicitly Deny The User

REVOKE: It is Something Neutral

Is It Possible that We Write the Query on SQL Server and Run on Oracle or DB2?

Well, it is more difficult, you see each of the database renders have different dialogues for SQL. So there is minor differences between them also there are different built-in functions that make it difficult to move from one database to another. It usually requires some Rewriting at one level or another to be able to move it.

SQL Server

SQL Server is nothing but an implementation of the relational database. A big pile of data that a lot of people can access at once some may be reading, some may be writing it, some may be changing it. Its highly scalable implementation of the relational database. SQL Server itself is a collection of Services, Applications, Libraries. And when we put them all together, we call them SQL server.

You might know about SSMS (SQL Server Management Studio) which is the GUI Application and it is used to develop an application for SQL Server and to manage it. SSMS to SQL Server to what Visual Studio is to .Net Applications. Working with GUI sometimes the very productive way to create applications but other times it just more convenient to work from the command line. And SQL Server gives us a couple of applications we can use to work from the command line Sqlcmd and the other is called Powershell. And Powershell is the name imply a command shell. The principal difference between Powershell is it process objects, not text, as UNIX shell does. Powershell isn’t the part of SQL Server directly, but SQL server includes an extension to Powershell called a PowerShell provider. Sqlcmd is a command line application that allows you to access SQL server from the command line.

There are some other applications as well.

  • SQL Server Profiler

It allows you to make it trace of all the commands that being sent to SQL server. You can capture the log and you can save this log to a regular file. You can save it to a table or save it to a table on a different machine. It is also useful when you wanna optimize the performance of SQL server

  • Database Engine Tuning Advisor

It is just used to optimize the performance of the sql server. And it does this by analyzing the commands being sent to sql server in figuring out where the best place is to put index and stuff like that. In fact, typically you collect a trace of what’s going on in your server in real operations using sql server profile.

Database Engine

If we want to connect to the table’s database different users, different roles then we need to connect with Database Engine. When you’re creating a new development type database.

Click on (in Object Explorer)

Databases > New Database > Name the Database.

Make sure to change the recovery model to Simple because if you don’t. SQL Server is gonna build up very large transaction log probably you don’t care about. And you’ll end up having this database stop running to your clean up that log. Typically for development purposes, we always gonna use Simple recovery model

SQL Server Part 1

When you’re writing the sql in SQL Server this is what we call T-SQL.

Similarly we create the database in SSMS with GUI. Whenever you reference a table, you can references by 4 parts.

  1. Specify the Server
  2. Specify the Database
  3. Specify the schema
  4. And Table Name

So the easiest way is

SQL Server Part 1

Select the database from Object Explorer and Press New Query. It saves my typing of specifying above credentials. And if we directly go into New Query, the system master database automatically selected.

But we’ll work with Chinook database which is a readymade database to work with it has built-in tables and data inside. I’ve attached the database file just open this file in SSMS and Execute it. All the query statements will successfully execute and your schema will be ready with data.

If you want to move with sqlcmd then you can go ahead and start your learning. Here we’ve some important links for that.

And if you want to know about SQL PowerShell then we’ve another tool sqlps just search in your windows OS, and you’ll find that. And if you want to explore this, then try to explore them your own. Because most of the time developers just prefer to use SSMS. But if you’re really nerdy about learning the new things then I’ve shown you the direction and explore it.

It is not necessary that you should know about the SQL queries how to implement DML, DDL operations and stuff like that but you can do everything with GUI. Most of the time when developer experienced about the things or databases then he tries to perform their tasks with GUI with the help of drag and drop, database diagram, object explorer like tools in SQL Server Management Studio.

SQL Server Part 1

This is how we generate the SQL script to recreate the things with Object Explorer. Here we’ve multiple options to create any kind of query like Create, Drop, Select, Insert etc.

SMO (SQL Server Management Object)

Besides the ADO.NET for making the connection and interacting with SQL server, there is another library available to you called the SQL Server Management Object library. And it is actually the core of SQL Server Management Studio and sqlps (SQL Powershell) command shell and in fact, both of those are really just thin wrappers around SMO but certainly SSMS, the main job it does to present the GUI. All the things we have done by SMO at the backend. SMO is the set of .Net classes which represent the object you can findSQL sql server and has all the intelligence about the hierarchy of the objects.

The script which we generate is SSMS from the tables is actually done by SMO. If you’re writing an application which generates its own tables and things like that SMO might be very handy way to go and do that

Analysis And Reporting

We said Sql server is highly scalable implementation of a relational database. Sometimes when we say something (database) then it might means that Online Transaction Processing System (OLTP) that is each of the operations done to it individually take far less than a millisecond to accomplish. So when we design the schema for online transaction processing system, we’ve to be very careful the operations we do to it can run as fast as possible.

And here we wanna do some kind of analysis, some kind of reporting may be output is just the simple printed report, may be in excel spreadsheet, may be you wanna put it on the web.

So here we’ve 2 things.

  • OLTP (Online Transaction Processing)

Here the data comes of daily basis processing like whose the customer came here, how much time the delivery takes. Everyday tasks are come inside here. Day to day operations comes here. It is an operational processing. It is transaction oriented. The users of OLTP are Clerk, DBA, and Database Professionals. It focuses on to capture the data. It is an ER based – application oriented. The data we use here is current, guaranteed and upto dated. The main purpose of OLTP is to perform a short and simple transaction. Here we’ve access to Read and Write both. The size is from MBs to GBs. It is high performance, high availability

  • OLAP (Online Analytical Processing)

OLAP means that how much sales completed of this day, how much loss we bear today, which product is selling successfully and which product is not selling. These kind of information comes here. It is an informational processing. It is analysis oriented. It is used by business owners, knowledge workers because it helps them to guide how they can increase the business. It focuses on Information out, different ways to give better results. We use star / snowflare here, it is subject oriented. It uses historical data. It performs a complex query. Here we mostly read the data. The size of OLAP is from GBs to TBs. it shows highly flexible of data and here is end user autonomy.

So here we need reporting infrastructure to report the things and SQL Server Reporting Services provide this feature. And obviously the most important point is OLTP and OLAP can’t really be located on the same database server because one would be taking too much power of the other. So what we do to make the OLAP database separately is by copying the data out of an OLTP database and transforming it into the form that OLAP database needs in order to be able to answer questions like in reporting. And for SQL Server that particular product called SQL Server Integration Services.

SSAS

Sql Server Analysis Services is just plain old SQL Server but the way we uses tables in OLTP is different here because it keeps track of facts. These are small pieces of information i.e. time etc. It also works with dimensions. Dimensions in terms of kind of things like time, weeks, hours, years. Product is also another dimension i.e. each of your sku are different units in that particular dimension. And that you might wanna see things spread out across the product that means you’re using the product dimension.

SSAS has something special language called Multi-dimensional Expression language (MDX) which uses to produce something called cubes.

SSIS

In order to use SSIS, you need some way to transform the data you’ve in OLTP database to an OLAP database, SSIS is the product that does that. It is actually a very general purpose product which helps to move and transform the data from anywhere else to anywhere even if there is no databases are involved. Moving data is sometimes is easy like drag and drop but getting the data in the database through the form needs the program (lines of code). So SSIS takes the source of data and transform it in which you want, SSIS is the development framework for creating packages and a SQL Server Service from managing packages. Data movement in SSIS called package.

SSRS

There are two major parts to SQL Server Reporting Services.

  • Development Framework

It is used to develop reports. You might think because its SQL Server reporting services that the source of data of all those reports would be a sql server database, but that’s not the case at all. The data might be coming from the floppy disk, hard drive so the source of the data can be anything

  • Runtime Service

It takes those reports and generate output and output can be all kinds of different things. You might output excel spreadsheet, might be in pdf files, it might be html to display on the web page

Conclusion

We’ve been looking at SQL Server from the point of view of a developer who develop applications which use Sql Server.

And we’ve seen SQL Server consists of collection of services like Database Engine (used for relational database and uses T-SQL internally), SSAS (implements OLAP databases used to do sophisticated analysis of the data working with), SSIS (to move and transform data from OLTP to OLAP) and SSRS (to generate the reports and show to the user).

There are number of applications that developer usually work with like SSMS, sqlcmd (to work on command line and execute the queries on cmd) and we can use the object model that SSMS presents of SQL Server on the command line by using sqlps, Profiler (allows you to trace all the things going on under the database), and tuning advisor (to advice well and fact tuning up your database for better performance).

There are a number of libraries that application use like ADO.NET where we perform connections, commands and get back results and we can used to send sql queries from an application to sql server and get the results back. Linq to Sql where we use C# code to generate the sql queries and we deal with them on compile time and handle all kind of exceptions on compile time. And SMO does all the operations under the hood which we might be thinking that they done by SSMS or sqlcmd or sqlps.