This section will introduce you to the SQL Server architecture, the entities involved—SQL Server instances, databases, schemas, and database objects.
SQL Server Instances
A SQL Server instance is an installation of a SQL Server database engine/service. You can install multiple instances of SQL Server on the same computer. Each instance is completely independent of the others in terms of security and the data that it manages. At the logical level, two different instances residing on the same computer have no more in common than two instances residing on two separate computers. But they do share the server’s physical resources such as CPU, memory, and disk.
One of the instances on the computer can be set up as the default instance, while all others must be named instances. You determine whether an instance is the default or a named one upon installation; you cannot change this later.
For a client application to connect to a default instance, it needs to specify the computer’s name or IP address. To connect to a named instance, the client needs to specify the computer’s name or IP address, followed by a backslash (\), followed by the instance name (as provided upon installation). For example, say you have two instances of SQL Server installed on a computer called Server1. One of the instances was installed as the default instance, and the other was installed as a named instance called Inst1. To connect to the default instance you need to specify Server1 as the server name; to connect to the named instance you need to specify Server1\Inst1.
Databases
You can think of a database as a container of objects such as tables, views, stored procedures, and so on. Each instance of SQL Server can contain multiple databases. When you install SQL Server, the setup program creates several system databases that hold system data and serve internal purposes. After installation you can create your own user databases that will hold application data.
The system databases that the setup program creates include master, Resource, model, tempdb, and msdb. A description of each follows.
master The master database holds instance-wide metadata information, server configuration, information about all databases in the instance, and initialization information.
Resource The Resource holds all system objects. When you query metadata information in a database, this information appears to be local to the database but in practice it resides in the Resource database.
model The model database is used as a template for new databases. Every new database that you create is initially created as a copy of model. So if you want certain objects (such as data types) to appear in all new databases that you create, or certain database properties to be configured in a certain way in all new databases, you need to create those objects and configure those properties in the model database. Note that changes you apply to the model database will not impact existing databases—only new databases that you create in the future.
tempdb The tempdb database is where SQL Server stores temporary data such as work tables, sort space, row versioning information, and so on. Note that this database is destroyed and recreated as a copy of the model every time you restart the instance of SQL Server. For this reason, when I need to create objects for test purposes and I don’t want the objects to remain in the database, I usually create them in tempdb.
msdb The msdb database is where a service called SQL Server Agent stores its data. SQL Server Agent is in charge of automation, which includes entities such as jobs, schedules, and alerts. The SQL Server Agent is also the service in charge of replication. The msdb database also holds information related to other SQL Server features such as Database Mail and Service Broker.
You can create as many user databases as you might need within an instance. A user database will hold objects and data for an application.
You can define a property called collation at the database level that will determine language support, case sensitivity, and sort order for character data in that database.
In terms of security, to be able to connect to a SQL Server instance, the DBA must create a login for you. The login can be tied to your Windows credentials, in which case it is called a Windows authenticated login. With a Windows authenticated login, you won’t need to provide login and password information when connecting to SQL Server because you already provided those when you logged on to Windows. The login can also be independent of your Windows credentials, in which case it is called a SQL Server authenticated login. When connecting to SQL Server using a SQL Server authenticated login, you will need to provide both a login name and a password.
The DBA needs to map your login to a database user in each database that you are supposed to have access to. The database user is the entity that will be granted permissions to objects in the database.
The database is made of data and transaction log files. When you create a database, you can define various properties for each file, including the file name, location, initial size, maximum size, and an autogrowth increment. Each database must have at least one data file and at least one log file (the default in SQL Server). The data files hold object data, and the log files hold information that SQL Server needs to maintain transactions.
Although SQL Server can write to multiple data files in parallel, it can only write to one log file at a time, in a sequential manner. Therefore, unlike with data files, having multiple log files does not result in performance benefit. You might need to add log files if the disk drive where the log resides runs out of space.
Data files are organized in logical groups called filegroups. A filegroup is the target for creating an object, such as a table or an index. The object data will be spread across the files that belong to the target filegroup. Filegroups are your way to control what will be the physical locations of your objects. A database must have at least one filegroup called PRIMARY, and optionally other user filegroups as well.
The PRIMARY filegroup contains the primary data file (extension .mdf), and the database’s system catalog. You can optionally add secondary data files (extension .ndf) to PRIMARY. User filegroups contain only secondary data files. You can determine which of the filegroups is marked as the default filegroup. An object is created on the default filegroup when the object creation statement does not explicitly specify the target filegroup.
Schemas and Objects
A database contains schemas, and schemas contain objects. You can think of a schema as a container of objects such as tables, views, stored procedures, and others.
You can control permissions at the schema level. For example, you can grant a user SELECT permissions on a schema, allowing the user to query data from all objects in the schema. So security is one of the considerations for determining how to arrange objects in schemas.
The schema is also used as a prefix to the object name. For example, say you have a table called Orders in a schema called Sales. The schema-qualified object name (also known as the two-part object name) is Sales.Orders. If you omit the schema name when referring to an object, SQL Server will apply a process to resolve the schema name such as checking whether the object exists in the user’s default schema, and if it doesn’t, checking whether it exists in the dbo schema. It is recommended that when you refer to objects in your code you always use the two-part object names. Some insignificant extra costs are involved in resolving the object name when it’s not explicitly specified. As insignificant as this extra cost might be, why pay it? Also, if multiple objects with the same name exist in different schemas, you might end up getting a different object than the one you wanted.
Reference : Book – Microsoft_SQL_Server_2008_T_SQL_Fundamentals by Itzik Ben-Gan.
I Hope, you will like this post. Please share your thoughts on this topic or write comments in the comment section.
SQL Destination 