Before beginning the SQL Server site database replication process, ensure that you perform the required pre-replication setup tasks.

Some tasks are required on both the publisher computer (site database server) and the subscriber computer (SQL Server that will host the site database replica). Other tasks are specific to the publisher or subscriber SQL Server computer.

For both the publisher and subscriber SQL Server computers you must run the SQL Server 2005 Surface Area Configuration Wizard to configure required services and connections and CLR integration.

Additionally, on the publisher SQL Server computer, you should configure the maximum text character replication size to enable long character text replication. A shared network path should also be created to store the site database information publication information that will be accessed by subscriber SQL Server computers before configuring replication.

On the subscriber SQL Server computer, an empty SQL Server database must be created to store the site database replica information retrieved from the site database publication stored in the shared network path created on the publisher computer during publication of the site database information.

Configure SQL Server Settings

SQL Server 2005 includes the SQL Server Surface Area Configuration Tool, which provides an intuitive graphical user interface (GUI) for configuring the server. The tool opens with a brief explanation of its purpose, and a link to documentation. It includes a link to configure services and protocols and another to configure other features.

Before beginning the SQL Server site database replication process, the SQL Server 2005 Surface Area Configuration wizard should be run on all SQL Servers involved in the site database replication process to configure the necessary network communication protocols and SQL Server settings.

To configure SQL Server settings using the SQL Server 2005 Surface Area Configuration wizard

  1. Click Start \ All Programs \ Microsoft SQL Server 2005 \ Configuration Tools \ SQL Server Surface Area Configuration to start the SQL Server 2005 Surface Area Configuration Wizard.

  2. Select Surface Area Configuration for Services and Connections. On the View by Instance tab, expand Database Engine and click Remote Connections. Ensure Local and remote connections is selected, select Using TCP/IP only and click Apply.

    Note
    TCP/IP is required for network communications to allow Kerberos authentication. Named pipes communication is not required for Configuration Manager 2007 site database operations and should only be used to troubleshoot Kerberos authentication issues.

  3. Select SQL Server Agent and select Automatic from the Startup type list and click Start to start the SQL Server Agent service and click OK.

    Note
    The SQL Server Agent service must be running to schedule site database replication actions.

  4. Select Surface Area Configuration for Features. On the View by Instance tab, expand Database Engine and select CLR Integration. Select Enable CLR integration.

    Note
    This step is required to support the desired configuration management feature. Failing to enable the common language runtime (CLR) integration option will result in clients being unable to download desired configuration baselines that are assigned to them; consequently they will be unable to send their current compliance status.

  5. Close the SQL Server 2005 Surface Area Configuration Wizard.

Configure the Publisher SQL Server Computer to Replicate Long Character Text Data

When using SQL Server replication to send data from your primary site database server to a replicated site database, the length of some data to be replicated may be longer than the SQL Server default maximum length size of 64KB. Specifically, some software update assignment data may be truncated or not replicated at all.

To enable SQL Server replication for replication data to succeed for required Configuration Manager 2007 data, you can run the following SQL command on your publishing SQL Server to set the size of replicated data to the maximum size allowed:

To enable SQL Server to replicate long character text data

  1. In the SQL Server 2005 SQL Server Management Studio console connected to the SQL Server hosting the site database, expand Databases, right click the site database name and click New Query.

  2. In the query results pane, enter the following command:

    EXEC sp_configure ‘max text repl size’, 2147483647

  3. Click Execute.

  4. In the query results pane, delete the previous command and enter the following command:

    RECONFIGURE WITH OVERRIDE

  5. Click Execute to begin reconfiguration.

  6. Verify that the maximum text replication size has been changed in the Messages pane.

Create a SQL Server Database to Host the Site Database Replica

An empty SQL Server database must be created to host the site database replica. When creating the database to host the site database replica, ensure that the database collation for the site database and new database created to host the site database replica are the same.

To create the SQL Server database to host the site database replica

  1. In the SQL Server 2005 SQL Server Management Studio console connected to the SQL Server that will host the site database replica, right click Databases and click New Database…

  2. In the New Database dialog, General page, enter a name for the replicated site database in Database name:.

    Note
    There is no required name for the database that will host the site database replica, but a common practice is to name the database similarly to the site database name to be replicated with the additional characters _REP. This allows administrators to more easily identify the replicated database in the future as well as which Configuration Manager 2007 site database this replica represents. For example, a site database replica for a site database named SMS_XYZ would be named SMS_XYZ_REP.

  3. Click OK to close the New Database dialog.

See Also