SQL server

Software Requirements:

Ram: 512 MB and minimum of 192 MB

Drive Space: 600 MB.

Operating Systems:

 SQL server 2005 express edition

 Windows server 2003 Enterprise edition
 Windows server 2003 Express edition
 SQL developer Edition
 Windows 2000 Data center server edition
 Standard Edition ….

In our application we are using SQL server 2005 Enterprise Edition. While comparing with other
editions Enterprise edition is very compatible to us.

DATA BASE:

A database is a collection of objects that is organized so that it can easily be accessed, managed, and
updated.

SQL SERVER:

It is a server which is used to store Database which comes from RDBMS (Relational Database
Management System).

It is a product of Microsoft and it is platform dependent.

The name SQL server itself says that it supports server environment.

SQL DATABASES:

SQL has two types of Databases. They are

1. U.D.DB(User Defined Database)

2. S.D.DB(System Defined Database)
U.D.DB:

It is a Database which has been developed by the user for his convenience .It is a built in program. User
can create his own Tables, Functions, Triggers, and Cursors….ETC; whenever the user wants he can use
this Database. In SQL user can create So many databases and retrieve data when required.

S.DB:

It is a Database which Comes default, while installing SQL Server into our Machine. It is the main
advantage in SQL server .The databases that will be there in S.DB are

1. MASTER 2.MODEL 3.MSDB 4.TEMP 5.RESOURCE

MASTER:

It is heart of SQL server. Every Instance of SQL server will have an dependent of Master Database. The
Database includes information such as system logins, configuration settings, linked servers, and general
information regarding the other system and user Database for Instance, without master database SQL
Server cannot be restored. If Master Database is corrupted it should be restored from the latest Backups.

MODEL:

The Model database is basically used as a template when creating databases in SQL Server.SQL Server
takes a copy of model database, when ever user tries to create a new Database in SQL Server.

MSDB:

The MSDB database stores information regarding Database Backups and tells about the SQL Agent Job
History, DB creation History. This Database also Stores SSIS packages created in SQL Server 2005 and
2008.The Data Transformation Service (DTS) packages in SQL server 2000 are also stored in MSDB
Database.

TEMP:

The TEMP Database is the only Database which is recreated every time when SQL Server Restarts. It
stores the data temporarly.It basically stores all the temporary objects such as Temporary Tables, Global
Temporary Tables, Stored Procedures Etc. It does not support Backup and Recovery operations.

RESOURCE:

The Resource Database is a read only, hidden system database that contain all the SQL server system
objects which are physically available only in the Resource Database even though they logically appear in
the sys schema of every Database .Resource Database does not contain any user data or user meta data
(data about data) replica of master database will be there in Resource Database.
Types of Files

There are three types of files in SQL Server, there are

1. DATA FILE (Primary File)

2. LOG FILE

3. SECONDARY FILE

The above three are combinely called as DATABASE.

DATA FILE:

It is also called as Primary file. The extension name of this file is .MDF (Master Data File). It was
having the internal data. Every database as one primary Data file. It is the main database file .All
the Tables, stored procedures, views, Triggers …etc are stored in .MDF file of SQL Server .Once
we attach the .MDF we are able to see all the data exist in the respective Database. The primary
data file is the starting point of the database and points to the other files in the database.

LOG FILE:

The Extension name of this file is .LDF. The LOG files hold all the LOG information that is used to
recover the database. There must be at least one log file for each database, although there can
be more than one. Once the transactions are Committed the data will be moved into MDF.

SECONDARY FILE:

The Extension name of this file is .NDF. The secondary data files make up all the data files other
than the primary data file. All the databases must not have secondary files. We can create so
many secondary files for a Data File.
ROLES AND RESPONSIBILITIES OF DBA

The primary role of Data Base Administrator is to administer, develop, maintain and implement
the policies and procedures necessary to ensure the security and integrity of the corporate
database.

 Must be having knowledge on RDBMS.

 Must have knowledge of installing SQL Server into the machine

 Establish and maintain backup and recovery policies and procedures. Establishing the
needs of users and monitoring user access and security.

 Considering both ‘back end’ organization of data and ‘front end’ accessibility for end
users.

 Further refining the ‘physical design’ to meet system storage requirements. Installing
and testing new versions of the database management system (DBMS).

 Writing database documentation, including data standards, procedures and definitions

for the data dictionary (‘metadata’).

 Controlling access permissions and privileges.

 Developing, managing and testing backup and recovery plans.

 Ensuring that storage, archiving, backup and recovery procedures are functioning
correctly.

 Application tuning and performance monitoring.

 Working closely with IT project managers, database programmers and web developers.

 Communicating regularly with technical, applications and operational staff to ensure

database integrity and security.

 Installing new applications.

 LOGINS

A “LOGIN” is used for user Authentication. LOGINS are given to users by the security identifier. A
LOGIN is required for access to the SQL Server. We can give permissions to the users by creating
LOGIN to him so that the user can able to access to certain database in that Server.

This can be done in two ways. They are

1. Server Authentication method.

2. Windows Authentication method

Server Authentication method:

This can be again done in two ways, they are

 Using Query optimization method

 Using object explorer

Using Query optimization method:

User can create logins directly by using the following syntax in query editor

SYNTAX:

CREATE LOGIN [login name] WITH PASSWORD = '<password>‘;

EX:

Create LOGIN [Chaitu] WITH PASSWORD =’chaitu’;

Using Object Explorer:

The following is the procedure while doing in object explorer

1. In SQL Server Management Studio, open Object Explorer and expand the folder where
we have to create new Login

2. Right-click the Security folder, point to New, and then click on Login.
 3. On the General page, enter a name for the new login in the Login name box.

4. Select SQL Server Authentication (the windows application icon will be disabled).

5. Enter a password for the login.

6. Select the password policy options that should be applied to the new login. In general,
enforcing password policy is the more secure option.

7. Click OK.

Windows Authentication method

This can be again done in two ways, they are

 Using Query optimization method

 Using object explorer

Using Query optimization method:

User can create logins directly by using the following syntax in query editor

SYNTAX:

CREATE LOGIN <name of Windows User> FROM WINDOWS; GO

EX:

CREATE LOGIN [msat\ct88493] FROM WINDOWS; GO

Using Object Explorer:

The following is the procedure while doing in object explorer

1. In SQL Server Management Studio, open Object Explorer and expand the folder of the server
instance in which to create the new login.

2. Right-click the Security folder, point to New, and then click on Login.

3. On the General page, enter the name of a Windows user in the Login name box.

4. Select Windows Authentication (server authentication icon will be disabled).

5. Click OK.

Windows Authentication is the best method to create Logins.

BACKUPS

A Backup is nothing but taking the copy of a data that is used to restore and recover data after a
system failure.

There are four types of Backups in SQL Server, they are

1. Full Backup

2. Differential Backup

3. Transaction Log Backup

4. File Group Backup

Full Backup:

A Full Backup is nothing but taking the Backup of entire Data. A Full Backup makes a complete
backup of your database. You will almost always need to start your backup strategy with a full
backup of your database. The extension name of this Backup is .bak

In our work (company) a Full Backup is done for once in a day.

Syntax:

Backup Database <Database name> to disk=’c: \backups\.bak’;

Example:

Backup Database Chaitu to disc=’c: \backups\..\.bak’;

Differential Backup:

A Differential Backup is the Backup that stores all the changes that have occurred to the
database since the last full Backup. If we want a backup of few hours back data then it is better
to go for Differential backup. The extension name of this Backup is .diff

In our work (company) a Differential Backup is done for every four hours.
Syntax:

Backup Database <Database name> to disk=’c: \backups\.diff’ with Differential;

Example:

Backup Database Chaitu to disc=’c: \backups\...\.diff’ with Differential;

Transaction Log Backup:

A Transaction Log Backup is the Backup that creates a copy of all changes that are made to the
database that are currently stored in the Transaction Log. The extension name of this Backup is
.trn

In our work (company) a Transaction Backup is done for every half hour.

Syntax:

Backup Log <Database name> to disk=’c: \backups\.trn’;

Example:

Backup Log Chaitu to disc=’c: \backups\...trn’;

File Group Backup:

A File Group Backup is the Backup used to take Backup of certain files. Using File Group Backup
we can increase the speed of recovery by letting you restore only damaged files, without
restoring rest of the database.

In our work (company) we are not using File Group Backup.

Backup using object explorer in SQL Server

1. After connecting to the Microsoft SQL Server Database Engine, in Object Explorer, click
the server name to expand the server tree.

2. Right-click the database, go to Tasks, and then click on Back Up. The Backup Database
dialog box appears.

3. In the Database list box, verify the database name. You can optionally select a different
database from the list.

4. Select the type of Backup (Full, Differential, Transaction Log, File group).

5. We can accept the default backup set name suggested in the Name text box, or enter a
different name for the backup set.

6. Optionally, in the Description text box, enter a description of the backup set.

7. Choose the type of backup destination by clicking Disk. The selected paths are displayed
in the Backup to list box.

8. After the Backup has been taken check in the path whether the data has been copied or
not.

Follow the above steps to take the Backup using the object explorer.
RECOVERY MODELS

Restoring the data from system files (Log File) is called as “Recovery”. SQL Server
provides three types of Recovery models that allow you to specify the way the SQL server
manages Log Files and prepare for the data recovery in case of disaster.

There are 3 different recovery models.

1. Simple Recovery Model

2. Full Recovery Model
3. Bulk Logged Recovery Model

Simple Recovery Model:

In Simple Recovery model, SQL Server Maintains only a minimal amount of information in the
Transaction log. It can support Full and Differential. There may be chance of data Loss. It does
not support Transaction Log, because once the transaction has been committed the data will be
moved to MDF and it kills the data in LDF, so there is no Transaction Log in this model. The
criticality of this model is less.

Syntax: Alter database <Database name> set recovery Simple;

Full Recovery Model

In Full Recovery model the entire data base can be recovered using Backups. No data is lost and
it supports Full, Differential and Transaction Log Backups. It can be recovered at any point of
time. If the log File is damaged, the most recent log backup must be done again. It requires the
Log Backups. It can take lot of disk space

Syntax: Alter database <Database name> set recovery Full;

Bulk Logged Recovery Model

The Bulk Log Recovery model is same as Full Recovery Model the difference is that are rows that
are inserted during bulk operations are not logged, full restore is still possible because the
extents that have been changed are tracked. Point in time recovery is not done.

Syntax: Alter database <Database name> set recovery Bulk Loged;

Using Object Explorer:

The following is the procedure while doing in object explorer

1. Click on the database in Object Explorer and select "properties".

2. After right click on properties database properties a window appears.

3. Click on options

4. By clicking on options a dialogue box will appears there we can find recovery model

5. Select which model you want (Full, Simple, Bulk)

6. Click on ok

Mostly we will use Full Recovery Model to recover the data.

RESTORE
A ‘Restore’ is nothing but restoring the data from Backup files. We can restore the Data in
Database by taking the Backup of that Data. For this we have to know how to take Backups and
Recovery Models.

The Restoration can be done in Two Ways. They are

1. Query Optimization Method.

2. Object Explorer Method.

Query Optimization Method:

The following is the procedure for restoring the data

Syntax:

Restore Database <database name> from disk=’c: \......\.bak’;

Example:

Restore Database chaitu from disk=’c: \.....\.bak’;

NOTE:

If you want to move the Database to 2 files i.e. Master Data File (mdf) and Log Data File (.Ldf)
then the following is the process.

Syntax:

Restore Database <database name> from disk=’c: \......\.bak’

With

Move ‘Name_data To ‘c: \..._data.mdf’,

Move ‘Name_data_log To ‘c: \..._data_log.ldf’;

Example:

Restore Database <database name> from disk=’c: \......\.bak’

With
Move ‘chaitu_data To ‘c: \..._data.mdf’,

Move ‘chaitu_data_log To ‘c: \..._data_log.ldf’;

Object Explorer Method:

The following are the steps that we have to follow to Restore Data

1. In the Object Explorer click on server and click on Database.

2. Right click on the Database and Select Restore Database.

3. A Restore Database dialogue box appears.

4. Type the Database wants to Restore in the “To Database Field “and Choose the
“From Device Radio Button” to choose where you want your Backup files to store.

5. Your File now appears in the “Select Backups To Restore Text Box”.

6. Place a Check in the Check Box to Continue and click ok.

7. The “Restore Database Dialog box” will appear.

8. Go to “Options” and Select the Path where to restore the data (we can take both master
file (mdf), log file (Ldf)).

9. Click on OK.

10. Now refresh the database the database which you have restored will appear in the
Database.

PROFILERS
DESCRIPTION:

PROFILERS are used to trace the data. In our environment profilers are used for Auditing
purpose, by using profilers we can know who has been using the specific servers. By using the
profilers we can Trace the data (insertion, updations, procedurechanges…ETC)
HOW TO CREATE PROFILE
Go to server -> All Programs ->MICROSOFT SQL SRVER ->Performance Tools->SQL Server Profiler.

Click on <Connect> Button (connect to the server)

Go To Files ->Templates->New Templates

New properties Screen will come. In that there are two Tabs

* GENERAL

* EVENT SELECTION

In general tab select Server type (2005, 2000...ETC) after that give the Template name

If u wants to give for an existing one then check the base new template on the existing one.

(WHAT IS EVENT CLASS?

Event class is a type of event that can be used to trace the data)

After that go to Events Selection and select the type of the Event
(TSQL, PROCEDURES<DATABASES…ETC) and select the type of sub events and check the boxes
whatever you want to trace and click on save button.

HOW TO TRACE
Go to Microsoft SQL Server->Go to file->New Trace ->a TRACE PROPERTY dialogue box will
appears. In that there are two Tabs

* GENERAL

* EVENT SELECTION
In General Tab give the Trace name and Trace provider name (default Trace Provider name will
appear) select the type of the provider and select the template you want to trace and check the
save to file and browse where you want to put the trace data.

After that Go to event selection tab

 Click on applications then the EDIT FILTER dialogue box will appear.

After that select the type of the database or anything you want in the SQL
In the above way we have to select the table (or) procedure...Etc you want .After that select the
query type (insert, update, delete, drop….)

After selecting the text data (table names) click on OK button

Click on save button, the trace will start and if we want we can pause or stop the trace

The above is the process of creating a profilers and creating a Trace

DRAWBACKS:

The main Drawback of using Traces is it will save all the data, memory will be wasted

BLOCKING AND DEAD LOCK

BLOCKING:

A Blocking is nothing but, when one connection to SQL Server using the data (it will be locked
by the user) and a second connection to SQL Server requires the same data to be used then the
second one will be in waiting stage because the first connection will lock the data .Until the lock
has been unlocked (first one released the data) the second connection cannot use that data this
is called as Blocking.

By default, a connection will wait an unlimited amount of time for the blocking lock to go away.

DEAD LOCK:
When two or more users waiting for a single resource to use in a same time, then it is called as
Dead lock.

(OR)

Deadlock occurs when two or more SQL Server processes have locks on separate database
objects and each process is trying to acquire a lock on an object that the other processes have
previously locked.

For example take two connections say it as A1, A2 and take two tables as T1, T2.

Now A1 is using T1 (A1 locks T1) and A2 is using T2 (A2 locks T2), now A1 wants to use T2 but in
the same time A2 want to use T1.

Now the problem arises the below diagram shows the process of Dead Lock
Event 1: A1 places a lock on T1 inside its transaction and continues to execute other statements

Event 2: A2 places a lock on T2 inside its transaction and continues to execute other statements

Event 3: A1 attempts to place a lock on T2 (Needs to access T2 before it can finish the
transaction) but has to wait for A2 to release its lock

At this point, a block is created since A2 is blocking A1

Event 4: While A1 is waiting, A2 attempts to place a lock on T1 (Needs to access T1 before it can
finish its own transaction)

The above steps clearly show the problem.

* SQL Server automatically resolves the DeadLock by choosing one of the connections as a
deadlock victim and killing it.
SHRINKING

The Main intention of using Shrinking is to reduce the LOG Space. Shrinking data files recovers
space by moving pages of data from the end of the file to unoccupied space closer to the front
of the file.

If there is no enough space in the disk then the performance of the server will be
reduced. So we have to shrink the log files to increase the performance.

To check the log space the following is the script we have to execute

dbcc sqlperf(logspace)

By executing the above Script we can know the log space of the databases.

Shrinking can be done in two ways

1. Using Query Optimization Method.

2. Using Object Explorer.

Using Query Optimization Method:

The following steps are to be followed to shrink the database.

Step1: First of all we have to select the database for which we want to Shrink (execute the script
dbcc sqlperf (logspace)) after that execute the below script.

Syntax: Backup log <dbname> with truncate_only

Step2: Execute the below query with specified logfile name.

Dbcc shrinkfile(logfile name)

The above two steps we have to do to shrink the database using Query Optimization method.

NOTE: To show the name of the database execute the below query (procedure)

Query: Sp_helpdb
Using Object Explorer:

The following are the steps we have to do for shrinking the database or file

 Go to object explorer in Microsoft SQL management Studio

 Connect to the specific Instance
 Open the database
 Right click on the database go to tasks->go to shrink
 Select the type(for database you want to Shrink)
 The below screen shot will appears

 It will show the current allocated space and available free space
 Check the box and select the percentage you want to shrink
 Click OK

It will shrink the database to the percentage you are expecting

SHRINKING THE FILES IN A DATABASE

If we want to shrink the files in the database

 Open the database

 Right click on the database GO to tasks->GO to shrink
 Select the type(file type)
  The below dialogue box will appears

Now we have to select the type of file with the file group and file name with the location

Currently allocated space and free space will be showed

 Now in the Shrink action check the radio button reorganize pages and shrink the file to
your limit(minimum 2MB)
 If we want to migrate also we can migrate the data with selecting the third radio button
in the above screen shot.
 Click on OK
The above is the process we have to follow for Object explorer

INDEXING
INDEXING:
 Avoiding the Table Scan is called Indexing. Indexes are created on columns in tables or views.
The index provides a fast way to look up data based on the values within those columns. An index is
made up of a set of pages (index nodes) that are organized in a B-tree structure. If we cannot create
Indexes it will check line by line (every row in the table) so that more time will be wasted.

The Main intention of using INDEXES is for performance tuning. Indexes are done on tables. An index
is made up of a set of pages (index nodes) that are organized in a B-tree structure. This structure is
hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom.

The following is the syntax for Indexes

SYNTAX:

Create index (index name) on (table name) (columns)

EXAMPLE: create index chaitanya on aa2 (sno,nm,nm1);

There are two types of indexes. They are as follows

 Clustered Indexes.
 Non Clustered Indexes.

CLUSTERED INDEX:
Clustered Index is physically varying and its point to actual data. A clustered index
stores the actual data rows at the leaf level of the index. An important characteristic of the
clustered index is that the indexed values are sorted in either ascending or descending order.
As a result, there can be only one clustered index on a table or view.
A table that has a clustered index is referred to as a Clustered table. A table that has
no clustered index is referred to as a Heap.

NON CLUSTERD INDEX:

Non Clustered Index is logically varying and its point to address of the data. In Non
Clustered Index we are having maximum of 256 Non Clustered Indexes. With a Non clustered
index, the physical order of the data rows is independent of their indexed order. The default
index is Non Clustered index only .We has to create Clustered Index before Creating Non
Clustered Index.

The below proc will give the description of the index.

Syntax: SP_HELPINDEX ‘<table name>’

Example: SP_HELPINDEX'aa2';
NOTE: If Scan density is <=75 we have to ReArrange the INDEXES.

Re arranging indexes classified into 3 types

REINDEX
REBUILD
REORGANIZATION

REINDEX AND REBULID will give the same result.

REINDEX:
It will Recreate the INDEX means overwrite the existing INDEX.

SYNTAX: DBCC DBREINDEX (‘First table name’, ‘Second INDEX Name’, ‘FILL FACT’)

FILLFACTOR: Fill Factor is a value to fill the memory of the indexes.

REBUILD:
It will drop the existing index and recreate the new INDEX.

REORGANIZE:
Re organizing is nothing but to reorganizing the leaf Nodes. It will not write in the LDF.

FRAGMENTATION
FRAGMENTATION:
A Fragmentation is nothing but dividing the memory into separate parts. Fragmentation exists
when indexes have pages in which the logical ordering, based on the key value, does not match
the physical ordering inside the data file.
 There are two types of Fragmentations

1. INTERNAL FRAGMENTATION.
2. EXTERNAL FRAGMENTATION.

INTERNAL FRAGMENTATION:
Continue memory wastage is called as internal Fragmentation, means there will continue
memory wastage in this. For example consider an array like a[0],a[1],……………..,a[n] but after a[1]
there is no data upto a[5]and in a[6]there is a data then it is called as Internal
Fragmentation(continue memory wastage will be there).

EXTERNAL FRAGMENTATION:
Gaps of Array wastage is called as external fragmentation, means there will be a gap in
arrays. . For example consider an array in that like a[0],a[1],……………..,a[n] but after a[1]there is a
gap in a[2],after that there is a value in a[3] after that there is a gap in a[4] and there is data in
a[5] this is called as External Fragmentation(there is a gap in memory waste).

FRAGMENTATION LEVELS:

If Fragmentation level is 0-10% then it is good

If Fragmentation level is 10-30% then it is to be Re Organized
If Fragmentation level is > 30% we have to Re Build the Index.

How to Reduce the Time of Execution:

Reduce the Inner Joins

Reducing the count *(instead of that we have to use count of column)
Filtering (by using the where clauses)

LINKED SERVERS
Linked Servers allows you to connect to other database instances on the same server (or) on
another machine or remote servers. After setting up the Linked Servers we can easily access the
other server tables, procedures etc. linked servers are configured to enable the Database Engine
to execute a Transact-SQL statement that includes tables in another instance of SQL Server.

For example: Take two servers, Server1 and Server2. In server1 test_db database is there and in
server2 ABC database is there .Now in ABC database XYZ table want to be accessed by server1
so in server2 a login has to be created and in server1 a linked server has to be created to access
the table of server 2.

The following is the process for creating linked server

1. Click Start, click All Programs, click Microsoft SQL Server, and then click SQL Server
Management Studio.
2. Go to server objects->linked servers->new linked servers the below dialogue box will
appeared.

3. In the linked server name give the linked server name. if destination server name is
equal to linked server name then select sql server(radio button enable) otherwise select
other data source.
4. If you select other data source. Give the provider name ,product name and data source
name
 5. After that click on security. The below screen shot will appears.

6. Click on ADD button. And select the database name (local login) and give the remote user
login and password.
 6. After that select the radio button be made using the security context and give the
password of the remote user.

Advantages of using Linked servers:

1. Remote server access can be done.

2. The ability to issue distributed queries, updates, commands, and transactions on
heterogeneous data sources across the enterprise.

3. The ability to address diverse data sources similarly.

The following syntax will give the linked servers that are present in the server.

Syntax: sp_linkedservers
 IMPORT & EXPORT

Importing And Exporting is used to import the Data from one to other, means to copy the data from
database to Excel, or flat file (notepad), one Table to Another Table. By using the Import and Export
wizard we can dump the Data from one source to Destination depending on our requirement.

Importing Table to table by using Script:

select * into [chaitu test db]..holiday1 from Bharath..holiday1

For different servers (or) instances

Select * into [chaitu test db]..Holiday1 from htsapps065.Bharath.dbo.holiday1

Using GUI:

Goto source database->tasks->import data

The Below Screen Shot will be appeared.

Select the Source

Select the Destination as shown in below

Select source table and destination Table Name.

After that click on next the data will be imported

sp_attach_single_file_db (stored procedure) is the one used in attach to add a database without Ldf file
REPLICATION
A Replication is a technology used in SQL Server for copying and distributing the
data from one Database to another Database and synchronizing between the Databases to
maintain consistency.

Whenever you will create a replication the data will be copied from one Database
(Source) to another Database (destination) and any changes done to source will directly reflect
to destination depending on the type of the Replication. Using Replication, we can distribute the
data to different locations over a wide area networks.

There are 3 components in Replication

1. Publisher

2. Subscriber

3. Distributor

Publisher:

It is the source database where we have to select the data (Table, Database……) means the data
which you want to take a copy. We have to create the Publisher in source server.

Subscriber:

It is the destination database where we want copy of source data. We have to create the Subscriber
in destination server.

Distributor:

It is a mediator between the publisher (Source) and subscriber (Destination).we can create this
distributor in any one of the server (either in source or destination)

TYPES OF REPLICATIONS:
There are four types of replications in Sql server. They are as follows

1. Snapshot Replication
2. Transactional Replication
3. Merge Replication
4. Transactional with Updatable
Snapshot Replication:

In Snapshot replication we can take the copy of the data as Snapshot. The publisher simply
takes a snapshot (copy) of the entire replicated database and shares it with the subscribers. The
modifications which we done in source (Publisher) will not be reflected in the destination Server
(Subscriber) .we can use this when there is no chance of data modifications in source server.
Unless and until the Snapshot Agent runs again then only it will be modified in destination
server (Subscriber).

Transactional Replication:

In Transactional Replication we can take the copy of the data .The difference is when ever any
transactions (modifications) are made in source (Publisher) it will directly reflect in the
destination (Subscriber) without any manual interface.

Merge Replication:

By using Merge Replication we can make any changes in any of the server either in Source or
Destination, means if we change the data in destination it will reflect in the source and if we make
changes in Source it will reflect in Destination also without any manual interface. It is the main
advantage of using Replications in SQL server.

AGENTS USED IN REPLICATION

AGENTS: Agents are used to transfer the data in Replications. It is a connector for connecting the
two servers. There are 5 types of Replication Agents

1. Snapshot Agent
2. Distributor Agent
3. Log Reader Agent
4. Merge Agent
5. Queue Reader Agent

Snapshot Agent:

Snapshot Agent is used in all types of Replication. This agent takes the snapshot of all the database
schema and data that needs to be replicated from publisher to the subscriber. All the changes that
occur in the publisher will be captured and then later sent to the subscriber when the snapshot
agent runs again. Snapshot agent and distribution agents are used in the Snapshot replication.
Snapshot.exe executes in the distributor server.
Distributor Agent:

Distributor Agent is used in Snapshot and Transactional Replication.Distrib.exe is the executable for
this agent. Distribution agent serves two purposes: Apply snapshots at the initial stage of replication
initialization process and to send transactions from distribution database to the subscriber.

Log Reader Agent:

Log reader Agent is used only in Transactional Replication. All the committed transactions from the
transaction log of publisher database will be extracted. Once the data is being extracted from the
transaction log of the publisher database, log reader agent makes sure that each transaction is
written into the distribution database of the distributor server, and the data should be written in
the same sequence as that of the transaction sequence made in the publisher database.

Merge Agent:

Merge agent is used only with merge replication. The main function of merge agent in merge
replication method is to apply the snapshot that is generated when the subscriber is initialized at
first during the replication process. Replmerg.exe is an executable for this agent and this agent runs
behind the scene to finish the replication process successfully.

Queue Reader Agent:

The main function of queue reader agent is to transfer or send the queue from the subscriber to
the publisher. Qrdrsvc.exe is an executable for this agent.