Q
Problem solve Get help with specific problems with your technologies, process and projects.

Replicating SQL Server over the Internet, part 1

I am planning to have one SQL Server database on my company's LAN and another database on a server managed by our ISP (i.e. in a seperate location). I would like to replicate these databases across the Internet. Initially there is only a small amount of data (40-50 MB), and we have a 2 MB link to the Internet through our ISP. Is this a sensible idea, and if so, then what tips or suggestions could you offer with regards to replication methods, VPNs and general ideas for starting such a venture?

Based on your question, it sounds like you are really in search of a SQL Server high availability (HA) solution....

Luckily Microsoft has offered a number of options in SQL Server environments to address high availability needs with a varied level of automation, data consistency and overall system availability. Below are a set of SQL Server high availability solutions that address these three needs:

  • Custom code via a DTS Package to move a subset of data scheduled on a predefined basis via SQL Server Agent. For more information visit DTS Basics.
  • Full database backups and restores via native code or from a unique product that compresses the database backups yielding significant time and disk savings as delivered by SQL LiteSpeed from DBassociatesIT. For more information visit Backing Up and Restoring Databases.
  • Log Shipping migrates data at a transaction level as recorded in the database transaction log in a sequential manner from a source system and applies the transactions in the same order to the destination system with the ability to manage the two data sets independently while maintaining transactional integrity between the source and destination databases. For more information visit Log Shipping.
  • Replication to migrate a subset of data via a Publisher, Subscriber and Distributor paradigm with Articles to define the data and a predefined schedule to replicate the data. For more information visit Introducing Replication.
  • Clustering is another option which is comprised of a redundant hardware solution with 2 or more nodes and a single data set on a shared drive to maintain availability when a hardware component fails on the SQL Server. For more information visit Failover Clustering.
  • Some organizations employ combinations of these technologies such as clustering and log shipping to maintain hardware redundancy and two data sets to recover from an immediate hardware failure and maintain latency between the two data sets to recover from data corruption.

As organizations begin to move towards more automated SQL Server high availability solutions, it is important to not overlook the underlying hardware and purchase hardware with a solid reputation and redundant features. These features include redundant hard drives, controller cards and network interface cards (NICs). These components would be complimented by a redundant network infrastructure in terms of connectivity and processing for the user community to enjoy no interruptions. As time progresses it is imperative to ensure these servers remain under warranty and consider an agreement with the hardware vendor to have the needed equipment on site and repaired with one, two, four or eight hours of the failure. Although this was a brief explanation of the SQL Server high availability solutions, for more information about SQL Server 2000 high availability options visit the Microsoft SQL Server 2000 High Availability Series.

This response is continued...

This was last published in February 2004

Dig Deeper on SQL Server Replication

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close