Home / server / Questions / 45126
Accepted
Akash Kava
Asked: 2009-07-25 04:40:54 +0800 CST

SQL 2008 Replication over Internet

  • 772

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.

  1. SQL 2005 supports or can be configured to support replication over internet? If Yes then should we downgrade to 2005?

  2. 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?)

sql-server-2008 replication

4 Answers

  1. 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.

    • 1

  2. As mrdenny said, it shouldn't be much of a problem as long as you have the correct ports public facing. Assuming you have more infrastructure then just a couple of MSSQL boxes, aren't you going to want a VPN anyway?

    • 0

  3. SQL Server Merge Replication supports Web Synchronization. Web Synchronization uses Web Services over SSL to support secure replication over the internet.

    "The Internet connection uses Secure Sockets Layer (SSL); therefore, a virtual private network (VPN) is not required"

    Please see: http://msdn.microsoft.com/en-us/library/ms151763.aspx

    This form of replication is slightly slower than if you had a direct TCP connection, but that is the tradeoff you make for replication over SSL. This solution has worked well for us, and I hope it will work well for you as well, so you don't need to write a custom sync service.

    • 0
  4. Best Answer

    I finally wrote trigger based own replication program, that helps for now !! But its so pity that even after buying Sql Standard License, the easy replication thing also needed to be hand written !!! MySQL offers easy replication over internet, but mysql uses innodb "secrete file format", not only that, it doesnt have good auto grow feature that leads to too many fragements for bigger tables.

    • 0