This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What should be your steps and consideration to monitor the SQL Server Replication?
- Set the agent profiles, schedules, properties, and notifications for replication agents.
- View and troubleshoot agent activity, including verifying when agents last ran, monitoring agent activity, and analyzing replication performance.
- Receive notification through a replication alert when an event occurs on a replication agent.
- Validate subscriptions to ensure that data values are the same at the Publisher and at Subscribers.
- Reinitialize one or all subscriptions to a publication as needed.
- Manage replication agents from a central location.
Have you ever used Replication Monitor component of SQL Server Enterprise Manager?
Yes: for viewing the status of replication agents and troubleshooting potential problems at the Distributor. Replication Monitor shows up as a node in SQL Server Enterprise Manager under the server that is enabled as a Distributor when the user is a member of the sysadmin fixed server role.
What do you know about the Heterogeneous Database Replication and have you ever tried to replicate data from SQL Server to non-SQL Server subscribers?
Yes, Heterogeneous data source means data source other than SQL Server.
SQL Server supports publishing of data using ODBC or OLEDB driver.
It also supports publishing data from Oracle to SQL Server and publishing data from SQL Server to non-SQL Server Subscribers.
Does activity need to be stopped on a database when it is published?
No. Activity can continue on a database while a publication is being created. Be aware that producing a snapshot can be resource-intensive, so it is best to generate snapshots during periods of lower activity on the database (by default a snapshot is generated when you complete the New Publication Wizard).
What happens if the Snapshot Agent has not completed when the Distribution or Merge Agent starts?
It will not cause an error if the Distribution Agent or Merge Agent runs at the same time as the Snapshot Agent.
Should I script my replication configuration?
Yes. Scripting the replication configuration is a key part of any disaster recovery plan for a replication topology.
How do I replicate data over the Internet?
Replicate data over the Internet using:
A Virtual Private Network (VPN). For more information, see Publish Data over the Internet Using VPN.
The Web synchronization option for merge replication. For more information, see Web Synchronization for Merge Replication.
Does replication work over low bandwidth connections? Does it use compression?
Yes, replication does work over low bandwidth connections. For connections over TCP/IP, it uses the compression provided by the protocol but does not provide additional compression. For Web synchronization connections over HTTPS, it uses the compression provided by the protocol and also additional compression of the XML files used to replicate changes.
In Transactional replication, If a table in database ‘A’(Publisher) is dropped, will the table get dropped in ‘B’(Subscriber)?
You cannot drop a table that is replicated. You have to first drop the article.
In Transactional Replication, If we drop a column in a table in database ‘A’, what will happen to the column in the same table in database ‘B’?
Issuing ALTER TABLE … DROP COLUMN at the Publisher, will result in the command being replicated to the Subscriber.
How can I prevent the snapshot agent failing with ‘Server execution failed’?
The value of:
“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\Subsystems\Snapshot”
should be changed to:
C:\Program Files\Microsoft SQL Server\MSSQL\BINN\SQLREPSS.DLL,C:\Program Files\Microsoft SQL Server\80\COM\SNAPSHOT.EXE,ReplStart,ReplEvent,ReplStop,120
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. For merge replication, you must ensure that change tracking metadata is updated properly.
What Options we use to secure Replication Data?
Ensure that SQL Browser is running and TCP/IP is enabled. Enforce TDE (Transparent Data Encryption) so that every data bit is encrypted.
Why you should consider Compressed Snapshots?
Compressing snapshot files in the alternate snapshot folder can reduce snapshot disk storage requirements and, in some cases, improve the performance of transferring snapshot files across the network when they are used for replication over the Internet. However, compressing the snapshot requires additional processing by the Snapshot Agent when generating the snapshot files, and by the merge agent when applying the snapshot files.
What are the different stored procedure to manage tracer tokens?
- sp_posttracertoken (used to push a token into a publication).
- sp_helptracertokens (returns a row for each tracer token that has been inserted at a publisher for a given publication).
- sp_helptracertokenhistory (returns latency information for a given token id value retrieved from either of the procedures above).
- sp_deletetracertokenhistory (removes token records from the system meta tables).
How could you replicate data between different schemas?
When we setup replication we don’t change a lot of the default values. One of the default configuration values is Replication Schema which is set to Destination Object Owner ( Subscriber Articles Owner) will be same as of Source Object Owner.
If we have Publication articles configured under the ABC schema then by default the Subscriber schema will also be ABC. By adjusting the subscriber schema, we can create the tables on the subscriber under a different schema without impacting the existing tables.
Would you please explain the output of sp_replcounters?
- Database – publisher database
- Replicated transactions – Number of transactions in the log awaiting delivery to the distribution database
- Replication rate trans/sec – Average number of transactions per second delivered to the distribution database
- Replication latency – Average time, in seconds, that transactions were in the log before being distributed
- Replbeginlsn – Log sequence number (LSN) of the current truncation point in the log
- Replendlsn – LSN of the next commit record awaiting delivery to the distribution database
What should I do if my system is running out of memory when too many agents synchronize?
You can limit the number of concurrent synchronizations; in merge this is done on the GUI or by setting the @max_concurrent_merge property of sp_addmergepublication. For other agents you’ll need to make the necessary edits to the registry.
What options are there to delete rows on the publisher and not on the subscriber?
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.
