A Guide to SQL Server 2000 Transactional and Snapshot Replication

A Guide to SQL Server 2000 Transactional and Snapshot Replication Copyright @ 2004 Hilary Cotter All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN (pbk) 0-9749736-0-2

Printed and bound in the United States of America 12345678910 Trademark names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. All trademarks belong to their owners. Technical Review: Mark Allison Editor: Kevin Campbell Book Design and Production: Amy Rothstein [email protected] Copy Editor: Linda Devore [email protected] Cover Design: Wendy Reynolds [email protected] Index: Hilary Cotter For information on translations, please contact Not While the Surf ’s Up Press by email: [email protected]. The information in this book is distributed on an “as is” basis, without any warranty. Although every precaution has been taken in the preparation of this work, neither the author nor Not While the Surf ’s Up Press shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers at http://www.nwsu.com in the Downloads section.

Chapter 2 Installing Replication Let me live like a sky rocket. Let me colour the night sky for a single instant, burning with all of my being. And then burn out. — Yukio Mishima This chapter focuses on installing replication on your SQL Server. Most users will only install replication on their SQL Server and then enable Subscribers. We’ll look at these two options and then look at how to set up your SQL Server to use a remote Distributor. After looking at these options, we’ll look at Replication Monitor, which is the central point for replication administration. In the process we’ll cover some of the more advanced options and settings. Following that, for those of you who are wizard adverse, we will look at the replication stored procedures that you can use to carry out the same things. There are really two parts to installing replication: • Configuring your SQL Server as a Publisher/Distributor or a Distributor • Configuring your Subscribers Recall from Chapter 1 that a Distributor is a SQL Server that stores and distributes transactions from your Publisher to your Subscriber. For most replication solutions, your Publisher and Distributor will be on the same server. For high throughput replication solutions, you should look at using a remote Distributor.

Configuring your SQL Server as a Distributor Configuring your SQL Server as a Distributor involves running a wizard that creates a distribution database and several jobs in the msdb database. After your distribution database is installed, you are ready to create your publications. Let us walk through the wizards to understand how to configure your server as a Distributor. The first time you configure your SQL Server as a Distributor, open up Enterprise Manager (EM), connect to your SQL server, and on the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors (Fig. 2.1). You can bypass this altogether and merely click Create and Manage Publications, which is what most DBAs do.

Installing Replication 37

Figure 2.1 Configuring SQL Server for replication Both options will launch the Configure Publishing and Distribution Wizard (Fig. 2.2). Figure 2.2 Splash Screen for the Configure Publishing and Distribution Wizard

Click Next to advance to Select Distributor (Fig. 2.3).

38 Chapter 2

Figure 2.3 The Select Distributor dialog box

This dialog box allows you to select which Distributor you want to use, or whether you want to configure the local SQL Server as a Distributor. If you are using Transactional replication and you have a high transaction throughput, you may wish to offload your distribution database to another server. We call this migrating to a remote Distributor. In Merge and Snapshot replication, the location of the Distributor is less critical. A good indication to migrate to a remote Distributor is when you get large numbers of deadlocks on your publishing server or whenever operations in EM seem to lock, especially when you are attempting to administer replication. For now we will be clicking Next to move to the Specify Snapshot Folder dialog box (Fig. 2.4). Figure 2.4 Specify Snapshot Folder dialog box

This dialog box allows you to place your snapshot files in a specific folder or drive. Notice that the suggested folder location is in the form of a


UNC (Universal Naming Convention) using the administrative share C$. You would want to place your snapshot folder location on another drive for performance reasons (snapshot generation can be IO intensive), if you are using ftp instead of UNCs, or for space reasons. Space is typically an issue if you are replicating very large tables. If you chose not to use the default security model replication runs under, you might want to create a shared directory (for example, share C:\Program Files\Microsoft SQL Server\MSSQL\ReplData as ReplData, or even ReplData$) and then you can use an account on your Subscriber that has rights to read the ReplData share and underlying files and folders but not your administrative share (the C$ share). For the security conscious reader, the $ in C$ and ReplData$ will make this a hidden share, i.e., this share will not show up in a net view \\PublisherServerName. We will be covering more about this later. Clicking Next may give you the following dialog box (Fig. 2.5). Figure 2.5 The prompt SQL Server will display if it can’t verify the share.

You will get this dialog box if your machine is not connected to a network or if you are in a workgroup. If you have entered your own snapshot location, check to verify that you have not made a typo. You will not get this prompt if you are logged on to a network and have correctly spelled your path name. Click Next to get the Snapshot Folder Path Verification dialog box (Fig.2.6). Figure 2.6 The prompt SQL Server will display if it can’t verify the share.

This is a warning about connecting to the administrative share, which we discussed earlier. Clicking Yes will give you the Customize the Configuration dialog box (Fig. 2.7).

40 Chapter 2

Figure 2.7 The Customize the Configuration dialog box

Typically, you will not have to make any modifications at this point. There are some instances where you might want to set the distribution database properties: • Use another name for the distribution database; • Place your distribution database in a folder or drive other than the default C:\Program Files\Microsoft SQL Server\MSSQL\Data; • Enable other Publishers to use the distribution database on this server; • Select the authentication mode that replication will use to replicate data from the distribution database to the Subscribers; and/or • Enable other databases for Transactional, Snapshot, or Merge replication. In the above cases you would select the Yes, let me set the distribution database properties, enable Subscribers, or set the Publishing settings, which we will henceforth refer to as advanced options. We will go into these advanced options in the Distributor Properties section on page 85. Clicking Next will bring you to the Completing the Configure Publishing and Distribution Wizard dialog box (Fig. 2.8).


Figure 2.8 The Completing the Configure Publishing and Distribution Wizard dialog box

Clicking Finish will bring you to an information window (Fig. 2.9). Figure 2.9 A progress window illustrating the configuration steps

You have the option to cancel the progress of the installation of replication on your SQL Server, and the SQL Server will rollback and recover from the installation of replication. If you have any errors, you also can check to see at which point in the installation failed. After replication is completely set up on your SQL Server, you will receive the prompt illustrated in Figure 2.10. Figure 2.10 A final dialog box

Click OK. You have finally completed setting up your SQL Server as a Publisher/Distributor. The SQL Server will then throw up another dialog box (Fig. 2.11) informing you that it has added another folder or node to your EM (Enterprise Manager) MMC (Microsoft Management Console),

42 Chapter 2

called Replication Monitor. Replication Monitor administers your Publishers, Subscribers, and replication agents. Figure 2.11 The Distributor Completion dialog box

There is also an advertisement for a node in EM called Replication Monitor Group. As this tool is very helpful when you have several SQL Servers that are running replication, we’ll have a quick look at enabling this at the end of this chapter on page 75. Every time you open up EM and Click your Replication Monitor, you will get the dialog box shown below unless you select the option Yes, automatically refresh Replication Monitor by polling the Distributor (Fig. 2.12). Figure 2.12 The Replication Monitor Refresh dialog box

If this dialog box gets too annoying, you can select Do not ask this again, and you will never see it again. You can set EM to refresh automatically or manually by right clicking Replication Monitor in EM, and pointing to Refresh Rate and Settings, and selecting or clearing the check boxes for the Refresh Results Pane group.


If you select the option to have SQL Server automatically refresh Replication Monitor, your Replication Monitor will be refreshed every 10 seconds. Hint: You might find this setting to be too short, as occasionally on heavily used servers, EM can become locked by all of the update activity going on in your distribution database. Then again, if the time period is too long, your EM won’t reflect job status and failures as promptly as if it were refreshed every 10 seconds, and it will have more information to pore through when you manually do a refresh, resulting in more locking. You might want to set a refresh rate of 10 minutes and then use Replication Alerts to alert you when you have job failures. Replication Alerts have no performance impact on your SQL Server. We’ll look at setting up manual refreshes later. Right now we’ll select Do not ask this again and No, I will manually fresh Replication Monitor.

Enabling Subscribers If you are replicating to a remote SQL Server, in other words, if you are replicating to a database that does not exist on your local SQL Server, you will need to enable the remote SQL Server as a Subscriber of your local SQL Server publications. To do this, go to the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors in EM (Fig. 2.1). You will then get the Publishers and Distributors Properties dialog box (Fig. 2.13).

44 Chapter 2

Figure 2.13 The Publisher and Distributor Properties dialog box

Click the Subscribers tab (Fig.2.14). Figure 2.14 The Enable Subscribers dialog box

Clicking the Subscribers tab will show a list of all the SQL Servers that you have registered in SQL Server EM. If your SQL Server does not appear here, you can click New to register it. We will cover this in the Enable New Subscribers section on page 48. To enable one or more Subscribers you have two options: • Click the check box to the left of the server name and click Apply. By default, your local SQL Server (Hilary2KP, in this case) will be the only enabled Subscriber. Installing Replication 45

• You can click Enable All to enable all the SQL Servers as Subscribers. There is no real performance penalty for choosing to Enable All Subscribers; in fact it prevents you from having to go back and enable Subscribers on an as needed basis. All this option does is configure the Publisher to replicate to the Subscribers. You also have the option of clicking Enable None, which will disable all enabled Subscribers and drop any subscriptions they may have with the publications on the local SQL Server. The SQL Server will throw up message warning you about disabling a Subscriber (Fig. 2.15). Figure 2.15 The Disable Enabled Subscribers dialog box

After you have enabled your SQL Server as a Subscriber, you will notice a browse button (what looks like three dots) to the right of your SQL Server. Clicking Browse will allow you to control how your Subscriber will authenticate with the Publisher. Clicking Browse will launch the Subscriber Properties dialog box (Fig. 2.16). Figure 2.16 The Subscriber Properties dialog box

The most significant option in this dialog box is Agent connection to the Subscriber. This option is poorly understood by the SQL Server community at large, so I’ll spend a little bit of time explaining it.

46 Chapter 2

Impersonate the SQL Server Agent or SQL Authentication? There are two options to have your Subscriber connect to your Distributor or Publisher/Distributor: • Impersonate the SQL Server Agent account on your Publisher • Use SQL Server Authentication Under most circumstances you should, if at all possible, use the Impersonate the SQL Server Agent account on your Publisher. It is a good security practice to configure your SQL Servers to use Windows Authentication only. However, in some circumstances (for instance when you are replicating over the Internet where ports used by Windows Authentication are blocked by all security conscious firewall administrators), you should use Mixed Authentication. The reason you should use the Impersonate the SQL Server Agent account on your Publisher is that when the Snapshot Agent is downloading your snapshot files to the Subscriber, it has to connect to the path you defined in Figure 2.4. This path is by default of this form: \\PublishingServerName\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\UNC

Notice that this is an administrator share. The $ sign renders it invisible, so the share name will not show up if you issue a net view \\ServerName

Only accounts that are in the Administrators group on the Publisher will be able to connect to this path and download the snapshot files. When you are downloading the snapshot using a push subscription, the Distribution Agent will be using the SQL Server Agent account on the Publisher. If you are downloading the snapshot using a pull subscription, the Distribution Agent will be using the SQL Server Agent account on the Subscriber. There is a similar configuration option when setting up your pull subscription agents. The SQL Server Authentication account is designed to be used for all other cases. These cases can be grouped into the following: • When you are replicating to Subscribers over the internet • When your Publisher and Subscriber are in different untrusted domains • When your Publisher and/or Subscriber are in a workgroup, where the number of connections are limited


The Publisher will have to be configured for Mixed Mode Authentication for SQL Authentication to be possible. If your Subscriber is in one of the above three categories, it will be unable to connect to the distrib executable (which your Distribution Agent uses) using NT Authentication (or the Impersonate the SQL Server Agent option). If you are using a SQL Server authentication, you may have to modify your snapshot share name and its underlying permissions so the SQL Server agent (on the Publisher for push subscriptions and on the Subscriber for pull subscriptions) will be able to download the snapshot files. In some cases (e.g. untrusted domains), you will have to open up the permissions underlying the snapshot share to the Everyone group. It is possible to use pass-through authentication for the latter two cases as well. Pass-through authentication is where the SQL Server Agent accounts on the Publisher and Subscriber have the same name and password. The domain authentication check will be bypassed if the local authentication mechanism detects that the account names and passwords are the same. For more information on this, check out Microsoft Knowledge Base article 321822. You will be unable to use pass through authentication on your DC or PDC.

Enabling New Subscribers The New button is considerably more interesting. You use this option to enable new SQL Server Subscribers that do not appear in the Subscriber tab you see in Figure 2.14, and to enable other types of Subscribers, such as • Microsoft Jet 4.0 MS Access databases, • OLE DB Data Sources (Oracle, Sybase, DB2, MySQL, Visual FoxPro) databases, or • ODBC Data Sources. Let’s go through some examples of how to set up each type of data source. Clicking New will display the Enable New Subscriber dialog box (Fig. 2.17).

48 Chapter 2

Figure 2.17 The Enable New Subscriber dialog box

Accepting the prompt for a SQL Server will bring up the same dialog box you will see when you register any SQL Server in EM, with the exception that upon completing this dialog box, your SQL Server will automatically be an enabled Subscriber. Notice that this applet does not discriminate between versions of SQL Server. A SQL Server 2000 server can publish to any SQL 7 server. It can also replicate to a SQL 6.5 server through an ODBC DSN.

Configuring an MS Access database as a Subscriber You can replicate to local MS Access databases. To do this, click Microsoft Jet 4.0 database (Microsoft Access) in Enable New Subscriber. This will launch the Enable Subscriber-Microsoft Jet 4.0 Database (Fig. 2.18). Notice that I have two Access linked servers already: FoodMart and Adventure Works (ADVWORKS). Figure 2.18 The Enable Subscriber – Microsoft Jet 4.0 Database dialog box

Click Add to create a new linked server to your Access database or highlight the existing MS Access linked server (ADVWORKS for example), enter the login and password if any, and click OK. The account you add here is not an NT account or a SQL account, but rather the account used Installing Replication 49

by the MS Access database. The default MS Access account is admin with no password. Add will launch the linked server dialog box (Fig. 2.19). If your Access database does not already exist, SQL Server will create it for you. You can have a pre-existing Access database, which can be blank or have objects in it. Figure 2.19 The Add Microsoft Jet Linked Server dialog box

If you enter the incorrect password you will get an error message while replicating to the access database stating: Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

In this case, you did not configure the account and password correctly while you were configuring this Access database as a Subscriber. Return to the Enable Subscriber-Microsoft Jet 4.0 Database dialog box (Fig 2.18) and re-enter the account and password there. Hint: An Access database can be a Subscriber to Transactional, Snapshot, and Merge publications. You can’t make an Access database a publisher for a Transactional or Snapshot publication, but with Merge, you can create a Merge publication on a SQL Server database and then make the Access database a Subscriber, and all transactions occurring on the Merge Subscriber will be “published” or merged to the SQL Server database.

50 Chapter 2

Configuring an MSDE Subscriber MSDE (MicroSoft Data Engine) is a redistributable version of the SQL Server 2000. It is very similar to SQL Server, only with certain limitations: • It does not include SQL Full Text Search. • It is optimized for 8 simultaneous workloads. Subsequent connections are throttled by a governor. • Databases are limited to 2 GB in size. • The Publisher and Distributor must be on the same server. MSDE does not permit Remote Distributors. • An MSDE cannot be a Publisher for a Transactional publication; however, it can be a Publisher for Merge or Snapshot publications. It can be a Subscriber for all types of publications. • If you use MSDE as a Publisher for a Snapshot or Merge publication, you must create the ReplData folder and then create the publication. By default you would create the ReplData folder in the C:\Program Files\Microsoft SQL Server\MSSQL directory • MSDE does not ship with EM. You must manage it through a workstation or server that has EM installed, or use stored procedures, SQL DMO, or the Replication ActiveX controls to manage it. To enable an MSDE to be a Subscriber, you would register it in EM and then enable it as a Subscriber, just like you would enable any other SQL Server as a Subscriber.

Configuring an ODBC data source ODBC is a data access technology that was designed for always connected Client-Server applications. In other words, the Client would log on to SQL Server (or another data source) at the start of a session and not log off until it was through, sometimes hours or days later. Although ODBC provides very fast access for sequential bulk read operations, it was not designed for the large numbers of uses that Web applications support. Nor was it designed for NRHRS (Non Rectangular Heterogeneous Record Sets), like Exchange mail items or the Internet Publishing Provider. OLE DB did provide these services and was designed with the Internet in mind. ODBC was and is still very popular because it is simple to configure and test. You can create a subscription to any data source that has an ODBC driver written for it that meets the ODBC conformance level 1 or higher specifications. This includes most of the commercial databases out there. Not every data source with an ODBC driver can be enabled as a Subscriber. Consider the Text Driver or an Excel spread sheet. There are ODBC drivers for them, but they can’t be enabled as Subscribers. Installing Replication 51

To check to see if your ODBC driver is compatible for replication, download the ODBC Driver Conformance Level tool. Check out Microsoft Knowledge Base article 279882 for more information about this tool. For an ODBC data source to support SQL Server replication, it must • be at least ODBC level-1 compliant, or above, • be 32-bit, thread-safe, and for the processor architecture (Intel or Alpha) on which the distribution process runs, • be transaction capable, • support the Data Definition Language (DDL), • be readable and writable, i.e. not read-only, and • support long table names, such as MSreplication_subscriptions. There are two steps involved in enabling a Subscriber using ODBC for replication: • Build a DSN. • Enable the data source as a Subscriber. To build the DSN you must launch the odbcad32 executable, which can be found in %WINDIR%\System32. This program is also found in the Control Panel under the Administrator Tools and is called Data Sources (ODBC). Click Add and browse to locate your driver. If you do not find the driver for the data source type you are trying to build the DSN for, you must install the ODBC driver for the data source on the Publisher. Contact the vendor for the driver. After you have built and tested the DSN, you must then enable it as a Subscriber. To do this, click ODBC data source in the Enable New Subscribers dialog box (Fig 2.17). The OBDC driver may then launch a driver-specific login dialog box to login to the data source. For examples on how to set up popular RDBMSs as Subscribers using ODBC drivers, please refer to the sections below. I was hoping to provide an example of replicating to a PostgreSQL server, but I ran into problems using their ODBC driver. There is no OLE DB provider for PostgreSQL, and the person supporting the ODBC driver has retired due to personal problems. I also ran into problems using other vendors’ ODBC drivers who support PostgreSQL. If in the future I am able to enable PostgreSQL as a Subscriber, I will post instructions on how to do this on my Web site, http://www.replicationfaq.com.

Configuring an OLE DB data source OLE DB was the data access technology that replaced ODBC. OLE DB was designed with the disconnected medium of the Internet in mind, where a data 52 Chapter 2

source could expect large numbers of very fast connects and disconnects. OLE DB was also designed for distributed and asynchronous processing. OLE DB tends to be much faster and is much more scalable than ODBC. Microsoft’s new data access technology is ADO.Net using managed providers. For an OLE DB provider to support replication, it must support the following objects: • • • • •

DataSource object Session object Command object Rowset object Error object

To configure an OLE DB data source for replication, you must do the following: • Build a linked server. • Enable the linked server as a Subscriber. You have two options to build a linked server: • the stored procedure sp_addlinkedserver • Enterprise Manager (EM) Before you can build a linked server, you must ensure that the OLE DB provider is installed on your Publisher. To do this, issue a sp_enum_oledb_providers command using Query Analyzer (isqlw.exe) and look for a reference to your particular provider in the Provider Description column. If your OLE DB provider does not show up here, you must install it on the Publisher. Contact the vendor for the provider. Even if your provider does show up in this list, that is no guarantee that it is able to provide Subscriber services. For instance, there is an Index Server/Indexing Services provider (MSIDXS), but it will not support being a Subscriber. If your Subscriber is listed, notice the provider name. This will be the @srvproduct and @provider parameter values in the sp_addlinkedserver procedure. The sp_addlinkedserver procedure has the following parameters: Table 2.1 Linked Server Parameters

@server @srvproduct @provider @datasrc @location @provstr @catalog

Server name product name OLE DB provider name OLE DB datasource property OLE DB location property OLE DB provider-string property OLE DB catalog property Installing Replication 53

@server @server parameter is the name of your linked server and will be the name of your Subscriber. This is a string value. @srvproduct The @srvproduct is the provider name obtained from the sp_enum_oledb_providers. This is a string value. @datasrc The @datasrc parameter is the name of the data source. Each OLE DB provider implements this parameter differently; however, it most frequently is the name of your server, the DSN name, or the path to the database or data source. This is a string value. @location The @location parameter is typically the database name or the path to the database, but for most providers this parameter is optional. This is a string value. @provstr The @provstr parameter is your provider string. Hint: As the value of this parameter is poorly documented for most OLE DB providers I use the complete provider string that I would use when using this provider in VBScript or Visual Basic. I then test to ensure it works, and then remove various portions until I determine exactly what is necessary to get this to work. Most often it is just the User ID or account and its password. These typically must be entered as User ID = replaccount; password=se1cure#;

Ideally, if possible use sp_addlinkedserverlogin to enter the account and password. This is a string value. @catalog The @catalog parameter is most frequently the name of your database, and most frequently is optional. This is a string value. Please consult some of the examples here for samples of linked servers.

54 Chapter 2

Configuring Sybase Servers for Replication using ODBC You can configure a Sybase server as a Subscriber to a Transactional or Snapshot publication. The most complex part of setting this up is creating your ODBC link. Please see Figure 2.20 for a screenshot of a typical ODBC connection to a Sybase server. Figure 2.20 A typical ODBC DSN to a Sybase server

In my experience, most users have a problem setting up the ODBC connection to a Sybase server. Most of the problems are either forgetting that the Sybase database name is case sensitive or forgetting to append the port number after the server name (i.e. ServerName, 5000) in the Network Address section. 5000 is the default listening port for a Sybase server. When you have built your ODBC DSN and tested it, go to the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors, select the Subscribers tab and then click New. Select ODBC data source and click the Sybase ODBC DSN you have just built. In Login, enter the account you will be using to connect to Sybase and then enter the password and confirm it. Sybase currently uses SQL Authentication. When you create your publication and get to the Specify Subscriber Types, ensure that you use Heterogeneous Data Sources.


Configuring Sybase Servers for Replication Using OLE DB The OLE DB providers in general offer better performance than the respective ODBC drivers. There are two key elements to setting up the OLE DB provider for Sybase: • Building your IDS file • Building your linked server The Sybase IDS file is similar to a DSN, only it is a text file that is deposited by default in your C:\Sybase\oledb directory (your location may vary). To build your IDS file, open a command prompt and navigate to C:\Sybase\oledb (your location may vary), and run Sybase Configuration Manager, which is an executable called sydaadm.exe. After you open Sybase Configuration Manager, select the menu option File and point to New and then click Data Source. You will then get a dialog box that allows you to name your data source. Enter the name you wish to use and then click Set Up Data Source. Figure 2.21 is a screenshot of what a configured OLE DB Data Source would look like for Sybase. Click Apply and Close to write your new IDS file for your OLE DB Sybase data source. Figure 2.21 A configured OLE DB Data Source for a Sybase Server

Once you have configured your IDS, you must build your linked server. There are two ways to do this: through the sp_addlinkedserver procedure and through EM. To add a Sybase linked server using sp_addlinkedserver, you need to issue the following command: 56 Chapter 2

sp_addlinkedserver @server ='NameOfYourLinkedServer', @srvproduct='Sybase.ASEOLEDBProvider', @provider = 'Sybase.ASEOLEDBProvider', @datasrc= 'Name_Of_Your_IDS_File', @provstr='User ID=ReplAccount;Password=se1cure#;', @catalog='Subscriber_Database_On_Sybase_Server'

When entering the name of your data source, make sure you only enter the file name, not the extension. So if your IDS file looks like Connect.IDS, you would enter Connect for the name of your @datasrc parameter. Also make sure you customize the @provstr parameter for your user id and password, and the @catalog for your Subscriber database on the Sybase server. You can also leave the @provstr parameter blank and configure the account using the sp_addlinkedsrvlogin. Here is statement to add a linked server: sp_addlinkedsrvlogin @rmtsrvname='SybaseServerName',_ @useself='False',@locallogin=null,@rmtuser=_ 'SybaseAccount',@rmtpassword='SybaseAccountPassword'

You can also use EM create a linked server. To do this, connect to your server, expand your server, expand the securities folder, right click Linked Servers, and point to New Linked Server (Fig.2.22). Figure 2.22 New Linked Server

In the New Linked Server text box, enter the name you wish to use for your Sybase Subscriber. In the Provider Name list, select Sybase ASE OLE DB Provider. If the Sybase ASE OLE DB Provider does not appear in the drop down list, the provider is not installed on your Publisher, and you will need to install it. You can obtain it from the Sybase Web site.


In the Product Name text box, enter Sybase.ASEOLEDBProvider. In the Data Source text box, enter the name of your IDS file. Only enter the file name; do not enter the extension or the directory it is in. So if your IDS file is called test.IDS and is in your C:\Sybase\oledb directory (your location may vary), merely enter “test” in the Data Source text box (Fig. 2.23). Figure 2.23 Configuring a linked server for Sybase using the Sybase OLE DB provider

In the Security tab, select Be made using this security context, and enter the Sybase account and password. You could also enter this in the provider string text box, but then this would be visible to anyone. Sybase does not support a SQL Server replicating tables with hyphens in them. So if you are replicating hyphenated tables from a SQL Server to Sybase, you will have to create your articles with different destination table names on the Subscriber. When you create your publication and get to the Specify Subscriber Types, ensure that you use Heterogeneous Data Sources (Fig. 2.34).

58 Chapter 2

Figure 2.24 The Specify Subscriber Types dialog box

Configuring Oracle for Replication using the Microsoft ODBC Driver for Oracle There are two ODBC drivers for Oracle 9i, those from Microsoft (referred to as the Microsoft ODBC driver for Oracle) and those from Oracle (referred to as the Oracle ODBC driver). Both drivers support replication, and there is debate as to which one works the best. On the Microsoft SQL Server replication newsgroup, the consensus seems to be that the Microsoft driver performs better; however, some users do advocate the Oracle drivers, complaining about the instability of the Microsoft drivers. My experience, and that of others, is that the Microsoft drivers are more stable, and I wonder if the choice doesn’t boil down to personal preference or prejudice. What makes the Microsoft ODBC drivers for Oracle the better choice is the fact that Microsoft supports replicating to Oracle using the Microsoft ODBC driver for Oracle. We’ll be looking at configuring replication with both drivers. First we’ll look at the Microsoft ODBC driver for Oracle. To configure the Microsoft ODBC driver for Oracle you need the following: • The name of your Oracle server. I use the Oracle Net Manager to obtain this information. • An account that has rights to create objects on the Oracle database, preferably the SYSTEM account, and its password. • The Oracle client installed on the Publisher. • The name of the Oracle database you are replicating to. Figure 2.25 is what your Oracle ODBC connection would look like using the Microsoft ODBC driver for Oracle.


Figure 2.25 Configuring the Microsoft Oracle ODBC driver

Here I am using the Oracle SYSTEM account to connect with, and my Oracle server’s name is NAME. When you have built your ODBC DSN and tested it, open up EM, and on the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors, select the Subscribers tab, and then click New. Select ODBC data source, and click the Oracle ODBC DSN you have just built. In the Login section, enter the account you will be using to connect to Oracle, and then enter the password and confirm it. When you create your publication and get to Specify Subscriber Types, ensure that you use Heterogeneous Data Sources.

Configuring Oracle for Replication using Oracle ODBC Driver To configure an ODBC DSN to an Oracle server using the Oracle ODBC Driver, you need the following: • The Oracle client installed on your Publisher. • The TNS Service Name. • An account that has rights to create objects on the Oracle database, preferably the SYSTEM account, and its password. Figure 2.26 is what your ODBC DSN would look like using the Oracle ODBC driver.

60 Chapter 2

Figure 2.26 An Oracle ODBC DSN

In this case, our Oracle TNS Service Name is NAME, and the User ID I am connecting to Oracle with is SYSTEM. When you have built your ODBC DSN and tested it, in EM go to the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors, select the Subscribers tab, and then click New. Select ODBC data source, and locate the Oracle ODBC DSN you have just built. In Login, enter the account you will be using to connect to Oracle, and then enter the password and confirm it. When you create your publication and get to the Specify Subscriber Types, ensure that you use Heterogeneous Data Sources.

Configuring Oracle for Replication using Oracle OLE DB The Microsoft and Oracle OLE DB providers offer better performance than the ODBC drivers. Again, there is no clear consensus as to which provider is superior. Likewise, I am not sure why some DBAs prefer to use the ODBC driver over the OLE DB provider when creating their Oracle Subscriber. To me, the natural choice is OLE DB. To create a Subscriber to an Oracle server using Oracle OLE DB, you must create a linked server to Oracle. To create the linked server you must have the following: • • • •

The name of your Oracle server. The account you wish to connect to Oracle with and its password. The Oracle Client installed locally on the Publisher. The name of the user database on the Oracle Server you wish to replicate to.

Figure 2.27 is what your Oracle linked server would look like when you are using the Oracle OLE DB provider.


Figure 2.27 A linked server using the Oracle OLE DB provider

In the drop down list Provider Name, select Oracle Provider for OLE DB. In Product name, enter Oracle. In the Data source, enter the name or your Oracle server. In the Security tab for your linked server, select the be made using this security context and enter the name of the account you wish to access Oracle with. When you have built your Oracle linked server, test it by expanding your linked server and clicking the Tables node. Your linked server should connect to your Oracle server and display a list of tables in your database. Then, in EM, go to the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors, select the Subscribers tab, and then click New. Select OLE DB data source, and select the Oracle OLE DB linked server you have just built. In the Login section, enter the account you will be using to connect to Oracle, and then enter the password and confirm it. When you create your publication and get to the Specify Subscriber Types, ensure that you use Heterogeneous Data Sources.

Configuring Oracle for Replication using Microsoft OLE DB To create a Subscriber to an Oracle server using the Microsoft OLE DB, you must create a linked server to Oracle using the Microsoft OLE DB provider. To create the linked server you must have the following: • The name of your Oracle server. • The account you wish to connect to Oracle with and its password.

62 Chapter 2

• The Oracle Client installed locally on the Publisher. • The name of the user database on the Oracle Server you wish to replicate to. Figure 2.28 is what your Oracle linked server would look like when you are using the Microsoft OLE DB provider for Oracle. Figure 2.28 A linked server using the Microsoft OLE DB provider for Oracle

In the drop down list Provider Name, select Microsoft OLE DB Provider for Oracle. In the Product name, enter Oracle. In Data source, enter the name of your Oracle server. In the Security tab for your linked server, select the be made using this security context and enter the name of the account you wish to access Oracle with. When you have built your Oracle linked server, test it by expanding your linked server and clicking the Tables node. Your linked server should connect to your Oracle server and display a list of tables in your database. Then in EM, on the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors, select the Subscribers tab, and then click New. Select OLE DB data source, and click the Oracle OLE DB linked server you have just built. In the Login section, enter the account you will be using to connect to Oracle, and then enter the password and confirm it. When you create your publication and get to the Specify Subscriber Types, ensure that you use the Heterogeneous Data Sources.


Configuring MySQL for Replication It is possible to create a MySQL server as a Subscriber to Transactional and Snapshot replication. Setting up a MySQL Database as a Subscriber is a very simple process; however, there is a bug. If your Distribution Agent stops and restarts, it will continually fail until you re-initialize your Subscriber. To enable MySQL as a Subscriber you must • Install the MySQL ODBC driver, obtainable from the MySQL Web site. • Create a DSN to your MySQL Server. Please refer to Figure 2.29 for an illustration. • Enable a new Subscriber as a ODBC data source. Use a MySQL account for your DSN that has all rights associated to it (i.e. GRANT ALL PRIVILEGES ON AdventureWorks* TO replaccount@localhost). • Locate your MySQL DSN (Data Source Name), and select it, entering the account name and password. When you create your publication and get to the Specify Subscriber Types, ensure that you use Heterogeneous Data Sources. I have tested this with most recent versions of MySQL, including version 4.0.17-nt, and tested inserts, updates, and deletes without any problems (other than the fact that you have to keep your Distribution Agent running). Figure 2.29 is what your ODBC DSN configuration for a MySQL Server would look like. Figure 2.29 An ODBC DSN to a MySQL Server

I am connecting to a MySQL Server running on a UNIX server (FreeBSD), to a database called AdventureWorks, using an account called replaccount, to which I have assigned a password. MySQL’s sa account, called “root,” by default has no password, and the guest account is enabled. 64 Chapter 2

In other words, any account can gain access. When you have built your ODBC DSN and tested it, go to EM and select the menu option Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors, select the Subscribers tab, and then Click New. Select ODBC data source, and locate the MySQL ODBC DSN you have just built. In Login, enter the account you will be using to connect to MySQL, and then enter the password and confirm it. When you create your publication and get to the Specify Subscriber Types, ensure that you use Heterogeneous Data Sources.

Configuring DB2 for Replication using ODBC DB2 is IBM’s RDBMS offering, and it is widely regarded as one of the premier database systems. DB2 has been implemented on a variety of platforms: • • • • •

zOS (formerly called MVS) AS400 AIX (UDB) Linux (UDB) NT (UDB)

UDB is Universal Database Server, which is a different product offering from the DB2 versions that ship on zOS and AS400. For DB2/NT, you can use the IBM ODBC driver or OLE DB provider. For the other systems, a variety of solutions exist, e.g. Shadow Direct, DataMirror, IBM’s Client Access, or Microsoft’s Host Integration Server (HIS). Interestingly enough, a merge replication solution was planned for HIS 2000, which would allow Host Systems (like DB2) to be a Merge Publisher to SQL Server. You can still see the schematics for this by searching on “merge” in the Host Integration Server compiled help file (HIServer.chm), which ships with HIS server or client. Look for a .gif there called IF_HREP03.gif. With luck, this will ship in a future version of HIS. We’ll look at using IBM’s ODBC driver and OLE DB provider to enable DB2/NT as a Subscriber. After that, we will look at how to replicate to DB2 AS/400 using Client Access, which is IBM’s connectivity software to connect Windows clients to DB2 on the AS400 systems. To replicate to DB2/NT using ODBC, you must configure your ODBC DSN. Figure 2.30 illustrates a configured OBDC DSN for DB2.


Figure 2.30 An ODBC DSN to a DB2 server

The key to getting a correctly configured DSN is having the correct DB2 server name, ALBANYDB204, in our case. After you configure your DSN, you will have to insert the following entry in your msdb database on your Distributor: INSERT INTO msdb.dbo.MSdatatype_mappings VALUES ('DB2/NT','nvarchar','VARCHAR',4000,4,1)

When you create your publication and get to the Specify Subscriber Types, ensure that you use Heterogeneous Data Sources.

Configuring DB2 for Replication using OLE DB To replicate to DB2/NT using OLE DB, you must create your linked server using the IBM OLE DB Provider for DB2. For the Product name, enter IBMDADB2. For Data Source, enter the name of your server running DB2, and for the Provider String, enter the name of your ODBC connection like this: DSN=DB2_ODBC_DSN

Your DB2 linked server will read your ODBC DSN for DB2 to obtain configuration information, but it will use the IBM OLE DB provider. Figure 2.31 illustrates this.

66 Chapter 2

Figure 2.31 Your DB2 linked server

Let me be perfectly clear. If you configured an ODBC DSN to DB2/NT called Test, the provider string would be DSN=Test

and the IBM OLE DB Provider for DB2/NT would be used, but it would be configured using the parameters in the ODBC DSN named test. After you configure your linked server, you will have to issue the following entry in your msdb database on your Distributor: INSERT INTO msdb.dbo.MSdatatype_mappings VALUES ('DB2/NT','nvarchar','VARCHAR',4000,4,1)

When you create your publication and get to the Specify Subscriber Types, ensure that you use the Heterogeneous Data Sources option.

Configuring DB2 AS/400 for Replication using Client Access and ODBC To replicate to DB2 AS/400 using Client Access, you would create another ODBC connection, this time selecting the Client Access driver. The screenshots below were kindly provided by SQL Server MVP Allan Mitchell. Figure 2.32 illustrates the General Tab of your Client Access DSN.


Figure 2.32 The General tab of the Client Access DSN

Notice that you have to select the correct system in the System drop down box. Then in the Server tab (Fig. 2.33) ensure that you have the correct library listed. Figure 2.33 The Server tab of the Client Access DSN

Using a Remote Distributor There are times when you will find that performance on your Publisher is degraded to the point that it becomes necessary to move your Distributor to another SQL Server that will be dedicated as a Distribution server. 68 Chapter 2

Unfortunately there is no single quantifiable identifier to tell you when to use a Remote Distributor. Rather it is when the number of deadlocks on Publisher start to become unacceptable to the applications using this database. Another sign is when you can’t manage your publications through EM or TSQL because of locking problems. To create a Remote Distributor, connect to your Distribution server using EM and install Replication by going to the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors and follow the defaults for making this SQL Server its own Distributor. After you have installed Replication, go back to your Publisher, and select the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors, and this time select the Publishers tab (Fig. 2.34). Figure 2.34 Configuring a Publisher to use a Remote Distributor

In this case, the Distribution Server is Hilary2kp. I want to enable the AlbanySQL04 to use Hilary2kp as its Distribution Server, so I click the check box to the left of the server AlbanySQL04. When you enable the Publisher to use the local Distributor, you will get a warning that the Distributor Server is password protected (Fig. 2.35). By default, the Distributor Server is password protected so that only other DBAs who know the password can have Remote Publisher use the local Distributor server.


Figure 2.35 Warning dialog box that the Distributor server is password protected

After you have enabled a Remote Publisher to use the Local Distributor, you have the option to configure the Publisher’s properties by clicking Browse (…) to the right of the Publisher’s name. Clicking Browse will launch the Publisher Properties dialog box (Fig. 2.36). You have the option in this dialog box to configure where the snapshot folder is on your Publisher, and how the Publisher will connect to the Distributor. For most topologies you will be using Impersonate the SQL Server Agent on the publisher. However, in some cases, you will want to use SQL authentication. These cases are typically where NT Authentication (Impersonate the SQL Server Agent on the publisher) won’t work, such as • having a Publisher and Distributor in different untrusted domains, • having a Publisher and Distributor in a workgroup, or • having a Publisher and Distributor running across the Internet, keeping in mind the security risks associated with doing this. Please refer to page 47 for a more complete description of these cases. When you use SQL Server authentication, the sa password will be the sa password of the Distributor Server, not the Publishing Server. If you enter the incorrect password, you may get the following error message when you try to add articles to your publication: Error 50007: xp_execresultset: unable to get a bound connection back to server.

Also notice in Figure 2.36 that you can specify the option that This publisher requires a password to establish a link to the Distributor. Some DBAs prefer to password protect their Distributor servers so that only other DBAs who know the password can use it. Your Distribution server will be password protected by default.

70 Chapter 2

Figure 2.36 Configuring your Publisher Properties when using a Remote Distributor

Notice that there is a drop down box for the Distribution database text box in Figure 2.36. You can have each Remote Publisher publish to a different distribution database on the Distributor. For very high throughput systems, the replication metadata tables on the Distributor can get very large, and there are some advantages to giving each Publisher its own distribution database. After enabling your Publisher to use the local Distributor server, you must connect to your Publisher using EM, and then select the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors. Click Next on the Welcome to the Configure Publishing and Distribution Wizard splash screen, and you will then get the Select Distributor dialog box (Fig. 2.37). Notice that we have the option of having the server (AlbanySQL04) use its own distribution database, but in this case, we are going to use the Remote Distributor (Hilary2kp). Click Next.


Figure 2.37 Selecting a Remote Distributor

You will then get a prompt for the Administrative Link password (Fig. 2.38). If you have forgotten the Administrative Link password, you can reset it. Go to the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, and Distributors. Enter a new password in the Administrative Link password text box in the Distributor tab (Fig. 2.39). Existing Publishers using the Distributor will fail if you change the Administrative Link password, so it’s best to treasure your Administrative Link password. Figure 2.38 Entering the Administrative Link password

72 Chapter 2

Figure 2.39 Changing the Administrative Link password

After entering the password, click Next, and you will have the opportunity to customize the publication settings (Fig. 2.40). Figure 2.40 The Customize the Configuration dialog box

If you choose to configure the publishing settings, the wizard will launch the dialog Enable Publication Databases dialog box (Fig. 2.41). This will allow you to enable databases on the Publisher for Merge, Transactional, or Snapshot publications.


Figure 2.41 The Enable Publication Databases dialog box

Clicking Next in the dialog boxes you see in Figure 2.40 and then 2.41 allows you to enable Subscribers to this Publisher. Figure 2.42 Enable Subscribers dialog box

Clicking Next in the Enable Subscribers dialog box will launch a summary page of actions, and there will be a Finish button to click to complete the process. Clicking Finish will launch another dialog box that will announce the successful completion of the wizard and the installation of your remote Distributor.

Migrating to a Remote Distributor Most of the time in the lifetime of your various replication solutions, you will find that you will migrate from using a Local Distributor to a Remote Distributor, as opposed to installing a Remote Distributor when you first 74 Chapter 2

deploy replication. It is hard to estimate whether you should start off with a Remote Distributor. If you do have a heavily loaded SQL Server to begin with, it is a clear choice to initially use a Remote Distributor with Pull Subscribers. Unfortunately, the way you migrate from a Local Distributor to a Remote Distributor involves dropping all subscriptions and publications, and then disabling publishing completely on the Publisher. Then on your Distribution Server you have to configure it as a Distributor if you have not already done so. After the Distributor is configured, enable the Publisher to publish to it, and then on the Publisher, configure the Distributor server as the Distributor. Then recreate your publications and subscriptions, and start up the agents.

Replication Monitor Group Replication Monitor Group is a new feature in SQL 2000 that allows you to monitor replication status and jobs on many servers. Essentially, it presents to the DBA a list of all the SQL Servers that are running replication that you have registered. To enable it, go to the Tools menu, point to Replication, and then click Show Replication Monitor Group (Fig. 2.43).

Figure 2.43 The Show Replication Monitor Group menu item Another folder or node entitled Replication Monitor Group will be added to your EM MMC (Fig. 2.44).


Figure 2.44 The Replication Monitor Group node

You then have to add to the Replication Monitor Group the servers you wish to monitor. To do so, right click Replication Monitor Group and click Add Distributor to Monitor (Fig. 2.45). Figure 2.45 The Replication Monitor Group

You will then get the Add Distributor to Monitor dialog box (Fig. 2.46), which allows you to pick from the SQL Servers and/or their instances that you have already registered in EM. You can select multiple servers by holding down the CTRL key and clicking them with your mouse. Figure 2.46 Adding registered servers to your Replication Monitor Group

76 Chapter 2

Clicking OK will display the server(s) you just selected in the Replication Monitor Group (Fig. 2.47). Figure 2.47 The Replication Monitor Group illustrating SQL Servers being monitored

What makes Replication Monitor Group so valuable is that SQL Servers that have a failed agent on them will show up with a red X on them. If you did not have Replication Monitor Group, you would have to rely on expanding every Replication Monitor Folder and looking for these red X marks, or rely on alerts or network monitoring software to detect errors written to the NT Event Log. Replication Monitor Group also presents a lightweight object for you to manipulate your publications and replication agents without having to drill down through all the folders that you would have to otherwise if you were using the SQL Server object you see in EM. Figure 2.48 is an illustration of what I am talking about. To see errors in replication looking at the Server node in EM, you must expand the SQL Server, and then expand Replication Monitor, and click Agents folder, and then you can see the error. In Replication Monitor Group, the error is plainly visible as soon as you expand it to display all the servers.


Figure 2.48 The Replication Monitor Group illustrating a SQL Server with a failed job

This visual exception monitoring and the ability to quickly access your replication agents will pay dividends when you have many SQL servers you are monitoring.

Replication Monitor Before we leave the replication server setup, there are a few more things to look at. If you right click Replication Monitor, you will notice the following options: • • • • •

Performance Monitor Distributor Properties Refresh Rate and Settings Select Columns Show Anonymous Subscribers

We’ll look at each of these options in detail in the following sections.

78 Chapter 2

Performance Monitor The Performance Monitor option is used to launch Performance Monitor with Replication Counters. As your replication topology grows, you may run into performance problems and have to resort to Performance Monitor to help identify bottlenecks. The counters supplied in this dialog box are below: • • • •

SQL Server: Replication Agents: Log Reader SQL Server: Replication Agents: Distributor SQL Server: Replication Agents: Merge SQL Server: Replication Agents: Snapshot

These counters merely report the number of agents of each type running. These replication counters will only be on your system if you selected the Server Component, Performance Counters sub-component when installing SQL Server. You can add more Performance Monitor Counter and Objects to this default msc (the MMC Performance Monitor configuration file). To do this click Start, click Run and then type perfmon and hit enter. When Performance Monitor (perfmon) launches, go to the menu item Console and click Open and navigate to C:\Program Files\Microsoft SQL Server\80\Tools\Binn and open up the perfmon.msc file. You can then add the Performance Objects and Counters you that are important to you, and then select the menu item Save. When you launch Performance Monitor again by right clicking Replication Monitor and clicking Performance Monitor, these Performance Objects and Counters will be loaded by default. I find the Performance Object SQL Server Replication Dist with the Counter Dist:Delivery Latency to be one of the more helpful counters. I also monitor overall system CPU utilization (Process:%Processor Time:_Total, and Process:%Processor Time:sqlservr), server work queues (Server Work Queues:Queue Length:0), and the number of page faults (Memory:Page Faults per sec). Here is a complete list of Replication Performance Objects available to you: • SQLServer: Replication Agents:Running – The number of replication agents currently running • SQLServer: Replication Dist:Dist:Delivered Cmds/sec – The number of commands per second delivered to the Subscriber • SQLServer: Replication Dist:Dist:Delivered Trans/sec – The number of transactions per second delivered to the Subscriber


• SQLServer: Replication Dist:Dist:Delivery Latency – The current amount of time, in milliseconds, elapsed from when transactions are delivered to the Distributor to when they are applied at the Subscriber • SQLServer:Replication Logreader: LogReader: Delivered Cmds/sec – The number of commands per second delivered to the Distributor • SQLServer:Replication Logreader: LogReader: Delivered Trans/sec – The number of transactions per second delivered to the Distributor • SQLServer:Replication Logreader: LogReader: Delivery Latency – The current amount of time, in milliseconds, elapsed from when transactions are applied at the Publisher to when they are delivered to the Distributor • SQLServer:Replication Merge:Conflicts/sec – The number of conflicts per second occurring during the merge process • SQLServer:Replication Merge:Downloaded Changes/sec – The number of rows per second merged from the Publisher to the Subscriber • SQLServer:Replication Merge:Uploaded Changes/sec – The number of rows per second merged from the Subscriber to the Publisher. • SQLServer:Replication Snapshot:Snapshot Delivered Cmds/sec – The number of commands per second delivered to the Distributor • SQLServer:Replication Snapshot:Snapshot Delivered Trans/sec – The number of transactions per second delivered to the Distributor Performance monitoring is widely described as an art and not a science. I don’t really want to discuss the validity of this statement in this book. However, what performance monitoring boils down to is identifying what process or hardware component is the bottleneck in your system, or to put it another way, which hardware component or process is degrading overall performance on your system. The way to first approach tuning a system is to realize that it takes the system several orders of magnitude longer to access hardware resources as compared to resources in memory. For instance, when I was a child, it was common wisdom that data from memory is read 1000 times faster than data read from disk. With the advances in CPU speed and performance and disk speed, I am not sure if this ratio still holds true, but the performance hit is still there today. So you should be analyzing your system to minimize as much hardware interaction as possible. This normally means you first look at your memory usage patterns because, ideally, your system will be able to cache everything in memory. In these days when RAM prices are nose diving, you can afford GBs of RAM 80 Chapter 2

on your systems, but still this is not enough to hold the ballooning database sizes. It is not uncommon to work with databases that are hundreds of GB in size. The NT OS family (NT, Win2k, WinXP, Win2003) and all modern OSs have a memory management technique that creates what is called virtual memory. On NT, Win2x and WinXP, each process running on your machine thinks it has 4 GB of RAM available to it. This 4 GB of virtual memory is further subdivided into two 2-GB blocks: 2 GB for the user data, and 2 GB for the system data. The 2 GB available for system data is shared between all system processes and the 2 GB of user data is accessible by your programs. So a process like a SQL Server will store database pages in cache using the 2 GB of user memory space, and use the 2 GB of system data to communicate with other NT processes. There are switches that you can set in your boot.ini file to change this memory allocation so that 3 GB of memory is accessible by user processes on NT 4.0 Enterprise and Win2x Advanced Server products. There is also a /PAE switch that allows Win2x Advanced Server and above to access more memory. Consult Kb articles 274750 and 283037 for more information on this. You should be asking yourself the question, “But my system only has 256 MB of RAM on it. How can I access the 2 GB of RAM?” Keep in mind this is virtual memory, not physical memory. Your system allows each user process to access up to 2 GB of virtual memory. However, OS engineers realize that at any one time slice, your user process will not need all 2 GB of memory; it may need 64 KB or 345 KB (just to throw out some numbers) at any one time. The Virtual Memory Manager will keep recently accessed process data in RAM, and the rest of the user data will be stored in a physical file called the Page File (pagefile.sys) in blocks of data called pages. The Virtual Memory Manager will flush to disk pages that haven’t been recently used in the Page File, keeping the most recently accessed pages in RAM. On well-tuned systems, the Virtual Memory Manager will only flush pages to disk every minute or so. On poorly tuned systems, the Virtual Memory Manager will be spending all its time paging to disk. The reason for this is that memory demands have forced it to page data that has been recently accessed to disk, and then it has to read it again as the user process requires it again. The act of hitting the disk so frequently causes a performance hit (remember our maxim “It takes a system 1000 times longer to access data off a disk than off memory.”), which slows your system down even further, causing your system to have to spend more memory resources managing the page file, and resulting in more page file flushes. On systems with insufficient memory, the Virtual Memory Manager will have to rely on the Page File stored on your physical disk to store memory pages. Suppose you don’t have enough CPU on your system. For Installing Replication 81

instance, say your system runs a Web server that has to crunch a lot of ASP code to render the ASP pages as HTML and send them back to your clients. In this case, ASP page requests start getting queued along with the user data to fulfill the page request. Soon your system spends more time managing the queue of requests and less time crunching the ASP pages. What this means for your system is that your page file will be flushing these queued page requests back to disk. We use Performance Monitor to identify such bottlenecks. Here are some of the ones I normally add to my default Performance Monitor management console for replication, along with their descriptions: • Memory:Available Bytes – The amount of physical memory available to processes running on the computer, in bytes. The Virtual Memory Manager may allocate 2 GB of memory space to user processes, but not all of this space is taken. Likewise, not all of the physical RAM is in use. Available Bytes is a measure of how much physical RAM is not in use. Consider Available Bytes as the amount of space available for the Page File to use before having to resort to page faults. Page faults occur when the request for a page could not be found in RAM and has to be requested from the disk (the Page File). When Available Bytes goes below 5% to 10%, you are running out of available memory. • Memory:Cache Bytes – The total number of bytes that the System process is currently using. Just like user processes, the Virtual Memory Manager will swap out System data to the page file. This counter has more meaning when used in conjunction with the Memory:Cache Faults per second. A high value of Cache Faults per second indicates that you should adjust your cache size. • Memory:Cache Faults per second – The number of faults that occur when a page sought in the file system cache is not found there and must be retrieved from elsewhere in memory (a soft fault) or from disk (a hard fault). The file system cache is an area of physical memory that stores recently used pages of data for applications. Cache activity is a reliable indicator of most application I/O operations. This counter counts the number of faults, without regard for the number of pages faulted in each operation. • Memory:Pages/sec – The number of pages read from or written to disk to resolve hard page faults. Hard page faults occur when a process requires code or data that is not in its working set or elsewhere in physical memory and must be retrieved from disk. This counter was designed as a primary indicator of the kinds of faults that cause system-wide delays. It is the sum of Memory:Pages Input/sec and Memory:Pages Output/sec. It is counted in numbers of 82 Chapter 2

•

•

• •

•

• •

pages, so it can be compared to other counts of pages, such as Memory:Page Faults/sec, without conversion. It includes pages retrieved to satisfy faults in the file system cache (usually requested by applications) and non-cached mapped memory files. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval. More than 5 pages/seconds is an indication of memory bottlenecks. Memory:Page Reads/sec – The number of times the disk was read to resolve hard page faults. If you have a large number of Pages Reads/sec used in reading as opposed to writing, your system is seriously bottlenecked. Memory:Pool Paged Bytes – Recall that of the 4 GB of virtual memory available to any user process, 2 GB are accessible for user data, and 2 GB are available as shared memory address space by system processes. Some of this data is available to be paged to disk. This area is termed Pool Paged Bytes. Memory:Pool Nonpaged Bytes – The number of bytes in the nonpaged pool that cannot be paged to disk and must remain in physical memory. Memory:Page Faults/sec – The number of page faults per second. This counter includes both hard faults (those that require disk access) and soft or Transition Faults (where the faulted page is found elsewhere in physical memory and not in the page file on disk; these types of page faults are called Transition Faults). Most processors can handle large numbers of soft faults without consequence. However, hard faults can cause significant delays. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval. Memory:Transition Faults/sec – The Virtual Memory Manager marks memory pages to be flushed to disk; the page is not immediately flushed to disk. Sometimes the page will be requested again by the Virtual Memory Manager before it is flushed to disk. Such requests are termed soft page reads or Transition faults. The Page Faults per second contains not only hard page faults but also soft page faults. Transition Faults per second can help you to determine the real number of hard page faults. Paging File:% Usage object (_Total) – The amount of the Page File in use in percent. Server:Pool Paged Bytes – The number of bytes of pageable computer memory the server is currently using. Can help in determining good values for the MaxPagedMemoryUsage parameter. If you tune this value, more memory will be available to your Pool Paged Bytes. Installing Replication 83

• Server:\Pool Nonpaged Bytes – The number of bytes of non-pageable computer memory the server is using. This value is useful for determining the values of the MaxNonpagedMemoryUsage value entry in the Windows NT Registry. If you tune this value, more memory will be available to your Pool Paged Bytes. • Processor:% Processor Time (all instances) – The percentage of time that the processor is executing a non-Idle thread. This counter was designed as a primary indicator of processor activity. It is calculated by measuring the time that the processor spends executing the thread of the Idle process in each sample interval, and subtracting that value from 100%. (Each processor has an Idle thread that consumes cycles when no other threads are ready to run). It can be viewed as the percentage of the sample interval spent doing useful work. This counter displays the average percentage of busy time observed during the sample interval. It is calculated by monitoring the time the service was inactive, and then subtracting that value from 100%. Percentage Processor Time should be less than 75%. • System:Processor Queue Length (all instances) – The number of threads in the processor queue. There is a single queue for processor time even on computers with multiple processors. Unlike disk counters, this counter counts ready threads only, not threads that are running. A sustained processor queue of greater than two threads generally indicates processor congestion. This counter displays the last observed value only; it is not an average. This counter indicates that processes are waiting for resources to be available. If other counters reveal that there are no hardware bottlenecks, you need to concentrate on improving your processor performance possibly by adding another processor, increasing the L2 cache, or migrating to another machine with more horsepower. So now you are armed with counters to help you to identify bottlenecks in your system. Where do you go from here? How do these counters relate to replication? You will find on heavily used systems, especially when you are doing filtering, that the performance hit of replication can degrade overall performance. Most significant is the increase in Processor Utilization (Processor:% Processor Time [all instances]). All this counter reveals is that your system is busier, or doing more work. Is this a bad thing? No, unless your Server Work queues counter is climbing significantly. Before you rush out and by a Quad or replace all your processors with ones with higher CPU speeds, ask your self if perhaps the problem is a memory bottleneck or a disk bottleneck, or can’t be solved by using a less complex filter, or perhaps by migrating to pull subscriptions.

84 Chapter 2

After installing replication, you may also notice that your system is paging more than normal. Expect a greater amount of disk activity, especially when generating snapshots, to cause this. But is this disk activity causing a bottleneck? The ratio of Page Fault Reads/sec to Page Faults/sec is an indication that you should add memory. The above counters can help you to identify where the bottlenecks lie. The temptation can be to throw hardware at the problem, whereas there could be more cost-effective solutions to the problem, like adding memory. You may also find that by moving to pull subscriptions, which run at staggered intervals, the load on your Distributor will decrease, and overall performance will increase. A Publisher, Subscriber, and Distributor server should be tuned as a network application. To do this, right click the My Computer icon, point to Properties, click the Advanced tab, click Performance Options, and select Applications.

Distributor Properties The Distributor Properties menu item allows you to fine tune your distribution database or even to locate the distribution database on another server or drive. Selecting the menu option for Distributor Properties will launch the Publishers and Distributor Properties dialog box (Fig. 2.49). Figure 2.49 Distributor Properties tab

This dialog box is equivalent to the option to set the Advanced Properties you would see in the Customize the Distributor Properties dialog box (Fig. 2.7) if you were to select Yes, let me set the distribution database properties, enable Publishers, or set the publishing settings. Installing Replication 85

The Administrative link password allows you to password protect your distribution database. There is no built-in NT Authentication analog for this option. When you enable Remote Publishers to publish to your distribution database, they will have to supply the Administrative link password at that time. We will discuss the Agent profiles at the end of this section. Clicking Properties will allow you to configure some aspects of your distribution database and Distributor properties (Fig. 2.50). Figure 2.50 Distribution Database Properties

The settings that you can modify here are Transaction retention and History retention. Transaction retention means that transactional data will be retained no more than 72 hours before being deleted from the replication system tables (MSrepl_transactions and MSrepl_commands). By default, the distribution clean up wizard will clean up the transactions that have been replicated to the Subscribers every 10 minutes. The values for Transaction retention and History retention should be larger than the value of the Subscription expiration period (right click on your publication and select publication properties, and look in the subscription expiration section). If all three of these settings don’t match, you may find your subscriptions expire prematurely. If you are doing custom programming where you are rolling your own replication agents, you may want to store the transactions, which would be otherwise flushed, for a longer time period for debugging or logging. You can set this retention time period here. This option is not valid when using snapshot replication or merge replication. Some DBAs like to use this option as a measure of disaster recovery. We cover this in Chapter 10, on Troubleshooting.

86 Chapter 2

Hint: Some DBAs prefer to modify the “At least value” to several days, which will make your MSrepl_transactions and MSrepl_commands tables very large. However, if a Subscriber has to be restored from yesterday’s backup, the Distribution Agent will backfill the missing transactions to sync up the Subscriber using the commands stored in the MSrepl_transactions and MSrepl_commands tables. If these commands had been flushed, you would have to do a resynchronization. The History retention period is by default 2 days, which means historical information is stored for 2 days before being purged from MSdistribution_history. You might want to change this value to 3 or 4 days to account for long weekends, so that when you come back into the office, you can see what has failed or succeeded over the weekend, and to prevent premature subscription expiration. I find this handy as I capture this information to determine overall throughput per Subscriber over a weekend, and detect problem links where low latencies are required to meet SLAs. Notice also that all Publishers that can publish transactions to this database are listed in These Publishers use this database list box. In Figure 2.50, the local Publisher, Hilary2kp, and Hilary2kp\test (another instance) are listed as Publishers. Clicking OK returns us to the opening tab for the Distribution Properties tab. The New button allows you to create another distribution database. Existing publications using the old distribution database will have to be dropped and moved to the new distribution database. New Publishers (note that I mean a new publication server, not new publications) can publish to this new distribution database if you enable them in the Publishers tab (Fig. 2.34). If they were already enabled before you created the new distribution database, they will publish to the old distribution database. It is entirely possible to create a different distribution database for each remote Publisher. If you run out of space on your existing distribution database, you must resort to adding a new file group to your existing distribution database. Use a command similar to the one below: ALTER DATABASE [distribution] ADD FILE(NAME = N'distribution_1_Data', FILENAME = N'd:\SQLData\dist.mdf' , SIZE = 1, FILEGROWTH = 10%) ALTER DATABASE [distribution] ADD LOG FILE(NAME = N'distribution_1_Log', FILENAME = N'd:\SQLData\dist.ldf' , SIZE = 1, FILEGROWTH = 10%) Installing Replication 87

Click New to launch the Distribution Properties dialog box (Fig 2.51) which allows you to create another distribution database on another drive, or another folder, for that matter. Figure 2.51 The Distribution Database Properties tab

Notice here we can name the distribution database to anything (the default will be distribution1) and we can place the data file and the log files wherever we want. The data file should be placed on a RAID 5, and the log file on a RAID 1, 10, or 01 array. The new distribution database will have the same retention periods for the transactions and the history as the original distribution database.

Agent Profiles The Agent Profiles button allows you to set what profile you want your replication agents to run under. A profile is a named group of settings that you can apply to an agent. Each profile encapsulates a set of functionality, and you have the ability to build your own. For instance, let’s look at the Merge Agent’s profiles. The Merge Agent has profiles for • • • • • •

a default setting, a high volume setting, two data validation profiles, a slow link profile for low bandwidth links, a verbose profile for debugging purposes, and a Windows Synchronization Manager profile.

Figure 2.52 illustrates the Agent Profile dialog box, showing all the tabs for the various replication agents tabs. 88 Chapter 2

Figure 2.52 The Agent Profile dialog box

Notice how you can apply the profile that you select to all existing agents, by selecting Change all existing Distribution Agents to use the selected profile. The new profile will be active when the agent is stopped and restarted. Clicking View Details (Fig. 2.53) will allow you to examine the various configuration settings for this profile. The New Profile button will allow you to build a new profile. Figure 2.53 The Agent Profile Details dialog box

The View Details button will allow you to check the settings of a profile. Notice the –LoginTimeout value of 15 seconds. This is acceptable for Distribution agents that are connecting with Publishers, Distributors, or Subscribers that are not under load, but you will find that on some loaded Publishers, Distributors, or Subscribers (especially Subscribers that are connecting to the Distributor over slow, low bandwidth or lossy links), you will


want to adjust this value to 60 or 120 seconds. To do this, you might want to set up a Slow Link Profile. Click New Profile in the Agent Profiles dialog box (Fig. 2.52). This will launch the New Profile dialog box (Fig. 2.54). Figure 2.54 The Replication Agent Profile Details dialog box

In Figure 2.54 we have set the LoginTimeout value to 120. I have positioned the cursor at the –QueryTimeout parameter. Hint: If you have large snapshots, you might want to bump this value to a large value, perhaps 10 minutes or more. Give your profile a name and click OK. Your new profile will then appear in the Distribution Agent Profiles dialog box. Click the radio button in the Use column next to your new profile to select it, and then click OK (Fig. 2.55). Figure 2.55 Selecting the new profile

90 Chapter 2

You will have to stop and start your agent for this profile to be used by your Distribution Agent.

Refresh Rate and Settings If you right click your Replication Monitor Node, you have the option to set your Refresh Rate and Settings (Fig. 2.56). This allows you to control how often you want your replication agents polled. Figure 2.56 Refresh Rate and Settings

Selecting Refresh results pane and Refresh server tree will refresh your results pane and server tree. The results pane is the right-hand portion of your MMC when you expand any of the replication nodes. The server tree is the left-hand portion in any of the replication nodes. You want to set this frequency according to your monitoring needs. I normally leave it off and refresh as needed. If you select Refresh results panes, your Replication Monitor will be automatically refreshed every 10 seconds. This may cause some added load on your Distributor and may cause some locking. The inactivity threshold is the amount of time SQL Server will allow a replication agent to go without hearing back from it. Normally this heartbeat interval is set up for 10 minutes, but for some larger publications, or for some Subscribers that are on low bandwidth links, you will need to bump this value up. If you pick an inactivity threshold value that is too low, the job may be marked as failed prematurely. Hint: This is especially the case when you are distributing large snapshots that take over 10 minutes to push a single table to a Subscriber. Once, I set this value to 1440 (1 day) while sending a particularly large snapshot over a particularly low Installing Replication 91

bandwidth link to Guam before leaving work one day. The next day, my snapshot job had just completed. You might want to do the same when you encounter the same conditions. The Show anonymous subscriptions check box will display anonymous Subscribers in the results pane and the server tree node. By default, this check box will be enabled. If you have hundreds of anonymous Subscribers, you will want to uncheck this option; otherwise your MMC performance will be degraded and your MMC will look cluttered. Replication Monitor will still report job failures for these anonymous Subscribers. Please see the Show Anonymous Subscriptions (on page 94) section for more information on this option.

Select Columns This menu option allows you to configure your results pane for your agents. You can configure the columns displayed for the following agents: • • • • • • • •

Trans(actional) Publication View Merge Publication View Snapshot Agents Miscellaneous Agents Merge Agents Distribution Agents Log Reader Agents Queue Reader Agents

This option is illustrated in Figure 2.57.

92 Chapter 2

Figure 2.57 Select Columns

You can select the columns that you wish to show up in each view, or select the order of them. I like to move the Status and the Conflicts columns closer than the way Microsoft orders them for the Merge Agents tab. Use Move Up and Move Down for this. This way the conflicts and job failures are more visible, and you can sort the Columns to display list to make the failed jobs bubble to the top. In the default configuration, the Publisher Conflicts and Subscriber Conflicts columns for the merge replication agents are too far to the right to display and are easily missed. I normally uncheck the Type column. The Type column is either push or pull. My reasoning is that you should know what type of publication you have set up, and if you forget, you can add this column back in, refresh, note the publication type, and then exclude it again. The Status column is the status of your agent. It can be Started, Succeeded, In progress, Idle, Retry, Fail, etc. This is another column that should be moved closer to the top. The Reset button will always reset your column arrangement to Microsoft’s defaults. These settings are stored in the following registry key: HKEY_CURRENT_USER\Software\Microsoft\_ Microsoft SQL Server\80\Tools\SQLEW\Replication

You can export this key into a reg file for recovery options or to deploy it to other servers or machines you will be running EM on.


Show Anonymous Subscriptions There are two types of subscriptions: • Named • Anonymous Named subscriptions are subscriptions that are tracked by name in the distribution database. Transactions are retained in the MSrepl_transactions and MSrepl_commands tables until they are replicated to the named Subscriber. History information for the subscription is stored in the distribution database as well. Anonymous subscriptions are most often used with pull subscriptions, especially when you are replicating over the Internet or have many Subscribers. Transactions are retained in the MSrepl_transactions and MSrepl_commands tables until the retention period for the server has expired. You set this in the Distribution Database Properties dialog box (Fig. 2.51). You can only create pull subscriptions that are anonymous; you can’t create anonymous push subscriptions. Figure 2.58 shows an anonymous subscription: Hilary2kp\Test:c-156.

Figure 2.58 Anonymous Subscriptions showing up in the publications folder If Show Anonymous Subscriptions is not selected, this subscription will not show up, as illustrated in Figure 2.59.

94 Chapter 2

Figure 2.59 Anonymous Subscriptions hidden in the Publishers folder The Show Anonymous Subscriptions option will show agents for the anonymous subscriptions if this option is checked. I normally leave this unchecked, as there is some overhead with enabling this option. Note that the anonymous subscriptions will show up in the Publishers folder as well as in the replication agents’ folders. Please refer to Chapters 3, 4, and 5 for information on how to set up anonymous subscriptions.

Replication Alerts If you expand the Replication Monitor folder in EM, you will notice three nodes or folders: Publishers, Agents, and Replication Alerts. Replication Alerts are illustrated in Figure 2.60.

Figure 2.60 Replication Alerts Installing Replication 95

Replication Alerts are alerts that are replication specific. There are eight default Replication Alerts. Table 2.2 Replication Alerts Alert

Description

Replication: agent custom shutdown

Raised when an agent shuts down

Replication: agent failure

Raised when an agent fails

Replication: agent retry

Raised when an agent retries

Replication: agent success

Raised when an agent succeeds

Replication: expired subscription dropped

Raised when a Subscriber expires and is dropped

Replication: Subscriber has passed data validation

Raised when an agent passes data validation

Replication: Subscriber reinitialized after validation failure

Raised when Subscriber is reinitialized after a data validation failure

These alerts will be raised whenever one of the above conditions happen. You can have these alerts write a message to the event log, execute a job, email an operator, send out a network message, or send out a page. The problem with these alerts is in most distributed environments, you get far too many job failures or job retries. In fact, many DBAs will either set their jobs to restart every 10 minutes, and then configure them to run continuously, or will have the job return to the first step on failure. Figure 2.61 illustrates this technique. I first heard about this technique from Gary Whitley, a Microsoft PSS support engineer in Charlotte, N.C. Figure 2.61 A Distribution Agent job which loops back to restart on failure

96 Chapter 2

Replication Stored Procedures Most DBAs will install replication using the Install Replication Wizard; however, you can also use the replication stored procedures shown below. This is not the complete set of replication stored procedures; rather, it is the subset that are used to install, configure, modify, and drop replication and replication objects. For a detailed study of replication stored procedures, go to Chapter 6.

Replication Stored Procedures to Install Replication To install replication, you need to run the first 3 stored procedures in order. We’ll look at each stored procedure and its options, then present an example.

sp_adddistributor The sp_adddistributor stored procedure is used to configure your server as a Distributor or, if you are using a remote Distributor, to configure your server to use the remote Distributor. It creates an entry in the sysservers table, marks the server entry as a Distributor, and stores property information. The procedure has the following parameters: • • • •

@distributor @heartbeat_interval @password @from_scripting

@distributor The name of the server acting as your distributor. There is no default. Here is an example of its use: sp_adddistributor @distributor='hilary2kp\test'

If you are using a remote Distributor, the value of the @distributor parameter would be your Distribution server name, and you would issue this statement on your Publisher. The data type for the @distributor parameter is sysname. @heartbeat_interval How long a replication agent can run before reporting back to the SQL Server. If the @heartbeat_interval exceeds this value, the job is considered suspect and marked as failed. The default is 10 minutes and is an integer. sp_adddistributor @distributor='hilary2kp\test', @heartbeat_interval=20


In the above example, we are setting the heartbeat interval to 20 minutes. The data type for the @heartbeat_interval parameter is int. @password Your Administrative Link password, the password that remote Publishers need to know to use your local Distributor. The default is NULL, which will force the SQL Server to auto generate a random password. Here is an example of its use: sp_adddistributor @distributor='hilary2kp\test', @heartbeat_interval=10, @password='se1cure#'

The data type for the @password parameter is sysname. @from_scripting For internal use only.

sp_adddistributiondb The sp_adddistributiondb procedure creates a new distribution database and installs the Distributor schema. The sp_adddistributiondb procedure has the following parameters: • • • • • • •

@database @data_folder @data_file @data_file_size @log_folder @log_file @log_file_size

• • • • • • • •

@min_distretention @max_distretention @history_retention @security_mode @login @password @createmode @database

@database The name of your distribution database. There is no default. Here is an example of its use: sp_adddistributiondb 'distribution'

The data type for the @database parameter is sysname. @data_folder The directory used to store the distribution database data file. This parameter is optional with a default of your SQL Server data directory: C:\Program Files\Microsoft SQL Server\Mssql\Data. Here is an example of its use: sp_adddistributiondb 'distribution', 98 Chapter 2

@data_folder='D:\Program Files\Microsoft SQL Server\Mssql\Data'

The data type for the @data_folder parameter is nvarchar(255). @data_file The name of the database file for your distribution database. The default is the name of your database. In the above examples, it will be distribution.mdf. This parameter is optional. Here is an example of its use: sp_adddistributiondb 'distribution', @data_folder='D:\Program Files\Microsoft SQL Server\Mssql\Data', @data_file='distribution.mdf'

Note that if you do not append the .mdf on your data file name, it will create a database without the .mdf extension and still work. The data type for the @data_file parameter is nvarchar(255). @data_file_size The initial data file size in MB. The default is 2 MB, and this parameter is optional. This parameter is an integer value in MB, so a value of 5 would create a distribution database with a data file of 5 MB. This parameter must be used in conjunction with an @createmode setting of 1. Here is an example of its use: sp_adddistributiondb 'distribution', @data_folder='D:\Program Files\Microsoft SQL Server\Mssql\Data', @data_file='distribution.mdf', @data_file_size=5, @create_mode=1

The data type for the @data_file_size parameter is int. @log_folder The @log_folder parameter is very similar to the @data_folder parameter, only its value specifies where the log file should be located. Ideally, on highvolume servers, your log file should be on a RAID 1 or 10 array. This value is optional and has a default as your SQL Server data directory. Here is an example of its use: sp_adddistributiondb 'distribution', @data_folder='D:\Program Files\Microsoft SQL Server\Mssql\Data', @data_file='distribution.mdf', @log_folder='D:\Program Files\Microsoft SQL Server\Mssql\Data', @log_file_size=50

The data type for the @log_folder parameter is nvarchar(255).


@log_file The name of the database log file for your distribution database. The default is the name of your database. In the above examples, it will be distribution.ldf. This parameter is optional. Here is an example of its use: sp_adddistributiondb 'distribution', @data_folder='D:\Program Files\Microsoft SQL Server\Mssql\Data', @data_file='distribution.mdf', @log_folder='D:\Program Files\Microsoft SQL Server\Mssql\Data', @log_file='distribution.ldf', @log_file_size=50

The data type for the @log_folder parameter is int. @log_file_size The initial log file size in MBs. The default is 2 MBs, and this parameter is optional. This parameter is an integer value in MBs, so a value of 5 would create a distribution database with a log file of 5 MBs. This parameter must be used in conjunction with a @createmode setting of 1. Here is an example of its use: sp_adddistributiondb 'distribution', @data_folder='D:\Program Files\Microsoft SQL Server\Mssql\Data', @data_file='distribution.mdf', @log_folder='D:\Program Files\Microsoft SQL Server\Mssql\Data', @log_file='distribution.ldf', @log_file_size=50,@create_mode=1

The data type for the @log_file_size parameter is int. @min_distretention Determines the minimum time transactions will stay in the MSrepl_commands and MSrepl_transactions tables. Replicated transactions (transactions that are applied on the Subscriber) will be deleted every time the Distribution Clean up Agent runs. Transactions that are not replicated will remain in the tables until they are older than the value specified in the @max_distretention parameter, and they will then be removed by the Distribution Clean up Agent. By default the Distribution Clean up Agent runs every 10 minutes. DBAs occasionally want to keep the transactions longer for debugging or disaster recovery purposes. Here is an example of its use: sp_adddistributiondb 'distribution', @min_distretention=24

The data type for the @min_distretention parameter is int.

100 Chapter 2

@max_distretention The maximum time in hours that transactions are stored in the MSrepl_commands and MSrepl_transactions table before being removed by the Distribution Clean up Agent. The default is 72 hours. Subscriptions that have not received replicated commands and that are older than the @max_distretention period are marked as inactive and need to be reinitialized. Here is an example of its usage: sp_adddistributiondb 'distribution', @max_distretention=24

The data type for the @max_distretention parameter is int. @history_retention The number of hours that historical information of the Distribution, Merge, Queue Reader, Log Reader, and Snapshot agents is stored. The default is 48 hours. I prefer to set this value to 72 hours to account for long weekends. The historical data for these agents is retained in the following tables: • MSsnapshot_history • MSlogreader_history • MSdistribution_history

• MSmerge_history • MSrepl_errors • MSqreader_history

Here is an example of its usage: sp_adddistributiondb 'distribution', @history_retention =72

The data type for the @history_retention parameter is int. @security_mode Controls the authentication mechanism used when creating the distribution database and related objects. The default is 0, which is SQL Server authentication; 1 specifies Windows Authentication. If you select SQL Server authentication, you must specify a login and password. Here is an example of its usage illustrating SQL Server authentication: sp_adddistributiondb 'distribution', @security_mode =0, @login='sa', @password='se1cure#'

Here is an example of its usage illustrating NT authentication: sp_adddistributiondb 'distribution', @security_mode =1

The data type for the @security_mode parameter is int.


@login The SQL Server login you want to use to create the distribution database and related objects. Use this parameter in conjunction with a @security_mode parameter of 1. The default is ‘sa’. This parameter is optional. Here is an example: sp_adddistributiondb 'distribution', @security_mode =0, @login='sa', @password='se1cure#'

Here is an example illustrating the optional usage of this parameter: sp_adddistributiondb 'distribution', @security_mode =0, @password='se1cure#'

If you specify @login and @password with the @security_mode of 1 (NT authentication), these values will be ignored. The data type for the @login parameter is sysname. @password The @password is the password for the login specified in the @login parameter. This password is used when creating the distribution database and related objects. There is no default. Please refer to the usage of the @login parameter for an example of this parameter. The data type for the @password parameter is sysname. @createmode Controls how the SQL Server builds the distribution database. Table 2.3 lists the values for this parameter. Table 2.3 @CreateMode Values (This table comes from BOL) Value

Description

0

The distribution database is created by attaching a copy of the distribution database model files (distmdl.mdf). The distribution database is created using the Create Database command, or an existing distribution database is used and then the instdist.sql file is run to create the replication objects. The advantage of this option is that you can modify the instdist.sql script yourself to include custom stored procedures, to add agents, or to configure other parameters. For internal use only.

1

2

The data type for the @createmode parameter is int.

102 Chapter 2

sp_adddistpublisher The sp_adddistpublisher stored procedure configures a Publisher to use the distribution database. This procedure is executed in the distribution database and has the following parameters: • @publisher • @working_directory • @distribution_db • @trusted • @security_mode • @encrypted_password • @login • @thirdparty_flag • @password @publisher The name of your Publisher, whether the Publisher is on the same server as the Distributor or on a different server than the Distributor. Here is an example. This stored procedure is issued on your Distributor in the distribution database: sp_adddistpublisher @publisher=@@servername, @distribution_db='distribution', @working_directory='\\hilaryk2p\temp'

The data type for the @publisher parameter is sysname. @distribution_db The name of the distribution database. This parameter is not optional and there is no default. Please consult the @publisher parameter for an example of its usage. The data type for the @distribution_db parameter is sysname. @security_mode Sets the authentication mode used by the replication agents as they connect to the Publisher. This parameter is optional with a default of NULL, which means the SQL Server will decide based on the Operating System (OS) type of the Distributor. If the OS on the Distributor is Win9x, SQL Authentication will be used. NT 4.0, Win2k, and Win2003 will use NT Authentication. A value of 0 will force SQL Server authentication, and a value of 1 will force NT Authentication. You will want to use SQL Authentication when NT Authentication won’t work. For example, if • your Publisher and Distributor are on different servers in different untrusted domains; • your Publisher and Distributor are on different servers in a workgroup; or


• your Publisher and Distributor are on different servers connected across the Internet Here is an example using SQL Authentication: sp_adddistpublisher @publisher=@@servername, @distribution_db='distribution', @working_directory='\\hilaryk2p\temp', @security_mode=0, @login='sa', @password='se1cure#'

Here is an example using NT Authentication: sp_adddistpublisher @publisher=@@servername, @distribution_db='distribution', @working_directory='\\hilaryk2p\temp', @security_mode=1

The data type for the @security_mode is int. @login The login you wish your Publisher to use to connect with your Distributor. This parameter is required if @security_mode is 0. The default is sa. This parameter is used by replication agents to connect to the Publisher. Please refer to the section on the @security_mode parameter for an example of how this parameter works. The data type for the @login parameter is sysname. @password The password you wish your Publisher to use to connect with your Distributor when logging on using the login in the @login parameter. This parameter is required if @security_mode is 0. The default is NULL. This parameter is used by replication agents to connect to the Publisher. Please refer to the section on the @security_mode parameter for an example of how this parameter works. The data type for the @password parameter is sysname. @working_directory The path that is used to store your snapshot files. The name should be specified in the UNC format. Please refer to the section on the @publisher parameter for an example of how this parameter works. The data type for the @working_directory parameter is nvarchar(255). @trusted When you use a remote Distributor, you can lock down your distribution database so that only Publishers who know the Administrative Link password can use your Distributor. You can bypass this by using the @trusted parameter. Setting the @trusted parameter to ‘True’ will allow bypass to

104 Chapter 2

locked down Distributors, so remote Publishers don’t need to enter the Administrative Link password. This parameter has three values: • True: Remote Publishers do not need to enter an Administrative Link password in the @password parameter when using a local Distributor. • False: Remote Publishers do need to enter an Administrative Link password in the @password parameter when using a local Distributor. • NULL: If you have a remote Publisher, this value defaults to true. If you have a local Publisher, this value defaults to False, but any password set for the Distributor is also set for the local distribution Publisher. Here is an example of its usage for a remote Publisher (this is run on the Distributor in the distribution database): sp_adddistpublisher @publisher='RemoteServerName', @distribution_db='distribution', @working_directory='\\hilaryk2p\temp', @trusted='true'

If I examine the properties for this Publisher on the Distributor, I will notice that the This Publisher requires a password to establish a link to the Distributor check box is unchecked. This is illustrated in Figure 2.62. Figure 2.62 A Trusted Publisher using the Local Distributor

The data type for the @trusted parameter is nvarchar(5).


@encrypted_password Used internally by the SQL Server when transmitting the Administrative Link password. This is encrypted so that network sniffers will not be able to read the Administrative Link password. The data type for the @encrypted_password parameter is bit. @thirdparty_flag Used when you are using the ActiveX Distribution to publish from a Heterogeneous data source to a local SQL Server Distributor. The default is 0, which is a local SQL Server database, and the other possible value is 1, which is another vendor’s RDBMS, or another data source. The data type for the @thirdparty_flag parameter is bit.

Replication Stored Procedures to Modify the Distributor The following replication stored procedures are used to change the properties of your distribution database and the publishers that use it. Most of these stored procedures, when issued without any parameters, will display a list of which properties they can change. You then have the option of specifying that property, along with the value you wish to change that property to.

sp_changedistpublisher The sp_changedistpublisher allows you to make changes to the properties of the distribution Publisher. This stored procedure is executed at the Distributor on any database. This stored procedure has the following parameters. • @publisher • @property • @value @publisher The name of your Publisher. There is no default and this parameter is not optional. Here is an example of its usage: sp_changedistpublisher @@servername

Running this procedure will display a list of properties that can be changed for your Publisher. The data type for the @publisher parameter is sysname.

106 Chapter 2

@property The @property parameter is the property you wish to change the value of. Here is an example for changing the status of your Publisher: sp_changedistpublisher @@servername, @property='active', @status='false'

Here is a list of the properties you can modify for an existing Publisher: • Active: Whether a remote Publisher is using the local server as a Distributor. A value of 0 indicates no; a value of 1 indicates yes. • distribution_db: The name of the distribution db. • login: The login the Publisher is using to connect to the Distributor if you are using a security_mode of 0. • password: The password the Publisher is using to connect to the Distributor if you are using a security_mode of 0. • security_mode: The security mode the Publisher will use to connect to the Distributor. This property has two values: 0 for SQL Server Authentication, 1 for NT Authentication. • working_directory: The working directory for the snapshot files. This should be in the format of a UNC. The data type for the @property parameter is sysname. @value The @value parameter contains the new value you wish to use for the property you are changing. This parameter is not optional for all properties, and if it is optional, it has a value of NULL, which will be the default for that property. Please refer to the section on the @property parameter for an example of how this parameter works. The data type for the @value parameter is nvarchar(255).

sp_changedistributiondb The sp_changedistributiondb changes the properties of the distribution database. This stored procedure is executed at the Distributor on the distribution database. This stored procedure has the following parameters: • @database • @property • @value


@database The name of your distribution database on your Distributor. There is no default for this parameter and it is not optional. Here is an example illustrating the usage of the @database parameter, changing the history_retention property: sp_changedistributiondb @database='distribution', @property='history_retention',@value=90

The data type for the @database parameter is sysname. @property The property you wish to change on your distribution database. This parameter is optional and has a default of NULL. If you do not specify a parameter, a list of all the properties you can change will be displayed. Here is an example of its usage, displaying all properties you can change. sp_changedistributiondb @database='distribution'

Here is a list of the properties you can change: • history_retention • max_distretention • min_distretention Please refer to the section on the @database parameter for an example of how this parameter works. The data type for the @property is sysname. @value The value you wish to change the property specified in the @property parameter to. This parameter is not optional for all properties, and if it is optional, it has a value of NULL, which will be the default for that property. Please refer to the section on the @database parameter for an example of how this parameter works. The data type for the @value parameter is nvarchar(255).

sp_changedistributor_password The sp_changedistributor_password stored procedure changes the Administrative Link password for a Distributor. This stored procedure is executed at the Publisher on the distribution database. This procedure has a single parameter: • @password. An example of its usage: sp_changedistributor_password @password='se1cure#' 108 Chapter 2

The data type for the @password parameter is sysname.

sp_changedistributor_property The sp_changedistributor_property stored procedure is used to make changes to the properties of your Distributor server. This stored procedure is executed at the Distributor on the distribution database. This stored procedure has the following parameters: • @property • @value @property The name of the property you wish to modify. The only property you can modify is the heartbeat_interval parameter. If you run this stored procedure without specifying any properties or using a property value of NULL, this property is displayed. Here is an example of its usage: sp_changedistributor_property @property='heartbeat_interval', @value=20

The heartbeat_interval is the amount of time that can elapse before a replication agent is considered suspect. The data type for the @property parameter is sysname. @value The value of the property you wish to change. In this case, the only property you can change is the heartbeat_interval property. The default is null, which will change the heartbeat_interval to 10 minutes. The data type for the @value parameter is nvarchar(255).

Replication Stored Procedures to Display Replication Properties The following replication stored procedures are used to display the properties of your Distributor, distribution database, and the Publishers that use it. The stored procedures either return a results set or allow you to check the value of an individual property. When the stored procedure returns a results set, I explain what each of the columns mean in the tables accompanying that stored procedure’s description.

sp_helpdistpublisher The sp_helpdistpublisher stored procedure returns the properties of a Publisher that is using the local Distributor. This procedure is run on the Distributor in any database. The parameters for this stored procedure are


• @publisher • @check_user

@publisher The name of your Publisher. This parameter is optional, and if the @publisher parameter is not used, a list of all Publishers will be returned. Here is an example that returns a list of all publishers using the local Distributor: sp_helpdistpublisher

Here is an example that displays the properties of the local Publisher: sp_helpdistpublisher @publisher=@@servername

The data type for the @publisher parameter is sysname. @check_user The @check_user parameter is undocumented and is for internal use only. Here is a list of the columns returned by the sp_helpdistpublisher procedure and a brief description of them.

110 Chapter 2

Table 2.4 Columns returned by the sp_helpdistpublisher (This table comes from BOL.) Column Name

Description

Name distribution_db

Name of Publisher The name of the Distribution database for the specified Publisher

security_mode

Security mode used by the replication agent in a push subscription to connect to the Publisher. Possible values are 0 for SQL Authentication, and 1 for NT Authentication.

Login

If the security mode is 0, the login is the name of the account used by the Publisher to connect with the Distributor.

Password

Password returned (in simple encrypted form)

Active

Whether a remote Publisher is using the local server as a Distributor. Possible values are 0 for no, and 1 for yes.

working_directory

The UNC path of your snapshot folder. By default it is \\ServerName\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData

Trusted

Whether the Publisher is trusted and does not have to use the Administrative Link Password, or is untrusted and does have to.

thirdparty_flag

Whether the Publisher is using a Microsoft SQL Server database (0) or is a Heterogeneous data source (1).

sp_helpdistributiondb The sp_helpdistributiondb stored procedure is used to display properties of your distribution database. This stored procedure is executed at the Distributor on the distribution database. This stored procedure has a single parameter, which is the name of your distribution database. Here is a list of the columns returned by the sp_helpdistributiondb procedure and a brief description of them.


Table 2.5 Columns returned by the sp_helpdistributiondb stored procedure (This table comes from BOL.) Column name

Description

distribution_database min_distretention

Name of the database Minimum retention period, in hours, before replicated transactions are deleted max_distretention Maximum retention period, in hours, before transactions are deleted history retention Number of hours to retain replication agent history history_clean up_agent Name of the History Clean up Agent distribution_clean up_agent Name of the Distribution Clean up Agent Status Not supported data_folder Name of the directory used to store the distribution database’s data files data_file Name of the database file data_file_size Initial data file size in megabytes log_folder Name of the directory for the database log file log_file Name of the log file log_file_size Initial log file size in megabytes

sp_helpdistributor The sp_helpdistributor stored procedure displays information about the Distributor, distribution database, working directory, and SQL Server Agent user account. This stored procedure is executed at the Distributor on any database. The sp_helpdistributor procedure has the following parameters: • • • • • • •

@distributor @distribdb @directory @account @min_distretention @max_distretention @history_retention

112 Chapter 2

• • • • • •

@history_cleanup_agent @distrib_cleanup_agent @publisher @local @rpcsrvname @distributor

This stored procedure can be used in two ways. You can execute it without any parameters and return the results set illustrated in Table 2.5: sp_helpdistributor

Or you can query a specify property/parameter. The list of parameters is specified above. Declare @distributorname varchar(255) exec sp_helpdistributor @distributor = @distributorname OUTPUT print @distributorname

This will display only the name of the Distributor. Some parameters will display only the results set illustrated in Table 2.5, e.g. the @publisher parameter. @distributor The name of the Distributor server. There is no default, but this parameter is optional. This is an output parameter. For an example of its usage, please refer to the above code sample. The data type for the @distributor parameter is sysname. @distribdb The name of the distribution database. There is no default, but this parameter is optional. This is an output parameter. Here is an example of this parameter’s usage: Declare @distribdnname varchar(255) exec sp_helpdistributor @distributor = @distribdnname OUTPUT print @distribdnname

This will display only the name of the distribution database. The data type for the @distribdb parameter is sysname. @directory The name of the folder where your snapshot files are created and stored. By default this path is \\ServerName\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData. There is no default, but this parameter is optional. This is an output parameter. Here is an example of this parameter’s usage: Declare @directoryname varchar(255) exec sp_helpdistributor @directory = @directoryname OUTPUT print @directoryname

This will display only the name of the snapshot share. The data type for the @directory parameter is nvarchar(255). Installing Replication 113

@account The name of the NT account used by the Publisher to connect to the Distributor. There is no default, but this parameter is optional. This is an output parameter. Here is an example of this parameter’s usage: Declare @accountname varchar(255) exec sp_helpdistributor @account = @accountname OUTPUT print @accountname

This will display only the name of the account used by the Publisher to connect to the Distributor’s snapshot share where the snapshot files are created and stored. The data type for the @account is nvarchar(255). @min_distretention The minimum length of time that replicated transactions are stored in the distribution database before the Distribution Clean Up Agent will remove them. There is no default, but this parameter is optional. This is an output parameter. Here is an example of this parameter’s usage: Declare @retentiontime varchar(255) exec sp_helpdistributor @min_distretention = @retentiontime OUTPUT print @retentiontime

This will display only the minimum distribution retention time. The data type for the @min_distretention is int. @max_distretention The maximum length of time that replicated transactions are stored in the distribution database before the Distribution Clean Up Agent will remove them. There is no default, but this parameter is optional. This is an output parameter. Here is an example of this parameter’s usage: Declare @retentiontime varchar(255) exec sp_helpdistributor @max_distretention = @retentiontime OUTPUT print @retentiontime

This will display only the maximum distribution retention time. The data type for the @max_distretention is int. @history_retention The maximum length of time that historical information from the replication agents is stored in the distribution database. There is no default, but this parameter is optional. This is an output parameter. Here is an example of its usage:

114 Chapter 2

Declare @historyretentiontime varchar(255) exec sp_helpdistributor @history_retention = @historyretentiontime OUTPUT print @historyretentiontime

This will display only the history retention time. The data type for the @history_retention is int. @history_cleanup_agent The name of the History clean up agent. By default, the SQL Server will name this agent “Agent history clean up: distribution”. There is no default, but this parameter is optional. This is an output parameter. Here is an example of its usage: Declare @history_cleanupagent_name varchar(255) exec sp_helpdistributor @history_cleanup_agent = @history_cleanupagent_name OUTPUT print @history_cleanupagent_name

This will display only the history clean up agent name. The data type for the @history_cleanupagent is nvarchar(100). @distrib_cleanup_agent The distribution clean up agent. By default, the SQL Server will name this agent “Distribution clean up: distribution”. There is no default, but this parameter is optional. This is an output parameter. Here is an example of its usage: Declare @distrib_cleanupagent_name varchar(255) exec sp_helpdistributor @distrib_cleanup_agent = @distrib_cleanupagent_name OUTPUT print @distrib_cleanupagent_name

This will display only the distribution clean up agent name. The data type for the @distrib_cleanupagent is nvarchar(100). @publisher The Publisher. If you have more than one Publisher, only the local Publisher will be displayed. There is no default, but this parameter is optional. Here is an example of its usage: exec sp_helpdistributor @publisher = @@servername

This will display only the results set illustrated in Table 2.5. The data type for the @publisher is sysname. @local The @local parameter determines whether the results set display should be for the local Publisher or the Distributor. Here is an example of its usage: Installing Replication 115

exec sp_helpdistributor @local = @@servername

This will display only the results set illustrated in Table 2.5. The data type for the @local is sysname. @rpcsrvname The replication executables (distrib, logreader, snapshot, qrdrsvc, replmerg) do all their server-to-server communication via Remote Procedure Calls (RPCs). The @rpcsrvname is the name of the server that issues these calls. The server that the SQL Server replication uses is a virtual server, which is by default a remote server called repl_distributor. You can see this virtual server if you expand the security folder for your server in EM, and then expand the remote server node. Here is an example of its usage: Declare @rpcserver_name varchar(255) exec sp_helpdistributor @rpcsrvname = @rpcserver_name OUTPUT print @rpcserver_name

This will display only the RPC server name. The data type for the @rpcsrvname is sysname. Table 2.5 Columns returned by issuing a sp_helpdistributor (This table comes from BOL.) Column Name

Description

Distributor distribution database Directory

Name of the Distributor Name of the distribution database Name of the path where the snapshot files are stored Name of the NT account used by the Publisher to connect with the Distributor Minimum distribution retention period Maximum distribution retention period History retention period Name of the History Clean up Agent Name of the Distribution Clean up Agent agent Name of the remote or local Distributor Login used for remote procedure calls to the remote Distributor

Account min distrib retention max distrib retention history retention history clean up agent distribution clean up rpc server name rpc login name

If the distribution database is not installed, a NULL value is returned.

116 Chapter 2

Replication Stored Procedures to Drop Replication The following stored procedures are used to drop or disable the Distributor. These stored procedures are listed in the order that they must be run to drop replication. If you are bold you can issue a sp_dropdistpublisher @publisher= @@servername, @no_checks=1

or a sp_dropdistributor @no_checks=1

and be done with it; however, this is not always the wisest choice if you have remote Publishers using this Distribution server.

sp_dropdistpublisher The sp_dropdistpublisher stored procedure will drop Publishers using the local distribution database. This stored procedure is executed at the Distributor on any database. This stored procedure has 2 parameters: • @publisher • @no_checks @publisher The @publisher parameter is the name of the Publisher you want to drop from the local Distributor. This parameter is not optional and there is no default. Here is an example of its usage: sp_dropdistpublisher @publisher='albanysql02'

The data type for the @publisher is sysname. @no_checks Sometimes a remote Publisher using your local Distributor will go offline unexpectedly and will have to drop it from your local distribution database. If you try to run sp_dropdistpublisher when the remote Publisher is offline, this procedure will fail. If you set the value of @no_checks to 1, the sp_dropdistpublisher stored procedure will not attempt to contact the remote Publisher, and the stored procedure will succeed. If there is a chance that the remote Publisher will come back online, you will have to manually remove replication from this server by issuing sp_dropdistributor with @ignore_distributor = 1 on the remote Publisher. If the value of @no_checks is 0, and the remote Publisher is offline, this procedure will fail. If the Publisher is local, and you issue this command and there are any publications, this procedure will again fail, and you will Installing Replication 117

have to manually drop the publications. The default for this stored procedure is 0. Here is an example of its usage: sp_dropdistpublisher @publisher='albanysql02', @no_checks=1

If you have only a local Publisher, you can issue this command and it will completely disable replication without your having to issue any of next two stored procedures. The data type for the @no_checks is bit.

sp_dropdistributiondb The sp_dropdistributiondb stored procedure drops the local distribution database. This stored procedure is executed at the Distributor on any database and has the following single parameter: • @database @database The @database parameter is the name of your distribution database, which by default is called distribution. There is no default for this parameter. Here is an example of its usage: sp_dropdistributiondb @database='distribution'

The data type for the @database is sysname.

sp_dropdistributor The sp_dropdistributor stored procedure uninstalls the Distributor. This stored procedure is executed at the Distributor or on a remote Publisher on any database. This stored procedure has two parameters: • @no_checks • @ignore_distributor @no_checks The @no_checks parameter will completely disable replication if it has a setting of 1. Any remote Publishers using this distribution database will be orphaned, and you will have to connect to them and issue an sp_dropdistributor with @ignore_distributor = 1. The default for this procedure is 0, and this parameter is optional. If you specify a value of @no_checks for this procedure, if there is a distribution database on your Distributor, this procedure will fail. Here is an example of its usage: sp_dropdistributor @no_checks=1

The data type for the @no_checks is bit. 118 Chapter 2

@ignore_distributor Allows the user to drop replication, but it leaves the distribution database intact. This parameter is used in recovery operations when the Distributor is offline or being uninstalled. The default for this procedure is 0. A value of 0 for this parameter will cause the SQL Server to connect with the Distributor and remove all replication objects. If the SQL Server is unable to connect to the Distributor, the stored procedure fails. If this parameter has a value of 1, the SQL Server attempts to connect to the Distributor, and if it does so, it will remove all replication objects. If it can’t connect to the Distributor, the procedure succeeds, but no replication objects are removed. The data type for the @ignore_distributor is bit.

Summary This chapter focused on installing replication on your server, as well as enabling Subscribers. You should have learned all aspects of installing and configuring your distribution database through Enterprise Manager, how to configure your local Distributor for remote Publishers, and how to enable Subscribers. We took a look at how to enable replication to other vendors’ databases. We then took a brief look at profiles, and Replication Alerts, their usage and shortcomings. We then had a look at the replication stored procedures that are necessary to install, configure, and remove replication. We will now move on in subsequent chapters to how to create your publications using EM, stored procedures, SQL DMO, and ActiveX controls and how to deploy them to Subscribers.


A Guide to SQL Server 2000 Transactional and Snapshot Replication

Recommend Documents