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

DBA'S Roles and Daily Activities

DBA's Roles and Daily Activities:

• As part of DBA team need to provide 24/7 production support to client and users.
• We will look into client mail box first and respond to required mails
• We will check Ticketing tool and will work on the available tickets which includes Incidents, SR's, Changes and Problem tickets based on priority whether it's P1\P2\P3 
• We will check and work on recurring activities if any such as weekly, monthly, quarterly, half yearly and yearly.
• We will work on adhoc requests which are requested  by client.
• Make sure all the maintainence jobs are running successfully.
• Make sure all backup jobs executed successfully without any issues on all servers.
• Checking sql server logs to identify bottlenecks.
• Checking drive spaces on critical servers to ensure that there is ample amount of space.
• Regularly monitor data file growth,log file growth,database growth as part of capacity planning.
• Maintain documentation of all the tasks and issues that you encounter for future reference.
• Check whether all sql services are running or not.

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

SQL SERVER DBA ATTACH AND DETACH THE DATABASES

It is one of the method for moving the database from one instance to other instance in sql server.In this process real time used for sql DBA's.

lenova

Read More