Hamachi:Microsoft SQL Server Replication
From LogMeInWiki
Contents |
Overview
The goal of this article is to demonstrate a practical application of using Hamachi to provide the underlying connectivity necessary to replicate two Microsoft SQL databases across the Internet. This article does not deal heavily with security concerns, so please consider proper security precautions before attempting any implementation that deals with confidential data.
Do not use the "sa" SQL account in a production implementation. It is a security issue, and it is a worst practice.
Concepts
This article will deal with two concept. They are
- A publisher/distributor server. This server will be the main data set that will be replicated out.
- A subscriber server. This server will be the server that "gets" the data from the distributor.
For this scenario, we will assume that there are two identical Microsoft Server 2003 servers running Microsoft SQL Server 2005 Service Pack 2.
Before you start
- Ensure that each server is configured to accept TCP connections. This is not a default for security reasons. This is done in SQL Server Configuration Manager.
- Make sure each server can ping the other, by the host name you intend to use for replication. If you can not ping by host name via normal means (NetBIOS typically works), see the troubleshooting article for help.
Publisher/Distributor Configuration
Configuring the distribution server is fairly easy. The concept to keep in mind for the context of this article is that the distributor server will be the same as the publisher, so in essence, all you need to do is configure a publication.
- Open SQL Server Management Studio
- Connect to the server that will be the publisher. You must connect via the hostname, even if you're logged in to localhost/127.0.0.1 , or you will get an error message when trying to start the replication config wizard.
- Right click on Replication and choose Configure Distribution.
- Click Next, Next again leaving <server> will act as its own Distributor.
- Define a path where the snapshot data will be stored. This path needs to have enough space to mirror your entire databases, so make sure you have enough room beforehand. For safety's sake, this should not be the same partition/drive as the operating system (nor should your actual databases be stored there, even though it's a default). Click Next.
- Name your Distribution, and choose the locations to store the replication database and log. Click Next twice.
- Choose both the option to create the Distribution, and also the scripts. Creating scripts now will make it easier to recover your configuration if there is a catastrophic failure in one of your servers in the future.
- Click Next, and then click Finish.
Distribution is now configured. Now to create a Publication.
- Expand Replication, and right click Local Publications.
- Click New Publication.
- Click Next, choose the desired database you want to replicate, and click Next.
- Choose the type of replication you want to use. In this example, we'll use Snapshot Replication. Do some homework on which type of replication is best for your scenario. Snapshot Replication is good for creating a replicated, non-editable copy of the database for scenarios like making a local reporting server that users can query without impacting the production environment.
- Choose the tables to replicate, click Next.
- Filter the data set as desired, click Next.
- Check Create a snapshot immediately, click Next.
- Configure the security settings as needed. The exact options are out of the scope of this article, but you should use MSDN or TechNet for recommendations on best security practices.
- Click Next, create the publication, and the scripts as done above with the distribution wizard.
- Click Next, provide a Publication Name, and then click Finish.
Your Publication server should now be ready for its Subscribers.
Subscriber Configuration
- Open SQL Server Configuration Manager
- Connect to the server that will receive the replication data via the hostname. Similar to above, you must use the hostname, not an IP address or localhost.
- Expand Replication
- Right click Local Subscriptions and click New Subscriptions
- Choose or search for the publisher server. Remember to use the host name if you have to use the find option.
- Choose the Publication to replicate.
- Choose the option to Run all agents on the Distributor.
- Confirm the subscriber server, and the target database. If you don't have a target database yet, you can create it now. One caveat, you must ensure the data file settings on the target database are sufficient to receive all the replicated data, otherwise the replication job will fail. It is highly suggested that you use the Script As option to reproduce the database and tables of the Publication database onto the Subscriber server so that you can ensure proper table configuration, file groups, data file settings, etc.
- Configure Distribution Agent Security. Again, refer to MSDN or TechNet for best practices.
- Configure a schedule, and click Next.
- Click Finish.
Your Subscriber server is now configured. This should complete the setup of Microsoft SQL Server Replication.
Notes
- Although Hamachi is being used to facilitate the replication, the setup for the replication itself is pretty standard, and nothing specific to Hamachi is really required, with the exception of ensuring proper host name resolution. Whether NetBIOS, DNS, or HOSTS file entries are used, the configuration is exactly the same once that name resolution is confirmed working at both ends.
- There is no bandwidth throttling built into Hamachi by design. Keep this in mind when configuring the scheduling for subscriptions, as it's important to ensure subscription replication does not impact the local or production environments.----
Note: This article was written for the Hamachi 1.x version. Some features and functionality may be different, missing, or using different steps in Hamachi version 2.x . If you wish to update this article to cover Hamachi2, change the {{{c-hamachi}}} tag to {{{c-hamachi2}}} after your edits.