This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is the role of the Immediate Sync option for SQL Server Replication?
You can see that the immediate_sync option set to TRUE is useful especially if you have the requirement of adding new subscriptions during the course of the retention period. You can also see that the data in the distribution database replication tables gets maintained even though the data is already replicated from the publisher to the subscriber.
We have two instances SERV1 and SERV2 with publication database db1 on SERV1 and subscriber database db1 on SERV2. Now we need to move the subscriber from SERV2 to SERV3 without dropping the existing subscription and creating a new subscription on the new server.
How it is possible?
- Create a full SQL Server database backup on existing Subscriber.
- Copy SQL Server backup and restore on new Subscriber with KEEP_REPLICATION option.
- Update replication tables on SQL Server Publisher database.
- Update the SQL Server replication tables on new Subscriber database.
- Update SQL Server replication tables on Distribution database.
- Modify the SQL Server Agent Job.
- Re-enable Synchronizing for the SQL Server Publication.
What is Parameterized Row Filters option of Merge Replication?
- Parameterized row filters allow different partitions of data to be sent to different Subscribers without requiring multiple publications to be created.
- A parameterized row filter uses a WHERE clause to select the appropriate data to be published.
- you specify one or both of the following system functions: SUSER_SNAME() and HOST_NAME().
What is a tracer tokens?
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).
A tracer token is basically a small piece of data that is inserted into the transaction log for the given publication just as if it were data for a standard database transaction and is treated basically the same way as well.
What is Article?
An article identifies a database object that is included in a publication. A publication can contain different types of articles, including tables, views, stored procedures, and other objects. When tables are published as articles, filters can be used to restrict the columns and rows of the data sent to Subscribers.
What is Publication?
A publication is a collection of one or more articles from one database. The grouping of multiple articles into a publication makes it easier to specify a logically related set of database objects and data that are replicated as a unit.
What is the use sp_replcounters?
Returns replication statistics about latency, throughput, and transaction count for each published database. This stored procedure is executed at the Publisher on any database.
What do you know about the trace token feature of Transaction Replication?
Transactional replication provides the tracer token feature, which provides a convenient way to measure latency in transactional replication topologies and to validate the connections between the Publisher, Distributor and Subscribers.
What permissions are needed to a user to monitor replication.
The replmonitor database role in the distribution database. These users can monitor replication, but cannot change any replication properties.
Does replicated database dependent on any recovery model?
Replication is not dependent on any particular recovery model. A database can participate in replication whether it is in simple, bulk-logged, or full.
Does replication encrypt data?
No. Replication does not encrypt data that is stored in the database or transferred over the network.
How we can generate Replication Script using SSMS?
- To script multiple objects from Management Studio
- Connect to the Distributor, Publisher, or Subscriber in Management Studio, and then expand the server node.
- Right-click the Replication folder, and then click Generate Scripts.
- Specify options in the Generate SQL Script dialog box.
- Click Script to File.
- Enter a file name in the Script File Location dialog box, and then click Save. A status message is displayed.
- Click OK, and then click Close.
Why does replication add a column to replicated tables?
To track changes, merge replication and transactional replication with queued updating subscriptions must be able to uniquely identify every row in every published table.
Have you ever used sp_repltrans stored procedure to find out data latency between publisher and subscriber in Transaction Replication?
Yes:
To check if replication is fine, we could run sp_repltrans on the publisher database. This displays the undistributed commands present in the publisher database. If your log reader agent is scheduled to run continuously and if this command returns no rows, replication is fine on the publisher side.
However, if your log reader agent is scheduled to run at intervals and there are changes that need to be sent to the distribution database, you would see rows returned when you execute this procedure which shows the LSNs of the transactions.
I have transactional replication configured in my production environment. The business team has requested that I do not replicate delete operations on certain articles.
How you can achieve this?
In SSMS go to Replication -> Local Publications and right click on your publication and select Properties. In the Publication Properties window click on ‘Articles’ and select the relevant article. Go to “Article Properties” and select “Set Properties of Highlighted Table Article”.
In the article properties window, change the “DELETE delivery format” to “Do not replicate DELETE statements”.
Can a publication support push and pull at one time?
Yes, A publication can simultaneously support both push and pull subscriptions; however, any given subscriber is restricted to either a push or pull subscription.
