Replication

 

EASY

1) What is SQL Server replication? 

2) What are the different types of SQL Server replication?

3) What is the difference between Push and Pull Subscription?

4) What are different replication agents and what's their purpose?

 

5) Does a specific recovery model need to be used for a replicated database?

Medium

1) What type of locking occurs during the Snapshot generation?

2) What options are there to delete rows on the publisher and not on the subscriber?

3) Is it possible to run multiple publications and different type of publications from the same distribution database?

4) Data is not being delivered to Subscribers, what can be the possible reasons?

5) Explain what stored procedure sp_replcounters is used for?

Hard

1) How will you monitor replication latency in transactional replication?

2) 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?

3) Is it possible to replicate data from SQL Server to Oracle?

4) How will you monitor replication activity and performance? What privilege do you need to use replication monitor?

5) Can you tell me some of the common replication DMV's and their use?

6) What are the main prerequisites you should plan before configuring SQL Server Transactional Replication?

Answer  Below are the things you should follow before setting up transactional replication.

7) What are the possible reasons for a SQL Server Replication lag or Replication latency issues?

Answer  There could be multiple reasons for replication latency issues. Some of the reasons are given below.

 

8) Can we truncate a table that is marked as an article in SQL Server Replication?

Answer - No, you cannot truncate a table that is marked for replication.

9) Can we configure SQL Server Replication for a database that is part of an Availability Group?

Answer - Yes, you can configure replication for a database that is in an Availability Group.

10) Can you tell me where the SQL Server Merge Agent will run for a PULL subscription?

Answer  If you configure a PULL subscription, the Merge Agent will be configured to run on the Subscriber server.

11) How can we drop an article from publication?

Answer We can drop any article from publication if needed. We need to launch Publication properties page then we need to select the publication from which we need to drop the identified article. To delete an article, clear the check box next to each article you want to delete then click OK.

12) Adding/dropping article(s) from an existing publication by invalidating the snapshot agent

1.     First, change the allow_anonymous property of the publication to FALSE

EXEC sp_changepublication

@publication = N'Adventureworks2016-Pub',

@property = N'allow_anonymous',

@value = 'FALSE'

GO

  1. Next, disable Change immediate_sync

EXEC sp_changepublication

@publication = N'Adventureworks2016-Pub',

@property = N'immediate_sync',

@value = 'FALSE'

GO

 

  1. Invalidate the snapshot

EXEC sp_addarticle

@publication = N'Adventureworks2016-Pub',

@article =N'Cities',

@source_object =N'Cities',

@force_invalidate_snapshot=1

  1. Refresh subscriptions

EXEC sp_refreshsubscriptions @publication = N'Adventureworks2016-Pub'

  1. Now, start Snapshot Agent using Replication monitor
  2. Next, start log reader agent
  3. Re-enable the disabled properties, first, immediate_sync and then Allow_anonymous options

EXEC sp_changepublication

@publication = N'Adventureworks2016-Pub',

@property = N'immediate_sync',

@value = 'TRUE'

 

EXEC sp_changepublication

@publication = N'Adventureworks2016-Pub',

@property = N'allow_anonymous',

@value = 'TRUE'

  1. Now you can verify the article on all your subscribers