Home » Replication

Replication

sql server DBA 999

SQL 2008 Transactional Replication and initializing from a backup file

In some environments,running Fresh snapshot will take ages to synchronise the subscribers with publishers. Even if it is done in low activity hours, it will run into business hours because of time takes to take snapshot, copy, restoring snapshot, synchronising transactions again.
There is way in which the replication can be setup by initialising using a backup in SQL Server 2008. This new feature is very useful in environments where it takes a lot of time to run a fresh Snapshot/Re-initialise the replication.
I have followed a following set of steps in setting this up and testing:

Step No Steps Followed
1 Set up the
… Read the rest
sql server DBA 999

Adding Article to Transactional Replication Publisher without Snapshot Re-initialisation

Add article to Transactional Replication without Snapshot Re-initialisation

To add a new article to a publisher efficiently without reinitializing whole replication but running the below scripts/procedure can be done without whole snapshot to be run again. This method only snapshots the new article added to the publisher.

The Steps needs to be followed in same order.

Step No Steps
1 EXEC sp_changepublication @publication = ‘ContosoPub’
@property = N’allow_anonymous’
@value = ‘false’
GO 		Change the property of replication.
2 EXEC sp_changepublication @publication = ‘ContosoPub’
@property = N’immediate_sync’
@value = ‘false’
GO 		Change the property of replication.
3 add article to publication either by script below or GUI
EXEC sp_addarticle @publication = ‘ContosoPub’
4 add article to subcriber either by script below
… Read the rest
sql server DBA 999

Administration of Transactional Replication

Administration of Transactional Replication

There are 3 types of Replication Types in SQL Server:

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

Replication of data can be configured between same server or can be different servers connected by Network. SQL Server replication uses a publisher, distributor and subscriber for replication to work
.
Publisher is the database that sends its data to another database which can be located in same or another server.

Subscriber is the database that receives data from another database which can be located in same or another server.

Distributor is the server that manages the flow of data through the replication system. This server contains the distribution database.

… Read the rest
sql server DBA 999

Transactional Replication

Transactional Replication in SQL Server 2008:Transactional replication will start with a snapshot of the publication database objects and data which is called as initial snapshot. Subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.
By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that allow updates at the Subscriber.
Transactional replication is implemented by the SQL … Read the rest