Sql-server – How to implement data encryption in SQL Express for a VB6 application

It sounds like the dba is talking about horizontal partitioning rather than table partitioning, by breaking the troublesome tables using rules, such as all customers that start with the letter a go in tableA, b in tableB, etc. This can be helpful in some circumstances, and can be done with any edition of SQL server, but has many of the same issues already mentioned, i.e. I/O.

For a meer 52 client sites replication will do just fine. Consider your local copy as the 'master' site and have the application work only on the local copy, always. Use replication to aggregate the data to the central repository for aggregated reporting. Index maintenance operations normally do not generate replicaiton traffic. Manage schema changes through application deployment migrations, ie. have upgrade scripts for each released version.

As the number of sites increases, managing replication becomes harder and harder. Also licensing would push toward deploying Express on periphery. And SQL Server upgrade is an absolute pain when replication is involved, as the order of upgrading the components (publisher, distributor, subscriber) is critical is but difficult to coordinate with many sites. Deployments that I know of with +1500 sites use customized data movement based on Service Broker. See Using Service Broker instead of Replication for an example. I've even seen designes that use Service Broker to push out to periphery new bits (application changes) which in turn deploy, when activated, migrations and schema changes, making the entire deployment operated from the center.