Implementing Transactional Replication

  • Article

Updated : November 14, 2002

This chapter teaches you how to increase the availability of a Microsoft SQL Server data center by implementing transactional replication. After reading this chapter, you will be able to configure each component in this high-availability solution. To configure transactional replication using Network Load Balancing (NLB), read Solution Guide Chapter 4, "Implementing Network Load Balancing."

The implementation steps include annotation to help you understand the selected configuration choices. The configuration options presented in this chapter have been designed by using input from the field, reviewed thoroughly by Microsoft Product Support Services (PSS), and tested by an independent team to ensure accuracy and functionality.

Transactional replication is only part of achieving a highly available data center. To achieve a highly available data center, you must also follow the processes outlined in the accompanying Planning Guide.

On This Page

Understanding Transactional Replication
Deploying Transactional Replication
Changing the Role of a Subscriber
Reverting to the Original Publisher

Understanding Transactional Replication

Transactional replication is a high-availability solution that is provided with all SQL Server 2000 editions and that can be implemented with any server edition of Windows 2000. If you want to use the client redirection capabilities of the NLB; however, you must use either Microsoft Windows 2000 Advanced Server or Windows 2000 Datacenter Server.

Transactional replication is an inexpensive high-availability solution that uses SQL Server jobs to initialize and then periodically update a copy of the production database (the subscriptions database) on one or more secondary servers (subscribers). With transactional replication, proximity is not a limitation; you can place a subscriber in a geographically remote location to provide protection against a catastrophic disaster. Subscribers do not need to be identical to the primary server (the publisher), although differences in the amount of memory, number and speed of the processors, and the robustness of the storage system can affect SQL Server performance after a role change.

You can use the subscription database on a subscriber in place of the production database in case the publisher fails. The subscription database is always slightly out of sync with the production database. You can control the level of synchronicity — it can be as short as several seconds (by using a high speed network), or as long as you choose.

With transactional replication, a subscription database can be used for reporting or maintenance tasks without affecting the transactional currency of the subscription database. Transactional replication was not designed as a high-availability solution, however, and does not provide a built-in role change mechanism to change the role of a subscriber. In addition, transactional replication does not replicate most schema changes to the subscription database. You replicate added or dropped columns using special stored procedures.

Changing the role of a subscriber is a manual process that requires the data base administrator (DBA) to perform a number of tasks to complete the role change, including the manual redirection of client requests to the promoted subscriber. To point clients to the new primary server, use one of the following client redirection options:

  • Program an alternative server list into the client application

  • Update the client Data Source Name (DSN)

  • Update the Domain Name System (DNS)

  • Update the COM+ component

  • Update the NLB configuration.

Any transactions that have not been copied to the distributor before the publisher fails are lost. Because changing server roles is a manual process, changing roles takes longer than changing nodes with failover clustering. Changing roles, however, is significantly faster than restoring a database to a secondary server from a cold backup.

Transactional replication is most useful as an alternative to log shipping when you want to use the subscription database for reporting and maintenance tasks without sacrificing transactional currency.

To implement transactional replication, you need to have certain things in place:

  • You need people in the following roles:

    • Database administrator

    • Network administrator

  • You need the following software:

    • Windows 2000 Server, Windows 2000 Advanced Server, or Windows 2000 Datacenter Server

    • SQL Server 2000 Standard Edition or SQL Server 2000 Enterprise Edition

  • You need systems and system components that are certified to work together and with the chosen Windows 2000 operating system edition. Windows 2000 Datacenter Server has more stringent certification requirements than Windows 2000 Advanced Server. To find systems and system components that are certified by Microsoft, search the "Hardware Compatibility List" on the Microsoft Web site at https://support.microsoft.com/kb/131900.

For more information about planning for transactional replication, see "Planning for Transactional Replication" on the MSDN Web site at https://msdn2.microsoft.com/library/aa237475.aspx.

For more information about tuning transactional replication, see "Transactional Replication Performance Tuning and Optimization" on the TechNet Web site at https://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx.

For information about using transactional replication with log shipping, see "Transactional Replication and Log Shipping" on the MSDN Web site at https://msdn2.microsoft.com/library/aa237088.aspx.

For information about connectivity between geographically dispersed sites, see Publishing Data Over the Internet Using VPN in SQL Server Books Online, and Virtual private network (VPN) connections overview on the MSDN Web site at https://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/conn_vpn.mspx.

Deploying Transactional Replication

Implementing transactional replication is wizard-driven and easy to follow. You must make some schema changes before you begin the transactional replication setup wizard, however.

This chapter assumes the following with respect to each participating server in the transactional replication solution:

  • Windows 2000 Server, Windows 2000 Advanced Server, or Windows 2000 Datacenter Server is installed.

  • The most recent operating system service pack is installed.

  • The server is a member of the same Microsoft Active Directory service forest as each other server.

  • At least one network interface card (NIC) is installed.

  • The server is connected to the corporate network.

  • SQL Server 2000 Standard Edition or SQL Server 2000 Enterprise Edition is installed and configured to use the same authentication mode as each other server.

  • The most recent SQL Server 2000 service pack is installed.

  • The SQL Server Agent service is configured to use the same domain user account used by each the SQL Server Agent service on each other server.

  • The SQL Server Agent service is configured to start automatically.

  • The server is registered in SQL Server Enterprise Manager.

  • If e-mail notifications will be used, SQLAgentMail is configured.

This chapter also assumes that the production database on the primary server is configured to use either the bulk-logged recovery model or the full recovery model. The logical design of the transactional replication solution built in this chapter is described below, followed by the specific steps required to build this high-availability solution.

Understanding the Logical Design

Figure 3.1 shows the logical design of the transactional replication solution that is described in this chapter. For simplicity, only the subscriber/distributor is shown. Additional subscribers can be added — for instance, at geographically remote locations — to protect against site-level disasters.

Cc917706.haso0301(en-us,TechNet.10).gif

Figure 3.1: Logical design diagram of a transactional replication solution

After you set up the production database on the publisher, set up transactional replication. Designate a subscriber as the distributor in a separate site from the publisher. Placing the distributor in a separate site increases the availability of the data center in the event the primary site fails. On the server containing the production database, designate the server as the publisher, and publish the production database. Configure one or more subscribers to receive the publication and maintain a subscription database. The snapshot agent, the distribution agent, and the log reader agent perform the work of maintaining the transactional currency of the subscription database on each subscriber.

The snapshot agent takes an initial snapshot of the production database and stores the snapshot in the snapshot folder on the subscriber/distributor. The distribution agent initializes the subscription database on each subscriber by using the snapshot. The log reader agent reads the production database transaction log, copying all changes since the initial snapshot to the distribution database on the subscriber/distributor. The distribution agent periodically retrieves the production database changes stored in the distribution database and updates the subscription database on each subscriber.

Although no special networking is required for transactional replication, you must ensure that the bandwidth between the publisher and each subscriber (especially the subscriber/distributor) is sufficient to handle the network traffic generated by the transactional replication agents. These replication agents must be able to keep up with the volume of changes occurring in the production database. The amount of traffic depends on the number and size of the transactions generated in the production database. On the publisher, ensure that all disks are fault tolerant to minimize the need for a role change because of a disk failure. On the subscriber/distributor, use fault tolerant disks to ensure that all copied transactions are replicated to each subscriber and that the subscriber is available to function as a replacement server if needed. On each additional subscriber, use fault tolerant disks to ensure that each subscriber is available to function as a replacement primary server if necessary.

The steps required to implement this logical design are below, followed by the steps in detail.

To implement transactional replication with SQL Server

  1. Create an empty database on each subscriber.

  2. Create and share the snapshot folder on the distributor.

  3. Create and share a synchronization folder on the publisher.

  4. Prepare the schema for replication.

  5. Configure the distributor.

  6. Configure the publisher, and publish the production database.

  7. Configure and initialize subscribers.

  8. Create and execute a login synchronization job.

  9. Script and copy jobs, alerts, and operators.

  10. Copy Data Transformation Services (DTS) packages.

Creating an Empty Database on Each Subscriber

If you do not have a current script for the production database, use SQL Server Enterprise Manager to generate a script of the production database. Create an empty database on each subscriber that is the same size as the production database and that uses the same drive letters and paths. If you use a different drive letter or path on a subscriber, edit the script to point to the appropriate drive letter and path before you use it on that subscriber.

To create a Transact-SQL script of the production database

  1. In SQL Server Enterprise Manager, connect to the SQL Server instance containing the production database, and then expand Databases.

  2. Right-click the production database, point to All Tasks, and then click Generate SQL Script.

  3. In Generate SQL Scripts, click Show All on the General tab, and then click the Options tab.

  4. On the Options tab, select the Script Database check box, and then click OK.

  5. In Save As, type a file name in the File Name box, and then click Save.

  6. Click OK to close the message box.

To create the empty database on each subscriber

  1. Use SQL Query Analyzer to connect to SQL Server on a subscriber.

  2. In SQL Query Analyzer, click Open on the File menu.

  3. In Open Query File, browse to and click the Transact-SQL script created in the previous procedure, and then click Open.

  4. In the Query window, review and edit the script as necessary, and then click Execute Query on the toolbar.

  5. Verify that the script is executing successfully, and then close SQL Query Analyzer.

Note: Repeat this procedure on each subscriber.

Creating and Sharing the Snapshot Folder

On the subscriber/distributor, create a snapshot folder. The snapshot agent uses the snapshot folder to store the snapshot of the production database. The distribution agent uses this snapshot to populate the subscription database on each subscriber. These replication agents are SQL Server jobs that run in the security context of the SQL Server Agent service. The SQL Server Agent service must have permission to read and write to the snapshot folder. Use a mirrored drive that is not used for data or log files. Be sure only authorized users have access to the snapshot folder.

To create the shared snapshot folder on the subscriber/distributor

  1. Log on or connect to the subscriber/distributor with a user account that is a member of the Administrators local group on that computer.

  2. On a drive not used for data or log files, create a folder to store the production database snapshot.

  3. Grant full-control, file-level access to this folder to the domain user account that the SQL Server Agent service uses and to the Administrators local group. Remove the Everyone group from the file level permissions list.

  4. Share the new folder.

    Use a share name that has no spaces. If the share name has a space, you will have to put double quotation marks around it when you reference it in command prompt utilities.

  5. Grant full-control, share-level access through this share to the domain user account that the SQL Server Agent service uses and to the Administrators local group. Remove the Everyone group from the share level permissions list.

Creating and Sharing the Synchronization Folder

On the publisher, create and share a synchronization folder. This folder is used to store login information output by the bulk copy program (BCP) utility, the jobs script, the alerts script, and the operators script. You use the BCP output and these Transact-SQL scripts to synchronize objects between the publisher and each subscriber. The SQL Server Agent service must have permission to read and write to the synchronization folder. Use a mirrored drive that is not used for data or log files. Be sure only authorized users have access to the synchronization folder.

To create and share the synchronization folder on the publisher

  1. Log on or connect to the publisher by using a user account that is a member of the Administrators local group on that computer.

  2. On a drive not used for data or log file, create a folder to store the synchronization information.

  3. Grant full-control, file-level access to this folder to the domain user account used by the SQL Server Agent service and members of the Administrators local group. Remove the Everyone group from the file level permissions list.

  4. Share this new folder.

    Use a share name without a space. If you create a share name that has a space in its name, you will have to put double quotation marks around the share name when referencing it in command prompt utilities.

  5. Grant full–control, share-level access through this share to the domain user account used by the SQL Server Agent service and members of the Administrators local group. Remove the Everyone group from the share level permissions list.

Preparing the Schema for Replication

Before configuring transactional replication, you must prepare the schema for transactional replication. In a database that you want to publish, you must eliminate unresolved references in views and user-defined functions. You must set identity columns and triggers to Not for Replication and also ensure that all literals are defined with apostrophes rather than quotation marks. In addition, primary keys must be defined for every table. Finally, you must create Transact-SQL scripts for all tables with identity columns or time stamp data types.

Note: Encrypted stored procedures, views, triggers, and user-defined objects cannot be published because SQL Server transaction replication cannot recreate these objects on a subscriber.

You can only make schema changes to a published table in a database through the Replication Publication Properties dialog box in SQL Server Enterprise Manager or through special replication stored procedures. Changes made using these tools are propagated to each subscriber by the distribution agent. However, if you add a column, you need to reinitialize the subscription database at each subscriber.

Do not make schema changes to published tables using the ALTER TABLE statement or by using SQL Server Enterprise Manager. Changes made to the schema of a published table using these tools will not be propagated to subscribers. If you add an existing column to a publication, the subscribers must be re-initialized, which can impact their use as reporting databases. For more information about making schema changes, see Schema Changes on Publication Databases in SQL Server Books Online.

Note: If you need to change the schema of a published table, thoroughly test the changes on a non-production server first. This is a very high-risk change for applications in a high-availability environment.

To eliminate unresolved references in stored procedures, views, and user-defined functions

  1. Using SQL Server Enterprise Manager or SQL Query Analyzer, connect to the production database on the publisher and identify all stored procedures, views, and user-defined functions that reference objects that do not exist in the production database.

    The creation of the initial snapshot will fail if these references cannot be resolved.

  2. Eliminate all unresolved references by using one or more of the following methods:

    • Create the missing objects.

    • Alter the definition of the views and user-defined functions that reference non-existent objects to point to existing objects.

    • Delete the views and user-defined functions referencing non-existent objects.

Note: If unresolved references are detected during the initialization of the subscription database on a subscriber, the following error message will appear: "Cannot use empty object (or) column names. Use a single space if necessary."

To set identity columns to Not for Replication

  1. Using SQL Query Analyzer, connect to the production database on the publisher and use the following Transact-SQL script to report which columns are identity columns:

    2. 



SELECT O.name, C.name from sysobjects O
INNER JOIN syscolumns C on O.id = C.id
WHERE o.type='U' AND objectproperty (o.id, 'TABLEHASIDENTITY') = 1
AND columnproperty (o.id, c.name, 'IsIdentity') = 1
ORDER BY O.name, C.name



The schema for these tables must be modified before the schema is recreated on a subscriber by using the **Not for Replication** attribute. This attribute causes the value from the production database to be inserted into the subscription database rather than incrementing the identity value in the subscription database.



    

  1. Set the Not for Replication attribute for the identity column in each identified table using one of the following methods:
    

      

    • In SQL Query Analyzer, use the ALTER TABLE statement to add the Not for Replication attribute to the identity column in each identified table.
      

      • 

    • In SQL Server Enterprise Manager, right-click each table, and then click Design Table to change the identity property to Yes (Not for Replication).
      

      • 

    

    2. 



To set triggers to Not for Replication


    

  1. Using SQL Query Analyzer, connect to the production database on the publisher and use the following Transact-SQL script to report which tables have triggers:
    

    2. 



SELECT O.name from sysobjects O
WHERE
o.type='U' AND (objectproperty (o.id, 'TableHasDeleteTrigger') = 1
OR objectproperty (o.id, 'TableHasInsertTrigger') = 1
OR objectproperty (o.id, 'TableHasUpdateTrigger') = 1)
ORDER BY O.name



The schema for these triggers must be modified before the schema is recreated on a subscriber by using the **Not for Replication** attribute. This attribute disables the firing of the trigger on the subscriber when data is inserted into the subscription database by the distribution agent.



    

  1. Set the Not for Replication attribute for each trigger in each identified table using one of the following methods:
    

      

    • In SQL Query Analyzer, use the ALTER TRIGGER statement to add the Not for Replication attribute to each trigger in each identified table.
      

      • 

    • In SQL Server Enterprise Manager, right-click each table, point to All Tasks, and then click Manage Triggers to add the Not for Replication attribute to each trigger in each identified table.
      

      • 

    

    2. 



To change literal definitions to use apostrophes rather than quotation marks


    

  1. Using SQL Server Enterprise Manager, connect to the production database on the publisher and generate a Transact-SQL script for all stored procedures and views. Then use a text editor to determine the views and stored procedures that define literals with quotation marks rather than apostrophes.
    

    These views and stored procedures must be changed to use apostrophes or an error will appear that states that there is an invalid column (but with no context for the error) when the view or stored procedure is created on each subscriber.
    

    2. 

  2. Change the literal definitions from quotation marks to apostrophes in each identified view or stored procedure by using one of the following methods:
    

      

    • In SQL Query Analyzer, use the ALTER VIEW and ALTER PROCEDURE statements to modify the identified views and stored procedures.
      

      • 

    • In SQL Server Enterprise Manager, double-click each identified view and stored procedure, and then modify the literal definitions.
      

      • 

    

    3. 



To define a primary key for a table


    

  1. Using SQL Query Analyzer, connect to the production database on the publisher and use the following Transact-SQL script to report which tables do not have primary keys:
    

    2. 



SELECT O.name from sysobjects O
WHERE o.type='U' AND objectproperty (o.id, 'TableHasPrimaryKey') = 0
ORDER BY O.name



You must create a primary key for each of these tables.



    

  1. Create a primary key for each identified table by using one of the following methods:
    

      

    • In SQL Query Analyzer, use the ALTER TABLE statement to modify the identified table.
      

      • 

    • In SQL Server Enterprise Manager, right-click each identified table, click Design Table, and then click Set Primary Key on the toolbar.
      

      • 

    

    2. 



To create Transact-SQL scripts for tables with identity columns or time stamps


    

  1. Using SQL Query Analyzer, connect to the production database on the publisher and use the following Transact-SQL script to report which tables have identity columns or time stamps:
    

    2. 



SELECT O.name from sysobjects O
WHERE o.type='U' AND (objectproperty (o.id, 'TableHasIdentity') = 1
OR objectproperty (o.id, 'TableHasTimestamp') = 1)
ORDER BY O.name



You must create a Transact-SQL script for each of these tables. You supply the subscriber/distributor with these scripts so that the distribution agent can use them when creating tables on a subscriber. These scripts are required to ensure that the subscription database will function properly if it is ever used as the new production database.



    

  1. In SQL Server Enterprise Manager, connect to SQL Server on the publisher, and then expand Databases.
    

    2. 

  2. Right-click the production database, point to All Tasks, and then click Generate SQL Script.
    

    3. 

  3. In Generate SQL Scripts, click Show All on the General tab.
    

    4. 

  4. In Objects to Script, select the All Defaults and the All User-Defined Data Types check boxes.
    

    5. 

  5. In the Objects on < production database > list, select each identified table, and then click Add.
    

    Each identified table should now appear in the Objects to Be Scripted list.
    

    6. 

  6. On the Formatting tab, select the following check boxes.
    

      

    • Generate the CREATE <object> Command for Each Object
      

      • 

    • Generate the DROP <object> Command for Each Object
      

      • 

    

    7. 

  7. In the Table Scripting Options box on the Options tab, clear each of the following check boxes.
    

      

    • Script Indexes
      

      • 

    • Script Full-Text Indexes
      

      • 

    • Script Triggers
      

      • 

    • Script Primary Keys, Foreign Keys, Defaults, and Check Constraints
      

      • 

    

    These options are configured later by the snapshot.
    

    8. 

  8. In the File Options box on the Options tab, verify that the following options are selected.
    

      

    • International Text (Unicode)
      

      • 

    • Create One File
      

      • 

    

    9. 

  9. Click OK.
    

    10. 

  10. In Save As, browse to the snapshot folder location on the subscriber/distributor, type a name for the Transact-SQL script, and then click Save.
    

    Be sure to save this script to the shared snapshot folder. This script must be accessible to the distribution agent along with the initial snapshot.
    

    11. 



To create Transact-SQL scripts for user-defined data types and defaults


Note: If you make use of user-defined data types, you must create a Transact-SQL script containing their definitions. This step is not necessary if you have followed the steps to create a script for tables with identity columns or timestamps. You will provide this script when configuring the publication properties, which will supply it to the subscriber/distributor so that the distribution agent can use them when creating the schema on a subscriber.


    

  1. In SQL Server Enterprise Manager, connect to SQL Server on the publisher, and then expand Databases.
    

    2. 

  2. Right-click the production database, point to All Tasks, and then click Generate SQL Script.
    

    3. 

  3. In Generate SQL Scripts, click Show All on the General tab.
    

    4. 

  4. In Objects to Script, select the All User-Defined Data Types check boxes.
    

    5. 

  5. On the Formatting tab, select the following check boxes.
    

      

    • Generate the CREATE <object> Command for Each Object
      

      • 

    • Generate the DROP <object> Command for Each Object
      

      • 

    

    These options are configured later by the snapshot.
    

    6. 

  6. In the File Options box on the Options tab, verify that the following options are selected:
    

      

    • International Text (Unicode)
      

      • 

    • Create One File
      

      • 

    

    7. 

  7. Click OK.
    

    8. 

  8. In Save As, browse to the snapshot folder location on the subscriber/distributor, type a name for the Transact-SQL script, and then click Save.
    

    Be sure to save this script to the shared snapshot folder. This script must be accessible to the distribution agent along with the initial snapshot.
    

    9. 



Configuring the Distributor


Transactional replication requires that a server be designated as the distributor. When you use transactional replication as a high availability solution, use a subscriber as the distributor. This reduces the load on the publisher and, in most environments, will not place too much of a load on the subscriber. Use SQL Server Enterprise Manager to configure the distributor on the selected subscriber. When you configure the distributor, you designate the server, specify a snapshot folder, create the distribution database, and enable publishers.


To configure the distributor


    

  1. Using SQL Server Enterprise Manager, connect to the SQL Server instance that will function as the distributor (usually a subscriber), and then click Databases.
    

    You must connect as a member of the System Administrators server role.
    

    2. 

  2. On the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distribution.
    

    3. 

  3. On the Welcome to the Configure Publishing and Distribution Wizard page, click Next.
    

    4. 

  4. On the Select Distributor page, click Next to use the server as its own distributor.
    

    If the SQL Server Agent service on the server uses the local system account, you will be prompted to change this account to a domain user account that is a member of the System Administrators server role. You must use a domain user account. Using the same domain user account for all participating servers makes setup and administration of transactional replication easier.
    

    If SQL Server Agent is not configured to start automatically on the distributor, you are asked to configure the SQL Server Agent service to start automatically. Configure the SQL Server Agent service to start automatically on all participating servers (unless you are deploying transactional replication on an MSCS cluster).
    

    5. 

  5. On the Specify Snapshot Folder page, type or browse to the share that you created for the snapshot folder, and then click Next.
    

    Specify a Universal Naming Convention (UNC) path for the snapshot folder rather than a drive path. Do not use the default share. This share is accessible only to members of the Administrators local group.
    

    6. 

  6. On the Customize the Configuration page, click Yes, Let Me Set the Distribution Database Properties; Enable Publishers; or Set the Publishing Settings, and then click Next.
    

    7. 

  7. On the Provide Distribution Database Information page, type or browse to the folder you want to use for the data file, type or browse to the folder you want to use for the log file, and then click Next.
    

    8. 

  8. On the Enable Publishers page, clear the check box for this subscriber, and then select the check box for the publisher in the Registered Servers list.
    

    An informational page appears describing the information you must provide to enable a publisher to use this distributor. Close the page after you have read it.
    

    9. 

  9. In Publisher Properties for the publisher, click OK to accept the default properties.
    

    10. 

  10. On the Enable Publishers page, click Next.
    

    11. 

  11. On the Completing the Configure Publishing and Distribution Wizard page, click Finish.
    

    The wizard configures the distributor. When complete, close the message. Review the information that appears about the Replication Monitor, and then close the message.
    

    12. 



Publishing the Production Database


Transactional replication requires the production database to be published. Use SQL Server Enterprise Manager to configure the publisher and to publish the production database. When you configure the publisher, you designate the distributor for the publisher. When you publish the production database, you specify transactional replication, the types of subscribers, the articles in the publication, and the name of the publication. After the production database is published, you modify the properties of the publication to use the identity property and time-stamp data type scripts that you created earlier.


To configure the publisher


    

  1. Using SQL Server Enterprise Manager, connect to the SQL Server instance containing the production database, and then click Databases.
    

    You must connect as a member of the System Administrators server role.
    

    2. 

  2. On the Tools menu, point to Replication, and then click Create and Manage Publications.
    

    3. 

  3. In Create and Manage Publications on < publisher >, click Create Publication.
    

    4. 

  4. On the Welcome to the Create Publication Wizard page, click Next.
    

    Do not select the Show Advanced Options in This Wizard check box. Advanced options are required only for publications that support updatable and transformable subscriptions. These options are not used for this high-availability solution.
    

    5. 

  5. On the Select Distributor page, click Use the Following Server (the Selected Server Must Already Be Configured as a Distributor), click the server you configured as the distributor, and then click Next.
    

    6. 



To begin publishing the production database


    

  1. On the Choose Publication Database page, select the production database in the Databases list, and then click Next.
    

    2. 

  2. On the Select Publication Type page, click Transactional Publication, and then click Next.
    

    3. 

  3. On the Specify Subscriber Types page, ensure that only the Servers Running SQL Server 2000 check box is selected, and then click Next.
    

    When transactional replication is used as a high-availability solution, all participating servers should be running the same version of SQL Server.
    

    4. 

  4. In the Object Type list on the Specify Articles page, select the Publish All check box for Table, the Publish All check box for Stored Procedures, and the Publish All check box for Views.
    

    5. 



To modify table article defaults


    

  1. Click Article Defaults.
    

    2. 

  2. In Default Article Type, click Table Articles, and then click OK.
    

    3. 

  3. On the Snapshot tab in Default Table Article Properties, modify the properties as follows:
    

      

    1. Click the Keep the Existing Table Unchanged option button.
      

      2. 

    2. Select the Include Declared Referential Integrity check box.
      

      3. 

    3. Select the Cluster Indexes check box (the default).
      

      4. 

    4. Select the User Triggers check box.
      

      5. 

    5. Select the Extended Properties check box.
      

      6. 

    6. Select the Collation check box.
      

      7. 

    7. Clear the Convert User-Defined to Base Data Types check box.
      

      8. 

    8. Click OK, and then click Yes to update all published articles with these new defaults.
      

      9. 

    

    4. 

  4. On the Specify Articles page, click Next.
    

    5. 



To finish publishing the production database


    

  1. On the Article Issues page, review the warnings, and then click Next.
    

    2. 

  2. On the Select Publication Name and Description page, type a unique descriptive publication name and description, and then click Next.
    

    3. 

  3. On the Customize the Properties of the Publication page, click No, Create the Publication as Specified, and then click Next.
    

    4. 

  4. On the Completing the Create Publication Wizard page, click Finish.
    

    The wizard configures the publisher and publishes the production database.
    

    If you created custom Transact-SQL scripts for objects with the identity property or time-stamp data types, do not click Close when finished.
    

    5. 



To modify the publication properties to use the custom Transact-SQL scripts for identity property, time-stamp data types, and user-defined data types


    

  1. After the publication is created, click Publication Properties if you created custom Transact-SQL scripts for objects with the identity property or time-stamp data type; otherwise, click Close.
    

    2. 

  2. In Additional Scripts on the Snapshot tab, in the Before Applying the Snapshot, Execute This Script text box, type or browse to the custom script you created and saved earlier to the snapshot folder, and then click OK.
    

    Be sure you use a UNC path to the snapshot folder. If you use a drive path that is local to the subscriber/distributor, the distribution agent, which runs on the subscriber/distributor, will not be able to access and execute this script.
    

    3. 

  3. In Create and Manage Publications, click Close.
    

    4. 



Configuring and Initializing Subscribers


After the distributor and publisher are configured and the production database is published, you are ready to configure the subscribers. Configure a push subscription with continuously running distribution agents to minimize the transactional latency between the publisher and each subscriber. Use SQL Server Enterprise Manager to connect to the publisher and to configure the push subscriptions. When you configure a subscriber, you specify the publication, the subscription database, and the distribution schedule. You also initialize the subscription database by creating and applying the initial snapshot of the production database. Finally, if you manually created tables to preserve columns with a time-stamp data type, you modify the insert and update stored procedures on the subscriber to properly handle the time-stamp data.


To configure a subscriber


    

  1. Using SQL Server Enterprise Manager, connect to the SQL Server instance containing the production database, and then click Databases.
    

    You must connect as a member of the System Administrators server role.
    

    2. 

  2. On the Tools menu, point to Replication, and then click Push Subscriptions to Others.
    

    3. 

  3. In Create and Manage Publications on < publisher >, expand the production database in the Databases and Publications list, select the publication of that database, and then click Push New Subscription.
    

    4. 

  4. On the Welcome to the Push Subscription Wizard page, click Next.
    

    Do not select the Show Advanced Options in This Wizard check box. Advanced options are required only for updatable and transformable subscriptions, which are not used when transactional replication is implemented as a high-availability solution.
    

    5. 

  5. On the Choose Subscribers page, select one or more subscribers from the Subscribers list, and then click Next.
    

    6. 

  6. On the Choose Destination Database page, click Next to use the same database name as the production database.
    

    To ensure application compatibility in the event of a role change, the name of the subscription database must be identical to the name of the production database.
    

    7. 

  7. On the Set Distribution Agent Schedule page, click Continuously, and then click Next.
    

    This option ensures minimal latency between committed transactions at the publisher and their propagation to the subscriber.
    

    8. 

  8. On the Initialize Subscription page, click Yes, Initialize the Schema and Data; select the Start the Snapshot Agent to Begin the Initialization Process Immediately; and then click Next.
    

    Doing so locks the production database while the snapshot is being created. If you want to wait to create the snapshot, do not select the check box to start the process immediately. After you finish the wizard, you can schedule the snapshot agent to run at a specific time. Select a time that minimizes the impact on production database users.
    

    9. 

  9. On the Start Required Services page, click Next.
    

    The SQL Server Agent on the server functioning as the distributor must be running for the push subscription to run.
    

    10. 

  10. On the Completing the Push Subscription Wizard page, click Finish.
    

    The wizard configures the subscribers and creates the push subscriptions. Click Close when finished, and then close Create and Manage Publications.
    

    11. 



Note: During the initialization of subscriptions for large publication, you may see the following error in the Replication Monitor: The agent is suspect. No activity reported within the last 10 minutes." This error occurs due to the length of time required to create the script of the production database, initialize the subscription database, and insert the data into the subscription database on the subscriber. You can ignore this error.


To modify insert procedures for time-stamp data types


    

  1. Using SQL Query Analyzer, connect to the subscription database on the subscriber and use the following Transact-SQL script to report which tables have time stamps:
    

    2. 



SELECT O.name FROM sysobjects O
WHERE o.type='U' AND objectproperty (o.id, 'TableHasTimestamp') = 1
ORDER BY O.NAME



    

  1. Using SQL Enterprise Manager, connect to the subscription database on the subscriber and open each insert stored procedure that needs to be changed.
    

    The name of each insert stored procedure is sp_MSins_<tablename>.
    

    2. 

  2. Locate the VALUES clause of the INSERT statement. Replace the time-stamp parameter with the DEFAULT constant.
    

    The time-stamp parameter is a variable with a data type of binary (8). The position of this parameter in the INSERT statement is different for different tables.
    

    This change preserves the time-stamp value when the data is inserted into the table on the subscriber. The following Transact-SQL script illustrates this change. The original VALUES clause is commented out and replaced with a modified VALUES clause.
    

    3. 



CREATE PROCEDURE sp_MSins_SpecialReplText
@c1 int,@c2 uniqueidentifier,@c3 binary(8)
AS
BEGIN
INSERT INTO SpecialReplText(ID, UID, TS)
--VALUES (@c1, @c2, @c3)
-- @c3 is time stamp parameter
VALUES
(@c1, @c2, DEFAULT)
-- Removed parameter @c3, substituted DEFAULT
END



    

  1. Save the procedure.
    

    2. 

  2. Repeat this process for each insert stored procedure that needs to be changed.
    

    3. 

  3. Using SQL Enterprise Manager, script out all the changed stored procedures for use during a role change. Apply the script file to all subscribers.
    

    4. 



Creating and Executing a Login Synchronization Job


Important: Apply the changes specified in article 310882, "BUG: sp_resolve_logins Stored Procedure Fails If Executed During Log Shipping Role Change " in the Microsoft Knowledge Base at https://support.microsoft.com/default.aspx?scid=kb;en-us;310882&sd=tech to each server before proceeding. The article discusses how you must edit the stored procedure sp_resolve_logins in the master database. Locate the following text in the procedure:


SELECT   *
INTO     #sysloginstemp
FROM     syslogins
WHERE    sid = 0x00



Replace it with the following text:


SELECT   *
INTO     #sysloginstemp
FROM     master.dbo.syslogins
WHERE    sid = 0x00



After you have set up transactional replication, you must synchronize logins between the publisher and each subscriber.


You must synchronize logins before clients can use the subscription database. Synchronizing logins is a manual process requiring you to transfer logins from the publisher to each subscriber, export login information from the syslogins table on the publisher to a file, and synchronize logins and user accounts on the subscriber.


System tables containing logins, passwords, database permissions, users, and user roles do not participate in replication. The procedures below create a login synchronization job for each subscriber to ensure that the logins remain synchronized between the publisher and the subscriber, and SQL Server logins remain synchronized with user accounts. Run this job when you initially set up each subscriber and each time you change logins on the publisher. Server Roles are not synchronized between servers by these procedures, so all users in system roles should be managed manually at each server using a script created by you or directly in Enterprise Manager.


To create a transfer logins task in the transfer logins DTS package


Note: If you have created a login for SQL Server by using a local user account in Windows 2000, this transfer logins task will fail because a local login is not valid on any other server. In a high-availability environment, only create logins for domain users, or create SQL Server logins.


    

  1. Using SQL Server Enterprise Manager, connect to SQL Server on the publisher.
    

    2. 

  2. Right-click Data Transformation Services, and then click New Package.
    

    3. 

  3. On the Task menu, click Transfer Logins Task.
    

    4. 

  4. On the Source tab in Transfer Logins Properties, browse to or type the name of the publisher in the Source Server box, and verify that Use Windows Authentication is selected.
    

    5. 

  5. On the Destination tab, browse to or type the name of a subscriber in the Destination Server box, and verify that Use Windows Authentication is selected.
    

    6. 

  6. On the Logins tab, click Logins for Selected Databases.
    

    7. 

  7. In the Databases list, select the check box for the production database, and then click OK.
    

    Do not close the transfer logins DTS package.
    

    8. 



To create a connection to the subscriber in the transfer logins DTS package


    

  1. On the Connection menu, click Microsoft OLE DB Provider for SQL Server.
    

    2. 

  2. In Connection Properties, name the new connection < subscriber >.
    

    3. 

  3. In the server list, click the subscriber to which you are transferring the logins, and verify that Use Windows Authentication is selected.
    

    4. 

  4. In the Database list, select the database you are replicating, and then click OK.
    

    Do not close the DTS package.
    

    5. 



To create a Copy SQL Server Objects task in the transfer logins DTS package


    

  1. On the Task menu, click Copy SQL Server Objects Task.
    

    2. 

  2. On the Source tab, type Copy users for a description.
    

    3. 

  3. On the Source tab in Copy SQL Server Objects Task Properties, in the Server list, select the publishing server.
    

    4. 

  4. On the Source tab, verify that Use Windows Authentication is selected.
    

    5. 

  5. On the Source tab in Copy SQL Server Objects Task Properties, in the Database list, select the database being published.
    

    6. 

  6. On the Destination tab in Copy SQL Server Objects Task Properties, in the Server list, select the subscriber server.
    

    7. 

  7. On the Destination tab, browse to or type the name of a subscriber in the Destination Server box, and verify that Use Windows Authentication is selected.
    

    8. 

  8. On the Destination tab in Copy SQL Server Objects Task Properties, in the Database list, select the subscriber database.
    

    9. 

  9. On the Copy tab, select the Create destination objects and Drop destination objects first check boxes. Clear all other check boxes on the Copy tab.
    

    10. 

  10. Click Select objects.
    

    11. 

  11. On the Select Objects page, click Uncheck, and then click OK.
    

    12. 

  12. Click Options, and then on the Options page, select the Copy database users and database roles, Copy object-level permissions, and Use quoted identifiers when copying objects check boxes. Click OK twice.
    

    13. 

  13. Click the Transfer Logins Task, press the Ctrl key, and then click the Copy Users.
    

    14. 

  14. On the Workflow menu, click On Success.
    

    Do not close the transfer logins DTS package.
    

    15. 



To create an Execute SQL task in the transfer logins DTS package


    

  1. On the Task menu, click Execute SQL Task.
    

    2. 

  2. In Execute SQL Task Properties, type Resolve Logins in the Description text box.
    

    3. 

  3. Verify that the Existing connection list displays the name of the connection created in the preceding procedure.
    

    4. 

  4. In the SQL Statement box, type the following Transact-SQL script:
    

    5. 



sp_resolve_logins '<database name>', '\<publisher>&lt;share name of
synchronization folder>', 'syslogins.dat'



    

  1. Click OK.
    

    2. 

  2. Click Copy Users Task, press the Ctrl key, and then click Resolve Logins.
    

    3. 

  3. On the Workflow menu, click On Success.
    

    A green-and-white striped workflow arrow appears that points toward the Resolve Logins task.
    

    Do not close the DTS package.
    

    4. 



To save the transfer logins DTS package


    

  1. On the Package menu, click Save As.
    

    2. 

  2. In Save DTS Package, type Transfer_Logins_< subscriber > in the Package Name box, and then click OK.
    

    The transfer logins package is saved to the msdb database on the publisher.
    

    3. 

  3. Close the transfer logins DTS package.
    

    4. 



Note: Create a separate transfer logins package on the publisher for each subscriber.


To create a login synchronization job for a subscriber


    

  1. Using SQL Server Enterprise Manager, connect to the publisher.
    

    2. 

  2. Expand Management, expand SQL Server Agent, right-click Jobs, and then click New Job.
    

    3. 

  3. On the General tab in New Job Properties, type Synchronize Logins < subscriber > in the Name box.
    

    4. 

  4. In the Owner list, select the domain user account that the SQL Server Agent service uses.
    

    5. 



To create a BCP job step for the synchronize logins job


    

  1. On the Steps tab, click New.
    

    2. 

  2. On the General tab in New Job Step, type BCP Out in the Step Name box.
    

    3. 

  3. In the Type list, select Operating System Command (CmdExec).
    

    4. 

  4. In the Command text box, type the following command as follows:
    

    5. 



BCP master..syslogins out \<publisher>&lt;share name of
synchronization folder>\syslogins.dat /N /S <publisher> /T



**Note**: If the share name of the synchronization folder has a space in it, you must put double quotation marks around the entire path for the output file.



    

  1. Click OK.
    2. 



To create a transfer logins job step for the synchronize logins job


    

  1. On the Steps tab, click New.
    

    2. 

  2. On the General tab in New Job Step, type Transfer Logins < subscriber >in the Step Name box.
    

    3. 

  3. In the Type list, select Operating System Command (CmdExec).
    

    4. 

  4. In the Command box, type the following command:
    

    5. 



DTSRun /S<publisher> /E /NTransfer_Logins_<subscriber>



    

  1. Click OK.
    

    2. 

  2. Click OK to close the synchronize logins job.
    

    3. 



Note: Create a separate synchronize logins job on the publisher for each subscriber.


To execute the synchronize logins job for a subscriber


    

  1. Using SQL Server Enterprise Manager, connect to the publisher.
    

    2. 

  2. Expand Management, expand SQL Server Agent, and then expand Jobs.
    

    3. 

  3. Right-click Synchronize Logins < subscriber >, and then click Start Job.
    

    4. 

  4. In Start Job on < publisher >, click Start to execute all steps in the synchronize logins job.
    

    5. 



Note: Execute the synchronize logins job for each subscriber whenever you change logins on the publisher.


Scripting and Copying Jobs, Alerts, and Operators


The msdb database, not the production database, stores SQL Server jobs, alerts, and operators. If you have jobs, alerts, or operators that run on the publisher on which your production database relies, manually copy these jobs, alerts, and operators to each subscriber. You cannot simply back up the msdb database and restore it to a subscriber. Doing so overwrites all jobs, alerts, and operators in the msdb database on the subscriber, including all of the transactional replication jobs on that subscriber. You can copy jobs, alerts, and operators by using SQL Server Enterprise Manager to generate scripts for them and then applying the scripts to each subscriber. You must complete these tasks before users connect to a subscriber that is promoted to become the new primary server.


Use the following procedures to copy jobs, alerts, or operators that the production database needs to each subscriber.


To script all jobs on the publisher


    

  1. In SQL Server Enterprise Manager, connect to SQL Server on the publisher, expand Management, and then expand SQL Server Agent.
    

    2. 

  2. Right-click Jobs, point to All Tasks, and then click Generate SQL Script.
    

    3. 

  3. In Generate SQL Script, browse to or type the path to the synchronization folder on the publisher and then type a file name for the jobs script using the extension .sql.
    

    4. 

  4. Verify that the International Text (Unicode) check box is selected in the File Format box and that the Replace Job If It Exists check box is selected in the SQL Generation Options box.
    

    5. 

  5. Accept the default for the TSQL Batch Separator.
    

    6. 

  6. Click OK to create a Transact-SQL script for all jobs on the publisher.
    

    7. 

  7. Use SQL Query Analyzer to edit the jobs script to remove all transactional replication jobs.
    

    8. 



To execute the jobs script on each subscriber


    

  1. Using SQL Query Analyzer, connect to SQL Server on a subscriber.
    

    2. 

  2. On the File menu, click Open.
    

    3. 

  3. In Open Query File, type or browse to the jobs script in the shared synchronization folder on the publisher, and then open the jobs script.
    

    4. 

  4. Execute the jobs script, and then close SQL Query Analyzer.
    

    5. 

  5. In SQL Server Enterprise Manager, connect to the appropriate instance on the subscriber, expand Management, expand SQL Server Agent, and then click Jobs.
    

    6. 

  6. In the details pane, review the jobs that were created. Disable those jobs until they are needed after a role change.
    

    Note: Repeat this procedure on each subscriber.
    

    7. 



To script all alerts on the publisher


    

  1. In SQL Server Enterprise Manager, connect to SQL Server on the publisher, expand Management, and then expand SQL Server Agent.
    

    2. 

  2. Right-click Alerts, point to All Tasks, and then click Generate SQL Script.
    

    3. 

  3. In Generate SQL Script, browse to or type the path to the synchronization folder on the publisher and then type a file name for the alerts script using the extension .sql.
    

    4. 

  4. Verify that the International Text (Unicode) check box is selected in the File Format box
    

    5. 

  5. Verify that the following check boxes are selected in the SQL Generation Options box.
    

      

    • Replace Alert If It Exists
      

      • 

    • Include the Name of the Job Executed by the Alert
      

      • 

    

    6. 

  6. Clear the Include Notifications Sent by the Alert to the Operator check box.
    

    Note: Notifications are copied when the operators are copied to ensure that the operators will exist prior to creating the notification.
    

    7. 

  7. Accept the default for the TSQL Batch Separator.
    

    8. 

  8. Click OK to create a Transact-SQL script for all alerts on the publisher.
    

    9. 



Note: A bug in the scripting routine exports all Net Send notifications as E-mail notifications. If you use Net Send in any notifications, edit the script file and change the notification method in each sp_add_notification statement from @notification_method =1 to @notification_method =4 to configure the notification to use Net Send.Note: If any alerts use user-defined messages, you must create those messages on the subscriber before applying this script. It is recommended that you always use a script to create user-defined messages so you can recreate the messages on any SQL Server instance.


To execute the alerts script on each subscriber


    

  1. Using SQL Query Analyzer, connect to SQL Server on a subscriber.
    

    2. 

  2. On the File menu, click Open.
    

    3. 

  3. In Open Query File, type or browse to the alerts script in the shared synchronization folder on the publisher, and open the alerts script.
    

    4. 

  4. Execute the alerts script, and then close SQL Query Analyzer.
    

    5. 

  5. In SQL Server Enterprise Manager, connect to the appropriate instance on the subscriber, expand Management, expand SQL Server Agent, and then click Alerts.
    

    In the details pane, review the alerts that were created.
    

    6. 



Note: Repeat this procedure on each subscriber.


To script all operators on the publisher


    

  1. In SQL Server Enterprise Manager, connect to SQL Server on the publisher, expand Management, and then expand SQL Server Agent.
    

    2. 

  2. Right-click Operators, point to All Tasks, and then click Generate SQL Script.
    

    3. 

  3. In Generate SQL Script, browse to or type the path to the synchronization folder on the publisher and then type a file name for the operators script using the extension .sql.
    

    4. 

  4. Verify that the International Text (Unicode) check box is selected in the File Format box.
    

    5. 

  5. Verify that the following check boxes are selected in the SQL generation options box.
    

      

    • Replace Operator If It Exists
      

      • 

    • Include Notifications Sent by Alert to the Operator
      

      • 

    

    6. 

  6. Accept the default for the TSQL Batch Separator.
    

    7. 

  7. Click OK to create a Transact-SQL script for all operators on the publisher.
    

    8. 



To execute the operators script on each subscriber


    

  1. Using SQL Query Analyzer, connect to SQL Server on a subscriber.
    

    2. 

  2. On the File menu, click Open.
    

    3. 

  3. In Open Query File, type or browse to the operators script in the shared synchronization folder on the publisher, and open the operators script.
    

    4. 

  4. Execute the operators script, and then close SQL Query Analyzer.
    

    5. 

  5. In SQL Server Enterprise Manager, connect to the appropriate instance on the subscriber, expand Management, expand SQL Server Agent, and then click Operators.
    

    6. 

  6. In the details pane, review the operators that were created.
    

    7. 



Note: Repeat this procedure on each subscriber.


Copying DTS Packages


DTS packages can be stored in the msdb database or in the file system. If you have DTS packages that run on the publisher on which your production database relies, you must manually copy these DTS packages to each subscriber. You cannot simply back up the msdb database and restore it to a subscriber. Doing so overwrites all jobs, alerts, operators, and DTS packages in the msdb database on the subscriber. You can open DTS packages saved to the msdb database on the publisher and save them to each subscriber. For DTS packages saved to the file system on the publisher, simply copy these DTS packages to a folder on each subscriber. This task must be completed before users connect to a subscriber that is promoted to become the new primary server.


Use the following procedure to copy DTS packages that the production database needs to each subscriber.


To copy DTS packages


    

  1. For each DTS package stored in the file system on the publisher, copy the DTS package to each subscriber by using Windows Explorer.
    

    When copying DTS packages to each subscriber, use the same drive and path as the publisher is using. Doing so will eliminate potential path problems.
    

    2. 

  2. For each DTS package stored in the msdb database on the publisher, use SQL Server Enterprise Manager to open the DTS package, and then save it to the msdb database on each subscriber.
    

    3. 



Note: To ensure that DTS packages execute properly on a subscriber, use an alias or the dynamic properties task. For more information, see "Redirecting Client Network Traffic to a Promoted Secondary Server" in Planning Guide Chapter 5, "Minimizing Downtime by Using Redundant Components."


Changing the Role of a Subscriber


To change the role of a subscriber when the publisher fails or is taken offline, disable all replication jobs, and then use the subscription database as the new production database. Ensure that all logins, jobs, alerts, operations, and DTS packages are synchronized and then redirect clients to the promoted subscriber. For more information about facilitating this client redirection task, see Planning Guide Chapter 5, "Minimizing Downtime by Using Redundant Servers."


In a planned role change, pause all applications that use the production database, and then place the production database in single user mode. Pausing the production database helps ensure that the subscription database is transactionally current with the production database. Pausing the production database fails if applications do not disconnect from the database after each transaction. Be sure you don’t have open connections in Enterprise Manager, Query Analyzer, or other utilities that would cause the pause procedure to fail.


In an unplanned role change, recent transactions that are not replicated before the publisher fails are lost.


To pause applications that use the production database


    

  1. Disconnect applications from the production database if the database is still available.
    

    The method for doing so varies by application. In a planned failover, allow open transactions to complete, but do not allow new ones to start.
    

    2. 

  2. If you implemented NLB to direct traffic to a specific server, issue a WLBS command to ensure that all transactions have completed:
    

    3. 



WLBS DrainStop <NLB cluster name>:<publisher> /PASSW <password>



Note: For more information about NLB, see Solutions Guide Chapter 4, "Implementing Network Load Balancing."


To place the production database in single user mode from a command prompt


    

  • Put the following command into a batch file so that it is ready when you need it:
    

    • 



OSQL –Q ALTER DATABASE <production database> SET SINGLE_USER WITH
ROLLBACK IMMEDIATE –S<publisher> -d master –E



To stop transactional replication jobs on the publisher


    

  1. Using SQL Server Enterprise Manager, connect to SQL Server on the publisher, right-click Replication, and then click Disable Publishing.
    

    2. 

  2. On the Welcome to the Disable Publishing and Distribution Wizard for < server >page, click Next.
    

    3. 

  3. On the Disable Publishing page, click Yes, Disable Publishing on < publisher >, and then click Next.
    

    4. 

  4. On the Confirm Dropping of Publications page, click Next to drop all publications.
    

    5. 

  5. On the Completing the Disable Publishing and Distribution Wizard page, click Finish.
    

    6. 

  6. Click OK to acknowledge that transactional replication was disabled on the publisher.
    

    7. 



To stop transactional replication jobs on the subscriber/distributor


    

  1. Connect to the secondary server instance of SQL Server on the subscriber/distributor, right-click Replication, and then click Disable Publishing and Distribution.
    

    2. 

  2. On the Welcome to the Disable Publishing and Distribution Wizard page, click Next.
    

    3. 

  3. On the Disable Publishing and Distribution page, click Yes, Disable Distribution (and Publishing) on < subscriber/distributor >, and then click Next.
    

    4. 

  4. On the Confirm Disabling Remote Publishers page, click Next to disable all publishers.
    

    5. 

  5. On Completing the Disable Publishing and Distribution Wizard, click Finish.
    

    6. 

  6. Click OK to acknowledge that transactional replication was disabled on the subscriber/distributor.
    

    7. 



Note: The former subscriber is now the primary server. To maintain the database redundancy protection provided by transactional replication, configure the new primary server as a publisher with a subscriber/distributor and additional subscribers as appropriate.


Reverting to the Original Publisher


Reverting to the original publisher is unnecessary if the performance of new publisher equals the performance of the old publisher. It might make sense to leave the system operating the way it is rather than interrupt operations with another role change procedure. Your recovery plan should indicate which option you have selected. If your recovery plan calls for reverting to the original publisher, follow the procedural steps above for a planned role change. Note that you can re-use the Transact-SQL scripts you created to manage tables with identity and time-stamp data types — and the modified replication stored procedures used for replication to tables with time stamps. You do not need to recreate them.