Walking with SQL Server

In SQL Server, there are three types of backups: Full Backup: Full backup means it will take the backup of the total .mdf file. Differential Backup : Differential backup means it will take the backup of the extent's (8 pages= 1 extent) data which has been changed since last full backup. It is cumulative in nature and so is also called cumulative backup. cumulative means it will take backup from last full backup. T-log Backup: T-log backup means it will backup the transaction log file. It is not cumulative in nature. Now, there are three Recovery models in SQL server which are defined as: Simple Recovery Model: In this recovery model, we can only have full backup and differential backup. If the data needs to be restored for the database having simple recovery model then last full backup will be restored first and after that the last differential backup. There will be no point in time recovery in this case. Full Recovery Model: In this recovery model, we...

When crash happens, the recovery of uncommitted and committed transaction is done by using transaction log. Transaction Log Architecture There are basically two types of transaction log architectures: Physical : In physical architecture virtual log files are maintained for different transactions. Virtual log files are maintained for easy management. Logical : In logical architecture LSN is maintained. Recovery always starts from last uncommitted transaction or checkpoint whichever is less. Last uncommitted transaction is known as MIN LSN Checkpoint Checkpoint occurs in two conditions: 1. Automatic : It will occur automatically in either of the two following cases(whichever is lower) Recovery Interval 70% log is full 2. When an activity happens in database : It will occur if any of the following case happens When we write explicitly checkpoint in query window When new db is created When backup is created When SQL server shuts down The recovery is not ...

There are basically three set of statements: DDL: Data Definition Language - Create and Alter statements fall in this category DML: Data Manipulation Language - Insert, Update and Delete statements fall in this category DCL : Data Control Library - Commit and Rollback statements fall in this category DDL and DCL are permanent in nature and DML statements are actually treated as transactions. Transactions should follow atomicity i.e. All or none property i.e. if execution happens it will happen for all the statements in a transactions or else it would not happen for anyone. In SQL server every command or statement(DML) is treated as one transaction. So there are basically two types of transactions: Implicit: Where we do not need to add any keyword from our side to begin and end a transaction. SQL server itself adds it at the beginning and completion of each statement. Explicit:    Where we need to add "Begin Tran" at the beginning and 'com...

Till 2005 version of SQL, NTAUTHORITY\ADMINISTRATORS was there. It was there, because this group knew who are the administrators on the system. We didn't need to create a group or user here. Whoever would be the administrator of windows will be able to enter SQL Server without any login creation. But customers did not wanted it, so it was removed in 2008 version. So, now if customer wants to not have this feature in 2005, we have to delete NTAUTHORITY\ADMINISTRATORS and then create login for whatever user they want explicitly. Protocols of SQL Server(For Network) TCP\IP(Transmission Control Protocol/Internet Protocol) NP(Named Pipes) LPC(Local Process Communication) or Shared Memory VIA(Virtual Interface Adapter)-depleted or very rarely used Shared Memory or LPC When client is present on the same machine where SQL Server is installed. It means client and server both are on the same box. Named Pipes It is used only in case of intranet. TC...

To understand the security feature of SQL Server you need to understand the levels of security. So what happens at windows level, you need to user and password. As soon as you are inside windows you need to enter inside instance and for that you need a login. After entering into an instance you need to have users for each database. There will be mapping of users and login. So the main points are:  To enter into an instance we need a login.  To enter into database we need a user.  Users and logins must be mapped. When a machine is on a network, it can be:  Workgroup or   Domain Workgroup is a standalone machine which may or may not be connected to a network and all the machines will be independent of each other while domain is a centralised machine where Active Domain(AD) is installed therefore it is called a Domain Controller(DC). So it is not needed in this case to go on each machine separately. But while installing windows...

When we say databases, the first thing that comes into mind is data. It means it is something that deals with the data. So, basically what is a database? Database is a collection of tables, stored procedures, functions, triggers, indexes and jobs. So this definition uses a lot of new words that you may not be knowing. Nothing to worry about. We will come to know about all these terms in my next coming posts. To work on databases, we need an environment that could be either SQL Server environment, oracle, DB2 or any other environment. We are going to focus on SQL Server 2008 R2. The installation of the environment can be found here.  When we install the environment, some folders get generated automatically. The folders are listed here:  80 folder - For SQL Server 2000  90 folder - For SQL Server 2005  100 folder - For SQL Server 2008  MSAS_10_50 - Analysis Services  MSRS_10_50 - Reporting Services  MSSQL_10_50.MSSQLSERVER - ...

T o learn SQL Server the first most important thing is to learn about Windows Operating System. Here is a basic overview of what you should know about windows to learn SQL Server. Client and server: First of all we need to understand what are Clients and Servers. As the name suggests, clients are meant for requesting services and servers for providing services. It is not necessary to have separate hardware for residing client and server, it can be on the same hardware. When they are on different hardware they communicate with the help of network which can be either cable or wireless media. Components of Computer: Among the different components of a computer there are four components which you need to know before learning SQL Server as these are components that play a great role in the working of SQL Server. These components are: CPU RAM Hard disk Network CPU:  In CPU we basically mean processor whose speed is measured in GHz(Giga Hertz). If we talk about pr...