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 - For a particular instance
These folders can be found in microsoft sql server folder in program files( for 64 bit) or program files(x86)(for 32 bit)
Analysis services basically allows you to do analysis of data. Analysis can be data warehousing. If we talk about Reporting Services, as the name suggests they are for generating reports.
Instances are basically a combination of databases, memory structures and background processes. Instances are basically used to do manipulations inside the database.
Memory Structures & Background Processes
As we all know that SQL Queries run in RAM while DB Files get stored in harddisk.
In RAM area gets allocated for different instances separately. These separate allocation of areas for each instance are called memory structures.
When data is read or written to harddisk. The processes that do the read and write are called Background Processes.
If we talk about SQL Server there can be any number of databases in a single instance while in case of oracle server, only one database can be there in one SQL Server Instance.
Now the question arises that if we are having the choice of having a lot of databases in single instance then why do we create different instances?
The answer to this question is that based on the different requirements of the customer we create different instances. So that if one instance goes down, the other databases which are in other instances will not be affected. Other instances will run properly.
But there is a disadvantage of having more than one instance i.e. there will be more memory requirement. But this disadvantage we can bear but we can't bear one instance going down. So we use more than one instance.
Types of databases
When SQL Server is installed, some databases also get installed automatically. These databases are known as system databases.
Generally there are 2 types of databases:
- User databases - These are created by users explicitly
- System databases - These are created automatically
There are 5 types of system databases. These are :
- Master - It stores all the configuration details and information about all the other system databases. We can read or write in this database. It has its own .mdf and .ldf files. These files will be explained shortly. If this database gets corrupted, SQL Server instance won't come up and services will not be able to install.
- MSDB - It stores all the job related information.Jobs are basically the schedules designed by us to do daily activities. Job history can also be seen here. We can also check whether a job is running properly or not. This database is also having its own .mdf and .ldf files and also we can read and write in this database.
- TempDB - All the temporary operations run here. If we want to create temporary objects explicitly, we need to insert a # before the object name. Ex- create table #tablename. This will create a temporary table. For each instance there is a separate tempDB. It also has its own .mdf and .ldf files and we can read from or write to this database.
- Model - It has all the default configurations and its like a template. It carries its own .mdf and .ldf files and we can read from or write to this database.
- Resource - It is called a pseudo database because we cant see it in management studio. It stores tge version information. It is having its own .ldf and .mdf files. This is a read only database. We cant write into it.
What are .mdf and .ldf files?
Both of them are physical files.
.mdf file is a master data file which is also called primary data file. It consists of data related to database. Its can be found in data folder of microsoft sql server folder. If you need to see the extensions as well if they are not shown, just go to data folder press alt+t -> click in tools -> folder options -> view -> uncheck hide extensions -> ok.
.ldf is a physical files which stores all the transactions that have happened in the particular database since the start of SQL Server database. With every start of SQL Server, new transactions will be generated which is called transaction log.
There is one more physical file called .ndf which is created explicitly if we need more space. It us called secondary data file.
Types of Instances
There are 2 types of instances:
- Default - We do not need to give name for this instance. It automatically takes name of the machine as name. It runs on 1433 port no. which is constant. So it does not needa SQL Browser Service that assigns port to an instance.
- Named - Named instance is named by us explicitly during the installation of SQL Server. Ex- default name\ name given by us. This is the format of named instance. It can run on either static or dynamic port.
How to check no. of instances installed?
Go to services.msc -> name -> Check for no. of SQL Server (MSSQLSERVER) services. If it has some name in the bracket it us a named instance. You can count and know the no. of instances installed on the machine. You can also check the same by configuration manager. We can start or stop SQL Server service from configuration manager.
There is an executable called sqlservr.exe which is found in binn folder of microsoft SQL Server folder. It is an executable that runs as a service.
Note : Never start or stop SQL Server service from services.msc. Start or stop the service using configuration manager or sql command line.
You can see one more service in services.msc or configuration manager, SQL Server Agent which fetches information of jobs from msdb database. It is used to run the jobs. If SQL Server Service is not up, SQL Server Agent cannot work. Its file is SQLAGENT.OUT which is located in MSSQL LOG.This is generally the log which stores all the errors related to agent service. Old files will be named as SQLAGENT.1 ,.2,.3 etc.
Likewise if SQL Server is not working or there is an error, you can check it out in errorlog generated in data folder.
Configuration manager can be used to start or stop any service and lot of other tasks which we will be discussing later. In configuration manager go to SQL Server(MSSQLSERVER) -> Right click -> properties-> advanced tab-> startup parameters -> It shows the errorlog file path. At a time 6 errorlogs are maintained. Errorlog is the latest file among them. Rest .1,.2,.3 etc are old files.
Some common SQL Queries
1. Creating database
Create database db_name
2. Changing context
Use db_name
3. Creating table
Create table table_name(column_name1 datatype of column, column_name2 datatype of the column
4. Inserting into table
Insert into table_name values (value1, value2)
5. Showing table
Select * from table_name
6. Updating table
Update table_name set column_name= new_value where distinct_column=distinct_value
7. Deleting a row
Delete from table_name where distinct_ column= distinct_value
Starting/Stoping SQL Server service using command line
Go to command prompt(run as admin) and navigate to binn folder and press enter then run the command sqlservr.exe -SMSSQLSERVER and press enter.
You can find the binn folder by going to services-> right click on SQL Server service-> properties-> copy the path to executables.
There are various ways to start SQL Server service using command line. They are:
1. To start default instance
Sqlservr.exe
2. To start named instance
Sqlservr.exe -S<instance name>
3. For single user mode in case of default instance
Sqlservr.exe -m
4. For single user mode in case of named instance
Sqlservr.exe -m -s < instance name>
5. To start with minimal configuration in default instance
Sqlservr.exe -f
6. To start with minimal configuration in named instance
Sqlservr.exe -f -s<instance name>
Comments
Post a Comment