Sunday, February 12, 2012

SQL Server 2008 R2 Profiler

In this post :

-Introduction
-Where SQL Server Profiler
-SQL Server Profiler Keywords
-When using SQL Server Profiler
-Using SQL Server Profiler
-About Next step

Introduction

SQL Server Profiler is a tool that let administrator to monitoring an instance of database engine or analysis services. The Result of monitoring is data saved in file (trace file) or stored in database (database table) to analyze it later .

Where SQL Server Profiler

·         From start > All Programs > Microsoft SQL Server 2008 R2 > Performance Tools > SQL Server Profiler

or
·         login to SQL server database engine
o   Select SQL Server Profiler from Tools menu




SQL Server Profiler Keywords


Event
it's about specific action fired in an instance of SQL Server. for example :
- Login connections, failures, and disconnections.
- The start or end of a stored procedure.
- Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
EventClass
is a type of event that can be traced .it's contains all the data that can be reported by an event .It's determines the type of data that can be collected .  for example :
- SQL:BatchCompleted
- Audit Login
- Audit Logout
EventCategory
it's a category classification for an event.
DataColumn
It's attribute of an event class . for example  Lock:Acquired event class, the BinaryData data column contains the value of the locked page ID or row.
Template
is a selected collection of configuration for a trace. you can make a template with your selection of specific events and data columns.
Trace
is a process of recording data about specific events and data columns. SQL Server let to make a trace by SQL Server Profiler or using System Stored Procedures.
Filter
It's a way to filter the result of tracing . trace need to be filtered if you need a thin trace file that contains what you need only .

When using SQL Server Profiler.


Microsoft says, it's good to use SQL Server Profiler when one of the following case needed .
·         Find the worst-performing queries.
·         Identify the cause of a deadlock.
·         Monitor stored procedure performance.
·         Audit SQL Server activity.
·         Monitoring Transact-SQL activity per user.
·         Collect a representative sample of events for stress testing.
·         Collect a sample of events for tuning the physical database design by using Database Engine Tuning Advisor.

Using SQL Server Profiler


- Open SQL Server Profiler.
- Select New Trace from File menu

- Connect to your instance that you want to trace .

- The trace properties window, will appear.

try to insert data in the following fields in the General Tab:
·         Trace name : The name of your trace
·         Use the template : Select the template that your want to use. in this example leave the default selection.
·         check in the Save to file or Save to table or both to let profiler to know how your tracing data will be store.
in the example : check in both option .
·         if you check Save to file option  you will need to specify file name and location .

·         if you check Save to table option you will need to specify your server and authentication that you want tracing data  to store it .

o   The Destination Table form will be appear .

§  Try to specify the following :
·         Database :  the database the host your tracing data .
·         Schema : the location of your table inside your database selected .
·         Table : the name of the table that you want to store your tracing data .
in this example, type MyTable_Tbl in the Table field .

- the Enable trace stop time checkbox enable to end the tracing in the specific date and time .


- Select the next tab, Events Selection tab. in this tab all events and attributes that can be traced by SQL Server Profiler.



Note that, there are default selection events . it's not all events. the show all events and show all columns checkbox can display all events with its attributes that available .

Note also the following :
1- Button Column filters ... to filter the result of tracing . for example it's applicable to filter the result of tracing only in the specific database name .
2- Button Organize Columns ... to sort columns in a certain way .

  
- After selecting all events wanted in tracing, Press Run button .
- The new window will appear .

it's a window of tracing . SQL Server Profiler now trace the activity of SQL Server in the selected events and store the data of tracing in the My_Trace file and MyTable_Tbl table that is found in the My_Test_DB database .

by clicking on any row in the grid of tracing, the details of the event will appear in the bottom of grid .

- SQL Server Profiler can pause the activity of tracing or stop it by the buttons in the toolbox of SQL server Profiler.


- After a little time,  Press Stop Button . the tracing will stop now and the task of SQL Server Profiler finish now also .
- all Traced data now stored in the file trace and table database .

 
(File Trace)

 
(Structure of table trace)

 
(Data stored in the Trace table)

About Next step

Database Engine Tuning Advisor is a performance tool that let administrator to analyze and view recommendations about specific issues.  this tool need to data to be analyze, this data can be prepared from SQL Server Profiler .
Follow the following steps, in order to analyze and view recommendation bout traced data .
- From Start > Microsoft SQL Server 2008 R2 > Performance Tools > Database Engine Tuning Advisor

- Try connect to SQL Server
- In the Workload section, select File option or Table option depending on the result of SQL server Profiler .

- from Database for workload analysis option, select the database that will do the analysis of workload .
- Then, simply select databases and tables to analyze it .

- from tool bar, select Start Analysis button. the Progress tab will appear.

- After Progress tab finish, the Recommendations and Reports tabs will appear.

and

there are a set of reports in Tuning Reports Section

Thursday, February 9, 2012

Security Scenario: Creating login object with User object only in the specific database.



This scenario will create the following only in SQL Server .
- Login object in selected SQL Server instance.
-User Object in specific database

1- Problem
Need to prove that creating Login and user object only not sufficient to let user access to objects of database .

2- Solution
·         Logon to SQL Server by administrator account.
·         Click in the New Query button and typethe following lines.

·         From SQL Server instance open Security folder and then Logins folder

·         Right Click on the Logins folder and then select New Login ... Option
·         The Login - New form will appear.
·         In the General tab of Login - New form type the Login Name 'My_Test_Account'
·         And then choose SQL Server authentication option to enter password and confirm it. type password 'abc'
·         Remove the check of Enforce password policy

·         in the User Mapping tab, check on 'My_Test_Account' Database. this check mean SQL Server will create user account for this Login and will put it in the selected database and it's mean also this login can connect to selected database but until now without any permission to working with selected database objects. the login will not be able to connect to other databases that not check in the User Mapping tab.

·         Then Press OK Button . login now ready to use.

(Login created)


(User created depending on Login)



·         To check that My_Test_Account can access My_Test_DB database, try to Right Click on the My_Test_DB  database and then select Properties .
·         In the Permissions tab, search for Connect permission in the Explicit tab .note that this permission has Grant by default.

·         Before testing the login object, try to check Server authentication that let My_Test_Account Login to access SQL Server .
·         Right Click on Instance Server, and then click Properties.
·         The Server Properties form will appear .
·         Select the Security Tab, and then select SQL Server and Windows authentication mode option.
·         Now click OK button. SQL Server will need to restart the SQL Server Engine, help SQL to do that !.
·         After SQL server restart SQL Server Engine, try to log off from administrator login account.
·         try to logon again by New Login account.

·         Try to browse My_Test_DB Database. it will open successfully .

·         Try to browse any database else My_Test_DB Database, it will make the following message.

·         click on New Query button and then type the following.

it will make the following result.



3-conclusion

In this scenario, the login account without user object in the database can't connect to database . administrator need to make a set of permissions for users objet in the database in order to ensure the ability to use objects of database. The new Login Account with new User Account can connect to database only without any access to any object of that database.

Wednesday, February 1, 2012

Fixed Server role for SQL Server 2008


The following is description of fixed server roles that exists in SQL Server.



Public

o Any login created in SQL Server must be a member of public role.

sysadmin

o If login is a member of sysadmin, then login can do anything in the SQL Server even if this login blocked from doing something.

o If you need to check login which is a member of specific role, type the following statement in a new SQL Server query
SELECT IS_SRVROLEMEMBER('sysadmin');

bulkadmin

o transferring data from out of SQL Server to inside it (bulk insert operation) or other operation such as service accounts that are performing Extract, Transform, and Load (ETL) need to be performed by a login that a member of bulkadmin.

o To be a member of bulkadmin not enough to do bulk insert operation for example but it’s need also to has insert permission to complete operation successfully.
dbcreator

o This role allows to its members the ability of creating databases.
diskadmin

o This role allows to its members the ability of managing backup devices.
processadmin

o This role allows to its members the ability of alter any connections.
securityadmin

o This role allows to its members the ability of managing and controlling logins properties for SQL Server.

o The member of securityadmin role can manage other server roles but not sysadmin role.

o This role can’t define any internal security within a database.
serveradmin

o This role to manage the SQL Server configuration.

o The Member of this role can shutdown SQL Server .
 Shutdown SQL Server by command SHUTDOWN or SHUTDOWN WITH NOWAIT

 To display a list of all active processes and locks, run exec sp_who and exec sp_lock.
setupadmin

o This role allows to its members the ability of controlling linked servers.



Follow by Email