Configuring and verifying the primary Microsoft SQL Server

Before you configure Replication Agent and configure the primary Microsoft SQL Server database for replication, complete these procedures:

Verify the compatibility level of Microsoft SQL Server.
Disable Microsoft SQL Server replication.
Create a Microsoft SQL Server user and grant Microsoft SQL Server permissions.
Enable remote DAC.
Obtain the Microsoft SQL Server DAC port number.
Verify the supplemental logging of primary key data.
Make the primary transaction log files readable for Replication Agent.
Stop the Analysis Service.
Enable TCP/IP.

Replication Agent supports only Microsoft SQL Server 2005 Service Pack 2 and later. Verify that the database compatibility level is set to Microsoft SQL Server 2005 (90):

In the Microsoft SQL Server Management Studio, right-click your primary database and select Properties.
In Database Properties, click Options, and verify that the Compatibility level is “Microsoft SQL Server 2005 (90).” If it is not, select that compatibility from the drop-down list, and click OK.

A Microsoft SQL Server publication cannot be created in the primary database used by Replication Agent for Microsoft SQL Server, and you cannot simultaneously use Microsoft replication and Replication Agent on the same Microsoft SQL Server database. If a Microsoft SQL Server publication already exists, remove the publication before using Replication Agent for Microsoft SQL Server.

Log in to the primary Microsoft SQL Server as a system administrator, and run the following commands to create a Microsoft SQL Server user named “ra_user” with the password “sybase,” and grant permissions to the user:
```
use master;
```
```
create login ra_user;
```
```
use <primary_database>;
```
```
create user ra_user for login ra_user;
```
```
EXEC sp_addsrvrolemember 'ra_user','sysadmin';
```

From the Windows Start menu, choose Microsoft SQL Server | Configuration Tools | Surface Area Configuration | Surface Area Configuration for Features.
In the Surface Area Configuration for Features window, choose DAC under the MSSQLSERVER/Database Engine tree, and make sure the Enable Remote DAC check box is selected.

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
```

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

2007-11-09 13:40:02.40 Server Dedicated admin

connection support was established for listening

locally on port 1348.

Record the port number specified in this entry for use in a later step.

To make the primary transaction log files readable for Replication Agent, the Microsoft Filter Manager Library must be version 5.1.2600.2978 or later.

To determine the version of the library, in Windows Explorer, right-click c:\windows\system32\fltlib.dll, select Properties, and click the Version tab in the Properties dialog box.
If the version is earlier than 5.1.2600.2978, go to the Microsoft Web site at http://windowsupdate.microsoft.com, and update your Windows system.

Install and set up the sybfilter driver so that Replication Agent can read the primary transaction log files.

In Windows Explorer, navigate to the sybfilter driver installation directory. This directory is located at %SYBASE%\RAX-15_2\system\<platform>, were <platform> is winx86, winx64, or winvistax64.
Right-click the sybfilter.inf file to install the sybfilter driver.

There can be only one installation of the sybfilter driver on a Windows machine. After the driver is installed, it works for all Replication Agent for Microsoft SQL Server instances running on the same machine.
Under any directory, create a configuration file to store all log file paths for primary databases. The configuration file must have a .cfg suffix. For example, under the directory %SYBASE%\RAX-15_2\system\<platform>, create a file named LogPath.cfg.
Add a system environment variable named RACFGFilePath, and set its value to the path of the configuration file.
1. Open the Control Panel, click System, click the Advanced tab, and choose Environment Variables.
2. Click New to add a new system variable.
3. Name the variable RACFGFilePath, and set its value to the location of your configuration file.
In Windows Explorer, navigate to %SYBASE%\RAX-15_2\bin, and double-click the sybfiltermgr.exe file to start the sybfilter driver management console.
To start the sybfilter driver, enter start at the management console.
Add the log file path to the sybfilter driver with the user manager or by modifying the configuration file:
- User manager – use the add command in the management console. The syntax for this command is as follows:
```
add serverName dbName logFilePath
```
  For example, to add the log file named pdb2_log.ldf at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ to the dbName database on the serverName data server, use the following:
```
add myserverName dbName C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\pdb2_log.ldf	
```
  If you add the log file path with the user manager, the user manager refreshes all log paths in the sybfilter driver automatically after adding the log path into the configuration file.
- Configuration file – to add the log file path directly to the configuration file, open and manually edit the configuration file. This an example of log file path entries:
```
[myserver, pdb1]
```
```
log_file_path=C:\Program Files\Microsoft SQL
```
```
Server\MSSQL.1\MSSQL\Data\pdb11_log.ldf
```
```
log_file_path=C:\Program Files\Microsoft SQL
```
```
Server\MSSQL.1\MSSQL\Data\pdb12_log.ldf
```
```
[myserver, pdb2]
```
```
log_file_path=C:\Program Files\Microsoft SQL
```
```
Server\MSSQL.1\MSSQL\Data\pdb2_log.ldf
```
  After you have added the log file paths to the configuration file, use the refresh command in the management console.
If you added a log file for your primary database before adding the log file path to the sybfilter driver, restart Microsoft SQL Server to make the log file readable.
At the management console, verify that log files are readable. If some log files are unreadable, make sure the files have been created and that Microsoft SQL Server has been restarted, if necessary.

See the Replication Agent Primary Database Guide.

From the Windows Control Panel, select Administrative Tools | Services.
In the listed services, find the service named Microsoft SQL Server Analysis Service (SERVER), where SERVER is the name of your Microsoft SQL Server data server.
Stop this service.

From the Windows Start menu, choose Microsoft SQL Server | Surface Area Configuration | Surface Area Configuration for Services and Connections.
Under the Database Engine tree, click Remote Connections.
Select “Local and remote connections,” and “Using both TCP/IP and Named Pipes protocols.”

Go to the Microsoft download site (http://www.microsoft.com/downloads) and search for the following string:
```
Microsoft SQL Server 2005 JDBC driver
```
Click the link for the 1.2 driver, and follow the instructions to download and install the driver.
Open Control Panel | System, click the Advanced tab, and click Environment Variables.
In the System Variables window, if the CLASSPATH variable is not listed, click New. Otherwise, select it, and click Edit.
Enter the full path of the JDBC driver, using a semicolon (;) to separate it from any other drivers.
Click OK three times.