0

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

Improve this question

4 Answers 4

Reset to default
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.

Improve this answer
5
  • Thanks for the answer, since we are storing big number of files, the DB grows by 1GB per day, mirroring is not possible because DB currently is 200GB and expected to grow to 1TB by end of this year. On Windows 2008 Web Edition there is no NAT service (I dont know correctly), if it exists and if you can configure, please let me know. I agree installing VPN is itself a huge burden on my shoulder but dont know how to get this SQL replicated automatically.
    – Akash Kava
    Jul 24, 2009 at 19:48
  • Are your servers sitting dirrectly on the Internet, or are they behind a firewall and router? I assumed they were behind a router. If not then you can use replication or mirroring directly to the public IP. Just open a whole in the firewall on the needed port. A 1 TB database shouldn't be a problem for mirroring. You may want to look into moving the files out of the database and only storing pointers in the database to the files, or using SQL 2008's file stream (not sure how this would effect or replication).
    – mrdenny
    Jul 25, 2009 at 2:51
  • After you have setup either the replication or the mirroring (I'm assuming this is for DR) how are you going to route the traffic to the backup site?
    – mrdenny
    Jul 25, 2009 at 2:54
  • Mirroring is not an option because these servers are only for DR, not for HA, for HA we have divided our data on multiple servers. One server has maximum of 250GB data. And we want this server to replicate other 3 server's 250GB onto it. So for HA, each server has 3 different options.
    – Akash Kava
    Jul 25, 2009 at 12:27
  • Mirroring is a perfectly viable solution for DR. Many people use Clustering for HA, and Mirroring for DR.
    – mrdenny
    Jul 26, 2009 at 4:51
0

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?

Improve this answer
1
  • Thanks for your reply, however we are not worried about security but we are more worried about backup of our data and thats why replication is important over internet as our data is simple live web portal data, so it probably has no use if it is not online and we dont care if somebody accidentially deleted anything, but we need redundancy
    – Akash Kava
    Jun 24, 2010 at 15:22
0

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.

Improve this answer
3
  • There is no documentation on how to setup Web Sync method, when I try to add replication subscription at other server, it just doesnt accept anything over internet. I am ready to use web sync but i dont know how to configure other SQL 2008 to subscribe.
    – Akash Kava
    Jul 24, 2009 at 20:56
  • I just read here msdn.microsoft.com/en-us/library/ms345214.aspx that you cant add subscriber by management studio, you have to actually add it via SQL stored procedures, and there isnt any sample code, the procedures have many parameters and I am new on all this.
    – Akash Kava
    Jul 24, 2009 at 21:00
  • This isn't a basic setup you are dealing with. A system of this size needs an experienced DBA/Architect to ensure the highest levels of performance as making changes after the fact will be very tough.
    – mrdenny
    Jul 25, 2009 at 2:53
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .