We have decided to put our servers in data centers on east and west coast of US, to keep high level redundancy. After evaluating number of replication options, apart from VPN there is no other way to do replication for SQL Server. We are investigating VPN but I have following questions.
Our Large DB consists of media information (pictures/movies/audio/pdf) etc, so we are not very concerned about security because they are not financial sensitive data.
-
SQL 2005 supports or can be configured to support replication over internet? If Yes then should we downgrade to 2005?
-
If SQL 2008 Publisher is configured for Web Sync, can we write an automatic program (C# Windows Service) to act as pull subscriber and run on the subscriber server and replicate subscriber database?
Or are there any API available in SQL where we can write our own program to do replication in very generic way? (In a nut shell, can we write our own C# Windows Service based Subscriber program?)
Best Answer
Yes you can write your own Replication, but it isn't necessary. You can use SQL Server Replication between the two data centers without a VPN between them (a VPN is always preferred).
Before configuring Replication I'd urge you to look at SQL Server's Database Mirroring between the two sites. Database Mirroring will give you an exact copy of the database on the other site.
In either case you simply need to open the correct TCP ports in the firewalls at both sites (and configure NAT on both sites between a public IP and the private IP if the SQL Servers have private IPs). Then simply tell the Replication or database mirroring to use the public IP of the remote SQL Server as the replication or mirroring partner.
As I said above, you'll want to setup a VPN between the sites so that you can route all your Active Directory replication traffic over that VPN, as well as all your administrative traffic when you are managing the remote machines.