Sql-server – Download the replication snapshot file using FTPS

A few thoughts. It sounds to me like you may be able to get away with a couple options if replication is what you end up with. First a word of caution - Replication isn't something that should just be configured in production and enabled/used unless you have some experience with it. This becomes truer with more important workloads and busier systems. Replication can cause headaches if not properly done. I'd invest in mentoring/training/consulting to help you along the path from folks who have been burned by mistakes already.

That said. It looks to me like your requirements don't seem to have any 2-way changes that need to be synced up or merged. It looks like you have a bunch of things that need to go one directional. Orders never come in locally, only on the website. Order status never gets generated on the website, only consumed, etc.

If that is the case - this sounds like Transaction Replication to me. Merge is more for when you need to have multiple versions that can make changes and receive changes at the same time. The tired old Microsoft example still works - a bunch of sales folk all with a local app that does lead management and a big central server. They can get their leads when in the home office and get data, then they work disconnected and sync up their changes. With Merge you have to deal with conflicts (if the same data is modified in two places, who wins?) Transactional is more what is used when you have changes going in one direction (but you can have it go bidirectional here too even).

And you can set replication up to have various tables included or not included in the publication as articles.

A couple links to give you a bit more info:

Transactional Replication Overview

Merge Replication Overview

Now if you have data that can be updated on each and needs to be "merged" then merge may be a better answer. When I work with replication - I prefer to stay as close to Transactional as I can, though. It is easier to administer and there is no conflict resolution worries if you don't need it.

Depending on your volume - if it is low enough - and your latency - if some lag time is acceptable - you might also consider something more programatic or ETL like. Perhaps staging data and sending it over in batches. Sending messages across as data changes in one side, etc.

According to Books Online, the certificate used for encrypting connections must meet the following requirements:

1. The certificate must be issued for Server Authentication
2. The name (common name or CN) on the certificate must match the full-qualified domain name used by clients to connect.

If those two conditions are not met, the certificate will not show in the drop-down listing. Additionally, the service account for SQL Server must have read access to the private key of the certificate. Without that right, you will be able to select the certificate, but the service will not start.

Since this is a test system you intend to use as proof-of-concept, a self-signed certificate should be adequate. I am not sure what language you are using for your client application, but this link provides options available for connection strings in .Net. You should review at least the following two options.

• Encrypt - Tells the client application to force encryption.
• TrustServerCertificate - Tells the client application to skip validating the certificate chain. This is important with a self-signed certificate as the chain likely will not be trusted.