Are you searching for SQL Server Replication job? Preparing for any interview is a tough task. We simplified this system. SQL Server Replication is the process of replicating the existing data and database objects from one to another to maintain consistency. Many Organizations are awaiting for SQL Server Replication job candidates for several roles maintain the huge amounts of data. Job opportunities are present everywhere for this technology. SQL Server Replication job description might include operations like storing, manipulating, retrieving data from relational database, tables and indexes with the help of SQL where several queries are used. Wisdomjobs created interview questions exclusively for the candidates who are in search of job. Do check our page for SQL Server Replication job interview questions and answers to get set for the interview.
Replication is subset of SQL Server that can move data and database objects in an automated way from one database to another database. This allows users to work with the same data at different locations and changes that are made are transferred to keep the databases synchronized.
Snapshot replication - As the name implies snapshot replication takes a snapshot of the published objects and applies it to a subscriber. Snapshot replication completely overwrites the data at the subscriber each time a snapshot is applied. It is best suited for fairly static data or if it's acceptable to have data out of sync between replication intervals. A subscriber does not always need to be connected, so data marked for replication can be applied the next time the subscriber is connected. An example use of snapshot replication is to update a list of items that only changes periodically.
Transactional replication - As the name implies, it replicates each transaction for the article being published. To set up transactional replication, a snapshot of the publisher or a backup is taken and applied to the subscriber to synchronize the data. After that, when a transaction is written to the transaction log, the Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. Only committed transactions are replicated to ensure data consistency. Transactional replication is widely applied where high latency is not allowed, such as an OLTP system for a bank or a stock trading firm, because you always need real-time updates of cash or stocks.
Merge replication - This is the most complex types of replication which allows changes to happen at both the publisher and subscriber. As the name implies, changes are merged to keep data consistency and a uniform set of data. Just like transactional replication, an initial synchronization is done by applying snapshot. When a transaction occurs at the Publisher or Subscriber, the change is written to change tracking tables. The Merge Agent checks these tracking tables and sends the transaction to the distribution database where it gets propagated. The merge agent has the capability of resolving conflicts that occur during data synchronization. An example of using merge replication can be a store with many branches where products may be centrally stored in inventory. As the overall inventory is reduced it is propagated to the other stores to keep the databases synchronized.
Push - As the name implies, a push subscription pushes data from publisher to the subscriber. Changes can be pushed to subscribers on demand, continuously, or on a scheduled basis.
Pull - As the name implies, a pull subscription requests changes from the Publisher. This allows the subscriber to pull data as needed. This is useful for disconnected machines such as notebook computers that are not always connected and when they connect they can pull the data.
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.
Locking depends on the type of replication used:
One option is to replicate stored procedure execution instead of the actual DELETE command. You can create two different versions of the stored procedures one on the publisher that does the delete and the other on the subscriber that does not do the delete.
Another option is to not replicate DELETE commands.
Yes this can be done and there are no restrictions on the number or types of publications that can use the same distribution database. One thing to note though is that all publications from a Publisher must use the same Distributor and distribution database.
There are a number of possible causes for data not being delivered to Subscribers:
Sp_replcounters is a system stored procedure that returns information about the transaction rate, latency, and first and last log sequence number (LSN) for each publication on a server. This is run on the publishing server. Running this stored procedure on a server that is acting as the distributor or subscribing to publications from another server will not return any data.
Tracer tokens 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 subscriber(s).
Question 12. If I Create A Publication With One Table As An Article, And Then Change The Schema Of The Published Table (for Example, By Adding A Column To The Table), Will The New Schema Ever Be Applied At 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.
Yes this can be done using heterogeneous replication. In SQL Server 2000, publishing data to other databases such as DB2 or Oracle was supported; however, publishing data from other databases was not supported without custom programming. In SQL Server 2005 and later versions, Oracle databases can be directly replicated to SQL Server in much the same way as standard SQL Server replication.
The easiest way to monitor replication activity and performance is to use replication monitor, but you can also use the below tools to monitor replication performance:
To monitor replication, a user must be a member of the sysadmin fixed server role at the Distributor or a member of the replmonitor fixed database role in the distribution database. A system administrator can add any user to the replmonitor role, which allows that user to view replication activity in Replication Monitor; however, the user cannot administer replication.
SQL Server Replication Related Tutorials
|SQL Database Tutorial||DB2 Using SQL Tutorial|
|Database system concepts Tutorial||Database Testing Tutorial|
|DocumentDB SQL Tutorial||Hyper SQL Database Tutorial|
SQL Server Replication Related Interview Questions
|SQL Database Interview Questions||DB2 Using SQL Interview Questions|
|Database Interview Questions||Database system concepts Interview Questions|
|SQL DBA Interview Questions||Database Testing Interview Questions|
|MYSQL DBA Interview Questions||Database Design Interview Questions|
|Database Administration Interview Questions||Hyper SQL Database Interview Questions|
|Database Optimization Interview Questions||Database Replication Interview Questions|
|Database Mirroring Interview Questions||Database Backup Recovery Interview Questions|
SQL Server Replication Related Practice Tests
|SQL Database Practice Tests||DB2 Using SQL Practice Tests|
|Database Practice Tests||Database system concepts Practice Tests|
|SQL DBA Practice Tests||Database Testing Practice Tests|
|MYSQL DBA Practice Tests||Database Design Practice Tests|
|Database Administration Practice Tests|
Object-based Databases And Xml
Data Storage And Querying
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.