With the introduction of SQL Server High Availability options like Availability Groups, Database Mirroring, Clustering etc. the role and the magnitude of SQL Server replication usage have reduced. One of the major reason behind this is because of the complexities involved in maintaining a replication environment. With the being said, Database replication is still relevant in today’s scenarios and have many use cases which cannot be addressed by any other technology without a lot of manual coding. In this post, I am going to talk about SQL Server Replication Interview Questions.
Even though the SQL Server Replication Services (For Example: Merge, Transactional, P2P, Snapshot) are strictly not meant for high availability, the term is used reciprocally. This can confuse you in an interview. It is highly recommended to clarify the interviewee which replication methodology the questions are being asked for. So, let’s get started.
There are different types of replication features available in SQL Server, and all of these features have a different use case.
For High Availability and Disaster Recovery:
Answer: There are three main types of Replication Services available in SQL Server.
If the data is fairly static or if it’s acceptable to have data out of sync between replication intervals. Just like Merge Replication, A subscriber does not need an always connected system, so data marked for replication can be applied the next time the subscriber is connected. A good example could be to update a list of items that only changes periodically.
Transactional Replication has a sub-type of replication, known as Peer to Peer replication which is only available in the Enterprise Edition.
In a Peer-to-peer replication, all the servers act both as a distributor, as well as subscribers. It provides a scale-out and high availability solution by maintaining copies of data across multiple server instances, commonly referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of reading operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data. P2P replication has a conflict detection system but there is no conflict resolution system which you have in a merge.
The replication agents are executables which run from the bin folder of the SQL Server Installation directory. The run externally to SQL Server Database engine and runs like an application to connect to SQL Server.
This will mostly depend on which transactional replication option you choose. If the suspension or disruption occurs while the Queue agent is updating, then it might cause an issue.
Yes, you can, by using the “NOT FOR REPLICATION CLAUSE” for the column using the identity. What this replication clause will do is tell SQL Server to disable the code that reseeds an identity column when a replication agent is inserting data into an identity column. This preserves all of the identity ranges that you have setup. The drawback to this option is you will not be able to alter a table and once you enable this option.
Replication is not dependent on any particular recovery model. A database can participate in replication whether it is in simple, bulk-logged, or full. However, how data is tracked for replication depends on the type of replication used.
Yes, this can be done and there are mostly no restrictions on the number or types of publications that can use the same distribution database. However, you need to use the same database for all Publishers and the same for the Distributor.
You can use Tracer tokens which were introduced with SQL Server 2005 transactional replication as a way to monitor the latency of delivering transactions from the publisher to the distributor and from the distributor to the subscribers.
Yes. Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the table and since you cannot drop a published objects, the schema change will fail.
Using Oracle Publisher, you can set up a heterogeneous replication. From SQL Server 2005 onwards, publishing Oracle databases can be directly replicated to SQL Server in the same way as a Standard SQL Server replication setup.
Publisher: The Publisher is a server that makes data available for replication to other servers. In addition to being the server where you specify which data is to be replicated, the Publisher also detects which data has changed and maintains information about all publications at that site. Usually, any data element that is replicated has a single Publisher, even if it may be updated by several Subscribers or republished by a Subscriber.
Distributor: The Distributor is a server that contains the distribution database and stores metadata, history data, and/or transactions. The Distributor can be a separate server from the Publisher (remote Distributor), or it can be the same server as the Publisher (local Distributor). The role of the Distributor varies depending on which type of replication you implement
Subscribers: Subscribers are servers that receive replicated data. Subscribers subscribe to publications, not to individual articles within a publication, and they subscribe only to the publications that they need, not necessarily all of the publications available on a Publisher.
Yes, A publication can simultaneously support both push and pull subscriptions; however, any given subscriber is restricted to either a push or pull subscription
Comments are closed.