FRAGMENTATION CONCEPT IN SQL SERVER DBA

Fragmentation:

• Storing data non-contiguously on disk is known as fragmentation.
• We have 2 types of fragmentation.

1. Internal fragmentation
2. External fragmentation

1.Internal Fragmentation:

• When records are stored non-contiguously inside the page, then it is called internal fragmentation.
• In other words, out of 8kb page data, pages left empty not filled properly inside.Due to this 4 pages data stored in 8 pages.This is Internal fragmentation.
• This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE) that are made on the table and therefore, the indexes defined on the table.
• Since these changes are not equally distributed among the rows of the table and indexes, the fullness of each page can vary over time.
• This unused space causes poor use of cache and more I/O, which eventually led to the poor performance of queries.

2.External fragmentation:

• My data earlier stored in one extent,due to shrinking operation or recompilation my required data goes to 10th extent instead of 1st extent.
• While searching it has scan all 10 extent pages.This is called external fragmentation.

FIND THE FRAGMENTATION BY USING COMMANDS:

1.DBCC SHOWCONTIG:It shows you how fragmented a table.

syntax: dbcc showcontig(tablename)

• This feature will be removed in a future version of Microsoft SQL Server.
• Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. 

2.Use sys.dm_db_index_physical_stats instead:
Find the fragmentation by using DMV commands:

select * from sys.dm_db_index_phisical_stats

This DMV will show all indexes information on server including system databases.

As well as other information, there are two important columns for the detection of fragmentation, which are:

1.avg_fragmentation_in_percent: This is a percentage value that represents external fragmentation.
For a table and leaf cluster level index pages, this is the logical fragmentation, whereas heap, it is externalfragmentation.
The higher this value is, the better it is. If this value is greater than 10%, corrective action must be taken.
2.avg_page_space_used_in_percent: This is a use of the average percentage of pages representing the internal fragmentation.
The higher the value, the better it is. If this value is less than 75%, corrective action must be taken.
Reducing fragmentation:

Reducing fragmentation in a Heap:

• To reduce the fragmentation of a heap, create a clustered index on the table. 
• Creating the clustered index, rearrange records in order, then place the contiguous pages on disk.

Reducing fragmentation in an index: 

1.Reorganize:(If avg_fragmentation_in_percent> 5% and <30%)

• The type reorganize defragmentation uses the DBCC INDEXDEFRAG  to reorganize pages leaves the index up.
• This process is similar to a sort of bubble.
• Although the pages are rearranged physically, they may not be contiguous in the data file. 
• This problem can cause interleaved indexes, which need to be rebuilt to store in contiguous pages.
• Then use ALTER INDEX  ALL ON TABLENAME REORGANIZE
• This declaration is the replacement for DBCC INDEXDEFRAG  to reorganize the leaf pages of the index in a logical order.

2.Rebuild:(If avg_fragmentation_in_percent> 30%)

• The rebuild defragmentation type uses the DBCC DBREINDEX command to rebuild the indexes on the tables. The rebuild operation creates new, contiguous pages. 
• SQL Server 2005/2008 allows the option to Rebuild Online, which allows access to the tables before the operation is finished.
• However, choosing to rebuild online requires more resources (disk space, CPU, memory), and may slow performance.
• Then use ALTER INDEX ALL ON TABLENAME REBUILD: This is a replacement for DBCC DBREINDEX to rebuild the index online or offline. 
• In this case, we can also use the drop and recreate method index.

Read More

SQL Server Security Authentication Concept

 

Authentication:

• Authentication is one of the security concept in SQL DBA..
• Validating credentials(user name and password) on server called Authentication.For any user who are connecting to server will have login name and password.

        We have 2 types of logins:

1. Window login
2.  SQL Server login

1.Window login

• Domain user,Database Administrators,other admins,developers will have windows login for connect to server.
• These user use same account for connecting to windows and SQL Server.
• Windows login store in windows component is called active directory.
• this login provides high security.

2.SQL Server login:

• For the users who are connecting externally from frontend we are providing SQL login.
• They first connect to windows and then connect to SQL Server.These logins stores syslogins table of Master database.
• We need to provide security to SQL Server.We have to validate both windows and SQL logins.This process of checking login name and password is Authentication.

There are 2 types of Authentication in SQL Server.

1.Windows mode authentication

2.Mixed Mode:

1.Windows mode authentication:

• It allows only window login users in this mode.
• It enforces only windows user to connect and access the databases.
• When we use highly restricted database we go with windows login.
• We don't provide access to external users if the data is highly restricted.
• This mode provides best security.
• It validates logins in Active directory.

2.Mixed Mode:

• This mode allows both windows and SQL logins.
• When ever any login tries to establish connection first it validate user name and password in active directory.
• If login exists,establishes connection other wise validation goes to syslogins of master database and verifies there.
• If login exists it will establish a connection.
•     When ever any request comes first we need to verify the login active directory.If it is not exist we need to create (or) ask Active directory admins to create command for windows.

     CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> }

Authentication at database level:

• Login Account is a key to Main door means to connect to server.It's a server level access.
• We require another account called user account to access the databases inside the server.This is a database level access.
• We need to grant access for these user account on each database separately.

User Mapping:

• Every  login account will be mapped with the user Account.
• Every login will have a security id(sid),its a unique security number inside SQL Server to avoid duplication of logins using the sid of login account.
• We will map to sid of user account.
• Now it establishes relationship between login account and user account,this is user mapping.
• One login account will mapped to one user account only We call as one to one mapping.
• Once the mapping is created between login and user account,SQL Server allows to connect to server and then to access the databases inside the SQL Server.

Creating Windows login:

           create login login name from windows

Creating SQL Server login:

1. For creating any login,It requires 4 properties
2. Login name
3. Password
4. Default database
5. Default language(optional)

Using T-SQL:

        sp_addlogin 'loginname' 'password' 'default database'

         ex: sp_addlogin 'sql2012admin' 'ravi@123' 'master'

Using GUI:

 go to security folder under management studio-->Go to login-->Right click on login-->Select New login.

               

Read More

SQL SERVER DBA MIGRATION CONCEPT

 

MIGRATION:

• Migration means moving one or set of databases from one server to another server.

Pre-Migration Checklist:

1. Analyze the target server disk space for the new database, if the disk space is insufficient add more space on the target server
2. Confirm the data and log file location to the destination server
3. Collect the information on the database properties (Auto Stats, DB owner , Recovery Model , compatibility level , Trustworthy options etc ).
4. Collect information dependent applications , make sure application services will be stopped during the database migration.
5. Collect information database connections , users and their permissions . (Optional).
6. Check the database for orphaned users if necessary.
7. Check the SQL Server for all dependent objects ( SQL Agent Jobs and linked servers ).
8. Check if the database is part of a maintenance plan.

Steps in migration:

  1.Script to Check the Disk and Database Size:

• To check disc space command:

                xp_fixeddrives

• To Check database size:

           exec sp_helpdb [dbName]

     2.Transfer logins:

• Before moving the databases,script out the logins on source server and keep them a side.
• There is a stored procedure sp_help_revlogin,We have execute this stored procedure in the server.
• First it creates the stored procedure under master database.This will not come as part of sql installation.
• Once the stored procedure created we have to run the stored procedure second time it will script out all logins of source server including password in encrypted format.
• We can copy all logins or few logins according to our requirement.
• Copy all the logins to destination server and execute them on server.same login and password will be created.
•  second way,Right click on login and script as copy them on other server.
•  This process will not copy passwords.

   3.Transfer jobs:

• Right click on job-->script job as-->create to new window,copy the script and run on other server.
• When ever we want to transfer all jobs we need create a package and transfer that package.

  4.Transfer the packages:

• We have to connect to Integration services-->go to msdb database-->under stored packages we will have SSIS packages-->
• Right click on packages-->on the package wizard select export package
• Provide destination server location:
•  Now connect to destination server-->go to integration services-->goto stored packages-->under MSDB right click on packages and
• select import package,package will be created on server.

   5.Transfer the databases:

      Backup and restore:

• Take backup on source server and copy them to destination server.
•  Right click on database in destination server-->select restore and provide backup details and click ok backup and restore is online.
•  source will not be distributed here.Roll back is easy.

      Attach and Dettach:

• Right click on database of source server-->go to task-->select dettach database,database will go offline copy files from source server and paste them.
• Goto destination server-->select attach-->provide path details

  6.Script to List Linked Servers

     select  * from sys.sysservers

  7.Check new database properties:

     Verify the database size from database properties

      From options verify--recovery model,db owner,compatability level and collation settings.

     Change the db owner using: sp_change dbowner 'sa'

    Change compatability level:

    ALTER DATABASE DatabaseName

    SET SINGLE_USER

   GO

   EXEC sp_dbcmptlevel DatabaseName, 90;

   GO

   ALTER DATABASE DatabaseName

   SET MULTI_USER

   GO

8.Find and fix orphaned users:

• When ever taking full backup and copy & restore on destination server.
• All user account will copy destination server.
• User account with out corresponding login account is called orphaned users.
• As the login will present in active directory or syslogins of master db.
• Some times sychronization will miss even after transfer logins also.

     To find orphaned users:

     sp_change_user_login 'report'

    To fix orphaned users:

    sp_change_users_login 'update_one' 'user name' 'login name'

Update one will establish sychronization between username and login name

Read More

SQL Server Security Authorization Concept

 

Authorization:

• Once user connected to server,they should have permissions to perform/operating certain tasks.
• As a DBA we need to restrict the permissions by granting the roles,roles are set of privilages for connection server and to access the database.

       We have 2 types of roles.

1. Server role
2. Database role

1.Server role:

server role to perform server side operatios.

We have 8 server roles and by default it will be public role.

      1.sysadmin:

• Top most privilage on the server,capable of doing any thing on the server.DBA'S will have this role.

      2.serveradmin:

• Allows users to manage configurations on the server.
• capable of start and stop services from configuration manager,server settings,memory configurations and shutdown of SQLServer.

     3.setupadmin:

• setupadmin capable of creating linked server.
• He can run setup(utilities) like replication,mirroring.

     4.securityadmin:

• Capable of creating new logins,delete logins and reset passwords.
• He has ability to grant,revoke and deny permissions.

     5.processadmin:

• Monitors the process running under taskbar,capable of killing problematic process.

     6.bulkadmin:

• Capable to performing bulk inserts and updates.

     7.diskadmin:

•  Manage disk files.

      8.DBcreator:

• This role is capable of creating databases.
• He can alter and drop.
• Taking backups and restore.

Using T-SQL:

     sp_addsrvrolemember 'loginname' 'privilage'

     ex:sp_addsrvrolemember 'ravi' 'sysadmin'

Using GUI:

   Right click on login-->New login-->click on server roles-->check certain privilage-->ok.

coolpad

2.Database roles:

      1.db_owner:

• Dbowner is capable of doing any thing on particular database.
• Highest privilage at database level.

       2.db_securityadmin:

• He is responsiable for creating/deleting/modifying roles to users and manages permissions.

       3.db_datareader:

• He can run select queries on all tables,only read activities.

       4.db_datawriter:

• He can perform Insert/update/delete data in the database.

        5.db_denydatareader:

• To restrict the read access on particular confidential data even through user has server level (or) high privilages at db level.
• We can prevent that user in accessing particular database using deny role.

        6.db_denydatawriter:

• He can not able to write data on restricted databases.

        7.db_ddladmin:

• HE can able to create/alter/drop the tables and other objects.

         8.db_accessadmin:

• He can not able to create (or) modify any user role.
• He can grant only roles and remove access to other users.

         9.backupoperator:

• He can able to take backup of databases.

         10.public:

• This is default role on database level.

Using T-SQL command:

         sp_addrolemember 'username' 'rolename'

         ex:sp_addrolemember 'testadmin' 'db_datawriter'

Using GUI:

    Right click on login-->go to properties-->go to user mapping-->select database and grant database role.

NOTE:SQL Server2012 introduces contained databases,there are 2 types of Authentications in this

1. Contained databases users with passwords.Stores all the passwords with in contained database.
2. Contained databases window is similar to windows login.

3.Object level:

  We need to identify 3 things in providing object level access.

1. Principals are login accounts to whom we are granting permissions.login accounts and user account comes under principal.
2. Privilages, how we granting permissions to access objects.
3. Securables,on which object we are granting tables,stored procedures and views.

Through T-SQL::

        grant/revoke permissions on securables to principal

        ex:grant insert on dbo.sqltest to empadmin

Through users:

        Go to users under database-->go to properties-->go to securables-->select objects search-->object types provides list of objects tables,view etc.

select them-->provide permissions and click ok.

Read More

Protocols available in SQL Server DBA

Protocols available in SQL Server:

SQL Server network interface(SNI) is a  protocol layer that establishes the network connection between the client and server.

SQL server supports 4 protocols

1.Shared memory

2.Named pipes

3.TCP/IP

4.VIA

Shared memory:

• It is default protocol used to connect client and SQL server on the same machine.

Named pipes:

• Client and server will connect with in a LAN.

• It has certain limitations.

TCP/IP:

• TCP/IP is most used protocol for SQL server.

• Client establishes connection with SQL server using an IP address and a port number(1433).

• We can access the databases using internet hence there are no boundaries for this protocol.

VIA(Virtual Interface Adaptor):

• Via is a wireless internet protocol for connecting client and server with certain range.

• The VIA protocol is deprecated.

• This feature will be removed in a future version of Microsoft SQL Server.

coolpad

How to see this protocols:

 Goto SQL server configuration manager--->click SQL Server Network Configurations-->protocols for particular instance.

Read More

SERVER LEVEL AUDIT IN MS SQL SERVER DBA

 

Server level Auditing in MS SQL Server:

• Audit of an instance of SQL Server or SQL Server database involves tracking and logging events that occur on the system.
• The SQL Server Audit object collects a single server instance or on the basis of equity and action groups to monitor.
• Verification is the SQL Server instance. You can have multiple audits per SQL Server instance.
• The specification of objects of verification server describes what to check. As its name suggests, this article focuses on the actions of the server instance.
• An audit server specification is associated with a server audit to design audit data is written.
• There is a one-to-one relationship between the specification of server object auditing and purpose of checking the server.
• A key element of any data security strategy is the ability to track who has accessed or attempted to access your data.
• This provides the ability to detect unauthorized access attempts or, where appropriate, to replenish stock malicious insiders misused their legitimate access.
• In addition, a rich and robust tracking capability can monitor sensitive configuration changes made by administrators.

       Audit SQL Server is designed with the following main objectives:

• Security - The audit function, and its objects are to be really sure.
• Performance - Performance impact should be minimized.
• Management - The audit function should be easy to manage.
• DiscoverAbility - Audit focuses on issues should be easy to answer.

permissions

Users with ALTER ANY DATABASE AUDIT permission can to create basic audit specification data and link them to any audit.

After an audit specification database is created, it can be accessed by managers with the control server, ALTER DATABASE all verification authorizations, or sysadmin account.

lenova

Using Transact-SQL:

           USE master ; 

           GO 

                Create the server audit.  

                CREATE SERVER AUDIT auditname 

                TO FILE ( FILEPATH ='path' )

            [WITH (QUEUE_DELAY = 0, ON_FAILURE = SHUTDOWN)] ;  

 Enable the server audit.  

       ALTER SERVER AUDIT auditname  

       WITH (STATE = ON) ; 

Alter the server level audit:

       Transact-SQL:

       ALTER SERVER AUDIT auditname

       TO APPLICATION_LOG

       WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)

• This statement creates an audit specification of the server to monitor backup / restore events, SQL Server service starts and stops, and changes in the composition of the server roles.

Transact-SQL:

  CREATE SERVER AUDIT auditname

   FOR SERVER AUDIT PCI_Audit

  ADD (BACKUP_RESTORE_GROUP),

  ADD (SERVER_STATE_CHANGE_GROUP),

  ADD (SERVER_ROLE_MEMBERSHIP_CHANGE_GROUP)

  WITH (STATE = OFF)

• Audit server specifications can be reassigned to a different server audit after their creation by using the ALTER SERVER AUDIT SPECIFICATION.
• This statement also provides the list of action groups checked change and the state of being switched between on (ON) and off (OFF).

The following example adds an action group for the specification created in the previous statement.

Transact-SQL:

  ALTER SERVER AUDIT auditname

  ADD (SERVER_PRINCIPAL_CHANGE_GROUP)

The following statement reads all audit files for a specific audit.

Transact-SQL:

SELECT * FROM auditname(

'D:\Audits\MyAudit-_C26128D1-F97B-4B82-9E47-B6A296045B05_*.sqlaudit',

default, default)

Configuring server level auditing in GUI method Video:

Read More

SQL SERVER DBA LOG SHIPPING CONCEPT

 

LOG SHIPPING:

• Log shipping SQL Server allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances.
• Backups of the transaction log are applied individually to each of secondary databases.
• It is a continuous process in the form of batches.

Logshipping requires 3 servers:

1.primary server:It contains source database for sending the transaction logs.

2.Secondary server:It contains destination database receiving the transaction logs.

3.Monitor server:

      An optional instance of SQL Server that tracks all of the details of log shipping, including:

• When the transaction log on the primary database was last backed up.
• When the secondary servers last copied and restored the backup files.
• Information about any backup failure alerts.
• Once the monitor server has been configured, it cannot be changed without removing log shipping first.

Log shipping mainly depend on 4 jobs:

1.Backup job:

• For backing up transaction logs on primary server database.
• this job always run on primary server.

2.Copy job:

• Copy job runs from secondary server,copies transaction log backup from primary server to secondary server.

3.Restore job:

• Restore run on secondary server to restore backups.

4.Alert job:

• A SQL Server Agent job that raises alerts for primary and secondary databases when a backup or restore operation does not complete successfully within a specified threshold.
• When log shipping is enabled on a database, job category "Log Shipping Alert" is created on the monitor server instance.

iphone 6s

Logshipping steps:

1. Before log shipping configuration,take full backup of primary database and restore it on secondary server.This is initial synchronization.
2. Create backup job on primary server to backup the transaction logs of primary database.
3. Create copy job on secondary server to copy the transaction logs.
4. Create restore job on secondary server to restore the transaction logs.
5. Create an alert job on monitor server to indicate a failure if the databases are out of synchronization beyond the configure threshold.

Failover steps:

Planned failover:

planned failover situations:

1. upgrade from 2008R2 to SQL Server2012
2. Hardware failure

In these situations keep primary server in offline and make secondary server database online.

1. Disable all log shipping jobs on both servers.
2. Make sure copy all backups copied to secondary server.
3. Restore all T-log backup in same order.
4. Change connection to secondary server and start jobs.

Unplanned/Disaster:

Need to identify crash time,when last backup,copy and restoration happened on server using 'dbo.logshipping_monitor'tables of MSDB databases.

1. Disable all log shipping jobs.
2. Perform Tail-log backups if log files available.
3. Manually copy last backup's from network share to secondary server.
4. Restore all T-log backups in same sequence.
5. Now secondary database will come online.
6. Sync all logins,jobs then find orphaned user and fix them.

Log shipping Prerequisites:

1. SQL Server 2005 or later.
2. Standard, Workgroup or Enterprise editions must be installed on all server instances involved in log shipping.
3. To setup a log-shipping you must have sysadmin rights on the server.
4. Make sure recovery model should be full.
5. Maintain same hardware between both server to avoid performance issue.
6. Services account running on both servers must have access to network share where log backups store.
7. Keep secondary server always in no recovery.
8. Make sure number of drives and name of drives should be same on both servers.

TUF file in log shipping:

• TUF file stands for Transaction Undo File(TUF).
• TUF file contains the modifications that were not committed on primary database.
• When transaction log backup was in progress and when log was restoring on secondary database.
• When next transaction restoring in secondary server,SQL Server uses the TUF file and starts restoring incomplete transactions

Log shipping Advantages:

1. Only logshipping allow to keep secondary database in stand by mode.
2. We can maintain multiple secondary databases.
3. No additional hardware or software required.

Logshipping Disadvantages:

More downtime possiable,We can not bring secondary server immediately.

Data loss possiable,as jobs run every 15 minutes.

We have to perform manual failover.

Need to perform several steps to bring secondary server online which we call warm stand by.

pendrive

Read More

Versions,Editions, Service Packs ,Compatability Levels of SQL SERVER

VERSIONS OF SQL SERVER:

Version               Year Codename
6.0                      1995 SQL95
6.5                      1996 Hydra
7.0                      1998 Sphinx
8.0 (2000)            2000 Shiloh
9.0 (2005)            2005 Yukon
10.0 (2008)          2008 Katmai
10.5 (2008 R2)     2010 Kilimanjaro
11.0 (2012)          2012 Denali
12.0 (2014)          2014 Hekaton

EDITIONS OF SQL SERVER:

SERVICE PACKS:

2005(4)         2008(4)          2008R2(3)  2012 (2)          2014(1)              
RTM 9.00.1399 10.00.1600 10.50.1600  11.00.2100 12.00.2000
SP1 9.00.2047 10.00.2531 10.50.2500  11.00.3000 12.00.4050
SP2 9.00.3042 10.00.4000 10.50.4000  11.00.5058
SP3 9.00.4035 10.00.5500 10.50.6000
SP4 9.00.5000 10.00.6000
QFE/CU 9.00.5324 10.00.6526-HF 10.50.6525-HF 11.00.5582-CU5

COMPATABILITY LEVELS:

Version Compatibility
6.0          60
6.5          65
7.0          70
2000          80
2005                  90
2008         100
2008 R2         100
2012 110
2014 120

pendrive

Read More

SQL Server Security Encryption Concept

          Encryption:

•  Encryption is a one of the important process in SQLServer security concepts.
•  Using encryption we can protect the confidential data while transferring data through       networks.
• It restricts hackers to access data. 

1. Encryption using certificate.
2. Encryption using asymmetric keys.
3. Encryption using symmetric keys.

1.Encryption using certificate:

• Certificate Encryption is used to identify users,devices and organizations.
• Certification authorities generate these certificates to provide high security.
• SQL service will use these certificates and encrypts data.

2.Encryption using asymmetric keys:

• asymmetric keys uses a public key that encrypts data before sending and private decrypts that message after receiving.Here we have 2 keys in encryption.

3.Encryption using symmetric keys:

• symmetric key uses same key to encrypt and decrypt the data.

NOTE:Transparent Data Encryption(TDE):TDE is a SQLServer2012 enhanced feature that allows encryption of data in better way.

Read More

STORAGE ARCHITECTURE & DATA FILE ARCHITECTURE OF MS SQL SERVER

 

STORAGE ARCHITECTURE & DATA FILE ARCHITECTURE:

File groups:

• The database files can be grouped in file groups for allocation and administration.
• No file can be a member of more than one group of files.
• The log files are never part of a group of files, log space is managed separately from data space.
• Files in a file group will not be autoGrow unless there is no space available on any of the files in the file group.
• Two types of file groups are there in SQL Server 1. Primary 2. User Defined.
• primary filegroup contains the primary data file and any other files not specifically assigned to another group of files.
• All pages for the system tables are allocated in the primary filegroup.
• Files user-defined groups are groups of files specified using the keyword filegroup in create database or alter database.
• A group of files in each database runs as the default file group.
• When SQL Server allocates a page to a table or index for which no filegroup is specified when they are created, pages are allocated from default filegroup.
• To change the default file group of a group of files to another file group must have db_owner fixed role db.
• By default primary filegroup is the default file group.
• User should have db_owner fixed db role to take backup of files and file groups individually.

Files:

• The databases have three types of files 1. Primary 2. Secondary datafile datafile file 3. Log
• primary datafile is the point of the database and points to other files starting in the database.
• Each database has one primary data file.
• We can give any extension to the primary data file, but the recommended extension is .mdf.
• secondary datafile is a file other than the primary data file in the database.
• Some databases can have multiple secondary data files.
• Some databases may not have a single secondary data file.
• Extension recommended for secondary datafile is .ndf, but we can give as our wish.
• Logfiles hold all the log information used to recover the database.
• Database must have at least a logfile.
• We can have several log files to a database.
• The recommended extension for logfile is .ldf, but we can change.
• Recommended Extension files to help identify use of the file.
• The location of all files in a database are stored in both master database and the main file of the database.
• when restoring master database
• Files with names 1. 2. two physical logic
• Logical name is used to refer to the file in all T-SQL statements
• Physical name is os_file_name, it must follow the rules of the OS
• The data files and logs can be placed on FAT or NTFS file systems, but can not be placed on compressed file systems
• We can have upto 32,767 files in one database.

Extents:

• Extents are the basic unit in which space is allocated to tables and indexes.
• A measure is 8 contiguous pages or 64 KB.
• SQL Server has two types of extents 1.Uniform 2.Mixed.
• uniform extents consist of only one object.
• Mixed extents are shared by up to eight objects.

Pages:

• It is the basic unit of data storage in MS SQL Server
• The page size is 8 kb
• The beginning of each page is 96 byte header used for information storage system such as the page type, amount of free space on the page and the object id of the object with the page

9 types of data pages are there in SQL Server

1. data --- data lines with all data except text, ntext and image data
2. Index --- Index entries
3. Test --- picture text, picture and data ntext
4. GAM --- the assigned extensions Information
5. SGAM --- Extensions Information allocated at system level
6. Free Space page (PFS) --- Information about free space on pages
7. Allocation Index map (IAM) --- Information extensions used by a table or index
8. Bulk changed the map (BCM) --- Information extensions modified by bulk operations since the declaration of last backup log
9. Differential Changed Map (DCM) --- Information on extensions that have changed since the last backup database statement

• The log files do not contain pages, but they contain a series of log records.
• offset table line starts at the end of the page.
• Row offset table contains one entry for each line on the page and each entry records how far the first byte of the line since the beginning of the page
• The inputs of the line offset table are in reverse order of the sequence of lines on the page

Read More

SQL SERVER - Introduction & Uses of SQL SERVER

SQL SERVER: 

• It is a software which is implemented from the specification of RDBMS.
• It is also ORDBMS.
• It is from Microsoft.
• It is high performance and low price RDBMS.
• It is platform dependent.
• It is both GUI and command based software.
• It supports SQL (SEQUEL) language which is IBM product, non-procedural, common database and case insensitive language.

SQL SERVER USES:

• To create databases
• To maintain databases
• To analyze the data (SSAS)
• To generate reports (SSRS)
• To do the ETL operations (SSIS)

Read More

New Features & Enhancements of SQL SERVER 2005,2008,2008R2,2012,2014

New Features & Enhancements of SQL SERVER 2005:

·         Integrated installation of SQL Server.

·         Resource database.

·         Client tools are integrated.

·         Database snapshot.

·         Database mail.

·         DAC (Dedicated administration connection) connection.

·         SSIS (SQL SERVER Integration Services).

·         Schema concept.

·         Partially, Copy-only and Mirror backups.

·         Online index rebuilds.

·         Database mirroring.

·         DMV's (Dynamic management views).

·         SNAC (SQLSERVER Native Access Client).

·         Online restores

New Features & Enhancements of SQL SERVER 2008&2008R2:

·         Activity monitor.

·         SQL Server audit.

·         Backup compression.

·         Data compression.

·         Central management servers.

·         Data collector and management data warehouse.

·         Policy-Based management.

·         Resource governor.

·         Transparent data encryption (TDE).

·         Change Data Capture (CDC).

·         Sqlserver 2008R2 datacenter.

New Features & Enhancements of SQL SERVER 2012:

·         Always On Availability groups.

·         Windows server core support.

·         Column store indexes.

·         User-defined server roles.

·         Default schema for Windows groups.

·         Enhanced auditing features.

·         Distributed replay.

·         Indirect checkpoints feature.

·         Partially contained database.

·         Online index create, rebuild, drop for varchar (max), nvarchar (max) and varbinary (max) columns.

New Features& Enhancements of SQL SERVER 2014:

·         In-memory OLTP engine.

·         Always On enhancements.

·         Buffer pool extension.

·         Updatable column store indexes.

·         Backup encryption.

·         Sqlserver managed backup to windows Azure.

Read More