Sql Server Architecture
10:00 PM Posted In SqlServer Basic Edit This 0 Comments »
Microsoft® SQL Server data is stored in databases. The data in a database is organized into the logical components visible to users. A database is also physically implemented as two or more files on disk.
When using a database, you work primarily with the logical components such as tables, views, procedures, and users. The physical implementation of files is largely transparent. Typically, only the database administrator needs to work with the physical implementation.
Each instance of SQL Server has four system databases (master, model, tempdb, and msdb) and one or more user databases. Some organizations have only one user database, containing all the data for their organization. Some organizations have different databases for each group in their organization, and sometimes a database used by a single application. For example, an organization could have one database for sales, one for payroll, one for a document management application, and so on. Sometimes an application uses only one database; other applications may access several databases.
It is not necessary to run multiple copies of the SQL Server database engine to allow multiple users to access the databases on a server. An instance of the SQL Server is capable of handling thousands of users working in multiple databases at the same time. Each instance of SQL Server makes all databases in the instance available to all users that connect to the instance, subject to the defined security permissions.
When connecting to an instance of SQL Server, your connection is associated with a particular database on the server. This database is called the current database. You are usually connected to a database defined as your default database by the system administrator.
SQL Server allows you to detach databases from an instance of SQL Server, then reattach them to another instance, or even attach the database back to the same instance. If you have a SQL Server database file, you can tell SQL Server when you connect to attach that database file with a specific database name.
Physical Database Files and Filegroups
Microsoft® SQL Server maps a database over a set of operating-system files. Data and log information are never mixed on the same file, and individual files are used only by one database.
SQL Server databases have three types of files:
Primary data files
The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary
data files is .mdf.
Secondary data files
Secondary data files comprise all of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files. The recommended file name extension for secondary data files is .ndf.
Log files
Log files hold all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.
SQL Server does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions are recommended to help identify the use of the file.
In SQL Server, the locations of all the files in a database are recorded in both the master database and the primary file for the database. Most of the time the database engine uses the file location information from the master database. For some operations, however, the database engine uses the file location information from the primary file to initialize the file location entries in the master database.
SQL Server files have two names:
logical_file_name is a name used to refer to the file in all Transact-SQL statements.
The logical file name must conform to the rules for SQL Server identifiers and must be unique to the database.
os_file_name is the name of the physical file.
It must follow the rules for Microsoft Windows NT® or Microsoft Windows® Me, and Microsoft Windows 98 file names.
These are examples of the logical file names and physical file names of a database created on a default instance of SQL Server:
SQL Server data and log files can be placed on either FAT or NTFS file systems, but cannot be placed on compressed file systems.
Use the following SQL Statement to list the logical and physical file names:
USE MyDb
SELECT SUBSTRING(name,1,20) Name,
SUBSTRING(filename,1,50) Filename
FROM dbo.sysfiles
Name Filename
-------------------- ------------------------------------
MyDb_System E:\MsSQLServer\Data\MyDb_System.MDF
MyDb_Log_1 E:\MsSQLServer\Data\MyDb_Log_1.LDF
MyDb_Data_1 E:\MsSQLServer\Data\MyDb_Data_1.NDF
MyDb_Index_1 E:\MsSQLServer\Data\MyDb_Index_1.NDF
If you have a Backup and you would know, the logical and physical file names within this Backup, then you can use RESTORE FILELISTONLY
RESTORE FILELISTONLY FROM
DISK = N'E:\MsSQLServer\Backup\MyDb.bak'
WITH FILE = 7
LogicalName PhysicalName
----------------------------------------------------------------
MyDb D:\sql2005\MSSQL.1\MSSQL\Data\MyDb.mdf
MyDb_log C:\DATA\MyDb_log.ldf
Recovery Model
SQL Server offers three recovery models for each database: full recovery, simple recovery and bulk-logged recovery. The recovery models determine how much data loss is acceptable in case of a failure and what types of backup and restore functions are allowed.
Most people either select full or simple for all of their databases and just stick with the same option across the board. In most cases, selecting the full recovery model is the smartest option, because it gives you the greatest flexibility and minimizes data loss in the event a restore has to take place.
Although using the full recovery model makes logical sense, there are reasons why the other two options are available. We will further define why there are three options and when you might want to use the different options to protect your databases. First, let's take a closer look at each model.
Simple
The simple recovery model allows you to recover data only to the most recent full database or differential backup. Transaction log backups are not available because the contents of the transaction log are truncated each time a checkpoint is issued for the database.
Full
The full recovery model uses database backups and transaction log backups to provide complete protection against failure. Along with being able to restore a full or differential backup, you can recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk-loading data, are fully logged and recoverable.
Bulk-Logged
The bulk-logged recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations. Under the bulk-logged recovery model, a damaged data file can result in having to redo work manually based on the operations that are not fully logged. In addition, the bulk-logged recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.
So once again, based on the information above it looks like the Full Recovery model is the way to go. Given the flexibility of the full recovery model, why would you ever select any other model? The following factors will help you determine when another model could work for you:
Select Simple if:
Your data is not critical.
Losing all transactions since the last full or differential backup is not an issue.
Data is derived from other data sources and is easily recreated.
Data is static and does not change often.
Select Bulk-Logged if:
Data is critical, but logging large data loads bogs down the system.
Most bulk operations are done off hours and do not interfere
with normal transaction processing.
You need to be able to recover to a point in time.
Select Full if:
Data is critical and no data can be lost.
You always need the ability to do a point-in-time recovery.
Bulk-logged activities are intermixed with normal transaction processing.
You are using replication and need the ability to resynchronize all
databases involved in replication to a specific point in time.
Switching recovery models
For some databases, you may need to use a combination of these recovery models. Let's say you have a critical system and you cannot afford to lose any data during daily operations; but during off hours there are maintenance tasks and data loads that use way too much transaction log space to log every transaction. In a case like this, you may want to switch recovery models prior to your maintenance tasks. This can be automated using T-SQL in the job that runs your maintenance or data load tasks. After the maintenance task is completed, the recovery model can be switched back again.
Switching between full and bulk-logged models is probably the best scenario for changing recovery models and also the safest and easiest. You can switch from any recovery model to another recovery model, but prior to or after the switch, you may need to issue additional transaction log or full backups to ensure you have a complete backup set.
ALTER DATABASE Northwind SET RECOVERY FULL
GO
Transaction Log Architecture
Every Microsoft® SQL Server™ 2000 database has a transaction log that records all transactions and the database modifications made by each transaction. This record of transactions and their modifications supports three operations:
Recovery of individual transactions
If an application issues a ROLLBACK statement, or if SQL Server detects an error such as the loss of communication with a client, the log records are used to roll back the modifications made by an incomplete transaction.
Recovery of all incomplete transactions when SQL Server is started.
If a server running SQL Server fails, the databases may be left in a state where some modifications were never written from the buffer cache to the data files, and there may be some modifications from incomplete transactions in the data files. When a copy of SQL Server is started, it runs a recovery of each database. Every modification recorded in the log which may not have been written to the data files is rolled forward. Every incomplete transaction found in the transaction log is then rolled back to ensure the integrity of the database is preserved.
Rolling a restored database forward to the point of failure
After the loss of a database, as is possible if a hard drive fails on a server that does not have RAID drives, you can restore the database to the point of failure. You first restore the last full or differential database backup, and then restore the sequence of transaction log backups to the point of failure. As you restore each log backup, SQL Server reapplies all the modifications recorded in the log to roll forward all the transactions. When the last log backup is restored, SQL Server then uses the log information to roll back all transactions that were not complete at that point.
Truncating the Transaction Log
If log records were never deleted from the transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files. At some point in time, old log records no longer necessary for recovering or restoring a database must be deleted to make way for new log records. The process of deleting these log records to reduce the size of the logical log is called truncating the log.
The active portion of the transaction log can never be truncated. The active portion of the log is the part of the log needed to recover the database at any time, so must have the log images needed to roll back all incomplete transactions. It must always be present in the database in case the server fails because it will be required to recover the database when the server is restarted. The record at the start of the active portion of the log is identified by the minimum recovery log sequence number (MinLSN).
The recovery model chosen for a database determines how much of the transaction log in front of the active portion must be retained in the database. Although the log records in front of the MinLSN play no role in recovering active transactions, they are required to roll forward modifications when using log backups to restore a database to the point of failure. If you lose a database for some reason, you can recover the data by restoring the last database backup, and then restoring every log backup since the database backup. This means that the sequence of log backups must contain every log record that was written since the database backup. When you are maintaining a sequence of transaction log backups, no log record can be truncated until after it has been written to a log backup.
The log records before the MinLSN are only needed to maintain a sequence of transaction log backups.
In the simple recovery model, a sequence of transaction logs is not being maintained. All log records before the MinLSN can be truncated at any time, except while a BACKUP statement is being processed. NO_LOG and TRUNCATE_ONLY are the only BACKUP LOG options that are valid for a database that is using the simple recovery model.
In the full and bulk-logged recovery models, a sequence of transaction log backups is being maintained. The part of the logical log before the MinLSN cannot be truncated until those log records have been copied to a log backup.
Log truncation occurs at these points
At the completion of any BACKUP LOG statement.
Every time a checkpoint is processed, provided the database is using the simple recovery model. This includes both explicit checkpoints resulting from a CHECKPOINT statement and implicit checkpoints generated by the system. The exception is that the log is not truncated if the checkpoint occurs when a BACKUP statement is still active
Transaction logs are divided internally into sections called virtual log files. Virtual log files are the unit of truncation. When a transaction log is truncated, all log records before the start of the virtual log file containing the MinLSN are deleted
The size of a transaction log is therefore controlled in one of these ways
When a log backup sequence is being maintained, schedule BACKUP LOG statements to occur at intervals that will keep the transaction log from growing past the desired size.
When a log backup sequence is not maintained, specify the simple recovery model.
This illustration shows a transaction log that has four virtual logs. The log has not been truncated after the database was created. The logical log starts at the beginning of the first virtual log and the part of virtual log 4 beyond the end of the logical file has never been used.
This illustration shows how the log looks after truncation. The rows before the start of the virtual log containing the MinLSN record have been truncated.
Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file.
Shrinking the Transaction Log
The size of the log files are physically reduced when:
A DBCC SHRINKDATABASE statement is executed.
A DBCC SHRINKFILE statement referencing a log file is executed.
An autoshrink operation occurs
Shrinking a log is dependent on first truncating the log. Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.
The unit of size reduction is a virtual log. For example, if you have a 600 MB log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but it cannot be reduced to sizes such as 433 MB or 525 MB.
Virtual logs that hold part of the logical log cannot be freed. If all the virtual logs in a log file hold parts of the logical log, the file cannot be shrink until a truncation marks one or more of the virtual logs at the end of the physical log as inactive.
When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual logs from the end of the file are freed to reduce the log to the size requested by the user. The target_size specified by the user is rounded to the next highest virtual log boundary. For example, if a user specifies a target_size of 325 MB for our sample 600 MB file with 100 MB virtual log files, the last two virtual log files are removed and the new file size is 400 MB.
In SQL Server, a DBCC SHRINKDATABASE or DBCC SHRINKFILE operation attempts to shrink the physical log file to the requested size (subject to rounding) immediately:
If no part of the logical log is in the virtual logs beyond the target_size mark, the virtual logs after the target_size mark are freed and the successful DBCC statement completes with no messages.
If part of the logical log is in the virtual logs beyond the target_size mark, SQL Server frees as much space as possible and issues an informational message. The message tells you what actions you need to perform to get the logical log out of the virtual logs at the end of the file. After you perform this action, you can then reissue the DBCC statement to free the remaining space.
For example, assume that a 600 MB log file with six virtual logs has a logical log starting in virtual log 3 and ending in virtual log 4, when you execute a DBCC SHRINKFILE statement with a target_size of 275 MB:
Virtual logs 5 and 6 are freed immediately because they hold no portion of the logical log. To meet the specified target_size, however, virtual log 4 should also be freed, but cannot because it holds the end portion of the logical log. After freeing virtual logs 5 and 6, SQL Server fills the remaining part of virtual log 4 with dummy records. This forces the end of the log file to virtual log 1. In most systems, all transactions starting in virtual log 4 will be committed within seconds, meaning that all of the active portion of the log moves to virtual log 1, and the log file now looks like this:
The DBCC SHRINKFILE statement also issues an informational message that it could not free all the space requested, and indicate that you can execute a BACKUP LOG statement to make it possible to free the remaining space. Once the active portion of the log moves to virtual log 1, a BACKUP LOG statement will truncate the entire logical log that is in virtual log 4:
Because virtual log 4 no longer holds any portion of the logical log, if you now execute the same DBCC SHRINKFILE statement with a target_size of 275 MB, virtual log 4 will be freed and the size of the physical log file reduced to the size requested.
Example Shrinking the Transaction Log
Here is an example how boths steps can be performed:
Database is in FULL Recovery Mode
# For this example we switch to FULL Mode
USE master
ALTER DATABASE MyDb SET RECOVERY FULL;
GO
The command(s) completed successfully.
# Add logical Devices for the Backup (The directories must exist!)
EXEC sp_addumpdevice 'disk', 'MyDb_dat',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyDb_dat.dat'
GO
(1 row(s) affected)
'Disk' device added.
EXEC sp_addumpdevice 'disk', 'MyDb_log',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyDb_log.dat'
GO
(1 row(s) affected)
'Disk' device added.
# Create a Backup before Truncating / Shrinking
BACKUP DATABASE MyDb TO MyDb_dat
GO
Processed 26392 pages for database 'MyDb', file 'MigrationBasisplus_Data' on file 9.
Processed 1 pages for database 'MyDb', file 'MigrationBasisplus_Log' on file 9.
BACKUP DATABASE successfully processed 26393 pages in 9.756 seconds (22.161 MB/sec).
BACKUP LOG MyDb TO MyDb_log
GO
Processed 1 pages for database 'MyDb', file 'MigrationBasisplus_Log' on file 5.
BACKUP LOG successfully processed 1 pages in 0.065 seconds (0.039 MB/sec).
# Truncate the Transaction Log
BACKUP LOG MyDb WITH TRUNCATE_ONLY
GO
The command(s) completed successfully.
# Drop logical Devices
sp_dropdevice 'MyDb_dat'
GO
Device dropped.
sp_dropdevice 'MyDb_log'
GO
Device dropped.
# Get the Name of the Transaction Log
USE MyDb
SELECT name FROM dbo.sysfiles
GO
# Shrink the physical Size of the Transaction Log to 20MB
USE MyDb
DBCC SHRINKFILE (MigrationBasisplus_Log, 20)
GO
# Avoid a transaction log grows unexpectedly
USE [master]
GO
ALTER DATABASE [MyDb] MODIFY FILE
(NAME = N'MyDb_Log_1', SIZE = 772096KB,
MAXSIZE = 921600KB , FILEGROWTH = 10240KB)
GO
Database is in SIMPLE Recovery Mode
# For this example we switch to SIMPLE Mode
USE master
ALTER DATABASE MyDb SET RECOVERY SIMPLE;
GO
The command(s) completed successfully.
# Add logical Device for the Backup (The directories must exist!)
EXEC sp_addumpdevice 'disk', 'MyDb_dat',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyDb_dat.dat'
GO
(1 row(s) affected)
'Disk' device added.
# Create a Backup before Truncating / Shrinking
BACKUP DATABASE MyDb TO MyDb_dat
GO
Processed 26392 pages for database 'MyDb', file 'MigrationBasisplus_Data' on file 9.
Processed 1 pages for database 'MyDb', file 'MigrationBasisplus_Log' on file 9.
BACKUP DATABASE successfully processed 26393 pages in 9.756 seconds (22.161 MB/sec).
# Truncate the Transaction Log
BACKUP LOG MyDb WITH TRUNCATE_ONLY
GO
The command(s) completed successfully.
# Drop logical Device
sp_dropdevice 'MyDb_dat'
GO
Device dropped.
# Get the Name of the Transaction Log
USE MyDb
SELECT name FROM dbo.sysfiles
GO
The command(s) completed successfully.
# Shrink the physical Size of the Transaction Log to 20MB
USE MyDb
DBCC SHRINKFILE (MigrationBasisplus_Log, 20)
GO
SQL Server Overview
System and User Databases (= Oracle Schema)
Master (Controls other Databases)
Model (Template for new Databases)
Tempdb (Temporary Storage)
Msdb (Scheduling and Job Information)
Distribution (Replication Information)
SQL Server Services
SQL Server includes four services
MSSQLServer (Database Engine)
SQLServerAgent (Job Scheduling)
MS DTC, Distributed Transaction Coordinater (Distributed Queries, 2P Commit)
Microsoft Search (Full Text Engine)
Referring Objects
select * from...object
select * from Northwind..customer (Owner is missing)
Metadata (Data Dictionary)
System Stored Procedures ( sp_ )
sp_helpdb [db_name]
Infos for Database
sp_help [any object]
Infos an Tables, Procedures, etc
sp_helpindex [table_name]
Show Indexes for table_name
sp_who
Show System Activity
SELECT @@spid
Which is my Server Process ID ?
select user_name(),db_name(), @@servername
Database User Name, Database, Server ?
sp_helpdb Northwind
sp_help Employees
System Tables ( sys... )
master..syslogins
Available login Accounts
master..sysmessages
Available System Error / Warnings
master..sysdatabases
Available Databases on SQL Server
sysusers
Available Win 2000 Users, SQL Server Users
sysobjects
Available Objects in the Database
use master
select * from sysdatabases
use northwind
select * from sysobjects
where xtype = 'U'
System Functions ( see QA: Common Objects )
DB_ID(DbName)
Get Database ID
USER_NAME (id)
Get UserName
GETDATE()
Get SystemDate
use master
select * from sysdatabases
use northwind
select * from sysobjects
where xtype = 'U'
Schema Views ( System Table Independent Views)
select * from information_schema.tables
Tables in a Database
select * from information_schema.columns
Columns in a Database
select * from information_schema.table_privileges
Privileges on Tables
SQL Server Logon and Database Access
Login Authentication (Windows Authentication or Mixed Mode)
Mapping of OS User to Database User Accounts and Roles
All W2K Administrators are automatically allowed to logon. This can be disabled by deleting the \BUILTIN\Administrators in the Security Tab on SQL Server Level.
Windows Authentication is the Default (Trusted Connection)
Database Users
Specific to SQL-Server, not the same as the Windows User or Login Account !
Normally dbo is used, mapping is done on Database Level (EM: Users)
Roles
Fixed Server Roles (e.g. System Administrators = DBA) on SQL-Server Level
Fixed Database Role (e.g. db_owner = Has all permissions in the database)
Fixed server role Description
sysadmin Can perform any activity in SQL Server.
serveradmin Can set serverwide configuration options, shut down the server.
setupadmin Can manage linked servers and startup procedures.
securityadmin Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords.
processadmin Can manage processes running in SQL Server.
dbcreator Can create, alter, and drop databases.
diskadmin Can manage disk files.
bulkadmin Can execute BULK INSERT statements.
You can get a list of the fixed server roles from sp_helpsrvrole, and get the specific permissions for each role from sp_srvrolepermission.
Fixed database role Description
db_owner Has all permissions in the database.
db_accessadmin Can add or remove user IDs.
db_securityadmin Can manage all permissions, object ownerships, roles and role memberships.
db_ddladmin Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements.
db_backupoperator Can issue DBCC, CHECKPOINT, and BACKUP statements.
db_datareader Can select all data from any user table in the database.
db_datawriter Can modify any data in any user table in the database.
db_denydatareader Cannot select any data from any user table in the database.
db_denydatawriter Cannot modify any data in any user table in the database.
Example
USE Northwind
GO
sp_addlogin @loginame = 'Akadia', @passwd = 'Akadia', @defdb = 'Northwind'
GO
sp_grantdbaccess 'Akadia'
GO
sp_addrole 'Masters'
GO
sp_addrolemember 'Masters', 'Akadia'
GO
GRANT SELECT ON Employees TO Masters
GO
SQL Server Query Designer
Query Designer can be used to graphicaly build a SQL statement, for example the syntax for an ANSI OUTER Join Syntax. Follow the following steps:
Open Enterprise Manager
Select a table in the desired Database / Tables
Right-Click an select "Open Table / Query", the Query Designer opens.
Right-Click an empty area on the diagram oane, and then click "Add Table"
Choose another table, in the SQL Pane you can now see the generated SQL statement
For an OUTER Join, right-click the Relation and choose "All rows from"
When using a database, you work primarily with the logical components such as tables, views, procedures, and users. The physical implementation of files is largely transparent. Typically, only the database administrator needs to work with the physical implementation.
Each instance of SQL Server has four system databases (master, model, tempdb, and msdb) and one or more user databases. Some organizations have only one user database, containing all the data for their organization. Some organizations have different databases for each group in their organization, and sometimes a database used by a single application. For example, an organization could have one database for sales, one for payroll, one for a document management application, and so on. Sometimes an application uses only one database; other applications may access several databases.
It is not necessary to run multiple copies of the SQL Server database engine to allow multiple users to access the databases on a server. An instance of the SQL Server is capable of handling thousands of users working in multiple databases at the same time. Each instance of SQL Server makes all databases in the instance available to all users that connect to the instance, subject to the defined security permissions.
When connecting to an instance of SQL Server, your connection is associated with a particular database on the server. This database is called the current database. You are usually connected to a database defined as your default database by the system administrator.
SQL Server allows you to detach databases from an instance of SQL Server, then reattach them to another instance, or even attach the database back to the same instance. If you have a SQL Server database file, you can tell SQL Server when you connect to attach that database file with a specific database name.
Physical Database Files and Filegroups
Microsoft® SQL Server maps a database over a set of operating-system files. Data and log information are never mixed on the same file, and individual files are used only by one database.
SQL Server databases have three types of files:
Primary data files
The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary
data files is .mdf.
Secondary data files
Secondary data files comprise all of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files. The recommended file name extension for secondary data files is .ndf.
Log files
Log files hold all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.
SQL Server does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions are recommended to help identify the use of the file.
In SQL Server, the locations of all the files in a database are recorded in both the master database and the primary file for the database. Most of the time the database engine uses the file location information from the master database. For some operations, however, the database engine uses the file location information from the primary file to initialize the file location entries in the master database.
SQL Server files have two names:
logical_file_name is a name used to refer to the file in all Transact-SQL statements.
The logical file name must conform to the rules for SQL Server identifiers and must be unique to the database.
os_file_name is the name of the physical file.
It must follow the rules for Microsoft Windows NT® or Microsoft Windows® Me, and Microsoft Windows 98 file names.
These are examples of the logical file names and physical file names of a database created on a default instance of SQL Server:
SQL Server data and log files can be placed on either FAT or NTFS file systems, but cannot be placed on compressed file systems.
Use the following SQL Statement to list the logical and physical file names:
USE MyDb
SELECT SUBSTRING(name,1,20) Name,
SUBSTRING(filename,1,50) Filename
FROM dbo.sysfiles
Name Filename
-------------------- ------------------------------------
MyDb_System E:\MsSQLServer\Data\MyDb_System.MDF
MyDb_Log_1 E:\MsSQLServer\Data\MyDb_Log_1.LDF
MyDb_Data_1 E:\MsSQLServer\Data\MyDb_Data_1.NDF
MyDb_Index_1 E:\MsSQLServer\Data\MyDb_Index_1.NDF
If you have a Backup and you would know, the logical and physical file names within this Backup, then you can use RESTORE FILELISTONLY
RESTORE FILELISTONLY FROM
DISK = N'E:\MsSQLServer\Backup\MyDb.bak'
WITH FILE = 7
LogicalName PhysicalName
----------------------------------------------------------------
MyDb D:\sql2005\MSSQL.1\MSSQL\Data\MyDb.mdf
MyDb_log C:\DATA\MyDb_log.ldf
Recovery Model
SQL Server offers three recovery models for each database: full recovery, simple recovery and bulk-logged recovery. The recovery models determine how much data loss is acceptable in case of a failure and what types of backup and restore functions are allowed.
Most people either select full or simple for all of their databases and just stick with the same option across the board. In most cases, selecting the full recovery model is the smartest option, because it gives you the greatest flexibility and minimizes data loss in the event a restore has to take place.
Although using the full recovery model makes logical sense, there are reasons why the other two options are available. We will further define why there are three options and when you might want to use the different options to protect your databases. First, let's take a closer look at each model.
Simple
The simple recovery model allows you to recover data only to the most recent full database or differential backup. Transaction log backups are not available because the contents of the transaction log are truncated each time a checkpoint is issued for the database.
Full
The full recovery model uses database backups and transaction log backups to provide complete protection against failure. Along with being able to restore a full or differential backup, you can recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk-loading data, are fully logged and recoverable.
Bulk-Logged
The bulk-logged recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations. Under the bulk-logged recovery model, a damaged data file can result in having to redo work manually based on the operations that are not fully logged. In addition, the bulk-logged recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.
So once again, based on the information above it looks like the Full Recovery model is the way to go. Given the flexibility of the full recovery model, why would you ever select any other model? The following factors will help you determine when another model could work for you:
Select Simple if:
Your data is not critical.
Losing all transactions since the last full or differential backup is not an issue.
Data is derived from other data sources and is easily recreated.
Data is static and does not change often.
Select Bulk-Logged if:
Data is critical, but logging large data loads bogs down the system.
Most bulk operations are done off hours and do not interfere
with normal transaction processing.
You need to be able to recover to a point in time.
Select Full if:
Data is critical and no data can be lost.
You always need the ability to do a point-in-time recovery.
Bulk-logged activities are intermixed with normal transaction processing.
You are using replication and need the ability to resynchronize all
databases involved in replication to a specific point in time.
Switching recovery models
For some databases, you may need to use a combination of these recovery models. Let's say you have a critical system and you cannot afford to lose any data during daily operations; but during off hours there are maintenance tasks and data loads that use way too much transaction log space to log every transaction. In a case like this, you may want to switch recovery models prior to your maintenance tasks. This can be automated using T-SQL in the job that runs your maintenance or data load tasks. After the maintenance task is completed, the recovery model can be switched back again.
Switching between full and bulk-logged models is probably the best scenario for changing recovery models and also the safest and easiest. You can switch from any recovery model to another recovery model, but prior to or after the switch, you may need to issue additional transaction log or full backups to ensure you have a complete backup set.
ALTER DATABASE Northwind SET RECOVERY FULL
GO
Transaction Log Architecture
Every Microsoft® SQL Server™ 2000 database has a transaction log that records all transactions and the database modifications made by each transaction. This record of transactions and their modifications supports three operations:
Recovery of individual transactions
If an application issues a ROLLBACK statement, or if SQL Server detects an error such as the loss of communication with a client, the log records are used to roll back the modifications made by an incomplete transaction.
Recovery of all incomplete transactions when SQL Server is started.
If a server running SQL Server fails, the databases may be left in a state where some modifications were never written from the buffer cache to the data files, and there may be some modifications from incomplete transactions in the data files. When a copy of SQL Server is started, it runs a recovery of each database. Every modification recorded in the log which may not have been written to the data files is rolled forward. Every incomplete transaction found in the transaction log is then rolled back to ensure the integrity of the database is preserved.
Rolling a restored database forward to the point of failure
After the loss of a database, as is possible if a hard drive fails on a server that does not have RAID drives, you can restore the database to the point of failure. You first restore the last full or differential database backup, and then restore the sequence of transaction log backups to the point of failure. As you restore each log backup, SQL Server reapplies all the modifications recorded in the log to roll forward all the transactions. When the last log backup is restored, SQL Server then uses the log information to roll back all transactions that were not complete at that point.
Truncating the Transaction Log
If log records were never deleted from the transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files. At some point in time, old log records no longer necessary for recovering or restoring a database must be deleted to make way for new log records. The process of deleting these log records to reduce the size of the logical log is called truncating the log.
The active portion of the transaction log can never be truncated. The active portion of the log is the part of the log needed to recover the database at any time, so must have the log images needed to roll back all incomplete transactions. It must always be present in the database in case the server fails because it will be required to recover the database when the server is restarted. The record at the start of the active portion of the log is identified by the minimum recovery log sequence number (MinLSN).
The recovery model chosen for a database determines how much of the transaction log in front of the active portion must be retained in the database. Although the log records in front of the MinLSN play no role in recovering active transactions, they are required to roll forward modifications when using log backups to restore a database to the point of failure. If you lose a database for some reason, you can recover the data by restoring the last database backup, and then restoring every log backup since the database backup. This means that the sequence of log backups must contain every log record that was written since the database backup. When you are maintaining a sequence of transaction log backups, no log record can be truncated until after it has been written to a log backup.
The log records before the MinLSN are only needed to maintain a sequence of transaction log backups.
In the simple recovery model, a sequence of transaction logs is not being maintained. All log records before the MinLSN can be truncated at any time, except while a BACKUP statement is being processed. NO_LOG and TRUNCATE_ONLY are the only BACKUP LOG options that are valid for a database that is using the simple recovery model.
In the full and bulk-logged recovery models, a sequence of transaction log backups is being maintained. The part of the logical log before the MinLSN cannot be truncated until those log records have been copied to a log backup.
Log truncation occurs at these points
At the completion of any BACKUP LOG statement.
Every time a checkpoint is processed, provided the database is using the simple recovery model. This includes both explicit checkpoints resulting from a CHECKPOINT statement and implicit checkpoints generated by the system. The exception is that the log is not truncated if the checkpoint occurs when a BACKUP statement is still active
Transaction logs are divided internally into sections called virtual log files. Virtual log files are the unit of truncation. When a transaction log is truncated, all log records before the start of the virtual log file containing the MinLSN are deleted
The size of a transaction log is therefore controlled in one of these ways
When a log backup sequence is being maintained, schedule BACKUP LOG statements to occur at intervals that will keep the transaction log from growing past the desired size.
When a log backup sequence is not maintained, specify the simple recovery model.
This illustration shows a transaction log that has four virtual logs. The log has not been truncated after the database was created. The logical log starts at the beginning of the first virtual log and the part of virtual log 4 beyond the end of the logical file has never been used.
This illustration shows how the log looks after truncation. The rows before the start of the virtual log containing the MinLSN record have been truncated.
Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file.
Shrinking the Transaction Log
The size of the log files are physically reduced when:
A DBCC SHRINKDATABASE statement is executed.
A DBCC SHRINKFILE statement referencing a log file is executed.
An autoshrink operation occurs
Shrinking a log is dependent on first truncating the log. Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.
The unit of size reduction is a virtual log. For example, if you have a 600 MB log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but it cannot be reduced to sizes such as 433 MB or 525 MB.
Virtual logs that hold part of the logical log cannot be freed. If all the virtual logs in a log file hold parts of the logical log, the file cannot be shrink until a truncation marks one or more of the virtual logs at the end of the physical log as inactive.
When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual logs from the end of the file are freed to reduce the log to the size requested by the user. The target_size specified by the user is rounded to the next highest virtual log boundary. For example, if a user specifies a target_size of 325 MB for our sample 600 MB file with 100 MB virtual log files, the last two virtual log files are removed and the new file size is 400 MB.
In SQL Server, a DBCC SHRINKDATABASE or DBCC SHRINKFILE operation attempts to shrink the physical log file to the requested size (subject to rounding) immediately:
If no part of the logical log is in the virtual logs beyond the target_size mark, the virtual logs after the target_size mark are freed and the successful DBCC statement completes with no messages.
If part of the logical log is in the virtual logs beyond the target_size mark, SQL Server frees as much space as possible and issues an informational message. The message tells you what actions you need to perform to get the logical log out of the virtual logs at the end of the file. After you perform this action, you can then reissue the DBCC statement to free the remaining space.
For example, assume that a 600 MB log file with six virtual logs has a logical log starting in virtual log 3 and ending in virtual log 4, when you execute a DBCC SHRINKFILE statement with a target_size of 275 MB:
Virtual logs 5 and 6 are freed immediately because they hold no portion of the logical log. To meet the specified target_size, however, virtual log 4 should also be freed, but cannot because it holds the end portion of the logical log. After freeing virtual logs 5 and 6, SQL Server fills the remaining part of virtual log 4 with dummy records. This forces the end of the log file to virtual log 1. In most systems, all transactions starting in virtual log 4 will be committed within seconds, meaning that all of the active portion of the log moves to virtual log 1, and the log file now looks like this:
The DBCC SHRINKFILE statement also issues an informational message that it could not free all the space requested, and indicate that you can execute a BACKUP LOG statement to make it possible to free the remaining space. Once the active portion of the log moves to virtual log 1, a BACKUP LOG statement will truncate the entire logical log that is in virtual log 4:
Because virtual log 4 no longer holds any portion of the logical log, if you now execute the same DBCC SHRINKFILE statement with a target_size of 275 MB, virtual log 4 will be freed and the size of the physical log file reduced to the size requested.
Example Shrinking the Transaction Log
Here is an example how boths steps can be performed:
Database is in FULL Recovery Mode
# For this example we switch to FULL Mode
USE master
ALTER DATABASE MyDb SET RECOVERY FULL;
GO
The command(s) completed successfully.
# Add logical Devices for the Backup (The directories must exist!)
EXEC sp_addumpdevice 'disk', 'MyDb_dat',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyDb_dat.dat'
GO
(1 row(s) affected)
'Disk' device added.
EXEC sp_addumpdevice 'disk', 'MyDb_log',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyDb_log.dat'
GO
(1 row(s) affected)
'Disk' device added.
# Create a Backup before Truncating / Shrinking
BACKUP DATABASE MyDb TO MyDb_dat
GO
Processed 26392 pages for database 'MyDb', file 'MigrationBasisplus_Data' on file 9.
Processed 1 pages for database 'MyDb', file 'MigrationBasisplus_Log' on file 9.
BACKUP DATABASE successfully processed 26393 pages in 9.756 seconds (22.161 MB/sec).
BACKUP LOG MyDb TO MyDb_log
GO
Processed 1 pages for database 'MyDb', file 'MigrationBasisplus_Log' on file 5.
BACKUP LOG successfully processed 1 pages in 0.065 seconds (0.039 MB/sec).
# Truncate the Transaction Log
BACKUP LOG MyDb WITH TRUNCATE_ONLY
GO
The command(s) completed successfully.
# Drop logical Devices
sp_dropdevice 'MyDb_dat'
GO
Device dropped.
sp_dropdevice 'MyDb_log'
GO
Device dropped.
# Get the Name of the Transaction Log
USE MyDb
SELECT name FROM dbo.sysfiles
GO
# Shrink the physical Size of the Transaction Log to 20MB
USE MyDb
DBCC SHRINKFILE (MigrationBasisplus_Log, 20)
GO
# Avoid a transaction log grows unexpectedly
USE [master]
GO
ALTER DATABASE [MyDb] MODIFY FILE
(NAME = N'MyDb_Log_1', SIZE = 772096KB,
MAXSIZE = 921600KB , FILEGROWTH = 10240KB)
GO
Database is in SIMPLE Recovery Mode
# For this example we switch to SIMPLE Mode
USE master
ALTER DATABASE MyDb SET RECOVERY SIMPLE;
GO
The command(s) completed successfully.
# Add logical Device for the Backup (The directories must exist!)
EXEC sp_addumpdevice 'disk', 'MyDb_dat',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyDb_dat.dat'
GO
(1 row(s) affected)
'Disk' device added.
# Create a Backup before Truncating / Shrinking
BACKUP DATABASE MyDb TO MyDb_dat
GO
Processed 26392 pages for database 'MyDb', file 'MigrationBasisplus_Data' on file 9.
Processed 1 pages for database 'MyDb', file 'MigrationBasisplus_Log' on file 9.
BACKUP DATABASE successfully processed 26393 pages in 9.756 seconds (22.161 MB/sec).
# Truncate the Transaction Log
BACKUP LOG MyDb WITH TRUNCATE_ONLY
GO
The command(s) completed successfully.
# Drop logical Device
sp_dropdevice 'MyDb_dat'
GO
Device dropped.
# Get the Name of the Transaction Log
USE MyDb
SELECT name FROM dbo.sysfiles
GO
The command(s) completed successfully.
# Shrink the physical Size of the Transaction Log to 20MB
USE MyDb
DBCC SHRINKFILE (MigrationBasisplus_Log, 20)
GO
SQL Server Overview
System and User Databases (= Oracle Schema)
Master (Controls other Databases)
Model (Template for new Databases)
Tempdb (Temporary Storage)
Msdb (Scheduling and Job Information)
Distribution (Replication Information)
SQL Server Services
SQL Server includes four services
MSSQLServer (Database Engine)
SQLServerAgent (Job Scheduling)
MS DTC, Distributed Transaction Coordinater (Distributed Queries, 2P Commit)
Microsoft Search (Full Text Engine)
Referring Objects
select * from
select * from Northwind..customer (Owner is missing)
Metadata (Data Dictionary)
System Stored Procedures ( sp_ )
sp_helpdb [db_name]
Infos for Database
sp_help [any object]
Infos an Tables, Procedures, etc
sp_helpindex [table_name]
Show Indexes for table_name
sp_who
Show System Activity
SELECT @@spid
Which is my Server Process ID ?
select user_name(),db_name(), @@servername
Database User Name, Database, Server ?
sp_helpdb Northwind
sp_help Employees
System Tables ( sys... )
master..syslogins
Available login Accounts
master..sysmessages
Available System Error / Warnings
master..sysdatabases
Available Databases on SQL Server
sysusers
Available Win 2000 Users, SQL Server Users
sysobjects
Available Objects in the Database
use master
select * from sysdatabases
use northwind
select * from sysobjects
where xtype = 'U'
System Functions ( see QA: Common Objects )
DB_ID(DbName)
Get Database ID
USER_NAME (id)
Get UserName
GETDATE()
Get SystemDate
use master
select * from sysdatabases
use northwind
select * from sysobjects
where xtype = 'U'
Schema Views ( System Table Independent Views)
select * from information_schema.tables
Tables in a Database
select * from information_schema.columns
Columns in a Database
select * from information_schema.table_privileges
Privileges on Tables
SQL Server Logon and Database Access
Login Authentication (Windows Authentication or Mixed Mode)
Mapping of OS User to Database User Accounts and Roles
All W2K Administrators are automatically allowed to logon. This can be disabled by deleting the \BUILTIN\Administrators in the Security Tab on SQL Server Level.
Windows Authentication is the Default (Trusted Connection)
Database Users
Specific to SQL-Server, not the same as the Windows User or Login Account !
Normally dbo is used, mapping is done on Database Level (EM: Users)
Roles
Fixed Server Roles (e.g. System Administrators = DBA) on SQL-Server Level
Fixed Database Role (e.g. db_owner = Has all permissions in the database)
Fixed server role Description
sysadmin Can perform any activity in SQL Server.
serveradmin Can set serverwide configuration options, shut down the server.
setupadmin Can manage linked servers and startup procedures.
securityadmin Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords.
processadmin Can manage processes running in SQL Server.
dbcreator Can create, alter, and drop databases.
diskadmin Can manage disk files.
bulkadmin Can execute BULK INSERT statements.
You can get a list of the fixed server roles from sp_helpsrvrole, and get the specific permissions for each role from sp_srvrolepermission.
Fixed database role Description
db_owner Has all permissions in the database.
db_accessadmin Can add or remove user IDs.
db_securityadmin Can manage all permissions, object ownerships, roles and role memberships.
db_ddladmin Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements.
db_backupoperator Can issue DBCC, CHECKPOINT, and BACKUP statements.
db_datareader Can select all data from any user table in the database.
db_datawriter Can modify any data in any user table in the database.
db_denydatareader Cannot select any data from any user table in the database.
db_denydatawriter Cannot modify any data in any user table in the database.
Example
USE Northwind
GO
sp_addlogin @loginame = 'Akadia', @passwd = 'Akadia', @defdb = 'Northwind'
GO
sp_grantdbaccess 'Akadia'
GO
sp_addrole 'Masters'
GO
sp_addrolemember 'Masters', 'Akadia'
GO
GRANT SELECT ON Employees TO Masters
GO
SQL Server Query Designer
Query Designer can be used to graphicaly build a SQL statement, for example the syntax for an ANSI OUTER Join Syntax. Follow the following steps:
Open Enterprise Manager
Select a table in the desired Database / Tables
Right-Click an select "Open Table / Query", the Query Designer opens.
Right-Click an empty area on the diagram oane, and then click "Add Table"
Choose another table, in the SQL Pane you can now see the generated SQL statement
For an OUTER Join, right-click the Relation and choose "All rows from
0 comments:
Post a Comment