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

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

Shrinking database and database files in SQL DBA

Shrinking databases and database files in SQL Server DBA:

• The size of database is reduced by collectively shrinking the database files,releasing unused space.
• TO shrink individual database files.
• We can shrink tempdb. But shrinking files regularly is not a recommended practice, because these files may probably grow again. Also shrink operations causes’ data fragmentation.

We have some limitations on shrinking tempdb:

• Shrink operations do not shrink the version store or internal objects.
• In some cases the DBA might need to restart the server in a single user mode to allow shrinking of the tempdb.

  

Read More