Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » SQL Server » SQL Server Articles Archive » Replicating over the Internet
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 5064
 

Replicating over the Internet

by Hilary Cotter

One of the most frequently asked questions about replication is how to replicate over the Internet.

To replicate over the Internet, you will need a File Transfer Protocol (FTP) Server (not necessarily on the Publisher), and your Subscriber and Publisher must be configured for mixed authentication.

This article is broken into four sections:

      • Configuring your Publication
      • Configuring your Subscriber/Subscription
      • Configuring your FTP Server
      • ActiveX Controls

Configuring Your Publication to Replicate Over the Internet

Ideally, you will have registered your publisher in Enterprise Manager using its NetBIOS name. You can determine your NetBIOS name by issuing the following in Query Analyzer:

  Master.dbo.xp_cmdshell 'echo %computername%'
  Select @@ServerName

Two rows will be returned, and they should be identical. If they arent, delete your SQL Server registration and re-register your SQL Server using what is returned from the first statement.

If Select @@ServerName returns a NULL, issue the following commands:

  Use master 
  GO
  Sp_DropServer @@ServerName
  GO 
  Use master 
  GO
  Sp_AddServer 'NewServerName', 'local' 
  GO

Create your publication as you normally do. For SQL 2000, after you have created your publication, click on the Publication Properties button as illustrated in figure 1.

Figure 1: The Publication Properties dialog box.

You can also access your publication properties by expanding the Replication node, then the Publications node, right-clicking on your publication, and selecting Properties. For SQL 2005, expand the Replication node, right click on Local Publications, right click on your publication and select properties.

Once the Publication Properties dialog box opens; click on the Snapshot Location tab as illustrated in figure 2:

Figure 2: The Snapshot Location tab.

You can also click on the Snapshot Location tab in SQL 2005, as illustrated in figure 3:

Figure 3: The snapshot location dialog box for SQL 2005.

We will go over each of the options in turn first for SQL 2000.

SQL Server 2000

Generate snapshots in the following location

You must check the Generate snapshots in the following location check box to enable the other options (refer to figure 4 to see the enabled options). You can uncheck the Generate snapshots in the normal snapshot folder.

Folder

You will be creating your snapshot files in the Folder text box. For your Folder, select either the root of your FTP Server (which by default will be C:\Inetpub\FtpRoot, although many Windows Administrators will move this location to another drive to prevent cannonicalization hacks), or select the physical root of an FTP virtual directory (we will cover how to create an FTP Virtual Directory later in this article).

For example, if you do create a virtual directory called SQLVD, which has a physical root located at D:\SQLVD, you would enter the physical root (D:\SQLVD). Keep in mind that for the Client Path to this folder from the FTP root, you would now enter the FTP virtual directory name in addition to /ftp. So, in our case, with a FTP Virtual Directory of SQLVD you would enter /SQLVD/ftp.

Figure 4: The Snapshot Location tab with all options enabled.

Compressing your snapshots

After you have checked the Generate snapshots in the following location check box, you have the options to compress your snapshot and use FTP to transfer your snapshot to your subscribers. If you are using FTP, you will probably want to compress your snapshots to minimize the transfer time over the Internet. The problems with compressing your snapshot are:

      • Microsoft only supports using the CAB file format to compress your snapshots and there is a 2 Gig CAB file size limitation,
      • you will need more disk space on your subscriber to hold the cab file and to extract it. By default, the CAB file will be extracted in %windir%\temp within a subdirectory named with a random GUID.

Subscribers can access this folder using FTP

The two transfer mechanisms available to deploy your snapshot are a file copy using UNC share, and FTP. If you are replicating over the Internet you will likely be using a pull subscription and have the ports necessary to do the file copy blocked at your firewall, as having them open is a security risk.

While it is possible you could do a push subscription, it is likely to be impractical as chances are your Subscribers do not have fixed IPs and there will be host resolution problems as your Publisher tries to connect to them.

FTP Server Name

The FTP Server Name must be the Fully Qualified Domain Name (FQDN) of your ftp server. Your FTP Server is likely to be the same as your Publisher, but does not have to be. Typical names will look like this Publisher.MyDomain.com, or Publisher.SubDomain.MyDomain.com. The FTP Server name has to be an FQDN as the SQL 2000 Subscriber will connect to the Publisher, retrieve the FTP Server name and then connect to it. If you enter a NetBIOS name of your FTP Server, the Subscribers will be unlikely to find it unless you have an entry for it in your hosts file.

Port

This will be the port that your FTP Server listens on, likely to be port 21. Make sure that the firewall allows inbound traffic on port 21.

Login

The login will be the name of the account you wish to use to authenticate against your FTP Server with. You have two options:

      • Anonymous
      • NT Account

“Anonymous authentication” is a guest account, which means any password will work, provided the users logs on using the account Anonymous (case insensitive). “NT Account” means that you have created a local machine account and applied permissions to the filed and folders under the FTP directory or virtual directory to restrict access. Some security experts recommend using Anonymous authentication over an NT account, because:

      • if you use an NT account, chances are that this NT Account will follow a naming convention used elsewhere on your server or Enterprise and you are therefore giving your hackers possibly useful information for them in their hacking attempts
      • using NT authentication is really only raising the bar slightly, so you are only keeping out more junior hackers; the more sophisticated ones will sail right in
      • Most security compromises are the result of internal unauthorized access (Joe in accounting) as opposed to external hackers.

Microsoft recommends that you use a VPN to replicate over the Internet; however most users find hardware VPN solutions too expensive, and software VPNs are too slow.

Password and confirm password

Enter a password and confirm it. Note that there will be no attempts to validate the accuracy of the password in this dialog.

SQL Server 2005

Configuring a publication for replication over the internet is very similar for SQL 2005 with the exception of the web synchronization feature for merge replication (covered in a future article), however the dialog now spread over two tabs. In figure 3, notice the option to have your snapshot files located in an alternate folder – the put your files in the following folder option. You also have the option to compress your snapshot files there. If you are deploying your snapshot via an ftp server, the alternate folder should be a location within the root of your ftp server, or a virtual directory.

After you have completed configuring these two settings click on the FTP Snapshot tab as illustrated in figure 5.

Figure 5: The FTP Snapshot dialog box.

The above options are completely symmetrical with the SQL 2000 options.

Configuring Your Subscriber/Subscription

There are two parts to configuring your Subscriber to pull the subscription:

      • Registering the Subscriber on the Publisher
      • Configuring an Account to use to pull the subscription

You only need to register the Subscriber on the Publisher if you are using a named subscription. When you are replicating over the Internet, you will need to use Anonymous Subscribers, as these offer better performance than using Named Subscribers if you have large numbers of Subscribers.

Named Subscribers – SQL 2000

Ideally, when you are configuring your Subscriber, all you will do is connect with your Publisher/Distributor in Enterprise Manager and click Tools, point to Replication, and click Configure Publishers, Subscribers, and Distribution. Click on the Subscribers tab, and click the check box to the left of your Subscriber. This is illustrated in figure 6.

Figure 6: Configuring your Subscriber.

If your SQL Server does not show up here, click on New to register it. You may need to use Client Network Utility to create an Alias to your Subscriber. Ideally, this alias will be to an IP address or a FQDN as illustrated in figures 7 and 8.

Figure 7: A Subscriber aliased in Client Network Utility using a FQDN.

Figure 8: A Subscriber aliased in Client Network Utility using an IP Address.

SQL Server 2005

To register a new subscriber right click on the Local Subscriptions folder, select New Subscription and select and follow the dialogs until you get to the Subscribers dialog box. Click on the Add Subscriber button to register your SQL Server Subscriber, or select pre-registered SQL Servers in the left hand pane. This is illustrated in figure 9.

Figure 9: The Subscribers dialog box.

You may need to register your SQL Server subscriber. To do this click Start, point to programs, Microsoft SQL Server 2005, Configuration Tools, and click SQL Server Configuration Manager. Expand the SQL Server Native Client Configuration node, and right click on Aliases, and select New Alias. The dialog is illustrated in figure 10.

Figure 10: The Alias – New dialog box.

Create an alias to your subscriber. This dialog’s properties are identical to that of SQL 2000.

Anonymous Subscribers

If you have a large number of Subscribers or if you do not know the IP addresses of your Subscribers, you will be unable to register them in Enterprise Manager on the Publisher, and you need to take two steps:

      • You will need to configure your Publication for Anonymous Subscribers. To do this, right click on your Publication and select Properties. Click the Subscription Options tab and check Allow Anonymous Subscriptions.
      • Your Subscribers must be able to connect to your Publisher to pull the Subscription. You have several options:
          • Configure a hosts file that contains the name of your publisher and its IP address. You can do this by going to every Subscriber machine, navigate to %windir%\System32\Drivers\Etc, and making the entry in a text file you will find there, called “hosts.” Your entry will look like this: Publisher 234.34.123.
          • Connect to each Subscriber using remote control software, launch Client Network Utility, and configure an Alias to your Publisher using TCP/IP and its IP address; then, register this publisher in Enterprise Manager on your Subscriber machine.
          • Use ActiveX Controls to pull your subscription. ActiveX Controls have programmatic methods which allow you to create an Alias without having to register the Publisher in Client Network Utility.

After creating the alias, ensure that the account to which the Subscriber will connect to the Publisher is listed in the PAL for the publication.

For SQL 2000 set the account you wish to use to pull your subscription by connecting to your Subscriber in Enterprise Manager, and clicking Tools, pointing to Replication, and clicking Pull Subscription to your subscriber. Follow the prompts and enter the account and password in the Specify Synchronization Agent Login dialog box as illustrated in figure 11.

Figure 11: The Specify Synchronization Agent Login dialog box.

Follow the prompts to complete your subscription. Then on your Publisher add this account to the SQL Server logins. Make the default database your publication database, and add this account to the Public role. Once the account is added, right click on your publication, and select Properties. Then click the Publication Access List and add the account here.

For SQL 2005, connect to your subscriber using SQL Server Management Studio, expand the Replication node, and right click on Local Subscriptions. Select New Subscriptions and follow the dialogs until you get to the Distributor Agent security dialog box. Click on the browse button to the right of the Connection to Subscriber group. Enter the security information here. This option is illustrated in figure 12.

Figure 12: The Distribution Agent Security dialog.

Ensure that you select Using the following SQL Server login and enter the credentials here for an account which is configure in the PAL on your publisher.

Configuring your FTP server

There are three aspects to configuring your FTP server for replication:

      • An NT Account
      • Restricting FTP access via IP Ranges
      • Configuring a Virtual Directory

An NT account

If you choose not to use Anonymous authentication, you must create a local NT account and then assign this account rights to read the files and folders beneath the FTP root or your virtual directory. This account should have Read and List Folder Contents permissions for the FTP root or your virtual directory.

Next, make sure this is the account you use when specifying your account and password in your Snapshot Location folder as illustrated in figure 3.

Restricting FTP access via IP ranges

FTP has the ability to grant or deny access by IP address. If your clients are located in a known, fixed IP range, you can selectively grant access to this IP range. To do this, connect to the IIS Manager, expand the FTP Site node, right click on your FTP site (or virtual directory), select properties, and click on the Directory Security tab. Enter the ranges of IP address you wish to allow access to your FTP server. This option is illustrated in figure 13.

Figure 13: Restricting FTP Access by IP address ranges.

ActiveX Controls

ActiveX Controls are COM components that allow you to pull subscriptions to your Server or work station. These lightweight components allow you to quickly build subscriptions without too much coding effort. They are ideal for situations in which you can’t visit each subscriber to register the Publisher in the Subscriber’s Enterprise Manager or to create an alias to your Subscriber using Client Network Utility.

Consider replicating over the Internet and having to connect to a Publisher with a netbios name of Publisher. The FQDN name for this Publisher is SQLServer1.MyDomain.com. The code to connect to this Publisher and pull the subscription would look like this:

 option explicit
 Const DB_AUTHENTICATION = 0
 Const NT_AUTHENTICATION = 1
 Const TRANSACTIONAL = 1
 Const ANONYMOUS = 2
 Const PULL = 1
 Const FILETRANSFERFTP = 1
 Const TCPIP_SOCKETS = 1
 dim objSQLDist, objNetwork

 Set objSQLDist = 
 CreateObject("SQLDistribution.SQLDistribution.2")
 Set objNetwork = Wscript.CreateObject("Wscript.Network")
 objSQLDist.Publication="northwind"
 objSQLDist.Publisher=objNetwork.ComputerName
 objSQLDist.PublisherAddress="SQLServer1.MyDomain.com"
 objSQLDist.PublisherNetwork=TCPIP_SOCKETS 
 objSQLDist.PublisherDatabase="Northwind"
 objSQLDist.PublisherSecurityMode=DB_AUTHENTICATION 
 objSQLDist.PublisherLogin="testInternet"
 objSQLDist.PublisherPassword="testInternet"
 objSQLDist.FileTransferType=PULL 
 objSQLDist.Subscriber=objNetwork.ComputerName
 objSQLDist.SubscriptionType=ANONYMOUS
 objSQLDist.SubscriberSecurityMode=DB_AUTHENTICATION 
 objSQLDist.SubscriberLogin="testInternet" 
 'tran is in the PAL for the publication
 objSQLDist.SubscriberPassword="testInternet"
 objSQLDist.SubscriberDatabase="transub"
 objSQLDist.Initialize
 objSQLDist.Run

Troubleshooting

For troubleshooting, the best approach is to check for Error Details on your merge or distribution agent. Sometimes the error messages in Error Details are not sufficient to allow you to determine exactly what is the cause of the problem, and you will need to diagnose further.

Check the FTP logs

The FTP logs will often provide you with necessary information. To view these logs, navigate to %Windir%\System32\LogFiles and look in a subdirectory entitled \MSFTPSVC1\. (To interpret the status codes you will find there check out this link: http://support.microsoft.com/default.aspx?scid=kb;en-us;318380.)

For instance, the status code 404 means the file or directory can’t be found, 403 means a permission problem (i.e., the NT account does not have read rights on the directory), 401 means access denied – normally, due to an incorrect password.

Enable logging

Logging allows you to review a complete log of all output for a synchronization. Locate your pull agent, which will be in the Pull Subscriptions folder in your database. Right click on it and select Properties. Click on the Synchronization tab, and click on the Agent Properties button. Click on the Steps tab, and double-click on Run Agent. Click in the commands text box and press the End key. Press the space bar and type:

 -Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel 2

Click on Apply OK, Apply OK, and then OK. Restart your agent. A log will be created in your C drive called agent.txt. After your agent fails again, examine this log in your favorite text editor. For more information on this option, check out: http://support.microsoft.com/default.aspx?scid=kb;en-us;312292&sd=tech

Summary

In this article we covered all aspects of replicating over the Internet. Key to this are the following:

      • Use SQL Authentication
      • Make sure the client path to the folder from the snapshot root is /ftp
      • You have an alternate snapshot folder to the root of your ftp site.
      • You enter the FTP site as a FQDN in the Snapshot Location tab.

Use ActiveX controls to pull your subscription and use the PublisherNetwork and PublisherAddress properties to help your control locate your Publisher. This prevents you from having to connect to each subscriber via remote console software to enter a host’s file or build an alias using Client Network Utility.

If you follow the sets noted previously, you will find that replicating over the Internet is relatively simple and painless.

--

Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant in the tri-state area of New York, New Jersey and Connecticut. He was first recognized by Microsoft in 2001 with the Microsoft SQL Server MVP award. After receiving his Bachelor of Applied Science degree in Mechanical Engineering from the University of Toronto, he studied both economics at the University of Calgary and Computer Science at the University of California at Berkeley. Hilary has worked for Microsoft, Merrill Lynch, UBS-Paine Webber, MetLife, VoiceStream, Tiffany & Co., Pacific Bell, Cahners, Novartis, Petro-Canada, and Johnson and Johnson. He has just completed A Guide to SQL Server 2000 Transactional and Snapshot Replication and has a companion volume on merge replication in the works for 2005.


Contributors : Hilary Cotter
Last modified 2006-04-28 02:44 PM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone