Configuring and verifying the primary Microsoft SQL Server

Before you configure Mirror Replication Agent and configure the primary Microsoft SQL Server database for replication, complete these procedures, which are explained in the following subsections.

• 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 Mirror Replication Agent.

• Stop all Microsoft SQL Server services related to your Microsoft SQL Server instance, including the Analysis Service and Reporting Service.

• Enable TCP/IP.

Mirror 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).

1. In the Microsoft SQL Server Management Studio, right-click on your primary database and select Properties. This brings up the Database Properties dialog box.

2. 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.

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

1. Using Microsoft SQL Server Management Studio or the sqlcmd utility, connect to the primary Microsoft SQL Server, run the following commands to create a Microsoft SQL Server user named “ra_user” with the password “sybase,” and grant permissions to the user:

   CREATE LOGIN ra_user WITH PASSWORD=’sybase’,
   

   DEFAULT_DATABASE=primary_database;
   

2. At the primary database, enter the following command to allow the user to access the primary database:

   CREATE USER ra_user FOR LOGIN ra_user;
   

3. Grant sysadmin permission to ra_user:

   EXEC sp_addrolemember 'sysadmin', 'ra_user';
   

1. From the Windows Start menu, choose Microsoft SQL Server | Configuration Tools | Surface Area Configuration | Surface Area Configuration for Features.

2. 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.

   After you select Enable remote DAC, you should only click Apply and OK.

1. Log in to Microsoft SQL Server as a system administrator.

2. Invoke the sp_configure stored procedure as follows:

   sp_configure 'remote admin connections', 1
   

3. Issue the RECONFIGURE command to validate the configuration changes.

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 the following:

   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 for use later.

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

Perform the following steps to install and set up the sybfilter driver so that Mirror Replication Agent can read the primary transaction log files.

1. In Windows Explorer, navigate to the sybfilter driver installation directory. On Windows, this directory is located at %SYBASE%\MA-15_1\system\<platform>. Here, <platform> is winx86, winx64, or winvistax64.

2. Right-click the sybfilter.inf file, and click on Install to install the sybfilter driver. To reinstall the sybfilter driver, click Install on the correct filter.

   There can be only one installation of the sybfilter driver on a Windows machine. After the driver is installed, it works for all Mirror Replication Agent for Microsoft SQL Server instances running on the same machine. To verify that the sybfilter driver has been installed and is running, enter the following command at the DOS prompt:

   sc query sybfilter
   

   If the sybfilter driver has been installed, the sc command displays details about the driver.

3. 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%\MA-15_1\system\<platform>, create a file named LogPath.cfg.

4. 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 the your configuration file.

5. Start the sybfilter driver.

   1. Set the environment variables at a DOS prompt by executing %SYBASE%\SYBASE.bat.

   2. Change to %SYBASE%\MA-15_1\bin, and execute sybfiltermgr.exe.

   3. Enter start at the console.

6. 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, you must use the refresh command in the management console.

7. If you added a log file for your primary database before adding the log file path to the sybfilter driver, you must restart Microsoft SQL Server to make the log file readable.

8. At the management console, enter check to 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.

9. Exit the management console by entering exit.

For more information on the sybfilter driver, see the Mirror Replication Agent Primary Database Guide.

If you have not installed Microsoft SQL Server-related services, skip to the next procedure.

1. From the Windows Control Panel, select Administrative Tools | Services.

2. In the listed services, find the services named Microsoft SQL Server Analysis Service (SERVER) and Microsoft SQL Server Reporting Service (SERVER), where SERVER is the name of your Microsoft SQL Server data server.

3. Stop these services.

1. From the Windows Start menu, choose Microsoft SQL Server | Configuration Tools | Surface Area Configuration | Surface Area Configuration for Services and Connections.

2. Under the Database Engine tree, click Remote Connections.

3. Select “Local and remote connections,” and “Using both TCP/IP and Named Pipes protocols.“

1. Log in to Microsoft SQL Server as a system administrator.

2. Invoke the sp_configure stored procedure as follows:

   sp_configure 'remote access', 1
   

3. Issue the RECONFIGURE command to validate the configuration changes.

1. Go to the Microsoft download site (http://www.microsoft.com/downloads) and search for the following string:

   Microsoft SQL Server 2005 JDBC driver
   

2. Click on the link for the 1.2 driver, and follow the instructions to download and install the driver.

3. Open Control Panel | System, click on the Advanced tab, and click Environment Variables.

4. In the System Variables window, if the CLASSPATH variable is not listed, click New. Otherwise, select it and click Edit.

5. Enter the full path of the JDBC driver using the semi-colon (;) to separate it from any other drivers, for example, C:\path\sqljdbc.jar.

6. Click OK three times.