Thursday, January 28, 2010

Replication in SQL Server


Replication

- process of distributing data from one database to another on the same server or servers connected through LAN or the internet.
- synchronize data between databases that may be at remote location.
- maintain copies of database at more than one locations.
- It can be used for load balancing of server by maintaining area specific data independently and replicate later to have single version of data

Types of Replication

1. Snapshot Replication
2. Merge Replication
3. Transactional Replication

Snapshot Replication
- Snapshot of entire data is copied from publisher to the subscriber's database on regular
interval.
- This kind of replication is used for replicating data that doesn’t change frequently.
- It is used where the source can manage with out of date data.
- This replication is considered when amount of data to be replicated is small.

Merge Replication
- This allows both publisher and subscriber to work independently, online or offline and merges
the changes later.
- Here changes are track on both publisher and subscriber and then merged.

Transactional Replication
- This kind is used when changes are frequent.
- The replication agent monitors the changes at the publisher and distributes the changes to the
subscribers.
- Its required where up to date data is required.

Cursors in SQL Server


Cursors

-It is a special programming construct that allows data to be manipulated on a row-by-row basis.
-They point to a certain location within a record set and allow the operator to move forward (and sometimes backward, depending upon the cursor type) through the results one record at a time.
-A cursor is used to access the result set stored in the memory on execution of a query.


Steps to use Transact-SQL Cursor.

Declare the cursor,
Open the cursor,
Fetch record row by row,
Close cursor,
Deallocate cursor.


Example of a cursor

Declare @EmpId int
Declare curEmp CURSOR READ_ONLY FOR SELECT EmpId FROM Employee
Open curEmp
Fetch next from curEmp into @EmpId
While @@FETCH_STATUS = 0
Begin
Print @EmpId
Fetch next from curEmp into @EmpId
End
Close curEmp
Deallocate curEmp


Different types of cursors

1. DYNAMIC: It reflects changes happened on the table while scrolling through the row.

2. STATIC: It works on snapshot of record set and disconnects from the server. This kind
doesn’t reflects changes happened on the table while scrolling through the row.

3.KEYSET: In this kind, new record is not reflected, but data modification can be seen.
Define the cursor lock types.

SQL Server supports three types of cursor namely Transact-SQL server cursor, API server cursor, and client cursor.


Disadvantages/limitation of the cursor
- consume network resources.
- Close cursor when it is not required.
- You shouldn’t forget to deallocate cursor after closing it.
- Cursor requires a network roundtrip each time it fetches a record.

Avoid cursors
- Using the SQL while loop
- User defined functions
- Using CTE(Common Table Expression) Using WITH Clause.
-Temporary table
- Table variable
- You should fetch least number of records.
- You should use FORWARD ONLY option when there is no need to update rows.