First-Time Initialization

You must initialize the primary Microsoft SQL Server so that Replication Agent can open the supplemental log of a table or procedure that is marked for replication. Do this only once for each primary data server.

1. Stop the Microsoft SQL Server Analysis Service. In Control Panel > Administrative Tools > Services, find the service named SQL Server Analysis Services. Stop this service.
2. Make sure Microsoft SQL Server allows a remote dedicated administrative connection (DAC):

   sp_configure 'remote admin connections', 1
   GO
   RECONFIGURE
   GO

   To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. This permission is implicitly held by the sysadmin and serveradmin fixed server roles.

3. Determine the primary Microsoft SQL Server DAC port number.
   1. Open the ERRORLOG file in a text editor. This file is located in the log directory of your Microsoft SQL Server. For example,

      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

   2. Search for the string “Dedicated admin” to find an entry similar to:

      2007-11-09 13:40:02.40 Server Dedicated admin 
      connection support was established for listening 
      locally on port 1348.

   3. Record the port number specified in this entry.
4. Update your sql.ini file with the instance name and port number of your Replication Agent instance.
5. Log in to your Replication Agent, and set the pds_dac_port_number configuration parameter:

   ra_config pds_dac_port_number, port

   where port is the DAC port number you recorded.

6. Also configure these Replication Agent connectivity parameters for the Microsoft SQL Server primary database:

   • pds_server_name

   • pds_database_name

   • pds_username

   • pds_password

   • pds_port_number

   For information about these configuration parameters, see the Replication Agent Installation Guide and Replication Agent Reference Manual.

7. Stop the Microsoft SQL Server service.
   1. In Control Panel > Administrative Tools > Services, find the service named SQL Server (SERVER), where SERVER is the name of your Microsoft SQL Server data server.
      
   2. Stop this service.
8. Open a command window, and restart Microsoft SQL Server in single-user mode.
   For example:

   "C:\Program Files\Microsoft SQL 
   Server\MSSQL.1\MSSQL\Binn\sqlservr.exe” -m -s 
   instanceName

   Note: The directory path may vary depending on the version of Microsoft SQL Server.

   where instanceName is the name of the Microsoft SQL Server instance.

9. Make sure that there are no other connections to the primary database, and verify that Replication Agent can connect to the primary database.
   1. Log in to the Replication Agent instance:

      isql -U username -P password -S instanceName 

      where username, password, and instanceName are your user ID, password, and Replication Agent instance name.

   2. Issue:

      test_connection PDS

10. Initialize the Microsoft SQL Server data server and Replication Agent:

    server_xlog init
    ra_admin init

    In the primary database, Replication Agent creates tables, procedures, and triggers. The sp_SybSetLogforReplTable, sp_SybSetLogforReplProc, and sp_SybSetLogforLOBCol procedures are created in the mssqlsystemresource database with execute permission granted to Public.

11. Stop the Microsoft SQL Server service again by using CTRL+C at the command prompt. Alternatively, at the command prompt, run the SHUTDOWN command.
12. Restart Microsoft SQL Server in multiuser mode (normal start).
    1. In Control Panel > Administrative Tools > Services, find the service named SQL Server (SERVER), where SERVER is the name of your Microsoft SQL Server data server.
       
    2. Start this service.

       Start other Microsoft SQL Server services, such as Microsoft SQL Server Agent service or the Microsoft SQL Server Analysis Service.