Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Identity and Access Control: Authentication and Users


  • Access to a SQL Server database takes place through three levels of security.
  • In each one you need to have access and permissions configured independently.
  • You may need to:
    • (1) Login in to a Windows domain (level 1) (except for non-windows clients)
    • (2) The Windows login is then mapped to SQL Server Login (level 2)
    • (3) The SQL Server Login is mapped to a database user (level 3).

Authentication Mode
  • Controls how application users connect to SQL Server
  • (a) Windows Authentication mode
    • Default and recommended
    • Only authenticated Windows users can access the SQL Server instance
    • Allowed connections:
      • by users who are authenticated through the Windows Active Directory service or
      • by the local user account databse on the SQL Server machine.
    • A Windows Login(within SQL Server) has to be created for each Windows user or group that needs acess to the SQL instance.

  • (b) Mixed Mode Authentication
    • Windows users can be mapped to SQL Server Windows logins or
    • SQL Logins can be created directly in SQL Server.
    • This mode is required when non-Windows clients need to access the server.
    • SQL Logins are not considered as secure as Windows Logins.
    • While Windows uses Kerberos authentication, SQL Logins store username/passwd in the master database.
    • If the use of SQL Logins is a requirement, you should also use some form of lower-layer network encryption (i.e. IPSec or SSL).

Principals
  • Principals: Entities (individuals, groups, processes) that can request SQL Server resources.
  • Securables: the server, databases, and objects within databases.
  • Principals can be arranged in a hierarchy
  • Scope of influence of a principal depends on:
    • (a) its definition scope: Windows -> Server -> database
    • (b) whether principal is indivisible (i.e. windows login, SQL Logins) or collection (i.e windows group)
  • Every principal has a security indentifier (SID)



Windows-level principals
  • Windows group
  • Windows domain login
  • Windows local login

SQL Server-level principals
  • SQL Server Login
    • The sa login is created when instance is installed.
    • sa default database: master.
    • Server-level principals for internal use: names enclosed with '##' (certificate-based)
  • Fixed Server Role:
    • sysadmin, serveradmin, securityadmin, processadmin,
    • setupadmin, bulkadmin, diskadmin, dbcreator, public

Database-level principals
  • Database User
    • Every database includes information_schema and sys.
    • These entities appear as users, BUT information_schema and sys ARE NOT principals. Cannot be dropped.
    • The guest user is created by default in every user database.
    • The guest user cannot be dropped, but can be disabled (revoke CONNECT), except in master and tempdb.
  • Fixed | Flexible Database Role
  • (i.e. public. every database user belongs to the public db role.)
  • Application Role

Types of SQL Server Logins:
(neither of which are mapped to an OS user)
  • Windows Logins
  • SQL Server Logins

Creating a SQL Server Login
(a) Create a SQL Server login that uses Windows Authentication
-- domain/user have to be a valid/existing Windows user
Create Login [domain/user] from windows;


-- domain/group has to be a valid Windows group
create login [domain_name/group_name] from windows;

(b) create a SQL Server Login that uses SQL Server authentication
CREATE LOGIN [test_SQLAuth_1] WITH PASSWORD=***, 
     DEFAULT_DATABASE=[Books], 
     DEFAULT_LANGUAGE=[us_english], 
     CHECK_EXPIRATION=ON, 
     CHECK_POLICY=ON
GO

ALTER LOGIN [test_SQLAuth_1] DISABLE
GO

Drop login test_SQLAuth_1;

(a) use sys.server_principals

select name, type_desc, default_database_name
from sys.server_principals;
name type_desc default_database_name
----------------------------------------------
sa SQL_LOGIN master
public SERVER_ROLE NULL
...
NT AUTHORITY\SYSTEM WINDOWS_LOGIN master
NT SERVICE\MSSQL$SQLSERVER08 WINDOWS_GROUP master
iranmr-PC\iranmr WINDOWS_LOGIN master
NT SERVICE\SQLAgent$SQLSERVER08 WINDOWS_GROUP master
..
test_SQLAuth_1 SQL_LOGIN Books

(b) use sys.sql_logins
select name, type_desc, default_database_name
from sys.sql_logins;
name type_desc default_database_name
-----
sa SQL_LOGIN master
...
test_SQLAuth_1 SQL_LOGIN Books

Server-Level Roles (Fixed Server Roles)
  • SQL Server-level Logins can be managed using fixed server roles.
  • There are 9 Server-level roles.
    • sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator, public
  • Roles are similar to Windows OS groups
  • Server-level roles: also called fixed server roles (because cannot be created by users)
  • Have server-wide scope.
  • You can assign SQL Server logins, Windows accounts, and Windows groups to server-level roles.
Information about Server-Level Roles:
exec sp_helpsrvrole;
ServerRole Description
-----
sysadmin System Administrators
securityadmin Security Administrators
serveradmin Server Administrators
setupadmin Setup Administrators
processadmin Process Administrators
diskadmin Disk Administrators
dbcreator Database Creators
bulkadmin Bulk Insert Administrators

exec sp_helpsrvrolemember;
ServerRole MemberName MemberSID
-----
sysadmin sa
sysadmin NT AUTHORITY\SYSTEM 
sysadmin NT SERVICE\MSSQL$SQLSERVER08
sysadmin iranmr-PC\iranmr
sysadmin NT SERVICE\SQLAgent$SQLSERVER08


Adding/Dropping a Server Login to a Server-level Role
exec sp_addsrvrolemember test_SQLAuth_1, sysadmin;

exec sp_helpsrvrolemember;
ServerRole MemberName MemberSID
-----
sysadmin sa
sysadmin NT AUTHORITY\SYSTEM 
sysadmin NT SERVICE\MSSQL$SQLSERVER08
sysadmin iranmr-PC\iranmr
sysadmin NT SERVICE\SQLAgent$SQLSERVER08
sysadmin        test_SQLAuth_1

exec sp_dropsrvrolemember test_SQLAuth_1, sysadmin;


Database users
Database users are principals at the database level.
A database user needs to be created for each login that needs to access the database.
When a login that does not have a user mapped to it tries to access a database, it is logged as the guest database user.
However, this only happens if the guest user has been granted CONNECT to the database.
(a) Create database used mapped to an existing SQL Server Login
USE [Books]
GO

CREATE USER [test_auth_dbbooks1] FOR LOGIN [test_SQLAuth_1] 
       WITH DEFAULT_SCHEMA=[dbo]
GO

(b) Check the created database user among the database principals
use Books; go select name, type_desc, default_schema_name from sys.database_principals; name type_desc default_schema_name ------- public DATABASE_ROLE NULL dbo WINDOWS_USER dbo guest SQL_USER guest INFORMATION_SCHEMA SQL_USER NULL sys SQL_USER NULL test_auth_dbbooks1 SQL_USER dbo ...

Fixed | Flexible Database Roles
  • Roles are security principals that group other principals.
  • Can be used to group database users with similar access privileges.
  • You can create new (flexible) database roles to define groups based on business requisites.
  • You can nest roles (add role into other role)
  • Create new role: CREATE ROLE role_name
  • Add user to role: exec sp_addrolemember role_name, user_name.
  • Fixed roles exist in all databases:
    • db_onwer, db_securityadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, db_denydatareader


Application Role
  • Is a database principal that enables an application to run with its own, user-like permissions.
  • Can be used to enable access to specific data only to users connecting through a specific application.
  • Application roles contain no members.
  • How it works:
    • (1) Client application connects to SQL Server as a database user.
    • (2) Client application executes sp_setapprole.
    • (3) If validated, a context switch occurs and the application assumes the permission of the application role.
CREATE APPLICATION ROLE weekly_receipts 
    WITH PASSWORD = '987G^bv876sPY)Y5m23', 
    DEFAULT_SCHEMA = Sales;
GO


Managing Schemas
  • Schemas are collections of database objects that form a single namespace.
  • Starting in SQL Server 2005, schemas and database users are separate entities.
  • User name is no longer part of the object name.
  • Each schema is now a distinct namespace that exists independently of the database user who created it.
  • Each schema is owned either by a user or a role.
  • If you need to drop a user, you need to transfer the schema ownership to someone else first.
  • You can define a default schema for a user.
  • Implications of the separation of ownership:
    • Ownership of schemas is now transferable
    • Objects can be moved between schemas.
    • Multiple database users can share a single default schema.
    • Improved object-permission management.
    • Schema can be owned by any database principal: user, database role, application role.

Checking schemas
use Books;
go
select * from sys.schemas;

name                schema_id   principal_id
------------------  ----------  ------------
dbo                 1          1
guest 2 2
INFORMATION_SCHEMA 3 3
sys 4 4
db_owner 16384 16384
db_accessadmin 16385 16385
db_securityadmin 16386 16386
db_ddladmin 16387 16387
db_backupoperator 16389 16389
db_datareader 16390 16390
db_datawriter 16391 16391
db_denydatareader 16392 16392
db_denydatawriter 16393 16393


Using SQL Server and Database Principals, Schemas, application roles and context switching
(a) Create a SQL Server login, database user and schema.
(b) Create application role associated with the new schema
(c) Activate the new application role, switching user context
(d) Return to previous context.
-- Using database books
USE books;
GO
(1) Create a SQL Server login user1
CREATE login user1 WITH password = ***;
(2) Create a [books] database user user1. -- user1 is created in the [books] database, -- is associated with user1 SQL Server principal (login) -- has schema1 as default schema.
CREATE USER  user1 FOR login user1 WITH default_schema = schema1;
(3) Query sys.sql_logins to check that the SQL Server principal was created
SELECT name, type_desc, default_database_name
    FROM sys.sql_logins;
name type_desc default_database_name -------------- --------- --------------- sa SQL_LOGIN master ... test_SQLAuth_1 SQL_LOGIN Books user1 SQL_LOGIN master (4) Query books.sys.database_principals to check that databse user user1 was created
SELECT name, principal_id, type_desc, default_schema_name
    FROM sys.database_principals;
name principal_id type_desc default_schema_name ------------------- ------------- ------------- --------------- public 0 DATABASE_ROLE NULL dbo 1 SQL_USER dbo guest 2 SQL_USER guest INFORMATION_SCHEMA 3 SQL_USER NULL sys 4 SQL_USER NULL user1 6 SQL_USER schema1 db_owner 16834 DATABASE_ROLE NULL ... (5) Query books.sys.schemas to check that the schema1 DOES NOT yet exist.
SELECT name, schema_id, principal_id
    FROM sys.schemas;
name schema_id principal_id ----------------- --------- -------- dbo 1 1 guest 2 2 INFORMATION_SCHEMA 3 3 sys 4 4 db_owner 16384 16384 ...

(6) Create schema books.schema1, owned by user1
USE Books;
GO
CREATE SCHEMA schema1 AUTHORIZATION user1;
(7) Query sys.schemas to check schema1 was created.
SELECT name, schema_id, principal_id
    FROM sys.schemas;
name schema_id principal_id ----------------- --------- -------- dbo 1 1 guest 2 2 INFORMATION_SCHEMA 3 3 sys 4 4 schema1 5 6 -- <= owner: user1 db_owner 16384 16384 ... (8) Create table emp_sch1 in schema1, and insert one row.
CREATE TABLE schema1.emp_sch1 (id INT, name VARCHAR(20));
INSERT INTO books.schema1.emp_sch1 
        VALUES (1, 'John');
(9) Query books.schema1.emp_sch1 to check the inserted row.
SELECT * 
    FROM books.schema1.emp_sch1;
id name -- ---- 1 John

(10) Create application role approle_sch1, with default schema schema1. Grant select on schema1 to approle_sch1.
USE Books;
GO
CREATE application role approle_sch1
            WITH password = 'p4sswd',
            default_schema = schema1;
            GRANT 
SELECT ON schema::schema1 TO approle_sch1;
(11) Query the current user (should return 'dbo')
SELECT USER_NAME() AS USER_NAME;
user_name --------- dbo (12) Switch context to approle_sch1 using sp_setapprole. Use cookies to save orginal context information.
DECLARE @cookie VARBINARY(8000);
EXEC sp_setapprole 'approle_sch1', 'p4sswd',
    @fCreateCookie = true,
    @cookie = @cookie OUTPUT;
SELECT USER_NAME() AS USER_NAME;
    -- should return approle_sch1
SELECT * 
    FROM emp_sch1;
    -- should return one row
EXEC sp_unsetapprole @cookie;
GO
user_name ---------- approle_sch1 id name -- ---- 1 John (13) Revert to the original context using sp_unsetapprole.
SELECT USER_NAME() AS USER_NAME;
user_name ---------- dbo



SQL Server: the data dictionary


Distribution of catalog information:
  • What is stored in the master database? What is kept in the user databases?
  • Base metadata tables are stored in the resource database. Metadata views are available in the master database (system-wide information) and in each user database (db specific and some system-wide information).
  • Metadata information can be obtained through catalog views, information schema views, system stored procedures and functions, as well as OLE DB schema rowsets and ODBC catalog funtions.

Obtaining metadata information can be done by using either:
  • (a) System Stored Procedures (T-SQL)
    • Catalog stored procedures
  • (b) System views:
    • Object Catalog views
    • Dynamic Management Views and Functions
    • Information Schema Views

Catalog Stored Procedures and Object Catalog views

sp_columns, sys.columns, and information_schema.columns
sp_columns
USE AdventureWorks2008r2;
GO
EXEC sp_columns department;
Returns 19 columns.. TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME ... TYPE_NAME PRECISION LENGTH -------------------- -------------- --------- ------------ ----------------- ---------- ------ AdventureWorks2008R2 HumanResources Department DepartmentID smallint identity 5 2 AdventureWorks2008R2 HumanResources Department Name Name 50 100 AdventureWorks2008R2 HumanResources Department GroupName Name 50 100 AdventureWorks2008R2 HumanResources Department ModifiedDate datetime 23 16 (or)
USE AdventureWorks2008r2;
GO
EXEC sp_columns department, @column_name= 'name';
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME ... TYPE_NAME PRECISION LENGTH -------------------- -------------- --------- ------------ ----------------- ---------- ------ AdventureWorks2008R2 HumanResources Department Name Name 50 100 (for column information, you may also use:)
EXEC sp_help 'HumanResources.department';

sys.columns
Returns a row for each column of an object that has columns (system, internal and user tables, views, table-valued sql function, inline table-valued sql function, table-valued assembly functions).
SELECT name, system_type_id, is_nullable
    FROM sys.columns
    WHERE OBJECT_NAME(OBJECT_ID)= 'Department';
name system_type_id is_nullable ------------ -------------- ------------ DepartmentID 52 0 Name 231 0 GroupName 231 0 ModifiedDate 61 0
SELECT o.name table_name, c.name column_name, t.name data_type,
            t.length length, t.prec precision
    FROM syscolumns c
            INNER JOIN sysobjects o ON o.id = c.id
            LEFT JOIN  systypes t   ON t.xtype = c.TYPE
    WHERE o.TYPE = 'U'
        AND o.name = 'Department'
    ORDER BY o.name, c.name;
table_name column_name data_type length precision ---------- ----------- --------- ------ -------- Department DepartmentID smallint 2 5 Department GroupName NULL NULL NULL Department ModifiedDate datetime 8 23 Department Name NULL NULL NULL

information_schema.columns
Information schema views provide a system table-independent view of the SQL Server metadata.
The idea is that it provides an interface that remains unchanged even after significant changes have been made to the underlying system tables.
USE AdventureWorks2008R2;
GO
SELECT TABLE_NAME, COLUMN_NAME,
            COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Department';
TABLE_NAME COLUMN_NAME COLUMN_ID ---------- ----------- -------- Department DepartmentID 1 Department Name 2 Department GroupName 3 Department ModifiedDate 4

sp_column_privileges and information_schema.column_privileges
sp_column_privileges
USE AdventureWorks2008R2;
GO
EXEC sp_column_privileges 'Employee',
    @table_owner='HumanResources',
    @column_name = 'SalariedFlag';
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE -------------------- -------------- ---------- ------------ ------- ------- --------- --------- AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo INSERT YES AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo REFERENCES YES AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo SELECT YES AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo UPDATE YES
information_schema.column_privileges

SELECT table_name, column_name,column_name, privilege_type, is_grantable
    FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
    WHERE TABLE_NAME = 'Department';

sp_special_columns
sp_special_columns
Returns the optimal set of columns that uniquely identify a row in the table.
Returns columns automatically updated when any value in the row is updated.
USE AdventureWorks2008R2;
GO
EXEC sp_special_columns 'Department',
    @table_owner='HumanResources';
SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE PSEUDO_COLUMN ----- -------------- --------- ----------------- --------- ------ ----- ------------ 1 DepartmentID 5 smallint identity 5 2 0 1

sp_stored_procedures, sp_sproc_columns, sys.procedures and information_schema.routines
sp_stored_procedures
USE AdventureWorks2008R2;
GO
EXEC sp_stored_procedures;
PROCEDURE_QUALIFIER PROCEDURE_OWNER PROCEDURE_NAME ... ------------------- -------------- ----------------- AdventureWorks2008R2 dbo ufnGetAccountingEndDate;0 ... AdventureWorks2008R2 HumanResources uspUpdateEmployeeHireInfo;1 .. AdventureWorks2008R2 sys sp_spaceused;1 (also)
USE AdventureWorks2008R2;
GO
EXECUTE sp_stored_procedures @sp_owner = N'dbo';

sp_sproc_columns
Returns column information for a stored procedure or user-defined function.
USE AdventureWorks2008R2;
GO
EXEC sp_sproc_columns @procedure_name = 'uspLogError';
PROCEDURE_QUALIFIER PROCEDURE_OWNER PROCEDURE_NAME COLUMN_NAME ... TYPE_NAME ... IS_NULLABLE ------------------- --------------- -------------- ----------- --------- ----------- AdventureWorks2008R2 dbo uspLogError;1 @RETURN_VALUE int NO AdventureWorks2008R2 dbo uspLogError;1 @ErrorLogID int YES

sys.procedures
Returning all stored procedures in a given database;
USE AdventureWorks2008R2;
GO
SELECT name procedure_name,
            SCHEMA_NAME(schema_id) AS schema_name,
            type_desc,
            create_date,
            modify_date
    FROM sys.procedures;

information_schema.routines
Returns the stored procedures and functions that can be accessed by the current user in the current database.
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_BODY, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.routines;

sp_databases, sys.databases
sp_databases
Lists databases in an instance.
USE MASTER;
GO
EXEC sp_databases;
DATABASE_NAME DATABASE_SIZE REMARKS -------------------- ------------ -------- AdventureWorks2008R2 186240 NULL master 5120 NULL model 1792 NULL msdb 20288 NULL MyDB 10240 NULL tempdb 8704 NULL
sys.databases
USE MASTER;
GO
SELECT name, create_date, compatibility_level, user_access_desc,
            state_desc, recovery_model_desc
    FROM sys.databases;
name create_date compat_level user_access_desc state_desc rec_model_desc ------------------- ----------------------- ------------ ---------------- ---------- -------------- master 2003-04-08 09:13:36.390 100 MULTI_USER ONLINE SIMPLE ... model 2003-04-08 09:13:36.390 100 MULTI_USER ONLINE FULL TESTSQL 2009-06-08 10:43:13.347 100 MULTI_USER ONLINE BULK_LOGGED AdventureWorks2008R2 2009-06-05 17:40:24.250 100 MULTI_USER ONLINE SIMPLE
Other database information views: sys.database_files, sys.database_mirroring, sys.database_recovery_status, sys.master_files


sp_statistics

sp_fkeys


sp_pkeys

sp_table_privileges

sp_server_info

sp_tables








SQL Server: Database Snapshots


  • Available only in the Enterprise version.
  • Snapshots provide read-only, static views of a database.
  • Each snapshot capture the state of all committed data at the point of snapshot creation.
  • Snapshots must be on the same server instance as the source database.
  • Users can query the database snapshot

Why use database snapshots
  • "Freeze data at a point-in-time:" Can be used for reporting data in a particular point in time. (i.e. financial quarter)
  • If snapshot available for query in a mirrored server, it deflects read load from the production server.
  • Protect agains administrative errors:
  • Can be created just before a major change to the database. (i.e. bulk update, schema chenage). Reverting here is much faster than restoring a backup.
  • Protect against user errors:
  • In case of user errors, database can be restored to the time of snapshot creation.
  • Manage a test database: guaratee identical data at each round of testing.

Snapshots use:
  • sparce files
  • copy-on-write operations

How snapshots work
  • When you create a snapshot, no data is necessarily copied from the source database.
  • The snapshot may be thought as being the source database itself.
  • Copy from source to the snapshot occurs only as pages are modified in the source database.
  • The snapshot uses one or more sparse files.
  • Only as DMLs are issued, the original pages are copied to the snapshot before being modified, in a copy-on-write operation.
  • Subsequent updates are not copied to the snapshot.
  • Snapshots contain a catalog of changed pages: a bitmap with the list of pages in the source database that have changed since the snapshot creation.
  • When a page is copied from the source to the snapshot sparse file, its corresponding bit in the catalog of changed pages is updated from o to 1.
  • Growth rate:
    • In this way, the size of the snapshot is proportional to the intensity of database writes on different pages.
    • If most of the updates are repeated changes to a small subset of pages, the growth rate of the snapshot sparse files will be slow.
    • On the other hand, if ALL original pages receive at least one update, the snapshot will grow to the size of the source database.
  • Read operations in a snapshot always access the original data pages, either in the source database (those that have not been modified since the snapshot was created) or in the sparse files with original page data (for the pages modified after the snapshot creation).
Creating a snapshot
  • Use the CREATE DATABASE ... AS SNAPSHOT OF statement. It has to include:
  • One entry for each filename in the source database
  • The logical filename has to be equal to the logical filename in the source database
  • The physical filename indicates where the sparse file will be created.
CREATE DATABASE AdventureWorks2008R2_dbss ON
    ( name = adventureworks2008r2_Data,
            filename = 'c:\mssqldrive\e\AdventureWorks2008R2_Data.ss' )
            AS snapshot OF adventureworks2008r2;
GO

Checking snapshot files and file size:
  • During database snapshot creation, sparse files are created by using the file names in the CREATE DATABASE statement.
  • These file names are stored in sys.master_files in the physical_name column.
  • The size-on-disk of the snapshot files is listed in the size column (in 8-kb page units).
SELECT name, physical_name, type_desc, state_desc, size
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'AdventureWorks2008R2_dbss';
name physical_name type_desc state_desc size ------------------------- ------------------------------------------- --------- ---------- ------- adventureworks2008r2_Data e:\mssqldrive\e\AdventureWorks2008R2_Data.ss ROWS ONLINE 23024 FileStreamDocuments2008R2 c:\Program...\DATA\Documents2008R2 FILESTREAM ONLINE 0

  • You can also use the fn_virtualfilestats system table-valued function.
  • fn_virtualfilestats takes database_id and file_id as parameters.
SELECT database_id, FILE_ID, physical_name
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'AdventureWorks2008R2_dbss';
database_id file_id physical_name ----------- ------- ------------ 11 1 e:\mssqldrive\e\AdventureWorks2008R2_Data.ss 11 65537 c:\Program...\DATA\Documents2008R2
SELECT FROM fn_virtualfilestats(11,1);
GO
DbId FileId TimeStamp NumberReads BytesRead ... NumberWrites BytesWritten ... BytesOnDisk ...
---- ------ --------- ----------- ---------     ------------ ------------     -----------
11   1      23108022  195         1597440       163          1466368          3407872

Some restrictions:
  • You cannot backup, restore or detach a database snapshot
  • It must be in the same SQL Server instance as the source database
  • Full-text indexes are not supported
  • You cannot drop, detach or restore a source database if it has a snapshot.
  • You cannot perform structural changes (i.e. add/remove filegroup) to the source database

Dropping a snapshot
DROP DATABASE AdventureWorks2008R2_dbss;

Reverting a database from a Snapshot
Restrictions:
  • Only a single snapshot can exist against a source database before it can be reverted
  • Reverting a database rebuilds the transaction log, so no recovery can be performed aftewards
  • Source database and snapshot database must be offline
USE MASTER;
GO
RESTORE DATABASE AdventureWorks2008R2 
 FROM DATABASE_SNAPSHOT = 'AdventureWorks2008R2_dbss';
GO

Managing Snapshot
(a) Check the MyDB database files
(b) Verify rows on emp table
(c) Create snapshot of MyDB database
(d) Check created snapshot files
(e) Check the size on disk of snapshot files
(f) Insert data on emp table in the source database.
(g) Query snapshot. Emp table is empty.
(a) Check the MyDB database files
sp_helpdb myDb; 

name          fileid filename                           filegroup size    usage
------------  ------ ------------------------------------ --------- -----   --------
MyDB_primary  1      c:\Program...\data\MyDB_Prm.mdf      PRIMARY   4096 KB data only
MyDB_archlog1 2      c:\Program...\data\MyDB_archlog1.ldf NULL      1024 KB log only
MyDB_FG1_Dat1 3      c:\Program...\data\MyDB_FG1_1.ndf    MyDB_fg1  1024 KB data only
MyDB_FG1_Dat2 4      c:\Program...\data\MyDB_FG1_2.ndf    MyDB_fg1  1024 KB data only
MyDB_FG1_Dat3 5      c:\Program...\data\MyDB_FG1_3.ndf    MyDB_fg1  1024 KB data only
MyDB_FG1_Dat4 6      c:\Program...\data\MyDB_FG1_4.ndf    MyDB_fg1  1024 KB data only
MyDB_FG2_Dat1 7      c:\Program...\data\MyDB_FG2_1.ndf    MyDB_fg2  1024 KB data only

(b) Verify rows on emp table
USE MyDB;
GO
SELECT * 
    FROM emp;
empid name ----- ---- (c) Create snapshot of MyDB database
CREATE DATABASE MyDB_dbss ON
    ( name = MyDB_primary,
                filename = 'c:\mssqldrive\e\MyDB_Prm.ss' ),
    ( name = MyDB_FG1_Dat1,
                filename = 'c:\mssqldrive\e\MyDB_FG1_1.ss' ),
    ( name = MyDB_FG1_Dat2,
                filename = 'c:\mssqldrive\e\MyDB_FG1_2.ss' ),
    ( name = MyDB_FG1_Dat3,
                filename = 'c:\mssqldrive\e\MyDB_FG1_3.ss' ),
    ( name = MyDB_FG1_Dat4,
                filename = 'c:\mssqldrive\e\MyDB_FG1_4.ss' ),
    ( name = MyDB_FG2_Dat1,
                filename = 'c:\mssqldrive\e\MyDB_FG2_1.ss' )
            AS snapshot OF MyDB;
GO
(d) Check created snapshot files
SELECT name, physical_name, type_desc, state_desc, size
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'MyDB_dbss';
name physical_name type_desc state_desc size ------------- ----------------------------- --------- --------- ---- MyDB_primary c:\mssqldrive\e\MyDB_Prm.ss ROWS ONLINE 512 MyDB_FG1_Dat1 c:\mssqldrive\e\MyDB_FG1_1.ss ROWS ONLINE 128 MyDB_FG1_Dat2 c:\mssqldrive\e\MyDB_FG1_2.ss ROWS ONLINE 128 MyDB_FG1_Dat3 c:\mssqldrive\e\MyDB_FG1_3.ss ROWS ONLINE 128 MyDB_FG1_Dat4 c:\mssqldrive\e\MyDB_FG1_4.ss ROWS ONLINE 128 MyDB_FG2_Dat1 c:\mssqldrive\e\MyDB_FG2_1.ss ROWS ONLINE 128 (e) Check the size on disk of snapshot files
SELECT database_id, FILE_ID, physical_name
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'MyDB_dbss';
database_id file_id physical_name ----------- ------- ------------- 12 1 c:\mssqldrive\e\MyDB_Prm.ss 12 3 c:\mssqldrive\e\MyDB_FG1_1.ss 12 4 c:\mssqldrive\e\MyDB_FG1_2.ss 12 5 c:\mssqldrive\e\MyDB_FG1_3.ss 12 6 c:\mssqldrive\e\MyDB_FG1_4.ss 12 7 c:\mssqldrive\e\MyDB_FG2_1.ss
SELECT * 
    FROM fn_virtualfilestats(12,1);
DbId FileId TimeStamp NumberReads BytesRead ... NumberWrites BytesWritten ... BytesOnDisk ... ---- ------ --------- ----------- --------- ------------ ------------ ----------- 12 1 24067974 50 409600 3 24576 131072 (f) Insert data on emp table in the source database.
USE MyDB;
GO
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 100) -- Insert 100 new rows
    BEGIN
    INSERT INTO emp
            VALUES (@a, 'John');
    SELECT @a = @a +1;
    END;
GO
SELECT COUNT(*) num_rows FROM emp;
num_rows ------- 99 (g) Query snapshot. Data is not there.
USE MyDB_dbss;
GO
SELECT COUNT(*) num_rows  FROM emp;
num_rows ------- 0

Reverting database MyDB from database snapshot Mydb_dbss
(a) Check num rows in emp, MyDB database.
(a) Emp table is dropped in MyDB
(b) Revert the database from MyDB_dbss
(c) At the end, Emp table will have been recovered, but without the data inserted/updated after the snapshot had been created.
(a) check table emp;
USE MyDB;
SELECT COUNT(*) num_rows 
    FROM emp;
num_rows ------- 99 (b) drop table emp
USE MyDB;
DROP TABLEemp;
(c) Revert the database snapshot. At the end, the emp table will be available, but without the data inserted after the snapshot creation.
USE MASTER;
GO
RESTORE DATABASE mydb 
 FROM database_snapshot = 'mydb_dbss';
USE MyDB;
SELECT COUNT(*) num_rows 
    FROM emp;
num_rows ------- 0





SQL Server: Moving a Database



Three mechanisms for moving a database (within the same server, or between servers):
  1. Backup and Restore
  2. Copy Database Wizard
  3. Detach/Attach

Detaching/Attaching databases
  • Data files and transaction logs can be detached and then reattached to the same or different SQL Server instance.
  • You may transfer databases across 32-bit and 64-bit environments.

Note that:
  • Make sure you have a backup of the database before attaching it to a newer version server.
  • Once you attach a SQL Server 2000 or 2005 database to a 2008 SQL Server, you will not be able to attach it back into previous version servers.
  • After attaching a database to a new server, you will need to add users from the old server, if they are to continue to have access to the database.

You CANNOT detach a dataabse when:
  • The database is replicated AND published. Before detaching you need to disable publishing (run sp_replicationdboption)
  • Database contains a database snapshot.
  • Database is being mirrored.
  • While there are database sessions.
  • Database is suspect. Needs to be put into emergency mode.
  • Database is a system database.

Moving a database:

(a) Detach the database as follows:
    -- (1) Set database in single_user mode
USE MASTER;
ALTER DATABASE AdventureWorks2008R2
    SET single_user;
GO
    --(2) Check access mode 
SELECT name, user_access_desc
    FROM sys.databases
    WHERE name = 'adventureworks2008r2';
    --(3) Detach database. Set option to skip update statistics for true.
    -- By default, the detach operation retains any out-of-date optimization statistics  
    -- If you want updated optimization statistics to be gathered, set the parameter to 'false'
            .
EXEC sp_detach_db 'AdventureWorks2008R2', 'true';

    --(4) check that the database is no longer listed in the catalog
SELECT name     FROM sys.databases;

(b) MOVE/COPY data and log files to the new location
(c) Re-attach the database as follows:
USE MASTER;
GO
EXEC sp_attach_db @dbname = 'AdventureWorks2008R2' ,
    @filename1 = 'c:\mssqldrive\e\AdventureWorks2008R2_Data.mdf',
    @filename2 = 'c:\mssqldrive\e\AdventureWorks2008R2_Log.ldf';
    -- (2) Check that the database is in the catalog and the new file locations:

EXEC ssp_helpdb Adventureworks2008r2; 
name db_size owner dbid created status compatibility_level -------------------- --------- -------------- ---- ---------- ----------------- -------------------- AdventureWorks2008R2 181.88 MB Pholder\sqltest 9 May 10 2010 Status=ONLINE,... 100 name fileid filename filegroup size ... usage ------------------------- ------ ------------------------------------------ --------- -------- ------ AdventureWorks2008R2_Data 1 g:\sqldata\..\AdventureWorks2008R2_Data.mdf PRIMARY 184192 KB ... data only AdventureWorks2008R2_Log 2 g:\sqldata\..\AdventureWorks2008R2_Log.ldf NULL 2048 KB ... log only

Attaching a database
  • Use sp_attach_db only if the database was previously detached with sp_detach_db, or copied.
  • If you need to specify more than 16 files, use instead
    • CREATE DATABASE dbname FOR ATTACH or
    • CREATE DATABASE dbname FOR_ATTACH_REBUILD_LOG
(more information: here)

SQL Server: Database Configuration Options


Configuration Options
  1. Autogrowth
  2. Recovery Model
  3. Compatibility Level
  4. Auto Shrink
  5. Restrict Access


Autogrowth - Property of each database file.
  • Control how the file expands (FILEGROWTH clause).
  • By default, each file assume values set in the model database.
  • Configure automatic growth rate (percent or fixed-size)
  • Configure maximum file size. Once reached, database goes into read-only mode.
  • Full database generates Event ID 3758.
  • Can be checked with sp_dbhelp, sp_dbhelp dbnamed, sp_helpfile filename
  • If all files have a maxsize, the database has a maxsize.


Recovery model
Determines how the transaction log is used: how transactions are logged, whether the transaction log is backed up, and what restore options are available.
Three values: { SIMPLE | FULL | BULK-LOGGED }


SIMPLE recovery model
  • Minimal logging.
  • Minimizes administrative overhead for the Transaction Log (TL).
  • Backups may need to be performed more often.
  • Here the use of differential backups can help reduce the backup overhead.
  • TL truncated after each checkpoint. During a checkpoint the dirty pages are written from bugger memory to the data file and the TL is truncated.
  • Transaction logs(TL) cannot be backed up or restored.
  • You also cannot restore individual data pages.
  • Recovery possible only to the end of the last backup.
When to use simple recovery model?
  • test environments, development, read-only production scenarios.

BULK-LOGGED recovery model
  • Minimal logging performed for bulk actions: Logs contain only the information that a bulk operation has occurred, but not the data changes incurred with such operation.
  • These include:
    • SELECT INTO,
    • Some INSERT INTO that use SELECT statement (OPENROWSET (bulk...). TABLOCK hint)
    • BULK INSERT operations,
    • Write actions performed by the BCP program
    • UPDATE with WRITE clause
    • CREATE, ALTER or DROP INDEX
  • Intended as temporary to be used when large bulk operations need to be performed in the database.

Restore possible?
  • In Bulk-Logged mode, a backup of the transaction logs also includes all data extents marked as updated by a bulk operation. This guarantees that a restore can be performed, even though not all necessary change information is available in the transaction logs.
  • Point-in-time recovery: possible ONLY if no bulk action have occurred since the last full backup.

why use Bulk-logged model?
  • Allows for high-performance bulk copy operations.
  • If many or large bulk operations are to be performed, this configuration minimizes the use of transaction logs (improving performance with less write operations) while still guaranteeing data availability if a restore is needed.

FULL recovery model
  • Full logging.
  • Logs never truncated.
  • Can be used to recover to an arbitrary point in time.

To view the Recovery model:

(a) Use the Object Explorer on SMSS. Select database; right-click and choose database properties.
(b) Use sp_helpdb [dbname]
(c) Query sys.databases
USE MASTER;
GO
SELECT name, recovery_model_desc 
FROM sys.databases;
name recovery_model_desc ------- -------------- master SIMPLE tempdb SIMPLE model FULL msdb SIMPLE TESTSQL FULL MyDB FULL

(d) To view the recovery mode of the available backup sets use msdb.dbo.backupset catalog table

i.e Change the recovery model of TESTSQL database to perform bulk copy operation:
-- (1) Backup database
BACKUP DATABASE TESTSQL TO DISK='C:\mssqlbackup\TESTSQL.bak'
GO
Processed 304 pages for database 'TESTSQL', file 'TESTSQL' on file 1. Processed 3 pages for database 'TESTSQL', file 'TESTSQL' on file 1. BACKUP DATABASE successfully processed 307 pages in 0.397 seconds (6.022 MB/sec).
-- (2) Change recovery model to BULK_LOGGED
USE MASTER;
GO
SELECT name, recovery_model_desc FROM sys.databases;
name recovery_model_desc ------- -------------- master SIMPLE tempdb SIMPLE model FULL msdb SIMPLE TESTSQL BULK_LOGGED MyDB FULL
--(3) Perform BULK operation

--(4) Change recovery model back to FULL
ALTER DATABASE TESTSQL SET recovery FULL

--(5) Backup database to include new data
BACKUP DATABASE TESTSQL TO DISK='C:\mssqlbackup\testsql.bak'
GO

Compatibility Level.
  • Database-specific.
  • Provides only partial backward compatibility.
  • Default 100.
  • ALTER DATABASE dbname SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
  • ALTER DATABASE Replaces sp_dbcmptlevel procedure
  • Do not change compatibility level while users are connected to the database.
  • (changes may affect query plan compilation)
  • Set database to single-user access mode before altering compatibility level.
  • i.e. ALTER DATABASE SET SINGLE_USER
Further reading and examples:
  • Books online





  • Database Size: Auto Shrink
    • Available, but perhaps not a good idea to use.
    • Its disk and processor intensive: file reorganization.
    • Just don't: Auto_shrink will increase Index fragmentation enormously.
    • If you need to shrink, do it manually (DBCC SHRINKFILE and DBCC SHRINKDATABASE).
    • When AUTO_SHRINK is on, both data and log files can be automatically shrunk. Files are shrunk when more than 25% of the file contains free space. Files are then shrunk to a size where 25% of the file is unused.
    • Check a good discussion here.
    -- To set AUTO_SHRINK on|off: 
    ALTER DATABASE SET AUTO_SHRINK { ON | OFF }
    
    To check the status of AUTO_SHRINK:
    SELECT name, is_auto_shrink_on  FROM sys.databases;
    go
    
    name       is_auto_shrink_on
    master     0
    tempdb     0
    model      0
    msdb       0
    TESTSQL    0
    
    alter database TESTSQL
       set auto_shrink ON;
    go
    
    select name, is_auto_shrink_on
    from sys.databases;
    go
    
    name       is_auto_shrink_on
    master     0
    tempdb     0
    model      0
    msdb       0
    TESTSQL    1
    

    Restrict Access.

    Specify which users may access the database.
    SINGLE_USER
    • Used for maintenance actions.
    • Any single user may connect, but only one user is accepted at a time.
    • To switch to single_user mode when other sessions are active, you need to disconnect them first.
    -- The command below allow 6 minutes for current sessions to complete. 
    -- After this any transaction will be rolled back: 
    
     ALTER DATABASE Sales
       SET SIGLE_USER
       WITH ROLLBACK AFTER 360;
    

    MULTI_USER
    • Default and normal state for production.
    • Allows multiple user access.

    RESTRICTED
    • Only members of the db_owner, dbcreator, or sysadmin roles.