More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Unable to specify a remote distributor for a Desktop/MSDE publisher. Why? How to start replication agents programmatically?
Replication agents (Snapshot, Distribution, Logreader and Merge agents) run according to the schedules you have set while setting up the replication scenario. But you can always override those schedules and start the agents from your applications programmatically! Here a couple of ways to start the agents programmatically:
Method #1:
Use sp_start_job: In the Enterprise Manager of your Distributor, select the Agent under 'Replication Monitor' -> Agents. In the right hand side pane, right click on the agent and select 'Agent properties' from the popup menu. From the Agent properties dialog box, copy the name of the agent, as we need to pass it to sp_start_job. Now, open ISQLW, connect to your Distributor, and execute the following command. Replcae the with the one you copied from Agent properties dialog box.
EXEC msdb..sp_start_job <Agent Name>
GO
Note that sp_start_job results in an asynchronous operation, and the control returns to the next statement of your code, as soon as the agent starts.
Method #2
Run the agents from the command prompt as EXEs: In SQL Server 7.0 all the replication agents are implemented as EXEs (Executable files). So, you can run them directly from the command prompt, or schedule them with the NT's AT command. Use logread.exe, distrib.exe, snapshot.exe and replmerg.exe to run logreader agent, distribution agent, snapshot agent and merge agent respectively.
Go to the same place in the Enterprise Manager. In the Agent properties dialog box, go to Steps tab. Double click on 'Run Agent' step. Copy the text available in the Command text box, as we need to pass it to the EXE file. Open Command Prompt and run any of the following commands, based on your requirement. Replace with the text you copied from 'Run agent' step.
Distrib.exe <Parameters>
Logread.exe <Parameters>
Sanpshot.exe <Parameters>
Replmerg.exe <parameters>
Note that, these are ActiveX EXEs and they open up a new console window whenever you run them. You have to press CTRL+C to close these console windows.
Method #3
Use xp_cmdshell: Go to the same place in the Enterprise Manager. In the Agent properties dialog box, go to Steps tab. Double click on 'Run Agent' step. Copy the text available in the Command text box, as we need to pass it to the xp_cmdshell command. Now, open ISQLW, connect to your Distributor, and execute the following command. Replace the with the text you copied from 'Run agent' step.
EXEC master..xp_cmdshell "distrib.exe <Command list>"
Method #4
Use the replication ActiveX controls: SQL Server 7.0 comes with replication ActiveX controls which can be used via programming like Visual Basic, Visual C++ etc.
Method #5
Use the SQL DMO objects: You can manipulate replication agents using SQL DMO objects also, again these objects can be used in programming languages like VB, VC++ etc.
(Continued on next question...)
