Rakesh Patil

SQL Interview Questions on SQL Server replication – Part 1

October 30, 2018 by

The following is the first part of SQL interview questions series, covering the topic of SQL Server Replication

SQL Interview Questions 1: What are the new features added in SQL Server 2008 Replication?

Enhanced Peer-To-Peer Transactional Replication:

  • The ability to detect conflicts during synchronization
  • The ability to add nodes to a replication topology without quisling/disabling the topology

Improved Replication Monitor:

  • On Monitor grid now, we can apply “Selection,” “Sort” and “Filter” on grid columns
  • Common Jobs Tab on Publisher node has been renamed Agents and it showcase all agents and jobs associated with the selected Publisher

SQL Interview Questions 2: What are the new features added in SQL Server 2008 R2 Replication?

In 2008 R2 there are no changes in Replication features.

SQL Server Interview Questions 3: What are the new features added in SQL Server 2012 Replication?

  • Replication supports ALWAYSON Availability Groups
  • There are 4 new system stored procedures introduced for replication support on Availability groups
  • Replication supports extended events
  • Replication supports up to 15,000 partitions for tables and indexes

SQL Server Interview Questions 4: What are the new features added in SQL Server 2016 Replication?

  • Replication supports memory-optimized tables
  • Replication is now supported for Azure SQL Database

SQL Interview Questions 5: Can you define replication?

Replication is one of the High Availability technologies which is used to maintain the second copy of database objects called Articles. Through replication we can replicate single database objects like tables, stored procedures, etc., which we cannot do individually in other High Availability technologies.

SQL Server Interview Questions 6: What are the areas where the replication can be useful?

Load balancing: Replication is used to distribute data to other servers and reduces the load on server by distributing query execution

SQL Interview Questions 7: What are the various components involved in replication?

  • Publisher
    Publisher is database source from where data is replicated
  • Distributor
    Distributor is used as a bridge between Publisher and Subscriber. It is used to store data from Publisher before applying it to Subscriber. It stores snapshot from snapshot replication and transactional log from transactional replication, after which Distributor agent of Distributor database applies these to Subscriber
  • Local Distributor
    Publisher and Distributor hosted on the same server
  • Remote Distributor
    Publisher and Distributor hosted on two different servers
  • Subscribers
    Subscriber is a database instance which acts as a destination for the replication process. It receives replicated data from Publisher
  • Article
    Database objects like Tables and stored procedures to be replicated during the replication process are called Articles

SQL Interview Questions 8: What are the different types of replication implementation?

There are mainly 3 types of methodologies available in replication

  • Snapshot replication
  • Merge replication
  • Transactional repliation

SQL Server Interview Questions 9: Can you brief about how Snapshot Replication works?

The Publisher takes a snapshot of the database. Snapshot is put in the snapshot folder in the Distribution database. The Distribution database applies the snapshot to the Subscriber.

It is used for databases that rarely change.

SQL Interview Questions 10: Can you explain how Transactional Replication works?

  • Snapshot Agent
    Snapshot agent is used to take snapshot of data files and put it into snapshot folder in the Distribution database. It is used mainly in all replication type.
  • Log Reader Agent
    The Log Reader Agent is used in transactional replication. It transfers committed transactions from transaction log of the Publisher database to the Distribution database.
  • Distribution Agent
    It transfers transactions log taken from the log agent and snapshots taken from the snapshot agent to the Subscriber.

SQL Interview Questions 11: Can you explain how Merge Replication works?

Merge replication is usually used whenever there is a slow or intermittent network connection between the Publisher and Subscriber. It enables sites to work autonomously and to synchronize the changes to the data when they are next online.

It needs a snapshot to initialize the replication, after which subsequent changes are tracked with triggers. One side effect of merge replication is the possibility of conflicts when offline changes are synchronized in. Merge replication automatically resolves these issues in the Merge agent using the conflict-resolver model chosen when the publication was created.

If you don’t want to use automatic conflict resolution, you can configure the publication for interactive conflict resolution. When the publication is configured for interactive conflict resolution, each conflict must be resolved manually. You can do this using the Interactive Resolver user interface.

SQL Interview Questions 12: What is Transactional Replication with Updatable Subscriptions?

Transactional replication supports updates at Subscribers through updatable subscriptions and peer-to-peer replication. The following are the two types of updatable subscriptions:

  • Immediate updating: This is an online process. Data is updated at Subscriber, then transferred to Publisher and then distributed to another Subscriber
  • Queued updating: This is an offline process. Data is updated at Subscriber, then transferred to Publisher and then distributed to another Subscriber. Changes are stored in queue and when network is online, they are applied to Publisher

SQL Server Interview Questions 13: In which replication methodology we can update at Subscribers?

  • Merge replication
  • Peer-to-peer transactional replication
  • Transactional replication with updating subscriptions

SQL Interview Questions 14: What are different Replication models?

  • Single Publisher, One or More Subscribers

    A Single Publisher model is perhaps the simplest topology to use with a single Publishing database that has one or more subscription databases. You might use this topology where you need to keep a hot standby system or distribute data from a central office to multiple field offices. Figure 15.1 shows the basic structure of this topology with the Distributor on the Publishing server.

  • Multiple Publishers, Single Subscriber

    A Point of Service (POS) application is a good example of a Multiple Publisher model. A POS application has multiple Publishers, but only one subscriber. In the POS, it is often necessary to send data from many POS terminals in a store to a central system either in the store or at the head office where the individual transactions can be consolidated. The replication topology to use for this is a Multiple Publisher, Single Subscriber model.

  • Multiple Publishers Also Subscribing

    A Customer Resource Management (CRM) application is a good example of the Multiple Publishers also Subscribing model. In the CRM application, it might be necessary to have an address book containing all contacts that is updated locally yet synchronized across all sites. One way to do this is to have each branch office publish the updates made at that office and subscribe to the updates made by all other branch offices.

  • Updating Subscriber

    The CRM application can also be implemented when using an Updating Subscriber model. In this topology, the master copy of the contacts is held at a central location. This would be published to all branch offices. Any changes at the branch offices are then updated back to the Publisher using the Updating Subscriber feature built into replication.

SQL Interview Questions 15: Your customer provided a business requirement and asked you to configure the replication topology. How do you suggest the correct replication topology?

Before suggesting the type of replication first we need to understand these replication types and their usage.

    Snapshot:
  • Data does not change frequently
  • It is used to replicate small amount of data
    Transactional:
  • It is used to transfer data to Subscriber as it arrives at Publisher
  • It is used if you have big volume of data to be replicated which may have DML operations
    Merge:
  • Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers
  • Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers
  • Each Subscriber requires a different partition of data. Conflicts might occur and, when they do, you need the ability to detect and resolve them.

SQL Interview Questions 16: What recovery model is required on a replicated database?

Replication can use any of the recovery models: simple, bulk-logged, or full.

SQL Server Interview Questions 17: How do I manage constraints on published tables?

Transactional and Merge replication need Primary Key Constraint on each table which needs to publish. By default, primary key constraints, indexes, and check constraints are replicated to Subscribers.

SQL Server Interview Questions 18: How do I manage identity columns?

Replication sets automatic identity management, it does not increment the identity column value in the Subscriber table.

SQL Server Interview Questions 19: What are the ports required in replication?

Replication uses SQL Server port 1433

SQL Server Interview Questions 20: Does replication affect the size of the transaction log?

Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications are transferred to the Distribution database.

SQL Interview Questions 21: What is “sync with backup” in transactional Replication?

If Sync with Backup option is enabled, Transaction log will be truncated only once they are backed up. It maintains relationship between Publication and Distribution database.

SQL Server Interview Questions 22: How to manually remove replication? What is the sequence?

We can remove replication manually by System Stored Proc. We need drop all Subscriptions, Publications and Distributors configured for replication.

SQL Server Interview Questions 23: Can multiple publications use the same Distribution database?

Yes. We can used multiple publications on same Distribution database.

SQL Server Interview Questions 24: Does replication encrypt data?

Replication does not encrypt data.

SQL Server Interview Questions 25: How can we replicate data over the Internet?

We can use The Web synchronization for merge replication

SQL Server Interview Questions 26: Does replication resume if a connection is dropped

Yes. Replication processing resumes at the point at which it left off if the connection is dropped.

SQL Interview Questions 27: Does replication work over low bandwidth connections? Does it use compression?

Replication can work on low bandwidth connections. Connections which are on TCP/IP use compression provided by the protocol but does not provide additional compression. The connections which are on HTTPS, like web synchronization, use additional compression and XML files are used to replicate changes.

SQL Server Interview Questions 28: Are logins and passwords replicated automatically?

No. You could create a SSIS package to transfer logins and passwords from a Publisher to one or more Subscribers.

SQL Server Interview Questions 29: Why can’t I run TRUNCATE TABLE on a published table?

We cannot track changes by TRUNCATE TABLE as it’s non-logged operation. Transactional replication tracks by Transactional log. Merge tracks by triggers on published tables.

SQL Server Interview Questions 30: What is the effect of running a bulk insert command on a replicated database?

For transactional replication, bulk inserts are tracked and replicated like other inserts.

SQL Interview Questions 31: How can we rebuild indexes in replicated databases?

We can rebuild indexes as we do on normal databases.

SQL Server Interview Questions 32: How can we add or change indexes on publication and Subscription databases?

If we make changes on indexes on publication, we need to make change on respective Subscriber.

SQL Server Interview Questions 33: How do I drop a table that is being replicated?

First, we need to drop the Article from the publication using sp_droparticle, sp_dropmergearticle. We cannot drop Articles from snapshot or transactional publications after Subscriptions is configured. We need to drop the Subscriptions first.

SQL Server Interview Questions 34: How can we add or drop columns on a published table?

First, we need to execute ALTER TABLE … DROP COLUMN at the Publisher, and the statement is replicated to Subscribers and then executed to drop the column.

SQL Interview Questions 35: In replication setup, how can we confirm that Subscriber is in sync with Publisher without using GUI?

We need to check if all replication agents are running.

On Subscriber, connect to Subscriber database and run query.

Example:

0x000004570001E3FE000100000000

Transcation_Timestamp represents the last xact_seqno/transaction got loaded in the Subscription database. Ex.xact_seqno: 0x000004570001E3FE0001 (Excluded all zeros at the end)

Connect to Distribution database and run below query

If there are no rows returned from the below query, it means the replication is in sync.

SQL Server Interview Questions 36: Can we configure both log shipping and replication on the same database?

If Log shipping is failover, then Replication will not work, Transaction will not be propagated to Subscriber. Replication will continue once log shipping fails back to primary.

SQL Interview Questions 37: Can you explain how the snapshot agent works?

The Snapshot Agent performs the following steps:

  • Locking
    Snapshot agent is used in snapshot and Transactional replication to lock
    • .SCH file: Writes a copy of the table schema for each Article to a .sch file
    • Additional Script Files: It generates additional script files if database objects are published, such as indexes, constraints, stored procedures, views, user-defined functions, etc.
    • BCP Files: Copies the data from the published table at the Publisher and writes the data to the snapshot folder. The snapshot is generated as a set of bulk copy program (BCP) files.
    • MSrepl_commands and MSrepl_transactions: For snapshot and transactional publications, the Snapshot Agent appends rows to these tables in the Distribution database. The entries in the MSrepl_commands table are commands indicating the location of .sch and .bcp files, any other snapshot files, and references to any pre- or post-snapshot scripts.
  • Releases any locks on published tables

During snapshot generation, you cannot make schema changes on published tables. After the snapshot files are generated, you can view them in the snapshot folder using Windows Explorer.

SQL Interview Questions 38: Can you explain how Distribution Agent works?

It is used in snapshot and Transactional replication. It makes a connection with Distributor.

There are two tables in Distribution Database, MSrepl_commands and MSrepl_transactions. Distribution agent reads these two tables. It reads snapshot files location from MSrepl_commands table then it applies schema to Subscriber.

SQL Server Interview Questions 39: Can you explain how Merge Agent works?

It makes connection with the Publisher. There is sysmergeschemachange table in the Publisher. This table contains location of snapshot files. If a new snapshot is available, the Merge Agent applies it to the Subscription database.

SQL Interview Questions 40: What is the useful system CATALOGS/tables related to Replication?

On PUBLISHER DB:

  • sysarticles
  • sysarticleupdates
  • syspublications
  • syssubscriptions
  • master..sysservers

On DISTRIBUTION DB:

  • master..sysservers
  • msarticles
  • msdistribution_agents
  • msdistribution_history
  • mslogreader_agents
  • mslogreader_history
  • mspublication_access
  • mspublications
  • mspublisher_databases
  • msrepl_backup_lsns
  • msrepl_commands
  • msrepl_errors
  • msrepl_identity_range
  • msrepl_transactions
  • mssnapshot_agents
  • mssnapshot_history
  • mssubscriber_info
  • mssubscriber_schedule
  • mssubscriptions
  • mssync_states
  • On SUBSCRIBER DB:
  • msreplication_subscriptions
  • mssubscription_agents
  • mssubscription_properties
  • DMV:
  • sys.dm_repl_articles
  • sys.dm_repl_schemas
  • sys.dm_repl_tranhash
  • sys.dm_repl_traninfo

SQL Interview Questions 41: What are the useful replication-related system stored procedures available?

  • sp_replshowcmds: It is used to execute at Publisher database. It gives transactions data which is not yet transferred to distribution
  • sp_repltrans: It is used to get transactions in Publisher database log that are committed for replication
  • sp_replcmds: It gives commands for transactions which are committed for replication
  • sp_browsereplcmds: It gives readable version of the replicated commands stored in the Distribution database

SQL Interview Questions 42: I heard that there was a problem in replication when SQL Server 2014 was released. Is that true?

There were problems, but most problems got resolved in the service pack. Some of the errors are as below:

    Error 21820 “Cannot write to the script file in the snapshot folder at the Distributor”:
  • There should be enough disk space available.
  • Snapshot Agent must have write permissions to the snapshot folder.
    Error 21331 “Unable to copy script file to the snapshot folder at the Distributor”:
  • Make sure there is enough disk space available
  • Crosscheck if the account under which the Snapshot Agent runs has permissions to write to the snapshot folder and its subdirectories

Error: 20690 – When Article is created, we should set appropriate ranges, and then rerun the Snapshot Agent.

Error: 20605 “Invalidated the existing snapshot of the publication” – To resolve this problem, we need to rerun the snapshot agent to generate a new snapshot.

Error: 14098 – We may get this error when the remote Publisher is used as Distributor. To resolve this, we should disable publishing at the Publisher before attempting to drop relationship.

Error: 14071 – We get this error when Distributor is not installed at Publisher instance.

Rakesh Patil

Rakesh Patil

Data Architect at Phoenix Innovations
I am Rakesh Patil, having 6+ years of experience in SQL database Administration, worked with Top MNCs with major Financial clients. currently working as Data Architect with Phoenix Innovations.
I have hands-on Experience in Performance tunning, Database Disaster recovery,
AlwaysOn,Replication and AWS.
Rakesh Patil
Replication, SQL interview questions

About Rakesh Patil

I am Rakesh Patil, having 6+ years of experience in SQL database Administration, worked with Top MNCs with major Financial clients. currently working as Data Architect with Phoenix Innovations. I have hands-on Experience in Performance tunning, Database Disaster recovery, AlwaysOn,Replication and AWS.

139 Views