:::: MENU ::::

Posts Categorized / SQL Server Training

  • Sep 19 / 2017
  • 0
dbDigger, High Availability, Replication, SQL Server tools, SQL Server Training

SQL Server Replication refresher

During phase of training material preparation, a brief overview of replication architecture is with me. In coming lines i am going to put it in this post as a good starting point for replication learners.


Replication is a way of distributing data automatically from a source database to one or more recipient databases. It is built to sync the selected objects of a database not whole DB.  It encompasses set of objects in a database whose data is required to keep sync at subscriber databases. Replication architecture is not part of the SQL Server database engine, but a separate feature or application. It works through agents, triggers and scheduled jobs specific for this purpose. Replication can have uses in high availability systems or in a distributed system and can have different toplogies.

There are 3 main types of replication

  • Transactional Replication (one or two way)
  • Merge Replication
  • Snapshot Replication

Transactional Replication

This type of SQL Server replication is used for near real time or short delayed synchronization from publisher and subscriber. Initially a snapshot process synchronizes the subscribers with the publisher. All committed transactions on the publisher are then propagated to the subscribers in sequence. It may be one way transactional replication or two way.

In one way replication, modifications from publisher are synced on subscriber but no modification from subscriber is propagated back to publisher. While in case of peer to peer or two way replication, changes are propagated both ways and data remains sync on both sides for selected articles.

There are two options while propagating changes back from subscriber to publisher.

  • Immediate updating
  • Queued updating

Both methods can be used at the same time to maximize the availability and efficiency.

Immediate updating

In case of immediate updating changes made at the subscriber are synced to the publisher using a two-phase commit process managed by Microsoft Distributed Transaction Coordinator (MS DTC). Changes made at the subscriber must be made to both systems or are rolled back. This method is suitable for reliable, high-speed connection between the Publisher and Subscriber.

Queued updating

This method is suitable for less reliable connections. Writes updates at the subscriber to a queue. Publisher reads the queue and applies changes. This flow might result in conflicts due to latency.


Peer to Peer replication

Peer to peer replication was introduced in SQL Server 2005 and only supported in Enterprise edition of SQL Server version. It resembles in some features features with two way transactional replication. Main difference is that peer to peer replication involves more than just single publisher and associated subscriber nodes. It can span on multiple nodes and each will be kept in Sync. Also peer to peer replication is better while propagating the DDL of articles and locking mechanism.

Merge Replication

Merge replication is relatively complex type of replication. It does not use transactions but a set of defined rules used to resolve conflicts when data updated at multiple sites is synchronized. It tracks changes through triggers and metadata tables provides the means to implement part of a high-availability system. This also serves well the original purpose of serving mobile and disconnected users. It is designed for cases where the publishers are not in constant communication with the subscribers. After the initial snapshot synchronization, subsequent changes are tracked locally and the databases are merged when in contact using series of rules to resolve conflicts.


Snapshot Replication

Data changes are not tracked for snapshot replication. Each time a snapshot is applied, it completely overwrites the existing data. Snapshot replication process provides the initial synchronization for transactional and merge publications. In several cases, this initial synchronization is all that is necessary. Data is synced at defined intervals as opposed to constant transactional sync of other types.


Distribution Setup

Distribution is important setup in replication architecture. It has following components and terms.

Distribution server

Distribution server is a role where distribution setup (agent, jobs, DB) resides. Distribution server may be same as publisher or a dedicated server. For big and busy replication setups separate distributor server is recommended. We can mark a server as distributor before going to setup replication publisher and subscriber.

Distribution database

Distribution is system database for replication setup and is created automatically on server with distributor role. We can customize the DB name, files name, location and file size however. For transactional replication, the distribution database tracks changes made to publications. For merge replication it stores only synchronization history.

Distribution agent

Distribution agent is setup as a separate module and works in form of scheduled job. Distribution agent is a process that moves transactions and snapshots held in the distribution database to subscribers. Works in form of scheduled job under replication jobs category.


Replication components

There are some other terms and components that are always involved in replication setup.


Publisher is a server that makes data available for other servers, i.e. it is the server that makes data available for replication.


Publication can be a selection of tables, indexed view and stored procedures. Each publication can be replicated as a snapshot publication, a transactional publication or a merge publication. It is important to choose the publication articles on a valid base like size, relation of tables. There may be multiple publications in a single publisher.


An object included in a publication is called an article. Articles are combined on some basis to form a publication. Article can be an entire table, select rows from a table, specified columns from a table indexed view or a stored procedure. An article is a unit in publication.


Subscriber is the server that hosts the DB where data from publisher is being synced. There may be multiple subscribers against a single publisher. Subscriber may be on same server as that of publisher. Also there may be multiple subscriber databases on a single subscriber server.


Subscription relates to publication and goes to subscriber database. There may be multiple subscriptions against a single publisher and each will go to separate subscriber database. There are two types of subscriptions.

Pull Subscription

With pull subscription the subscriber asks the publisher for periodic updates. With pull subscription each subscriber can specify the best time for the update. Pull subscription is generally considered the better form of replication when a large number of subscribers are involved, or where the load on the LAN needs to be distributed.

Push Subscription

With push subscription the publisher pushes any changes made to the data out to the subscribers. Typically push subscription is used where changes need to be replicated to the subscribers shortly after the change occurs, i.e. where the replication of changes is required in near real time.

Replication Agents

As we have discussed several components of replication in above sections some agents were discussed as well. It looks suitable to list the agents so that they may give a clear picture of setup

  • Snapshot agent
  • Log Reader Agent
  • Distribution Agent
  • Merge Agent
  • Que Reader Agent

Replication effect on log file

It is important to know the relation and effects of replication on database log file. If replication falls in latency then log begins to accumulate in log file unless it is send to distributor. This may ultimately fill the log file and slow down the system in some cases. Situation is same when database is with any recovery model FULL or even SIMPLE.

Replication topologies

Topology is how the replication setup is designed and components are arranged. Following are different disgrams to give view of different toplogies.

SQL Server Replication Topology Central Distributor


SQL Server Replication Topology Central Publisher with Remote Distributor


SQL Server Replication Topology Central Publisher


SQL Server Replication Topology Central Subscriber


SQL Server Replication Topology Publishing Subscriber

  • Feb 10 / 2015
  • 0
DBA Interview questions, dbDigger, SQL Server Training, SQL Server versions and editions

SQL Server versions, editions, service packs, OS and deployment

Like other Microsoft technologies SQL Server follows the categorization of versions and editions. Updates are applied mainly through service packs and patches that are released by Microsoft for each version. Understanding of these concepts is important for DBAs as they have to make use of this information for deployment, upgrades and proposals. It is better for beginner level DBAs to explore this information prior to work on these during their jobs.

Click here to download presentation on this topic.

  • Jan 29 / 2014
  • 0
DBA Special events, DBA thoughts, dbDigger, SQL Server Community, SQL Server Training

Turn your self from DBA to a community DBA

Often i notice that many skilled and seasoned DBA lack a guide line to share their knowledge with the online DBA community. By using the several free services, tools and plugins it is now very easy to access the knowledge base of your interest and also to share your knowledge with the whole community.
I am utilizing the community aspects of DBA since 2008 and found it a very valuable feature for every IT professional. So i arranged webinar in my organization (Systems Ltd) to motivate and guide my fellow DBAs and Developers towards a path where they start utilizing the community content as well as actively add their own efforts towards the community through blogs, articles, webcasts, presentations and webinars etc.
Due to my own background my presentation was slightly inclined to SQL Server DBAs but i made sure that it should be general enough to be adoptable by all DBAs and Devs. Today the webinar went quite successful. DBAs and Devs across various Systems Ltd offices participated and showed their eagerness towards all the points mentioned in the webinar.
Click here to download the presentation.

  • Oct 03 / 2013
  • 0
Learn from the community behind Microsoft products
dbDigger, SQL Server Training

Learn from the community behind Microsoft products

We all are involved with Microsoft products and MS has shown wonderful improvement in all its products. Obviously a very dedicated teamwork is behind all this improvement. And what if you get a chance to learn from those skillful and handwork resources who are part of products success. It will be a great deal of course. So here is a link that will lead you to lot of quality and focused videos on various Microsoft products and technologies. Watch online or download the short videos.

  • Oct 01 / 2013
  • 0
dbDigger, SQL Server Training

Want to improve your SQL Server Query Tuning skills then watch these videos

If you are new to DBA career or did not spend enough time on SQL Server Query Tuning skills then here is a right stuff to start with. As licensing models are changing and adding hardware also associates the licensing costs along with the hardware cost itself so fine tuning the queries and procedures is a required skill for DBAs.

You can download about 6 hours of training videos related to SQL Server Query Tuning skills. Videos are equipped with theory and demos side by side. These training videos are prepared for SQL Sentry by

Click here to download the videos from SQL Sentry TV .

  • Nov 17 / 2012
  • 0
Backup and Restore, dbDigger, Disaster Recovery, SQL Server Training

Implementing retention span for SQL Server backup device

SQL Server uses logical devices or media to perform database backups. Backups may be append or overwrite to existing backups in these media sets or devices. It may be a serious issue when a backup in device is silently overwritten without your notice. This scenario may occur if you repeat a previous backup device name with INIT option. Consider the following example for this scenario

-- Create a backup  
 TO DISK = 'E:DBD-BackupDemo.bak'  
 Create another backup with same device name.  
 By default backup would be performed by using  
 WITH NOINIT option and would be appended  
 TO DISK = 'E:DBD-BackupDemo.bak'  
 -- Verify the two backups on backup device  

Here we are shown two backups on the device. It shows that second backup was also appended to the device. Going further we would issue another backup command by using WITH INIT. This time backup would overwrite the existing backups on the device.

 Create another backup with same device name and INIT option.  
 It would overwrite the existing backups on device.  
 TO DISK = 'E:DBD-BackupDemo.bak'  

Now verify that only last backup exists on the device and all previous backups were overwritten.

 Verify that previous backups are overwritten   
 leaving behind the last backup  


At this point we require some way to ensure that backups on the device would not be overwritten even by accidentally using the WITH INIT option. There may be two approaches to make sure the retention of backups on the device for a specified time.
  • Set retention days server wide for all databases

Following is a brief description of these two options


This option operates along with backup command for a specific device of a database. We may specify a date or days along with WITH EXPIREDATE or WITH RETAINDAYS option respectively.  Here we would create a backup device with retention span of two days

-- Create a backup with retention span 2 days  
 TO DISK = 'E:DBD-BackupDemo2.bak'  

Now create a backup in same device along with using WITH INIT option. This time error would be generated because backups in the device are retained for two days.

 Create another backup with same device name and INIT option.  
 It would overwrite the existing backups on device.  
 TO DISK = 'E:DBD-BackupDemo2.bak'  

If next backups are created in the device without any retention parameter even then device would maintain the backups with retention parameter and could not be overwritten for next two days. We may get the expiration date or retention span of files by using RESTORE HEADERONLY.
Similarly EXPIREDATE option may be used to specify an expiration date to which backups on the device would be retained. Date provided should be according to system configured date format.


We  have another option to include the fixed retention days for every backup device of databases on that very server. For using this method

  • Right click on instance
  • Go to properties
  • Go to database settings in the left panel
  • Set default media retention span in days here. By default it is zero

The retention days span here would automatically be implemented for every backup device of that server without explicit use of EXPIREDATE or RETAINDAYS.


Although WITH INIT would not be able to overwrite the retained media but WITH FORMAT may overwrite the header and content of device.

Consult us to explore the Databases. Contact us